Mysql – Zerofill Auto Increment – theSql

You would generally use integers rather than varchars because they consume less space, have well understood sorting pattern are fast to index etc. Integers are natural data types of a CPU, and hence performance is generally optimal. Typically an integer is 4 bytes, equivalent to just 4 characters in a (non-unicode) varchar.

If you were worried about running out of space with an INT type, then try BIGINT, which gives you 8-byte numbers. The limit on this is pretty huge, and you'd probably run out of disk space before you reached that limit of records :-) The performance of BIGINT is also going to be very good, especially as many servers are now 64-bit too.

The answer to the first part of your question about what happens when you run out in INTs is not simple, especially as you said without changing the datatype to BIGINT. Basically there isn't much you can do, and what you may be able to do is limited very much by the nature of the data in your database. What records are foreign-keyed to this data? Do you still need all the data in that table and the related records? On the assumption that you could archive off a lot of the initial data (and its related data), then the only thing I can suggest is moving the data out of the table (lets say the first 1 to X million records), and then resetting the identity seed to 1. There are all sorts of reasons though I wouldn't recommend it - for example there are many bits of code I have seen that do things like check the maximum value of an id field, to see what has just been added, and that would not work (and shouldn't be done). Also, people assume that record N was created before N+1. No easy answer I think.

Finally, I don't know about MySQL, but SQL Server would give an overflow error if you reached the limit.

That's a limit of the InnoDB engine. It cannot index more than 767 bytes (for a single column)

See the manual:

http://dev.mysql.com/doc/refman/5.5/en/innodb-restrictions.html

By default, an index key for a single-column index can be up to 767 bytes
...
When you attempt to specify an index prefix length longer than allowed, the length is silently reduced to the maximum length