Search code examples
mysqlsqlauto-increment

How to insert id's number in the same row?

I'm trying to put the number of id in the neighbor column. Something like this:

+----+-------------------+
| id | identical_with_id |
+----+-------------------+

id is AUTO INCREMENT and all need is getting id's number when inserting. I can do that like this:

INSERT INTO `table_name`
  VALUES( NULL, 
          (SELECT `AUTO_INCREMENT` FROM INFORMATION_SCHEMA.TABLES 
             WHERE TABLE_SCHEMA = 'database_name' AND
                   TABLE_NAME   = 'table_name')
        );

But I don't know it will work all the time. Maybe between executing that sub-query and inserting that row, a new row (another row) get insert. In this case the number of id will not be the same as neighbor column. Hum? is that possible?

Note: Please don't tell me: "what do you need this for?"

Solution

  • Usually everyone sometimes need to do such things. And it's best to not ask for reasons :)

    I think you are doing it in correct way but you need to check which MySql engine you are using and make sure that inserts are executed sequentially.

    Usually insert statement will lock the table and unlock when it's finished. That means your query for retrieving auto increment is pretty much safe.

    For MySql engine and related settings this post can help you:

    Does a MySQL multi-row insert grab sequential autoincrement IDs?

    For InnoDb:

    http://dev.mysql.com/doc/refman/5.1/en/innodb-auto-increment-handling.html