1.语句格式
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}] ...]
参数顺序不能乱
-
local:在客户端执行load data命令,一定记得加上local参数
-
REPLACE | IGNORE:如果指定了,那么,当前的数据跟表中的数据有惟一性冲突的时候,采用什么样的方式,是替换已有还是忽略当前。特别需要说明的是,当这两种方式都未指定时,如果数据来自于客户端,则重复的数据会忽略,如果来源于服务端,则命令将终止执行。
-
[{FIELDS | COLUMNS}
[TERMINATED BY ‘string’] 字段间的分隔符
[[OPTIONALLY] ENCLOSED BY ‘char’] 字段值由什么符号包围
[ESCAPED BY ‘char’] 指定转义字符
]默认的字段分隔符是\t, 默认字段值无任何值包围,默认转义字符为.(反斜) -
[LINES [STARTING BY ‘string’] [TERMINATED BY ‘string’]]
指定每一行的起始符与终止符,默认情况下,起始符为空,终止符为’\n’,对于windows产生的文本文件来说,需要指定换行符为’\r\n’. -
IGNORE : 忽视前几行
-
[(col_name_or_user_var,…)],指定列名或者声明表达式。如(username,@var,description) set age=@var+1,意思把第一列数据给username,第二列数据给自定义变量@var,然后设置实际age的值为@var+1,入库的age数据就会是文件提供的数据+1。
2.使用示例
- 建测试表
CREATE DATABASE `load_test` CHARACTER SET 'utf8' COLLATE 'utf8_general_ci';
DROP TABLE IF EXISTS `user`;
CREATE TABLE user(
id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
username VARCHAR(20) NOT NULL,
age VARCHAR(10) NOT NULL,
description TEXT NOT NULL,
time datetime null,
PRIMARY KEY (id)
) ENGINE=MYISAM DEFAULT CHARSET=utf8
- 开启load data infile
show global variables like 'local_infile';
set global local_infile=1;
如果依然报错:ERROR 1148 (42000): The used command is not allowed with this MySQL version。需要在连接的时候设置allowLoadLocalInfile为true
- 导入txt
"张三",20","1111"
"李四","26","2222"
例如图上数据,语句就应该修改为:
load data local infile 'D:/mywork/loadDataInfile/test1.txt' into table user
CHARACTER SET utf8
fields terminated by ',' enclosed by '"'
lines terminated by '\r\n'
(username,age,description) set time=current_time;
CHARACTER SET utf8 使文件编码改为utf8
数据间用单引号隔开,数据被双引号包围,断行使用的是"\r\n"
(username,age,description)指明文件每一列数据对应的字段(或者是自定义变量)
set可以用做特殊字段处理,如添加时间
针对csv文件也是同样的参数设置,如果首行是属性名需要跳过ignore 1 lines。