本教程旨在解决从数据库中存储的json数组中提取项目id，并据此高效查询相关数据库记录的问题。通过演示如何将json字符串解码为php数组，聚合所有id，并利用sql的`in`子句执行单次查询，从而避免多次数据库往返，显著提升数据检索性能。
在现代Web应用开发中，有时我们需要在数据库的单个字段中存储一组相关的标识符（ID），例如用户收藏的商品ID列表、文章标签ID等。将这些ID以JSON数组的形式存储是一个常见的做法。然而，当需要根据这些存储在JSON数组中的ID来查询另一张表中的具体项目时，如何高效地实现这一目标是开发者面临的一个挑战。
问题背景与低效方案分析
假设我们有一个accounts表，其中包含一个名为bookmarks的字段，用于存储用户收藏的图书ID，其数据类型通常是TEXT或JSON，内容形如 "[101, 205, 312]"。现在，我们需要根据这些ID从books表中检索出所有对应的图书信息。
一种直观但效率低下的做法是：
从accounts表中查询出bookmarks字段的JSON字符串。将JSON字符串解码为PHP数组。遍历这个PHP数组，对每个ID执行一次SELECt * FROM books WHERe id = [id]的SQL查询。这种方法会导致N+1次数据库查询（1次获取JSON字符串，N次根据ID查询图书），随着ID数量的增加，数据库的负载和查询响应时间会急剧上升，严重影响应用性能。
高效解决方案：利用JSON解码与SQL IN子句
为了解决上述效率问题，我们可以采用以下策略：
从数据库中获取包含JSON数组的字段。将JSON字符串解码为PHP数组。从解码后的数组中提取所有项目ID，并聚合到一个新的扁平数组中。利用SQL的IN子句，将所有ID一次性传递给数据库，执行单次查询。这种方法将多次数据库查询合并为一次，显著减少了数据库往返次数，从而提升了查询效率。
步骤一：获取并解码JSON数据
首先，我们需要从accounts表中检索指定用户的bookmarks字段。获取到JSON字符串后，使用PHP的json_decode()函数将其转换为PHP数组。
爱派AiPy 融合LLM与Python生态的开源AI智能体
1 查看详情 
// 假设 $conn 是你的数据库连接对象$firstname = "John"; // 示例用户姓氏$lastname = "Doe"; // 示例用户名字$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);$bookmarkIds = [];if ($row = mysqli_fetch_assoc($result)) { // 确保 'bookmarks' 字段存在且不为空 if (isset($row['bookmarks']) && !empty($row['bookmarks'])) { // json_decode 第二个参数为 true，表示解码为关联数组 // 如果JSON数组只包含数字ID，则会解码为索引数组 $decodedJson = json_decode($row['bookmarks'], true); if (json_last_error() === JSON_ERROR_NONE && is_array($decodedJson)) { // 将所有ID收集到一个扁平数组中 foreach ($decodedJson as $id) { // 确保ID是有效的数字 if (is_numeric($id)) { $bookmarkIds[] = (int)$id; // 转换为整数类型 } } } else { // JSON解码失败或数据格式不正确 error_log("Error decoding bookmarks JSON: " . json_last_error_msg()); } }}mysqli_stmt_close($stmt);登录后复制说明：
这里使用了预处理语句（mysqli_prepare），以防止SQL注入攻击。json_decode($row['bookmarks'], true) 将JSON字符串解码为PHP数组。如果JSON数据是 [101, 205]，则会解码为 [101, 205]。在遍历解码后的数组时，我们进行了类型检查，确保只收集有效的数字ID，并将其转换为整数类型。步骤二：构建并执行IN子句查询
在获取到所有有效的bookmarkIds后，我们可以使用implode()函数将这些ID拼接成一个逗号分隔的字符串，并用于SQL的IN子句。
$books = [];if (!empty($bookmarkIds)) { // 构建IN子句的占位符字符串 // 例如：?, ?, ? $placeholders = implode(',', array_fill(0, count($bookmarkIds), '?')); $sqlBooks = "SELECt * FROM `books` WHERe id IN (" . $placeholders . ")"; $stmtBooks = mysqli_prepare($conn, $sqlBooks); // 动态绑定参数 // 需要创建一个包含所有ID的数组，并为每个ID指定类型 $types = str_repeat('i', count($bookmarkIds)); // 'i' 表示整数类型 mysqli_stmt_bind_param($stmtBooks, $types, ...$bookmarkIds); mysqli_stmt_execute($stmtBooks); $resultBooks = mysqli_stmt_get_result($stmtBooks); while ($rowBook = mysqli_fetch_assoc($resultBooks)) { $books[] = $rowBook; } mysqli_stmt_close($stmtBooks);}// 打印查询到的图书信息print_r($books);登录后复制说明：
array_fill(0, count($bookmarkIds), '?') 创建一个与ID数量相同，每个元素都是?的数组。implode(',', ...) 将这些问号用逗号连接起来，形成 ?, ?, ? 这样的占位符字符串。str_repeat('i', count($bookmarkIds)) 生成与ID数量相等的类型字符串，例如 iii 表示三个整数类型。mysqli_stmt_bind_param($stmtBooks, $types, ...$bookmarkIds) 动态绑定参数。...$bookmarkIds 是PHP 5.6+的splat操作符，用于将数组元素作为独立的参数传递。完整示例代码
结合上述步骤，以下是完整的示例代码：
<?php// 假设 $conn 已经是一个有效的 mysqli 数据库连接// $conn = new mysqli("localhost", "username", "password", "database");// if ($conn->connect_error) {// die("Connection failed: " . $conn->connect_error);// }// 示例数据$firstname = "John";$lastname = "Doe";// --- 步骤一：获取并解码JSON数据，提取ID ---$bookmarkIds = [];$sqlAccount = "SELECt bookmarks FROM `accounts` WHERe firstname = ? AND lastname = ?";$stmtAccount = mysqli_prepare($conn, $sqlAccount);if ($stmtAccount) { mysqli_stmt_bind_param($stmtAccount, "ss", $firstname, $lastname); mysqli_stmt_execute($stmtAccount); $resultAccount = mysqli_stmt_get_result($stmtAccount); if ($rowAccount = mysqli_fetch_assoc($resultAccount)) { if (isset($rowAccount['bookmarks']) && !empty($rowAccount['bookmarks'])) { $decodedJson = json_decode($rowAccount['bookmarks'], true); if (json_last_error() === JSON_ERROR_NONE && is_array($decodedJson)) { foreach ($decodedJson as $id) { if (is_numeric($id)) { $bookmarkIds[] = (int)$id; } } } else { error_log("Error decoding bookmarks JSON for user " . $firstname . " " . $lastname . ": " . json_last_error_msg()); } } } mysqli_stmt_close($stmtAccount);} else { error_log("Failed to prepare statement for accounts: " . mysqli_error($conn));}// --- 步骤二：构建并执行IN子句查询 ---$books = [];if (!empty($bookmarkIds)) { // 构建IN子句的占位符字符串，例如 ?, ?, ? $placeholders = implode(',', array_fill(0, count($bookmarkIds), '?')); $sqlBooks = "SELECt * FROM `books` WHERe id IN (" . $placeholders . ")"; $stmtBooks = mysqli_prepare($conn, $sqlBooks); if ($stmtBooks) { // 动态绑定参数类型字符串，例如 'iii' 表示三个整数 $types = str_repeat('i', count($bookmarkIds)); // 使用splat操作符将数组元素作为独立的参数传递给bind_param mysqli_stmt_bind_param($stmtBooks, $types, ...$bookmarkIds); mysqli_stmt_execute($stmtBooks); $resultBooks = mysqli_stmt_get_result($stmtBooks); while ($rowBook = mysqli_fetch_assoc($resultBooks)) { $books[] = $rowBook; } mysqli_stmt_close($stmtBooks); } else { error_log("Failed to prepare statement for books: " . mysqli_error($conn)); }}// 输出查询结果if (!empty($books)) { echo "<h2>收藏的图书列表:</h2>"; foreach ($books as $book) { echo "<p>ID: " . $book['id'] . ", 标题: " . $book['title'] . ", 作者: " . $book['author'] . "</p>"; }} else { echo "<p>未找到收藏的图书或用户未收藏任何图书。</p>";}// 关闭数据库连接// $conn->close(); ?>登录后复制注意事项与最佳实践
SQL注入风险与预处理语句： 教程中的示例代码已经采用了预处理语句（Prepared Statements）来构建SQL查询。这是防止SQL注入攻击的关键措施，尤其是在动态构建IN子句时，务必使用参数绑定而不是直接拼接字符串。性能优化： 使用IN子句代替循环多次查询是显著的性能优化。它减少了数据库连接的建立和关闭次数，以及网络传输的开销。错误处理：json_decode错误： 在使用json_decode后，应检查json_last_error()和json_last_error_msg()来判断JSON字符串是否成功解码。数据库查询错误： 始终检查mysqli_prepare()、mysqli_stmt_execute()等函数的返回值，并在失败时记录错误日志，以便于调试和问题排查。空ID列表： 在尝试构建IN子句之前，检查$bookmarkIds数组是否为空。如果为空，则无需执行后续的数据库查询。数据类型匹配： 在mysqli_stmt_bind_param()中，确保为每个绑定参数指定了正确的类型（例如，i代表整数，s代表字符串）。数据库设计考量： 尽管将ID列表存储为JSON数组在某些场景下很方便，但它并非总是最佳实践。优点： 简化了数据模型，减少了JOIN操作的复杂性（对于简单查询）。缺点： 无法直接在数据库层面进行索引、搜索或关联查询（例如，查询哪些用户收藏了ID为X的图书），数据冗余（如果同一个ID在多个JSON数组中出现），并且更新单个ID可能需要读取、修改、写回整个JSON字符串。替代方案： 对于需要频繁查询、索引或进行复杂关联的场景，更推荐使用第三范式（3NF）或联结表（Pivot Table）来存储多对多关系，例如创建一个user_bookmarks表，包含user_id和book_id字段。总结
通过本教程，我们学习了如何高效地从数据库中存储的JSON数组中提取ID，并利用SQL的IN子句结合预处理语句来查询相关数据。这种方法不仅提升了查询性能，还通过参数绑定增强了应用的安全性。在实际开发中，开发者应根据具体业务需求和数据访问模式，权衡JSON存储的便利性与传统关系型模型的可扩展性和查询能力。
以上就是从数据库JSON数组中高效查询指定ID的项目的详细内容，更多请关注php中文网其它相关文章！
