How to lock auto_increment value?

Question

I have a query that returns the next auto-increment value (id), and I use that value when I'm inserting data in table t_name.

SELECT AUTO_INCREMENT id
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'db_name'
AND TABLE_NAME = 't_name'

But I want that this query gives a different value each time. E.g. Me and my pal are inserting data in db at same time, so I will get one id, he will get another. When I run this query, I want it to give me a different and incremented value each time.

Is it possible? Or do I have to create tables with sequences?

Just let the database handle auto incrementing ids and you will be fine. — Marvin, Jun 16 '15 at 10:09
I need that id before i insert data. Postgres have sequences, I need something similar im mysql — Stiglic, Jun 16 '15 at 10:10
Define the id column as `auto_increment` and skip it while inserting. https://dev.mysql.com/doc/refman/5.7/en/example-auto-increment.html Why would you need the id before inserting? — Marvin, Jun 16 '15 at 10:11
Of course it is auto increment, it is obvious. Did you work with sequences in Postgres, because I think that you dont understand my question? — Stiglic, Jun 16 '15 at 10:15
I need it because I dont want to use mysql_insert_id() or LAST_INSERT_ID() ... — Stiglic, Jun 16 '15 at 10:16
You do not want to but it is the correct system. That said you can set the id without auto-increment and then get the max id and do a +1 to have your new id before insert. This is not concurrent with other session ok? — Goikiu, Jun 16 '15 at 10:18
Correct, I don't understand your question. Although I'm not yet sure it's because of Postgres. Anyway, maybe this helps: http://stackoverflow.com/questions/9046971/mysql-equivalent-of-oracles-sequence-nextval — Marvin, Jun 16 '15 at 10:19
That's it :))) But i have decided to create sequence table to track sequences. Thanks! And sorry I confused you :) — Stiglic, Jun 16 '15 at 10:41

score 0 · Answer 1 · answered Jun 16 '15 at 10:19

You can insert and than get the last inserted id:

    $connection  = mysqli_connect($rv, $username, $pass, $mydatabase);
    $result = $connection->query('INSERT INTO mytable (id, name) VALUES("", "myName")');
    if($result)
    {    $lastId = connection->insert_id;
         // so something with $lastId...
    }

How to lock auto_increment value?

1 Answers1