![]() ![]() To fixed the problem, MySQL recommends the use of CAST function - CAST( expr AS type). This strange behavior is expected and documented in MySQL Reference Manual. You know what happened next - members complained about the meaningless data 4294967295 shown on their page.Īctually this is not a bug in MySQL. Because of a bug in my code, the counter sometimes was trying to decrement to -1. When a member read an unread message, the counter is My site keeps a counter for the number of unread messages in Inbox for each member. The gotcha here is that MySQL does not throw an error and so you are not able to catch the error in your PHP or other languages' error Rather than raising an out-of-range error, MySQL simply returned the max integer value for the defined We subtracted 6 from 5 and it should return -1 and store it in each column of this row.īut because all columns are defined as unsigned integer data type, no negative number is allowed to be stored in there. The query will try to update the column to a negative number.Ĭheck what the value is in each column after the updates. Run a query for to update the value for each column. Then we add one record to the gotcha table. `test4` tinyint(3) unsigned NOT NULL default '0' `test3` smallint(5) unsigned NOT NULL default '0', `test2` mediumint(8) unsigned NOT NULL default '0', `test` int(10) unsigned NOT NULL default '0', In this table, we created four columns with data type of int, mediumint, smallint, and tinyint. Let's do an experiment to illustrate how this gotcha can easily occur.įirst, let's create a table called gotcha in SQLyog. Yes, there is a gotcha which is related specifically to MySQL. Tip #2: So you are using unsigned integer data type, but be aware of a gotcha. OrderID is defined as unsigned integer because we know it does not make sense if OrderID is a negative number. Max value for unsigned int is 4294967295 which is slightly over 4.2 billion. In addition, unsigned integer is smaller in length than the corresponding signed integer.įor example, auto-incremented (starting from 1) primary key column is a good candidate for unsigned int. Unwanted negative numbers from being stored in the column. If your application will never use negative numbers for an integer column, always define it as unsigned. Signed integers can be either positive or negative values. Unsigned integers are non-negative numbers. Tip #1: When creating an integer data type, define it as unsigned if the column is never going to store negative values. If you useĭifferent languages to work with MySQL, the following may not be applicable to you. Note that these considerations are based on my experience of using MySQL with PHP. To understand MySQL data type thoroughly, refer to MYSQL documentation for detailed explanation.īelow listed some practical tips you should consider when defining data types in MySQL. MySQL supports a number of data types in three categories:ĭata type constraint is defined when you create a new table or modifiy an existing table.īelow data type is defined for CategoryName column as varchar of 15 characters long. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |