mysql - 使用触发器预设列值

Question

如何使用触发器预设列的值？我使用的是 MySQL 5.5.54。

编辑。如果我删除 c2 上的 NOT NULL 约束，它似乎可以工作。

mysql> USE mydb;
Database changed
mysql> INSERT INTO t(c1) VALUES(123);
ERROR 1364 (HY000): Field 'c2' doesn't have a default value
mysql>

架构:

-- MySQL Script generated by MySQL Workbench
-- 02/21/17 08:26:27
-- Model: New Model    Version: 1.0
SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';

-- -----------------------------------------------------
-- Schema mydb
-- -----------------------------------------------------
DROP SCHEMA IF EXISTS `mydb` ;
CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci ;
USE `mydb` ;

-- -----------------------------------------------------
-- Table `mydb`.`t`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`t` (
  `c1` INT NOT NULL AUTO_INCREMENT,
  `c2` INT NOT NULL,
  PRIMARY KEY (`c1`))
ENGINE = InnoDB;


SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
USE `mydb`;

DELIMITER $$
USE `mydb`$$
CREATE TRIGGER `t_BINS` BEFORE INSERT ON `t` FOR EACH ROW
begin
SET NEW.c2=321;
end$$


DELIMITER ;

Answer 1

这似乎是一个 MySQL 错误，于 2004 年 10 月 27 日 23:30 提交，但尚未解决。引用https://dev.mysql.com/worklog/task/?id=6030 。我找不到任何其他文档。

两种解决方案是删除 NOT NULL 约束，或保留 NOT NULL 约束并提供默认值。

This WorkLog originated from BUG#6295 (TRIGGERS ARE NOT PROCESSED FOR NOT NULL COLUMNS).

The problem is that if a column is declared as NOT NULL, it is not possible to do INSERT NULL (or UPDATE to NULL) even though there is associated trigger, setting NOT-NULL value.

For example:

• Table 't1' with a NOT NULL column 'c1';

• The table has BEFORE INSERT trigger which sets the 'c1' column to not null value (SET NEW.c1 = 1);

• INSERT INTO t1 VALUES(NULL) or UPDATE t1 SET c1 = NULL fail with the following error: ERROR 1048 (23000): Column '' cannot be null

The cause for the current (wrong) behavior is that the column constraints are checked before triggers are executed.

This is against The Standard, which requires that column constraints are checked at the end of an SQL statement.