MySQL数据库迁移快速导出导入大量数据

一：导出语句（INTO OUTFILE）

SELECT * FROM ${tabName} 
INTO OUTFILE 'C:\\ProgramData\\MySQL\\MySQL Server 5.7\\Uploads\\${tabName}.csv' 
FIELDS TERMINATED BY ',' 
OPTIONALLY ENCLOSED BY '"' 
LINES TERMINATED BY '\r\n';

二：导入语句（LOAD DATA INFILE）
1、语法

LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name.txt'
    [REPLACE | IGNORE]
    INTO TABLE tbl_name
    [FIELDS
        [TERMINATED BY 'string']
        [[OPTIONALLY] ENCLOSED BY 'char']
        [ESCAPED BY 'char' ]
    ]
    [LINES
        [STARTING BY 'string']
        [TERMINATED BY 'string']
    ]
    [IGNORE number LINES]
    [(col_name_or_user_var,...)]
    [SET col_name = expr,...)]

2、示例

LOAD DATA INFILE 'C:\\ProgramData\\MySQL\\MySQL Server 5.7\\Uploads\\${tabName}.csv' 
INTO TABLE ${tabName} 
CHARACTER SET utf8
FIELDS TERMINATED BY ',' 
OPTIONALLY ENCLOSED BY '"' 
LINES TERMINATED BY '\r\n';

3、只载入一个表的部分列

LOAD DATA LOCAL INFILE 'C:\\ProgramData\\MySQL\\MySQL Server 5.7\\Uploads\\${tabName}.csv' INTO TABLE ${tabName}(col1,col2)

参数：
FIELDS TERMINATED BY ',' ：字段分隔符号

OPTIONALLY ENCLOSED BY '"' ：字段包围符号

LINES TERMINATED BY '\r\n' ：记录分隔符号

备注：
1、导入语句的LOCAL 关键字
当文本文件在本地，需要载入到远程mysql，可以增加此关键字
2、有个小坑、、、2700w数据导入以后发现乱码了，csv文件转码以后还是乱码，最后在INTO TABLE ${tabName} 后加上character set utf8，就不乱码了