[SOLVED] MySQL fails to set auto_increment start value > 1

qajaq · 04-28-2017, 11:34 AM

I've created a database and table ("sesame") in MySQL and populated it with 55 pre-existing values from a former spreadsheet. Now I'm writing a PHP script that will allow additions to the table in the future. I have a field ("id") to hold a unique identifier for each record. The existing records have "id" values from 1 to 55, so I want future additions to start the auto_incrementing from 56.

But when I give the following command from the MySQL CLI

Code:

ALTER TABLE sesame MODIFY COLUMN id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT = 56;

I get an error message that tells me to look for the "right syntax to use near '= 56' at line 1".

I've tried various permutations of the MODIFY TABLE command, but keep getting error messages. Any ideas what I should be using?

hydrurga · 04-28-2017, 01:03 PM

As far as I am aware, you apply the initial auto-increment value to the whole table once you have defined the auto increment column.

So:

Code:

ALTER TABLE sesame MODIFY COLUMN id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT;

ALTER TABLE sesame AUTO_INCREMENT = 56;

qajaq · 04-28-2017, 01:08 PM

Thank you, hydrurga! That worked. Other sites I'd found included a reference to the column name, so I had never thought to try it without that.

hydrurga · 04-28-2017, 01:10 PM

Quote:

Originally Posted by qajaq

Thank you, hydrurga! That worked. Other sites I'd found included a reference to the column name, so I had never thought to try it without that.

My pleasure, qajaq.