MySQL select into outfile 导出和 load data 的使用

select into outfile

是什么

导出数据到以文本文件形式存在本地上

语法

SELECT ... INTO OUTFILE 'file_name'
        [CHARACTER SET charset_name]
        [export_options]
 
export_options:
    [{FIELDS | COLUMNS}
        [TERMINATED BY 'string']
        [[OPTIONALLY] ENCLOSED BY 'char']
        [ESCAPED BY 'char']
    ]
    [LINES
        [STARTING BY 'string']
        [TERMINATED BY 'string']
    ]

导出示例

SELECT * FROM sys_log 
INTO OUTFILE "C:/Users/Administrator/Desktop/202012/2020-12-07.txt"  -- 文件夹必须存在
CHARACTER SET utf8 -- 可选，避免中文乱码问题
FIELDS TERMINATED BY '||' -- 字段分隔符，每个字段(列)以什么字符分隔，默认是 \t
OPTIONALLY ENCLOSED BY '' -- 文本限定符，每个字段被什么字符包围，默认是空字符
ESCAPED BY '\\' -- 转义符，默认是 \
LINES TERMINATED BY '\n' -- 记录分隔符，如字段本身也含\n，那么应先去除，否则load data 会误将其视作另一行记录进行导入

注意事项

在 Linux 上有可能会没有权限，要注意得有权限才可以

load data

是什么

• MySQL load data 语句能快速将一个文本文件的内容导入到对应的数据库表中（一般文本的一行对应表的一条记录）。
• 数据库应用程序开发中，涉及大批量数据需要插入时，使用 load data 语句的效率比一般的 insert 语句的高很多。

语法

LOAD DATA
    [LOW_PRIORITY | CONCURRENT] [LOCAL]
    INFILE 'file_name'
    [REPLACE | IGNORE]
    INTO TABLE tbl_name
    [PARTITION (partition_name [, partition_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
        [, col_name_or_user_var] ...)]
    [SET col_name={expr | DEFAULT},
        [, col_name={expr | DEFAULT}] ...]

导入示例

LOAD DATA LOCAL INFILE 'C:/Users/Administrator/Desktop/202012/2020-12-07.txt' INTO TABLE sys_log
CHARACTER SET utf8 -- 可选，避免中文乱码问题
FIELDS TERMINATED BY '||' -- 字段分隔符，每个字段(列)以什么字符分隔，默认是 \t
OPTIONALLY ENCLOSED BY '' -- 文本限定符，每个字段被什么字符包围，默认是空字符
ESCAPED BY '\\' -- 转义符，默认是 \
LINES TERMINATED BY '\n' -- 记录分隔符，如字段本身也含\n，那么应先去除，否则load data 会误将其视作另一行记录进行导入
(`id`,`log_type`,`log_content`,`operate_type`,`userid`,`username`,`ip`,`method`,`request_url`,`request_param`,`request_type`,`cost_time`,`create_by`,`create_time`,`update_by`,`update_time`) -- 每一行文本按顺序对应的表字段，建议不要省略

注意事项

• 字段中的空值 null 需要使用 \N 表示，如果用空字符串表示，那么根据不同的数据类型，MySQL 处理也各异

• 数据库字段如果是varchar/char，插入空时，load data 默认导入 空字符串
• 数据库字段如果是decimal，插入空时，load data 默认导入 0.00000000
• 数据库字段如果是datetime，插入空时，load data 默认导入 0000-00-00 00:00:00

• 字段类型如果是datetime，应该严格把控相应文本数据的格式，建议采用类似这种 yyyy-MM-dd HH:mm:ss 同时有日期、时间的格式，否则难以保证数据导入的正确性

• 数据库字段如果是datetime，插入yyyy-MM-dd时，load data 默认导入 yyyy-MM-dd 00:00:00，数据正确性能够保证
• 数据库字段如果是datetime，插入HH:mm:ss时，load data 默认导入 0000-00-00 00:00:00，数据正确性不能够保证

• local关键字

如果 load data 使用时指定了 local 关键字，则表示文件放在客户端主机上，从客户端读取文本文件；如果没指定，则表示从服务器主机读取文本文件

• replace 和 ignore 关键字

• replace 和 ignore 关键字用于控制与唯一键重复的记录的处理
• 如果指定 replace ，与唯一键重复的行将被覆盖更新。对于任意记录覆盖更新时，如果唯一键外的各个字段其实都没有变化，那么执行操作时受影响行数为1；如果除唯一键外的任意字段有变化，那么执行操作时受影响行数为2
• 如果指定 ignore ，与唯一键重复的行将被忽略，默认指定 ignore

参考1
参考2
参考3