MySQL Insert row with column value same as Auto Increment

Question

The id column is auto increment. I want the "rank" column to have the same value as the newly generated auto increment value from the "id" column. (eg. say the next auto increment value is 999... I want the rank to equal this too)

Can I do this in one query? OR do I have to run an additional query to get the auto increment value before

$query = "INSERT INTO $table1(id,name,rank)
VALUES('','bob','999')";

Why would you want 2 columns to have the same value? Seems like you should just reference `id` anywhere you'd need `rank`.... — kittykittybangbang, Jul 15 '15 at 18:16
the only thing i can imagine, is that you want to possibly change rank later on, but id always stays the same. in this case, you could set rank to -1 or NULL or the like in the beginning, and always use the id as rank if you find this value. — hoijui, Jul 15 '15 at 19:19
`rank` changes and will ONLY be the same as `id` on insert. Good point though :) — Bxx, Jul 15 '15 at 19:52

score 5 · Answer 1 · edited May 23 '17 at 12:14

5

You can get actual auto_increment value in one insert using this query:

insert into tablename (name,rank) values ( 'bob',(SELECT AUTO_INCREMENT
FROM information_schema.tables
WHERE table_name = 'tablename'
AND table_schema = DATABASE( ) ) )

See more here - How to get the next auto-increment id in mysql

edited May 23 '17 at 12:14

Community

1
1

answered Jul 15 '15 at 18:18

Ondřej Šotek

1,793
1
15
24

From a performance standpoint, is this faster than running 2 separate queries? One to get the AI value, then one to Insert – Bxx Jul 15 '15 at 18:48
@Bxx in MySQL reading a row of information takes less resources than writing a row of information, hence `SELECT AUTO_INCREMENT ...` should be faster than `UPDATE tablename ...`. – mikl Jul 29 '19 at 19:45
6

Is this guaranteed to be atomic? – mvorisek Sep 02 '19 at 19:22

score 4 · Answer 2 · answered Jul 15 '15 at 18:15

last_insert_id() is only set AFTER the insert completes (successfully or not). If you try something like

INSERT INTO yourtable (id, dupe_id) VALUES (null, last_insert_id())

and hoping that dupe_id gets the same ID that'll be assigned to id, then... no, it won't. last_insert_id() will return the ID creatd by whatever insert was run BEFORE this particular statement.

You'll have to do (basically):

INSERT ...;
UPDATE yourtable SET dupe_id=last_insert_id() WHERE id=last_insert_id();

MySQL Insert row with column value same as Auto Increment

2 Answers2