为了账号安全,请及时绑定邮箱和手机立即绑定

mysql中的数据导入与导出

2019.01.26 11:19 788浏览
Go开发工程师
点击查看
¥3880.00 3280.00


视频汇总首页:http://edu.51cto.com/lecturer/index/user_id-4626073.html

为了普及mysql的基本知识,特意弄了这个章节,主要是发现第一次接触的人都不知道怎么弄,或者看不懂,所以这里就详细说下吧

============================================================

数据导入

1.mysqlimport命令行导入数据

在使用mysqlimport命令导入数据时,数据来源文件名要和目标表一致,不想改文件名的话,可以复制一份创建临时文件,示例如下。

建立一个文本users.txt,内容如下:

wKiom1VG8VvyJSnwAAB6YYoyAIw669.jpg

创建一个表users

wKioL1VG8q_hcDpfAACUWsehht4443.jpg

使用mysqlimport将users.txt中数据导入users表

PS F:\> mysqlimport -u root -p123456 zz --default-character-set=gbk --fields-terminated-by=',' f:\users.txtzz.users: Records: 3  Deleted: 0  Skipped: 0  Warnings: 0-----------------------------验证----------------------------------mysql> select * from users\G*************************** 1. row ***************************

   id: 1003

 name: 王五

email: wangwu@163.com*************************** 2. row ***************************

   id: 1001

 name: 张三

email: zhangsan@163.com*************************** 3. row ***************************

   id: 1002

 name: 李四

email: lisi@hotmail.com

分列,使用--fields-terninated-by参数来指定每列的分隔符,例如:

wKiom1VG8ZexZtkoAAFBhQ7oYUg243.jpg

如果列值中出现了分隔符,例如 1004"#李#白"#"libai@hotmail.com"

PS F:\> mysqlimport -u root -p7758520 zz  --fields-terminated-by='#' --fields-enclosed-by=\"  f:\users.txt

如果遇到一条记录有多行,则可以使用--lines-terminated-by=name来指定行的结束符

PS F:\> mysqlimport -u root -p7758520 zz  --fields-terminated-by='#' --fields-enclosed-by=\"  --lines-terminated-by='xxx\n' f:\users.txt

2.使用Load Data语句导入数据

Load Data 语句的使用语法如下:

LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name'

    [REPLACE | IGNORE]

    INTO TABLE tbl_name    [CHARACTER SET charset_name]

    [{FIELDS | COLUMNS}

        [TERMINATED BY 'string']

        [[OPTIONALLY] ENCLOSED BY 'char']        [ESCAPED BY 'char']

    ]    [LINES

        [STARTING BY 'string']

        [TERMINATED BY 'string']

    ]    [IGNORE number {LINES | ROWS}]

    [(col_name_or_user_var,...)]

    [SET col_name = expr,...]

刚开始看到这个语法吓了一跳,这么长,其实没这么复杂,一般只需记住LOAD DATA INFILE file_name INTO TABLE tb_name这个即可,示例:

首先创建一个表sql_users,利用上面的users表复制一下

mysql> create table sql_users as select * from users;

Query OK, 1 row affected (0.06 sec)

Records: 1  Duplicates: 0  Warnings: 0mysql> truncate table sql_users;

Query OK, 0 rows affected (0.00 sec)

mysql> select * from sql_users;

Empty set (0.00 sec)

文本sql_users.txt

1004#李白#libai@hotmail.com

1005#杜牧#dumu@hotmail.com

1006#杜甫#dufu@hotmail.com

1007#苏轼#sushi@hotmail.com

利用LOAD DATA INFILEE语句导入数据

mysql> load data infile 'f:\sql_users.txt' into table sql_users fields terminated by '#';

Query OK, 4 rows affected (0.00 sec)

Records: 4  Deleted: 0  Skipped: 0  Warnings: 0

mysql> select * from sql_users;

+------+------+--------------------+

| id   | name | email              |

+------+------+--------------------+

| 1004 | 李白 | libai@hotmail.com

| 1005 | 杜牧 | dumu@hotmail.com

| 1006 | 杜甫 | dufu@hotmail.com

| 1007 | 苏轼 | sushi@hotmail.com  |

+------+------+--------------------+

4 rows in set (0.00 sec)

 如果在导入数据时,遇到字符串无法识别时,一般都是字符集有问题,使用charset选项即可解决

mysql> load data infile 'f:\sql_users.txt' into table sql_users  fields terminated by '#';

ERROR 1366 (HY000): Incorrect string value: '\xC0\xEE\xB0\xD7' for column 'name' at row 1--------------------------------字符集不一样-----------------------mysql> load data infile 'f:\sql_users.txt' into table sql_users  character set gbk fields terminated by '#';

Query OK, 4 rows affected (0.03 sec)

Records: 4  Deleted: 0  Skipped: 0  Warnings: 0

LOAD DATA INFILE命令默认要导入数据存放在服务上,如果要导入客户端的数据,可以指定LOCAL,那么mysql将从客户端读取数据,这样的方式会比服务器上操作要慢一点,因为客户端的数据需要通过网络传输到服务器。

mysql> load data local infile 'f:\sql_users.txt' into table sql_users  fields terminated by '#';

如果需要忽略与主键值重复的记录值或者替换重复值,可以使用IGNORE或REPLACE选项,但是LOAD DATA INFILE命令语法中有两处IGNORE关键字,前面一个是用来此功能的,后面一个用来指定需要忽略的前N条记录。

wKiom1VG8e3jvNrVAAInfPim2Hc600.jpg

如果不想导入数据文件的前N行,使用IGNORE N LINES来处理

wKioL1VG84CjiNb_AAGOj2zGNhE857.jpg

如果在数据文件中记录行头有某些字符,又不想被导入,可以使用LINES STARTING BY来解决,但是如果某行记录不包含这些字符的话,那么这行记录也会被忽略。

wKiom1VG8jLAEhrBAAJO-qUkBb0151.jpg

数据文件为Excel文件的处理,首先将Excel文件保存为CSV格式,这样字段间都是用逗号隔开的,再进行处理。

wKioL1VG87_hEyfXAAJH9ojE6Ec208.jpg

数据文件列值中有特殊符号,使用enclosed by来处理。例如,列值中有分隔符

wKioL1VG893ya5IFAAI8hvPpk3E453.jpg

数据导入时换行符的问题,在上面的示例中,有几个数据导入到表中后,查询时结果显示有点别扭,不知大家注意到了没。

在Windows系统中,文本格式的换行符有"\r+\n"组成,而在linux系统中,换行符是"\n"。因此出出现上述问题,解决方法就是指定换行符LINES TERMINATED BY。

mysql> LOAD DATA INFILE 'F:\stu.csv' INTO TABLE stu CHARACTER SET GBK FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES;

表的列数多余数据文件中的列数,解决方法就是指定要导入到表的字段,如下所示

wKiom1VG8pPBmfY6AAKwc3y6i-E654.jpg

如果是表的列数少于数据文件中的列数呢,解决办法可以指定用户变量来接收多余的列值,如下

wKioL1VG9CKBG5RVAAI02wZwTwA553.jpg

如果表的列数与数据文件的不同,且某些字段类型都不一致,那怎么解决呢?方法如下:

------------------文本----------------------

PS F:\> MORE .\stu.csv

学号,姓名,班级

4010404,祝小贤,"A1012",20,male,信息学院

4010405,肖小杰,"A1013",22,female,外院

4010406,钟小喜,"A1014",24,male,会计学院

4010407,钟小惠,"A1015",26,female,商学院

--------------------处理-------------------------

mysql> desc stu;   //表结构

+--------+-------------+------+-----+---------+-------+

| Field  | Type        | Null | Key | Default | Extra |

+--------+-------------+------+-----+---------+-------+

| sno    | int(11)     | NO   | PRI | NULL    |       |

| sname  | varchar(30) | YES  |     | NULL    |       |

| class  | varchar(20) | YES  |     | NULL    |       |

| age    | int(11)     | YES  |     | NULL    |       |

| gender | tinyint(4)  | YES  |     | NULL    |       |

+--------+-------------+------+-----+---------+-------+

rows in set (0.01 sec)

mysql> LOAD DATA INFILE 'F:\stu.csv' INTO TABLE stu CHARACTER SET GBK FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TER

MINATED BY '\r\n' IGNORE 1 LINES (SNO,SNAME,CLASS,AGE,@GENDER,@x) SET GENDER=IF(@GENDER='MALE',1,0);

Query OK, 4 rows affected (0.09 sec)

Records: 4  Deleted: 0  Skipped: 0  Warnings: 0

mysql> SELECT * FROM STU;

+---------+--------+-------+------+--------+

| sno     | sname  | class | age  | gender |

+---------+--------+-------+------+--------+

| 4010404 | 祝小贤 | A1012 |   20 |      1 |

| 4010405 | 肖小杰 | A1013 |   22 |      0 |

| 4010406 | 钟小喜 | A1014 |   24 |      1 |

| 4010407 | 钟小惠 | A1015 |   26 |      0 |

+---------+--------+-------+------+--------+

rows in set (0.00 sec)

数据导出

数据导出比较简单,只要会SELECT ...INTO OUTFILE语句即可,例如

mysql> SELECT * FROM STU INTO OUTFILE "F:\stu_bak.txt"  CHARACTER SET GBK FIELDS TERMINATED BY '##' LINES TERMINATED BY'\r\n';

Query OK, 4 rows affected (0.00 sec)

-------------------------------stu_bak.txt-----------------------

PS F:\> MORE .\stu_bak.txt

4010404##祝小贤##A1012##20##1

4010405##肖小杰##A1013##22##0

4010406##钟小喜##A1014##24##1

4010407##钟小惠##A1015##26##0

还有一个SELECT...INTO DUMPFILE,这个语句也是将数据导出到文件,但是不能格式化语句,如FIELDS,LINES这些,它是将数据原汁原味输出到文件。但是只能输出一个记录,用处不大。

wKioL1Y6nrbRFtzqAADqcjpg4hg407.jpg

©著作权归作者所有:来自51CTO博客作者小强测试的原创作品,如需转载,请注明出处,否则将追究法律责任

mysql知识数据导入导出LANMP


点击查看更多内容
0人点赞

若觉得本文不错,就分享一下吧!

评论

相关文章推荐

正在加载中
手记
粉丝
88
获赞与收藏
567

关注TA,一起探索更多经验知识

作者相关文章
意见反馈 帮助中心 APP下载
官方微信

举报

0/150
提交
取消