1

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?"

stack
  • 10,280
  • 19
  • 65
  • 117

2 Answers2

2

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

Community
  • 1
  • 1
IMujagic
  • 1,229
  • 10
  • 22
  • very and very good ..! This sentence is exactly what I was looking for: *insert statement will lock the table and unlock when it's finished*. upvote – stack May 11 '16 at 08:08
  • Look, I don't know English very well. My storage engine is InnoDB. In that link you've attached, is InnoDB safe for my purpose? – stack May 11 '16 at 08:12
  • But first check your engine and related settings. As you can see in that post. For InnoDb you need to do some changes.. – IMujagic May 11 '16 at 08:12
  • I have updated answer with InnoDb link. There you can find steps for setting it up. – IMujagic May 11 '16 at 08:14
  • What about using `LOCK TABLES ...` *(I'm using innodb engine)* ? http://stackoverflow.com/questions/8481421/does-a-mysql-multi-row-insert-grab-sequential-autoincrement-ids#8481629` – stack May 11 '16 at 08:25
  • I personally don't like this approach because you need manually to say lock and unlock. Sometimes you can forget to unlock and then party is starting :) I don't know. If you like it, and if you are sure that you can always unlock then go for it... – IMujagic May 11 '16 at 08:29
  • According to documentation the entire table IS NOT locked unless there are no indexes at all = https://dev.mysql.com/doc/refman/8.0/en/innodb-locks-set.html – Jose Manuel Abarca Rodríguez Dec 28 '22 at 16:29
1

You could use LAST_INSERT_ID() right after your query.

INSERT INTO `table_name` VALUES( NULL, NULL);
SELECT LAST_INSERT_ID() INTO @last_id;
UPDATE `table_name` SET duplicate_id_column = @last_id WHERE id = @last_id;
Ralph Melhem
  • 767
  • 5
  • 12
  • Still I fear between executing my insert-query and executing my update-query, another row get insert and then those two column won't be match. – stack May 11 '16 at 07:58
  • Which is why i added the verification WHERE id = @last_id; – Ralph Melhem May 11 '16 at 07:59
  • Ah .. Can I write both `insert` and `update` statements in one query? – stack May 11 '16 at 08:00
  • @stack You could have a transaction. – Aconcagua May 11 '16 at 08:02
  • I think you're pretty safe if you execute the queries subsequently. mysql docs suggest the same method: https://dev.mysql.com/doc/connector-odbc/en/connector-odbc-usagenotes-functionality-last-insert-id.html If you still have any fears concerning that and it's absolutely crucial to your app, i suggest doing it from the app side, even if the execution time takes a bit longer. – Ralph Melhem May 11 '16 at 08:08