PHP与MySQL：高效处理JSON存储的ID列表并进行批量查询

// 假设已获取 $firstname 和 $lastname
$sql = "SELECT bookmarks FROM `accounts` WHERE firstname = ? AND lastname = ?";
$stmt = mysqli_prepare($conn, $sql);
mysqli_stmt_bind_param($stmt, "ss", $firstname, $lastname);
mysqli_stmt_execute($stmt);
$result = mysqli_stmt_get_result($stmt);
$rows = [];
while($r = mysqli_fetch_assoc($result)) {
    $rows[] = $r;
}
mysqli_stmt_close($stmt);

// 错误且低效的做法：循环执行查询
foreach($rows as $row_data){
    $bookmark_json = $row_data['bookmarks'];
    $bookmark_ids = json_decode($bookmark_json, true); // 解析JSON
    foreach($bookmark_ids as $id) {
        // 为每个ID执行一次独立的查询，效率极低
        $book_sql = "SELECT * FROM books WHERE id = ?";
        $book_stmt = mysqli_prepare($conn, $book_sql);
        mysqli_stmt_bind_param($book_stmt, "i", $id);
        mysqli_stmt_execute($book_stmt);
        $book_result = mysqli_stmt_get_result($book_stmt);
        while($book_row = mysqli_fetch_assoc($book_result)) {
            print_r($book_row);
        }
        mysqli_stmt_close($book_stmt);
    }
}

// 假设已获取 $firstname 和 $lastname，并已建立 $conn 数据库连接
$sql = "SELECT bookmarks FROM `accounts` WHERE firstname = ? AND lastname = ?";
$stmt = mysqli_prepare($conn, $sql);
mysqli_stmt_bind_param($stmt, "ss", $firstname, $lastname);
mysqli_stmt_execute($stmt);
$result = mysqli_stmt_get_result($stmt);

$all_bookmark_ids = [];
while($r = mysqli_fetch_assoc($result)) {
    // 确保 'bookmarks' 键存在且不为空
    if (isset($r['bookmarks']) && !empty($r['bookmarks'])) {
        $decoded_ids = json_decode($r['bookmarks'], true);
        // 检查 json_decode 是否成功且返回的是数组
        if (is_array($decoded_ids)) {
            // 将所有ID收集到一个扁平数组中
            foreach($decoded_ids as $id){
                $all_bookmark_ids[] = (int)$id; // 强制转换为整数类型
            }
        }
    }
}
mysqli_stmt_close($stmt);

if (empty($all_bookmark_ids)) {
    echo "没有找到任何书签ID。\n";
    // 提前退出或处理无ID的情况
    exit(); 
}

// 过滤掉重复ID并保持唯一性，可选但推荐
$unique_bookmark_ids = array_unique($all_bookmark_ids);

// 为预处理语句构建占位符字符串
// 例如：如果unique_bookmark_ids有3个元素，则生成 "?,?,?"
$placeholders = implode(',', array_fill(0, count($unique_bookmark_ids), '?'));

// 构建包含IN子句的SQL查询
$bookmark_sql = "SELECT * FROM books WHERE id IN ($placeholders)";

$book_stmt = mysqli_prepare($conn, $bookmark_sql);

// 动态绑定参数
// 首先构建类型字符串，例如 "iii" 表示三个整数
$types = str_repeat('i', count($unique_bookmark_ids));
// 使用 call_user_func_array 动态绑定参数
mysqli_stmt_bind_param($book_stmt, $types, ...$unique_bookmark_ids);

mysqli_stmt_execute($book_stmt);
$book_result = mysqli_stmt_get_result($book_stmt);

while($book_row = mysqli_fetch_assoc($book_result)) { 
    print_r($book_row); // 输出书籍详情
}
mysqli_stmt_close($book_stmt);

<?php
// 假设 $conn 是已建立的数据库连接
// 假设 $firstname 和 $lastname 是从用户输入获取的，并且已经过验证

// 示例数据
$firstname = "John"; 
$lastname = "Doe";

// 1. 从 accounts 表中获取 bookmarks JSON 字符串
$sql = "SELECT bookmarks FROM `accounts` WHERE firstname = ? AND lastname = ?";
$stmt = mysqli_prepare($conn, $sql);

if (!$stmt) {
    die("预处理语句失败: " . mysqli_error($conn));
}

mysqli_stmt_bind_param($stmt, "ss", $firstname, $lastname);
mysqli_stmt_execute($stmt);
$result = mysqli_stmt_get_result($stmt);

$all_bookmark_ids = [];
while($r = mysqli_fetch_assoc($result)) {
    if (isset($r['bookmarks']) && !empty($r['bookmarks'])) {
        $decoded_ids = json_decode($r['bookmarks'], true);
        if (is_array($decoded_ids)) {
            foreach($decoded_ids as $id){
                // 确保ID是整数类型，避免SQL类型不匹配
                $all_bookmark_ids[] = (int)$id; 
            }
        } else {
            // 处理JSON解码失败的情况
            echo "警告: JSON解码失败或返回非数组类型。\n";
        }
    }
}
mysqli_stmt_close($stmt);

// 2. 如果没有书签ID，则无需继续查询
if (empty($all_bookmark_ids)) {
    echo "没有找到任何书签。\n";
    exit();
}

// 过滤重复ID，提高效率
$unique_bookmark_ids = array_unique($all_bookmark_ids);

// 3. 构建 SQL IN 子句的占位符和查询语句
// 例如：如果 unique_bookmark_ids = [1, 5, 10]，则 $placeholders = "?,?,?"
$placeholders = implode(',', array_fill(0, count($unique_bookmark_ids), '?'));
$bookmark_sql = "SELECT * FROM books WHERE id IN ($placeholders)";

$book_stmt = mysqli_prepare($conn, $bookmark_sql);

if (!$book_stmt) {
    die("预处理语句失败: " . mysqli_error($conn));
}

// 4. 动态绑定参数
// 构建类型字符串，例如 "iii"
$types = str_repeat('i', count($unique_bookmark_ids));
// 使用 ... 操作符将数组元素作为独立参数传递给 bind_param
mysqli_stmt_bind_param($book_stmt, $types, ...$unique_bookmark_ids);

mysqli_stmt_execute($book_stmt);
$book_result = mysqli_stmt_get_result($book_stmt);

echo "<h2>收藏的书籍列表:</h2>";
while($row = mysqli_fetch_assoc($book_result)) { 
    echo "ID: " . $row['id'] . ", 标题: " . $row['title'] . ", 作者: " . $row['author'] . "<br>";
}
mysqli_stmt_close($book_stmt);

// 关闭数据库连接 (如果不是持久连接)
mysqli_close($conn);
?>