您可以在一条语句中访问MySQL中的自动增量值吗? [英] Can you access the auto increment value in MySQL within one statement?
问题描述
我有一个包含用户表的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"的用户ID"字段中创建的值.
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 INT;
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 ...)
(*) To auto-name an image:
SET NEW.field = CONCAT('image_', next_id, '.gif');
(*) To create a hash:
SET NEW.field = CONCAT( MD5( next_id ) , MD5( FLOOR( RAND( ) *10000000 ) ) );
这篇关于您可以在一条语句中访问MySQL中的自动增量值吗?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!