MariaDB使用INSERT IGNORE时约束失败仅返回警告如何改为报错

环境信息 #

• MariaDB版本：10.6.4-MariaDB-1:10.6.4+maria~focal

问题描述 #

执行的插入SQL如下：

insert ignore into `step` (is_draft, created_by) values ('1', 2);

执行后未抛出预期的约束错误，仅返回3条警告，使用show warnings\G查询结果如下：

MariaDB [kite]> show warnings\G
*************************** 1. row ***************************
  Level: Warning
   Code: 1364
Message: Field 'course_id' doesn't have a default value
*************************** 2. row ***************************
  Level: Warning
   Code: 1364
Message: Field 'title' doesn't have a default value
*************************** 3. row ***************************
  Level: Warning
   Code: 1452
Message: Cannot add or update a child row: a foreign key constraint fails (`kite`.`step`, CONSTRAINT `step_ibfk_1` FOREIGN KEY (`course_id`) REFERENCES `course` (`course_id`) ON DELETE CASCADE ON UPDATE CASCADE)
3 rows in set (0.000 sec)

此前同场景会直接触发报错，排查时已确认当前SQL_MODE配置如下：

MariaDB [kite]> SELECT @@SQL_MODE, @@GLOBAL.SQL_MODE\G
*************************** 1. row ***************************
       @@SQL_MODE: STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
@@GLOBAL.SQL_MODE: STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

根因与解决方案 #

问题根源为SQL语句中使用了IGNORE关键字，该关键字的作用就是将执行过程中本应抛出的错误强制转换为警告返回，不会终止语句执行。
移除INSERT语句中的IGNORE关键字后，约束校验失败时就会正常抛出错误，符合预期行为。

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