kaman

When creating an integer data type, define it as unsigned if the column is never going to store negative values. This prevents unwanted negative numbers from being stored in the column. In addition, unsigned integer is smaller in length than the corresponding signed integer. For example, auto-incremented (starting from 1) primary key column is a good candidate for unsigned int.

E.g. OrderID is defined as unsigned integer because we know it does not make sense if OrderID is a negative number.

However, there is one problem with unsigned integer when we apply mathematical operation (subtraction). Here is the illustration:

1. Create a table called example with unsigned integers

CREATE TABLE `example`
(
`test` int(10) unsigned NOT NULL default '0',
`test2` mediumint(8) unsigned NOT NULL default '0',
`test3` smallint(5) unsigned NOT NULL default '0',
`test4` tinyint(3) unsigned NOT NULL default '0'
)

2. Then we add one record to the 'example' table. Add integer 5 for all 4 columns.

insert into `example`(`test`,`test2`,`test3`,`test4`) values (5,5,5,5);
3. Run a query for to update the value for each column. The query will try to update the column to a negative number.

update example set test = test - 6
update example set test2 = test2 - 6
update example set test3 = test3 - 6
update example set test4 = test4 - 6

4. Check what the value is in each column after the updates.
How to fix the problem?
To fixed the problem, MySQL recommends the use of CAST function - CAST(expr AS type). It will cast the value to 0 if a negative value is returned.

update example set test = CAST(test - 6 AS SIGNED)
update example set test2 = CAST(test2 - 6 AS SIGNED)
update example set test3 = CAST(test3 - 6 AS SIGNED)
update example set test4 = CAST(test4 - 6 AS SIGNED)

After updation the result will look like this,
LESSION: When you define an integer (UNSIGNED) column, think about how it's going to be used in your application. If the column value can be decremented, always use the CAST function in the update statement.

Thank you for reading. Please don't hesitate to drop your valuable comments as it helps improving this thread.

sharma.s.prakash

nice information
thanx

kaman

Thank you Mr Sharma for your prompt response.

harry

yes this is good sharing we are also adopting this funda