Mysql unsigned usage and elimination solutions for population Overflow

Unsigned is a non-negative number. You can use this type to increase the data length!
For example, if the maximum value of tinyint is 127, the maximum value of tinyint unsigned is 127*2.
The unsigned attribute is only for integer type, while the binary Attribute is only for char and varchar type.

Type	Description
Tinyint	Very small integer
Smallint	Small integer
Mediumint	Medium integer
Int	Standard integer
Bigint	Large integer
Float	Single-precision floating point number
Double	Double-precision floating point number
Decimal	Floating Point Number of a string

The names and value ranges of each numeric type are shown in table 2.

Type description	Value Range
Tinyint [(m)]	Signed value:-128 to 127 (-27 to 27-1) Unsigned value: 0 to 255 (0 to 28-1)1 byte
Smallint [(m)]	Signed value:-32768 to 32767 (-215 to 215-1) Unsigned value: 0 to 65535 (0 to 21 6-1)2 bytes
Mediumint [(m)]	Signed value:-8388608 to 8388607 (-22 3 to 22 3-1) Unsigned value: 0 to 16777215 (0 to 22 4-1)3 bytes
Int [(m)]	Signed value:-2147683648 to 2147683647 (-231 to 231-1) Unsigned value: 0 to 4294967295 (0 to 232-1)4 bytes
Bigint [(m)]	Signed value:-9223372036854775808 to 9223373036854775807 (-263 to 263-1) Unsigned value: 0 to 18446744073709551615 (0 to 264-1) 8 bytes
Float [(m, d)]	Minimum non-zero value: ± 1. 175494351e-38
Double [(m, d)]	Minimum non-zero value: ± 2. 225074255072014e-308
Decimal (m, d)	Variable; its value range depends on m and d

Table 2: value range of the value column type

The storage required for various types of values is shown in table 3.

Type description	Storage Requirements
Tinyint [(m)]	1 byte
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 bytes (mysql <3.23), m + 2 bytes (mysql> 3.23)

Table 3: Data column storage requirements

Mysql provides five types of integers: tinyint, smallint, mediumint, int, and bigint. Int is the abbreviation of integer. These types are different in the value range that can be expressed. An integer column can be defined as unsigned to disable negative values. This enables the value range of the column to be greater than 0. Different types of storage requirements are also different. The storage required for a type with a large value range is large.

Mysql provides three floating point types: float, double, and decimal. Different from integer, the floating point type cannot be unsigned, and its value range is also different from that of integer. The difference is that these types have the maximum value and the minimum non-zero value. The minimum value provides a metric of the corresponding type of precision, which is very important for recording scientific data.

Mysql5 unsigned subtraction overflow Solution

For speed, Mysql5 only stores the number of binary data. In addition and subtraction, it is also a binary operation.

Therefore, when using unsigned, be careful with the 0-1 operation. Try to make a judgment before such operation.

If you make a decision directly in the program, data errors will inevitably occur, because the data is stored in the memory for a little time, but not in the library, the best way is to directly send the SQL statement for modification. Here, mysql5 has a solution to CONVERT (field + (the number to be added or subtracted) AS SIGNED.

For example, UPDATE 'user' SET 'tk '= CONVERT (tk + (-8), SIGNED) WHERE 'id' = '2016'