MySQL数据库怎么用命令行导出带表头与不带表头的csv文件
发布时间：2022-01-21 07:26:57 所属栏目：MySql教程 来源：互联网
导读：本篇内容介绍了MySQL数据库怎么用命令行导出带表头和不带表头的csv文件的有关知识，在实际案例的操作过程中，不少人都会遇到这样的困境，接下来就让小编带领大家学习一下如何处理这些情况吧！希望大家仔细阅读，能够学有所成！ 实验如下: 建表： mysql CREAT
|
本篇内容介绍了“MySQL数据库怎么用命令行导出带表头和不带表头的csv文件”的有关知识，在实际案例的操作过程中，不少人都会遇到这样的困境，接下来就让小编带领大家学习一下如何处理这些情况吧！希望大家仔细阅读，能够学有所成！ 实验如下: 建表： mysql> CREATE TABLE `test` ( -> `id` varchar(64) NOT NULL, -> `ecode` varchar(10) DEFAULT NULL, -> `type` varchar(12) DEFAULT NULL, -> `timeid` varchar(12) DEFAULT NULL, -> `start_time` date DEFAULT NULL, -> `end_time` varchar(12) DEFAULT NULL, -> PRIMARY KEY (`id`), -> KEY `start` (`start_time`), -> KEY `end` (`end_time`) -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8; Query OK, 0 rows affected (0.35 sec) 不带表头: mysql> select * from test into outfile 'd:test.csv' fields terminated by ','enclosed by '"'lines terminated by 'rn'; ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement 导出报错，原因是5.7版本对mysqld 的导入导出做限制，解决办法： 在my.ini中加上 [mysqld] secure_file_priv='' 重启数据库使配置生效 PS C:WINDOWSsystem32> net stop mysql MySQL 服务正在停止. MySQL 服务已成功停止。 PS C:WINDOWSsystem32> net start mysql MySQL 服务正在启动 .. MySQL 服务已经启动成功。 再次运行命令成功: mysql> select * from test into outfile "d:/test.csv" fields terminated by ','enclosed by '"'lines terminated by 'rn'; Query OK, 1412 rows affected (0.00 sec) 用Notepad++打开文件发现没表头： "00000c-month-20161","00000c","month","20161","2015-12-29","2016-01-28" "00000c-month-201610","00000c","month","201610","2016-09-29","2016-10-28" "00000c-month-201611","00000c","month","201611","2016-10-29","2016-11-28" "00000c-month-201612","00000c","month","201612","2016-11-29","2016-12-28" "00000c-month-20162","00000c","month","20162","2016-01-29","2016-02-28" "00000c-month-20163","00000c","month","20163","2016-02-29","2016-03-28" "00000c-month-20164","00000c","month","20164","2016-03-29","2016-04-28" "00000c-month-20165","00000c","month","20165","2016-04-29","2016-05-28" "00000c-month-20166","00000c","month","20166","2016-05-29","2016-06-28" "00000c-month-20167","00000c","month","20167","2016-06-29","2016-07-28" "00000c-month-20168","00000c","month","20168","2016-07-29","2016-08-28" "00000c-month-20169","00000c","month","20169","2016-08-29","2016-09-28" "00000c-month-20171","00000c","month","20171","2016-12-29","2017-01-28" "00000c-month-201710","00000c","month","201710","2017-09-29","2017-10-28" "00000c-month-201711","00000c","month","201711","2017-10-29","2017-11-28" ......................................................................... ......................................................................... 查看表结构: mysql> desc test; +------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------+-------------+------+-----+---------+-------+ | id | varchar(64) | NO | PRI | NULL | | | ecode | varchar(10) | YES | | NULL | | | type | varchar(12) | YES | | NULL | | | timeid | varchar(12) | YES | | NULL | | | start_time | date | YES | MUL | NULL | | | end_time | varchar(12) | YES | MUL | NULL | | +------------+-------------+------+-----+---------+-------+ 6 rows in set (0.00 sec) 带表头导出csv: mysql> select * from (select 'id','ecode','type','timeid','start_time','end_time' union all select id,ecode,type,timeid,start_time,end_time from test) b into outfile "d:/test.csv" fields terminated by ','enclosed by '"'lines terminated by 'rn'; Query OK, 1413 rows affected (0.01 sec) 用Notepad++打开文件发现带表头： "id","ecode","type","timeid","start_time","end_time" "00000c-month-20161","00000c","month","20161","2015-12-29","2016-01-28" "00000c-month-201610","00000c","month","201610","2016-09-29","2016-10-28" "00000c-month-201611","00000c","month","201611","2016-10-29","2016-11-28" "00000c-month-201612","00000c","month","201612","2016-11-29","2016-12-28" "00000c-month-20162","00000c","month","20162","2016-01-29","2016-02-28" "00000c-month-20163","00000c","month","20163","2016-02-29","2016-03-28" "00000c-month-20164","00000c","month","20164","2016-03-29","2016-04-28" “MySQL数据库怎么用命令行导出带表头和不带表头的csv文件”的内容就介绍到这里了，感谢大家的阅读。 （编辑：晋中站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
