如何在MySQL中生成格式为DNI+姓氏首字母+名字首字母的自定义高校ID

以下方案默认你已存在学生信息表student，包含核心字段：

• dni：字符串/数字类型，存储用户DNI编号
• last_name：字符串类型，存储用户姓氏
• first_name：字符串类型，存储用户名字
  生成规则默认将首字母转为大写，可根据需求调整大小写规则。

1. 动态查询生成（无需持久化存储ID） #

如果不需要将ID固化存储到表中，仅在查询时返回自定义格式的ID，直接用MySQL内置函数拼接即可：

SELECT 
  dni,
  last_name,
  first_name,
  CONCAT(dni, UPPER(LEFT(last_name, 1)), UPPER(LEFT(first_name, 1))) AS custom_university_id
FROM student;

如果不需要统一首字母大写，去掉UPPER()函数即可。

2. 插入数据时自动生成ID（持久化存储） #

如果需要将生成的ID存入表中，首先新增存储ID的字段：

ALTER TABLE student ADD COLUMN custom_university_id VARCHAR(50) UNIQUE COMMENT '自定义高校ID';

可选两种自动生成方式：

• 方式1：插入语句中直接拼接

INSERT INTO student (dni, last_name, first_name, custom_university_id)
VALUES (
  '12345678',
  '张',
  '明',
  CONCAT('12345678', UPPER(LEFT('张', 1)), UPPER(LEFT('明', 1)))
);

• 方式2：触发器自动生成（无需手动维护拼接逻辑）

DELIMITER //
CREATE TRIGGER generate_university_id BEFORE INSERT ON student
FOR EACH ROW
BEGIN
  SET NEW.custom_university_id = CONCAT(NEW.dni, UPPER(LEFT(NEW.last_name, 1)), UPPER(LEFT(NEW.first_name, 1)));
END //
DELIMITER ;

配置触发器后，插入数据时不需要传入custom_university_id字段，数据库会自动完成ID生成和存储。

3. 批量更新历史存量数据的ID #

如果表中已存在旧数据需要批量补全ID，执行以下更新语句即可：

UPDATE student 
SET custom_university_id = CONCAT(dni, UPPER(LEFT(last_name, 1)), UPPER(LEFT(first_name, 1)))
WHERE custom_university_id IS NULL;

特殊场景适配 #

• 如果姓名包含空格/特殊分隔符，需要取第一个有效片段的首字母，可搭配SUBSTRING_INDEX处理：

-- 示例：取姓氏第一个空格前的片段的首字母
UPPER(LEFT(SUBSTRING_INDEX(last_name, ' ', 1), 1))

• 如果需要生成中文姓名的拼音首字母：MySQL原生不支持中文转拼音能力，需提前导入自定义拼音转换函数，或在上层业务代码中完成拼音首字母转换后再拼接ID。

内容的提问来源于stack exchange，提问作者Giorgio Ruitón Mauricci