MySQL导入CSV文件后日期列出现0000-00-00空值问题咨询

1. 出现0000-00-00的原因 #

MySQL的DATE类型默认仅支持YYYY-MM-DD格式的合法日期输入，你CSV中存储的dd/mm/yyyy格式字符串无法被MySQL直接识别为有效日期，导入时就会自动填充默认错误值0000-00-00（若开启SQL严格模式会直接抛出导入失败错误）。

2. 无法通过建表时调整日期格式解决该问题 #

MySQL的DATE、DATETIME等日期类型的存储和默认解析格式是固定的，不支持在建表时自定义日期的输入格式，因此调整建表语句无法解决该问题。

小提示：你提供的建表语句存在语法错误，字段间缺少逗号，且Date属于SQL关键字建议加反引号转义，修正后的建表语句如下：

CREATE TABLE tblname (
    `Date` DATE,
    id INT,
    region VARCHAR(45)
);

3. 可行解决方案 #

3.1 导入时直接转换日期格式 #

使用LOAD DATA INFILE导入时，通过STR_TO_DATE函数手动指定日期解析规则即可正确导入，示例语句：

LOAD DATA INFILE '你的本地CSV文件绝对路径'
INTO TABLE tblname
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"'
LINES TERMINATED BY '\r\n' -- Windows系统CSV换行用\r\n，Linux用\n
IGNORE 1 ROWS -- 若CSV第一行是表头则保留，否则删除该行
(@raw_date, id, region)
SET `Date` = STR_TO_DATE(@raw_date, '%d/%m/%Y');

3.2 生成Jan-2015格式的年月字段 #

不需要额外存储该字段，查询时用DATE_FORMAT函数动态生成即可：

SELECT *, DATE_FORMAT(`Date`, '%b-%Y') AS month_year FROM tblname;

如果需要持久化存储该字段，可执行以下语句新增并批量赋值：

-- 新增年月字段
ALTER TABLE tblname ADD COLUMN month_year VARCHAR(20);
-- 批量生成年月值
UPDATE tblname SET month_year = DATE_FORMAT(`Date`, '%b-%Y');

内容的提问来源于stack exchange，提问作者Chai