开发者问题收集

MySQL If else 构造语法

2018-11-17
61
mysql sql if-statement

我在 MySQL 的 sql 触发器中使用 if else 构造时遇到了一些问题。下表仅用于测试。

第一张表： 

  CREATE TABLE `test2`.`t1` (
  `c1` VARCHAR(5) NOT NULL,
  `c2` VARCHAR(45) NULL,
  `c3` VARCHAR(45) NULL,
  PRIMARY KEY (`c1`));

我的第二张表： 

    CREATE TABLE `test2`.`t2` (
  `cc1` VARCHAR(5) NOT NULL,
  `cc2` VARCHAR(45) NULL,
  `cc3` VARCHAR(45) NULL,
  PRIMARY KEY (`cc1`));

第三张表： 

CREATE TABLE `test`.`t3` (
  `ccc1` VARCHAR(5) NOT NULL,
  `ccc2` VARCHAR(45) NULL,
  `ccc3` VARCHAR(45) NULL,
  PRIMARY KEY (`cc1`));

c1 是 cc1 和 ccc1 的主键。

我使用的触发器是： 

DROP TRIGGER IF EXISTS `test`.`t1_AFTER_UPDATE`;

DELIMITER $$
USE `test`$$
CREATE DEFINER=`root`@`localhost` TRIGGER `t1_AFTER_UPDATE` AFTER UPDATE ON 
`t1` FOR EACH ROW 
BEGIN
DECLARE start varchar(1);
set start=substring((c2),1,1);

 IF(start='S') THEN
     UPDATE t2
     SET cc2 = NEW.c2  
     WHERE cc1 = NEW.c1;
 else

    UPDATE t3
    SET ccc2 = NEW.c2  
    WHERE ccc1 = NEW.c1;
 END IF;
  END$$
  DELIMITER ;

基本上，如果用户更新 t1.c2，则必须更新 t2.cc2 或 t3.ccc2。如果 NEW.c2 中的 t1.c2 以字母 S 开头，则必须更新 t2.cc2，否则必须更新 t3.ccc2。

查询执行顺利，但不允许我更新 c2 的值： 

Executing:
UPDATE `test2`.`t1` SET `c2` = '24' WHERE (`c1` = 'M123');

Operation failed: There was an error while applying the SQL script to the database.
ERROR 1054: 1054: Unknown column 'c1' in 'field list'
SQL Statement:
2个回答

在触发器中，我们通过 NEW （操作后）和 OLD （操作前）关键字引用正在更新/插入/删除的行中的列。

您需要使用这些关键字才能访问这些值。您可以使用 NEW 关键字从当前表访问 c2 值。您还可以通过仅在实际修改 c2 值时执行操作来进一步优化。 

DROP TRIGGER IF EXISTS `test`.`t1_AFTER_UPDATE`;

DELIMITER $$
USE `test`$$
CREATE DEFINER=`root`@`localhost` TRIGGER `t1_AFTER_UPDATE` AFTER UPDATE ON 
`t1` FOR EACH ROW 
BEGIN
DECLARE start varchar(1) DEFAULT NULL; -- default it to NULL

-- Check if there is any update on c2 column
IF (NEW.c2 <> OLD.c2)

  -- We access the updated value of c2 using NEW keyword
  SET start = SUBSTRING(NEW.c2,1,1);
END IF;

-- Do update operation on other tables only when start is not null
IF (start IS NOT NULL) THEN

  IF (start = 'S') THEN
     UPDATE t2
     SET cc2 = NEW.c2  
     WHERE cc1 = NEW.c1;
  ELSE

     UPDATE t3
     SET ccc2 = NEW.c2  
     WHERE ccc1 = NEW.c1;
  END IF;
END IF;

END$$
DELIMITER ;

来自 触发器文档 的一些值得注意的要点：

Within the trigger body, the OLD and NEW keywords enable you to access columns in the rows affected by a trigger. OLD and NEW are MySQL extensions to triggers; they are not case-sensitive.

In an INSERT trigger, only NEW.col_name can be used; there is no old row. In a DELETE trigger, only OLD.col_name can be used; there is no new row. In an UPDATE trigger, you can use OLD.col_name to refer to the columns of a row before it is updated and NEW.col_name to refer to the columns of the row after it is updated.

Madhur Bhaiya
2018-11-17

您的触发器基本上看起来没问题，除了 start 的定义。列引用缺少对 new 的引用。但我对错误消息感到困惑，它应该在 c2 而不是 c1 上。

无论如何，我都会简单地放弃它： 

DELIMITER $$
USE `test`$$

CREATE DEFINER=`root`@`localhost` TRIGGER `t1_AFTER_UPDATE` AFTER UPDATE ON 
`t1` FOR EACH ROW 
BEGIN
    IF (new.c2 LIKE 'S%') THEN
        UPDATE t2
            SET cc2 = NEW.c2  
            WHERE cc1 = NEW.c1;
    ELSE
        UPDATE t3
            SET ccc2 = NEW.c2  
            WHERE ccc1 = NEW.c1;
    END IF;
END$$

DELIMITER ;

您还需要修复 t3 的定义。主键列不存在。

Gordon Linoff
2018-11-17