mysql导出数据到csv的方式

爱被打了一巴掌 2024-02-19 12:46 50阅读 0赞

注：

MYSQL导入数据出现The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

这个原因其实很简单，是因为在安装MySQL的时候限制了导入与导出的目录权限

只能在规定的目录下才能导入

我们需要通过下面命令查看 secure-file-priv 当前的值是什么

mysql>

一、导出到csv

通过mysql客户端shell连接到服务器，选择使用的数据库，输入sql代码：

select \* from test\_info

into outfile '/tmp/test.csv'

fields terminated by ','　　　 ------字段间以,号分隔

optionally enclosed by '"'　　------字段用"号括起

escaped by '"' 　　　　　　------字段中使用的转义符为"

lines terminated by '\\r\\n';　　------行以\\r\\n结束

上面的 导出文件夹 需要手动创建，否则会报错：ERROR 1 (HY000): Can't create/write to file '\\tmp\\DKX.csv' (Errcode: 2)

select \* from test\_info into outfile '/tmp/test.csv' fields terminated by ',' optionally enclosed by '"' escaped by '"' lines terminated by '\\r\\n';

![761451-20170621101121913-1207412079.png][]

注意：

where gscode = 'BS监控' 有中文，导不出数据。

解决方法：

where gscode like 'BS%'

![761451-20171017165620912-1553230236.png][]

二、csv文件导入

load data infile '/tmp/test.csv'

into table test\_info

fields terminated by ','

optionally enclosed by '"'

escaped by '"'

lines terminated by '\\r\\n';

\#insert

load data infile '/tmp/test.csv' into table test\_info fields terminated by ',' optionally enclosed by '"' escaped by '"' lines terminated by '\\r\\n';

\#replace

load data infile '/tmp/test.csv' repalce into table test\_info fields terminated by ',' optionally enclosed by '"' escaped by '"' lines terminated by '\\r\\n';

场景：表1中的数据需要导入表2（表1、2结构相同，都有自增id字段）。表1中的自增id字段不要导出，让其在数据导入表2时自动生成，避免可能出现重复的自增id。

导出：

![761451-20170627100728711-662929664.png][]

导入：

![761451-20170627100749618-925906959.png][]

注意：

如果是远程连接的数据库，导出、导入时可能报错：ERROR 1045 (28000): Access denied for user 'quantuser'@'%' (using password: YES)

![761451-20170830110623077-1690066497.png][]

![761451-20170629155132696-1240124245.png][]

解决方法：

导出：(-N 不导出标题行)

mysql -h host -u user --password=pwd dbname -N -e "select \* from table" > D:/tmp/test.csv

![761451-20170830110945062-260139385.png][]

导入：

用 load data local infile 'XXX.csv' （如果指定LOCAL关键词，从客户主机读文件。如果LOCAL没指定，文件必须位于服务器上。(LOCAL在MySQL3.22.6或以后版本中可用。））

![761451-20170830110751999-1502726047.png][]

三、把从mongodb 中导出的csv文件，导入到 mysql

导出：

![761451-20171030152112074-544375836.png][]

导入mysql:

![761451-20171030152441011-871906873.png][]

效果：

![761451-20171030152309965-10429002.png][]

![761451-20171030152328949-1326354536.png][]

注意：

导入、导出的两个mysql 的数据库属性要一致，否则导入数据之后，中文字段是乱码的。

![761451-20171030153955652-1518660754.png][]

四、常用

mysql -h ip -uroot -p -Bse "SELECT \* FROM \_TABLE" | sed 's/\\t/","/g;s/^/"/;s/$/"/;s/\\n//g' > \_FILE\_NAME.csv

select \* from \_TABLE into outfile '\_FILE\_NAME.csv' fields terminated by ',' optionally enclosed by '\\"' escaped by '\\"';"

fields terminated by ','　　　 ------字段间以,号分隔

optionally enclosed by '"'　　------字段用"号括起

escaped by '"' 　　　　　　------字段中使用的转义符为"

lines terminated by '\\r\\n';　　------行以\\r\\n结束

[761451-20170621101121913-1207412079.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/8a5cea185d0b49839c005845ad6ebbab.png
[761451-20171017165620912-1553230236.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/cef28a70b98d44d3b86ffd9176a67f25.png
[761451-20170627100728711-662929664.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/03ce6a87f17449858c83088eb8ea39b0.png
[761451-20170627100749618-925906959.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/cef5d0e0831c42fd80be6af4c2145c06.png
[761451-20170830110623077-1690066497.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/6cba13412a5b499799ff59cf3b23e8aa.png
[761451-20170629155132696-1240124245.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/f9a4681ad0864330928750bdb8759f3d.png
[761451-20170830110945062-260139385.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/3b2ea4b4183b470fa095113b806fac6f.png
[761451-20170830110751999-1502726047.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/85477405dfac42719db858dbc9fb4817.png
[761451-20171030152112074-544375836.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/396c1f72193447278f27ad6651017d2e.png
[761451-20171030152441011-871906873.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/927e5ca5e2054f818e6626bcdd1f4a09.png
[761451-20171030152309965-10429002.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/8d1874f41f2c4a60bf197c64c9f87949.png
[761451-20171030152328949-1326354536.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/b7b556d315bd4b81a9cf09f522a21eaa.png
[761451-20171030153955652-1518660754.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/00bbb77c1faa4985a032df0984f19168.png