MySQL大数据导入导出指南

通过MySQL官方提供的工具将老表的数据转到新表，由于数据量较大3000多万，关键词SELECt INTO OUTFILe，LOAD DATA INFILE

// 导出到txt语法
SELECT * INTO OUTFILe '/var/lib/user.txt'
  FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
  LINES TERMINATED BY 'n'
  FROM testdb.user;

查看权限

// 查看是否开启，可导出的文件目录
SHOW VARIABLES LIKE "secure_file_priv";
//查看可导入的文件目录
SHOW VARIABLES LIKE 'local_infile'; 

// 导出数据到txt
select * into outfile '/var/lib/mysql-files/t_user.txt' from testdb.t_user;

// 导入到数据库
load data local infile '/var/lib/mysql-files/t_user.txt' replace into table testdb.t_user;

MySQL官网链接

由于数据量太大全量查询磁盘内存消耗太高，选择存储过程按日期分段查询

drop procedure IF EXISTS insertDate;
delimiter $$  
create procedure `insertDate`(IN `beginDate` date,IN `endDate` date)
BEGIN
DECLARE nowdate date DEFAULT NOW();
DECLARE endtmp date DEFAULT NOW();
set nowdate = DATE_FORMAT(beginDate,'%Y%m%d');
set endtmp = DATE_FORMAT(endDate,'%Y%m%d');
WHILE nowdate=nowdate and create_date 
//存在唯一键则先删除后插入
REPLACe INTO
//存在唯一键则不插入
INSERT IGNORE
//DUPLICATE UPDAT：code唯一，如果已存在则更新 name
INSERT INTO table (name,code) VALUES($name,$code) ON DUPLICATE UPDATE name=$name;
 
查看数据库容量 
// 查看数据库容量
SELECT
table_schema AS '数据库',
table_name AS '表名',
table_rows AS '记录数',
TRUNCATE(data_length/1024/1024, 2) AS '数据容量(MB)',
TRUNCATE(index_length/1024/1024, 2) AS '索引容量(MB)'
FROM information_schema.tables
ORDER BY data_length DESC, index_length DESC;