How to use command line to export csv files with and without headers in MySQL database
This article introduces the "MySQL database how to use the command line to export csv files with and without headers" related knowledge, in the actual case of the operation process, many people will encounter such a dilemma, and then let the editor lead you to learn how to deal with these situations! I hope you can read it carefully and be able to achieve something!
The experiments are as follows:
Build a table:
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` (`starttime`)
-> KEY `end` (`end_ time`)
->) ENGINE=InnoDB DEFAULT CHARSET=utf8
Query OK, 0 rows affected (0.35 sec)
Mysql >
Mysql >
Insert data:
Mysql > insert into test select * from date_rule
Query OK, 1412 rows affected (.49 sec)
Records: 1412 Duplicates: 0 Warnings: 0
Without a meter header:
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
An error was reported in the export, because version 5.7 restricts the import and export of mysqld. The solution:
Add to my.ini
[mysqld]
Secure_file_priv=''
Restart the database for the configuration to take effect
PS C:\ WINDOWS\ system32 > net stop mysql
The MySQL service is stopping.
The MySQL service stopped successfully.
PS C:\ WINDOWS\ system32 > net start mysql
The MySQL service is starting..
The MySQL service has started successfully.
Run the command again successfully:
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)
Open the file with Notepad++ and find no header:
"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"
....
....
View the table structure:
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)
Export csv with header:
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.01sec)
Open the file with Notepad++ and find it with a header:
"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"
This is the end of the content of "how to export csv files with and without headers from the MySQL database using the command line". Thank you for your reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!
