是否有与 Python 的 set 类型等效的 MySQL? [英] Is there a MySQL equivalent to Python's set type?

问题描述

标题说明了一切:我正在寻找一种在 MySQL 数据库中保存 Python [冻结] 集的标准方法.

The title says it all: I am looking for a standard way to preserve Python [frozen]sets in a MySQL database.

注意:SET MySql 数据类型不是等价的；它更像是一个限制列表对象的多项选择.

Note: the SET MySql datatype is not an equivalent; it is more of a multiple-choice from a restricted list object.

注 (2):我可以发明一些特别的东西；假设我的集合是不包含逗号的字符串，我可以将它记录为一个排序的逗号分隔的字符串列表.我正在寻找更通用和更标准的东西.

Note (2): I can invent something ad-hoc; assuming my set is of strings which contain no commas, I can record it as a sorted comma-separated list of strings. I am looking for something which is a bit more general and more standard.

注 (3):我看过 MySQL 参考网页.我在那里找不到它.我还在问，因为我不太精通数据库，我认为可能存在标准方法.

Note (3): I have looked at MySQL reference webpages. I could not find it there. I am still asking, as I am not so proficient with databases, and I think that it is possible that a standard method exists.

推荐答案

在 SQL 中实现集合的正确方法是使用两个额外的表:

The proper way to implement a set in SQL would be with two extra tables:

CREATE TABLE set (
    id int unsigned PRIMARY KEY AUTO_INCREMENT
);

CREATE TABLE set_member (
    set_id int unsigned NOT NULL,
    FOREIGN KEY (set_id) REFERENCES set(id),
    member <whatever_type_your_set_is_of> NOT NULL,
    UNIQUE (set_id, member)
);

(如果您只想将集合存储在一个表中，并且该表具有唯一 ID，那么您可能可以避免使用 set 表，并且每个记录只存储一个集合表)

(You might be able to avoid the set table, if you want to store sets in only one table, and that table has a unique ID, and you will only store one set per record in the table)

因为很可能您没有在寻找合适的 SQL 方法(这实际上很好，如果您永远不会对这些集合执行任何数据库内操作，除了存储和检索)，您可能会将非规范化的集合存储在表的单个字段中，而不是遵循推荐的做法；在这种情况下，没有可以表示 Python 集的本机 MySQL 数据类型，您需要在 varchar 或 text 列中序列化您的集.选择适合您需求的序列化格式完全取决于您.

Since it is very likely that you're not looking for a proper SQL approach (which is actally fine, if you're never going to perform any in-database operation on those sets, besides storage and retrieval), you will likely store your set denormalized in a single field on a table instead of following recommended practices; in that case there is no native MySQL data type that can represent a Python set and you will need to serialize your set in a varchar or a text column. It is completely up to you to pick a serialization format that fits your needs.

这篇关于是否有与 Python 的 set 类型等效的 MySQL?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！