MySQL基础『数据类型』-阿里云开发者社区

1.数据类型一览

作为专门用来存储数据的 数据库，MySQL 提供了丰富的数据类型，覆盖绝大多数使用场景

分类	数据类型	说明
数值类型	BIT(M)	位类型，M指定位数，默认为 1，范围 [1, 64]
	TINYINT [UNSIGNED]	有符号范围 [-128, 127]，无符号范围 [0, 255]，默认为有符号
	BOOL	0 表示假，1 表示真，在许多数据库系统中，BOOL 与 TINYINT(1) 是同义的
	SMALLINT [UNSIGNED]	有符号范围 [-2^15, 2^15-1]，无符号范围 [0, 2^16-1]
	INT [UNSIGNED]	有符号范围 [-2^31, 2^31-1]，无符号范围 [0, 2^32-1]
	BIGINT [UNSIGNED]	有符号范围 [-2^63, 2^63-1]，无符号范围 [0, 2^64-1]
	FLOAT [(M, D)] [UNSIGNED]	M 指定显示长度，D 指定小数位数，大小依赖具体平台，通常占用 4 字节，但在某些情况下可能达到 8 字节
	DOUBLE [(M, D)] [UNSIGNED]	比 FLOAT 精度更高，大小通常为 8 字节，在某些情况下可能更大
	DECIMAL(M, D) [UNSIGNED]	比 DOUBLE 精度更高，能完全保证数据的精度，适用于需要高精度的金融计算
字符、二进制类型	CHAR(SIZE)	固定长度字符串，最大支持 255 字符，用空格填充字符串到指定的长度
	VARCHAR(SIZE)	可变长度字符串，最大支持 65535 字符，它不会用空格填充
	BLOB	二进制数据
	TEXT	大文本，不支持全文索引、默认值，需要注意有一些变体
时间日期	DATE	格式为 yyyy-mm-dd
	DATETIME	格式为 yyyy-mm-dd hh:mm:ss
	TIMESTAMP	时间戳，通常表示一个时间点，包括日期和时间
其他类型	ENUM	字符串对象，表示枚举类型，只能选取其中一个值，由于在某些数据库系统中的限制，ENUM 的使用有时会受到争议
其他类型	SET	字符串对象，表示集合类型，可以选取零个或多个值

创建一个数据库 dataType

mysql> create database if not exists dataType;

选择数据库

mysql> use dataType;

2.整型

2.1.INT

首先看看最常用的 int 类型，同 C/C++ 中的一样，MySQL 中的 int 占 4 字节，支持创建无符号类型

INT [UNSIGNED]

mysql> create table if not exists testINT
(
  整型 int,
  无符号整型 int unsigned
);
mysql> desc testINT;

注意：

MySQL 中创建无符号类型，是在类型之后加上 unsigned
括号中的数字通常用于指定显示宽度，并不表示存储范围

向表中插入一组合法的数据

mysql> insert into testINT values (-1234, 4321), (1000000000, 3000000000);

整型的范围是 [-2147483648, 2147483647]，无符号整型的范围是 [0, 4294967295]，范围内的数据都可以正常插入，如果插入越界数据，则会插入失败

mysql> insert into testINT (整型) values (2147483648);
mysql> insert into testINT (整型) values (-2147483649);
mysql> insert into testINT (无符号整型) values (4294967296);
mysql> insert into testINT (无符号整型) values (-1);

由此可见：MySQL 中对于数据范围具有严格约束，会直接拦截非法数据的插入

也就是说：凡是成功插入 MySQL 中的数据，都是合法的

约束使用者，MySQL 能保证数据插入的合法性（数据库中的数据是可预期、完整的）

插入数据时，需要注意符合数据类型范围要求

除了最常用的 int 外，MySQL 还提供了其他几种衍生类型：

TINYINT极短整型，占 1 字节，范围 [-128, 127]
SMALLINT短整型，占 2 字节，范围 [-32768, 32767]
MEDIUMINT中整型，占 3 字节，范围 [-8388608, 8388607]
BIGINT大整型，占 8 字节，范围 [-9223372036854775808, 9223372036854775807]

不同大小的整型适用于不同场景中，MySQL 提供了选择，把选择的权力交给了用户，由用户根据数据库的预期大小，自由选择整型

都可以正常建表并使用

mysql> create table if not exists testIntFamily
(
  极短整型 tinyint,
  短整型 smallint,
  中整型 mediumint,
  整型 int,
  大整型 bigint
);
mysql> desc testIntFamily;
mysql> insert into testIntFamily values (127, 32767, 8388607, 2147483647, 9223372036854775807);
mysql> select * from testIntFamily;

注意：这些衍生类型如同 int 一样，可以声明为无符号类型，定义时在类型之后加上 unsigned 即可，创建无符号类型后，无法再存储负数，存储整正数的能力翻倍

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

2.2.BIT

MySQL 中提供了一个很有意思的类型：bit(m)，表示 位字段类型，M 的范围 1~64，这里的位指 比特位，64 比特位即 8 字节，接下来分别创建几个 bit 类型

BIT(M)

mysql> create table if not exists testBIT
(
  位字段类型_1 bit(1),
  位字段类型_2 bit(2),
  位字段类型_8 bit(8)
);
mysql> desc testBIT;

再分别向其中插入一些数据，查询时却看到了一些奇怪字符

mysql> insert into testBIT values (1, 3, 100), (0, 2, 255);
mysql> select * from testBIT;

这是因为 查表时，位字段类型默认按 ASCII 的形式显示，查表可以发现，100 正好对应字符 d

如果我们想让其正常显示，查询时可以使用 hex() 这个内置函数将查询结果转为十六进制显示

mysql> select hex(位字段类型_1), hex(位字段类型_2), hex(位字段类型_8) from testBIT;

注：64 表示 6 * 16 + 4 = 100, FF 则表示 15 * 16 + 15 = 255

值得注意的是，对于 BIT 类型，MySQL 也做出了相应的约束，对于超出范围的数据，拒绝插入

mysql> insert into testBIT (位字段类型_1) values (3);

这里的 3 明明只是一个整数，为什么会失败？

注意：位字段类型限制的是比特位，而非位数，3 的二进制表示为 0011，已经使用了两个比特位，自然也就超出范围了，所以 bit(1) 只能插入 0 或 1

3.浮点数

3.1.FLOAT

MySQL 支持我们熟悉的浮点数类型 float、double，所占大小分别为 4 字节和 8 字节

FLOAT [(M, D)] [UNSIGNED]
DOUBLE [(M, D)] [UNSIGNED]

mysql> create table if not exists testFloat
(
  浮点数 float(4, 2)
);
mysql> desc testFloat;

其中，float(4, 2) 中的 (4, 2) 表示当前浮点数支持显示 4 位，且小数精度为 2 位，数据范围为 [-99.99, 99.99]

float 和 double 在定义时允许不指明显示位数和小数精度

插入一些正常数据，可以看到插入成功

mysql> insert into testFloat values (50.05), (99.99), (-99.99);

插入一些超出范围的数据，直接被拦截了

mysql> insert into testFloat values (500.05);
mysql> insert into testFloat values (-500.05);
mysql> insert into testFloat values (-100.00);
mysql> insert into testFloat values (-100.0);

为什么插入 -100.0 也被拦截了？

这是因为 当小数部分不足时，MySQL 自动补齐，补齐后，实际插入的值为 -100.00，有五位数，超出范围，自然就被拦截了

MySQL 也并非是铁面无私，当我们插入的数据小数部分超过指定精度时，MySQL 会遵循四舍五入的规则，将多余的小数部分省略，五入向前进一位后，如果超出了范围，也会被拦截

mysql> insert into testFloat values (50.005), (50.000001), (99.994);
mysql> select * from testFloat;
// 下面这种情况会被拦截，因为四舍五入后，数据会变成 100.00
// mysql> insert into testFloat values (99. 995);

总之就是 MySQL 必须确保数据库中数据的合法性

如果将 float 类型定义为无符号类型，会导致它的负数部分直接丢弃

mysql> create table if not exists testFloatU
(
  无符号浮点数 float(4, 2) unsigned
);
mysql> desc testFloatU;
mysql> insert into testFloatU values (99.991), (50.00), (0);
// 插入失败
mysql> insert into testFloatU values (-99.991), (-50.00), (-1);

至于为什么这么奇怪，这是因为浮点数的存储规则不同于其他类型，采用 IEEE 754 标准，不是简单的直接使用二进制位，而是将二进制位划分为几个区间，各司其职，如果将其设为无符号类型，等价于将符号表示的二进制位直接禁用

float 存在精度损失，当插入的小数部分过长时，会导致精度丢失

比如这里先创建一张含有 float(10, 8) 类型的表

mysql> create table if not exists testFloatL
(
  浮点数 float(10, 8)
);
mysql> desc testFloatL;

现在向 testFloatL 表中插入一个较长的数据，并查询结果

mysql> insert into testFloatL values (1.23456789);
mysql> select * from testFloatL;

可以看到第八位小数精度丢失了，证明 float 在使用过程中存在 精度损失 问题

float 至多保证 7 位小数精度准确

至于 double，除了比 float 大一些之外，与 float 的使用方法一模一样，同样存在 精度损失问题，如果想要追求超高精度，可以改用 decimal 类型

3.2.DECIMAL

decimal 是 MySQL 针对高精度小数推出的浮点数类型，decimal 使用方法和 float / double 一样，在支持浮点数存储的同时，decimal 还可以保证小数部分的精度

DECIMAL(M, D) [UNSIGNED]

创建一张表，其中分别包含两个高精度浮点数类型

mysql> create table if not exists testDecimal
(
  f float(10, 8),
  d decimal(10, 8)
);
mysql> desc testDecimal;

分别插入普通数据和高精度数据，查看两者的差异

mysql> insert into testDecimal values (99.99, 99.99), (1.23456789, 1.23456789);
mysql> select * from testDecimal;

可以看到，float 不仅出现了精度丢失，还出现了只插入两位小数的情况下，后面出现了一堆小数；而 decimal 则是指哪打哪，插入的数据非常准确，精度保持的非常稳定

3.3.如何选择

float 比 decimal 占用空间小，decimal 比 float 精度高

如果对精度要求不是很高（精度少于等于七位），可以选择 float；如果从事金融相关工作，对精度的要求极其苛刻，选择 decimal

float 能保证的精度最多是 7 位

decimal 能保证的精度最多是 30 位，不同版本的 MySQL 略有差异

4.字符

4.1.CHAR

char 在 C/C++ 中称为字符类型，大小为 1 字节，在 MySQL 中则是叫做 固定长度字符串，也就是说 char 可以存储字符串（前提是长度足够）

CHAR(L)

创建一张表，其中包含了长度为 2 的 char

mysql> create table if not exists testChar
(
  固定字符串 char(2)
);
mysql> desc testChar;

向表中插入字符串

注：在 MySQL 中使用 ‘ ' 或者 " " 括起来的为字符串

mysql> insert into testChar values ('a'), ('bb'), ('战'), ('战狼');
mysql> select * from testChar;
mysql> insert into testChar values ('战狼三');
mysql> insert into testChar values ('ccc');

在 utf8 编码中，一个英文字符占 1 字节，一个中文字符占 3 字节

成功插入 2 个英文字符可以理解，但为什么也可以成功插入 2 个中文字符？

这是因为 MySQL 中的 char 类型大小并非 1 字节，而是 1 字符，无论是什么语言，都可以按个数插入，而非实际占用空间，这与 C/C++ 中的 char 有根本上的区别

这也就是解释了为什么 char 被称为 固定长度字符串，不过这个字符串也是有约束的，当实际插入的字符串长度超过 char 的固定长度时，插入操作会被拦截，并且 char 支持的最大长度为 255，大于 255 的字符串注定不能使用 char 类型存储

mysql> alter table testChar modify 固定字符串 char(255);
mysql> desc testChar;
// 修改失败
mysql> alter table testChar modify 固定字符串 char(256);

4.2.VARCHAR

varchar 表示 变长字符串，不同于 char 至多支持插入长度为 255 的字符串，varchar 至多支持插入总大小为 65535 字节的字符串

VARCHAR(L)

varchar 为什么被称为 变长字符串 ？

这是因为给 varchar 分配空间后（假设分配了 10 字符），如果实际插入的字符串长度为 5，则只会使用 varchar 中的 5 字符空间，如果实际插入的长度为 8，就会使用 8 字符空间，在长度范围之内，varchar 会根据实际插入的字符串长度，动态分配空间，达到变长的效果

mysql> create table if not exists testVarchar
(
  变长字符串 varchar(6)
);
mysqL> desc testVarchar;
mysql> insert into testVarchar values ('战狼一'), ('战狼一二三'), ('战狼一二三四');
mysql> select * from testVarchar;

尝试将 变成字符串 的大小改为 65535，结果提示最大支持修改为 21845

mysql> alter table testVarchar modify 变长字符串 varchar(65535);

为什么这里的 varchar 最大只支持 21845？

因为此时存入的是中文字符，前面提到过，一个中文字符在 utf8 编码中占 3 字节， 21845 * 3 = 65535，而这正是 varchar 支持字符串的最大字节数

需要注意的是 varchar 的最大长度与编码密切相关，在英文字符状态下，varchar 至多支持插入长度为 65535 的字符串，但是其中需要使用 1~3 字节的空间用来记录数据大小，所以实际至多支持插入长度为 65532 的英文字符串

对于中文字符串来说，除去 3 字节的记录空间，可用空间剩余 65532 字节，每个中文字符占 3 字节，实际至多插入长度 65532 / 3 = 21844 的中文字符串

将之前的 testVarchar 大小修改为 21844

mysql> desc testVarchar;
mysql> alter table testVarchar 变长字符串 varchar(21488);
mysql> desc testVarchar;

注意：我这里能修改成功是因为当前的 testVarchar 表中只有一个字符，数据表中的字段也是需要占用空间的，当字段过多时，是无法修改 varchar 为最大容量的

如果实际使用场景中多次涉及 varchar 的边界，可以改用 text，这是一个专门用来存储文本数据的类型，比如我当前编写的博客，就是通过该类型存储的

4.3.如何使用

char 与 varchar 的区别如下

实际存储	char(4)	varchar(4)	char 占用字符	varchar 占用字符
abcd	abcd	abcd	4*3=12	4*3+1=13
好	好	好	4*3=12	1*3+1=4
Abcde	X	X	数据超过长度	数据超过长度

varchar 会额外使用 1~3 字节的空间，用于记录数据大小

如何选择定长或变成字符？

如果数据确定长度都一样，就使用定长 char，比如身份证、手机号
如果数据长度有变化，就使用变长 varchar，比如名字、地址
定长的磁盘空间比较浪费，但是效率高
变长的磁盘空间比较节省，但是效率低
定长的意思是：直接开辟对应好的空间
变长的意思是：在不超过自定义范围的情况下，用多少开辟多少

5.时间和日期

5.1.DATE

在 MySQL 中，日期表示 年月日，通过 date 类型表示，占用 3 字节空间

// yyyy-mm-dd
DATE

mysql> create table if not exists testDate
(
  日期 date
);
mysql> desc testDate;

可以向其中插入日期信息

注意：插入的日期信息需要使用 ' ' 或者 " " 引起来

mysql> insert into testDate values ('2018-4-10'), ('2019-9-11'), ('2021-7-15');
mysql> select * from testDate;

5.2.DATETIME

如果想插入更为详细的日期信息，可以改用 datetime，格式为 年月日时分秒，称为 日期时间类型，占用 8 字节空间

范围从 1000 到 9999

// yyyy-mm-dd hh:mm:ss
DATETIME

mysql> alter table testDate add (日期时间 datetime);
mysql> desc testDate;

同样的，插入数据时需要使用 ' ' 或者 " " 将日期时间括起来

mysql> insert into testDate values ('1970-1-1', '1970-1-1 0:0:0'), ('2023-12-4', '2023-12-4 14:21:42');
mysql> select * from testDate;

诸如评论时间、博客发布时间等都是通过 datetime 类型存储的

除了日期和 日期时间，当然还有单纯时间类型，只能用来存储具体时间

// hh:mm:ss
TIME

5.3.TIMESTAMP

MySQL 中提供了 时间戳 类型 timestamp，占用 8 字节，时间戳 最大的特点是随着对表的更新操作，更新时间，并且 时间戳 在查询时，显示的格式与 datetime 一样

时间戳是指从 1970-1-1 0:0:0 开始计时的秒数

// yyyy-mm-dd hh:mm:ss
TIMESTAMP

mysql> alter table testDate add (时间戳 timestamp);
mysql> desc testDate;
mysql> select * from testDate;

注意：时间戳是一个非空字段，并且不允许用户手动插入时间

使用 update 指令更新字段信息，可以触发 时间戳 的更新

mysql> select * from testDate;
mysql> update testDate set 日期='2023-12-5' where 日期='2023-12-04';
mysql> select * from testDate;

利用这几个日期类型，编写一个简易版的 留言墙

mysql> create table if not exists commentWall
(
  name varchar(32) comment '用户名',
  regisTime date comment '注册时间',
  msg varchar(256) comment '留言信息',
  modifyTime timestamp comment '发布/修改时间'
);
mysql> desc commentWall;

向其中插入一些留言信息，并在片刻之后修改其中的留言信息

注意：因为时间戳不允许用户手动插入，所以在插入数据时，不能再使用全列插入的方式，需要指明往哪些列中插入数据

mysql> insert into commentWall (name, regisTime, msg) values 
('曹操', '2018-1-1', '宁叫我负天下人，休叫天下人负我'),
('孙武', '2021-6-7', '其疾如风，其徐如林，侵掠如火，不动如山，难知如阴，动如雷震'),
('王勃', '2023-4-10', '落霞与孤鹜齐飞 秋水共长天一色');
mysql> select name as 用户名, regisTime as 注册时间, msg as 留言内容, modifyTime as 修改时间 from commentWall;
mysql> update commentWall set msg='老骥伏枥，志在千里' where name='曹操';
mysql> select name as 用户名, regisTime as 注册时间, msg as 留言内容, modifyTime as 修改时间 from commentWall;

6.枚举和集合

6.1.ENUM

enum 称为枚举类型，用于提供一批元素，可以选择其中一个

ENUM

mysql> create table if not exists votes
(
  name varchar(16),
  gender enum('男', '女')
);
mysql> desc votes;

向其中插入值时，只能选择枚举中已经存在的元素，并且只能选择一个

mysql> insert into votes values ('张三', '男'), ('小红', '女');
mysql> select * from votes;
// 非法操作
mysql> insert into votes values  ('李四', '男, 女');
mysq> insert into votes values  ('王五', '沃尔玛购物袋');

除了直接选择枚举中的元素值外，还可以通过下标的方式进行选择，当然超过下标索引范围的选择也是非法的

注：这里的下标从 1 开始

mysql> insert into votes values ('李四', 1), ('王五', 2);
mysql> select * from votes;
// 非法操作
mysql> insert into votes values ('赵六', 0);
mysql> insert into votes values ('赵六', 3);

6.2.SET

如果想多选，需要使用集合类型

SET

mysql> alter table votes add (hobby set('唱', '跳', 'rap', '篮球', '写代码'));
mysql> desc votes;

可以看到，枚举和集合类型默认都是可以为空的，现在继续向表中插入数据

注意：在多选集合中的元素时，以 , 分隔，并且中间不能有空格

mysql> insert into votes values ('ruoli', '男', '唱,写代码'), ('小蔡', '女', '跳'), ('小徐', '男', 'rap'), ('小坤', '女', '篮球');
mysql> select * from votes;

集合类型支持通过数字多选元素

mysql> insert into votes values ('赵六', 1, 1), ('田七', 2, 2), ('老八', 1, 3);
mysql> select * from votes;

结果显得有些诡异，这是因为 集合中的数字并非下标，而是一个数值，其中的比特位对应着集合中的选项

当值为 1 时，二进制表示为 0001，即集合中的第一个元素唱
当值为 2 时，二进制表示为 0010，即集合中的第二个元素跳
当值为 3 时，二进制表示为 0011，即集合中的第一、二个元素 唱，跳

所以如果想通过数字全选当前集合中的所有元素（11111），转化成十进制，表示为 31，实际插入时结果符合预期

mysql> insert into votes values ('菜须鲲', 1, 31);
mysql> select * from votes;

如果把数值设为 0，会发现最终选择的元素为 空

注意：NULL 与 空 不等价

NULL 表示什么都没有，空 则表示某个类型存在，但它现在为空

mysql> insert into votes values ('旧人', 1, 0);
mysql> select * from votes;

关于集合中元素的查询

如果想查询包含其中一个元素的行信息，需要借助函数 find_in_set(val, set)

这个函数可以在 set 中查找是否存在 val，如果存在就返回其下标，不存在则返回 0，在 MySQL 中可以直接通过 select 指令执行函数

mysql> select find_in_set('a', 'a,b,c,d,e');
mysql> select find_in_set('c', 'a,b,c,d,e');
mysql> select find_in_set('f', 'a,b,c,d,e');

将 find_in_set 函数加入 select 查询子句中，即可筛选出包含集合中任意元素的行信息

mysql> select * from votes where find_in_set('唱', hobby);

如果想同时筛选出包含多个元素的行信息，可以使用 and 级联两个条件

mysql> select * from votes where find_in_set('唱', hobby) and find_in_set('写代码', hobby);

关于 MySQL 中数据类型的讲解到此结束，总之：数据类型本质上就是一种约束，确保插入数据库中数据的合法性

MySQL基础『数据类型』

1.数据类型一览

2.整型

2.1.INT

2.2.BIT

3.浮点数

3.1.FLOAT

3.2.DECIMAL

3.3.如何选择

4.字符

4.1.CHAR

4.2.VARCHAR

4.3.如何使用

5.时间和日期

5.1.DATE

5.2.DATETIME

5.3.TIMESTAMP

6.枚举和集合

6.1.ENUM

6.2.SET

热门文章

最新文章

相关课程

相关电子书

相关实验场景

推荐镜像

热门

活动广场

任务中心

开发者评测

高校计划

乘风者计划

训练营

阿里云MVP

话题

直播

下载

镜像站

技术资料

插件

MySQL基础『数据类型』

1.数据类型一览

2.整型

2.1.INT

2.2.BIT

3.浮点数

3.1.FLOAT

3.2.DECIMAL

3.3.如何选择

4.字符

4.1.CHAR

4.2.VARCHAR

4.3.如何使用

5.时间和日期

5.1.DATE

5.2.DATETIME

5.3.TIMESTAMP

6.枚举和集合

6.1.ENUM

6.2.SET

热门文章

最新文章

相关课程

相关电子书

相关实验场景

推荐镜像