MySQL 数据类型详情

吾爱主题阅读：170 2024-04-02 08:07:03 评论：0

1、数值类型

1.1、数值类型分类

严格数值类型（INTEGER、SMALLINT、DECIMAL 和 NUMERIC）
近似数值数据类型（FLOAT、REAL 和 DOUBLE PRECISION）

MySQL中数值类型：

整数类型	字节	最小值	最大值
TINYINT	1	有符号-128 无符号 0	有符号 127 无符号 255
SMALLINT	2	有符号-32768 无符号 0	有符号 32767 无符号 65535
MEDIUMINT	3	有符号-8388608 无符号 0	有符号 8388607 无符号 1677215
INT、INTEGER	4	有符号-2147483648 无符号 0	有符号 2147483647 无符号 4294967295
BIGINT	8	有符号-9223372036854775808 无符号 0	有符号 9223372036854775807 无符号 18446744073709551615
浮点数类型	字节	最小值	最大值
FLOAT	4	±1.175494351E-38	±3.402823466E+38
DOUBLE	8	±2.2250738585072014E-308	±1.7976931348623157E+308
定点数类型	字节	描述
DEC(M,D)， DECIMAL(M,D)	M+2	最大取值范围与 DOUBLE 相同，给定 DECIMAL 的有效取值范围由 M 和 D 决定
位类型	字节	最小值	最大值
BIT(M)	1～8	BIT(1)	BIT(64)

1.1.1、浮点数

浮点数如果不写精度和标度，则会按照实际精度值显示，如果有精度和标度，则会自动将四舍五入后的结果插入，系统不会报错；定点数如果不写精度和标度，则按照默认值 decimal(10,0)来进行操作，并且如果数据超越了精度和标度值，系统则会报错。

1.1.2、位类型

BIT(位)类型：范围从1-64，如果不写，默认为1位，对于此字段，直接使用select命令将不会看到结果，可以用bin()(显示为二进制格式)或者hex()（显示为16进制格式）函数进行读取

例：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 mysql> desc t2; + -------+--------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | + -------+--------+------+-----+---------+-------+ | id | bit (1) | YES | | NULL | | + -------+--------+------+-----+---------+-------+ 1 row in set (0.00 sec) mysql> insert into t2 values (1); Query OK, 1 row affected (0.05 sec) mysql> select * from t2; + ------+ | id | + ------+ | | + ------+ 1 row in set (0.00 sec) mysql> select bin(id),hex(id) from t2; + ---------+---------+ | bin(id) | hex(id) | + ---------+---------+ | 1 | 1 | + ---------+---------+ 1 row in set (0.03 sec)

bit类型数据插入时，首先将值转换为二进制，如果允许，则进行插入，如果位数小于实际定义的位数，则插入失败。

例：如果在刚刚的那张表中插入2，实际转换为二进制为10，超出了bit(1)的实际定义位数，就会报异常，将id定义为bit(2),在插入，就会成功

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 mysql> insert into t2 values (2); ERROR 1406 (22001): Data too long for column 'id' at row 1 mysql> alter table t2 modify id bit (2); Query OK, 1 row affected (0.67 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> insert into t2 values (2); Query OK, 1 row affected (0.03 sec) mysql> select bin(id),hex(id) from t2; + ---------+---------+ | bin(id) | hex(id) | + ---------+---------+ | 1 | 1 | | 10 | 2 | + ---------+---------+ 2 rows in set (0.00 sec)

1.1.3、时间日期类型

日期和时间类型	字节	最小值	最大值
DATE	4	1000-01-01	9999-12-31
DATETIME	8	1000-01-01 00:00:00	9999-12-31 23:59:59
TIMESTAMP	4	19700101080001	2038 年的某个时刻
TIME	3	-838:59:59	838:59:59
YEAR	1	1901	2155

DATE：用来表示年月日
DATETIME：用来表示年月日时分秒（支持插入格式：2007-9-3 12:10:10'、'2007/9/3 12+10+10'、'20070903121010'、20070903121010 等）
TIME：只用来表示时分秒
TIMESTAMP：经常插入或者更新日期为当前系统时间
YEAR：表示年

例1：创建时间表（dt），字段分别为 date、time、datetime 三种日期类型，并插入数值，查看显示结果

1 2 3 4 5 6 7 8 9 10 11 12 13 14 mysql> create table dt(d date ,t time ,dt datetime); Query OK, 0 rows affected (0.23 sec) mysql> insert into dt values (now(),now(),now()); Query OK, 1 row affected, 1 warning (0.05 sec) mysql> select * from dt; + ------------+----------+---------------------+ | d | t | dt | + ------------+----------+---------------------+ | 2021-05-13 | 10:14:07 | 2021-05-13 10:14:07 | + ------------+----------+---------------------+ 1 row in set (0.00 sec)

例2：创建测试表t，字段id1为TIMESTAMP类型，插入空值，并显示

1 2 3 4 5 6 7 8 9 10 11 12 13 mysql> create table t(id1 timestamp ); Query OK, 0 rows affected (0.22 sec) mysql> insert into t values ( null ); Query OK, 1 row affected (0.05 sec) mysql> select * from t; + ---------------------+ | id1 | + ---------------------+ | 2021-05-13 10:18:05 | + ---------------------+ 1 row in set (0.00 sec)

可以发现，系统给 id1 自动创建了默认值 CURRENT_TIMESTAMP（系统日期）。（注意，MySQL只给表中的第一个TIMESTAMP字段设置默认值为系统日期，如果有第二个TIMESTAMP类型，则默认值设置为0值）

例3：解释如上说明

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 mysql> alter table t add column id2 timestamp ; Query OK, 0 rows affected (0.48 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> show create table t \G; *************************** 1. row *************************** Table : t Create Table : CREATE TABLE `t` ( `id1` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP , `id2` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ) ENGINE=InnoDB DEFAULT CHARSET=latin1 1 row in set (0.00 sec) ERROR: No query specified

1.1.4、字符串类型

字符串类型	字节	描述及存储需求
CHAR（M）	M	M 为 0～255 之间的整数
VARCHAR（M）		M 为 0～65535 之间的整数，值的长度+1 个字节
TINYBLOB		允许长度 0～255 字节，值的长度+1 个字节
BLOB		允许长度 0～65535 字节，值的长度+2 个字节
MEDIUMBLOB		允许长度 0～167772150 字节，值的长度+3 个字节
LONGBLOB		允许长度 0～4294967295 字节，值的长度+4 个字节
TINYTEXT		允许长度 0～255 字节，值的长度+2 个字节
TEXT		允许长度 0～65535 字节，值的长度+2 个字节
MEDIUMTEXT		允许长度 0～167772150 字节，值的长度+3 个字节
LONGTEXT		允许长度 0～4294967295 字节，值的长度+4 个字节
VARBINARY（M）		允许长度 0～M 个字节的变长字节字符串，值的长度+1 个字节
BINARY（M）	M	允许长度 0～M 个字节的定长字节字符串

CHAR：固定长度，在检索时，会删除尾部的空格
VARCHAR：可变长字符串，在检索时，会保留这些空格

例：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 mysql> create table varc(v varchar (4),c char (4)); Query OK, 0 rows affected (0.20 sec) mysql> insert into varc values ( 'abc ' , 'abc ' ); Query OK, 1 row affected (0.03 sec) mysql> select length(v),length(c) from varc; + -----------+-----------+ | length(v) | length(c) | + -----------+-----------+ | 4 | 3 | + -----------+-----------+ 1 row in set (0.01 sec) mysql> select concat(v, '+' ),concat(c, '+' ) from varc; + ---------------+---------------+ | concat(v, '+' ) | concat(c, '+' ) | + ---------------+---------------+ | abc + | abc+ | + ---------------+---------------+ 1 row in set (0.00 sec)

BINARY：类似于char，但他是二进制字符串
VARBINARY：类似于varchar，单他是存储二进制字符串

例：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 mysql> create table bina(c binary (3)); Query OK, 0 rows affected (0.22 sec) mysql> insert into t set c= 'a' ; ERROR 1054 (42S22): Unknown column 'c' in 'field list' mysql> insert into bina set c= 'a' ; Query OK, 1 row affected (0.05 sec) mysql> select *,hex(c),c= 'a' ,c= 'a\0' ,c= 'a\0\0' from bina; + ------+--------+-------+---------+-----------+ | c | hex(c) | c= 'a' | c= 'a\0' | c= 'a\0\0' | + ------+--------+-------+---------+-----------+ | a | 610000 | 0 | 0 | 1 | + ------+--------+-------+---------+-----------+ 1 row in set (0.00 sec)

可以发现，当保存 BINARY 值时，在值的最后通过填充“0x00”（零字节）以达到指定的字段定义长度。从上例中看出，对于一个 BINARY(3)列，当插入时'a'变为'a\0\0'

1.1.5、ENUM 类型

ENUM：对 1～255 个成员的枚举需要 1 个字节存储；对于 255～65535 个成员，需要 2 个字节存储。

例：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 mysql> create table gend(gender enum( 'M' , 'F' )); Query OK, 0 rows affected (0.20 sec) mysql> insert into gend values ( 'M' ),( 'F' ),( '1' ),( null ); Query OK, 4 rows affected (0.03 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> select * from gend; + --------+ | gender | + --------+ | M | | F | | M | | NULL | + --------+ 4 rows in set (0.00 sec)

由此可以看出，枚举类型的插入值得时候，是忽略大小写的，初始下标从1开始；查询的时候会转换为大写，也可以插入空（null）

1.1.6、SET类型

SET：字符串对象，里面可以包含64个成员，成员不同，存储上也有所不同

1～8 成员的集合，占 1 个字节。
9～16 成员的集合，占 2 个字节。
17～24 成员的集合，占 3 个字节。
25～32 成员的集合，占 4 个字节。
33～64 成员的集合，占 8 个字节。
Set和EMUN区别在于，Set一次可以选则多个成员，而ENUM则只能选择一个

例：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 mysql> create table st(col set ( 'a' , 'b' , 'c' , 'd' )); Query OK, 0 rows affected (0.20 sec) mysql> insert into st values ( 'a,b' ),( 'a,d,a' ),( 'a,c' ),( 'a' ); Query OK, 4 rows affected (0.03 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> insert into st values ( 'f' ); ERROR 1265 (01000): Data truncated for column 'col' at row 1 mysql> insert into st values ( null ); Query OK, 1 row affected (0.03 sec) mysql> select * from st; + ------+ | col | + ------+ | a,b | | a,d | | a,c | | a | | NULL | + ------+ 5 rows in set (0.00 sec)