mysql - 如何在MySQL触发器中使用 "WITH "子句？

Question

我正在尝试编写一个 Mysql 查询。我在 select 函数的WITH 子句中遇到一些错误。

这是示例查询:

CREATE TRIGGER  SAMPLE BEFORE INSERT ON SERVER
  FOR EACH ROW
  BEGIN
    IF ((SELECT COUNT(S.ID) FROM SERVER S WITH UR) >= (SELECT L.SERVERS FROM SAMPLE L WHERE L.ID = 1 LIMIT 1)) THEN
        SIGNAL SQLSTATE '73550' SET MESSAGE_TEXT='+ID';
    END IF;
  END;

我收到错误:

right syntax to use near 'UR) >= (SELECT L.SERVERS FROM SAMPLE L WHERE L.ID = 1 LIMIT 1))

Mysql 不支持WITH 子句吗？或者我应该使用任何其他语法？任何建议都会有所帮助。

更新:

我还使用另一个查询:

CREATE TRIGGER SAMPLE_TRIGGER NO CASCADE BEFORE INSERT ON TABLE_1
  FOR EACH ROW
  BEGIN
      SET NEW.RID = (SELECT ID FROM TABLE_2 WHERE ACTIVE=0 FETCH FIRST 1 ROWS ONLY WITH RS USE AND KEEP EXCLUSIVE LOCKS);
      IF (NEW.RID IS NULL) THEN
          SIGNAL SQLSTATE '73550' SET MESSAGE_TEXT='+ID';
      END IF;
  END;

出现另一个错误

right syntax to use near 'LIMIT 1 WITH RS USE AND KEEP EXCLUSIVE LOCKS);
      IF (NEW.RID IS NULL) THEN'

Answer 1

在 DB2 中，with ur 表示“未提交的读取”。这是数据库中的锁定机制，如所解释的 here .

如果您将代码移植到 MySQL，我不会担心这一点。因此，只需将其删除即可:

CREATE TRIGGER  SAMPLE BEFORE INSERT ON SERVER
  FOR EACH ROW
  BEGIN
    IF ((SELECT COUNT(S.ID) FROM SERVER S) >= (SELECT L.SERVERS FROM SAMPLE L WHERE L.ID = 1 LIMIT 1)) THEN
        SIGNAL SQLSTATE '73550' SET MESSAGE_TEXT='+ID';
    END IF;
  END;