最近学习sql语句,网上看到了一个将文本文件中的数据导入mysql数据库的方法
(1)/home/zhu/mysql_data数据如下:2005-05-09 胜
2005-05-09 胜
2005-05-09 负
2005-05-09 负
2005-05-10 胜
2005-05-10 负
2005-05-10 负(2)mysql> load data local infile '/home/zhu/mysql_data' into table temp character set gbk fields terminated by ' ' (date, shengfu);
然后输出如下:mysql> select * from temp;
+------------+---------+
| date | shengfu |
+------------+---------+
| 2005-05-10 | ? |
| 2005-05-10 | ? |
| 2005-05-10 | ? |
| 2005-05-09 | ? |
| 2005-05-09 | ? |
| 2005-05-09 | ? |
| 2005-05-09 | ? |
+------------+---------+
7 rows in set (0.00 sec)
胜负都是问号。(3)mysql> show create table temp;
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------+
| temp | CREATE TABLE `temp` (
`date` varchar(10) DEFAULT NULL,
`shengfu` char(5) CHARACTER SET utf8 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)(4)mysql> show full columns from temp;
+---------+-------------+-------------------+------+-----+---------+-------+---------------------------------+---------+
| Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment |
+---------+-------------+-------------------+------+-----+---------+-------+---------------------------------+---------+
| date | varchar(10) | latin1_swedish_ci | YES | | NULL | | select,insert,update,references | |
| shengfu | char(5) | utf8_general_ci | YES | | NULL | | select,insert,update,references | |
+---------+-------------+-------------------+------+-----+---------+-------+---------------------------------+---------+
2 rows in set (0.00 sec)(5)mysql> show variables like 'char%';
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | latin1 |
| character_set_connection | latin1 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | latin1 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.01 sec)刚接触过mysql数据库,不知道怎么搞得,希望好心人帮忙,感激不尽!!
MySQL数据库
(1)/home/zhu/mysql_data数据如下:2005-05-09 胜
2005-05-09 胜
2005-05-09 负
2005-05-09 负
2005-05-10 胜
2005-05-10 负
2005-05-10 负(2)mysql> load data local infile '/home/zhu/mysql_data' into table temp character set gbk fields terminated by ' ' (date, shengfu);
然后输出如下:mysql> select * from temp;
+------------+---------+
| date | shengfu |
+------------+---------+
| 2005-05-10 | ? |
| 2005-05-10 | ? |
| 2005-05-10 | ? |
| 2005-05-09 | ? |
| 2005-05-09 | ? |
| 2005-05-09 | ? |
| 2005-05-09 | ? |
+------------+---------+
7 rows in set (0.00 sec)
胜负都是问号。(3)mysql> show create table temp;
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------+
| temp | CREATE TABLE `temp` (
`date` varchar(10) DEFAULT NULL,
`shengfu` char(5) CHARACTER SET utf8 DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)(4)mysql> show full columns from temp;
+---------+-------------+-------------------+------+-----+---------+-------+---------------------------------+---------+
| Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment |
+---------+-------------+-------------------+------+-----+---------+-------+---------------------------------+---------+
| date | varchar(10) | latin1_swedish_ci | YES | | NULL | | select,insert,update,references | |
| shengfu | char(5) | utf8_general_ci | YES | | NULL | | select,insert,update,references | |
+---------+-------------+-------------------+------+-----+---------+-------+---------------------------------+---------+
2 rows in set (0.00 sec)(5)mysql> show variables like 'char%';
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | latin1 |
| character_set_connection | latin1 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | latin1 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.01 sec)刚接触过mysql数据库,不知道怎么搞得,希望好心人帮忙,感激不尽!!
MySQL数据库
解决方案 »
- 如何备份整个mysql数据库?
- mysql的反应:use database
- 我用postgres数据库,创建了一个表,create table networkstat (cur_time time,s1 char(8),s2 char(8),s3 char(8)),
- 2003下的mysql服务老是停止
- 哪位大虾知道有什么介绍mysql存储管理、查询管理、事务管理、系统恢复的书或者资料的???急啊!!!谢谢了~~
- 怎么安装MYSQL的JDBC驱动
- 刚学mysql,我在mysql里建了一个数据库,如设置访问我的数据库的密码。(在线等待)
- 一道sql查询题,求高手指教
- 谁有MYSQL中文手册(是**.chm格式的)
- mysql win编译
- MySQL创建delete触发器语法错误了
- mysql表的查询量
set names utf8;
CREATE TABLE `temp` (
`date` varchar(10) DEFAULT NULL,
`shengfu` char(5) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
oad data local infile '/home/zhu/mysql_data' into table temp character set gbk fields terminated by ' ' (date, shengfu);
然后再导入。
我试了下, set names utf8,
然后在load data那句里, character set utf8,可以正常显示
用gbk就还是乱码。是不是看这个
| temp | CREATE TABLE `temp` (
`date` varchar(10) DEFAULT NULL,
`shengfu` char(5) CHARACTER SET utf8 DEFAULT NULL //这里有个utf8
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |得出是设置成utf8?
"然后在load data那句里, character set utf8,可以正常显示
用gbk就还是乱码。"详细描述。
set names ‘gbk';
mysql> load data local infile '/home/zhu/mysql_data' into table temp character set gbk fields terminated by ' ' (date, shengfu);改成 set names 'utf8'
mysql> load data local infile '/home/zhu/mysql_data' into table temp character set utf8 fields terminated by ' ' (date, shengfu);
就没乱码问题
看一下下面的文章
数据库默认类型改为utf-8