1

I was wondering if there is a simple way to manually increase the auto increment vaulue of a MySQL column by one.

The use case would be to keep the indexes in sync of two tables. In my case they usually both get a new entry at the same time. But sometimes only one of them gets a new entry. This causes the IDs to desync, which is not what I want. Due to limitations in the code I cannot retrieve the ID of any inserted rows, as there's a fair amount of limitations on the API I use to communicate with the database. I can however execute queries to my hearts content. So a simple query to bump the value by one would be much apprechiated!

2
  • So, lets say one table has IDs 1,2,3,4,7,8 and the other has 1,2,4,7,8,9. Do you want the IDs to be updated so they match? Jun 12 '18 at 15:27
  • just insert a record then delete it?
    – Jasen
    Jun 14 '18 at 7:26
1

I make no promises with this one, but you might get away with the following:

For a table mytable in the mydb database

SET @src_db = 'mydb';
SET @src_tb = 'mytable';
SELECT CONCAT('ALTER TABLE ',@src_db,'.',@src_tb,' AUTO_INCREMENT=',auto_increment+1)
INTO @sql FROM information_schema.tables
WHERE table_schema=@src_db AND table_name=@src_tb;
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

After running this, you can verify with

SHOW CREATE TABLE mydb.mytable\G

I just tried this with a table ganeshbora.customer on my Windows laptop (5.7.12)

SAMPLE CODE

SET @src_db = 'ganeshbora';
SET @src_tb = 'customer';
SHOW CREATE TABLE ganeshbora.customer\G
SELECT CONCAT('ALTER TABLE ',@src_db,'.',@src_tb,' AUTO_INCREMENT=',auto_increment+1)
INTO @sql FROM information_schema.tables
WHERE table_schema=@src_db AND table_name=@src_tb;
SELECT @sql;
PREPARE s FROM @sql;
EXECUTE s;
DEALLOCATE PREPARE s;
SHOW CREATE TABLE ganeshbora.customer\G

SAMPLE CODE EXECUTED

mysql> SET @src_db = 'ganeshbora';
Query OK, 0 rows affected (0.00 sec)

mysql> SET @src_tb = 'customer';
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW CREATE TABLE ganeshbora.customer\G
*************************** 1. row ***************************
       Table: customer
Create Table: CREATE TABLE `customer` (
  `cust_id` int(11) NOT NULL AUTO_INCREMENT,
  `firstname` varchar(20) DEFAULT NULL,
  `lastname` varchar(20) DEFAULT NULL,
  `address` varchar(40) DEFAULT NULL,
  PRIMARY KEY (`cust_id`)
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> SELECT CONCAT('ALTER TABLE ',@src_db,'.',@src_tb,' AUTO_INCREMENT=',auto_increment+1)
    -> INTO @sql FROM information_schema.tables
    -> WHERE table_schema=@src_db AND table_name=@src_tb;
Query OK, 1 row affected (0.00 sec)

mysql> SELECT @sql;
+---------------------------------------------------+
| @sql                                              |
+---------------------------------------------------+
| ALTER TABLE ganeshbora.customer AUTO_INCREMENT=15 |
+---------------------------------------------------+
1 row in set (0.00 sec)

mysql> PREPARE s FROM @sql;
Query OK, 0 rows affected (0.00 sec)
Statement prepared

mysql> EXECUTE s;
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DEALLOCATE PREPARE s;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW CREATE TABLE ganeshbora.customer\G
*************************** 1. row ***************************
       Table: customer
Create Table: CREATE TABLE `customer` (
  `cust_id` int(11) NOT NULL AUTO_INCREMENT,
  `firstname` varchar(20) DEFAULT NULL,
  `lastname` varchar(20) DEFAULT NULL,
  `address` varchar(40) DEFAULT NULL,
  PRIMARY KEY (`cust_id`)
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql>

As shown, the auto_increment of the table was incremented from 14 to 15. Of course, I did this with all privileges:

mysql> SELECT user(),current_user();
+---------------------------+---------------------------+
| user()                    | current_user()            |
+---------------------------+---------------------------+
| rolandomysqldba@localhost | rolandomysqldba@localhost |
+---------------------------+---------------------------+
1 row in set (0.00 sec)

mysql> SHOW GRANTS;
+--------------------------------------------------------------------------------+
| Grants for rolandomysqldba@localhost                                           |
+--------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'rolandomysqldba'@'localhost' WITH GRANT OPTION |
+--------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql>

So, if you have the necessary grants to do this ...

GIVE IT A TRY !!!

UPDATE 2018-06-12 14:57 EDT

A comment from JohnathanFite brings up a valid point:

How is this going to be safe for multiple users at a time? I can see using it to fix it when it's out of sync, but at least warn the OP that running this in a multi-user environment is going to cause problems

While this answer will fix the AUTO_INCREMENT in a perfect world, we don't live in a perfect world. You would need application downtime (1-2 min) to freeze writes and current auto_increment values. If you attempt my answer in a live system, you can quickly screw up auto_increment values because rollbacks of INSERTs against a table with auto_increment values will produce gaps. In many cases, the gap increment may be more than 1 due to bulk inserts that rollback.

Please read my old post from May 12, 2015 : MySQL auto increment column increases after insertion error occurs to see how auto_increment gaps are generated.

6
  • Man. I really hoped this would be simpler...
    – BrainStone
    Jun 12 '18 at 15:16
  • This is just doing it in pure Dynamic SQL in MySQL. If you are using Java/Javascript/PHP or some other language, you just code two SQL statements and do the following: 1) Retrieve result of SELECT auto_increment+1 FROM information_schema.tables ..., 2) ALTER TABLE db.tb AUTO_INCREMENT=newnumber. Jun 12 '18 at 15:27
  • How is this going to be safe for multiple users at a time? I can see using it to fix it when it's out of sync, but at least warn the OP that running this in a multi-user environment is going to cause problems. Jun 12 '18 at 17:17
  • @JonathanFite very valid point. I will update my answer in light of your comment Jun 12 '18 at 18:51
  • Would inserting and immediately deleteing a dummy entry work too? Like INSERT INTO tb (id) VALUES (NULL); DELETE FROM tb WHERE id = LAST_INSERT_ID()
    – BrainStone
    Jun 13 '18 at 6:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.