Mysql快速导入导出数据

╰+哭是因爲堅強的太久メ 2022-05-26 12:40 339阅读 0赞

使用load data infile filename和select \* from into outfile filename实现快速导入导出数据

一、load data infile的语法结构如下：

LOAD DATA [ LOW_PRIORITY | CONCURRENT ] [ LOCAL ] INFILE 'file_name.txt'
        [ REPLACE | IGNORE ]    
        INTO TABLE tbl_name
        [ FIELDS
          [ TERMINATED BY 'string' ]
          [ [OPTIONALLY] ENCLOSED BY 'char' ]
          [ ESCAPED BY 'char'  ]
        ]
        [ LINES 
          [ STARTING BY 'string' ]
          [ TERMINATED BY 'string' ]
        ]
       [ IGNORE number LINES ]
       [ (col_name_or_user_var,...) ]
       [ SET col_name = expr,...] ]

其中LOW\_PRIORITY：低优先级执行，不能同时读写，适用于表锁存储引擎，比如MyISAM，MEMORY，MERGE，如果写入数据过程中有程序读取该表的数据，写入过程就会中断，直至没有任何客户端程序读表才继续写入。

CONCURRENT：读写并行，在有其他程序读取该表数据的情况下写入数据

LOCAL：在客户端导入的文件和数据库服务器不在同一机器上时需要加上该标识，否则导入数据会失败。

file\_name：需要导入的文件名

REPLACE：当输入的行与已存在的行主键重复或者唯一索引重复时覆盖导入

IGNORE：当输入的行与已存在的行主键重复或者唯一索引重复时不导入

如果REPLACE和IGNORE都未指定，行为取决于是否指定了LOCAL关键字，如果指定了，发生主键重复就发出警告相当于IGNORE，如果没指定LOCAL发生主键重复就会终止操作。

FIELDS 和LINES：FIELDS和 LINES都是可选的，但同时出现时FIELDS语句要放在LINES语句的前面。如果指定了FIELDS选项，它的子句(TERMINATED BY, \[OPTIONALLY\] ENCLOSED BY, 和ESCAPED BY)必须指定至少一项。如果没有指定FIELDS和LINES，它们的默认值是：

FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\'   
    LINES TERMINATED BY '\n' STARTING BY ''

FIELDS ESCAPEDBY：指定字符非空，则输入时该字符被移除，后续的内容被添加到字段里。

LINES STARTINGBY 'prefix\_string'：如果所有读入的行都含有一个希望忽略的共同前缀，则可以使用'prefix\_string'来忽略前缀（包括前缀前的所有字符）。如果某行不包括前缀，则整个行被跳过

IGNOREnumber LINES：在导入的文件开始处忽略多少行。一般用于过滤掉表头行

CHARACTERSET：如果文件中的字符编码与默认不一致，最好用CHARACTER SET关键字指定编码方式，避免导入数据出现乱码

下面是示例：

1、导入数据库的文件和数据库在同一台机器上时：

load data infile '/root/aa.csv' into table test.test 
    CHARACTER SET gbk 
    FIELDS TERMINATED BY ',' optionally enclosed by '\"' escaped by '\"' 
    LINES TERMINATED BY '\r\n' 
    ignore 1 lines

不需要指定LOCAL关键字，如果导入的数据包含中文最好设置字符集是GBK，设置字段分隔符，这里的分隔符前面加了反斜杠”\\”这是因为服务器上存在不识别双引号的情况，设置换行符'\\r\\n' ，最后是忽略第一行的表头，不需要导入数据库。

2、导入的文件和数据库服务器不在同一台机器上：

#mysql –u服务器用户名 –p服务器密码 –h服务器IP 数据库用户名
    mysql -uroot -proot -h192.168.1.102 test -e 
    "load data local infile '/root/aa.csv' into table test.test 
    CHARACTER SET gbk 
    FIELDS TERMINATED BY ',' optionally enclosed by '\"' escaped by '\"' 
    LINES TERMINATED BY '\r\n' 
    ignore 1 lines"

与第一种情况基本相同，只有Local关键字必须要指定。

错误1：

ERROR 1083 (42000) at line 1: Field separator argument is not what is expected; check the manual

这是双引号不识别导致的，需要在前面加反斜杠转义

![20180502190245675][]

错误2：

ERROR 29 (HY000) at line 1: File '\root\aa.csv' not found (Errcode: 2)

这是没有加LOCAL导致的

![20180502190313413][]

如果输入文件与表的列顺序不同，我们必须指定一个列清单，否则mysql不能把输入文件的字段与表的列匹配起来。

二、SELECT ... INTO OUTFILE

SELECT ... FROM TABLE_NAME  INTO OUTFILE "FILENAME" FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n';

当我们使用SELECT ... INTO OUTFILE从数据库导出数据到文件，然后通过LOADDATA INFILE读取该文件到数据库，这两个语句的field和lines选项必须匹配，否则 LOAD DATA INFILE将不能正确的解释文件的内容。

SELECT ... INTO OUTFILE导出文件时可以通过参数--default-character-set指定导出文件的编码格式。

对于SELECT ... INTO OUTFILE …FIELDS \[OPTIONALLY\] ENCLOSED BY输出，如果不包含OPTIONALLY选项，所有的字段将会被ENCLOSED BY指定的字符包裹。如果我们指定OPTIONALLY，只有string数据类型（如 CHAR, BINARY, TEXT, 或 ENUM）的字段才会被ENCLOSED BY指定的字符包裹

测试：

mysql -uroot -proot -h192.168.1.102 test -e 
    "SELECT * FROM TEST.TEST INTO OUTFILE 'C:\\Users\\Administrator\\Desktop\\test.csv' 
    FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"' 
    LINES TERMINATED BY '\r\n'"

失败，导出文件到其他服务器无法执行，需要导出到数据库所在服务器才行，如果想要导出数据到不同机器，可以考虑使用mysqldump命令。

![2018050219041983][]

SELECT * FROM TEST.TEST INTO OUTFILE 'C:\\Users\\Administrator\\Desktop\\test.csv' 
    FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"' 
    LINES TERMINATED BY '\r\n'

![20180502190442458][]

Windows操作系统的换行是'\\r\\n'，而Linux系统的换行是'\\n'，要注意导出的系统。

如果是在Linux系统执行INTO FILE导出命令报：

ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

这是因为mysql设置了安全路径，可以通过修改mysql中配置的安全路径，或者将文件导出到安全路径解决问题。

![20180502190518852][]

[20180502190245675]: /images/20220526/70d2327afa294c8594ccfb3966adbdaf.png
[20180502190313413]: /images/20220526/550fa84c9c1841e190be98ad21a59a7b.png
[2018050219041983]: /images/20220526/3e2f69d1c7254d5591f58e2c87c6b5e5.png
[20180502190442458]: /images/20220526/b78bf802fa7a41a2a4d291370db760ac.png
[20180502190518852]: /images/20220526/637bfbdf399447059ce3284aff224bf0.png