如何在MySQL中高效存储1-1000的任意数字组合？

嘿，你这个位运算的思路其实挺巧妙，但确实碰到了bigint容量的天花板——毕竟1000位的话，确实要125字节，完全不实用。我给你分享几个在MySQL里高效存储这种数字组合的实用方案，每个都有各自的适用场景，你可以按需选择：

方案1：多对多关联表（最推荐、最规范） #

这是关系型数据库里处理这类“用户-拥有项”关系的标准做法，也是性能和灵活性平衡最好的方案。

实现思路 #

创建两张表：

• 一张用户表，存储用户基础信息
• 一张关联表，专门记录每个用户拥有的数字，用user_id和number做联合主键，避免重复存储

SQL示例 #

创建表：

-- 用户表
CREATE TABLE users (
    user_id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL UNIQUE
);

-- 用户数字关联表
CREATE TABLE user_numbers (
    user_id INT NOT NULL,
    number INT NOT NULL CHECK (number BETWEEN 1 AND 1000), -- 限制数字范围
    PRIMARY KEY (user_id, number), -- 联合主键防止重复
    FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE -- 删除用户时自动删除关联记录
);

插入用户的数字组合：

-- 给user_id=1的用户添加4、25、353这三个数字
INSERT INTO user_numbers (user_id, number) VALUES (1, 4), (1, 25), (1, 353);

查询用户拥有的所有数字：

SELECT number FROM user_numbers WHERE user_id = 1;

优缺点 #

• ✅ 优点：查询灵活（比如找拥有某个数字的所有用户、统计用户数字数量都很方便）、支持索引（查询速度快）、存储高效（只存实际拥有的数字，无冗余）
• ❌ 缺点：如果用户拥有大量数字，关联表的行数会增加，但MySQL轻松处理几十万甚至百万级别的行完全没问题

方案2：紧凑位串存储（节省空间优先） #

如果你特别在意存储空间，可以把数字组合转换成位串存储，本质是你最初思路的优化版——不用超大整数，而是用VARBINARY或BLOB来存二进制位串。

实现思路 #

每个数字对应位串中的一位：比如数字1对应第1位，数字2对应第2位……以此类推，某一位为1表示用户拥有该数字。1000位刚好是125字节（1000/8），用VARBINARY(125)就能存下。

SQL示例 #

创建表：

CREATE TABLE users (
    user_id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL UNIQUE,
    owned_numbers VARBINARY(125) NOT NULL DEFAULT ''
);

给用户添加数字：

-- 给user_id=1的用户添加数字4
UPDATE users SET owned_numbers = BIT_SET(owned_numbers, 4) WHERE user_id = 1;

检查用户是否拥有某个数字：

-- 返回1表示拥有，0表示没有
SELECT BIT_CHECK(owned_numbers, 4) FROM users WHERE user_id = 1;

统计用户拥有的数字数量：

SELECT BIT_COUNT(owned_numbers) FROM users WHERE user_id = 1;

优缺点 #

• ✅ 优点：存储空间极紧凑（固定125字节，不管用户拥有多少数字）、存在性检查速度快
• ❌ 缺点：可读性差（直接看存储值不知道对应哪些数字）、查询所有拥有的数字比较麻烦（需要逐个位检查）、不适合复杂的关联查询

方案3：JSON数组存储（快速开发、可读性优先） #

如果你的项目不需要极致性能，更看重开发速度和数据可读性，可以用MySQL的JSON类型存储数字数组。

实现思路 #

直接把用户拥有的数字存成JSON数组，MySQL 5.7及以上版本支持JSON类型，并且提供了丰富的JSON操作函数。

SQL示例 #

创建表：

CREATE TABLE users (
    user_id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL UNIQUE,
    owned_numbers JSON NOT NULL DEFAULT '[]'
);

插入或更新用户的数字组合：

-- 直接插入带数字数组的用户
INSERT INTO users (username, owned_numbers) VALUES ('john', '[4,25,353]');

-- 给已有用户添加数字
UPDATE users SET owned_numbers = JSON_ARRAY_APPEND(owned_numbers, '$', 100) WHERE user_id = 1;

查询用户拥有的所有数字：

SELECT JSON_EXTRACT(owned_numbers, '$[*]') FROM users WHERE user_id = 1;

检查用户是否拥有某个数字：

SELECT JSON_CONTAINS(owned_numbers, '4') FROM users WHERE user_id = 1;

优缺点 #

• ✅ 优点：可读性强（直接能看到数组里的数字）、开发成本低（不用维护关联表）、支持灵活的JSON操作
• ❌ 缺点：查询性能不如关联表（尤其是筛选拥有某个数字的用户时，索引支持有限）、存储有一定冗余

总结 #

• 如果你需要频繁做查询、统计或关联操作，优先选多对多关联表，这是最符合关系型数据库设计规范的方案
• 如果极致节省空间，且主要做存在性检查，选位串存储
• 如果追求快速开发和可读性，选JSON数组存储

内容的提问来源于stack exchange，提问作者John James