MySQL – How to Manually Increase AUTO_INCREMENT Column by One

auto-incrementMySQL

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!

Best Answer

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.

Related Question