MySQL导出csv格式数据
一、查看MySQL是否指定文件导出目录
root@localhost [(none)]>SELECT @@GLOBAL.secure_file_priv;
+---------------------------+
| @@GLOBAL.secure_file_priv |
+---------------------------+
| NULL |
+---------------------------+
1 row in set (0.00 sec)
MySQL默认不开启此文件目录，需要自己在my.cnf指定
[mysqld]
secure-file-priv=/data/mysql/data
增加目录后重启MySQL数据库，再次查看
root@localhost [(none)]>SELECT @@GLOBAL.secure_file_priv;
+---------------------------+
| @@GLOBAL.secure_file_priv |
+---------------------------+
| /data/mysql/data |
+---------------------------+
1 row in set (0.00 sec)
已经支持
二、使用mysqldump命令导出数据
mysqldump -uroot -p db-name --fields-enclosed-by='"' --fields-terminated-by=',' --lines-terminated-by='\r\n' --tab=/data/mysql/data
至此db-name下的所有表别被导出在/data/mysql/data目录下 也可指定表导致
mysqldump -uroot -p db-name table-name --fields-enclosed-by='"' --fields-terminated-by=',' --lines-terminated-by='\r\n' --tab=/data/mysql/data
把txt后缀的文件改为csv即可实现要求
# MySQL 导出指定字段的表的数据的SQL
mysql -p zx_sms -e "select concat('insert into sms_arrive(TASKID,MESSAGEID,CREATE_TIME) values(',TASKID,',\'',MESSAGEID, '\',\'',CREATE_TIME,'\');') from sms_arrive where id>100 INTO OUTFILE '/home/mysql/2.sql'"
cat 2.sql
insert into sms_arrive(TASKID,MESSAGEID,CREATE_TIME) values(171221155046284964,'cabf2200000bd77c','2017-12-21 15:50:56');
insert into sms_arrive(TASKID,MESSAGEID,CREATE_TIME) values(171221155046284955,'cabf2200000bd776','2017-12-21 15:50:56');
insert into sms_arrive(TASKID,MESSAGEID,CREATE_TIME) values(171221155046284992,'cabf2200000bd797','2017-12-21 15:50:56');
',TASKID,' 固定模式 数字
\'',MESSAGEID, '\' 固定模式字符串，中间，号分割
下面栗子
select concat('insert into sms_arrive(TASKID,MESSAGEID,CREATE_TIME,mdn,STAT_DESC) values(',TASKID,',\'',MESSAGEID, '\',\'',CREATE_TIME,'\',',mdn,',\'',STAT_DESC, '\');') from sms_arrive where id>100;
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| concat('insert into sms_arrive(TASKID,MESSAGEID,CREATE_TIME,mdn,STAT_DESC) values(',TASKID,',\'',MESSAGEID, '\',\'',CREATE_TIME,'\',',mdn,',\'',STAT_DESC, '\');') |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| insert into sms_arrive(TASKID,MESSAGEID,CREATE_TIME,mdn,STAT_DESC) values(181026201438247526,'ad50e980000b81cc','2018-10-26 20:14:59',13669760178,'DELIVRD'); |
| insert into sms_arrive(TASKID,MESSAGEID,CREATE_TIME,mdn,STAT_DESC) values(181026201439236998,'ad50e9c0000b81e8','2018-10-26 20:15:19',13988360680,'DELIVRD'); |
| insert into sms_arrive(TASKID,MESSAGEID,CREATE_TIME,mdn,STAT_DESC) values(181027044436247340,'ad92c940000bb89a','2018-10-27 04:45:01',13888646355,'DELIVRD'); |
| insert into sms_arrive(TASKID,MESSAGEID,CREATE_TIME,mdn,STAT_DESC) values(181120112938248140,'ba2dd980000b5d8f','2018-11-20 11:29:58',15887264467,'DELIVRD'); |
| insert into sms_arrive(TASKID,MESSAGEID,CREATE_TIME,mdn,STAT_DESC) values(181120112938248140,'ba2dd980000b5d90','2018-11-20 11:30:07',15887264467,'DELIVRD'); |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+
# END
