MySQL Autoincrement problem

lth2h

This is just an example that demonstrates a smiliar problem I'm having:

in MySQL

CREATE TABLE foo(id int(1) not null auto_increment, txt varchar(255), primary key(id));

Now the id field should be a number between 0 and 9 right?

However I have a PHP script which inserts values into the DB. The SQL is just INSERT INTO foo(txt) VALUES (\"$txt\"). After inserting about 100 records, id has values with 3 digits in them. Should that either cause an error or ovewrite the values or something?

mourisj

Here is what the MySql manual says:

(from manual of version 3.23.41)

Another extension is supported by MySQL for optionally specifying the display width of an integral value in parentheses following the base keyword for the type (for example, INT(4)). This optional width specification is used to left-pad the display of values whose width is less than the width specified for the column, but does not constrain the range of values that can be stored in the column, nor the number of digits that will be displayed for values whose width exceeds that specified for the column. When used in conjunction with the optional extension attribute ZEROFILL, the default padding of spaces is replaced with zeroes. For example, for a column declared as INT(5) ZEROFILL, a value of 4 is retrieved as 00004. Note that if you store larger values than the display width in an integer column, you may experience problems when MySQL generates temporary tables for some complicated joins, as in these cases MySQL trusts that the data did fit into the original column width.

Note: by default, length is set to 11

lth2h

So what that means is that as long as I don't use JOIN in my querry I won't have a problem. Yes? No? Maybe So?

mourisj

I don't think it means this at all! Your IDs are perfectly valid (integers), unless - of course - you want to join to some data which are restricted to one digit. Even then, your join simply won't return values.

It is simply a matter of formatting in special situations.

If you want to have a 1-digit-number, you have to achieve this by other means. In this case, I don't think I would use auto-increments, but rather check the maximum value and reset to 0 if I reach 9 or something similar.

To be really sure you don't have more than one digit, use to fields: one numeric id: num_id int autoincrement, and another field char_id char(1) which you fill with the last digit of your num_id (convert num_id to char, take last character of it).

This is rather weird, but you might have your reasons to do this.

Have a nice day,

JJ Mouris