第一步 :设置文件
为了安全,配置导入导出的文件路径,导入导出的数据只允许放在该路径下
(1)在my.cnf 增加变量
secure_file_priv=/tmp/mysql_out_in_file
重启服务,使其生效
(2)加权限
-- 修改所属用户 用户组
chown -R mysql:mysql mysql /tmp/mysql_out_in_file
-- 如果没有读写权限,请加上读写权限
-- 权限没有设置好,或导出路径不一致,则会报错
ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statemen
(3)查看配置
show VARIABLES like '%secure_file_priv%'
第二步 :导出 select into outfile
(1)
mysql -u root -p
-- 输密码
use test;
-- 方式一
SELECT * FROMtest.demoINTO OUTFILE '/tmp/mysql_out_in_file/demo1.csv'FIELDS TERMINATEDBY ','OPTIONALLY ENCLOSEDBY '"'LINES TERMINATEDBY '\n';
-- 这样导出的数据,没有表头列名
-- 方式二
select * into outfile '/tmp/mysql_out_in_file/demo6.csv' -- 导出文件的存放路径fields terminatedby ',' -- 字段','(逗号)分割,optionally enclosedby '"' -- 字段以双引号括起来,lines terminatedby '\n' -- 换行符
from(select
'master_id','dd_date','seq_num','matched_rule_id','tran_amt1','alert_date' -- 拼表头
union(SELECTmaster_id,dd_date,seq_num,matched_rule_id,tran_amt1,alert_date -- 查询数据from test.demo limit 2000)
) ddd
-- 自己拼接表名
-- 注意点:select 后面查询的字段,必须包含主键所有字段,否则,导出的数据会缺失。
(2) 查看文件
执行完毕之后,目录已经有该文件
第三步 使用LOAD DATA INFILE 导入到另外一个表
先连接mysql
[root@localhost bin]# mysql -u root -p
如果字段包括时间字段,所有的时间字段都有值,正常导入,与源数据一致。
但如果时间字段为null, 则导入的数据会出现很多情况。
比如 dd_date 字段定义如下
"dd_date" timestamp(2) NOT NULL DEFAULT '0000-00-00 00:00:00.00'
demo6.csv ,该字段为空 null
第①种方式
LOAD DATA INFILE '/tmp/mysql_out_in_file/demo6.csv' INTO TABLEtest.demo_into
FIELDS TERMINATEDBY ','OPTIONALLY ENCLOSEDBY '"'LINES TERMINATEDBY '\n'IGNORE1lines -- 忽略首行
(master_id,dd_date,seq_num,matched_rule_id,tran_amt1,alert_date) -- 字段顺序与 文本demo6.csv 一致,调换字段顺序,意义不大
查看结果
第②种方式
dd_data 设置为null
LOAD DATA INFILE '/tmp/mysql_out_in_file/demo6.csv' INTO TABLEtest.demo_into
FIELDS TERMINATEDBY ','OPTIONALLY ENCLOSEDBY '"'LINES TERMINATEDBY '\n'IGNORE1lines
(master_id,@re,seq_num,matched_rule_id,tran_amt1,alert_date) -- @re 设置
查看结果
第③种 设置值
LOAD DATA INFILE '/tmp/mysql_out_in_file/demo6.csv' INTO TABLEtest.demo_into
FIELDS TERMINATEDBY ','OPTIONALLY ENCLOSEDBY '"'LINES TERMINATEDBY '\n'IGNORE1lines
(master_id,dd_date,seq_num,matched_rule_id,tran_amt1,alert_date)set dd_date = '1999-09-09 01:02:04'
-- 此方法,根据目前的测试,如果是100个字段,此方法,没有生效
查看结果
end
[root@commonTest bin]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.7.20 MySQL Community Server (GPL)
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> LOAD DATA INFILE '/data/backup/test.txt' INTO TABLE demo.test \
-> FIELDS TERMINATED BY ',' \
-> OPTIONALLY ENCLOSED BY '"' \
-> LINES TERMINATED BY '\n' ;
Query OK, 2 rows affected (0.00 sec)
Records: 2 Deleted: 0 Skipped: 0 Warnings: 0