I am migrating a MariaDB database from my personal machine to a test
server. The database schema has changed so I need to recreate
the
tables. The source database is also a mix of data that I want to keep
and a lot of test junk that I weeded out of the dump text file
manually. Since I dropped a lot of data, I want to update the AUTO_INCREMENT
value back to the new maximum.
This works:
MariaDB [imok]> alter table accounts AUTO_INCREMENT 114;
Query OK, 11 rows affected (0.04 sec)
Records: 11 Duplicates: 0 Warnings: 0
But when I try to create a generic statement using a select, MariaDB complains:
MariaDB [imok]> alter table accounts AUTO_INCREMENT (select max(AcctID)+1 from accounts);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '(select max(AcctID)+1 from accounts)' at line 1
I tried using a variable, with the same result:
MariaDB [imok]> set @autoinc=(select max(AcctID)+1 from accounts);
Query OK, 0 rows affected (0.00 sec)
MariaDB [imok]> select @autoinc;
+----------+
| @autoinc |
+----------+
| 114 |
+----------+
1 row in set (0.00 sec)
MariaDB [imok]> alter table accounts AUTO_INCREMENT @autoinc;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '@autoinc' at line 1
QUESTION: How do I set the AUTO_INCREMENT
value to something other than
a literal value
in MariaDB?
NOTE: I am using MariaDB not MySQL.
NOTE: The '='
in the ALTER
statement is optional, MariaDB gives the same complaint
with or without it; setting AUTO_INCREMENT
using a literal value 113
works
without or without the '='
.