MySQL检测重复行并为特定行添加多分类ID的最优方法

嘿，针对你这个百万级数据的场景，直接在单字段存多个分类ID其实不是长久之计，反而会给后续查询、维护埋坑。给你两个更高效的MySQL方案，尤其是第一个，绝对是这类多对多关联场景的最优解：

这是数据库范式化设计的标准操作，完美适配视频和分类的多对多关系，性能和可维护性拉满：

表结构设计 #

-- 核心视频表：存唯一的视频信息，URL加唯一索引防重复
CREATE TABLE videos (
    id INT AUTO_INCREMENT PRIMARY KEY,
    url VARCHAR(255) NOT NULL UNIQUE,
    title VARCHAR(255),
    -- 其他视频相关字段（比如时长、缩略图等）
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 分类表：存所有分类信息
CREATE TABLE categories (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL UNIQUE
);

-- 关联表：建立视频和分类的绑定关系，联合主键自动防重复关联
CREATE TABLE video_categories (
    video_id INT NOT NULL,
    category_id INT NOT NULL,
    PRIMARY KEY (video_id, category_id), -- 确保同一个视频不会重复绑定同一个分类
    FOREIGN KEY (video_id) REFERENCES videos(id) ON DELETE CASCADE,
    FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE CASCADE
);

高效插入/更新流程 #

全程用单条或少量SQL完成，不需要PHP多次查询：

1. 先插入视频（如果不存在则新增，存在则更新其他字段）：

INSERT INTO videos (url, title) 
VALUES ('https://example.com/video1', 'Sample Video')
ON DUPLICATE KEY UPDATE title = VALUES(title); -- 按需更新其他字段

2. 再绑定分类（用INSERT IGNORE自动跳过已存在的关联关系）：

-- 先通过URL拿到视频ID，再批量绑定分类
INSERT IGNORE INTO video_categories (video_id, category_id)
VALUES (
    (SELECT id FROM videos WHERE url = 'https://example.com/video1'),
    1 -- 分类ID1
), (
    (SELECT id FROM videos WHERE url = 'https://example.com/video1'),
    2 -- 分类ID2
);

如果用MySQL 8.0+，可以用CTE简化子查询，性能更优：

WITH target_video AS (
    SELECT id FROM videos WHERE url = 'https://example.com/video1'
)
INSERT IGNORE INTO video_categories (video_id, category_id)
SELECT id, 1 FROM target_video UNION ALL
SELECT id, 2 FROM target_video;

这个方案的好处：

• 索引优化到位，百万级数据下查询、插入速度都很快
• 后续统计分类下的视频、视频关联的分类都非常方便
• 完全避免了单字段存多ID的查询复杂、更新麻烦问题

如果你非要在视频表的单个字段里存多个分类ID，推荐用MySQL的JSON类型（比SET灵活，因为SET的枚举值不能动态新增），结合ON DUPLICATE KEY UPDATE实现高效更新：

表结构调整 #

ALTER TABLE videos ADD COLUMN categories JSON;
-- 确保URL的唯一索引依然存在

插入/更新SQL #

用JSON_MERGE_PRESERVE合并分类ID，同时配合自定义逻辑去重（避免重复添加同一个分类ID）：

INSERT INTO videos (url, categories)
VALUES ('https://example.com/video1', '[1]')
ON DUPLICATE KEY UPDATE 
    categories = JSON_ARRAY_UNIQUE(
        JSON_MERGE_PRESERVE(categories, '[2]')
    );

注：JSON_ARRAY_UNIQUE是MySQL 8.0.19+才支持的函数，如果版本较低，需要用自定义函数或JSON_TABLE来实现去重逻辑，但会增加复杂度。

这个方案的缺点很明显：查询某个分类下的所有视频时，需要用JSON_CONTAINS，性能远不如关联表的JOIN查询，百万级数据下会很慢，而且后续维护（比如删除某个分类的所有关联）也很麻烦。

总结：针对300-400万条数据的规模，多对多关联表是绝对的最优解，既符合数据库设计规范，又能保证性能和可维护性，完全不需要PHP的三次查询操作，所有逻辑都能通过SQL高效完成。

内容的提问来源于stack exchange，提问作者Florin Andrei