sqlmysqlinsertauto-increment

Can you access the auto increment value in MySQL within one statement?


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.

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'.

e.g.

I'd like a statement like this:

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

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?

Thanks!

Update:

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.

Update:

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;

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

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.


Solution

  • 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;  -- Specify the data type
       SET information_schema_stats_expiry = 0; -- Without it, Won't work at MySQL8
       SET next_id = (SELECT AUTO_INCREMENT FROM information_schema.TABLES WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='tbl');
       SET NEW.field = next_id;
    END
    

    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 information_schema_stats_expiry = 0; -- Without it, Won't work at MySQL8
       SET NEW.field=(SELECT AUTO_INCREMENT FROM information_schema.TABLES WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='tbl');
    END
    

    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 information_schema_stats_expiry = 0; -- Without it, Won't work at MySQL8
       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');
    

    (*) To create a hash:

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