本文共 7235 字，大约阅读时间需要 24 分钟。
从外面导入大量的数据到数据库中，比如文本，表格，如何快速导入导出?
load data:加载数据，高速方法。
outfile:导出数据
mysqlimport:导入导出
使用mysql里面导入导出，3个注意事项:
01.需要数据库能够识别一个目录: 就是在my.cnf文件中加一个参数。secure-file-priv=/mysql/dataload
service mysql restart
mkdir -p /mysql/dataload
chown -R mysql:mysql /mysql/dataload/
02.同时还要开启my. cnf自动数据提交:autocommit=1
03.解决乱码的问题:vi ~l.bash_profileexport LANG=en_us.UTF8
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}] ...]
如果指定这个参数，运行加载命令后，mysql将会等别人没有读这个表的时候，才加载数据。
只要满足条件，现在就加载数据。
表明从客户主机读文件，如果没有指定，则文件必须放在服务器上。
replace:新行替换表里面唯一键相同的行。ignore:跳过有唯一键的行，避免数据重复插入。
如果不指定，就会报错。除非没有唯一索引，就会重复插入。
指定文件的分隔的格式
terminated by 'string'，分隔符，是什么字符作为分隔符，比如: 1,2,3，默认情况用tab键(\t)
enclosed by ' char',字段括起来的字符，比如:"itpux01" , "itpux02"
escaped by 'char',转义字符,默认就是/
columns:指定哪些列，比如:表有9列，文件只有3列，那我要导入进哪些列。
指定了每条记录的分隔符默认"ln'即为换行符[lines
[starting by 'string'以什么开头
[terminated by 'string']以什么结尾，换行。
create table sexyg (
id int(20) not null auto_increment,
name varchar(20) not null,
age int(10) not null,
member varchar(80) not null,
grade varchar(20) not null,
primary key (id),
UNIQUE index idx_name (name)
) engine=innodb DEFAULT CHARSET utf8;
cat /mysql/dataload/sexyg.txt
"李慧如",22,"一级会员",80
"黄春涛",23,"一级会员",90
"高莹莹",27,"一级会员",75
load data infile '/mysql/dataload/sexyg.txt' ignore into table syj.sexyg character set utf8 fields terminated by ',' enclosed by '"' lines terminated by '\n' (`name`,`age`,`member`,`grade`);
可以把默认的lines terminated by '\n' 不用加。
load data infile '/mysql/dataload/sexyg.txt' ignore into table syj.sexyg character set utf8 fields terminated by ',' enclosed by '"' (`name`,`age`,`member`,`grade`);
alter table syj.sexyg add time timestamp not null;
mysql> load data infile '/mysql/dataload/sexyg.txt' ignore into table syj.sexyg character set utf8 fields ted by ',' enclosed by '"' (`name`,`age`,`member`,`grade`) set time=current_timestamp;
场景:某些数据文本比较大，1千万行数据，一个文件2G左右。这种情况导入的参数注意。
从文件大小上处理:
一个文件单线程导入比较慢，linux有一个split分离文件的命令，将文件切成100万行一10个小文件，然后启动10个线程，分别执行load data infile语句。
split使用方法:
-b 分离后的文件大小，byte
-C单行最大的大小，byte
-d 分离后的文件以数字为后缀
-l每一个文件存多少行
-a后缀的名字长度
案例:
按行数: split -l 1 sexyg.txt -d -a 3 sexyg.
按大小: split -b 10k sexyg.txt -d -a 3 sexyg
-rw-r--r-- 1 root root 99 3月 28 15:52 sexyg.txt
split -l 1 sexyg.txt -d -a 3 sexyg
-rw-r--r-- 1 root root 33 3月 28 16:44 sexyg000
-rw-r--r-- 1 root root 33 3月 28 16:44 sexyg001
-rw-r--r-- 1 root root 33 3月 28 16:44 sexyg002
-rw-r--r-- 1 root root 99 3月 28 15:52 sexyg.txt
load data infile '/mysql/dataload/sexyg000' ignore into table syj.sexyg character set utf8 fields terminated by ',' enclosed by '"' (`name`,`age`,`member`,`grade`) set time=current_timestamp;
show variabtes like 'innodb_log_% ' ;
innodbpfile_per_table=1
innodb_flush_method=O_DIRECT
innodb_flush_log_at_trx_commit=0
innodb_buffer_pool_size=20000M
innodb_lock_wait_timeout=1000
innodb_log_file_size=2048M
innodb_log_buffer_size=2048M
01.主要是改表的存储引擎，导入的时候改为myisam，导入后再改回innodb，否则的话1000w，可能导1个小时。
02.导入前修改参数和禁止索引更新
show variables like '%BUFFER_SIZE%';
bulk_insert_buffer_size=256217728
myisam_sort_buffer_size=256217728
key_buffer_size=256217728
alter table 表名disable keys;
03.再导入数据
04.alter table表名enable keys;.'
select name,age,grade,time into outfile '/mysql/dataload/outsex1.txt' from syj.sexyg where id>36;
Query OK, 2 rows affected (0.01 sec)
cat /mysql/dataload/outsex1.txt
黄春涛 23 90 2021-03-28 16:45:11
高莹莹 27 75 2021-03-28 16:45:11
select name,age,grade,time into outfile '/mysql/dataload/outsex2.txt' fields terminated by ',' enclosed by'"' lines terminated by '\n' from syj.sexyg where id>36;
cat /mysql/dataload/outsex2.txt
"黄春涛","23","90","2021-03-28 16:45:11"
"高莹莹","27","75","2021-03-28 16:45:11"
create table zd (
id int(200) not null,
name varchar(20) not null,
date datetime not null,
expend int(100) not null,
income int(100) not null,
store varchar(30) not null,
primary key (id)
) engine=innodb DEFAULT CHARSET utf8;
load data infile '/mysql/dataload/zd.csv' ignore into table syj.zd character set utf8 fields terminated by ',,' enclosed by '"' lines terminated by '\n' ignore 1 lines;
ERROR 1300 (HY000): Invalid utf8 character string: ''
load data infile '/mysql/dataload/zd.csv' ignore into table syj.zd character set utf8 fields terminated by ',' enclosed by '"' lines terminated by '\n' ignore 1 lines (id,name,date,expend,income,store);
ERROR 1300 (HY000): Invalid utf8 character string: ''
如果报错，肯定是字符集有问题;。
ERROR 1300 (HY000): Invalid utf8ccharacter string: "'以源字符集导入就能解决向题:
load data infile '/mysql/dataload/zd.csv' ignore into table syj.zd character set gbk fields terminated by ','' enclosed by '"' lines terminated by '\n' ignore 1 lines (id,name,date,expend,income,store);
Query OK, 99 rows affected (0.01 sec)
Records: 99 Deleted: 0 Skipped: 0 Warnings: 0
select * into outfile '/mysql/dataload/zdout14.csv' fields terminated by ',' optionally enclosed by'"' liness terminated by '\n' from syj.zd where id<1234567961;
mysqlimport工具，跟load data一样的功能。从文本/表格里面导入到数据库中，两个方法不参数差不多。
语法:
mysqlimport -uroot -ppassword dbname filename.txt [option]
--local:指定的话就从当前主机上找文件，不指定就从服务器上找文件
--fields-enclosed-by:设置字符来把字段的值括起来'"'
--fields-escaped-by:转义符
--fields-optionally-enclosed-by·设置字符括住 char, varchar,text这些字符型字段。
--fields-termingted-by:分隔符 ','
--lines-terminated-by;每行数据结尾的字符
--ignore-lines:忽略多少行
--ignore:如果有相同的数据，针对唯一索引的忽略，不用插入重复的数据
--replace:如果有相同的数据，针对唯一索引的先替换再插入，不用插入重复的数据
--columns:针对导入表的列
mysqlimport -uroot -proot syj /mysql/dataload/sexyg.txt --fields-terminated-by=',' --fields-escaped-by='"' lines-terminated-by='\n';
mysqlimport: [Warning] Using a password on the command line interface can be insecure.
mysqlimport: Error: 1366, Incorrect integer value: '\xE6\x9D\x8E\xE6\x85\xA7\xE5\xA6\x82,22' for column 'id' at row 1, when using table: sexyg
mysqlimport -uroot -proot syj /mysql/dataload/sexyg.txt --fields-terminated-by=',' --fields-enclosed-by='"' lines-terminated-by='\n' --columns=name,age,member,grade;
mysqlimport: [Warning] Using a password on the command line interface can be insecure.
syj.sexyg: Records: 3 Deleted: 0 Skipped: 0 Warnings: 0
mysqlimport -uroot -proot syj /mysql/dataload/zd.csv --fields-terminated-by=',' --fields-enclosed-by='"' --ignore-lines=1 --columns=id,name,date,expend,income,store --default-character-set=utf8;
mysqlimport: [Warning] Using a password on the command line interface can be insecure.
syj.zd: Records: 98 Deleted: 0 Skipped: 0 Warnings: 0
转载地址：http://labai.baihongyu.com/