0基础学MySQL数据库—从小白到大牛（11）数据类型精讲

一、MySQL中的数据类型

类型	类型举例
整数类型	TINYINT、SMALLINT、MEDIUMINT、INT(或INTEGER)、BIGINT
浮点类型	FLOAT、DOUBLE
定点数类型	DECIMAL
位类型	BIT
日期时间类型	YEAR、TIME、DATE、DATETIME、TIMESTAMP
文本字符串类型	CHAR、VARCHAR、TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT
枚举类型	ENUM
集合类型	SET
二进制字符串类型	BINARY、VARBINARY、TINYBLOB、BLOB、MEDIUMBLOB、LONGBLOB
JSON类型	JSON对象、JSON数组
空间数据类型	单值类型：GEOMETRY、POINT、LINESTRING、POLYGON； 集合类型：MULTIPOINT、MULTILINESTRING、MULTIPOLYGON、 GEOMETRYCOLLECTION

浮点类型的FLOAT、DOUBLE如果位数过长，容易造成精度损失，推荐用DECIMAL

常见数据类型的属性，如下：

MySQL关键字	含义
NULL	数据列可包含NULL值
NOT NULL	数据列不允许包含NULL值
DEFAULT	默认值
PRIMARY KEY	主键
AUTO_INCREMENT	自动递增，适用于整数类型
UNSIGNED	无符号
CHARACTER SET name	指定一个字符集

1.2关于属性

CHARACTER SET name; 作用是在创建数据库或者表又或者是字段的时候都可以指定字符集

1.创建数据库时指定字符集：

CREATE DATABASE IF NOT EXISTS dbname CHARACTER SET 'utf8';

2.创建表时候也可以指定字符集：

CREATE TABLE temp(id INT)CHARACTER SET 'utf8';

3.甚至创建字段的时候也可以指定字符集

CREATE TABLE temp1(id INT,name VARCHAR(15)CHARACTER 'utf8');

二、整数类型

2.1类型介绍

整数类型一共有 5 种

• TINYINT
• SMALLINT
• MEDIUMINT
• INT（INTEGER）
• BIGINT

适用场景：
==TINYINT==：一般用于枚举数据，比如系统设定取值范围很小且固定的场景。

==SMALLINT==：可以用于较小范围的统计数据，比如统计工厂的固定资产库存数量等。

==MEDIUMINT==：用于较大整数的计算，比如车站每日的客流量等。

==INT、INTEGER==：取值范围足够大，一般情况下不用考虑超限问题，用得最多。比如商品编号。

==BIGINT==：只有当你处理特别巨大的整数时才会用到。比如双十一的交易量、大型门户网站点击量、证券公司衍生产品持仓等。

三、浮点类型

3.1类型介绍

首先说明，实际开发中有关浮点数类型的都替换成定点数类型DECIMAL。

浮点数和定点数类型的特点是可以 处理小数 ，你可以把整数看成小数的一个特例。因此，浮点数和定点数的使用场景，比整数大多了。 MySQL支持的浮点数类型，分别是 FLOAT、DOUBLE、REAL。

• FLOAT 表示单精度浮点数;
• DOUBLE 表示双精度浮点数；

​区别：==FLOAT==占用字节数少，取值范围小==DOUBLE==占用字节数多，取值范围也大

浮点数的无符号取值只相当于有符号取值的一半也就相当于是有符号取值范围大于等于零的部分，不像整数一样，是有符号的两倍
在MySQL中，单精度使用4字节，双精度使用8字节
然而在MySQL中，浮点数类型有个缺陷，就是不精准。下面我来重点解释一下为什么 MySQL 的浮点数不够精准。
比如，我们设计一个表，有f1这个字段，插入值分别为0.47, 0.44, 0.19，我们期待的运行结果是：0.47 + 0.44 + 0.19 = 1.1。而使用sum之后查询：

mysql> CREATE TABLE test_double2(     -> f1 DOUBLE    -> );Query OK, 0 rows affected (0.01 sec)mysql> INSERT INTO test_double2     -> VALUES(0.47),(0.44),(0.19);Query OK, 3 rows affected (0.00 sec)Records: 3  Duplicates: 0  Warnings: 0mysql> SELECT SUM(f1)    -> FROM test_double2;+--------------------+| SUM(f1)            |+--------------------+| 1.0999999999999999 |+--------------------+1 row in set (0.00 sec)

结果发现，出来的数值是1.0999999999999999，这和我们期待的结果不一样
我们做一下比较

mysql> SELECT SUM(f1) = 1.1,1.1 = 1.1    -> FROM test_double2;+---------------+-----------+| SUM(f1) = 1.1 | 1.1 = 1.1 |+---------------+-----------+|             0 |         1 |+---------------+-----------+1 row in set (0.00 sec)

可以发现虽然误差很小，但确实有误差，这样就导致了精度不精准，在编程中，如果用到浮点数，要特别注意误差问题，因为浮点数是不准确的，所以我们要避免使用 “ = ” 来判断两个数是否相等同时，在一些对精确度要求较高的项目中，千万不要使用浮点数，不然会导致结果错误，甚至是造成不可挽回的损失。那么，MySQL 有没有精准的数据类型呢？当然有，这就是定点数类型： DECIMAL 。

四、定点数类型

4.1定点数介绍

MySQL中的定点数类型只有 DECIMAL 一种类型。

使用 DECIMAL(M,D) 的方式表示高精度小数。其中，==M被称为精度==，==D被称为标度==。

• 0
• 0
• D

例如，定义DECIMAL（5,2）的类型，表示该列取值范围是-999.99~999.99

DECIMAL(M,D)的最大取值范围与DOUBLE类型一样，但是有效的数据范围是由M和D决定的。

DECIMAL 的存储空间并不是固定的，由精度值M决定，总共占用的存储空间为M+2个字节。也就是说，在一些对精度要求不高的场景下，比起占用同样字节长度的定点数，浮点数表达的数值范围可以更大一些。

定点数在MySQL内部是以==字符串==的形式进行存储，这就决定了它一定是精准的。

当DECIMAL类型不指定精度和标度时，其默认为DECIMAL(10,0)。当数据的精度超出了定点数类型的精度范围时，则MySQL同样会进行四舍五入处理。

浮点数 vs 定点数

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

定点数类型取值范围相对小，但是精准，没有误差，适合于对精度要求极高的场景（比如涉及金额计算的场景）

修改数据类型为DECIMALALTER TABLE tablename MODIFY f1 DECIMAL(5,2);

五、位类型bit

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

二进制字符串类型	长度	长度范围	占用空间
BIT(M)	M	1	约为(M + 7)/8个字节

BIT类型，如果没有指定(M)，==默认是1位==。这个1位，表示只能存1位的二进制值。这里(M)是表示二进制的 位数，位数最小值为1，最大值为64。
注意：在向BIT类型的字段中插入数据时，一定要确保插入的数据在BIT类型支持的范围内。
使用SELECT命令查询位字段时，可以用 BIN() 或 HEX() 函数进行读取。
BIN()、HEX()分别是以二进制和十六进制进行显示，因为在可视化界面显示的是特殊格式。

六、日期与时间类型

开发中规范：时间都用标准格式写，用单引号，间隔符，年份用4位。

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

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

• YEAR 类型通常用来表示年
• DATE 类型通常用来表示年、月、日
• TIME 类型通常用来表示时、分、秒
• DATETIME 类型通常用来表示年、月、日、时、分、秒
• TIMESTAMP 类型通常用来表示带时区的年、月、日、时、分、秒

类型	名称	字节	日期格式	最小值	最大值
YEAR	年	1	YYYY或YY	1901	2155
TIME	时间	3	HH:MM:SS	-838:59:59	838:59:59
DATE	日期	3	YYYY-MM-DD	1000-01-01	9999-12-03
DATETIME	日期时间	8	YYYY-MM-DD HH:MM:SS	1000-01-01 00:00:00	9999-12-31 23:59:59
TIMESTAMP	日期时间	4	YYYY-MM-DD HH:MM:SS	1970-01-01 00:00:00 UTC	2038-01-19 03:14:07UTC

6.1 YEAR

• YEAR类型用来表示年份，在所有的日期时间类型中所占用的存储空间最小，只需要1个字节的存储空间。

6.2DATE

DATE类型表示日期，没有时间部分，格式为YYYY-MM-DD ，其中，YYYY表示年份，MM表示月份，DD表示日期。需要 3个字节 的存储空间。在向DATE类型的字段插入数据时，同样需要满足一定的格式条件。

• 使用 CURRENT_DATE() 或者NOW()函数，会插入当前系统的日期。

6.3TIME

TIME

• TIME类型用来表示时间，不包含日期部分。在MySQL中，需要3个字节 的存储空间来存储TIME类型的数据，可以使用“HH:MM:SS”格式来表示TIME类型，其中，HH表示小时，MM表示分钟，SS表示秒。
• 在MySQL中，向TIME类型的字段插入数据时，也可以使用几种不同的格式。
• 1.可以使用带有冒号的 字符串，比如’ D HH:MM:SS’ 、’ HH:MM:SS ‘、’ HH:MM‘、’ D HH:MM ‘、’ D HH ‘或’ SS '格式，都能被正确地插入TIME类型的字段中。其中D表示天，其最小值为0，最大值为34。如果使用带有D格式的字符串 插入TIME类型的字段时，D会被转化为小时，计算格式为D*24+HH。当使用带有冒号并且不带D的字符串 表示时间时，表示当天的时间，比如12:10表示12:10:00，而不是00:12:10。
• 有冒号会变成-时分
• 2.可以使用不带有冒号的字符串或者数字，格式为’ HHMMSS '或者 HHMMSS 。如果插入一个不合法的字符串或者数字，MySQL在存储数据时，会将其自动转化为00:00:00进行存储。比如1210，MySQL会将最右边的两位解析成秒，表示00:12:10，而不是12:10:00。
• 没有冒号会变成-分秒
• 3.使用 CURRENT_TIME()或者NOW()，会插入当前系统的时间。

mysql> INSERT INTO test_time1    -> VALUES('2 12:30:29'), ('12:35:29'), ('12:40'), ('2 12:40'),('1 05'), ('45');Query OK, 6 rows affected (0.00 sec)Records: 6  Duplicates: 0  Warnings: 0mysql> INSERT INTO test_time1    -> VALUES ('123520'), (124011),(1210);Query OK, 3 rows affected (0.00 sec)Records: 3  Duplicates: 0  Warnings: 0mysql> INSERT INTO test_time1    -> VALUES (NOW()), (CURRENT_TIME());Query OK, 2 rows affected (0.00 sec)Records: 2  Duplicates: 0  Warnings: 0mysql> SELECT *     -> FROM test_time1;+----------+| f1       |+----------+('2 12:30:29')| 60:30:29 |('12:35:29')| 12:35:29 |('12:40')| 12:40:00 |('2 12:40')| 60:40:00 |('1 05'),| 29:00:00 |('45')| 00:00:45 |('123520')| 12:35:20 |(124011)| 12:40:11 |(1210)| 00:12:10 |NOW()| 15:50:01 |CURRENT_TIME()| 15:50:01 |+----------+11 rows in set (0.00 sec)为方便查看，这边把插入语句和结果放一起

6.4DATETIME

最常用的类型
DATETIME类型在所有的日期时间类型中占用的存储空间最大，总共需要 8 个字节的存储空间。在格式上为DATE类型和TIME类型的组合，可以表示为 YYYY-MM-DD HH:MM:SS ，其中YYYY表示年份，MM表示月份，DD表示日期，HH表示小时，MM表示分钟，SS表示秒。
在向DATETIME类型的字段插入数据时，同样需要满足一定的格式条件。标准格式==YYYY-MM-DD HH:MM:SS==

6.5 TIMESTAMP

TIMESTAMP类型也可以表示日期时间，其显示格式与DATETIME类型相同，都是 YYYY-MM-DD HH:MM:SS ，需要4个字节的存储空间。但是TIMESTAMP存储的时间范围比DATETIME要小很多，只能存储“1970-01-01 00:00:01 UTC”到“2038-01-19 03:14:07 UTC”之间的时间。其中，UTC表示世界统一时间，也叫作世界标准时间。

标准格式YY-MM-DD HH:MM:SS。

TIMESTAMP和DATETIME的区别：

• TIMESTAMP存储空间比较小，表示的日期时间范围也比较小
• 底层存储方式不同，TIMESTAMP底层存储的是毫秒值，距离1970-1-1 0:0:0 0毫秒的毫秒值。
• 两个日期比较大小或日期计算时，TIMESTAMP更方便、更快。
• TIMESTAMP和时区有关。TIMESTAMP会根据用户的时区不同，显示不同的结果。而DATETIME则只能 反映出插入时当地的时区，其他时区的人查看数据必然会有误差的。

用得最多的日期时间类型，就是==DATETIME==。虽然 MySQL 也支持 YEAR（年）、 TIME（时间）、DATE（日期），以及 TIMESTAMP 类型，但是在实际项目中，尽量用 DATETIME类型。因为这个数据类型包括了完整的日期和时间信息，取值范围也最大，使用起来比较方便。毕竟，如果日期时间信息分散在好几个字段，很不容易记，而且查询的时候，SQL 语句也会更加复杂。

此外，一般存注册时间、商品发布时间等，不建议使用DATETIME存储，而是使用时间戳，因为DATETIME虽然直观，但不便于计算。

mysql> SELECT UNIX_TIMESTAMP();+------------------+| UNIX_TIMESTAMP() |+------------------+|       1657615054 |+------------------+1 row in set (0.00 sec)mysql> mysql> SELECT FROM_UNIXTIME( 1657615054, '%Y%m%d' );+---------------------------------------+| FROM_UNIXTIME( 1657615054, '%Y%m%d' ) |+---------------------------------------+| 20220712                              |+---------------------------------------+1 row in set (0.00 sec)

两个日期比较大小或日期计算时，TIMESTAMP更方便、更快。因为TIMESTAMP底层存储的是毫秒数，便于计算。

七、文本字符串类型

在实际的项目中，我们还经常遇到一种数据，就是字符串数据。

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

7.1CHAR与VARCHAR

CHAR是固定长度的字符类型、VARCHAR是可变长度的字符类型
从CHAR(4)和VARCHAR(4)检索值并不是相同的，因为检索的时候，CHAR从尾部删除了空格

CHAR

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

VARCHAR

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

mysql> CREATE TABLE vc (    -> v varchar(4),    -> c char(4)    -> );Query OK, 0 rows affected (0.01 sec)mysql> INSERT INTO vc VALUES('AB   ','AB   ');Query OK, 1 row affected, 1 warning (0.00 sec)mysql> SELECT CONCAT(v,'+'),CONCAT(c,'+') FROM vc;+---------------+---------------+| CONCAT(v,'+') | CONCAT(c,'+') |+---------------+---------------+| AB  +         | AB+           |+---------------+---------------+1 row in set (0.00 sec

说明：VARCHAR能声明的最大长度并非65535，而是21845，原因65535 = 21845 * 3，一个汉字占3字节。

CHAR使用情况：

• 存储很短信息
• 固定长度信息
• 十分频繁改变的column(数据库列)

由于CHAR是固定的，所以他的处理速度会比VARCHAR快，但是缺点是浪费存储空间，程序需要对他的尾部进行处理，对那些变化长度不大的并且对查询速度没有要求的数据可以考虑使用CHAR类型来存储。

在使用VARCHAR类型的时候，不可因为VARCHAR可以变换类型长度，就都为VARCHAR定义一个很长的长度，仍要按照需求定义长度，定义一个远超需求的长度会影响程序的效率，并有更大的可能出发BUG

在MySQL下，不同的存储引擎对CHAR和VARCHAR的使用原则有所不同，这里简单说一个InnoDB
InnoDB引擎：他是建议使用VARCHAR类型的，对于InnoDB数据表，内部结构的行存储格式没有区分固定长度和可变列长度，因此在使用固定长度的CHAR不一定比使用可变长度的VARCHAR的性能要好。

7.2TEXT类型

在MySQL中，TEXT用来保存文本类型的字符串，总共包含4种类型，分别为

• TINYTEXT
• TEXT
• MEDIUMTEXT
• LONGTEXT 类型

在向TEXT类型的字段保存和查询数据时，系统自动按照实际长度存储，不需要预先定义长度。这一点和VARCHAR类型相同。

每种TEXT类型保存的数据长度和所占用的存储空间不同，如下：

由于实际存储的长度不确定，MySQL 不允许 TEXT 类型的字段做主键。遇到这种情况，你只能采用CHAR(M)，或者 VARCHAR(M)。

开发中经验：
TEXT文本类型，可以存比较大的文本段，搜索速度稍慢，因此如果不是特别大的内容，建议使用CHAR，VARCHAR来代替。还有TEXT类型不用加默认值，加了也没用。而且text和blob类型的数据删除后容易导致“空洞”，使得文件碎片比较多，所以频繁使用的表不建议包含TEXT类型字段，建议单独分出去，单独用一个表。

8、BLOB类型

BLOB是一个二进制大对象，可以容纳可变数量的数据。

MySQL中的BLOB类型包括TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB 4种类型，它们可容纳值的最大长度不同。可以存储一个二进制的大对象，比如 图片 、 音频 和 视频 等。

需要注意的是，在实际工作中，往往不会在MySQL数据库中使用BLOB类型存储大对象数据，通常会将图片、音频和视频文件存储到服务器的磁盘上，并将图片、音频和视频的访问路径存储到MySQL中。

二进制字符串类型	值的长度	长度范围	占用空间
TINYBLOB	L	0	L + 1 个字节
BLOB	L	0	L + 2 个字节
MEDIUMBLOB	L	0	（相当于16MB）
LONGBLOB	L	0	（相当于4GB）

TEXT和BLOB的使用注意事项：
在使用text和blob字段类型时要注意以下几点，以便更好的发挥数据库的性能。

① BLOB和TEXT值也会引起自己的一些问题，特别是执行了大量的删除或更新操作的时候。删除这种值==会在数据表中留下很大的"空洞"==以后填入这些"空洞"的记录可能长度不同。为了提高性能，建议定期使用 OPTIMIZE TABLE 功能对这类表进行碎片整理 。

② 如果需要对大文本字段进行模糊查询，MySQL 提供了前缀索引 。但是仍然要在不必要的时候避免检索大型的BLOB或TEXT值。例如，SELECT * 查询就不是很好的想法，除非你能够确定作为约束条件的WHERE子句只会找到所需要的数据行。否则，你可能毫无目的地在网络上传输大量的值。

③ 把BLOB或TEXT列 分离到单独的表 中。在某些环境中，如果把这些数据列移动到第二张数据表中，可以让你把原数据表中的数据列转换为固定长度的数据行格式，那么它就是有意义的。这会减少主表中的碎片 ，使你得到固定长度数据行的性能优势。它还使你在主数据表上运行 SELECT * 查询的时候不会通过网络传输大量的BLOB或TEXT值。

9、小结

在定义数据类型时，如果确定是整数 ，就用 INT ；如果是小数 ，一定用定点数类型DECIMAL(M,D)； 如果是日期与时间，就用 DATETIME 。

这样做的好处是，首先确保你的系统不会因为数据类型定义出错。不过，凡事都是有两面的，可靠性好，并不意味着高效。比如，TEXT 虽然使用方便，但是效率不如 CHAR(M) 和 VARCHAR(M)。

关于字符串的选择，建议参考如下阿里巴巴的《Java开发手册》规范：

阿里巴巴《Java开发手册》之MySQL数据库：

• 任何字段如果为非负数，必须是 UNSIGNED
• 【强制】小数类型为 DECIMAL，禁止使用 FLOAT 和 DOUBLE。
• 说明：在存储的时候，FLOAT 和DOUBLE 都存在精度损失的问题，很可能在比较值的时候，得到不正确的结果。如果存储的数据范围超过DECIMAL 的范围，建议将数据拆成整数和小数并分开存储。
• 【强制】如果存储的字符串长度几乎相等，使用 CHAR 定长字符串类型。
• 【强制】VARCHAR 是可变长字符串，不预先分配存储空间，长度不要超过 5000。如果存储长度大于此值，定义字段类型为 TEXT，独立出来一张表，用主键来对应，避免影响其它字段索引效率。