mysql 客户端 数据导出_mysql 客户端数据导入

ysql 客户端数据导入要么生成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