gpt4 book ai didi

MySQL 触发器语法错误 IF 存在

转载 作者:行者123 更新时间:2023-11-29 13:28:32 29 4
gpt4 key购买 nike

我有一个正在尝试创建的 MySQL 触发器,但无法获得正确的语法。

触发器应该遍历一组关键字并将其与插入数据库的新帖子的标题进行匹配。如果找到匹配项,它应该将新帖子分配给该存储桶并更新存储桶的关键字集。如果找不到匹配项,则应使用帖子中的值创建一个新存储桶。错误指向以 IF EXISTS 开头的行,但我无法弄清楚出了什么问题。任何帮助将不胜感激。谢谢!

<小时/>

更新:代码已更新为提取 IF EXISTS,但现在我从 ELSE 语句开始收到错误。

delimiter $$

CREATE TRIGGER bucket_trigger_v1 BEFORE INSERT ON posts
FOR EACH ROW
BEGIN
set @target = NEW.post_title;
set @bucket_id_number = '';
set @bucket_relevance = '';

SELECT idbuckets, MATCH (keywords) AGAINST (@target) AS score INTO @bucket_id_number, @bucket_relevance FROM buckets WHERE MATCH (keywords) AGAINST (@target) ORDER BY score DESC LIMIT 1;
IF (@bucket_id_number != '') THEN
BEGIN
SET NEW.buckets_idbuckets = @bucket_id_number;
UPDATE buckets SET keywords = concat(keywords, @target) WHERE idbuckets = @bucket_id_number;
END
ELSE
BEGIN
INSERT INTO buckets (idbuckets, keywords, creationdate) VALUES (, @target, NEW.postdate);
SET NEW.bucket_idbuckets = (SELECT LAST_INSERT_ID());
END
END IF;
END;

$$

最佳答案

需要考虑的几点:

13.2.9.1. SELECT ... INTO Syntax

An INTO clause should not be used in a nested SELECT because such a SELECT must return its result to the outer context.

19.3.1. Trigger Syntax and Examples

Such a SET statement has no effect in an AFTER trigger because the row change will have already occurred.

更新

以下代码正确创建触发器。创建 posts 表只是为了创建触发器。

/*Table structure for table `posts` */

DROP TABLE IF EXISTS `posts`;

CREATE TABLE `posts` (
`post_title` VARCHAR(500) DEFAULT NULL,
`buckets_idbuckets` INT(11) DEFAULT NULL,
`postdate` DATETIME DEFAULT NULL
) ENGINE=INNODB;

/* Trigger structure for table `posts` */

DELIMITER $$

/*!50003 DROP TRIGGER*/ /*!50032 IF EXISTS */ /*!50003 `bucket_trigger_v1` */$$

CREATE TRIGGER `bucket_trigger_v1` BEFORE INSERT ON `posts`
FOR EACH ROW
BEGIN
SET @target := new.`post_title`;
SET @bucket_id_number := '';
SET @bucket_relevance := '';
SELECT `idbuckets`, MATCH (`keywords`) AGAINST (@target) AS `score` INTO @bucket_id_number, @bucket_relevance FROM `buckets` WHERE MATCH (`keywords`) AGAINST (@target) ORDER BY `score` DESC LIMIT 1;
IF (@bucket_id_number != '') THEN
SET new.`buckets_idbuckets` := @bucket_id_number;
UPDATE `buckets` SET `keywords` = CONCAT(`keywords`, @target) WHERE `idbuckets` = @bucket_id_number;
ELSE
INSERT INTO `buckets` (`keywords`, `creationdate`) VALUES (@target, new.`postdate`);
SET new.`buckets_idbuckets` := LAST_INSERT_ID();
END IF;
END$$

DELIMITER ;

关于MySQL 触发器语法错误 IF 存在,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/19797442/

29 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com