MySQL快速导入文件LOAD DATA INFILE

MySQL导入文本数据LOAD DATA INFILE语法

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}] ...]

** FIELDS参数含义：** TERMINATED BY ',' //描述字段的分隔符，默认情况下是tab字符（\t） ENCLOSED BY '"' //描述的是字段的括起字符，比方以双引号括起每一个字段 ESCAPED BY '\' //描述的转义字符，默认的是反些杠（backslash：\ ）

** LINES参数含义：** TERMINATED BY '\n' // 描述行分隔符 STARTING BY '' // 行前缀，只加载该前缀开头的数据

实例：

mysql>LOAD DATA INFILE "${value}" INTO TABLE databases.table_name;

注意${value值查找方法查找LOAD DATA INFILE文件存放目录}

//LOCAL参数表示文件是本地的文件，服务器是远程已经登陆的服务器
mysql>LOAD DATA LOCAL INFILE "${value}" INTO TABLE databases.table_name;

//设置插入语句的优先级（LOW_PRIORITY 低优先级），LOAD DATA语句的执行将会被延迟，直到没有其它的客户端读取表
mysql>LOAD DATA LOW_PRIORITY INFILE "${value}" INTO TABLE databases.table_name;

//插入数据的时候，忽略文件与数据表中重复的键值
mysql>LOAD DATA LOW_PRIORITY INFILE "${value}" IGNORE INTO TABLE databases.table_name;

//插入数据的时候，替代文件与数据表中重复的键值
mysql>LOAD DATA LOW_PRIORITY INFILE "${value}" REPLACE INTO TABLE databases.table_name;

//字段以逗号分割，字段内容用双引号标记开始和结束
mysql>LOAD DATA INFILE "${value}" INTO TABLE databases.table_name FIELDS TERMINATED BY ',' ENCLOSED BY '"';

//按指定的列把文件导入到数据库中
mysql>LOAD DATA INFILE "${value}" INTO TABLE databases.table_name (column1, column2, column3);

测试：

导出数据到文件：

SELECT * INTO OUTFILE "C:/ProgramData/MySQL/MySQL Server 5.7/Uploads/test.sql" FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY'"' LINES TERMINATED BY '\n' FROM user ;

从文件中加载数据

LOAD DATA INFILE 'C:/ProgramData/MySQL/MySQL Server 5.7/Uploads/test.sql' REPLACE INTO TABLE test.ouser FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' ;