6 digit numeric column in MySQL

Question

Say I want to maintain a 6 digit ID column for a table in my database -is it possible to specify this at the DB level in MySQL?

Answer 1

Use the MEDIUMINT data type, or more exact, MEDIUMINT(6):

A medium-sized integer. The signed range is -8388608 to 8388607. The unsigned range is 0 to 16777215.

As you are going to use it as an ID column, you most likely want to make it UNSIGNED NOT NULL auto_increment.

Answer 2

You can set the initial value of the AUTO_INCREMENT column so that it starts off at 100000:

CREATE TABLE tbl (
    id INT NOT NULL AUTO_INCREMENT, 
    ...
);

ALTER TABLE tbl AUTO_INCREMENT = 100000;

This way, the first number to be inserted will be 100000, the next will be 100001, etc.

If that's not what you're after, you'll have to be more specific...