用load data的时候 第一行出现了乱码
要导入的数据mysql.txt:(txt的编码我已经保存为utf8了)
223456济南[email protected]
468872美国[email protected]
985435云南[email protected]
036597非洲[email protected]
698743泰安[email protected]
456987上海[email protected]
创建语句如下:mysql> show create table t_sql \G;
*************************** 1. row ***************************
Table: t_sql
Create Table: CREATE TABLE `t_sql` (
`phone` varchar(6) DEFAULT NULL,
`addr` varchar(8) DEFAULT NULL,
`email` varchar(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
导入的语句如下:mysql> load data infile 'c:\\mysql.txt'
-> into table t_sql (@xx)
-> set phone=SUBSTRING(@xx,1,6),
-> addr=SUBSTRING(@xx,7,2),
-> email=SUBSTRING(@xx,9);
Query OK, 6 rows affected (0.02 sec)
Records: 6 Deleted: 0 Skipped: 0 Warnings: 0
导入后的结果:mysql> select * from t_sql \G;
*************************** 1. row ***************************
phone: 锘?2345
addr: 6娴?
email: 鍗梛[email protected]
*************************** 2. row ***************************
phone: 468872
addr: 缇庡浗
email: [email protected]
*************************** 3. row ***************************
phone: 985435
addr: 浜戝崡
email: [email protected]
*************************** 4. row ***************************
phone: 036597
addr: 闈炴床
email: [email protected]
*************************** 5. row ***************************
phone: 698743
addr: 娉板畨
email: [email protected]
*************************** 6. row ***************************
phone: 456987
addr: 涓婃捣
email: [email protected]
6 rows in set (0.00 sec)*************************** 1. row ***************************
phone: 锘?2345
addr: 6娴?
email: 鍗梛[email protected]第一行,的数据是223456济南[email protected]
为什么导入后会乱码呢?
其他行的addr在客户端显示是乱码,但是我用mysql的工具查看中文是没有乱码的
要导入的数据mysql.txt:(txt的编码我已经保存为utf8了)
223456济南[email protected]
468872美国[email protected]
985435云南[email protected]
036597非洲[email protected]
698743泰安[email protected]
456987上海[email protected]
创建语句如下:mysql> show create table t_sql \G;
*************************** 1. row ***************************
Table: t_sql
Create Table: CREATE TABLE `t_sql` (
`phone` varchar(6) DEFAULT NULL,
`addr` varchar(8) DEFAULT NULL,
`email` varchar(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
导入的语句如下:mysql> load data infile 'c:\\mysql.txt'
-> into table t_sql (@xx)
-> set phone=SUBSTRING(@xx,1,6),
-> addr=SUBSTRING(@xx,7,2),
-> email=SUBSTRING(@xx,9);
Query OK, 6 rows affected (0.02 sec)
Records: 6 Deleted: 0 Skipped: 0 Warnings: 0
导入后的结果:mysql> select * from t_sql \G;
*************************** 1. row ***************************
phone: 锘?2345
addr: 6娴?
email: 鍗梛[email protected]
*************************** 2. row ***************************
phone: 468872
addr: 缇庡浗
email: [email protected]
*************************** 3. row ***************************
phone: 985435
addr: 浜戝崡
email: [email protected]
*************************** 4. row ***************************
phone: 036597
addr: 闈炴床
email: [email protected]
*************************** 5. row ***************************
phone: 698743
addr: 娉板畨
email: [email protected]
*************************** 6. row ***************************
phone: 456987
addr: 涓婃捣
email: [email protected]
6 rows in set (0.00 sec)*************************** 1. row ***************************
phone: 锘?2345
addr: 6娴?
email: 鍗梛[email protected]第一行,的数据是223456济南[email protected]
为什么导入后会乱码呢?
其他行的addr在客户端显示是乱码,但是我用mysql的工具查看中文是没有乱码的
贴出你的以下结果。show variables like '%char%';http://blog.csdn.net/ACMAIN_CHM/archive/2009/05/12/4174186.aspx
MySQL 中文显示乱码
然后上传你的c:\\mysql.txt'可以上传到 http://www.access911.net/csdn
复制进去和select查询出的中文 也都是乱码。。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:\Program Files\MySQL\MySQL Server 5.1\share\charsets\ |
+--------------------------+---------------------------------------------------------+
8 rows in set (0.00 sec)
mysql> set names utf8;
Query OK, 0 rows affected (0.00 sec)还有我安装的时候选择编码。我也是选的uft8。版本是5.1.35mysql> \s
--------------
C:\Program Files\MySQL\MySQL Server 5.1\bin\mysql.exe Ver 14.14 Distrib 5.1.35, for Win32 (ia32)Connection id: 1
Current database: test
Current user: root@localhost
SSL: Not in use
Using delimiter: ;
Server version: 5.1.35-community MySQL Community Server (GPL)
Protocol version: 10
Connection: localhost via TCP/IP
Server characterset: utf8
Db characterset: utf8
Client characterset: utf8
Conn. characterset: utf8
TCP port: 3306
Uptime: 2635 days 18 hours 28 min 28 secThreads: 3 Questions: 210 Slow queries: 0 Opens: 46 Flush tables: 1 Open tables: 1 Queries per second avg: 0.0
--------------
地址为:
http://access911.net/csdn/FileDescription.asp?mdb=2010-10-8&id=6
set names 'gbk';然后再试
关于为什么,在 #1楼 给你的贴子中已经有描述了。
但是第一行的第一个还是乱码 也不能说是乱码 只是感觉我的数据前还有一个字符
但是光看的话 已经没有了mysql> select * from t_sql \G;
*************************** 1. row ***************************
phone: ?22345
addr: 6济
email: 南[email protected]
*************************** 2. row ***************************
phone: 468872
addr: 美国
email: [email protected]
*************************** 3. row ***************************
phone: 985435
addr: 云南
email: [email protected]
*************************** 4. row ***************************
phone: 036597
addr: 非洲
email: [email protected]
*************************** 5. row ***************************
phone: 698743
addr: 泰安
email: [email protected]
*************************** 6. row ***************************
phone: 456987
addr: 上海
email: [email protected]
*************************** 7. row ***************************
phone: ?22345
addr: 6济
email: 南[email protected]
*************************** 8. row ***************************
phone: 468872
addr: 美国
email: [email protected]
*************************** 9. row ***************************
phone: 985435
addr: 云南
email: [email protected]
*************************** 10. row ***************************
phone: 036597
addr: 非洲
email: [email protected]
*************************** 11. row ***************************
phone: 698743
addr: 泰安
email: [email protected]
*************************** 12. row ***************************
phone: 456987
addr: 上海
email: [email protected]
12 rows in set (0.00 sec)
然后在DOS的MYSQL命令行中直接以 set names 'latin1' f直接LOAD就行 了。mysql> load data infile 'C:\\Users\\liuyann\\TEMP\\mysql.txt'
-> into table t_sql (@xx)
-> set phone=SUBSTRING(@xx,1,6),
-> addr=SUBSTRING(@xx,7,2),
-> email=SUBSTRING(@xx,9);
Query OK, 6 rows affected (0.02 sec)
Records: 6 Deleted: 0 Skipped: 0 Warnings: 0mysql>
mysql> select * from t_sql;
+--------+------+----------------------+
| phone | addr | email |
+--------+------+----------------------+
|23456 | 济 | 南[email protected]
|68872 | 美 | 国[email protected]
|5435 | 云 | 南[email protected]
|036597 | 非 | 洲[email protected]
|3 | 泰 | 安[email protected]
| 456987 | 上 | 海[email protected] |
+--------+------+----------------------+
6 rows in set (0.00 sec)mysql>