MySQL ORDER BY ASC仅返回4条数据，DESC正常返回10条求助

在PHP项目中查询已发布（published=1）的文章数据时，使用ORDER BY a.id ASC排序，页面仅显示4条文章；切换为DESC排序时，可正常显示10条符合条件的数据。以下是完整代码：

<?php
declare(strict_types = 1);                               // Use strict types
require 'includes/database-connection.php';              // Create PDO object
require 'includes/functions.php';                        // Include functions

$sql = "SELECT a.id, a.title, a.summary, a.category_id, a.member_id, 
               c.name AS category,
               CONCAT(m.forename, ' ', m.surname) AS author,
               i.file     AS image_file,
               i.alt      AS image_alt 
          FROM article    AS a
          JOIN category   AS c ON a.category_id = c.id
          JOIN member     AS m ON a.member_id   = m.id
          LEFT JOIN image AS i ON a.image_id    = i.id
         WHERE a.published = 1
      ORDER BY a.id ASC
         LIMIT 10;";                                      // SQL to get latest articles
$articles = pdo($pdo, $sql)->fetchAll();                 // Get summaries

$sql = "SELECT id, name FROM category WHERE navigation = 1;"; // SQL to get categories
$navigation  = pdo($pdo, $sql)->fetchAll();              // Get navigation categories

$section     = '';                                       // Current category
$title       = 'Creative Folk';                          // HTML <title> content
$description = 'A collective of creatives for hire';     // Meta description content
?>
<?php include 'includes/header.php'; ?>
  <main class="container grid" id="content">
    <?php foreach ($articles as $article) { ?>
      <article class="summary">
        <a href="article.php?id=<?= $article['id'] ?>">
          <img src="uploads/<?= html_escape($article['image_file'] ?? 'blank.png') ?>"
               alt="<?= html_escape($article['image_alt']) ?>">
          <h2><?= html_escape($article['title']) ?></h2>
          <p><?= html_escape($article['summary']) ?></p>
        </a>
        <p class="credit">
          Posted in <a href="category.php?id=<?= $article['category_id'] ?>">
          <?= html_escape($article['category']) ?></a>
          by <a href="member.php?id=<?= $article['member_id'] ?>">
          <?= html_escape($article['author']) ?></a>
        </p>
      </article>
    <?php } ?>
  </main>
<?php include 'includes/footer.php'; ?>

排查方向及解决方法 #

• 内连接过滤了数据：SQL中使用JOIN category和JOIN member（内连接），意味着只有同时存在对应分类和作者的文章才会被返回。如果ASC排序的前几条文章中，有部分没有匹配的分类或作者记录，就会被过滤，最终只显示4条有效数据；而DESC排序的10条刚好都有完整关联。
  • 验证：直接在数据库执行以下两条语句，对比结果：

    -- 统计所有已发布文章数量
    SELECT COUNT(*) FROM article WHERE published=1;
    -- 统计经过内连接后的已发布文章数量
    SELECT COUNT(*) FROM article a JOIN category c ON a.category_id=c.id JOIN member m ON a.member_id=m.id WHERE a.published=1;
    

  • 解决：如果允许文章无分类或无作者，将JOIN改为LEFT JOIN，确保所有已发布文章都能被查询到：

    SELECT a.id, a.title, a.summary, a.category_id, a.member_id, 
           c.name AS category,
           CONCAT(m.forename, ' ', m.surname) AS author,
           i.file     AS image_file,
           i.alt      AS image_alt 
      FROM article    AS a
      LEFT JOIN category   AS c ON a.category_id = c.id
      LEFT JOIN member     AS m ON a.member_id   = m.id
      LEFT JOIN image AS i ON a.image_id    = i.id
     WHERE a.published = 1
    

  ORDER BY a.id ASC
  LIMIT 10;

• 关联导致数据重复：如果image表中存在同一image_id对应多条记录的情况，LEFT JOIN image会导致同一文章被多次返回。ASC排序时前10条结果里重复数据占比高，实际独特的文章只有4条；DESC时的10条都是不重复的。
  • 验证：在查询语句中添加DISTINCT，看结果数量是否变化：

    SELECT DISTINCT a.id, a.title, a.summary, a.category_id, a.member_id, ...
    

  • 解决：要么清理image表的重复记录，要么在查询中使用DISTINCT或GROUP BY a.id确保每条文章只出现一次。
• PDO fetch模式异常：检查includes/functions.php中的pdo()函数实现，确认它返回的是正确的PDO结果集，没有因为自定义fetch模式导致数据丢失或覆盖。

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