Mysql integer sizes and ranges

[naiquevin]

When I create a table in MYSQL this way..

CREATE TABLE (

id INT(1000) NOT NULL AUTO_INCREMENT

);

it shows me an error msg

#1439 - Display width out of range for column 'id' (max = 255)

But from various sources over internet I find that the range for INT is from -2,147,483,648 to 2,147,483,647

Is MYSQL working normally here ? or is there any problem ?

thanks

[rocknbil]

INT(1000)

Creates an integer column with 1000 digits, not a maximum value of 1000. to support 2,147,483,647, you'd only need

int(10)

I don't recall what the maximum integer digit size is but your error ("for column 'id' (max = 255)") is telling you so. I've never needed anything beyond int(11).

I'm also surprised as to why it didn't give you an error for not supplying a table name.

An aside, you may also want your auto increment to be a primary key unless you have another field you need to use as a primary key:

create table mytable (id int(11) primary key auto_increment not null);

[naiquevin]

Ok ... I was under the impression that int(11) means max value 11..
thanks for the info..

I forgot the table name when I typed the query here ... The actual table that I created has many columns..

[Demaestro]

If you want you can force the table to only accept inserts and updates to that column that are <= 1000

I typically use a trigger and a function to force this behavior and back it up with other validation in the methods that write to the table, but a trigger and a function will ensure no one can directly enter a value that breaks your model.