使用触发器将MySQL列添加到表中 [英] adding MySQL column to a table using trigger

问题描述

我有一个看起来像这样的表：

I have a table that looks like this:

计数器：

| client_id | provider1 | provider2 | provider3 |

| client_id | provider1 | provider2 | provider3 |

_-_-_- _-_-_- _-_-_

_ - _ - _- _ - _ - _- _ - _ - _

列名 [provider1，provider2，...] 是提供者的ID，它存储在另一个称为 providers的表中。

the column names [provider1,provider2,...] is the id of provider which is stored in another table called "providers".

我需要的是，当将新的提供程序插入 proviers数据库时，触发器将自动在 Counters表中添加另一列，但是我似乎无法能够得到它。

what i need is, when a new provider is inserted into the "proviers" database a trigger would automatically add another column in the "Counters" table, but I can't seem to be able to get it.

这是我到目前为止所拥有的：

this is what i have so far:

CREATE TRIGGER `providers_AFTER_INSERT` AFTER INSERT ON `providers` FOR EACH ROW
    ALTER TABLE `counters` ADD NEW.prov_id int;

错误是在 NEW之间的。之间；和'prov_id'错误：

the error is at the '.' between 'NEW; and 'prov_id' with the error:

Syntax error: unexpected '.' (dot)

我猜想它需要一个字符串值作为列的名称，所以我四处搜索并找到了一种将其作为字符串传递的方法：

I am guessing it needs a string value as the name of the column so i searched around and found a way to pass it as a string:

CREATE TRIGGER `providers_AFTER_INSERT` AFTER INSERT ON `providers` FOR EACH ROW
    ALTER TABLE `counters` ADD CAST(NEW.prov_id AS CHAR CHARACTER SET utf8) int;

但这在'ADD'中显示了一个错误，表明这是意外的。

but this shows me an error at the 'ADD' saying it is unexpected.

也尝试用方括号 {NEW.prov_id} 包围变量，但无济于事，它在'ADD'处给出相同的错误

also tried surrounding the variable with brackets {NEW.prov_id} but to no avail, it gives the same error at the 'ADD' clause.

也，在搜索我的同时，一些帖子中说它不起作用，而有人说它起作用，而那些说它起作用的人则不使用

also, while searching I also in some posts that says it does not work while some say it does work, and those who say it works don't use a variable for the column name.

如何解决此错误？

我的MySQL是v5.0
我可以根据需要对其进行升级。

my MySQL is v5.0 I can upgrade it, if necessary.

感谢您的进阶

推荐答案

简短答案：不可能。

ALTER TABLE是所谓的 DDL语句（数据定义语言），与 DML语句（数据操作语言）不同。

ALTER TABLE is a so-called "DDL-statement" (data definition language), which differs from "DML-statements" (data manipulation language).

您将需要动态SQL来执行此操作，因为表名在DDL中不是变量。

You would need dynamic SQL to do this, because table names are not variable in DDL.

不幸的是，触发器中不允许使用动态SQL。尝试在触发器中执行prepare语句时，mysql会引发错误：

Unfortunately, dynamic SQL is not allowed in triggers. When trying to execute a prepare statement in a trigger, mysql throws an error:

Dynamic SQL is not allowed in stored function or trigger

更多mysql文档： https://dev.mysql.com/doc/refman/5.7/en/stored-program-restrictions.html

More in the mysql documentation: https://dev.mysql.com/doc/refman/5.7/en/stored-program-restrictions.html

这篇关于使用触发器将MySQL列添加到表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！