MySQL触发器在插入具有新的自动增量ID的更新列之前 [英] MySQL trigger before insert update column with new auto-increment id

问题描述

我想用国家代码和新的id(这是一个自动递增的值)更新表中的列.

I'd like to update a column in my table with the country code and the new id which is an auto-increment value.

BEGIN
SET new.key = concat(new.countryCode,new.id);
END

countryCode可以正常工作，但是id始终为空白.我该如何实现这样的目标? id来自autoincrement列.

countryCode works fine, but the id is always blank. How can I achieve something like this? The id comes from an autoincrement column.

我知道它不起作用，因为它是在插入后生成的.那我该怎么做呢?

I know it's not working because it's generated after the insert. So how can I do something like this?

推荐答案

AUTO_INCREMENT列仅在插入之后设置.

如果您需要访问该值，则只能在AFTER INSERT触发器中.但是，您不能在AFTER UPDATE触发器中修改列值...

If you need to access that value, you can only in an AFTER INSERT trigger. However, you cannot modify a column value in an AFTER UPDATE trigger...

此外，您不能以(

在存储的函数或触发器中，不允许修改 表格已经被(读取或写入)使用的表格 调用该函数或触发器的语句.

Within a stored function or trigger, it is not permitted to modify a table that is already being used (for reading or writing) by the statement that invoked the function or trigger.

---

这里唯一合理的解决方案是创建一个存储过程来更新表，调整事务中的相关列以模拟"您的原子插入语句.

---

Here the only reasonable solution would be to create a stored procedure to update the table, adjusting the relevant columns in a transaction to "emulate" you atomic insert statement.

也就是说，在您的特定情况下，key列是多余的，因为该列只是同一行中其他两个列的串联.

That being said, in your particular case, the key column is redundant as that column is just the concatenation of two other columns of the same row.

给出它的名字，您不是在寻找一种创建复合键的方法吗?像这样的东西:

Given its name, aren't you looking for a way to create a compound key instead? Something like that:

ALTER TABLE tbl ADD UNIQUE KEY (countryCode, id);

这篇关于MySQL触发器在插入具有新的自动增量ID的更新列之前的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！