mysql - 在 CREATE TRIGGER 的 ALTER TABLE 中使用查询

Question

我正在尝试创建动态 ALTER TABLE 命令，但某些命令将从查询生成。问题是我想在触发器中使用它!

尝试1:

ALTER TABLE `redinfomanager` CHANGE `Unterkategorie` `Unterkategorie` ENUM(("SELECT GROUP_CONCAT(CONCAT('\'', REPLACE(`Unterkategorien`, '\r\n', '\',\''), '\'') SEPARATOR  ',') FROM `kategorien` GROUP BY '1'")) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL;

尝试2:

SELECT @tmp:=GROUP_CONCAT(CONCAT('\'', REPLACE(`Unterkategorien`, '\r\n', '\',\''), '\'') SEPARATOR  ',') FROM `kategorien` GROUP BY '1';
SET @query=CONCAT('ALTER TABLE `redinfomanager` CHANGE `Unterkategorie` `Unterkategorie` ENUM(', @tmp, ') CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL');
PREPARE stmt FROM @query;
EXECUTE stmt;

尝试3:

SET @kat = (SELECT GROUP_CONCAT(CONCAT('\'', REPLACE(`Unterkategorien`, '\r\n', '\',\''), '\'') SEPARATOR  ',') FROM kategorien GROUP BY '1');
PREPARE stmt FROM 'ALTER TABLE redinfomanager CHANGE Unterkategorie Unterkategorie ENUM(?) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL';
EXECUTE stmt USING @kat;

它告诉我:

1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '; PREPARE stmt FROM 'ALTER TABLE redinfomanager CHANGE Unterkategorie `Unte' at line 1

我怎样才能做到这一点？

这个错误代表什么？

Answer 1

您似乎在第一个表上的每次插入后更改第二个表上的 ENUM 值？为什么不直接将其设为 foreign key constraint 。这将要求您要放入表 2 中的任何值都必须具有表 1 中的值。这也会更加高效:

举个例子( sqlfiddle link ):

CREATE TABLE category (
  id INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
  description VARCHAR(50)
);

CREATE TABLE thing (
  id INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
  cat_id INTEGER,
  description VARCHAR(50),
  FOREIGN KEY (cat_id) REFERENCES category(id)
);

INSERT INTO category (description) 
VALUES 
   ('Category 1')
  ,('Category 2');

INSERT INTO thing (cat_id, description)
VALUES 
   (1, 'Thing 1')
  ,(1, 'Thing 2')
  ,(2, 'Thing 3');

INSERT INTO thing (cat_id, description)
VALUES
   (3, 'Imma Fail!')

如果运行它，第三次插入将会失败，因为 category 表中没有 id 3。