Details
-
New Feature
-
Status: Open (View Workflow)
-
Minor
-
Resolution: Unresolved
-
None
-
None
-
None
Description
I cannot find a way to alter the auto_increment value of a table using a variable which would be useful when populating the value from a script. A use case would be a table in which you remove default value derived from sequence, then adding an auto_increment.
This is what the problem looks like:
drop table if exists account_ledger;
|
CREATE TABLE account_ledger (
|
id INT PRIMARY KEY AUTO_INCREMENT,
|
transaction_name VARCHAR(100));
|
insert into account_ledger values(null,'x'),(null,'y'),(null,'z');
|
select max(id)+100 into@VAR from account_ledger;
|
alter table account_ledger AUTO_INCREMENT=@VAR;
|
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 '@VAR' at line 1
|
If you do this, no error:
alter table account_ledger AUTO_INCREMENT=103;
|
Anything other than a simple integer will produce an error. You cannot do math or use a variable value.