change auto_increment within same table using subquery mysql

Question

I am using mysql. I have a database table with auto_increment counter set. Now because of a requirement I need to leave starting 100 ids free and move all existing records starting from 101, so current id 1 will go to 101 and id 2 will become 102 and so on.

I am able to move records to 101 but the problem is that how to change auto_increment counter to max(id)+1.

Main constraint here with me is that I need to do it in single sql statement. I can not save the value using @counter and use it later.

I tried using below query

ALTER TABLE role AUTO_INCREMENT = (SELECT rd.counter FROM (SELECT (MAX(id) + 1) AS counter FROM role r) rd);

But it is not working.

Answer 1

The parser does not support a subquery in the place you are trying to use it.

Here's the excerpt from the MySQL source, from sql/sql_yacc.yy:

create_table_option:
    . . .
    | AUTO_INC opt_equal ulonglong_num

What you should read there is that the AUTO_INCREMENT table option accepts only a single literal number, not an expression or a subquery or a variable or anything else. So you simply can't set the AUTO_INCREMENT in the same statement in which you do SELECT MAX(id)+1.

But you don't have to.

MySQL will never allocate an auto-increment id less than the largest value currently in the table. So if you have a table with id value 102, the next value allocated will be at least 103.

You can even try to set AUTO_INCREMENT=50 explicitly, but that will be increased automatically to MAX(id)+1.

Answer 2

You said in your question

Main constraint here with me is that I need to do it in single sql statement. I can not save the value using @counter and use it later.

Actually you can try using dynamic SQL and see if it works as follows:

SELECT (MAX(id) + 1) INTO @counter FROM role;
SET @sql = CONCAT('ALTER TABLE role AUTO_INCREMENT=',@counter);
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

This may or may not work. Even if it does work for you, Bill Karwin's answer shows that it would not be necessary because of how MySQL already handles auto increment values. No need to manually do what MySQL already does.

Also note the first line in Bill's post

The parser does not support a subquery in the place you are trying to use it.

I wrote a crazy post explaining why in the DBA StackExchange.

Therefore, you should accept Bill's answer because the best thing to do is nothing at all.

Answer 3

According to the maual (http://dev.mysql.com/doc/refman/5.5/en/example-auto-increment.html) the auto_increment value will automatically adjust, when you insert or alter a value in the auto_increment filed manually.

So after giving your records the new id = id+100 you need not care about the auto_increment value anymore...

Answer 4

In response to @Peter, it appears that you will have to update the AUTO_INCREMENT value after changing changing an existing one. The following example was done on MariaDB 10.0.12:

MariaDB [test]> show create table m;
+-------+--------------------------------------------------------+
| Table | Create Table                                           |
+-------+--------------------------------------------------------+
| m     | CREATE TABLE `m` (                                     |
|       |`id` int(10) unsigned NOT NULL AUTO_INCREMENT,          |
|       |`t` varchar(10) NOT NULL DEFAULT '',                    |  
|       |PRIMARY KEY (`id`)                                      |
|       |) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+--------------------------------------------------------+
1 row in set (0.00 sec)

MariaDB [test]> insert into m (t) values ('a'),('b');
Query OK, 2 rows affected (0.03 sec)
Records: 2  Duplicates: 0  Warnings: 0

MariaDB [test]> select * from m;
+----+---+
| id | t |
+----+---+
|  1 | a |
|  2 | b |
+----+---+
2 rows in set (0.00 sec)

MariaDB [test]> update m set id=id+10;
Query OK, 2 rows affected (0.05 sec)
Rows matched: 2  Changed: 2  Warnings: 0

MariaDB [test]> select * from m;
+----+---+
| id | t |
+----+---+
| 11 | a |
| 12 | b |
+----+---+
2 rows in set (0.00 sec)

MariaDB [test]> show create table m;
+-------+--------------------------------------------------------+
| Table | Create Table                                           |
+-------+--------------------------------------------------------+
| m     | CREATE TABLE `m` (                                     |
|       |`id` int(10) unsigned NOT NULL AUTO_INCREMENT,          |
|       |`t` varchar(10) NOT NULL DEFAULT '',                    |  
|       |PRIMARY KEY (`id`)                                      |
|       |) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1 |
+-------+--------------------------------------------------------+
1 row in set (0.00 sec)

MariaDB [test]> insert into m (t) values ('c');
Query OK, 1 row affected (0.04 sec)

MariaDB [test]> select * from m;
+----+---+
| id | t |
+----+---+
|  3 | c |
| 11 | a |
| 12 | b |
+----+---+
3 rows in set (0.00 sec)

MariaDB [test]>