问题:
Mysql版本是5.0.45,默认字符集是utf8,现在需要从一个文本文件中将内容追加到数据库表中。使用中发现当源文本文件中只有字母和数字时用 load data infile 'path/file ' into table 'table ' 命令可以正确导入数据,但是当文本中包括有中文时就会出错,系统会滤掉中文部分的内容,出现乱码问题。
解决:
字符集设置如下:
mysql> show variables like "%char% ";
+--------------------------+---------------------------------------------+
| Variable_name | Value |
+--------------------------+---------------------------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | utf8 |
| character_set_system | utf8 |
| character_sets_dir | C:\MySQL\mysql-5.0.21-win32\share\charsets\ |
+--------------------------+---------------------------------------------+
8 rows in set (0.00 sec)
若设置不是UTF8, 设置一下如:
SET character_set_client=utf8;
SET character_set_connection=utf8;
SET character_set_database=utf8;
SET character_set_results=utf8;
SET character_set_server=utf8;
SET character_set_system=utf8;
建了一个测试表,表结构如下:
mysql> desc test;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.01 sec)
新建了一个文本文件t1.txt,里面有一条记录:1 王二小
接着点另存为,选择字符集UTF-8,再用上面的
load data infile 'E:/Baidu/review/0804/t1.txt' into table test;
最后用mysql query browser来查看,并没有乱码,之所以用工具来查看,是因为在command line下,
dos不支持UTF-8字符集,所有的中文都是乱码的。
---- OVER -----
附录load data infile/outfile 应用:
1。 从mysql到出数据:
select * into outfile 'E:\\Baidu\\review\\0804\\outT2.txt' character set utf8
fields terminated by '|'
from test where name LIMIT 5
2。 带分隔符导入
load data infile 'E:/Baidu/review/0804/outT2.txt' into table test
character set utf8
fields terminated by '|' (责任编辑:最模板) |