23 September 2011

MySQL - change INT from signed to unsigned

If you need to use an INT field in MySQL, it's useless to set it to signed values (default) if all the values that you will store there are possitive (eg. auto-increments starting with 1). Also, you will be limited to 2,147,483,647 records.

If you wish to change the field type to unsigned, which will allow you to store 4,294,967,295 records, you can use the following statement:

sql> alter table TABLE_NAME modify FIELD_NAME int unsigned;
Query OK, 0 rows affected (0.88 sec)
Records: 0  Duplicates: 0  Warnings: 0


Read more about INT types here.

22 September 2011

MySQL int(11) - what does 11 mean?

Actually i want to answer 2 questions by this post. First one is what is that 11 (size) number and what is the limit of the MySQL INT field.

INT(11) - means a normal INT field, that will display as ZERO all numbers lower than 11 digits. We can set 4 there and we'll get the folowing example:

CREATE TABLE `foo` (
`bar` int(4) unsigned zerofill DEFAULT NULL
);

 SELECT * FROM foo;
+---------+
| bar     |
+---------+
|    0042 | 
|    0101 | 
| 9876543 | 
+---------+
It is not a limit for the size of the INT field (eg. from 0 to 9999). The INT size is always 4 bytes, going between -2,147,483,648 and 2,147,483,647 or if it's not signed (+/- values) going from 0 to 4,294,967,295.