Database: Table Operations-Data type (numeric type)

Introduced

The storage engine determines the type of table, and the data stored in the table has different types, each data type has its own width, but the width is optional

MySQL Common data Types overview

#1. Number:    integer: tinyinit  int  bigint    decimal:        float: Less accurate when the number of bits is short        : inaccurate            when the bit is longer 0.000001230123123123            : 0.000001230000        decimal: (If using decimal, use decimal is recommended) The            precise            internal principle is to save # # in string form. String:    Char (10): Simple rough, wasted space, fast access        root saved to root000000    varchar: precise, space-saving, slow Access    SQL optimization: When you create a table, the fixed-length type goes forward, A long, backward place                    such as           a gender such as address or description information    >255 characters, the file path is stored in the database.            compare slices, videos, etc. find a file server, only the path or URL is stored in the database. #3. Time type:    most commonly used: DATETIME#4. Enumeration types and collection types

　　

1, Integer type

Integer type: TINYINT SMALLINT mediumint INT BIGINT

Function: Store age, grade, ID, various numbers, etc.

========================================        tinyint[(m)] [unsigned] [Zerofill]            small integer, data type used to hold some range of integer numeric ranges:            Signed:                -128 ~ 127 unsigned            :                0 ~ 255            ps:mysql No boolean value, constructed using tinyint (1). ========================================        int[(M)][unsigned][zerofill]            integer, data type is used to hold some range of integer numeric ranges:            signed:                    -2147483648 ~ 2147483647 unsigned            :                    0 ~ 4294967295========================================        bigint[( m)][unsigned][zerofill]            large integer, data type used to hold some range of integer numeric ranges:            signed:                    9223372036854775808 ~ 9223372036854775807 Unsigned            :                    0  ~  18446744073709551615

Verify

mysql> CREATE table t1 (x tinyint);    # default is signed, that is, the number in front of the positive and negative query OK, 0 rows affected (0.12 sec) mysql> desc t1;+-------+------------+------+-----+---------+--- ----+| Field | Type       | Null | Key | Default | Extra |+-------+------------+------+-----+---------+-------+| X     | tinyint (4) | YES  |     | NULL    |       | +-------+------------+------+-----+---------+-------+1 row in Set (0.07 sec) mysql> insert into T1 values    -- 129),    ( -128), (    127), (    128); Query OK, 4 rows affected, 2 warnings (0.00 sec) records:4  duplicates:0  warnings:2mysql> select * from t1;+ ------+| X    |+------+| -128 |    #-129 was saved as a -128|. -128 |    #有符号, Minimum -128|  127 |    # signed, maximum value 127|  127 |    # 128 Save into 127+------+#

　　

# Set unsigned tinyintmysql> CREATE TABLE t2 (x tinyint unsigned); Query OK, 0 rows affected (0.05 sec) mysql> insert into T2 values, ( -1), (0), (255), (256);     Query OK, 4 rows affected, 2 warnings (0.00 sec) records:4 duplicates:0 warnings:2mysql> SELECT * FROM t2;+------+| 0 |    -1 saved as a 0| 0 |  #无符号, the minimum value is 0| 255 |  #无符号, the maximum value is 255| 255 | #256存成了255 +------+4 rows in Set (0.00 sec) ============ signed and unsigned int============= #int默认为有符号mysql > CREATE table t3 (x int) ;mysql> insert into T3 values, ( -2147483649), ( -2147483648), (2147483647), (21474836 48); Query OK, 4 rows affected, 2 warnings (0.00 sec) records:4 duplicates:0 warnings:2mysql> select * from t3;+-------- -----+| X |+-------------+| -2147483648 | #-2147483649 was saved as a -2147483648|. -2147483648 |  #有符号, the minimum value is -2147483648| 2147483647 |  #有符号, the maximum value is 2147483647| 2147483647 | #2147483648存成了2147483647 +-------------+4 rows in Set (0.01 sec) #设置无符号intmysql > CREATE tableT4 (x int unsigned);    Query OK, 0 rows affected (0.05 sec) mysql> insert into T4 values---( -1), (0), (4294967295), (4294967296); Query OK, 4 rows affected, 2 warnings (0.03 sec) records:4 duplicates:0 warnings:2mysql> select * from t4;+-------- ----+|          X |+------------+| 0 |          #-1 was saved as a 0|. 0 | #无符号, the minimum value is 0| 4294967295 | #无符号, the maximum value is 4294967295| 4294967295 | #4294967296存成了4294967295 +------------+============== signed and unsigned bigint=============mariadb [db1]> CREATE table T6 (x bigint); MariaDB [db1]> INSERT INTO T5 values, ( -9223372036854775809), ( -9223372036854775808), (9223 372036854775807), (9223372036854775808); MariaDB [db1]> SELECT * from t5;+----------------------+| X |+----------------------+| -9223372036854775808 | |  -9223372036854775808 | |  9223372036854775807 | | 9223372036854775807 |+----------------------+mariadb [db1]> CREATE table T6 (x bigint unsigned); MariaDB [DB1]&GT INSERT into T6 values--( -1), (0), (18446744073709551615), (18446744073709551616); MariaDB [db1]> SELECT * from t6;+----------------------+|                    X |+----------------------+|                    0 | | 0 | | 18446744073709551615 | | 18446744073709551615 |+----------------------+====== using Zerofill to test the display width of integer types =============mariadb [db1]> Create Table T7 (x int (3) zerofill); MariaDB [db1]> INSERT into T7 values---(1), (one), (111), (1111); MariaDB [db1]> SELECT * FROM t7;+------+|  X |+------+|  001 | |  011 | | 111 | | 1111 | #超过宽度限制仍然可以存 +------+

　　

Note: When you specify a width for this type, you only specify the display width of the query results, regardless of the storage scope, as follows

In fact, we don't have to specify a display width for an integer type, so you can use the default.

The default display width, which is based on the maximum value plus 1

The storage width of int is 4 bytes, or 32 bit, i.e. 2**32

The unsigned maximum value is: 4294967296-1

Signed Maximum: 2147483648-1

Signed and unsigned maximum numbers require a display width of 10 and 11 bits for a signed minimum value to display completely, so the default display width of int type is 11 is very reasonable

Finally: the type of shaping, in fact, there is no need to specify the display width, using the default is OK

2, floating-point type

Fixed-point number type Dec equals decimal

Float type: float DOUBLE

Function: Store salary, height, weight, physical parameters, etc.

====================================== #FLOAT [(m,d)] [UNSIGNED] [Zerofill] Definition: single-precision floating-point numbers (not accurate decimal values), M is the total number of numbers, D is the number after the decimal point.           The M maximum value is 255,d with a maximum value of 30 signed: -3.402823466E+38 to-1.175494351e-38, 1.175494351E-38 to 3.402823466E+38 unsigned: 1.175494351E-38 to 3.402823466E+38 accuracy: * * * with the increase in decimals, accuracy becomes inaccurate ****====================================== #DO uble[(m,d)] [UNSIGNED] [Zerofill] Definition: Double-precision floating-point numbers (not accurate decimal values), M is the total number of numbers, D is the number after the decimal point. M maximum value is 255,d maximum is 30 signed: -1.7976931348623157E+308 to-2.2250738585072014e-308 2.2250738585072014E-308 to 1 .7976931348623157E+308 unsigned: 2.2250738585072014E-308 to 1.7976931348623157E+308 accuracy: * * * * * with the increase in decimals, the accuracy is more than float To be high, but also to become inaccurate ****======================================decimal[(m[,d]) [unsigned] [zerofill] Definition: Accurate decimal values, M is the total number of digits (minus Not counted), D is the number after the decimal point. The M maximum value is 65,d maximum of 30. Accuracy: * * * * with the increase of decimals, accuracy is always accurate * * * for accurate numerical calculations, this type of decaimal is used to store exact values because they are stored internally as strings. Verify mysql> CREATE table t1 (x float (256,31)); ERROR 1425 (4): Too big scale to specified for column ' x '. Maximum is 30.mysql> CREATE table t1 (x float (256,30)); ERROR 1439 (42000): Display width out of range for column ' x ' (max = 255) mysql> CREATE table T1 (x float (255,30)); #建表成功Query OK, 0 rows affected (0.02 sec) mysql> CREATE TABLE t2 (x double (255,30)); #建表成功Query OK, 0 rows affected (0.02 sec) mysql> CREATE table t3 (x decimal (66,31)); ERROR 1425 (42000): Too big scale to specified for column ' x '. Maximum is 30.mysql> create table t3 (x decimal (66,30)); ERROR 1426 (42000): Too-big precision specified for ' x '. Maximum is 65.mysql> create table t3 (x decimal (65,30)); #建表成功Query OK, 0 rows affected (0.02 sec) mysql> Show tables;+---------------+| TABLES_IN_DB1 |+---------------+| T1 | | T2 | | T3 |+---------------+rows in Set (0.00 sec) mysql> insert into T1 values (1.1111111111111111111111111111111); #小数点后31个1Query OK, 1 row affected (0.01 sec) mysql> insert into T2 values (1.1111111111111111111111111111111); Query OK, 1 row Affected (0.00 sec) mysql> insert into T3 values (1.1111111111111111111111111111111); Query OK, 1 row affected, 1 warning (0.01 sec) mysql> select * from T1; #随着小数的增多, accuracy start inaccurate +----------------------------------+| X |+----------------------------------+| 1.111111164093017600000000000000 |+----------------------------------+row in Set (0.00 sec) mysql> select * from T2; #精度比float要准确点, but with the increase in decimals, the same becomes inaccurate +----------------------------------+| X |+----------------------------------+| 1.111111111111111200000000000000 |+----------------------------------+row in Set (0.00 sec) mysql> select * from T3; #精度始终准确, D is 30, leaving only 30 decimal places +----------------------------------+| X |+----------------------------------+| 1.111111111111111111111111111111 |+----------------------------------+row in Set (0.00 sec)

　　

Database: Table Operations-Data type (numeric type)