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

Question

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.

Probably want that trigger to run AFTER insert rather than BEFORE as the userid field doesn't have a value until after the insert is done. — tvanfosson
– tvanfosson, Commented Jan 22, 2009 at 16:44
try this: <code>CREATE TRIGGER default_assignment_self BEFORE INSERT ON users FOR EACH ROW BEGIN SET NEW.default_assignment = (SELECT AUTO_INCREMENT FROM information_schema.TABLES WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='users'); END//</code> — Resegue
– Resegue, Commented Nov 7, 2011 at 22:57
This is real mess... the best solution for me was to update the userid with the value of the primary key immediately after insert. — Elia Weiss
– Elia Weiss, Commented Dec 15, 2020 at 8:16

Jin Lim · Accepted Answer · 2024-10-16 23:21:16Z

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 ) ) );

+1 SET next_id = (SELECT AUTO_INCREMENT FROM information_schema.TABLES WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='tbl'); did the trick for me.
Thank you for your (SELECT AUTO_INCREMENT FROM information_schema.TABLES WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='tbl'). Worked fine for me too
In highly concurrent use innoDB table format, it'll lock the result of the subquery, avoiding one row selecting the autoincremet of the other. I've done some testing in this, placed 1200 requests in a random 10 to 1000 miliseconds, yelding 500 inserts per second, limited by the capacity of the server.
Multi-record is a problem indeed, please point a better solution if you can.
mR_fr0g it doesn't seem to help. I just tested with 10 concurrent looping insert threads and I got over 1024 cases of 2 or 3 duplicates after 25k inserts.

Uri Agassi · Accepted Answer · 2014-04-24 07:48:44Z

3

try this

INSERT INTO users (default_assignment) VALUES (LAST_INSERT_ID()+1);

edited Apr 24, 2014 at 7:48

Uri Agassi

37.5k16 gold badges82 silver badges96 bronze badges

answered Apr 24, 2014 at 7:31

Navrattan Yadav

2213 silver badges5 bronze badges

Comments

David · Accepted Answer · 2020-10-14 12:23:59Z

The only I found that would solve this problem without an extra table would be to calculate self the next number and put that in the fields required.

CREATE TABLE `Temp` ( `id` int(11) NOT NULL auto_increment, `value` varchar(255) , PRIMARY KEY (`idsequence`) ) ENGINE=InnoDB; CREATE TRIGGER temp_before_insert BEFORE INSERT ON `Temp` FOR EACH ROW BEGIN DECLARE m INT; SELECT IFNULL(MAX(id), 0) + 1 INTO m FROM Temp; SET NEW.value = m; -- NOT NEEDED but to be safe that no other record can be inserted in the meanwhile SET NEW.id = m; END;

This seems to be the simplest solution if you're not in a concurrent situation where race conditions for the MAX(id) function may occur

pilif · Accepted Answer · 2009-01-22 13:16:13Z

seeing that last_insert_id() wouldn't work in this case, yes, the trigger would be the only way to accomplish that.

I do ask myself though: What do you need this functionality for? Why do you store the users id twice? Personally, I don't like storing redundant data like this and I'd probably solve this in application code by making that ominous default_assignment column NULL and using the user id in my application code if default_assignment was NULL.

Sias Mey · Accepted Answer · 2009-05-27 09:20:04Z

Actually I just tried to do the same thing as was suggested above. But it seems Mysql doesent generate the inserted ID before the row actually gets commited. So NEW.userid will always return 0 in a Before insert trigger.

The above also wont work unless it is a BEFORE INSERT trigger, since you cant update values in a AFTER INSERT query.

From a Mysql Forum Post It seems the only way to handle this is using an additional table as a sequence. So that your trigger can pull in the values from an external source.

CREATE TABLE `lritstsequence` ( `idsequence` int(11) NOT NULL auto_increment, PRIMARY KEY (`idsequence`) ) ENGINE=InnoDB; CREATE TABLE `lritst` ( `id` int(10) unsigned NOT NULL auto_increment, `bp_nr` decimal(10,0) default '0', `descr` varchar(128) default NULL, PRIMARY KEY (`id`), UNIQUE KEY `dir1` (`bp_nr`) ) ENGINE=InnoDB; DELIMITER $$ DROP TRIGGER /*!50032 IF EXISTS */ `lritst_bi_set_bp_nr`$$ CREATE TRIGGER `lritst_bi_set_bp_nr` BEFORE INSERT ON `lritst` FOR EACH ROW BEGIN DECLARE secuencia INT; INSERT INTO lritstsequence (idsequence) VALUES (NULL); SET secuencia = LAST_INSERT_ID(); SET NEW.id = secuencia; SET NEW.bp_nr = secuencia; END;$$ DELIMITER ; INSERT INTO lritst (descr) VALUES ('test1'); INSERT INTO lritst (descr) VALUES ('test2'); INSERT INTO lritst (descr) VALUES ('test3'); SELECT * FROM lritst; Result: id bp_nr descr ------ ------ ------ 1 1 test1 2 2 test2 3 3 test3

This was copied from forums.mysql.com/read.php?99,186171,186241#msg-186241 but Im not allowed to post links yet.

Dani-Br · Accepted Answer · 2012-02-13 09:12:40Z

basically, the solution is like Resegue said.
But if you want it in one statement, you will use one of the below ways:

1. One long statement:

INSERT INTO `t_name`(field) VALUES((SELECT AUTO_INCREMENT FROM information_schema.TABLES WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='t_name'))

or for text with number:

INSERT INTO `t_name`(field) VALUES(CONCAT('Item No. ',CONVERT((SELECT AUTO_INCREMENT FROM information_schema.TABLES WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='t_name') USING utf8)))

it looks more clearly in PHP:

$pre_name='Item No. '; $auto_inc_id_qry = "(SELECT AUTO_INCREMENT FROM information_schema.TABLES WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='$table')"; $new_name_qry = "CONCAT('$pre_name',CONVERT($auto_inc_id_qry USING utf8))"; mysql_query("INSERT INTO `$table`(title) VALUES($new_name_qry)");

2. Using function: (not tested yet)

CREATE FUNCTION next_auto_inc(table TINYTEXT) RETURNS INT BEGIN DECLARE next_id INT; SELECT AUTO_INCREMENT FROM information_schema.TABLES WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME=table INTO next_id; RETURN next_id; END INSERT INTO users ('username','default_assignment') VALUES ('barry', next_auto_inc('users'))

George · Accepted Answer · 2013-07-27 03:11:07Z

$ret = $mysqli->query("SELECT Auto_increment FROM information_schema.tables WHERE table_schema = DATABASE() ");l while ($row = mysqli_fetch_array($ret)) { $user_id=$row['Auto_increment']; }

cronoklee · Accepted Answer · 2015-09-19 12:33:00Z

You can do this reliably using a simple subquery:

INSERT INTO users ('username','default_assignment') SELECT 'barry', Auto_increment FROM information_schema.tables WHERE TABLE_NAME='users'

jeffbuhrt · Accepted Answer · 2017-02-10 03:51:54Z

I tested the above trigger idea with 10 concurrent threads doing inserts and I got over 1000 cases of 2 or 3 duplicates after ~25k inserted.

DROP TABLE IF EXISTS test_table CASCADE; CREATE TABLE `test_table` ( `id` INT NOT NULL AUTO_INCREMENT, `update_me` VARCHAR(36), `otherdata` VARCHAR(36) NOT NULL, PRIMARY KEY (`id`) ) ENGINE = InnoDB DEFAULT CHARSET = utf8 COMMENT 'test table for trigger testing'; delimiter $$ DROP TRIGGER IF EXISTS setnum_test_table; $$ CREATE TRIGGER setnum_test_table BEFORE INSERT ON test_table FOR EACH ROW -- SET OLD.update_me = CONCAT(NEW.id, 'xyz'); BEGIN DECLARE next_id INT; SET next_id = (SELECT AUTO_INCREMENT FROM information_schema.TABLES WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='test_table' LOCK IN SHARE MODE ); -- SET NEW.update_me = CONCAT(next_id, 'qrst'); SET NEW.update_me = next_id; END $$ delimiter ; -- SELECT AUTO_INCREMENT FROM information_schema.TABLES WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='test_table' INSERT INTO test_table (otherdata) VALUES ('hi mom2'); SELECT count(*) FROM test_table; SELECT * FROM test_table; -- select count(*) from ( select * from ( SELECT count(*) as cnt ,update_me FROM test_table group by update_me) q1 where cnt > 1 order by cnt desc

I used 10 of:

while true ; do echo "INSERT INTO test_table (otherdata) VALUES ('hi mom2');" | mysql --user xyz testdb ; done &

And ran the last query to watch for duplicates

example output: '3', '4217' '3', '13491' '2', '10037' '2', '14658' '2', '5080' '2', '14201' ...

Note 'LOCK IN SHARE MODE' didn't change anything. With and without gave duplicates at about the same rate. It seems that MySQL AUTO_INCREMENT doesn't work like Postgres' next_val() and is NOT concurrency safe.

Vinzz · Accepted Answer · 2017-08-20 19:48:26Z

I know this post is from 2010, but I couldn't find a good solution. I've solved this by creating a separate table that holds the counters. When I need to generate an unique identifier for a column I just call a Stored proc:

CREATE DEFINER=`root`@`localhost` PROCEDURE `IncrementCounter`(in id varchar(255)) BEGIN declare x int; -- begin; start transaction; -- Get the last counter (=teller) and mark record for update. select Counter+1 from tabel.Counter where CounterId=id into x for update; -- check if given counter exists and increment value, otherwise create it. if x is null then set x = 1; insert into tabel.Counters(CounterId, Counter) values(id, x); else update tabel.Counters set Counter = x where CounterId = id; end if; -- select the new value and commit the transaction select x; commit; END

The 'for update' statement locks the row in the counters table. This avoids duplicates being made by multiple threads.

Collectives™ on Stack Overflow

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

10 Answers 10

7 Comments

Comments

1 Comment

Comments

Comments

Comments

Comments

Comments

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

10 Answers 10

7 Comments

Comments

1 Comment

Comments

Comments

Comments

Comments

Comments

Comments

Comments

Linked

Related