将MYSQL表导出为CSV时如何处理空字段

Question

目前当我将MYSQL表导出为CSV文件时，数据库中的NULL字段会显示为\N，这是预期的。请问在导出表格到CSV时，有没有一种方法可以将\N输出更改为空字符串？

谢谢

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  '"'

- user2700690

Answer 1

来自MySQL 文档

如果FIELDS ESCAPED BY字符为空，则不会转义任何字符，NULL会被输出为NULL而不是\N。如果您的数据中的字段值包含上述列表中的任何字符，那么指定空转义字符可能不是一个好主意。

- Jens

---

那么在我的情况下，如何将NULL字段输出为空字符串而不是\N？确切的查询是什么？谢谢。 - user2700690

@user2700690 请添加您的陈述，然后我可以进行更正。 - Jens

@user2700690在查询末尾添加ESCAPED BY "";。 - Jens

现在它返回的值是“NULL”，而不是“\N”。有没有办法返回空字符串来代替NULL字段？ - user2700690

@user2700690 在选择中执行：SELECT ifNull('CU_CustomerID',''),ifnull('CU_UserName',''),... - Jens

太棒了。只是不需要在列名周围加引号，否则它会将其视为字符串。感谢您的帮助！ - user2700690

Answer 2

简单易懂的方式：-

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 COALESCE(T1.CU_CustomerID,''),COALESCE(T1.CU_UserName,''),COALESCE(T1.CU_Password,''),COALESCE(T1.CU_Email,''),COALESCE(T1.CU_Company,''),COALESCE(T1.CU_Comment,''),COALESCE(T1.CU_ShopperPoints,''),COALESCE(T2.CON_FirstName,''),COALESCE(T2.CON_MiddleI,''),COALESCE(T2.CON_LastName,''),COALESCE(T2.CON_Address1,''),COALESCE(T2.CON_Address2,''),COALESCE(T2.CON_City,''),COALESCE(T2.CON_State,''),COALESCE(T2.CON_Province,''),COALESCE(T2.CON_Country,''),COALESCE(T2.CON_Zip,''),COALESCE(T2.CON_Phone1,''),COALESCE(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 '"'
将所有值默认为空字符串而不是NULL（'\N'）。

- Dallas Clarke

Answer 3

为了避免对每个变量都键入“ifnull（var，''）as var”，可以使用以下方法：

SELECT GROUP_CONCAT(CONCAT("IFNULL(",COLUMN_NAME,",'') AS ", COLUMN_NAME))
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = '<table name>'
AND TABLE_SCHEMA = '<schema name>'
ORDER BY ORDINAL_POSITION

- Rustam Guliev

Answer 4

我也遇到过这个问题，解决方法如下：

select ifnull(`addr`.`reference`, "") AS `reference`, data2 from ...

- Jomadee