MySQL触发器无法将'm/d/YYYY'转为DATE类型问题求助

你的疑惑完全合理——直接用STR_TO_DATE能成功，但触发器却报错，核心原因是MySQL的数据类型验证是在BEFORE触发器触发之前执行的。

当你执行INSERT INTO t1 VALUES ('9/7/2016')时，MySQL首先会尝试把字符串'9/7/2016'自动转换为DATE类型。由于这个格式不符合MySQL默认的YYYY-MM-DD或YYYYMMDD格式，转换失败直接抛出1292错误，这时候触发器还没来得及运行，自然无法帮你转换格式。而直接使用STR_TO_DATE('9/7/2016', '%c/%e/%Y')时，是先把字符串转换成合法的DATE值，再插入字段，所以不会触发类型验证错误。

可行的解决方案 #

根据你批量插入数据的需求，推荐以下几种方式：

1. 用LOAD DATA INFILE直接转换（最适合批量文件导入） #

如果是从文件批量导入数据，不需要触发器，直接在LOAD DATA语句里转换格式即可：

LOAD DATA INFILE '/path/to/your/datafile.csv'
INTO TABLE t1
FIELDS TERMINATED BY ','  -- 根据你的文件分隔符调整
LINES TERMINATED BY '\n'
( @temp_date )  -- 先把文件里的日期读到临时变量
SET TEST_DATE = STR_TO_DATE(@temp_date, '%c/%e/%Y');

2. 新增临时字段配合触发器 #

如果必须用触发器，可以给表加一个VARCHAR类型的临时字段，用来接收原始字符串，再通过触发器转换到DATE字段：

• 第一步，修改表结构：

ALTER TABLE t1 ADD COLUMN TEST_DATE_RAW VARCHAR(20);

• 第二步，创建触发器：

DELIMITER |
CREATE TRIGGER t1FixDate BEFORE INSERT ON t1 FOR EACH ROW
BEGIN
    SET NEW.TEST_DATE = STR_TO_DATE(NEW.TEST_DATE_RAW, '%c/%e/%Y');
END;
| DELIMITER ;

• 第三步，插入数据时给临时字段传值：

INSERT INTO t1 (TEST_DATE_RAW) VALUES ('9/7/2016');

3. 插入时直接转换（适合单条或小批量插入） #

如果是小批量插入，也可以直接在INSERT语句里用STR_TO_DATE，就像你测试的t2表那样：

INSERT INTO t1 VALUES (STR_TO_DATE('9/7/2016', '%c/%e/%Y'));

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