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