MySQL SET参数如何与VARCHAR交互？

SET 是一种特殊的数据类型，它允许你在一列中存储零个或多个预定义的值，它与 ENUM 类似，但 ENUM 只能选择一个值，而 SET 可以选择多个。

SET 数据类型简介

SET 列可以包含最多 64 个不同的成员，一个 SET 列的存储大小取决于它包含多少不同的成员：

• 1-8 个成员: 1 字节
• 9-16 个成员: 2 字节
• 17-24 个成员: 3 字节
• 25-32 个成员: 4 字节
• 33-64 个成员: 8 字节

当你向 SET 列插入值时，MySQL 会自动处理值的存储、排序和重复项的去除。

如何定义 SET 列

在创建表时,你需要使用 SET('value1', 'value2', 'value3', ...) 的语法来定义列，并指定该列可以包含的所有可能值。

示例：创建一个用户权限表

假设我们想创建一个 users 表，其中每个用户可以拥有多个权限（如 'read', 'write', 'delete', 'admin'）。

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    -- 定义一个 SET 类型的权限列，包含4个可能的值
    permissions SET('read', 'write', 'delete', 'admin') NOT NULL
);

如何向 SET 列插入和更新数据

向 SET 列插入数据非常灵活，你可以使用以下几种方式：

使用逗号分隔的字符串

这是最直观的方式,直接用逗号将你想要的值连接起来。

-- 插入拥有 'read' 和 'write' 权限的用户
INSERT INTO users (username, permissions) VALUES ('user1', 'read,write');
-- 插入拥有所有权限的用户
INSERT INTO users (username, permissions) VALUES ('admin_user', 'read,write,delete,admin');
-- 插入没有任何权限的用户
INSERT INTO users (username, permissions) VALUES ('guest_user', '');

使用数字位掩码

SET 内部是以位掩码的形式存储的，每个预定义的值都对应一个二进制位。

• 'read' -> 0001 (二进制) -> 1 (十进制)
• 'write' -> 0010 (二进制) -> 2 (十进制)
• 'delete' -> 0100 (二进制) -> 4 (十进制)
• 'admin' -> 1000 (二进制) -> 8 (十进制)

你可以直接插入这个十进制数字,MySQL 会自动将其转换成对应的字符串集合。

-- 'read' (1) + 'write' (2) = 3
INSERT INTO users (username, permissions) VALUES ('user2', 3); -- 等同于 'read,write'
-- 'read' (1) + 'write' (2) + 'delete' (4) = 7
INSERT INTO users (username, permissions) VALUES ('user3', 7); -- 等同于 'read,write,delete'
-- 'read' (1) + 'write' (2) + 'admin' (8) = 11
INSERT INTO users (username, permissions) VALUES ('user4', 11); -- 等同于 'read,write,admin'

使用 SET 函数

MySQL 提供了 SET() 函数来帮助你构造或修改 SET 值。

-- 先插入一个用户
INSERT INTO users (username, permissions) VALUES ('user5', 'read');
-- 使用 UPDATE 和 SET 函数来添加 'write' 权限
-- SET(permissions, 'write') 的意思是：在原有的 permissions 值基础上，添加 'write'
UPDATE users
SET permissions = SET(permissions, 'write')
WHERE username = 'user5';
-- user5 的权限是 'read,write'

如何查询 SET 列

查询 SET 列同样有多种强大的方式。

精确匹配

查询 permissions 列 完全等于 'read,write' 的用户。

SELECT * FROM users WHERE permissions = 'read,write';

使用 FIND_IN_SET() 函数（最常用）

这是检查一个特定值是否存在于 SET 集合中的最佳方式，它比 LIKE '%value%' 更高效和准确。

-- 查询所有拥有 'delete' 权限的用户
SELECT * FROM users WHERE FIND_IN_SET('delete', permissions) > 0;
-- 查询所有拥有 'admin' 权限的用户
SELECT * FROM users WHERE FIND_IN_SET('admin', permissions);

使用位运算符（高级）

利用 SET 内部的位掩码特性，可以进行非常高效的位运算查询。

• & (按位与): 检查是否包含某个或某些位。
• (按位或): 添加一个或多个位。
• (按位取反): 移除一个或多个位。

示例：查询拥有 'delete' 权限的用户 'delete' 对应的位是 4 (二进制 0100)，我们使用 & 运算，如果结果不为 0，说明包含该位。

-- 查询 permissions 与 4 ('delete') 进行与运算后结果不为 0 的记录
SELECT * FROM users WHERE permissions & 4; -- 等同于 permissions & 'delete'
-- 查询同时拥有 'read' 和 'write' 权限的用户
-- 'read' (1) + 'write' (2) = 3
SELECT * FROM users WHERE permissions & 3 = 3;
-- 查询拥有 'admin' 权限（8）但 NOT 拥有 'delete' 权限（4）的用户
-- (~4) 是对 'delete' 的位进行取反
SELECT * FROM users WHERE permissions & 8 AND permissions & ~4;

SET vs. VARCHAR (逗号分隔)

这是一个非常常见的选择题,什么时候用 SET，什么时候用 VARCHAR？

如果你的业务场景中，可选的值是固定且有限的，并且你对数据完整性和查询性能有较高要求，SET 是一个绝佳的选择。

如果你的选项是动态的、不确定的，或者你需要存储的是一段自由文本，那么应该使用 VARCHAR。

SET 是 MySQL 中一个功能强大且高效的数据类型，专门用于处理多个预定义值的集合，它通过位掩码实现了紧凑的存储和快速的位运算查询，在用户权限、多选项配置等场景下，它比使用 VARCHAR 加逗号分隔的方式更具优势。