MySQL unsigned usage and subtraction the solution of the complement overflow method _mysql

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 values: 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)

Table 3: Storage requirements for numeric column types

MySQL offers five integral types: tinyint, smallint, mediumint, int, and bigint. int is an integer abbreviation. These types are different in the range of values that can be represented. Integer series can be defined as unsigned to disable negative values, which makes the column take a range of 0 or more. Various types of storage requirements are also different. A large amount of storage is required for a type that has a larger range of values.

MySQL offers three floating-point types: float, double, and decimal. Unlike an integral type, a floating-point type cannot be unsigned, and its value range is different from an integral type, not only because these types have a maximum value, but also with a minimum value of 0. The minimum value provides a measure of the corresponding type precision, which is important for recording scientific data

Solution of MYSQL5 unsigned subtraction with complement overflow

Mysql5 for Speed, only a few binary data, and in addition and subtraction operations, is also a binary operation.

So in the use of unsigned is careful 0-1 operation. Try to make a judgment before this kind of operation.

If the decision is made directly in the program, inevitably there will be data errors, because there is a little time in the data is placed in memory, and did not put into the library, the best way to directly send out the SQL statements to modify, here MySQL5 have a solution CONVERT (field + (to be added) as signed) It's OK.

For example: UPDATE ' user ' SET ' tk ' = CONVERT (tk + ( -8), signed) WHERE ' id ' = ' 1330 '