$sql = "UPDATE ' dzxbbs_common_member ' SET newprompt=newprompt-1 WHERE uid= ' {$uid} '";
The type of newprompt in the ' dzxbbs_common_member ' table is the type of smallint unsigned length 6.
When the Newprompt value is 0 o'clock, execute this SQL statement and get an unexpected result newprompt the field value becomes 65535.
The server version that appears on this issue is the MySQL 5.1.38-log.
To resolve this need to set this parameter sql_mode= "No_unsigned_subtraction" enables this option after two UNSIGNED types are subtracted to return the signed type.
My local test results were an error.
#1690 –bigint UNSIGNED value is out of the range in ' (' Test '. ' Test '. ' View '-1) '
Server version: MySQL 5.5.16
Open source Electronic Business system Ecshop Commodity inventory also has the problem of suddenly becoming 65535
Add: Unsigned related field length
unsigned is not a negative number, use this type can increase the length of the data!
For example, if the tinyint max is 127, then the tinyint unsigned maximum can reach 127 * 2
The unsigned property is only for integers, and the binary property is used only for char and varchar.
Type
|
Description
|
tinyint
|
a very small integer
|
smallint
|
smaller integers
|
Mediumint
|
Medium size integers
|
Int
|
Standard integer
|
bigint
|
Larger integer
|
Float
|
Single-precision floating-point numbers
|
Double
|
Double-precision floating point numbers
|
Decimal
|
Floating-point numbers
for a string
|
The name and the range of values for each numeric
type
are shown in table 2.
Type Description
|
Take value range
|
Tinyint[(m)]
|
Signed values: 128 to 127 (-27 to 27-1)
Unsigned values: 0 to 255 (0 to 28-1) 1 bytes
|
smallint [(M)]
|
Signed values: 32768 to 32767 (-215 to 215-1)
Unsigned value: 0 to
65535(0 to 6-1) 2 bytes
|
Mediumint[(m)]
|
Signed values:-8388608 to 8388607 (-22 3 to 22 3-1)
Unsigned values: 0 to 16777215 (0 to 4-1) 3 bytes
|
Int[(m)]
|
Signed values: 2147683648 to 2147683647 (-231 to 231-1)
Unsigned values: 0 to 4294967295 (0 to 232-1) 4 bytes
|
Bigint[(m)]
|
Signed values: 9223372036854775808 to 9223373036854775807 (-263 to 263-1)
Unsigned values: 0 to 18446744073709551615 (0 to 264–1) 8 bytes
|
Float[(M, D)]
|
Min Non 0 value: ±1.175494351e-38
|
double[(M,D)]
|
Min Non 0 value: ±2.2250738585072014e-308
|
Decimal (M, D)
|
variable; the range
of values depends on M and D
|
Table 2: Range
of values for numeric column
types
The amount of storage required
for the various
types of values is shown in table 3.
Type Description
|
Storage requirements
|
Tinyint[(m)]
|
1 bytes
|
smallint [(M)]
|
2 bytes
|
Mediumint[(m)]
|
3 bytes
|
Int[(m)]
|
4 bytes
|
Bigint[(m)]
|
8 bytes
|
Float[(M, D)]
|
4 bytes
|
Double[(M, D)]
|
8 bytes
|
Decimal (M, D)
|
M-byte (
mysql < 3.23), m+2 bytes (
mysql > 3.23)
|