loadDataInfile一看就通

最新推荐文章于 2024-04-30 04:40:42 发布

大大丶大春哥

最新推荐文章于 2024-04-30 04:40:42 发布

阅读量511

点赞数 7

分类专栏：数据库文章标签： mysql

本文链接：https://blog.csdn.net/weixin_44313366/article/details/134592201

版权

数据库专栏收录该内容

6 篇文章 0 订阅

订阅专栏

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。