MySQL數據庫怎么用命令行導出帶表頭和不帶表頭的csv文件

本篇內容介紹了“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>
mysql>
插入數據:
mysql> insert into test select * from date_rule;
Query OK, 1412 rows affected (0.49 sec)
Records: 1412  Duplicates: 0  Warnings: 0




不帶表頭:
mysql> select * from test into outfile 'd:\test.csv' fields terminated by ','enclosed by '"'lines terminated by '\r\n';
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:\WINDOWS\system32> net stop mysql
MySQL 服務正在停止.
MySQL 服務已成功停止。


PS C:\WINDOWS\system32> net start mysql
MySQL 服務正在啟動 ..
MySQL 服務已經啟動成功。


再次運行命令成功:
mysql> select * from test into outfile "d:/test.csv" fields terminated by ','enclosed by '"'lines terminated by '\r\n';
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 '\r\n';
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文件”的內容就介紹到這里了，感謝大家的閱讀。如果想了解更多行業相關的知識可以關注億速云網站，小編將為大家輸出更多高質量的實用文章！