Minimum length for auto increment ID field in MySQL

How can I set a minimum length for my primary key ID which is auto incremented. Now the auto increment starts at 1 and goes up from there. However I would li开发者_StackOverflowke the id to be at least 5 characters long. So it would start at 10001, 10002, 10003 etc

If you have the table, but not the column run the following code with the appropriate modifications:

ALTER TABLE MyTableName 
   ADD MyTableNameId INT NOT NULL AUTO_INCREMENT,
   ADD INDEX (MyTableNameId);

If you have already created the column, you can do this:

ALTER TABLE MyTableName AUTO_INCREMENT = 10001;

Beginning with MySQL 5.0.3, InnoDB supports the AUTO_INCREMENT = N table option in CREATE TABLE and ALTER TABLE statements.

You can't define a length, but you can specify what value it starts with.

At declaration time:

CREATE TABLE test
(
    id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    whatever VARCHAR(10),
    ...
) AUTO_INCREMENT = 100000;

Or, after declaring / at run time:

ALTER TABLE test AUTO_INCREMENT=200000;