MySQL Basics 02 Data type (ii) numeric correlation type

1. INT

MySQL supports integer types in 5 , and each integer type can be divided into signed and unsigned integers.

Type	Number of bytes
TINYINT	1
SMALLINT	2
Mediumint	3
Int	4
BIGINT	8

1.1.2. DECIMAL (NUMERIC)

MySQL uses decimal to denote fixed-point numbers, andNUMERIC is an alias for decimal.

A decimal (5,2) indicates that the integer and fractional parts of this fixed-point number type Total 5 bits, where 3 bits are integers,and2 Bits are decimals.

the total number of decimal digits is up to five, and the error is exceeded.

Mysql> CREATE TABLE n3 (ID numeric (primary key);

ERROR 1426 (42000): Too-big precision specified for ' id '. Maximum is 65.

Create a data table that contains a type of fixed-point number with a decimal integer plus 3 digits.

Mysql> CREATE TABLE n2 (ID numeric (65,3) primary key);

Query OK, 0 rows affected (0.02 sec)

Mysql> Show create table n2;

+-------+------------------------------------------------------------------------------------------------------ ----------+

| Table | Create Table |

+-------+------------------------------------------------------------------------------------------------------ ----------+

| N2 | CREATE TABLE ' n2 ' (

' ID ' decimal (65,3) is not NULL,

PRIMARY KEY (' id ')

) Engine=innodb DEFAULT charset=latin1 |

+-------+------------------------------------------------------------------------------------------------------ ----------+

1 row in Set (0.00 sec)

in the Decimal (65,3) type, the fractional part is automatically truncated to 3 decimal places when it exceeds 3 bits .

mysql> insert into N2 values (12345678901234567890123456789012345678901234567890123456789012.345);

Query OK, 1 row affected (0.05 sec)

mysql> insert into N2 values (12345678901234567890123456789012345678901234567890123456789012.12345);

Query OK, 1 row affected, 1 warning (0.01 sec)

mysql> insert into N2 values (12345678901234567890123456789012345678901234567890123456789012);

Query OK, 1 row affected (0.01 sec)

Mysql> select * from N2;

+--------------------------------------------------------------------+

| ID |

+--------------------------------------------------------------------+

| 12345678901234567890123456789012345678901234567890123456789012.000 |

| 12345678901234567890123456789012345678901234567890123456789012.123 |

| 12345678901234567890123456789012345678901234567890123456789012.345 |

+--------------------------------------------------------------------+

3 Rows in Set (0.00 sec)

in the In the decimal (65,3) type, the integer part is not allowed to exceed the number of digits.

mysql> insert into N2 values (12345678901234567890123456789012345678901234567890123456789012345);

ERROR 1264 (22003): Out of Range value to column ' ID ' at row 1

when using Decimal type, if you do not specify a precision, the default is decimal (10,0).

Mysql> CREATE TABLE N7 (ID decimal primary key);

Query OK, 0 rows affected (0.03 sec)

Mysql> Show CREATE TABLE N7;

+-------+------------------------------------------------------------------------------------------------------ ----------+

| Table | Create Table |

+-------+------------------------------------------------------------------------------------------------------ ----------+

| N7 | CREATE TABLE ' N7 ' (

' ID ' decimal (10,0) is not NULL,

PRIMARY KEY (' id ')

) Engine=innodb DEFAULT charset=latin1 |

+-------+------------------------------------------------------------------------------------------------------ ----------+

1 row in Set (0.00 sec)

Operation of fixed-point number:

Mysql> Select cast (123456789012345678901234567890.1234567890 as Decimal (40,10)) + cast (1.1111111111 as Decimal ( 40,10));

+-------------------------------------------------------------------------------------------------------------+

| Cast (123456789012345678901234567890.1234567890 as Decimal (40,10)) + cast (1.1111111111 as Decimal (40,10)) |

+-------------------------------------------------------------------------------------------------------------+

| 123456789012345678901234567891.2345679001 |

+-------------------------------------------------------------------------------------------------------------+

1 row in Set (0.01 sec)

2.float/double

MySQL provides floating-point float and DOUBLE types, where float occupies 4 bytes, DOUBLE occupy 8 a byte.

Float (5,2) represents a floating-point number with a 3 -bit integer plus 2 decimal places.

The query for floating-point numbers cannot be compared in simple equality:

Mysql> CREATE TABLE n8 (ID float (10,3) primary key);

Query OK, 0 rows affected (0.03 sec)

Mysql> Show create TABLE N8;

+-------+------------------------------------------------------------------------------------------------------ --------+

| Table | Create Table |

+-------+------------------------------------------------------------------------------------------------------ --------+

| N8 | CREATE TABLE ' N8 ' (

' ID ' float (10,3) is not NULL,

PRIMARY KEY (' id ')

) Engine=innodb DEFAULT charset=latin1 |

+-------+------------------------------------------------------------------------------------------------------ --------+

1 row in Set (0.00 sec)

Mysql> INSERT into N8 values (1234567.123);

Query OK, 1 row affected (0.01 sec)

Mysql> select * from N8;

+-------------+

| ID |

+-------------+

| 1234567.125 |

+-------------+

1 row in Set (0.00 sec)

Mysql> SELECT * from N8 where id=1234567.123;

Empty Set (0.00 sec)

Mysql> SELECT * from N8 where id=1234567.125;

+-------------+

| ID |

+-------------+

| 1234567.125 |

+-------------+

1 row in Set (0.00 sec)

Mysql> SELECT * from N8 where id=1234567.126;

Empty Set (0.00 sec)

in the above query, the raw data inserted is 1234567.123, and the query to the actual is 1234567.125.

MySQL Basics 02 Data type (ii) numeric correlation type