如何用PHP/MySQL/jQuery基于含数组值的列对行排序？

问题场景 #

现有40000+用户的数据库，每个用户拥有多个职业，原本采用数组形式存储职业ID（如Person A对应[1,2,3]，对应actor、screenwriter、director）。需要实现按职业名称升序排列，多职业用户按每个职业重复展示的效果，比如Person A会同时出现在actor、director等职业分组下。

原数组存储的常规排序无法满足需求：分页后，后续页面只能看到单一职业的用户，跨职业用户会被遗漏。

最优解决方案：数据库范式化调整 #

1. 数据库表结构设计 #

放弃数组存储，改用多对多关联表，符合关系型数据库范式，同时适配大数据量的高效查询：

• persons（用户表）：存储用户基础信息

  CREATE TABLE persons (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    -- 其他用户字段
    INDEX idx_person_name (name)
  );
  

• occupations（职业表）：存储去重后的职业信息

  CREATE TABLE occupations (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL UNIQUE,
    INDEX idx_occupation_name (name)
  );
  

• person_occupation（用户-职业关联表）：建立用户与职业的多对多关系

  CREATE TABLE person_occupation (
    person_id INT NOT NULL,
    occupation_id INT NOT NULL,
    PRIMARY KEY (person_id, occupation_id),
    FOREIGN KEY (person_id) REFERENCES persons(id),
    FOREIGN KEY (occupation_id) REFERENCES occupations(id),
    INDEX idx_person_occupation (person_id, occupation_id)
  );
  

2. 核心查询语句 #

通过关联查询，将每个用户的每个职业拆分为独立行，自然实现按职业排序并重复展示用户的效果：

SELECT p.id, p.name, o.name AS occupation
FROM persons p
JOIN person_occupation po ON p.id = po.person_id
JOIN occupations o ON po.occupation_id = o.id
ORDER BY o.name ASC

3. PHP分页实现（适配大数据量） #

使用PDO预处理查询，避免SQL注入，同时支持高效分页：

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

// 分页参数
$page = isset($_GET['page']) ? max(1, (int)$_GET['page']) : 1;
$perPage = 20; // 每页展示20条
$offset = ($page - 1) * $perPage;

// 预处理查询
$sql = "SELECT p.id, p.name, o.name AS occupation
        FROM persons p
        JOIN person_occupation po ON p.id = po.person_id
        JOIN occupations o ON po.occupation_id = o.id
        ORDER BY o.name ASC
        LIMIT :offset, :perPage";

$stmt = $pdo->prepare($sql);
$stmt->bindParam(':offset', $offset, PDO::PARAM_INT);
$stmt->bindParam(':perPage', $perPage, PDO::PARAM_INT);
$stmt->execute();
$userList = $stmt->fetchAll(PDO::FETCH_ASSOC);
?>

4. 前端渲染（jQuery辅助） #

将查询结果渲染为表格，并添加分页控件：

<table id="userTable" border="1" cellpadding="8">
  <thead>
    <tr>
      <th>姓名</th>
      <th>职业</th>
    </tr>
  </thead>
  <tbody>
    <?php foreach ($userList as $user): ?>
    <tr>
      <td><?= htmlspecialchars($user['name']) ?></td>
      <td><?= htmlspecialchars($user['occupation']) ?></td>
    </tr>
    <?php endforeach; ?>
  </tbody>
</table>

<!-- 分页按钮 -->
<div class="pagination">
  <?php if ($page > 1): ?>
  <a href="?page=<?= $page - 1 ?>">上一页</a>
  <?php endif; ?>
  <span>第 <?= $page ?> 页</span>
  <a href="?page=<?= $page + 1 ?>">下一页</a>
</div>

5. 性能优化要点 #

• 给person_occupation表建立person_id + occupation_id的联合主键（同时也是索引），加速关联查询
• 给occupations.name建立索引，优化ORDER BY的排序效率
• 避免使用SELECT *，只查询需要的字段，减少数据传输

替代方案：不调整表结构的折中实现 #

如果无法修改现有数组存储的表结构（不推荐），可以使用MySQL的JSON函数拆分数组，但大数据量下效率低于多表关联：

SELECT p.id, p.name, o.name AS occupation
FROM persons p
JOIN JSON_TABLE(
  p.occupation_ids, -- 假设该字段是JSON类型的数组，如[1,2,3]
  '$[*]' COLUMNS(occupation_id INT PATH '$')
) jt
JOIN occupations o ON jt.occupation_id = o.id
ORDER BY o.name ASC
LIMIT :offset, :perPage;

体验优化替代方案：优先使用过滤搜索 #

如果不需要重复展示用户，可优先提供职业筛选功能，让用户选择特定职业后，展示所有拥有该职业的用户（每个用户仅出现一次）：

1. 筛选查询语句 #

SELECT p.id, p.name, GROUP_CONCAT(o.name SEPARATOR ', ') AS all_occupations
FROM persons p
JOIN person_occupation po ON p.id = po.person_id
JOIN occupations o ON po.occupation_id = o.id
WHERE o.id IN (1, 3) -- 选中的职业ID列表
GROUP BY p.id, p.name
ORDER BY p.name ASC;

2. 前端复选框筛选示例 #

<!-- 职业筛选复选框 -->
<div class="filter">
  <label><input type="checkbox" class="occupation-checkbox" value="1"> Actor</label>
  <label><input type="checkbox" class="occupation-checkbox" value="3"> Director</label>
  <button id="filterBtn">筛选</button>
</div>

<script src="https://code.jquery.com/jquery-3.6.0.min.js"></script>
<script>
$('#filterBtn').click(function() {
  const selectedIds = $('.occupation-checkbox:checked').map(function() {
    return $(this).val();
  }).get();
  
  $.get('filter-users.php', { occupation_ids: selectedIds.join(',') }, function(data) {
    $('#userTable tbody').html(data);
  });
});
</script>

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