Somebody ran into a problem after reading about the MySQL
CREATE statement and the
AUTO_INCREMENT option. They couldn’t get a
CREATE statement to work with an
AUTO_INCREMENT value other than the default of 1. The problem was they were using this incorrect syntax:
CREATE TABLE elvira ( elvira_id int unsigned PRIMARY KEY AUTO_INCREMENT=1001 , movie_title varchar(60)) ENGINE=InnoDB CHARSET=utf8;
It raises this error:
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '=1001 , movie_title varchar(60)) ENGINE=InnoDB CHARSET=utf8' at line 2
They concluded that MySQL requires you to assign a default value of 1 as the initial automatic numbering value; then you use the
ALTER statement to change the initial sequence value. That assumption is incorrect. The problem was with their assignment of an overriding
AUTO_INCREMENT value inside the parenthetical list of columns. That assignment needs to occur after the list of columns and constraints, like
CREATE TABLE elvira ( elvira_id int unsigned PRIMARY KEY AUTO_INCREMENT , movie_title varchar(60)) ENGINE=InnoDB AUTO_INCREMENT=1001 CHARSET=utf8;
It’s not an unlikely mistake since there’s no clear example on either of the referenced web pages (at the time of writing). It would be nice if they were added but I’m of the opinion some of the reference manual pages are too sparse.
After creating the table, you have the generic fix that seems to appear most often as an answer to setting or re-setting the auto numbering sequence of a MySQL table:
ALTER TABLE elvira AUTO_INCREMENT=1001;
Why would you set the auto numbering sequence to something other than 1? Some designers consider it a best practice to increment from a set point like 101 or 1,001 for your Application Programming Interface (API) and they leave a readily identifiable sequence number set for pre- or post-seeded data in tables. The region of preallocated numbers are typically only used in a few of the tables, in any application, but consistently managing sequences across all tables does sometimes simplifies data diagnostics.
Hope this helps somebody looking for a syntax fix. By the way, you can find it on Page 162 of the Oracle Database 11g & MySQL 5.6 Developer Handbook.