如何在MySQL/PHP中查找并删除含特定重复模式的book_id对应记录

一、纯MySQL实现方案 #

1. 定位存在问题的book_id #

核心思路是拆分author字段中的作者列表，统计同一book_id下每个作者的出现次数，筛选出存在重复作者的book_id。

步骤1：生成临时数字辅助表

用于拆分多作者字符串（可按需调整数字数量，适配最多作者数）：

CREATE TEMPORARY TABLE nums (n INT);
INSERT INTO nums VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10);
INSERT INTO nums SELECT n+10 FROM nums;
INSERT INTO nums SELECT n+20 FROM nums;
INSERT INTO nums SELECT n+40 FROM nums;

步骤2：查询问题book_id

SELECT book_id
FROM (
    SELECT 
        book_id,
        TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(c.author, ',', nums.n), ',', -1)) AS author_name
    FROM chapters c
    JOIN nums ON nums.n <= 1 + LENGTH(c.author) - LENGTH(REPLACE(c.author, ',', ''))
) AS split_authors
GROUP BY book_id, author_name
HAVING COUNT(*) > 1
GROUP BY book_id;

该查询会返回所有同一book_id下存在重复作者的记录ID。

2. 删除问题book_id对应的所有记录 #

执行前务必先备份数据，或用SELECT *验证结果：

DELETE FROM chapters
WHERE book_id IN (
    SELECT book_id
    FROM (
        SELECT 
            book_id,
            TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(c.author, ',', nums.n), ',', -1)) AS author_name
        FROM chapters c
        JOIN nums ON nums.n <= 1 + LENGTH(c.author) - LENGTH(REPLACE(c.author, ',', ''))
    ) AS split_authors
    GROUP BY book_id, author_name
    HAVING COUNT(*) > 1
    GROUP BY book_id
);

二、PHP脚本辅助方案 #

针对80万条数据，PHP分批处理可避免MySQL字符串拆分的性能瓶颈：

示例代码 #

<?php
// 初始化数据库连接
$pdo = new PDO('mysql:host=localhost;dbname=your_db;charset=utf8mb4', 'username', 'password');
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$batchSize = 1000; // 分批读取的记录数，按需调整
$offset = 0;
$problemBookIds = [];

// 分批读取数据并检查重复作者
while (true) {
    $stmt = $pdo->prepare("SELECT book_id, author FROM chapters LIMIT :offset, :batchSize");
    $stmt->bindParam(':offset', $offset, PDO::PARAM_INT);
    $stmt->bindParam(':batchSize', $batchSize, PDO::PARAM_INT);
    $stmt->execute();
    $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
    
    if (empty($rows)) break;
    
    foreach ($rows as $row) {
        $authors = array_map('trim', explode(',', $row['author']));
        // 去重后数量减少，说明存在重复作者
        if (count($authors) !== count(array_unique($authors))) {
            $problemBookIds[$row['book_id']] = true;
        }
    }
    
    $offset += $batchSize;
}

// 批量删除问题记录
if (!empty($problemBookIds)) {
    $placeholders = rtrim(str_repeat('?,', count($problemBookIds)), ',');
    $stmt = $pdo->prepare("DELETE FROM chapters WHERE book_id IN ($placeholders)");
    $stmt->execute(array_keys($problemBookIds));
    
    echo "已删除 " . $stmt->rowCount() . " 条记录，涉及 " . count($problemBookIds) . " 个book_id";
} else {
    echo "未发现存在问题的book_id";
}
?>

注意事项：

• 根据服务器内存调整$batchSize参数
• 执行删除前建议先注释删除逻辑，验证收集到的problemBookIds是否准确

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