mysql load data 与 select into outfile 配合实现文本文件导入导出

 

mysql数据导入导出通过下面的这种方式能够快速的实现百万千万级数据的快速导入导出。

• 导出到文件中（select into outfile）

SELECT fields INTO OUTFILE 'file_name' 
    [{FIELDS | COLUMNS}                     字段
        [TERMINATED BY 'string']                字段之间分隔符号
        [[OPTIONALLY] ENCLOSED BY 'char']       字段被包含在char中间
        [ESCAPED BY 'char']                     忽略字段里出现的char
    ]
    [LINES
        [STARTING BY 'string']              忽略开头是string的行
        [TERMINATED BY 'string']            行分隔符
    ]
FROM test_table;

导出：

secure_file_priv配置项

如果配置了这个，则只能导入到该目录下面。可以去改配置项，然后重启

 改为“”即可，改成null会导致不能导出。

https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_secure_file_priv

• 导入文件中的数据到mysql表

LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name'
    [REPLACE | IGNORE]                      遇到重复的时候处理方法，替换或者是忽略
    INTO TABLE tbl_name                     导入数据的目的表名
    [PARTITION (partition_name,...)]        分区选择
    [CHARACTER SET charset_name]            字符集
    [{FIELDS | COLUMNS}                     字段
        [TERMINATED BY 'string']                字段之间分隔符号
        [[OPTIONALLY] ENCLOSED BY 'char']       字段被包含在char中间
        [ESCAPED BY 'char']                     忽略字段里出现的char
    ]
    [LINES
        [STARTING BY 'string']              忽略开头是string的行
        [TERMINATED BY 'string']            行分隔符
    ]
    [IGNORE number {LINES | ROWS}]          忽略行/列
    [(col_name_or_user_var,...)]            目的表的表字段名或者用户变量名
    [SET col_name = expr,...]               设置表字段值

---

实例：

导入数据到数据库

建一个简单的用户表：

CREATE TABLE `user` (
`id` int(11) NOT NULL,
`name` varchar(50) NOT NULL DEFAULT '',
`age` int(11) NOT NULL DEFAULT '0',
`create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '创建时间',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

建立一个user.csv文件，数据如下;

文本如下：

假设这个里面有很多很多用户，比方说20条。。。

LOAD DATA LOCAL INFILE 'D:\\test\\user.csv' INTO TABLE `user` FIELDS TERMINATED BY ',' ENCLOSED BY '"' IGNORE 1 LINES (`name`,@dummy, age) 

将表里面的第一列用户名和第三列年龄导入到数据库中去，中间的中文名我们不关心，不要。第一行是列名，不要。

PS:@dummy是看官方文档给的，就是个普通变量，给赋值，但是没用到，帮我们实现了忽略列的作用。本身没提供忽略列的功能。

插入成功。

参考：

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