CREATE TABLE t(
id INT(10) NOT NULL AUTO_INCREMENT,
name CHAR(20) NOT NULL,
PRIMARY KEY(id)
);data.txt
1,张三
2,李四
3,王五
4,zhangsan
5,wangwuMYSQL和my.ini配置都是GBK,而文本文档没有GBK的编码,只有ANSI,Unicode,Unicode big endian,utf-8四种格式
我选择的是utf-8格式。mysql> LOAD DATA INFILE 'C:/data.txt' INTO TABLE t CHARACTER SET UTF8 FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n';
mysql> SELECT * FROM T;
+----+-----------+
| id | name |
+----+-----------+
| 张三
| 李四
| 王五
| 4 | zhangsan
| 5 | wangwu |
+----+-----------+
mysql> LOAD DATA INFILE 'C:/data.txt' INTO TABLE t CHARACTER SET GBK FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n';
mysql> SELECT * FROM T;
+----+-----------+
| id | name |
+----+-----------+
| | 寮犱笁
| | 鏉庡洓
| | 鐜嬩簲
| 4 | zhangsan
| 5 | wangwu |
+----+-----------+LOAD DATA INFILE 'C:/data.txt' INTO TABLE t FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n';
mysql> SELECT * FROM T;
+----+-----------+
| id | name |
+----+-----------+
| | 寮犱笁
| | 鏉庡洓
| | 鐜嬩簲
| 4 | zhangsan
| 5 | wangwu |
+----+-----------+
怎么正常插入,在公司的时候是可以的,但是查询的时候中文查询不出,SELECT * FROM t WHERE name='张三'; 没有结果。
现在回家想测试下,竟然都是乱码了.
id INT(10) NOT NULL AUTO_INCREMENT,
name CHAR(20) NOT NULL,
PRIMARY KEY(id)
);data.txt
1,张三
2,李四
3,王五
4,zhangsan
5,wangwuMYSQL和my.ini配置都是GBK,而文本文档没有GBK的编码,只有ANSI,Unicode,Unicode big endian,utf-8四种格式
我选择的是utf-8格式。mysql> LOAD DATA INFILE 'C:/data.txt' INTO TABLE t CHARACTER SET UTF8 FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n';
mysql> SELECT * FROM T;
+----+-----------+
| id | name |
+----+-----------+
| 张三
| 李四
| 王五
| 4 | zhangsan
| 5 | wangwu |
+----+-----------+
mysql> LOAD DATA INFILE 'C:/data.txt' INTO TABLE t CHARACTER SET GBK FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n';
mysql> SELECT * FROM T;
+----+-----------+
| id | name |
+----+-----------+
| | 寮犱笁
| | 鏉庡洓
| | 鐜嬩簲
| 4 | zhangsan
| 5 | wangwu |
+----+-----------+LOAD DATA INFILE 'C:/data.txt' INTO TABLE t FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n';
mysql> SELECT * FROM T;
+----+-----------+
| id | name |
+----+-----------+
| | 寮犱笁
| | 鏉庡洓
| | 鐜嬩簲
| 4 | zhangsan
| 5 | wangwu |
+----+-----------+
怎么正常插入,在公司的时候是可以的,但是查询的时候中文查询不出,SELECT * FROM t WHERE name='张三'; 没有结果。
现在回家想测试下,竟然都是乱码了.
MySQL 中文显示乱码
+--------------------------+---------------------------------------------------------+
| Variable_name | Value|
+--------------------------+---------------------------------------------------------+
| character_set_client | gbk|
| character_set_connection | gbk|
| character_set_database | gbk|
| character_set_filesystem | binary|
| character_set_results | gbk|
| character_set_server | gbk|
| character_set_system | utf8|
| character_sets_dir | D:\Program Files\MySQL\MySQL Server 5.0\share\charsets\ |
+--------------------------+---------------------------------------------------------+
8 rows in set (0.00 sec)在公司导入不会乱码,原来是default-character-set=utf8
不过这样设置了后,SELECT * FROM T WHERE name ='张三'就查询不出。为什么
SELECT * FROM t WHERE name LIKE '张三%'; 这样就可以查询出,其他中文都这样,但是我可以确保在文本文档里的“张三”后面没有内容