MySQL数据类型详解

目录

• mysql数据类型
• 数据类型分类
• 数值类型
• tinyint类型
• bit类型
• float类型
• decimal类型
• 字符串类型
• char类型
• varchar类型
• char和varchar比较
• 日期和时间类型
• enum和set类型

MySQL数据类型

数据类型的作用：

• 决定了存储数据时应该开辟的空间大小。
• 决定了如何识别一个特定的二进制序列。
• 决定了数据的取值范围。

数据类型分类

分类	数据类型	说明
数值类型	BIT(M)	位类型：M 指定位数，默认值为 1，范围为 1–64
	BOOL	布尔类型：使用 1 表示真，使用 0 表示假
	TINYINT [UNSIGNED]	占用 1 字节，默认为有符号
	SMALLINT [UNSIGNED]	占用 2 字节，默认为有符号
	MEDIUMINT [UNSIGNED]	占用 3 字节，默认为有符号
	INT [UNSIGNED]	占用 4 字节，默认为有符号
	BIGINT [UNSIGNED]	占用 8 字节，默认为有符号
	FLOAT[(M,D)] [UNSIGNED]	M 指定显示长度，D 指定小数位数，占用 4 字节
	DOUBLE[(M,D)] [UNSIGNED]	M 指定显示长度，D 指定小数位数，占用 8 字节
	DECIMAL(M,D) [UNSIGNED]	M 指定显示长度，D 指定小数位数；每 4 个字节表示 9 个数字，小数点占用 1 字节
文本、二进制类型	CHAR(L)	固定长度字符串：L 指定字符串长度，最大为 255
	VARCHAR(L)	可变长度字符串：L 指定字符串长度上限，最多占用 65535 字节
	BLOB	用于存储二进制数据
	TEXT	用于存储大文本数据
时间日期	DATE / DATETIME	日期类型：YYYY-MM-DD 格式 / YYYY-MM-DD HH:MM:SS 格式
时间日期	TIMESTAMP	时间戳：以 YYYY-MM-DD HH:MM:SS 格式进行显示
字符串类型	ENUM	枚举类型：在定义字段时指定取值范围；只能从成员中选取单个值；存储空间由成员个数决定
字符串类型	SET	集合类型：在定义字段时指定取值范围；可从成员中选取一个或多个值；存储空间由成员个数决定

注：MySpythonQL本身是不支持bool类型的，当把一个数据设置成bool类型时，数据库会自动将其转换成tinyint(1)的数据类型，其实这个就是变相的bool类型，因为tinyint(1)只有1和0两种取值，可以分别对应bool类型的true和false。

数值类型

tinyint类型

有符号tinyint范围测试

由于tinyint类型占用1字节，因此有符号tinyint的取值范围为-128~127，插入该范围内的数据时都能成功插入。如下：

如果插入的数据不在-128~127范围内，那么插入数据时就会产生报错。如下：

无符号tinyint范围测试

由于tinyint类型占用1字节，因此无符号tinyint的取值范围为0~255，插入该范围的数据时都能成功插入。如下：

如果插入的数据不在0~255范围内，那么插入数据时就会产生报错。如下：

建议：

• 尽量不使用unsigned，对于int类型可能存放不下的数据，int unsigned同样可能存放不 下，与其如此，还不如设计时，将int类型提升为bigint类型。

bit类型

bit类型的显示方式

mysql 客户端默认开启了 --binary-as-hex。

只要列是二进制类型（BIT / BINARY / VARBINARY / BLOB），客户端就用十六进制显示，以避免乱码。所以 BIT(8) 存了十进制 10，我们看到的就是 0x0A。

bit类型的范围测试

创建一个表，表当中包含用户名name和用户性别gender，其中gender的类型可以指定为1位bit类型，因为性别只有男和女两种取值，使用1个比特位来表示用户的性别就可以节省空间。如下：

如果插入gender列的数据不是0或1，那么插入数据时就会产生报错。如下：

建议：

• 虽然MySQL提供了位类型bit，但一般不建议将数据类型设置成位类型，除非将来这个数据本身就只是给程序看的，并且数据本身非常占用资源。
• 因为查询位类型数据时，默认会按照ASCII码对应的值进行显示，这对于将来数据库管理员维护数据库或程序员调试程序都是不太方便的。

float类型

有符号float范围测试

float[(m, d)] [unsigned] : M指定显示长度，d指定小数位数，占用空间4个字节

小数：float(4,2)表示的范围是-99.99 ~ 99.99，MySQL在保存值时会进行四舍五入。

此外，由于MySQL在保存值时会进行四舍五入，因此实际可插入float(4,2)的范围为-99.994~99.994，如果插入的数据不在该范围内，那么插入数据时就会产生报错。如下：

有符号float范围测试

创建一个表，表当中包含一个float(4,2)类型的列，并指定其为无符号类型。

无符号float类型的取值范围，实际就是把对应有符号float类型中的负数部分拿走了，因此float(4,2)的取值范围为0~99.99，实际可插入的范围是0~99.994。如下：

如果插入的数据不在0~99.994范围内，那么插入数据时就会产生报错。如下：

为什么这里和tinyint类型的无符号取值不一样？

整数类型（如 TINYINT）

• 有符号 TINYINT：范围是 -128 ~ 127，总共 256 个值。
• 无符号 TINYINT：范围是 0 ~ 255，总共也是 256 个值。

可以看到：总数相等，只是符号位的解释不同。MySQL 对整数采用定长存储（补码），有符号与无符号在存储层面占用 相同的 1 个字节，所以值的“个数”完全一致。

浮点类型（FLOAT/DOUBLE）

浮点数遵循 IEEE 754 标准，存储方式不同于整数：

• 浮点数由三部分组成：
  • 符号位（sign bit）
  • 指数（exponent）
  • 尾数（mantissa/fraction）
• 当你使用 UNSIGNED 时，只是告诉 MySQL：不允许负数。
• 但浮点的存储格式本身并不会把“符号位”重新拿来扩展有效数值范围。

换句话说：

• 有符号 FLOAT：能表示负数和正数，范围是 -3.402823466E+38 ~ +3.402823466E+38。
• 无符号 FLOAT：范围只是 0 ~ 3.402823466E+38。

因为 IEEE 754 并没有定义“把符号位转换成额外的数值空间”，所以无符号浮点数的取值范围不是两倍，而只是去掉负数部分。

decimal类型

decimal(m, d) [unsigned] : 定点数m指定长度，d表示小数点的位数

创建decimal为(5，2)的表

mysql 8.0以后的版本float和decimal有什么区别

存储方式

FLOAT / DOUBLE

• 存储为 IEEE 754 二进制浮点数（单精度/双精度）。
• 值是近似的，很多十进制数不能被精确表示（比如 0.1）。
• 占用存储空间较小（FLOAT 4 字节，DOUBLE 8 字节）。

DECIMAL(p,s)

• 存储为 精确的十进制数，按字符串拆分存储再转成定点数。
• 能够完全保持插入时的数值，不存在浮点误差。
• 占用空间取决于 p（精度），一般比浮点类型大。

精度和范围

• FLOAT
  • 大约 7 位有效十进制数字。
  • 范围大，±3.402823466E+38。
• DECIMAL(p,s)
  • 精度由 p（总位数）和 s（小数位数）决定。
  • 范围取决于定义，比如 DECIMAL(20,6) 可支持 20 位数，6 位小数。
  • 精度完全可控，不存在近似问题。

注：MySQL8.0以后的版本使用float警告信息更严格

• 在 5.7 里，很多 float 精度问题不会提示。
• 在 8.0 里，MySQL 在插入 out-of-range 或者 DECIMAL ↔ FLOAT 转换时，会给 warning，帮助开发者发现潜在风险。

字符串类型

char类型

char(L): 固定长度字符串，L是可以存储的长度，单位为字符，最大长度值可以为255

由于char(2)中最多可存储2个字符，因此只要插入的字符个数不超过2个都是能够成功插入的。如下：

说明： char(2) 表示可以存放两个字符，可以是字母或汉字，但是不能超过2个， 最多只能是255

好处：

• 在不同编码中，一个字符所占的字节个数是不同的，比如utf8中一个字符占3个字节，而gbk中一个字符占2个字节。MySQL限定字符的概念不是字节，这样用户就不用关心复杂的编码细节了。

varchar类型

varchar(L): 可变长度字符串，L表示字符长度，最大长度65535个字节

创建一个表，由于varchar(6)中最多可存储6个字符，因此只要插入的字符个数不超过6都是能够成功插入的。如下：

如果插入的字符个数超过了6个，那么在插入数据时就会产生报错。如下：

varchar类型可指定的字符个数上限

关于varchar(len),len到底是多大，这个len值，和表的编码密切相关：

• varchar长度可以指定为0到65535之间的值，但是有1 - 3 个字节用于记录数据大小，所以说有效字 节数是65535-3=65532。
• 当我们的表的编码是utf8时，varchar(n)的参数n最大值是65532/3=21844[因为utf中，一个字符占 用3个字节]，如果编码是gbk，varchar(n)的参数n最大是65532/2=32766（因为gbk中，一个字符 占用2字节）。

因此在定义编码格式为utf8的表时，varchar(L)中的L如果超过了21844，则会产生报错。如下：

char和varchar比较

实际存储	char(4)	varchar(4)	char 占用字节	varchar 占用字节
abcd	abcd	abcd	4*3=12	4*3+1=13
A	A	A	4*3=12	1*3+1=4
Abcde	✘	✘http://www.devze.com	数据超过长度	数据超过长度

char和varchar的区别如下：

• char类型可存储字符上限为编程255，varchar类型可存储字符上限与表的编码格式有关。
• char(L)定义后，无论存储的字符串长度是否到达L，都会开辟用于存储L个字符的定长空间，如果存储的字符串长度超过L则会报错。
• varchar(L)定义后，会根据存储字符串的长度按需开辟空间，并且需要使用1-3字节的空间用于表示存储字符串的长度以及其他控制信息，如果存储的字符串长度超过L则会报错。

如何选取char和varchar类型？

char和varchar的优缺点如下：

• char类型的数据是定长的，因此磁盘空间比较浪费，但是效率高（直接访问定长的空间）。
• varchar类型的数据是变长javascript的，因此磁盘空间比较节省，但是效率低（需要先读取存储字符串的长度，再访问指定长度的空间）。

如果要存储的数据是定长的，那就使用char类型进行存储，比如身份证号码、手机号、md5等。如果要存储的数据是变长的，那js就使用varchar类型进行存储，比如名字、地址等。

日期和时间类型

常用的日期有如下三个：

• date :日期 'yyyy-mm-dd' ，占用三字节
• datetime 时间日期格式 'yyyy-mm-dd HH:ii:ss' 表示范围从
• timestamp ：时间戳，从1970年开始的 四字节

创建一个表，表当中包含date、datetime和timestamp三种时间日期类型的列。

查看表结构可以看到，timestamp类型的t3列是不允许为空的，它的默认值为CURRENT_TIMESTAMP。

但明显这里的值不是CURRENT_TIMESTAMP，而是NULL；

这是因为MySQL 8.0 不会再自动给 TIMESTAMP 列加上 NOT NULL DEFAULT CURRENT_TIMESTAMP，现在需要 你自己明确指定。如下图：

插入数据后t3就会自动显示当前的时间戳

更新数据：

enum和set类型

enum和set类型的区别如下：

• 在定义enum字段时需要提供若干个选项的值，在设置enum字段值时只允许选取其中的一个值。
• 在定义set字段时需要提供若干个选项的值，在设置set字段值时可以选取其中的一个或多个值。

比如人的性别只能从男和女中进行二选一，因此可以定义成enum类型，而人的爱好在提供的选项中可能存在多个，因此可以定义成set类型。

调查表案例

有一个调查表votes，需要调查人的喜好， 比如（登山，游泳，篮球，武术）中去选择(可以多选)， （男，女）[单选]

向表中插入记录时，被调查人的性别只能从男和女中进行二选一，被调查人的爱好可以从提供的若干个选项中进行多选一或多选多，多个爱好之间需要通过英文逗号隔开。

通过数字设置enum

在插入记录时，除了通过指明男女来设置性别，还可以通过插入数字1和2来设置性别。

如下：

根本原因在于，MySQL出于效率考虑，在存储enum值时实际存储的都是数字，enum中提供的选项值依次对应数字1、2、3、…，最多65535个，因此在设置enum值时可以通过数字的方式进行设置。

通过数字设置set

在插入记录时，除了通过指明多个选项来设置爱好，还可以通过数字的方式来设置。如下：

set数字设置的规则：

建议：

• 虽然enum和set可以通过数字的方式进行设置，但严重不推荐这种做法，因为这样的SQL可读性太差，导致后期维护成本变高。

enum和set查找

如果想要筛选出调查表中所有男同志的信息，那么直接在筛选时指明gender='男'即可，因为enum类型的值只能多选一。如下：

但如果要筛选出调查表中爱好包含登山的人的信息就比较麻烦了，如果继续使用上述方式，那么最终筛选出来的是爱好仅为登山的人的信息。如下：

这时需要借助find_in_set(str,strlist)函数，该函数的作用是查询strlist中是否包含str，如果包含则返回str在strlist中的位置（从1开始），否则返回0。

通过select可以对find_in_set函数进行验证，依次查找集合a,b,c中是否包含字符a、b、d，这时在查找字符a和b时就会得到其在集合中的下标，而在查找字符d时就会得到0值。如下：

这时就可以通过select搭配find_in_set函数，来筛选出爱好包含登山的人的信息了。如下：

到此这篇关于MySQL数据类型的文章就介绍到这了,更多相关mysql数据类型内容请搜索编程客栈(www.devze.com)以前的文章或继续浏览下面的相关文章希望大家以后多多支持编程客栈(www.devze.com)！