MySQL database tables with SMALLINT id fields
I currently have a few tables in my MySQL database where I declare the id
field to be of type SMALLINT(6)
(is this correct as SMALLINT
only goes to 65535 [length of 5]). I have read here under integer types that when it is UNSIGNED
the range increases in the positive to 65535. How will this affect my database if开发者_运维百科 I specifically change the id
field's attributes to UNSIGNED
? Also, are there problems when going from SMALLINT
to MEDIUMINT
?
My database type is MyISAM.
You should be fine to switch from SMALLINT
to MEDIUMINT
- no data will be lost.
A normal integer in MySQL can be any number between a lower limit and an upper limit. With a signed integer, the lower limit is a negative number and the upper is a positive number. A SMALLINT
, signed, can be between -32768 and 32767. An unsigned integer, however, is only positive. The lower limit is zero, and the upper limit is 65535.
I usually use INT
for IDs, though. I'd rather have an upper limit to an ID field so high that it should never be reached.
The main problem with using UNSIGNED
types is that if you ever do any arithmetic that results in a negative number you end up with a large number instead. However, for ID's this is unlikely to be a problem for and I often use them to reduce memory usage a bit.
I can't think of any problems with using a SMALLINT
then changing to MEDIUMINT
because widening a data-type doesn't usually cause issues.
精彩评论