将MYSQL表导出到CSV时如何处理空字段 [英] How to handle null field when exporting MYSQL tables to CSV

问题描述

现在，当我将MYSQL表导出到CSV文件时，我在数据库中的NULL字段中得到了\N的期望值。将表导出到CSV时是否可以将\N输出更改为空字符串？

Right now when I export MYSQL tables to CSV files, I'm getting \N for NULL fields in the database which is expected. If there a way to change the \N output to just an empty string when exporting tables to csv?

谢谢

SELECT 'CU_CustomerID','CU_UserName','CU_Password','CU_Email','CU_Company','CU_Comment','CU_LastBasket','CON_FirstName','CON_MiddleI','CON_LastName','CON_Address1','CON_Address2','CON_City','CON_State','CON_Province','CON_Country','CON_Zip','CON_Phone1','CON_Phone2'
UNION
SELECT T1.CU_CustomerID,T1.CU_UserName,T1.CU_Password,T1.CU_Email,T1.CU_Company,T1.CU_Comment,T1.CU_ShopperPoints,T2.CON_FirstName,T2.CON_MiddleI,T2.CON_LastName,T2.CON_Address1,T2.CON_Address2,T2.CON_City,T2.CON_State,T2.CON_Province,T2.CON_Country,T2.CON_Zip,T2.CON_Phone1,T2.CON_Phone2
FROM CUSTOMERS AS T1
INNER JOIN CONTACT AS T2 ON T1.CU_CustomerID = T2.CON_CustomerID
WHERE T1.CU_CustomerID BETWEEN 0 AND 1000
INTO OUTFILE  'customers.csv'
FIELDS TERMINATED BY  ','
ENCLOSED BY  '"'

推荐答案

从MySQL 文档

如果FIELDS ESCAPED BY字符为空，则不会转义任何字符
，并且NULL输出为NULL，而不是\N。指定一个空的转义字符，
可能不是一个好主意，特别是如果数据中的字段值包含刚刚给出的列表中的任何字符。

If the FIELDS ESCAPED BY character is empty, no characters are escaped and NULL is output as NULL, not \N. It is probably not a good idea to specify an empty escape character, particularly if field values in your data contain any of the characters in the list just given.

这篇关于将MYSQL表导出到CSV时如何处理空字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！