Description:
MySQL support integer types which are signed and unsigned by default "int" types are signed.
If you want to change the type to signed with an ALTER TABLE statement MySQL will rebuild the table yet if it checked the minimum and maximum ranges (via a primary or secondary index) it would be able to immediately detect if the values are in range and just make a metadata change to the table. This would be very convenient.
How to repeat:
Example of a table I just populated with a number of rows
Create Table: CREATE TABLE `t` (
`id` int(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2490316 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
root@myhost [test]> alter table t modify id int unsigned not null auto_increment;
Query OK, 2097152 rows affected (18.38 sec)
Records: 2097152 Duplicates: 0 Warnings: 0
A waste of time. the minimum value was 1, so this could have been a metadata update and thus an almost instant change.
Moving from unsigned to signed would also be possible if the limits were checked to be in range.
Suggested fix:
Add some more intelligence in ALTER TABLE:
* the type change is between signed/unsigned
* the size is the same
* the column has an index which makes it easy to check minimum and maximum values
If the change does not break the range of the new type:
* signed to unsigned: no negative values, or
* unsigned to signed: highest value < max(signed value)
then go ahead and make a metadata change.
