MySQL + phpMyAdmin，IF语句触发器语法错误[重复]

dojqjjoe 于 2022-11-09 发布在 PHP

关注(0)|答案(3)|浏览(119)

此问题在此处已有答案：

Delimiters in MySQL（4个答案）
6个月前关闭。
我正在做一个小项目，目前正在建立一个MySQL数据库。不幸的是，这段代码快把我逼疯了：

CREATE TRIGGER main_db.tg_make_competitor
AFTER UPDATE ON main_db.persons
FOR EACH ROW 
IF (NEW.permissions LIKE '%Competitor%') && (SELECT COUNT(*) FROM main_db.competitors WHERE competitor_id LIKE NEW.person_id) = 0 THEN
    INSERT INTO main_db.competitors(competitor_id)
    VALUES (NEW.person_id);
END IF;

我的想法是，当表persons发生更新时，触发器应该检查记录是否具有Competitor（SET数据类型）的权限，如果Id = NEW.person_id的竞争者不存在，则创建一个。
对我来说，一切似乎都很好。但是phpMyAdmin抛出了一个错误：


# 1064 - Something is wrong in your syntax near '' in line 6

第六行是这个部分：

VALUES (NEW.person_id);

我错过了什么吗？我会感激任何提示。提前感谢！

EDIT：人员和竞争对手表结构：

CREATE TABLE main_db.persons 
(
    person_id INT,
    first_name VARCHAR(32) DEFAULT NULL,
    last_name VARCHAR(32) DEFAULT NULL,
    permissions SET('Standard', 'Competitor', 'Referee', 'Organizer', 'Director') DEFAULT 'Standard',
    FOREIGN KEY (person_id) REFERENCES main_db.accounts(account_id) ON DELETE CASCADE,
    PRIMARY KEY (person_id)
);

CREATE TABLE main_db.competitors 
(
    competitor_id INT,
    club VARCHAR(256) DEFAULT NULL,
    license VARCHAR(32) DEFAULT NULL,
    FOREIGN KEY (competitor_id) REFERENCES main_db.persons(person_id) ON DELETE CASCADE,
    PRIMARY KEY (competitor_id)
);

EDIT 2：不幸的是，添加了开始... END的代码也不起作用：/

CREATE TRIGGER tg_make_competitor 
AFTER UPDATE ON persons
FOR EACH ROW
BEGIN
IF (NEW.permissions LIKE '%Competitor%') && (SELECT COUNT(*) FROM competitors WHERE competitor_id LIKE NEW.person_id) = 0 THEN
    INSERT INTO competitors(competitor_id)
    VALUES (NEW.person_id);
END IF;
END

解决方案

感谢Ergest Basha和Luuk的回答，原来没有使用自定义分隔符才是问题所在。添加分隔符后，一切都变得很好用。

phpmyadmin

来源：https://stackoverflow.com/questions/72328376/mysql-phpmyadmin-syntax-error-on-trigger-with-if-statement

3条答案

按热度按时间

yzxexxkh1#

这段代码可以在PhpMyAdmin中使用：

DELIMITER //
CREATE TRIGGER test.tg_make_competitor
AFTER UPDATE ON test.persons
FOR EACH ROW 
IF (NEW.permissions LIKE '%Competitor%') && (SELECT COUNT(*) FROM test.competitors WHERE competitor_id LIKE NEW.person_id) = 0 THEN
    INSERT INTO test.competitors(competitor_id)
    VALUES (NEW.person_id);
END IF;
//
DELIMITER ;

它只会产生下列警告：

Warning: #1287 '&&' is deprecated and will be removed in a future release. Please use AND instead

注意：在本地测试时，我确实将main_db更改为test。您可能需要还原该更改。

赞(0）回复(0）举报 2022-11-09

qnzebej02#

您缺少BEGIN和ENDhttps://dev.mysql.com/doc/refman/8.0/en/trigger-syntax.html
在MySQL 8.0上测试

mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.25    |
+-----------+
1 row in set (0.00 sec)

mysql> CREATE TABLE persons
    -> (
    ->     person_id INT,
    ->     first_name VARCHAR(32) DEFAULT NULL,
    ->     last_name VARCHAR(32) DEFAULT NULL,
    ->     permissions SET('Standard', 'Competitor', 'Referee', 'Organizer', 'Director') DEFAULT 'Standard',
    ->     PRIMARY KEY (person_id)
    -> );
Query OK, 0 rows affected (0.78 sec)

mysql>
mysql>
mysql>
mysql> CREATE TABLE competitors
    -> (
    ->     competitor_id INT,
    ->     club VARCHAR(256) DEFAULT NULL,
    ->     license VARCHAR(32) DEFAULT NULL,
    ->     FOREIGN KEY (competitor_id) REFERENCES persons(person_id) ON DELETE CASCADE,
    ->     PRIMARY KEY (competitor_id)
    -> );
Query OK, 0 rows affected (0.52 sec)

mysql> DELIMITER //
mysql>  CREATE TRIGGER tg_make_competitor AFTER UPDATE ON persons
    ->     FOR EACH ROW
    ->     BEGIN
    ->          IF (NEW.permissions LIKE '%Competitor%') && (SELECT COUNT(*) FROM competitors WHERE competitor_id LIKE NEW.person_id) = 0 THEN
    ->                INSERT INTO competitors(competitor_id)
    ->                VALUES (NEW.person_id);
    ->          END IF;
    ->      END//
Query OK, 0 rows affected, 1 warning (0.08 sec)

mysql>  DELIMITER ;

赞(0）回复(0）举报 2022-11-09

du7egjpx3#

您的代码中没有SQL语法错误。我已经在db-fiddle中运行了此代码，它正在工作：

create table persons (person_id int,permissions varchar(200));
 create table competitors (competitor_id int);

 CREATE TRIGGER tg_make_competitor
 AFTER UPDATE ON persons
 FOR EACH ROW 
 IF NEW.permissions = '%Competitor%' && (SELECT COUNT(*) FROM competitors WHERE competitor_id LIKE NEW.person_id) = 0 THEN
     INSERT INTO competitors(competitor_id)
     VALUES (NEW.person_id);
 END IF;

db〈〉小提琴

请检查persion_id的值

赞(0）回复(0）举报 2022-11-09

我来回答

MySQL + phpMyAdmin，IF语句触发器语法错误[重复]

3条答案

相关问题

热门标签

最新问答