在日常工作中经常会遇见导出表中的数据到csv文件的操作,这里就简单总结一下导出的操作。
下面对csv文件的描述是摘录:
据RFC4180文档设置的,该文档全称Common Format and MIME Type for Comma-Separated Values (CSV) Files,其中详细描述了CSV格式,其要点包括: (1)字段之间以逗号分隔,数据行之间以\r\n分隔; (2)字符串以半角双引号包围,字符串本身的双引号用两个双引号表示。
这里关键点,我们导出的数据文件字段之间应该以逗号分隔,数据行之间应该以\r\n分隔;字段的值之间以半角双引号包围,字段值中的双引号以两个双引号表示。
导出表数据的三种方法:
使用select .... into outfile的方法
直接在mysql的交互界面使用select命令导出数据到文件。
root@newsdb 09:53:08>select * from t_hk_stock_news where news_time > "2018.12.31 23:59:59" into outfile "/db/test.csv"; ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement
以上报错需要在配置文件中指定secure_file_priv参数的值,这个值指向一个地址,导出的文件需要写入到对应的地址中。
在不知道你个默认的情况下,导出文件中字段之间用TAB键隔开,每行记录之间用\n换行符隔开,
root@newsdb 01:44:05>select * from t_hk_stock_news limit 1 into outfile "/db/test1.csv"; Query OK, 1 row affected (0.00 sec) root@newsdb 01:44:10>[root@test2 db]# head -n2 test1.csv 000028952-c807-11e7-85f5-00163e12520202 1 NOW.838555 hk03337 安东油田服务(03337.HK)就现有优先票据提交换要约及额外发行票据 2017-11-13 07:35:20000000001 -1 阿思达克 \N \N \N 2017-11-13 07:35:25 15105293425 \N \N 1 2f87c2337-d3d1-11e7-95494-0016233e020202 \N
可以看到对于记录中存储的null的值,导出的时候自动换成来\N的值的形式。
在使用select ... into outfile导出数据的时候,可以使用如下参数指定其分隔符,以及行与行之间的换行符。
fields terminated by描述字段的分隔符,默认情况下是tab字符(\t) optionally enclosed by描述的是字段的括起字符。 escaped by描述的转义字符。默认的是反斜杠(backslash:\ )
lines terminated by : 行与行之间的分隔
root :: t_hk_stock_news limit outfile "dbtest2.csv" fields terminated optionally enclosed escaped lines terminated rows affected (
null值转换成/N的值,不知道为何只用半个双引号,另外这个文件若用excel打开会出现乱码的现象;可以在导出的时候指定字符集。
root@newsdb 01:56:01>select * from t_hk_stock_news limit 3 into outfile "/db/test2.csv" character set gbk fields terminated by ',' optionally enclosed by '"' escaped by '"' lines terminated by "\r\n"; Query OK, 3 rows affected (0.00 sec)
上面加入了指定字符集的语句,但是在excel打开的时候会出现串格的问题,多个字段显示在同一个单元格中,特别数据库中存储的null值,在这里转换为\N值时,会出现多个值显示在一个单元格中的问题。
上面导出的数据并不包含每个字段的名字。
使用mysql命令导出数据
利用mysql的-e参数,可以导出数据,最重要的是我们可以对导出的数据进行正则处理。
如下利用mysql命令导出数据到csv文件,并且把表中的null值在excel中显示为空。
# mysql e " names gbk; newsdb.t_hk_stock_news news_time limit " sed e "s\t,g" e "s g" e "s\n\r\ng" dbtest.csv #在-e参数中实际使用了两条命令,一条是设置字符集,另一条是select语句,通过管道把每一行数据都通过正则来处理。 #正则中把字段之间的TAB键换为“,”,然后把字段值中的null替换为空字符
上面的文件内容如下:【因为字符集的问题所以产生了乱码,但是可以看到每个字段已经导了出来】
[root@test2 db]# head -n5 test.csv news_id,src_type,src_id,symbol,title,news_time,content_type,news_type,is_valid,origin,author,url,website,create_time,news_t,edit_desc,news_md5,is_repeat,repeat_id,img_url,update_time 009c7c4cdf3878289c25b6f88267cb2b,3,6f8d47cf17c1bbada470ea63bc34eba4,hk01363,א͏»·±£(01363)4Ղ1ɕͣƆ ԭӲδ֪,2019-04-01 00:00:00,5, ,-1,ׇͨ²ƾ, , , ,2019-04-01 09:55:02,1554048000, , ,1,2c9c348790f944d39d32838b4cff1e87, ,2019-04-01 21:16:220129b1dfb971aafec131f6f92c862aa9,3,d816360899e4f0049eabbac7e7cb54cb,hk01755,т³Ȕh01755)£ºϷ²ِ³Ȕď????£¼τゞ¶°칫˒ϯҵ,2019-04-01 00:00:00,5, ,-1,ׇͨ²ƾ, , , ,2019-04-01 21:30:04,1554048000, , ,1,c2ba22ac8d15ddef1ef543ea61784029, ,2019-04-02 14:31:1801dbb92fba915864f2e1daa8f71dfd2c,3,bae141970e2e18a6f96e69358030a613,hk00665,4Ղ1ɕ¿ªƌǰհ |¾¼Ċ????¤ǚ ¸۹ɼ͐????֧³Ƈ¿,2019-04-01 00:00:00,5, ,1,ׇͨ²ƾ, , , ,2019-04-01 07:55:02,1554048000, , ,0, , ,2019-04-01 07:56:0501dbb92fba915864f2e1daa8f71dfd2c,3,4d362b95079d4404f57051f64545826b,hk01788,4Ղ1ɕ¿ªƌǰհ |¾¼Ċ????¤ǚ ¸۹ɼ͐????֧³Ƈ¿,2019-04-01 00:00:00,5, ,1,ׇͨ²ƾ, , , ,2019-04-01 07:55:03,1554048000, , ,0, , ,2019-04-01 07:56:05[root@test2 db]#
把导出的文件用excel打开如下:
可以看到对应的null值已经变为空值,并且也没有出现多个字段合并到同一个单元格的问题。
若不导出标题行,可以使用-N参数。