MySQL part1——了解MySql的数据类型

MySQL数据类型

MySQL 支持多种类型，大致可以分为四类：

MySQL 常见字段类型总结

• 数值型
  • 整数类型：bit、bool、tiny int、small int、medium int、int、 big int
  • 浮点数类型：float、double
  • 定点数类型：decimal
• 字符串类型
  • 字符串：char、varchar
  • 文本类型：tiny text、text、medium text、longtext
  • 二进制文本类型：tiny blob、blob、medium blob、long blob
• 日期类型：date、datetime、timestamp、time、year
• 其他数据类型：binary、varbinary、enum、set、geometry、point、multipoint、linestring、multilinestring、polygon、geometrycollection等

MySQL 支持所有标准 SQL 数值数据类型。

这些类型包括严格数值数据类型(INTEGER、SMALLINT、DECIMAL 和 NUMERIC)，以及近似数值数据类型(FLOAT、REAL 和 DOUBLE PRECISION)。

关键字INT是INTEGER的同义词，关键字DEC是DECIMAL的同义词。

BIT数据类型保存位字段值，并且支持 MyISAM、MEMORY、InnoDB 和 BDB表。

数值和数据类型

类型	大小	范围（有符号）	范围（无符号）	用途
TINYINT	1 Bytes	(-128，127)	(0，255)	小整数值
SMALLINT	2 Bytes	(-32 768，32 767)	(0，65 535)	大整数值
MEDIUMINT	3 Bytes	(-8 388 608，8 388 607)	(0，16 777 215)	大整数值
INT或INTEGER	4 Bytes	(-2 147 483 648，2 147 483 647)	(0，4 294 967 295)	大整数值
BIGINT	8 Bytes	(-9,223,372,036,854,775,808（-(2^63)），9 223 372 036 854 775 807（(2^63)-1）)	(0，18 446 744 073 709 551 615)	极大整数值
FLOAT	4 Bytes	(-3.402 823 466 E+38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38)	0，(1.175 494 351 E-38，3.402 823 466 E+38)	单精度 浮点数值
DOUBLE	8 Bytes	(-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	双精度 浮点数值
DECIMAL 定点类型	对DECIMAL(M,D) ，如果M>D，为M+2否则为D+2	依赖于M和D的值，M：总位数，D：小数位数	依赖于M和D的值	小数值

整数类型

MySQL 支持 SQL 标准整数类型INTEGER（或INT）和 SMALLINT作为标准的扩展，MySQL 还支持整数类型TINYINT、MEDIUMINT和BIGINT。

MySQL 支持的整数类型所需的存储和范围

类型	存储（字节）	有符号最小值	最小值无符号	有符号最大值	最大值无符号（UNSIGNED）
TINYINT	1个	-128	0	127	255
SMALLINT	2个	-32768	0	32767	65535
MEDIUMINT	3个	-8388608	0	8388607	16777215
INT	4个	-2147483648	0	2147483647	4294967295
BIGINT	8个	-2^63	0	2^63-1	2^64-1

取值范围如果加了unsigned，代表无符号类型数字，则最大值翻倍，如 tinyint unsigned 的取值范围为(0~256)。

对于 MySQL 中UNSIGNED字段参与减法运算时，若结果为负会直接报错，而非返回负数

而且UNSIGNED INT（最大值 4294967295）超出 Java int范围（2147483647），需用long接收，否则会出现数据溢出，所以说，用unsigned的时候不咋多

整数的默认值和零填充ZEROFILL，为整数字段补零到指定长度，本质是 UNSIGNED + 补零显示。

CREATE TABLE test (id INT(5) ZEROFILL);
INSERT INTO test VALUES (12);
SELECT id FROM test; -- 输出 00012（显示层面补零，实际存储还是12）

INT(5)中的5是显示宽度，不影响存储范围，MySQL 8.0 后已废弃该属性，仅兼容

定点类型

DECIMAL和NUMERIC类型存储精确的数字数据值 。当保持精确的精度很重要时使用这些类型

在DECIMAL列声明中，可以（并且通常）指定精度和小数位数。例如：

salary DECIMAL(5,2)

在此示例中，5是精度，2是标度。精度表示为值存储的有效位数，标度表示小数点后可以存储的位数。

DECIMAL(M,D)中M和D的取值范围是这样：

• M（精度）：1~65（总有效位数，包含整数 + 小数部分）；
• D（标度）：0~30，且D ≤ M；
• 示例：DECIMAL(5,2)表示总位数 5，小数 2 位，整数部分最多 3 位，范围：-999.99 ~ 999.99。

如果小数位数为 0，则DECIMAL值不包含小数点或小数部分。

而 DECIMAL 并非直接存储为二进制，而是以字符串形式存储（每 9 位数字占 4 字节），因此精度无损失，是精确精度的浮点类型，但读写性能略低于整数 / 浮点型。

经典浮点型误差反例

SELECT 0.1 + 0.2; -- FLOAT/DOUBLE类型返回0.30000000000000004，而非0.3
SELECT CAST(0.1 AS DECIMAL(10,1)) + CAST(0.2 AS DECIMAL(10,1)); -- DECIMAL返回0.3

对货币等对精度敏感的数据，应该用定点数表示或存储

浮点类型（近似值）

FLOAT DOUBLE——MySQL 对单精度值使用四个字节，对双精度值使用八个字节。

FLOAT/DOUBLE 基于 IEEE 754 标准存储，用二进制表示十进制小数，部分小数（如 0.1）无法精确表示，会产生舍入误差；

FLOAT（单精度）占 4 字节（32 位），结构分为三部分

符号位（S）	指数位（E）	尾数位（M）
1 位	8 位	23 位

• 符号位：0 = 正数，1 = 负数；
• 指数位：存储指数（偏移量 127），决定数值的大小范围；
• 尾数位：存储有效数字（小数部分），决定精度（FLOAT 约 6-7 位有效数字，DOUBLE 约 15-17 位）。

这就是 IEEE 754 标准的存储逻辑

而 FLOAT和 DOUBLE 也支持类似定点类型的写法，也即是 FLOAT(M,D)/DOUBLE(M,D)的M和D，而且一样，仅用于显示约束，不影响存储范围（FLOAT 始终 4 字节，DOUBLE 始终 8 字节），MySQL 8.0 后同样废弃该写法，建议直接用FLOAT/DOUBLE

编程中，如果用到浮点数，要特别注意误差问题，并尽量避免做浮点数比较；

BIT——比特值类型

BIT数据类型用于存储位值。一种允许存储 bit 值的类型。范围从 1 到 64。

BIT(M)中M表示位数，1≤M≤64，默认 M=1；

它占用CEIL(M/8)字节（如BIT(6)占 1 字节，BIT(9)占 2 字节）；因为它直接存储的是二进制

如果将值分配给长度小于位的列，则该值将在左侧填充零

CREATE TABLE bit_test (b BIT(6));
-- 插入方式1：二进制字面量
INSERT INTO bit_test VALUES (b'101'); -- 自动补零为b'000101'
-- 插入方式2：十进制（自动转二进制）
INSERT INTO bit_test VALUES (5); -- 5的二进制是101，补零为000101
-- 查询：直接查返回二进制，需转十进制
SELECT b, CAST(b AS UNSIGNED) FROM bit_test; -- 输出 b'000101' 和 5

所以说 bit 一般情况下存储两种情况以上的开关状态，但是这种情况下，还是 TINYINT 更好用，因为 BIT 类型可读性差

日期和时间类型

表示时间值的日期和时间类型为DATETIME、DATE、TIMESTAMP、TIME和YEAR。

每个时间类型有一个有效值范围和一个”零”值，当指定不合法的MySQL不能表示的值时使用”零”值。

常用的日期有如下三个：

• date：日期 yyyy-mm-dd，占用三字节。
• datetime：时间日期格式 yyyy-mm-dd HH:ii:ss 表示范围从 1000 到 9999 ，占用八字节。
• timestamp：时间戳，从1970年开始的 yyyy-mm-dd HH:ii:ss 格式和 datetime 完全一致，占用四字节，本质上就是 INT 类型的上限。 表示现在的时间。

类型	大小 ( bytes)	范围	格式	用途
DATE	3	1000-01-01/9999-12-31	YYYY-MM-DD	日期值
TIME	3	‘-838:59:59’/‘838:59:59’	HH:MM:SS	时间值或持续时间
YEAR	1	1901/2155	YYYY	年份值
DATETIME	8	‘1000-01-01 00:00:00’ 到 ‘9999-12-31 23:59:59’	YYYY-MM-DD hh:mm:ss	混合日期和时间值
TIMESTAMP	4	‘1970-01-01 00:00:01’ UTC 到 ‘2038-01-19 03:14:07’ UTC 结束时间是第 2147483647 秒，北京时间 2038-1-19 11:14:07，格林尼治时间 2038年1月19日 凌晨 03:14:07	YYYY-MM-DD hh:mm:ss	混合日期和时间值，时间戳

字符串类型

字符串类型指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET。

MySQL 认为

类型	大小	用途
CHAR	0-255 bytes	定长字符串
VARCHAR	0-65535 bytes	变长字符串
TINYBLOB	0-255 bytes	不超过 255 个字符的二进制字符串
TINYTEXT	0-255 bytes	短文本字符串
BLOB	0-65 535 bytes	二进制形式的长文本数据
TEXT	0-65 535 bytes	长文本数据
MEDIUMBLOB	0-16 777 215 bytes	二进制形式的中等长度文本数据
MEDIUMTEXT	0-16 777 215 bytes	中等长度文本数据
LONGBLOB	0-4 294 967 295 bytes	二进制形式的极大文本数据
LONGTEXT	0-4 294 967 295 bytes	极大文本数据

而且字符串数据类型的列定义可以指定列字符集和排序规则：

• CHARACTER SET指定字符集。如果需要，可以使用该COLLATE属性以及任何其他属性指定字符集的排序规则。

  CREATE TABLE t
  (
      c1 VARCHAR(20) CHARSET utf8mb4,
      c2 TEXT CHARACTER SET latin1 COLLATE latin1_general_cs
  );

CHAR 和 VARCHAR 类型

char(n) 和 varchar(n)中括号中 n 代表字符的个数，一个定长一个不定长。

VARCHAR 类型用于存储可变长度字符串，是最常见的字符串数据类型。它比固定长度类型更节省空间，因为它仅使用必要的空间，会根据实际字符串的长度改变存储空间。

CHAR 类型用于存储固定长度字符串，MySQL 总是根据定义的字符串长度分配足够的空间。当存储 CHAR 值时，MySQL会删除字符串中的末尾空格，同时，CHAR 值会根据需要采用空格进行剩余空间填充，以方便比较和检索。但正因为其长度固定，所以会占据多余的空间，也是一种空间换时间的策略；

CHAR 是定长字符串，始终占用 M 个字节，默认长度是 1，最大 255，不足时候补空格，在查询的时候去除掉，所以说，定长字符串和不定长字符串是相对字节数长度而言的，并非真正意义上的字符串长度

因此 CHAR 会涉及到一些问题，包括 CHAR 的空格丢失问题，CHAR 默认检索时删除尾部空格，可能导致业务异常，VARCHAR 就没有这些事情，因为 VARCHAR 不会将尾随空格去掉

CREATE TABLE test (c1 CHAR(5), c2 VARCHAR(5));
INSERT INTO test VALUES ('a ', 'a ');
SELECT c1 = 'a', c2 = 'a'; -- 结果：1（true）、0（false）

启用PAD_CHAR_TO_FULL_LENGTH SQL 模式，保留尾部空格

SET sql_mode = 'PAD_CHAR_TO_FULL_LENGTH';
SELECT c1 = 'a ' FROM test; -- 结果：1（true）

对于 VARCHAR，它的不定长字符串的含义是指的是，VARCHAR 长度可以指定为 0 到 65535 之间的值，但是有 1-3 个字节用于记录数据大小，所以说有效字节数是65532。

这就涉及到了 VARCHAR 的长度计算陷阱，我们实际在使用的时候，VARCHAR(M)中的M是字符数，而非字节数，例如VARCHAR(10)在utf8mb4（4 字节 / 字符）下，实际占用字节数 = 10 * 4 + 2（长度前缀）= 42 字节，但是 VARCHAR 的长度约束是 65532 字节数，而非字符数

当我们的表的编码是 utf8 时，varchar(n)的参数 n 最大值是65532/3=21844，（因为 utf 中，一个字符占用3个字节），如果编码是 gbk，varchar(n)的参数n最大是65532/2=32766（因为gbk中，一个字符占用2字节）。

而且 VARCHAR 整行总字节数不能超过 65535，它是默认最大是所有列共享相加的，若表中有多个 VARCHAR 列，需分摊 65535 字节

-- 报错：Row size too large. The maximum row size for the used table type...
CREATE TABLE test (
  c1 VARCHAR(21845) CHARACTER SET utf8mb4, -- 21845*4=87380 > 65535
  c2 VARCHAR(10)
);

CHAR 和 VARCHAR 简单比较一下

image-20250318083228988

CHAR 和 VARCHAR 类型类似，但它们保存和检索的方式不同。它们的最大长度和是否尾部空格被保留等方面也不同。在存储或检索过程中不进行大小写转换。

例如

场景	CHAR (5) 存储内容	VARCHAR (5) 存储内容	检索结果（默认模式）
插入 “abc”	“abc”（补 2 个空格）	“abc” + 1 字节长度前缀	均为 “abc”
插入 “abcde”	“abcde”	“abcde” + 1 字节长度前缀	均为 “abcde”
插入 “abcdef”（超长）	报错（严格模式）	报错（严格模式）	-

BINARY 和 VARBINARY 类型

与 CHAR 和 VARCHAR 类似的类型还有 BINARY 和 VARBINARY，只不过它们存储的是二进制字符串。

二进制字符串跟常规字符串非常相似，但是二进制字符串存储的是字节码而不是字符。

特性	BINARY(M)	VARBINARY(M)
存储内容	二进制字节串	二进制字节串
长度单位	字节	字节
填充规则	不足 M 时补 0x00（零字节）	无填充
检索规则	保留填充的 0x00	保留所有字节
比较规则	基于字节数值（0x00 < 空格）	基于字节数值
字符集 / 排序规则	固定 binary/binary	固定 binary/binary
最大长度	255 字节	65535 字节（受行大小限制）
适用场景	长度固定的二进制数据	长度可变的二进制数据

例如，CHAR(5) BINARY（BINARY 属性）和BINARY(5)（BINARY 类型）

• CHAR(5) BINARY：本质还是字符类型，只是排序规则改为对应字符集的_bin
• BINARY(5)：本质是二进制类型，存储的是 5 个字节的原始字节串，字符集固定为binary。

对于两者的定长和不定长规则，是类似的，BINARY 是定长二进制字符串，不足指定长度时，右侧补 0x00 零字节，且检索时保留填充的 0x00；而且在比较的时候，所有字节，包括填充的 0x00，都参与比较，0x00 的数值小于空格（0x20）

所以这里存在一个问题就是，BINARY 的 0x00 填充会导致等值查询失效，例如存储 MD5 串（32 个字符，16 字节）时用BINARY(16)，但查询时直接用字符串匹配失败：

CREATE TABLE t (md5 BINARY(16));
-- MD5('test')的十六进制是 098f6bcd4621d373cade4e832627b4f6（16字节）
INSERT INTO t VALUES (UNHEX('098f6bcd4621d373cade4e832627b4f6'));

-- 错误查询：用字符串匹配，实际存储的是16字节二进制，而非字符串
SELECT * FROM t WHERE md5 = '098f6bcd4621d373cade4e832627b4f6'; -- 无结果

-- 正确查询：用UNHEX转二进制
SELECT * FROM t WHERE md5 = UNHEX('098f6bcd4621d373cade4e832627b4f6'); -- 有结果

实际上对于一个需要作为原始二进制来进行存储的内容，变长通常是更常用的， VARBINARY是变长二进制字符串，无填充，按实际字节长度存储，所有字节参与比较，它的意思就是，存储原始二进制

TEXT 类型

TEXT类型是一种特殊的字符串类型，包括 TINYTEXT、TEXT、MEDIUMTEXT 和 LONGTEXT，其长度和储存空间的对比如下表所示

image-20260227114542537

• TINYTEXT：最大255字节（约0.25KB），适合极短文本。
• TEXT：最大65,535字节（约64KB），适合普通长文本。
• MEDIUMTEXT：最大16,777,215字节（约16MB），适合较大文本内容。
• LONGTEXT：最大4,294,967,295字节（约4GB），适合超长文本存储。

各种TEXT类型的区别在于允许的长度和储存空间不同。因此，在这几种TEXT类型中，根据需求选取既能满足需要又节省空间的类型即可

TEXT 创建的时候无需指定长度，自动按实际长度存储，同样，下面说的 BLOB 也不需要指定长度，它也自动按实际长度存储，但是它们都受到所在类型的长度限制

而且和 VARCHAR 不一样，TEXT 允许的长度是指实际储存的字节数，而不是实际的字符数，比如假设一个中文字符占两个字节，那么 TEXT 类型可存储 65535/2=32767 个中文字符，而VARCHAR(100)代表可储存100个中文字符，实际占200个字节

来简单比较一下更多与 VARCHAR 的区别

• VARCHAR存储在表行内，而TEXT在行外存储
• 对于索引，VARCHAR支持完整前缀索引，TEXT仅支持前768字节的前缀索引。所以无法在TEXT列直接创建普通索引，虽然TEXT 类型的字段支持全文索引。
• VARCHAR(65535)受行大小限制，而TEXT类型独立计算

针对 TEXT ，大文本建议单独建表存储，避免影响主表查询效率，因为它删除后易产生表碎片，而且SELECT *查询可能将数GB文本加载到内存，导致瞬间内存飙升

切记：不要因存储方便而忽视数据建模的基本原则。在满足业务需求的前提下，保持数据结构的精简，才是数据库设计的终极艺术。

BLOB 类型

MySQL中，BLOB字段用于存储二进制数据，官方文档中说它是一个二进制大对象，是一个可以存储大量数据的容器，它能容纳不同大小的数据，所以它可以存储如图像、音频、视频等二进制对象数据

BLOB值被视为二进制字符串，它们有binary字符集和排序规则，比较和排序是基于列值中字节的数值，而TEXT值被视为非二进制字符串

有四种子类型：TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB，对应 TEXT 的那四种，存储容量与 TEXT 类型相同。

BLOB 类型的字段不支持全文索引，而且数据按字节进行排序和比较。

很多情况下，BLOB 类型的滥用会导致严重的性能问题

一般在保存少量字符串的时候，我们会选择 CHAR 或者 VARCHAR ，而在保存较大文本时，通常会选择使用 TEXT 或者 BLOB。二者之间的主要差别是 BLOB 能用来保存二进制数据，比如照片；而 TEXT 只能保存字符数据，比如一遍文章或日记。

BLOB和TEXT值会引起一些性能问题，特别是执行了大量的删除操作时。删除操作会在数据库表中留下很大的空洞，也就是内存碎片，以后要填入这些“空洞”的记录在插入的性能上会有影响。为了提高性能，建议定期使用OPTIMEIZE TABLE功能对这类表进行碎片整理，避免因为“空洞”导致性能问题。

ENUM类型

ENUM，枚举类型，用于存储单一值，可以选择一个预定义的集合。

enum('选项1','选项2','选项3',...);

该设定只是提供了若干个选项的值，最终一个单元格中，实际只存储了其中一个值；而且出于效率考虑，这些值实际存储的是“数字”，因为这些选项的每个选项值依次对应如下数字：1,2,3,…最多65535个；当我们添加枚举值时，也可以添加对应的数字编号。

ENUM是 MySQL 专属的字符串枚举类型，本质上是字符串表象加上下标数字底层存储，所以说，枚举值存储数字索引，而非完整字符串，因此它更紧凑

对于枚举类型，列值只能从创建表时预定义的枚举列表中选择，无法存储列表外的值，所以美剧天生数据合法，它会自动校验值是否在枚举列表中，无效值插入报错

创建 ENUM 列

-- 基础语法：ENUM('值1', '值2', ...)
CREATE TABLE shirts (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(40),
    -- 定义尺寸枚举列，可选值：x-small/small/medium/large/x-large
    size ENUM('x-small', 'small', 'medium', 'large', 'x-large')
);

枚举值的索引机制是ENUM最关键的规则

• 枚举列表中的每个值都有数字索引，从1开始递增；
• 空字符串（无效值）的索引是0；
• NULL的索引是NULL。

ENUM对 “数字字符串” 和 “纯数字” 的处理完全不同，所以说，ENUM 不推荐使用数字作为枚举值，若必须用，务必始终加引号，且插入时也用带引号的字符串

-- 创建枚举列：值为'0'/'1'/'2'（字符串），对应索引1/2/3
CREATE TABLE t (numbers ENUM('0','1','2'));

-- 插入不同形式的数字，结果完全不同
INSERT INTO t VALUES 
  (2),    -- 纯数字：视为索引2 → 对应值'1'
  ('2'),  -- 数字字符串：匹配枚举值 → 存储'2'
  ('3');  -- 数字字符串：无匹配，视为索引3 → 对应值'2'

SELECT numbers, numbers+0 FROM t;
-- 输出：
-- 1 | 2
-- 2 | 3
-- 2 | 3

ENUM默认按索引值排序，而非字符串的字母顺序，NULL < 空字符串 ’’ < 枚举值；

ENUM 列允许 NULL 和 NOT NULL 的区别是什么

• ENUM 列允许 NULL（默认）：默认值为 NULL，可插入 NULL，无效值插入空字符串 ’’（非严格模式）；
• ENUM 列声明 NOT NULL：默认值为枚举列表的第一个值，不可插入 NULL，无效值插入空字符串 ’’（非严格模式）。

SET 类型

SET：集合类型，用于存储多个值，可以选择多个预定义的集合。

set('选项值1','选项值2','选项值3', ...);

SET是 MySQL 专为多值枚举场景设计的字符串类型，表象对外展示为逗号分隔的字符串（如'a,d'），值可选自预定义的集合列表；

存储底层为位图类型，每个集合成员对应一个二进制位，占用 选项/8 个字节，它最多支持 64 个不同的集合成员，插入数字时会被视为位图值对应的字符串，而非字符串

• 每个集合成员对应一个2 的幂次整数，这样就能对应二进制中的某一位，多个值的组合是对应整数的求和，二进制表现为多个位被置 1。

  所以说，插入数字时会被视为位图值，而非字符串，需格外小心，会出现 SET 列插入数字 9，显示为 ‘read,admin’ 的情况

  集合成员	十进制值	二进制值	含义
  ’’	0	0000	无权限
  ‘read’	1	0001	仅读权限
  ‘write’	2	0010	仅写权限
  ‘delete’	4	0100	仅删除权限
  ‘admin’	8	1000	仅管理员权限
  ‘read,write’	3	0011	读 + 写权限（1+2=3）
  ‘read,delete,admin’	13	1101	读 + 删除 + 管理员（1+4+8=13）

• 集合值的处理规则是自动去重 + 排序，而且尾随空格自动删除，也就是说，插入时无论值的顺序如何、是否重复，检索时都会自动去重，并且按创建表时的集合列表顺序排列，创建表时，集合成员的尾随空格会被自动删除

• 对于 SET 的无效值处理

  • 非严格模式：忽略无效值，仅保留有效部分，触发警告；
  • 严格模式：直接报错，禁止插入。

列值可以是预定义列表中的零个 / 一个 / 多个值，用逗号分隔，但成员本身不能包含逗号

SET默认按底层十进制值排序，而非字符串顺序，NULL排在所有非 NULL 值之前

-- 插入不同权限组合
INSERT INTO user_permissions (username, permissions) 
VALUES 
('u1', 'read'),    -- 1 → 0001
('u2', 'write'),   -- 2 → 0010
('u3', 'read,write'), -- 3 → 0011
('u4', NULL);      -- NULL

-- 默认排序：NULL → '' → 1 → 2 → 3
SELECT username, permissions, permissions+0 
FROM user_permissions 
WHERE username IN ('u1','u2','u3','u4')
ORDER BY permissions;
-- 输出：
-- u4 | NULL | NULL
-- u1 | read | 1
-- u2 | write | 2
-- u3 | read,write | 3

查询包含某个 / 多个集合成员的记录，有 3 种常用方式

查询方式	语法示例	适用场景	注意事项
FIND_IN_SET ()（推荐）	WHERE FIND_IN_SET('read', permissions)>0	查找包含指定成员的记录	精准匹配，不会匹配子字符串
LIKE	WHERE permissions LIKE '%read%'	简单场景快速查询	可能匹配子字符串（如 ‘readonly’）
按位与（&）	WHERE permissions & 1	高性能精准匹配	需记住成员对应的十进制值

所以说，SET和ENUM都是枚举类类型，但适用场景完全不同

特性	SET 类型	ENUM 类型
取值数量	零个 / 一个 / 多个值	仅能取一个值
底层存储	整数位图（按位存储）	整数索引（按顺序编号）
成员数量限制	最多 64 个	最多 65535 个
存储字节	1~8 字节（按成员数）	1~2 字节（按成员数）
适用场景	多值选择（如用户标签、权限）	单值选择（如状态、性别）
示例	权限：‘read’,‘write’,‘delete’	状态：‘pending’,‘paid’

所以说，ENUM是 “单选框”，SET是 “复选框”。

创建 SET 列

-- 基础语法：SET('值1', '值2', ...)
CREATE TABLE user_permissions (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(20),
    -- 定义权限集合列：可选值read/write/delete/admin，允许选多个
    permissions SET('read', 'write', 'delete', 'admin')
);

对于插入 查询 更新，比较特殊

-- 插入单个值
INSERT INTO user_permissions (username, permissions) 
VALUES ('zhangsan', 'read');

-- 插入多个值（逗号分隔）
INSERT INTO user_permissions (username, permissions) 
VALUES ('lisi', 'read,write,delete');

-- 插入重复值/乱序值（自动去重+按定义顺序排序）
INSERT INTO user_permissions (username, permissions) 
VALUES ('wangwu', 'write,read,read'); -- 最终存储为'read,write'

-- 查询（直接显示逗号分隔的字符串，可读性高）
SELECT username, permissions FROM user_permissions WHERE username = 'lisi';
-- 输出：lisi | read,write,delete

-- 更新（新增一个权限）
UPDATE user_permissions 
SET permissions = 'read,write,delete,admin' 
WHERE username = 'lisi';

-- 插入无效值（非严格模式：忽略无效值，仅警告；严格模式：报错）
-- 非严格模式
SET sql_mode = '';
INSERT INTO user_permissions (username, permissions) 
VALUES ('zhaoliu', 'read,execute'); -- 警告，execute被忽略，存储'read'

-- 严格模式
SET sql_mode = 'STRICT_TRANS_TABLES';
INSERT INTO user_permissions (username, permissions) 
VALUES ('zhaoliu', 'read,execute'); -- 报错：Data truncated for column 'permissions'

空间数据类型

GEOMETRY，POINT，LINESTRING，POLYGON，MULTIPOINT，MULTILINESTRING，MULTIPOLYGON，GEOMETRYCOLLECTION：用于存储空间数据（地理信息、几何图形等）。

有生之年了

JSON 数据类型

MySQL 原生 JSON 类型区别于字符串存储 JSON

特性	JSON 类型	字符串存储 JSON
合法性校验	自动校验，无效 JSON 插入报错	无校验，只是一串字符串
存储格式	二进制优化格式（无需解析文本）	纯文本（查询时需全量解析）
访问效率	支持按键 / 索引直接访问子元素	需解析整个字符串后提取子元素
函数支持	丰富的 JSON 函数（查询 / 修改 / 合并）	需手动处理字符串（如 SUBSTRING）

使用起来也和其他类型别无二致

-- 创建含JSON列的表
CREATE TABLE user_info (
  id INT PRIMARY KEY AUTO_INCREMENT,
  info JSON -- 定义JSON类型列
);

-- 插入合法JSON（对象/数组/标量）
INSERT INTO user_info (info) VALUES
  ('{"name": "张三", "age": 25, "tags": ["java", "mysql"]}'), -- JSON对象
  ('[1001, "李四", {"score": 90}]'), -- JSON数组
  ('"hello world"'); -- JSON标量（字符串）

-- 插入非法JSON会报错
INSERT INTO user_info (info) VALUES ('{name: 张三}'); -- 报错：键未加引号，无效JSON

而且 MySQL 提供专用函数生成规范的 JSON 值

-- JSON_ARRAY：创建JSON数组
SELECT JSON_ARRAY('a', 1, NOW()); 
-- 输出：["a", 1, "2026-02-27 10:00:00.000000"]

-- JSON_OBJECT：创建JSON对象
SELECT JSON_OBJECT('name', '李四', 'age', 30);
-- 输出：{"name": "李四", "age": 30}

-- JSON_MERGE_PRESERVE：合并多个JSON（保留重复键）
SELECT JSON_MERGE_PRESERVE('{"a":1}', '{"b":2, "a":3}');
-- 输出：{"a": [1, 3], "b": 2}

-- JSON_MERGE_PATCH：合并多个JSON（最后一个重复键生效）
SELECT JSON_MERGE_PATCH('{"a":1}', '{"b":2, "a":3}');
-- 输出：{"a": 3, "b": 2}

MySQL 会自动规范化 JSON 值

• MySQL 8.0.3+ 遵循 最后一个重复键获胜，8.0.3 前是 第一个获胜

  -- MySQL 8.0.3+
  INSERT INTO user_info (info) VALUES ('{"x":1, "x":2}');
  SELECT info FROM user_info WHERE id = LAST_INSERT_ID(); -- 输出：{"x":2}

• 它会对空格规范化处理，自动去除多余空格，显示时格式化

  SELECT JSON_OBJECT('key1', 1, 'key2', 2);
  -- 输出：{"key1": 1, "key2": 2}（自动加空格）

JSON 列不能直接创建索引，一般是虚拟列或者多值索引