如何在 SQL 中为重复姓名自动生成带分组序号的标识符-php教程-PHP中文网

如何在 SQL 中为重复姓名自动生成带分组序号的标识符

本文介绍一种基于数据库规范化与窗口函数的优雅方案，解决电影数据库中重复演员/职员姓名需按人名分组编号（如“george henry-2”）的问题，避免 php 循环中手动计数导致的跨组累加错误。

本文介绍一种基于数据库规范化与窗口函数的优雅方案，解决电影数据库中重复演员/职员姓名需按人名分组编号（如“george henry-2”）的问题，避免 php 循环中手动计数导致的跨组累加错误。

在处理电影数据库中的演员与职员信息时，直接在应用层（如 PHP）用全局计数器实现“同名递增编号”极易出错——正如您遇到的：George Henry 正确生成了 -2、-3、-4，但 Matthew Fox 却从 -5 开始，说明 $counter++ 未在姓名变更时重置。根本原因在于逻辑耦合过重：PHP 循环无法天然感知 SQL 结果集中的“分组边界”。

更健壮、可维护且高性能的解法是将计数逻辑下推至数据库层，并配合合理的数据模型设计。我们推荐以下两步重构：

✅ 第一步：规范化数据结构

摒弃冗余存储姓名的 tbl_cast_person / tbl_crew_person 表，建立第三范式模型：

-- 统一人员主表（去重源头）
CREATE TABLE persons (
  id INT PRIMARY KEY AUTO_INCREMENT,
  firstname VARCHAR(255) NOT NULL,
  lastname  VARCHAR(255) NOT NULL,
  UNIQUE KEY uk_name (firstname, lastname)
);

-- 关联表（仅存外键，无重复姓名）
CREATE TABLE cast (
  id INT PRIMARY KEY AUTO_INCREMENT,
  person_id INT NOT NULL,
  FOREIGN KEY (person_id) REFERENCES persons(id) ON DELETE CASCADE
);

CREATE TABLE crew (
  id INT PRIMARY KEY AUTO_INCREMENT,
  person_id INT NOT NULL,
  FOREIGN KEY (person_id) REFERENCES persons(id) ON DELETE CASCADE
);

此设计确保姓名唯一性由数据库强制约束，tbl_dup_actor 表不再需要——重复项天然体现在 cast/crew 表对同一 persons.id 的多次引用。

✅ 第二步：用窗口函数生成分组序号

针对您原始需求（为每个重复姓名生成 Name-2、Name-3…），直接使用 ROW_NUMBER() 窗口函数，按姓名分组排序编号：

SELECT 
  CONCAT(p.firstname, ' ', p.lastname, '-', 
         ROW_NUMBER() OVER (
           PARTITION BY p.firstname, p.lastname 
           ORDER BY p.id ASC
         )) AS person_name,
  p.id AS person_id
FROM persons p
INNER JOIN cast c ON p.id = c.person_id
WHERE (p.firstname, p.lastname) IN (
  -- 筛选出至少出现2次的姓名组合
  SELECT firstname, lastname
  FROM persons
  INNER JOIN cast ON persons.id = cast.person_id
  GROUP BY firstname, lastname
  HAVING COUNT(*) > 1
)
ORDER BY p.firstname, p.lastname, p.id;

✅ 关键优势：