I’ve used MySQL for many years and I’ve (nearly) always used AUTO_INCREMENT to get unique primary keys for my tables.
I came across a requirement earlier today that meant that I needed an id to start from 100000, instead of the the usual 1.
Normally I’d have just created a dummy record with an id of 100000 so the next insert would occur at 100001. This works as AUTO_INCREMENT automatically inserts the current highest value plus 1 for a column when you insert a row with NULL or 0 for the row’s value. This isn’t very elegant so I spent a few minutes with the MySQL manual and found that it is possible to seed the AUTO_INCREMENT to start at a specific value without having to use a dummy insert.
When creating a table you add a seed value at the end of the create statement something like this…
CREATE TABLE test(
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(50)
) AUTO_INCREMENT = 100000;
This means the first insert will have an id value of 100001 (the highest current value plus 1).
You can also alter an existing table to set the AUTO_INCREMENT seed…
ALTER TABLE test AUTO_INCREMENT=200000;
The next insert will have an id of 200001 now.