您好,登录后才能下订单哦!
本篇内容介绍了“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文件”的内容就介绍到这里了,感谢大家的阅读。如果想了解更多行业相关的知识可以关注亿速云网站,小编将为大家输出更多高质量的实用文章!
免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:is@yisu.com进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。