A database field problem was encountered during development
WXL 2021-07-30 12:13:00

Daniel, don't waste your time , Suitable for beginners .

There is a problem today , Database field problem .

There is a table that stores user consumption records , When designing tables, you use shaping , Later increased demand , Some changes need to be made to the business , After the change, the test found that the storage 0.2,0.51 Such a floating-point type cannot be saved on , The result is 0,1 And so on. , After debugging, the database field problem is found , So the field type is modified ,sql sentence :

 

ALTER TABLE `db_action`.`money_record`
CHANGE `c_money` `c_money` decimal(11,2) unsigned NOT NULL DEFAULT '0.00' COMMENT ' Change in commercial currency '

 

from int Change the type to decimal(11,2), Integers 11 position , Keep two decimal places .

So I went to execute , I found the wrong report .

Check information :

unsigned

Both are nonnegative numbers , With this type you can increase the data length !

For example, if tinyint The biggest is 127, that tinyint unsigned Maximum You can go there. 127 * 2

unsigned Property is only for integers , and binary Property is only used for char and varchar.

type

explain

tinyint

Very small integers

smallint

Smaller integer

mediumint

Medium size integer

int

Standard integers

bigint

Larger integer

float

Single-precision floating-point

double

Double precision floating point

decimal

Floating point number of a string



The name and value range of each value type are shown in table 2 Shown .

Type specification

Value range

tinyint[(m)]

There are signed values :-128 To 127(- 27 To 27 - 1)

Unsigned value :0 To 255(0 To 28 - 1)1 Bytes

smallint[(m)]

There are signed values :-32768 To 32767(- 215 To 215 - 1)

Unsigned value :0 To 65535(0 To 21 6 - 1)2 Bytes

mediumint[(m)]

There are signed values :-8388608 To 8388607(- 22 3 To 22 3 - 1 )

Unsigned value :0 To 16777215(0 To 22 4 - 1)3 Bytes

int[(m)]

There are signed values :-2147683648 To 2147683647(- 231 To 231- 1)

Unsigned value :0 To 4294967295(0 To 232 - 1)4 Bytes

bigint[(m)]

There are signed values :-9223372036854775808 To 9223373036854775807(- 263 To 263-1)

Unsigned value :0 To 18446744073709551615(0 To 264 – 1) 8 Bytes

float[(m, d)]

Minimum nonzero value :±1.175494351e - 38

double[(m,d)]

Minimum nonzero value :±2.2250738585072014e - 308

decimal (m, d)

variable ; The range of values depends on m and d

surface 2: Value range of numeric column type

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

Type specification

Storage requirements

tinyint[(m)]

1 byte

smallint[(m)]

2 byte

mediumint[(m)]

3 byte

int[(m)]

4 byte

bigint[(m)]

8 byte

float[(m, d)]

4 byte

double[(m, d)]

8 byte

decimal (m, d)

m byte (mysql < 3.23),m+2 byte (mysql > 3.23 )

surface 3: Storage requirements for numeric column types

mysql Five integers are provided : tinyint、smallint、mediumint、int and bigint.int by integer Abbreviation . These types are different in the representable value range . The integer column can be defined as unsigned This disables negative values ; This makes the value range of the column 0 above . Various types of storage requirements are also different . Types with a large range of values require a large amount of storage .

mysql Three floating point types are provided : float、double and decimal. Unlike integers , Floating point type cannot be unsigned Of , Its value range is also different from integer , The difference is not only that these types have the greatest value , And there is a minimum non-zero value . The minimum value provides a measure of the accuracy of the corresponding type , This is very important for recording scientific data ( Of course , There are also negative maximum and minimum values ).

 

 

watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=

 

Please bring the original link to reprint ,thank
Similar articles

2021-06-05

2021-06-05

2021-06-06

2021-06-09

2021-06-09

2021-06-09

2021-06-10

2021-06-11