如何在MySQL中插入mm/dd/yyyy格式日期？CHECK约束报错如何解决

问题核心原因 #

你建表时的CHECK约束存在两处逻辑错误，是两次插入报错的根本原因：

1. 字段类型不匹配：END_DATE是DATE类型，但你用了仅适用于字符串的LIKE规则做校验
2. LIKE语法错误：LIKE语句中匹配单个任意字符的通配符是下划线_，你写的-是硬匹配横杠字符，完全不符合你预期的日期格式校验逻辑

两次报错的具体说明：

• 第一次报错：'10/10/2020'不是MySQL默认支持的DATE类型字面量格式，MySQL默认DATE输入格式为'YYYY-MM-DD'，斜杠分隔的字符串会被判定为非法日期值
• 第二次报错：STR_TO_DATE已经将字符串转为合法的DATE类型值，但DATE类型做LIKE匹配时会自动转为'YYYY-MM-DD'格式的字符串，完全匹配不上'--/--/----'的规则，触发约束违反

推荐解决方案：修正错误约束后插入 #

第一步：修改表约束 #

-- 删除错误的原有CHECK约束
ALTER TABLE job_history DROP CHECK job_history_chk_1;
-- 可按需添加合理的日期校验规则，比如校验结束日期晚于开始日期
ALTER TABLE job_history ADD CHECK (END_DATE > START_DATE);

DATE类型本身就会自动校验日期合法性，不需要额外加格式校验，非法日期根本无法写入。

第二步：执行插入（注意必须补全所有非空字段，仅插END_DATE会报错） #

写法1：用MySQL默认DATE格式插入

INSERT INTO job_history(EMPLOYEE_ID, START_DATE, END_DATE, JOB_ID, DEPARTMENT_ID) 
VALUES (1001, '2020-01-01', '2020-10-10', 'DEV', 10);

写法2：保留dd/mm/yyyy格式输入习惯

INSERT INTO job_history(EMPLOYEE_ID, START_DATE, END_DATE, JOB_ID, DEPARTMENT_ID) 
VALUES (1001, STR_TO_DATE('01/01/2020','%d/%m/%Y'), STR_TO_DATE('10/10/2020','%d/%m/%Y'), 'DEV', 10);

不推荐的兼容方案：保留原有格式校验逻辑 #

如果你一定要坚持用__/__/____格式做字符串校验，需要先修改字段类型为字符串：

-- 修改END_DATE字段为字符串类型，同时修正LIKE通配符
ALTER TABLE job_history MODIFY COLUMN END_DATE varchar(10) NOT NULL CHECK (END_DATE LIKE '__/__/____');
-- 直接插入斜杠分隔的日期字符串即可
INSERT INTO job_history(EMPLOYEE_ID, START_DATE, END_DATE, JOB_ID, DEPARTMENT_ID) 
VALUES (1001, '2020-01-01', '10/10/2020', 'DEV', 10);

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