MySQL数据类型详解

1.数据类型

每种编程语言都有自己所定义的数据类型，mysql也不例外，平时我们在创建表时，需要根据业务要求，结合存储、索引、字段临界值等条件来为字段定制不一样的类型。下面我们一起学习下mysql的几种常用的数据类型。

MySQL支持多种类型的SQL数据类型：数值，日期和时间类型，字符串（字符和字节）类型，空间类型和 JSON数据类型等

1.1、mysql中编码和字符

在 mysql 中，一个中文汉字所占的字节数与编码格式有关：

如果是GBK编码，则一个中文汉字占2个字节，英文占1个字节
如果是UTF8编码，则一个中文汉字占3个字节，而英文字母占1字节。
比如定义某个字段数据类型为：varchar(32)，表示这个可以存储 32 个字符，此时表示的是字符，所以跟中英文无关，也就是该字段可以存储 32 个中文，或者是 32 个英文，或者是 32 个中文和英文的混搭都行。但如果字符数超过 32 个的话就会报错。

1.2、MySQL中的数据类型

1.3、常见数据类型的属性

2.各种数据类型精讲

2.1、整数类型

在 MySQL 中支持的 5 个主要整数类型是 TINYINT，SMALLINT，MEDIUMINT，INT 和 BIGINT。这些类型在很大程度上是相同的，只有它们存储的值的大小是不相同的。

长度： 整数类型可以被指定长度，例如：INT(11)表示长度为11的INT类型。长度在大多数场景是没有意义的，它不会限制值的合法范围，只会影响显示字符的个数，而且需要和UNSIGNED ZEROFILL属性配合使用才有意义。
**例子：**假定类型设定为INT(5)，属性为UNSIGNED ZEROFILL，如果用户插入的数据为12的话，那么数据库实际存储数据为00012。
应用场景：
（1）TINYINT ：一般用于枚举数据，比如系统设定取值范围很小且固定的场景。
（2）SMALLINT ：可以用于较小范围的统计数据，比如统计工厂的固定资产库存数量等。
（3）MEDIUMINT ：用于较大整数的计算，比如车站每日的客流量等。
（4）INT、INTEGER ：取值范围足够大，一般情况下不用考虑超限问题，用得最多。比如商品编号。
（5）BIGINT ：只有当你处理特别巨大的整数时才会用到。比如双十一的交易量、大型门户网站点击量、证券公司衍生产品持仓等。

2.2、实数类型

DECIMAL可以用于存储比BIGINT还大的整型，能存储精确的小数。
其中，FLOAT 表示单精度浮点数；DOUBLE 表示双精度浮点数；而FLOAT和DOUBLE是有取值范围的，并支持使用标准的浮点进行近似计算。

计算时FLOAT和DOUBLE相比DECIMAL效率更高一些，DECIMAL你可以理解成是用字符串进行处理。
数据精度说明
（1）对于浮点类型，在MySQL中单精度值使用 4 个字节，双精度值使用 8 个字节。
（2）MySQL允许使用 非标准语法 （其他数据库未必支持，因此如果涉及到数据迁移，则最好不要这么用）： FLOAT(M,D) 或 DOUBLE(M,D) 。这里，M称为精度 ，D称为标度 。(M,D)中 M=整数位+小数位，D=小数位。 D<=M<=255，0<=D<=30。
例如，定义为FLOAT(5,2)的一个列可以显示为-999.99-999.99。如果超过这个范围会报错。
（3）FLOAT和DOUBLE类型在不指定(M,D)时，默认会按照实际的精度（由实际的硬件和操作系统决定）来显示。
（4）浮点类型，也可以加 UNSIGNED ，但是不会改变数据范围。
例如：FLOAT(3,2) UNSIGNED仍然只能表示0-9.99的范围。
（5）不管是否显式设置了精度(M,D)，这里MySQL的处理方案如下：

如果存储时，整数部分超出了范围，MySQL就会报错，不允许存这样的值；
如果存储时，小数点部分若超出范围，就分以下情况：
（1）若四舍五入后，整数部分没有超出范围，则只警告，但能成功操作并四舍五入删除多余的小数位后保存。
例如在FLOAT(5,2)列内插入999.009，近似结果是999.01。
（2）若四舍五入后，整数部分超出范围，则MySQL报错，并拒绝处理。
如FLOAT(5,2)列内插入999.995和-999.995都会报错。

（6）从MySQL 8.0.17开始，FLOAT(M,D) 和DOUBLE(M,D)用法在官方文档中已经明确不推荐使用，将来可能被移除。另外，关于浮点型FLOAT和DOUBLE的UNSIGNED也不推荐使用了，将来也可能被移除。
对于DECIMAL：
（1）MySQL中的定点数类型只有 DECIMAL 一种类型。
使用 DECIMAL(M,D) 的方式表示高精度小数。其中，M被称为精度，D被称为标度。0<=M<=65， 0<=D<=30，D<M。
例如，定义DECIMAL（5,2）的类型，表示该列取值范围是-999.99~999.99。
（2）DECIMAL(M,D)的最大取值范围与DOUBLE类型一样，但是有效的数据范围是由M和D决定的。DECIMAL 的存储空间并不是固定的，由精度值M决定，总共占用的存储空间为M+2个字节。也就是
说，在一些对精度要求不高的场景下，比起占用同样字节长度的定点数，浮点数表达的数值范围可以更大一些。
（3）定点数在MySQL内部是以 字符串的形式进行存储，这就决定了它一定是精准的。
（4）浮点数和定点数的对比：

浮点数相对于定点数的优点是在长度一定的情况下，浮点类型取值范围大，但是不精准，适用于需要取值范围大，又可以容忍微小误差的科学计算场景（比如计算化学、分子建模、流体动力学等）;
定点数类型取值范围相对小，但是精准，没有误差，适合于对精度要求极高的场景 （比如涉及金额计算的场景）。

2.3、日期与时间类型

日期与时间是重要的信息，在我们的系统中，几乎所有的数据表都用得到。原因是客户需要知道数据的时间标签，从而进行数据查询、统计和处理。

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

尽量使用timestamp，空间效率高于datetime，
用整数保存时间戳通常不方便处理。

注意： 为什么时间类型 TIME 的取值范围不是 -23:59:59～23:59:59 呢？
原因是 MySQL 设计的 TIME 类型，不光表示一天之内的时间，而且可以用来表示一个时间间隔，这个时间间隔可以超过 24 小时。
TIMESTAMP和DATETIME的区别：
（1）TIMESTAMP存储空间比较小，表示的日期时间范围也比较小。
（2）底层存储方式不同，TIMESTAMP底层存储的是毫秒值，距离1970-1-1 0:0:0 0毫秒的毫秒值。
（3）两个日期比较大小或日期计算时，TIMESTAMP更方便、更快。
（4）TIMESTAMP和时区有关。TIMESTAMP会根据用户的时区不同，显示不同的结果。而DATETIME则只能反映出插入时当地的时区，其他时区的人查看数据必然会有误差的。
总结：
用得最多的日期时间类型，就是 DATETIME 。虽然 MySQL 也支持 YEAR（年）、 TIME（时间）、DATE（日期），以及 TIMESTAMP 类型，但是在实际项目中，尽量用 DATETIME 类型。因为这个数据类型包括了完整的日期和时间信息，取值范围也最大，使用起来比较方便。毕竟，如果日期时间信息分散在好几个字段，很不容易记，而且查询的时候，SQL 语句也会更加复杂。
此外，一般存注册时间、商品发布时间等，不建议使用DATETIME存储，而是使用时间戳 ，因为DATETIME虽然直观，但不便于计算。

2.4、字符串类型

MySQL中，文本字符串总体上分为 CHAR 、VARCHAR 、 TINYTEXT 、 TEXT 、 MEDIUMTEXT 、LONGTEXT 、 ENUM 、 SET 等类型。
（1）CHAR类型

1、CHAR(M) 类型一般需要预先定义字符串长度。如果不指定(M)，则表示长度默认是1个字符。
2、如果保存时，数据的实际长度比CHAR类型声明的长度小，则会在右侧填充 空格以达到指定的长
度。当MySQL检索CHAR类型的数据时，CHAR类型的字段会去除尾部的空格。
3、定义CHAR类型字段时，声明的字段长度即为CHAR类型字段所占的存储空间的字节数

（2）VARCHAR类型

1、 VARCHAR(M) 定义时， 必须指定长度M，否则报错。
2、MySQL4.0版本以下，varchar(20)：指的是20字节，如果存放UTF8汉字时，只能存6个（每个汉字3字节） ；MySQL5.0版本以上，varchar(20)：指的是20字符。
3、检索VARCHAR类型的字段数据时，会保留数据尾部的空格。VARCHAR类型的字段所占用的存储空间为字符串实际长度加1个字节。

（3）CHAR和VARACHAR的比较及其应用场景

1、情况1：存储很短的信息。比如门牌号码101，201……这样很短的信息应该用char，因为varchar还要占个byte用于存储信息长度，本来打算节约存储的，结果得不偿失。
2、情况2:固定长度的。比如使用uuid作为主键，那用char应该更合适。因为他固定长度, varchar动态根据长度的特性就消失了，而且还要占个长度信息。
3、情况3：十分频繁改变的column。因为varchar每次存储都要有额外的计算，得到长度等工作，如果一个非常频繁改变的，那就要有很多的精力用于计算，而这些对于char来说是不需要的。
4、情况4：具体存储引擎中的情况：
（1）MyISAM 数据存储引擎和数据列：MyISAM数据表，最好使用固定长度(CHAR)的数据列代替可变长度(VARCHAR)的数据列。这样使得整个表静态化，从而使 数据检索更快 ，用空间换时间。
（2）MEMORY 存储引擎和数据列：MEMORY数据表目前都使用固定长度的数据行存储，因此无论使用CHAR或VARCHAR列都没有关系，两者都是作为CHAR类型处理的。
（4）InnoDB 存储引擎，建议使用VARCHAR类型。因为对于InnoDB数据表，内部的行存储格式并没有区分固定长度和可变长度列（所有数据行都使用指向数据列值的头指针），而且主要影响性能的因素是数据行使用的存储总量，由于char平均占用的空间多于varchar，所以除了简短并且固定长度的，其他考虑varchar。这样节省空间，对磁盘I/O和数据存储总量比较好。

2.5、ENUM类型

（1）ENUM类型也叫作枚举类型，ENUM类型的取值范围需要在定义字段时进行指定。设置字段值时，ENUM类型只允许从成员中选取单个值，不能一次选取多个值。
（2）其所需要的存储空间由定义ENUM类型时指定的成员个数决定。

1、当ENUM类型包含1～255个成员时，需要1个字节的存储空间。
2、当ENUM类型包含256～65535个成员时，需要2个字节的存储空间。
3、ENUM类型的成员个数的上限为65535个。

2.6、SET类型

（1）SET表示一个字符串对象，可以包含0个或多个成员，但成员个数的上限为64。设置字段值时，可以取取值范围内的0个或多个值。
（2）当SET类型包含的成员个数不同时，其所占用的存储空间也是不同的，具体如下：

（3）SET类型在存储数据时成员个数越多，其占用的存储空间越大。注意：SET类型在选取成员时，可以一次选择多个成员，这一点与ENUM类型不同。

2.7、二进制字符串类型

（1）MySQL中的二进制字符串类型主要存储一些二进制数据，比如可以存储图片、音频和视频等二进制数据。
（2）MySQL中支持的二进制字符串类型主要包括BINARY、VARBINARY、 TINYBLOB、 BLOB、MEDIUMBLOB 和LONGBLOB类型。
BINARY与VARBINARY类型介绍
（1）BINARY和VARBINARY类似于CHAR和VARCHAR，只是它们存储的是二进制字符串。
（2）BINARY (M)为固定长度的二进制字符串，M表示最多能存储的字节数，取值范围是0~255个字符。如果未指定(M)，表示只能存储 1个字节 。
例如BINARY (8)，表示最多能存储8个字节，如果字段值不足(M)个字节，将在右边填充’\0’以补齐指定长度。
（3）VARBINARY (M)为可变长度的二进制字符串，M表示最多能存储的字节数，总字节数不能超过行的字节长度限制65535，另外还要考虑额外字节开销，VARBINARY类型的数据除了存储数据本身外，还需要1或2个字节来存储数据的字节数。VARBINARY类型 必须指定(M) ，否则报错。

2.8、JSON类型

（1）JSON（JavaScript Object Notation）是一种轻量级的 数据交换格式 。简洁和清晰的层次结构使得 JSON 成为理想的数据交换语言。它易于人阅读和编写，同时也易于机器解析和生成，并有效地提升网络传输效率。JSON 可以将 JavaScript 对象中表示的一组数据转换为字符串，然后就可以在网络或者程序之间轻
松地传递这个字符串，并在需要的时候将它还原为各编程语言所支持的数据格式。
（2）在MySQL 5.7中，就已经支持JSON数据类型。在MySQL 8.x版本中，JSON类型提供了可以进行自动验证的JSON文档和优化的存储结构，使得在MySQL中存储和读取JSON类型的数据更加方便和高效。 创建数据表，表中包含一个JSON类型的字段 js 。

2.9、空间类型

（1）MySQL 空间类型扩展支持地理特征的生成、存储和分析。这里的地理特征表示世界上具有位置的任何东西，可以是一个实体，例如一座山；可以是空间，例如一座办公楼；也可以是一个可定义的位置，例如一个十字路口等等。MySQL中使用 Geometry（几何） 来表示所有地理特征。Geometry指一个点或点的集合，代表世界上任何具有位置的事物。

（2）MySQL的空间数据类型（Spatial Data Type）对应于OpenGIS类，包括单值类型：GEOMETRY、POINT、 LINESTRING、POLYGON以及集合类型：MULTIPOINT、MULTILINESTRING、MULTIPOLYGON、 GEOMETRYCOLLECTION 。