两个自动增量列或其他列中的自动增量和相同值 [英] Two autoincrements columns or autoincrement and same value in other column

问题描述

我需要表中的两列在插入时具有相同的值.有没有办法从数据库端做到这一点?

I need two columns in table that would have same value on insert. Is there any way to do it from database side?

推荐答案

所以你想让一个列使用 auto_increment 功能，但让同一个表中的另一列也有相同的值?

So you want to let one column use the auto_increment feature, but make another column in the same table also have the same value?

我想不出您需要此功能的原因.或许您可以解释一下您要实现的目标，我可以提出不同的解决方案?

I can't think of a reason you would need this feature. Perhaps you could explain what you're trying to accomplish, and I can suggest a different solution?

触发器对此不起作用.这是一个先有鸡还是先有蛋的问题:

A trigger won't work for this. It's a chicken-and-egg problem:

• 您不能在 AFTER 触发器中更改任何列的值.
• 但是当 BEFORE 触发器执行时，自动递增值尚未设置.

• You can't change any column's value in an AFTER trigger.
• But the auto-increment value isn't set yet when a BEFORE trigger executes.

使用 MySQL 5.7 GENERATED 列也不起作用:

It also won't work to use a MySQL 5.7 GENERATED column:

CREATE TABLE MyTable (
  id INT AUTO_INCREMENT PRIMARY KEY,
  why_would_you_want_this INT GENERATED ALWAYS AS (id)
);

ERROR 3109 (HY000): Generated column 'why_would_you_want_this' 
cannot refer to auto-increment column.

您不能在单个 SQL 语句中完成.您必须INSERT 行，然后立即执行UPDATE 以将第二列设置为相同的值.

You can't do it in a single SQL statement. You have to INSERT the row, and then immediately do an UPDATE to set your second column to the same value.

CREATE TABLE MyTable (
  id INT AUTO_INCREMENT PRIMARY KEY,
  why_would_you_want_this INT
);

INSERT INTO MyTable () VALUES ();

UPDATE MyTable SET why_would_you_want_this = LAST_INSERT_ID() 
WHERE id = LAST_INSERT_ID();

您也可以使用除 AUTO_INCREMENT 之外的其他机制(例如 Memcached 递增键)生成 ID 值.然后您可以在两列中插入新值:

You could alternatively generate the ID value using some other mechanism besides AUTO_INCREMENT (for example a Memcached incrementing key). Then you could insert the new value in both columns:

INSERT INTO MyTable (id, why_would_you_want_this) VALUES ($gen_id, $gen_id);

这篇关于两个自动增量列或其他列中的自动增量和相同值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！