mysql 客户端数据导入

mysql 客户端数据导入要么生成insert sql,也可以用load data infile,记的字符集可不能忽略，设置字符集比如set name gb2312;下面做一个实例，用load的方法
－－－－－
a.txt内容
64100194023 58012J01F
64100194032 58012J002
64100194010 58012J003
64100194047 58012J004
64100194057 58012J005
64100194064 58012J006
64100194072 58012J007
64100194089 58012J008
64100194096 58012J009
64100194100 58012J00A
.....
C:\\>mysql -h 192.168.212.51 -u root -p
Enter password: ********
Welcome to the MySQL monitor.  Commands end with ; or \\g.
Your MySQL connection id is 3454443
Server version: 5.0.77-log MySQL Community Server (GPL)
Type 'help;' or '\\h' for help. Type '\\c' to clear the current input statement.
mysql> use qkzgpx_hunan
Database changed
mysql> show tables;
+--------------------------+
| Tables_in_qkzgpx_hunan   |
+--------------------------+
| arti_article             |
| arti_article_attachment  |
| auxi_config              |
| auxi_msg                 |
| auxi_msg_ctg             |
| cms_admin                |
| cms_admin_channel        |
| cms_card                 |
| cms_card_log             |
| cms_career               |
| cms_channel              |
| cms_channel_attchment    |
| cms_chnl_model           |
| cms_chnl_model_item      |
| cms_comment              |
| cms_config               |
| cms_content_ctg          |
| cms_member               |
| cms_member_group         |
| cms_member_learninglog   |
| cms_recommend_group      |
| cms_recommend_item       |
| core_admin               |
| core_admin_function      |
| core_admin_role          |
| core_attachment          |
| core_function            |
| core_global              |
| core_member              |
| core_org                 |
| core_role                |
| core_role_function       |
| core_tpl_solution        |
| core_user                |
| core_website             |
| county_tree              |
| down_download            |
| down_download_attachment |
| down_language            |
| down_license             |
| down_record              |
| down_type                |
| ext_healthedu            |
| ext_learning             |
| ext_learning_history     |
| ext_paper_practise       |
| ext_quescate             |
| ext_question             |
| ext_register             |
| ext_testing              |
| ext_total_score          |
| ext_updatelog            |
| ext_video_gallery        |
| stat_member_sum          |
| stat_project             |
| stat_score               |
| stat_study               |
| tmp_chnl_arti            |
| tmp_data_common          |
| tmp_data_score           |
| tmp_data_study           |
| tmp_member_learning      |
| tmp_member_sum_all       |
| tmp_result               |
| tmp_sc_county            |
| tmp_score_detail         |
| video_type               |
| vote_item                |
| vote_record              |
| vote_topic               |
+--------------------------+
70 rows in set (0.00 sec)
mysql> create table imptemp
    -> (col1 varchar(20),
    -> col2 varchar(20));
Query OK, 0 rows affected (0.00 sec)
mysql> load data local infile 'd:\\a.txt' into table imptemp character set gb231
 fields terminated by '\\t';
Query OK, 50 rows affected (0.00 sec)
Records: 50  Deleted: 0  Skipped: 0  Warnings: 0
mysql> select * from imptemp;
+-------------+-------------+
| col1        | col2        |
+-------------+-------------+
  |4100194023 | 58012J01F
  |4100194032 | 58012J002
  |4100194010 | 58012J003
  |4100194047 | 58012J004
  |4100194057 | 58012J005
  |4100194064 | 58012J006
  |4100194072 | 58012J007
  |4100194089 | 58012J008
  |4100194096 | 58012J009
  |4100194100 | 58012J00A
  |4100194116 | 58012J00B
  |4100194123 | 58012J00C
  |4100194131 | 58012J00D
  |4100194142 | 58012J00E
  |4100194152 | 58012J00F
  |4100194163 | 58012J00G
  |4100194177 | 58012J00H
  |4100194185 | 58012J00I
  |4100194198 | 58012J00J
  |4100194203 | 58012J00K
  |4100194211 | 58012J00L
  |4100194221 | 58012J00M
  |4100194235 | 58012J00N
  |4100194246 | 58012J00P
  |4100194251 | 58012J00Q
  |4100194262 | 58012J00R
  |4100194273 | 58012J00S
  |4100194283 | 58012J00T
  |4100194290 | 58012J00U
  |4100194302 | 58012J00V
  |4100194319 | 58012J00W
  |4100194328 | 58012J00X
  |4100194339 | 58012J00Y
  |4100194344 | 58012J00Z
  |4100194354 | 58012J010
  |4100194366 | 58012J011
  |4100194370 | 58012J012
  |4100194383 | 58012J013
 |64100194392 | 58012J01G
  |4100194407 | 58012J014
  |4100194417 | 58012J015
  |4100194424 | 58012J016
  |4100194432 | 58012J017
  |4100194449 | 58012J018
  |4100194456 | 58012J019
  |4100194465 | 58012J01A
  |4100194475 | 58012J01B
  |4100194484 | 58012J01C
  |4100194491 | 58012J01D
  |4100194507 | 58012J01E
+-------------+-------------+
50 rows in set (0.01 sec)
--很怪，不知道为什么截取了一位
mysql> alter table imptemp modify col1 varchar(30);
Query OK, 50 rows affected (0.00 sec)
Records: 50  Duplicates: 0  Warnings: 0
mysql> desc imptemp
    -> ;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| col1  | varchar(30) | YES  |     | NULL    |       |
| col2  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
－－修改后再导入还是有问题，仔细看a.txt里在中间有一行后面有个小空格，删除，保存。
mysql> truncate table imptemp;
Query OK, 0 rows affected (0.00 sec)
mysql> load data local infile 'd:\\a.txt' into table imptemp character set gb2312
 fields terminated by '\\t';
Query OK, 50 rows affected (0.00 sec)
Records: 50  Deleted: 0  Skipped: 0  Warnings: 0
mysql> select * from imptemp;
+-------------+------------+
| col1        | col2       |
+-------------+------------+
 |64100194023 | 58012J01F
 |64100194032 | 58012J002
 |64100194010 | 58012J003
 |64100194047 | 58012J004
 |64100194057 | 58012J005
 |64100194064 | 58012J006
 |64100194072 | 58012J007
 |64100194089 | 58012J008
 |64100194096 | 58012J009
 |64100194100 | 58012J00A
 |64100194116 | 58012J00B
 |64100194123 | 58012J00C
 |64100194131 | 58012J00D
 |64100194142 | 58012J00E
 |64100194152 | 58012J00F
 |64100194163 | 58012J00G
 |64100194177 | 58012J00H
 |64100194185 | 58012J00I
 |64100194198 | 58012J00J
 |64100194203 | 58012J00K
 |64100194211 | 58012J00L
 |64100194221 | 58012J00M
 |64100194235 | 58012J00N
 |64100194246 | 58012J00P
 |64100194251 | 58012J00Q
 |64100194262 | 58012J00R
 |64100194273 | 58012J00S
 |64100194283 | 58012J00T
 |64100194290 | 58012J00U
 |64100194302 | 58012J00V
 |64100194319 | 58012J00W
 |64100194328 | 58012J00X
 |64100194339 | 58012J00Y
 |64100194344 | 58012J00Z
 |64100194354 | 58012J010
 |64100194366 | 58012J011
 |64100194370 | 58012J012
 |64100194383 | 58012J013
 |64100194392 | 58012J01G
 |64100194407 | 58012J014
 |64100194417 | 58012J015
 |64100194424 | 58012J016
 |64100194432 | 58012J017
 |64100194449 | 58012J018
 |64100194456 | 58012J019
 |64100194465 | 58012J01A
 |64100194475 | 58012J01B
 |64100194484 | 58012J01C
 |64100194491 | 58012J01D
 |64100194507 | 58012J01E
+-------------+------------+
50 rows in set (0.00 sec)
--正常
--后来发现如果有空格加上lines 控制也是可以的
mysql> load data local infile 'd:\\a.txt' replace into table imptemp fields termi
nated by '\\t' lines terminated by '\\r\
';
Query OK, 50 rows affected (0.02 sec)
Records: 50  Deleted: 0  Skipped: 0  Warnings: 0