MySQL直接导出CSV文件

 

更多内容请访问 www.uusystem.com

 

在MySQL中，它自己就能导出CSV文件 ，只不过是有如下几个问题需要大家解决。

一、 生成文件不成功，没有读写权限

　　1.关联mysql权限

(1).修改msql配置文件，my.ini中增加 secure_file_priv ="" 

(2).用root账户登陆mysql，执行grant file on *.* to dzwl_u1;

备注：secure_file_priv =""代表导出指定目录为随意目录。grant file on *.* to dzwl_u1; 给Mysql中用户dzwl_u1关联导出文件操作权限。

2.如果使用 select  ... into  outfile ...方式导出数据，需要分为两种情况 ：

(1). windows系统下：可以随意指定对应的目录 。

(2). linux 系统下 ：

A.不指定目录 ，默认在当前导出数据库数据目录下 ，例如  /var/lib/mysql/db_name 

B. 指定目录时，默认只能指定 /tmp目录 。否则回报 “ ERROR 1 (HY000) at line 1: Can't create/write to file '/xxxx/xxxx/xxx.csv' (Errcode: 13)” 错误 ！导致原因是因为权限问题，修改起来比较麻烦 ！可以放到/tmp/目录下，然后cp到其它你想存放的目录。【推荐使用这种方法】

二、导出数据为中文乱码

1.数据库表一般是utf8编码，excel默认编码格式是GBK，excel直接打开时候是一堆乱码。可以先保存成为txt格式，excel打开txt时候会提示选择用哪种编码方式打开，选择utf8解决。

2.导出时候加上CHARACTER SET gbk 即可【推荐使用】

三、数据没有格式化，阅读不方便

关于数据格式化的问题，需要给导出的字段传递一些参数

1 fields terminated by ',' optionally enclosed by '"' escaped by '"'   
2 lines terminated by '\r\n'   

这个参数是根据RFC4180文档设置的，该文档全称Common Format and MIME Type for Comma-Separated Values (CSV) Files，其中详细描述了CSV格式，其要点包括：

(1)字段之间以逗号分隔，数据行之间以\r\n分隔；

(2)字符串以半角双引号包围，字符串本身的双引号用两个双引号表示。

terminated by分隔符：意思是以什么字符作为分隔符

enclosed by字段括起字符
escaped by转义字符

terminated by描述字段的分隔符，默认情况下是tab字符（\t） 
enclosed by描述的是字段的括起字符。
escaped by描述的转义字符。默认的是反斜杠（backslash：\ ）  

四、示例代码

SELECT
    CREATEUSERID,
    BLACKID,
    BLACKNAME,
    DID,
    DEVICEID,
    ALARMTYPE,
    IMSI,
    IMEI,
    DEVICENAME,
    DEVICETYPE,
    DATATIME
FROM
    (
        SELECT
            T1.CREATEUSERID AS CREATEUSERID,
            T0.BLACKID AS BLACKID,
            T1.BLACKNAME AS BLACKNAME,
            T0.DID AS DID,
            T2.DEVICEID AS DEVICEID,
            T1.ALARMTYPE AS ALARMTYPE,
            T1.IMSI AS IMSI,
            T1.IMEI AS IMEI,
            T2.DEVICENAME AS DEVICENAME,
            T2.DEVICETYPE AS DEVICETYPE,
            T0.DATATIME AS DATATIME
        FROM
            T_DEFS_BLACK_WARN T0
        LEFT JOIN T_DEFS_BLACK_INFO T1 ON T0.BLACKID = T1.BLACKID
        LEFT JOIN T_DEFS_DEVICE_INFO T2 ON T0.DID = T2.DID
        WHERE
            T1.ALARMTYPE = 1
        AND T1.CREATEUSERID = 2
        UNION
            SELECT
                '创建者ID',
                '黑名单ID',
                '黑名单名字',
                '设备自增ID',
                '设备ID',
                '告警类型',
                'IMSI',
                'IMEI',
                '设备名字',
                '设备类型',
                '黑名单上线时间',
                '黑名单上线地点'
    ) m
ORDER BY
    m.BLACKID DESC INTO OUTFILE 'c://blackListAlarm.csv' CHARACTER
SET gbk FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '"' LINES TERMINATED BY '\r\n';