Generating a unique ID

Question

I was looking into functions that generate unique IDs - my specific requirement is that the unique ID would need to be 8 digits only.

Anyway I've just realised we can set an ID auto increment field to start from any number. So suppose I have it start from 11111111 - am I right in thinking that all new records will increment from this value? If so then I have my unique 8 digit ID and no need to use a function for this.

And also if a record is deleted then will a new record re-use that deleted ID?

Answer 1

That is correct. If your auto-number columns start at 11111111, then your next row will have the number 11111112.

When a record is deleted, its auto-number is not reused.

Edit: Regarding maximum values, that is dependent on the column type you have chosen. From the MySQL documentation:

Use a large enough integer data type for the AUTO_INCREMENT column to hold the maximum sequence value you will need. When the column reaches the upper limit of the data type, the next attempt to generate a sequence number fails. For example, if you use TINYINT, the maximum permissible sequence number is 127. For TINYINT UNSIGNED, the maximum is 255.

You could use an INT and go up to 4294967295, or BIGINT and go up to 18446744073709551615. See http://dev.mysql.com/doc/refman/5.0/en/numeric-types.html

Answer 2

1) Autoincrement-Values are not reused

2) Yes, by setting the autoincrement counter to an 8-digit number (e.g. 10000000), you can achieve this! (Table option "AUTO_INCREMENT = N")