MySQL - SET

MySQL：SET 数据类型

MySQL 中的 SET 数据类型是一种字符串对象，可以包含零个或多个值，每个值都必须选自创建表时指定的预定义允许值列表。它对于存储少量固定选项或标签很有用。

定义 SET 列

通过提供其可能成员的逗号分隔列表来定义 SET 列。

CREATE TABLE UserPreferences (
    user_id INT PRIMARY KEY,
    notifications SET('email', 'sms', 'push', 'in_app')
);

定义为 SET('a', 'b', 'c') 的列可以存储以下任何值：

• ” (空字符串)
• ‘a’
• ‘b’
• ‘c’
• ‘a,b’
• ‘a,c’
• ‘b,c’
• ‘a,b,c’

主要特性

• 最大成员数：一个 SET 最多可以有 64 个不同的成员。
• 自动去重：如果插入一个包含重复成员的值（例如，‘a,b,a’），MySQL 会将其存储为 ‘a,b’。
• 顺序保留：存储的值按表定义中的列表顺序排列，而不是插入顺序。
• 尾随空格：成员值中的尾随空格在表创建时会自动移除。

SET 值的存储方式

MySQL 以位图形式高效存储 SET 值。集合中的每个成员都被分配一个位。第一个成员对应位 0（值 1），第二个对应位 1（值 2），第三个对应位 2（值 4），依此类推。存储的值是所选成员值的总和。

集合成员	十进制值	二进制值
email	1	0001
sms	2	0010
push	4	0100
in_app	8	1000

存储 ‘email,push’ 等同于存储数值 5 (1 + 4)，或二进制 0101。

使用 SET 数据

让我们创建一个表并插入一些数据。

CREATE TABLE Articles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255),
    tags SET('mysql', 'docker', 'security', 'performance')
);

INSERT INTO Articles(title, tags) VALUES
('Optimizing Queries', 'mysql,performance'),
('Containerizing DBs', 'docker,mysql'),
('SQL Injection Guide', 'security'),
('All About Tags', 'mysql,security,performance');

使用 FIND_IN_SET() 查询

查找包含特定 SET 成员行的最可靠方法是使用 FIND_IN_SET() 函数。它比 LIKE 更安全，因为它检查完整的成员并避免部分匹配（例如，搜索 ‘app’ 并匹配 ‘in_app’）。

-- 查找所有标记为 'mysql' 的文章
SELECT title, tags FROM Articles
WHERE FIND_IN_SET('mysql', tags) > 0;

使用位运算符查询

为了提高性能，你可以使用数字表示进行查询。要查找标记为 ‘security’（值 4）的文章，你可以使用位运算符 AND (&)。

-- 查找所有标记为 'security'（十进制值 4）的文章
SELECT title, tags FROM Articles
WHERE tags & 4;

更新 SET 值

添加成员

要添加标签而不删除现有标签，请使用位运算符 OR (|)。让我们将 ‘security’ 标签（值 4）添加到关于 Docker 的文章中。

UPDATE Articles SET tags = tags | 4 WHERE id = 2;

移除成员

要移除标签，请使用位运算符 AND 和取反值 (& ~)。让我们从第一篇文章中移除 ‘performance’ 标签（值 8）。

UPDATE Articles SET tags = tags & ~8 WHERE id = 1;

注意事项和现代替代方案

尽管 SET 可能看起来很方便，但它是 MySQL 特有的功能，并且由于以下几个原因通常被认为是反模式：

• 违反范式：在单个列中存储多个值违反了数据库规范化的第一范式 (1NF)。
• 可移植性：它使你的模式不可移植到 PostgreSQL 或 SQL Server 等其他数据库系统。
• 查询复杂性：查询可能变得复杂且不够直观，特别是对于不熟悉位运算的开发人员。
• 索引限制：SET 列很难对所有类型的查询进行有效索引。

规范化替代方案：关联表

建模多对多关系（如文章和标签）的标准且最灵活的方法是使用关联表（也称为连接表或交叉引用表）。

-- 1. 包含所有可能标签的表
CREATE TABLE Tags (
    tag_id INT AUTO_INCREMENT PRIMARY KEY,
    tag_name VARCHAR(50) UNIQUE NOT NULL
);

-- 2. 将文章链接到标签的关联表
CREATE TABLE ArticleTags (
    article_id INT,
    tag_id INT,
    PRIMARY KEY (article_id, tag_id),
    FOREIGN KEY (article_id) REFERENCES Articles(id),
    FOREIGN KEY (tag_id) REFERENCES Tags(tag_id)
);

这种设计更具可伸缩性、可移植性，并且更易于查询和索引，因此是现代应用程序推荐的最佳实践。

在应用程序代码中使用 SET

Python (使用 mysql-connector-python)



### 代码
此示例插入一篇带有标签集的新文章，然后获取它。Python 驱动程序会智能地将 MySQL 中的逗号分隔字符串转换为 Python `set` 对象。

```python
# 文件名：use_set_type.py
import mysql.connector
from mysql.connector import Error

def manage_article_tags():
    try:
        with mysql.connector.connect(
            host='localhost', user='root', password='password', database='TUTORIALS'
        ) as connection:

            with connection.cursor() as cursor:
                # 插入一篇带标签的新文章
                title = 'Getting Started with SET'
                tags = 'mysql,security' # 作为逗号分隔字符串传递
                insert_query = "INSERT INTO Articles (title, tags) VALUES (%s, %s)"
                cursor.execute(insert_query, (title, tags))
                article_id = cursor.lastrowid
                connection.commit()
                print(f"Inserted article with ID: {article_id}")

                # 获取文章并检查标签
                select_query = "SELECT title, tags FROM Articles WHERE id = %s"
                cursor.execute(select_query, (article_id,))
                result = cursor.fetchone()

                if result:
                    fetched_title, fetched_tags = result
                    print(f"Title: {fetched_title}")
                    print(f"Tags: {fetched_tags}")
                    print(f"Type of tags object: {type(fetched_tags)}")

    except Error as e:
        print(f"Error: {e}")

if __name__ == "__main__":
    manage_article_tags()

输出

Inserted article with ID: 5
Title: Getting Started with SET
Tags: {'mysql', 'security'}
Type of tags object: <class 'set'>