MySQL 数据类型详情

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进制格式）函数进行读取

例：

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),在插入，就会成功

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、时间日期类型

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 三种日期类型，并插入数值，查看显示结果

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类型，插入空值，并显示

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：解释如上说明

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：可变长字符串，在检索时，会保留这些空格

例：

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，单他是存储二进制字符串

例：

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 个字节存储。

例：

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则只能选择一个

例：

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)

由此可以看出，set是可以插入多个成员的，也可以插入null，插入不存在的定义列时，会报异常插入失败