数据库多选项SET类型

前言

MySQL中的SET类型是一种非常实用的数据类型，允许在单个字段中存储多个预定义的选项。它适用于需要在一列中保存多个状态、标签或权限等多选项场景。本文将详细介绍SET类型的用法、相关案例、代码示例，并重点讲解如何利用二进制进行模糊匹配及其底层原理。

一、SET类型简介

SET类型本质上是一个字符串对象，可以包含0个或多个值，每个值必须从事先定义好的集合中选择。每个SET列最多可以有64个不同的成员。

语法示例

CREATE TABLE user_tags (
    id INT PRIMARY KEY AUTO_INCREMENT,
    tags SET('sports', 'music', 'travel', 'reading', 'coding')
);

在上述例子中，tags字段可以存储上述五个标签中的任意组合。

二、基本用法与插入数据

插入数据时，可以用逗号分隔的字符串指定多个选项：

INSERT INTO user_tags (tags) VALUES ('sports,music');
INSERT INTO user_tags (tags) VALUES ('travel,reading,coding');
INSERT INTO user_tags (tags) VALUES ('music');

查询时，显示的内容也是以逗号分隔的字符串：

SELECT * FROM user_tags;

结果示例：

三、SET类型的二进制存储原理

每个SET成员在内部都对应一个二进制位（bit），第一个成员对应最低位，第二个成员对应次低位，以此类推。例如：

• 'sports' -> 00001 (1)
• 'music' -> 00010 (2)
• 'travel' -> 00100 (4)
• 'reading' -> 01000 (8)
• 'coding' -> 10000 (16)

当一个字段包含多个选项时，其实际存储值为这些选项对应二进制位的按位或。例如：

• 'sports,music' -> 00011 (1+2=3)
• 'travel,reading,coding' -> 11100 (4+8+16=28)

你可以通过如下SQL查看其底层数值：

SELECT id, tags, CAST(tags AS UNSIGNED) AS tags_binary FROM user_tags;

四、二进制模糊匹配SET类型

由于SET类型底层是二进制存储，因此可以利用位运算进行高效的模糊匹配。例如，查找包含“music”标签的所有记录：

SELECT * FROM user_tags WHERE tags & 2;

解释：music是第二个成员，对应的二进制位是2（00010），tags & 2表示只要该位为1就匹配。

再如，查找同时包含“sports”和“music”的记录：

SELECT * FROM user_tags WHERE tags & 3 = 3;

解释：sports和music分别对应1和2，1+2=3（00011），只有同时包含这两个标签时，按位与结果才等于3。

五、进阶案例

1. 查询包含任意一个标签的记录

查找包含“travel”或“coding”的所有记录：

SELECT * FROM user_tags WHERE tags & (4 | 16);

2. 查询包含全部指定标签的记录

查找同时包含“reading”和“coding”的所有记录：

SELECT * FROM user_tags WHERE tags & (8 | 16) = (8 | 16);

3. 查询不包含某标签的记录

查找不包含“music”的所有记录：

SELECT * FROM user_tags WHERE NOT (tags & 2);

六、总结

SET类型为MySQL提供了灵活的多选项存储方案，适合标签、权限等多选场景。通过理解其二进制存储原理，可以利用位运算实现高效的模糊匹配和复杂查询。实际开发中，合理设计SET成员顺序和数量，可以极大提升数据存储与查询效率。

希望本文能帮助你更好地理解和使用MySQL的SET类型！