我需要重命名一些MySQL表,从而弄乱了我的程序。至少我是这样认为的。。。我遇到了以前没有的错误。
有两个表很重要:一个表称为rubrics
,它具有一列以计数与discussions
相关的rubric
。因此,每当插入新的discussion
时,都会触发procedure
来递增计数器。
这是两个表(简而言之):
这是我的表
CREATE TABLE IF NOT EXISTS `rubric` (
`id` INT NOT NULL AUTO_INCREMENT ,
`topic_id` INT NOT NULL ,
`name` VARCHAR(50) NULL ,
`description` TEXT NULL ,
`total_discussions` INT NULL DEFAULT 0 , // this is the COUNTER
`latest_activity_id` INT NULL ,
PRIMARY KEY (`id`, `topic_id`) ,
... // just some index and constraint..
ENGINE = InnoDB;
这是我的讨论表
CREATE TABLE IF NOT EXISTS `discussion` (
`id` INT NOT NULL AUTO_INCREMENT ,
`rubric_id` INT NOT NULL , // as you can see it has a rubric_id column
`topic_id` INT NOT NULL ,
`latest_activity_id` INT NULL DEFAULT 0 ,
`name` VARCHAR(50) NULL ,
`description` TEXT NULL ,
`total_posts` INT NULL DEFAULT 0 ,
`total_views` INT NULL DEFAULT 0 ,
PRIMARY KEY (`id`, `rubric_id`, `topic_id`) ,
... // just some index and constraint..
ENGINE = InnoDB;
这是应在专栏表中增加计数器的过程:
DELIMITER $$
CREATE TRIGGER count_discussions AFTER INSERT ON discussion
FOR EACH ROW
BEGIN
UPDATE rubric SET total_discussions = total_discussions + 1 WHERE rubric.id = discussion.rubric_id;
END $$
插入新行时出现的错误是:
Error code 1054, SQL state 42S22: Unknown column 'discussion.rubric_id' in 'where clause'
因此,我猜测这与该过程的WHERE子句有关,因为,我没有其他WHERE子句提及rubric_id列。但是正如您在讨论表中所看到的,该列存在。那是我不尊重的错字或某些语法吗?
最佳答案
尝试:
DELIMITER $$
CREATE TRIGGER count_discussions AFTER INSERT ON discussion
FOR EACH ROW
BEGIN
UPDATE rubric
INNER JOIN discussion ON rubric.id = discussion.rubric_id
SET total_discussions = total_discussions + 1 ;
END $$
您需要加入表格讨论。
关于mysql - “where子句”中的未知列“discussion.rubric_id”,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/24845475/