MySql基础篇——数据类型_mysql不推荐整数类型作为字符宽度

一、MySQL中的数据类型

常见数据类型的属性：

---

二、整数类型

2.1 类型介绍

整数类型一共有五种，包括TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT。

2.2 可选属性

整数类型的可选属性有三个：

• (M)

表示最低显示宽度，例如int(5)，当数据宽度小于5位时在数字前面需要用字符填满宽度。该项功能要配合”ZEROFILL“使用，否则指定宽度无效。
如果设置了显示宽度，不会对插入的数据有影响，还是按照类型的实际宽度保存。从MySQL8.0.17开始，整数数据类型不推荐使用显示宽度属性。

• UNSIGNED

表示无符号类型

• ZEROFILL

表示0填充。如果某列是ZEROFILL，那么mysql会自动为当前列添加UNSIGNED属性。
如果指定了ZEROFILL表示不够M位时，用0在左边填充，超过M位时，只要不超过数据存储范围即可。
(M)必须和UNSIGNED ZEROFILL一起使用才有意义，M的值跟int所占多少存储空间没有关系，int(3),int(4)在磁盘上都是占用4个字节。

2.3 使用场景

TINYINT: 一般用于枚举数据，比如系统设定范围很小且固定的场景。
SMALLINT: 可以用于较小范围的统计数据，比如统计工厂的固定资产库存数量等。
MEDIUMINT: 用于较大整数的计算，比如车站每日的客流量。
INT: 用于商品编号。
BIGINT: 取值范围足够大，不用考虑超限问题。
在实际工作中，系统故障产生的成本远远超过增加几个字段存储空间所产生的成本。因此，首先确保数据不会超过取值范围，在这个前提下，再考虑如何节省存储空间。

---

三、浮点类型

3.1 类型介绍

浮点数和定点数类型的特点是可以支持处理小数。MySQL支持的浮点数类型，分别是FLOAT、DOUBLE和 REAL。

REAL默认就是DOUBLE，如果吧SQL模式设定为启用REAL_AS_FLOAT，那么M有SQL就认为REAL是FLOAT。启用语句：set sql_mode = "REAL_AS_FLOAT"
MySQL存储浮点数的格式为：符号（S）、尾数（M）和阶码（E）。因此无论有没有符号，M有SQL的浮点数都会存储符号部分。因此无符号数的取值范围，就是有符号数取值范围大于等于0的部分。

3.2 数据精度说明

对于浮点数类型，在MySQL中单精度使用4个字节，双精度使用8个字节。

• MySQL允许使用非标准语法(其他数据库未必支持，因此如果涉及到数据迁移，则最好不要这么用)：FLOAT(M,D) , DOUBLE(M,D),M称为精度（整数+小数），D称为标度（小数）。D<M<=255,0<=D<=30。
  例如：定义FLOAT(5,2)的一个列可以显示-999.99~999-99，超出这个范围会报错。
• FLOAT和DOUBLE类型在不知道（M,D）时，默认按照实际的精度（由实际硬件和操作系统决定）来显示。
• 不管是否显示设置了精度（M,D），MySQL的处理方案如下：
  如果存储时，整数部分超出范围，MySQL就会报错，不允许存这样的值。
  如果存储时，小数点部分超出范围，则：若四舍五入后，整数部分没有超出范围，则只是警告，但能成功操作并且四舍五入删除多余的小数位后保存。例如FLOAT（5，2）插入999.009，近似结果是999.01；若四舍五入后，整数部分超出范围，则报错；

从MySQL8.0.17开始后，FLOAT(M,D) , DOUBLE(M,D)就明确不推荐使用，将来可能被移除。另外关于浮点型FLOAT和DOUBLE的UNSIGNED也不推荐使用，将来也可能被移除。
浮点类型存在精度损失，原因是：

---

四、定点数类型

4.1 类型介绍

（1） 使用DECIMAL(M,D)的方式表示高精度小数。其中M称为精度，D称为标度。
（2） D<M<=65,0<=D<=30,D<M。例如DECIMAL(5,2)表示该列取值范围是-999.99~999.99。
（3） 定点数在MySQL内部是以字符串进行存储，如果不指定精度和标度时，其默认为DECIMAL(10,0)。当精度超出定点数类型的精度范围时，同样会进行四舍五入。还不行就报错。
（4） DECIMAL的存储空间并不固定，由精度M决定，总共占用M+2字节。它的最大取值范围和double类型一样。

---

五、位类型

BIT类型中存储的是二进制，类似010110。

如果没有指定M，默认是1位。如果插入的是10进制的，会转为二进制插入。
在使用select命令查询字段时，可以使用bin()或hex()函数进行读取。

---

六、日期和时间类型

6.1 类型介绍

MySQL有多种日期和时间的数据类型，不同的版本可能有所差异。MySQL8.0版本所支持的日期和时间类型主要有：YEAR类型、TIME类型、DATE类型、DATETIME类型、TIMESTAMP类型。

• YEAR：表示年
• DATE：表示年月日
• TIME：表示时分秒
• DATETIME：表示年月日时分秒
• TIMESTAMP： 表示带时区的年月日时分秒

6.2 YEAR类型

（1） 以4位字符串或数字格式表示YEAR类型，其格式为yyyy，最小值为1901，最大值为2155。
（2） 以2位字符串格式表示YEAR类型，最小值为00，最大值为99：

• 当取值为01到69时，表示2001到2069；
• 当取值为70到99时，表示1970到1999；
• 当取值整数的0或00添加的话，那么是0000年；
• 当取值是日期/字符串的‘0’添加的话，是2000年。

（3） 从MySQL5.5.27开始，2位格式的YEAR已经不推荐使用。

6.2 DATE类型

DATE表示日期，没有时间部分，格式为yyyy-MM-dd，需要3个字节。
（1） 以yyyy-MM-dd格式或者YYYYMMDD格式表示的字符串日期，其最小取值为1000-01-01，最大取值为9999-12-03。YYYYMMDD格式会被转化为YYYY-MM-DD格式。
（2） 以YY-MM-DD格式或者YYMMDD格式表示的字符串日期，此格式中年份和YEAR类型特点相同。
（3） 使用CURRENT_DATE()或者NOW()函数，会插入当前系统的日期。

6.3 TIME类型

TIME类型表示时间，不包含日期部分。在MySQL中，向TIME类型的字段插入数据时，也可以使用几种不同的格式：
（1） 可以使用带有冒号的字符串，比如’D HH:MM:SS’、‘HH:MM:SS’ 、‘HH:MM’、‘D HH:MM’、'D HH’格式。其中D表示天，最小值为1，最大值为34。如果使用带有D格式的字符串时，D会被转化为小时，计算格式为D*24+HH。当使用带有冒号并且不带D的字符串表示时间时。表示当天时间。例如：‘12.10’表示12:10:00而不是00:12:10。
（2） 可以使用不带有冒号的字符串或数字，格式为HHMMSS。如果插入一个不合法字符串或数字，MySQL在存储时自动转化为00:00:00进行存储。
（3） 使用CURRENT_TIME()或者NOW()或CURTIME()函数，会插入当前系统的时间。

6.4 DATETIME类型

DATETIME类型在所有日期时间类型中占用的存储空间最大，总共需要8个字节的存储空间。在格式上为YYYY-MM-DD HH:MM:SS。
（1） 以YYYY-MM-DD HH:MM:SS格式或者YYYYMMDDHHMMSS格式的字符串。最小值为1000-01-01 00:00:00,最大值为9999-12-03 23:59:59。
（2） YY-MM-DD HH:MM:SS符合YEAR类型规则。
（3） 使用CURRENT_TIMESTAMP()或者NOW()函数，会插入当前系统的日期时间。
（4） 其他时区的人看会有误差

6.5. TIMESTAMP类型

TIMESTAMP需要4个字节的存储空间。但是TIMESTAMP存储时间范围比DATETIME小很多，只能存储“1970-01-01 00:00:01UTC” 到 “2038-01-19 03:14:07 UTC”之间的时间。UTC表示世界统一时间，也叫做世界标准时间。
存储数据的时候需要对当前时间所在的时区进行转换，查询的时候再将时间转换为当前的时区。因此TIMESTAMP存储的时时间戳，不同时区查询会显示不同的时间。

---

七、文本字符串类型

总体上分为CHAR、VARCHAR、TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT、ENUM、SET等类型。

7.1 char和varchar类型

char类型
（1）一般需要预先定义字符串长度。如果不指定M，则默认是1个字符。
（2）如果保存时，数据的实际长度比M小，则会在右侧填充空格以达到指定长度。当mysql检索char类型数据时，会去除尾部的空格。
（3）定义char类型字段时，最多存放的字符数是255，和编码无关。
varchar类型
（1）定义时，必须指定长度M，否则报错，M表示字符。
（2）mysql4.0版本以下，varchar(20)指的是20字节，如果存放utf8汉字时，只能存6个（每个汉字3个字节）；5.0版本以上，指的是20字符，60个字节。
（3）检索varchar数据时，会保留数据尾部的空格。
（3）定义varchar类型字段时，最多存放的字节数是65535，和编码有关。

7.2 text类型

TEXT（65535字节，64kb）类型用来保存文本类型的字符串，总共包含4中类型：TINYTEXT,TEXT,MEDIUMTEXT和LONGTEXT。
在向text类型的字段保存和查询数据时，系统自动按照实际长度存储，不需要按照预先定义的长度，这一点和varchar相同。

text和blob类型的数据删除后容易导致“空洞”，使得文件的碎片比较多。所以频繁使用表 不建议包含text类型字段，建议单独分出去，单独用一个表。

7.3 ENUM类型

设置字段时，ENUM类型只允许从成员和null中选取单个值，不能一次选取多个值。

当ENUM类型包含1-255个成员时，需要1个字节存储。包含256-65535个成员时需要2个字节存储。
ENUM('第一','第二')

7.5 SET类型

SET和ENUM类似，但是set成员上限是64个，可以选取多个值。

---

八、二进制字符串类型

二进制字符串类型主要存储一些二进制数据，比如存储图片、音频和视频等。但是实际中文件一般都上传在文件服务器。

8.1 BINARY 与VARBINARY类型

binary和varbinary类似与char和varchar。M表示字节。
（1） binary(M)最多存储255字节。如果未指定，则默认为1个字节。
（2） varbinary必须指定(M),否则报错。

8.1 BLOB类型