你能在一个语句中访问 MySQL 中的自动增量值吗?

2022-01-09 00:00:00 sql insert mysql auto-increment

我有一个包含用户表的 MySQL 数据库.表的主键是'userid'，设置为自增字段.

I have a MySQL database which contains a table of users. The primary key of the table is 'userid', which is set to be an auto increment field.

当我将新用户插入表中时，我想做的是使用与自动增量在不同字段default_assignment"的userid"字段中创建的相同值.

What I'd like to do is when I insert a new user into the table is to use the same value that the auto increment is creating in the 'userid' field in a different field, 'default_assignment'.

例如

我想要这样的声明:

INSERT INTO users ('username','default_assignment') VALUES ('barry', value_of_auto_increment_field())

所以我创建了用户Barry"，userid"生成为 16(例如)，但我也希望default_assignment"具有相同的值 16.

so I create user 'Barry', the 'userid' is generated as being 16 (for example), but I also want the 'default_assignment' to have the same value of 16.

请问有什么方法可以实现吗?

Is there any way to achieve this please?

谢谢！

更新:

感谢您的回复.default_assignment 字段不是多余的.default_assigment 可以引用 users 表中的任何用户.创建用户时，我已经有一个允许选择另一个用户作为 default_assignment 的表单，但是在某些情况下需要将其设置为同一用户，因此我的问题是.

Thanks for the replies. The default_assignment field isn't redundant. The default_assigment can reference any user within the users table. When creating a user I already have a form that allows a selection of another user as the default_assignment, however there are cases where it needs to be set to the same user, hence my question.

更新:

好的，我已经尝试了更新触发器建议，但仍然无法使其正常工作.这是我创建的触发器:

Ok, I've tried out the update triggers suggestion but still can't get this to work. Here's the trigger I've created:

CREATE TRIGGER default_assignment_self BEFORE INSERT ON `users`  
FOR EACH ROW BEGIN
SET NEW.default_assignment = NEW.userid;
END;

当插入新用户时，default_assignment 始终设置为 0.

When inserting a new user however the default_assignment is always set to 0.

如果我手动设置用户 ID，那么 default_assignment 确实会设置为用户 ID.

If I manually set the userid then the default_assignment does get set to the userid.

因此，自动分配生成过程显然发生在触发器生效之后.

Therefore the auto assignment generation process clearly happens after the trigger takes effect.

推荐答案

不用再建表，max()根据表的auto_increment值会有问题，这样做:

there's no need to create another table, and max() will have problems acording to the auto_increment value of the table, do this:

CREATE TRIGGER trigger_name BEFORE INSERT ON tbl FOR EACH ROW
BEGIN
   DECLARE next_id;
   SET next_id = (SELECT AUTO_INCREMENT FROM information_schema.TABLES WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='tbl');
   SET NEW.field = next_id;
END

我声明 next_id 变量是因为它通常会以其他方式使用 (*)，但您可以直接执行 new.field=(select ...)

I declare the next_id variable because usually it will be used in some other way(*), but you could do straight new.field=(select ...)

CREATE TRIGGER trigger_name BEFORE INSERT ON tbl FOR EACH ROW
BEGIN
   SET NEW.field=(SELECT AUTO_INCREMENT FROM information_schema.TABLES WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='tbl');
END

同样在(SELECT 字符串字段)的情况下，您可以使用 CAST 值；

Also in cases of (SELECT string field) you can use CAST value;

CREATE TRIGGER trigger_name BEFORE INSERT ON tbl FOR EACH ROW
BEGIN
   SET NEW.field=CAST((SELECT aStringField FROM information_schema.TABLES WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='tbl') AS UNSIGNED);
END

(*) 自动命名图像:

(*) To auto-name an image:

SET NEW.field = CONCAT('image_', next_id, '.gif');

(*) 创建哈希:

SET NEW.field = CONCAT( MD5( next_id ) , MD5( FLOOR( RAND( ) *10000000 ) ) );

相关文章