MySQL 命令之 导入导出 SELECT ... INTO OUTFILE

SELECT ... INTO OUTFILE 导出数据

https://dev.mysql.com/doc/refman/8.0/en/select-into.html

SELECT...INTO OUTFILE 是 MySQL 中用于将查询结果导出到文件的语法。允许你将查询的结果写入一个文本文件。

语法

SELECT * FROM Table INTO OUTFILE '/tmp/employee_data_1.txt'  -- file_name 不能是已存在的文件，这样可以防止修改/etc/passwd 和数据库表等文件
FIELDS TERMINATED BY ','            -- FIELDS TERMINATED BY 用来指定字段值之间的符号，例如， “TERMINATED BY ‘,’”指定了逗号作为两个字段值之间的标志。
OPTIONALLY ENCLOSED BY '"'          -- ENCLOSED BY 用来指定包裹文件中字符值的符号，例如， “ENCLOSED BY ’ ” ‘”表示文件中字符值放在双引号之间，若加上关键字 OPTIONALLY 表示所有的值都放在双引号之间。
ESCAPED BY '\'                      -- ESCAPED BY 用来指定转义字符。 
LINES TERMINATED BY '\n';           -- LINES TERMINATED BY 在 LINES 子句中使用 TERMINATED BY 指定一行结束的标志，如“LINES TERMINATED BY ‘?’”表示一行以“?”作为结束标志

示例

SELECT id, name, email  
INTO OUTFILE '/tmp/user_data.csv'  
FIELDS TERMINATED BY ','    -- 指定了列之间的分隔符（逗号）
LINES TERMINATED BY '\\n'   -- 指定了行之间的分隔符（换行符）
FROM users;

SELECT ... INTO OUTFILE 注意事项

• SELECT...INTO OUTFILE 'file_name' 形式的 SELECT 可以把被选择的行写入一个文件中。该文件被创建到服务器主机上，因此您必须拥有FILE权限，才能使用此语法。
• 输出不能是一个已存在的文件。防止文件数据被篡改。
• 你需要有一个登陆服务器的账号来检索文件。否则 SELECT ... INTO OUTFILE 不会起任何作用。
• 在UNIX中，该文件被创建后是可读的，权限由MySQL服务器所拥有。这意味着，虽然你就可以读取该文件，但可能无法将其删除。

LOAD DATA INFILE 导入数据

https://dev.mysql.com/doc/refman/8.0/en/load-data.html

对 SELECT ... INTO OUTFILE 导出的文本文件，可以通过 LOAD DATA INFILE 命令导入到表里。LOAD DATA INFILE 是 SELECT ... INTO OUTFILE 的逆操作。

语法

LOAD DATA
    [LOW_PRIORITY | CONCURRENT] [LOCAL]         -- 如果指定 LOCAL 关键词，则表明从客户主机读文件。如果没指定 LOCAL，文件必须位于服务器上
    INFILE 'file_name'
    [REPLACE | IGNORE]                          -- replace 和 ignore 控制对现有的唯一键记录的重复的处理。如果指定 replace，新行将代替有相同的唯一键值的现有行。如果指定 ignore，跳过有唯一键的现有行的重复行的输入。如果不指定任何一个选项，当找到重复键时，出现一个错误，并且文本文件的余下部分被忽略
    INTO TABLE tbl_name
    [PARTITION (partition_name [, partition_name] ...)]
    [CHARACTER SET charset_name]
    [{FIELDS | COLUMNS}                         -- 字段相关
        [TERMINATED BY 'string']                -- ERMINATED BY 分隔符：意思是以什么字符作为分隔符 
        [[OPTIONALLY] ENCLOSED BY 'char']       -- 字段括起字符
        [ESCAPED BY 'char']                     -- ESCAPED BY 'char' 转义字符 
    ]
    [LINES                                      -- 行相关
        [STARTING BY 'string']
        [TERMINATED BY 'string']
    ]
    [IGNORE number {LINES | ROWS}]
    [(col_name_or_user_var
        [, col_name_or_user_var] ...)]
    [SET col_name={expr | DEFAULT}
        [, col_name={expr | DEFAULT}] ...]

示例

-- 导入过程中忽略错误，继续将符合条件的数据导入：
load data infile '/usr/local/mysql/backup/students.txt' ignore into table students fields terminated by ',' enclosed by '"' lines terminated by '\r\n';