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\n’;
Query OK, 50 rows affected (0.02 sec)
Records: 50 Deleted: 0 Skipped: 0 Warnings: 0
微信扫一扫，打赏作者吧～
目前这篇文章有1条评论(Rss)评论关闭。