嘿，这个问题我太熟了！你现在的设计确实会遇到扩展性瓶颈，每次新增测试类型都要修改test_ref表加新列，长期下来表结构会越来越臃肿。咱们可以用多态关联的设计思路来优化，完美满足你的需求——只用两个核心字段，就能让test_ref表指向任意测试表的唯一记录，彻底摆脱频繁改表的麻烦！

核心设计思路 #

多态关联的本质是用两个字段配合实现动态关联：

• 一个字段存储关联的目标表标识（比如测试表的名称test_a、test_b，或者自定义的类型编码）
• 另一个字段存储目标表中对应记录的主键ID

通过这两个字段的组合，就能唯一定位到某张测试表中的某一条记录，而且新增测试类型时，完全不用修改test_ref的表结构，只要在插入数据时指定新的表标识就行。

具体表结构改造 #

1. 重构test_ref表 #

去掉原来的test_a_id、test_b_id等专属关联列，换成以下两个核心字段：

ALTER TABLE test_ref
  DROP COLUMN test_a_id,
  DROP COLUMN test_b_id,
  -- 删掉其他类似的关联列
  ADD COLUMN test_type VARCHAR(50) NOT NULL COMMENT '关联的测试表名称/类型标识',
  ADD COLUMN test_record_id INT NOT NULL COMMENT '对应测试表的记录主键ID',
  -- 添加联合索引，提升查询效率
  ADD INDEX idx_test_ref_type_id (test_type, test_record_id);

如果想更严谨，可以把test_type设为枚举类型，限制输入的有效值，避免错误的表标识：

ALTER TABLE test_ref
  MODIFY COLUMN test_type ENUM('test_a','test_b','test_c') NOT NULL COMMENT '关联的测试表类型';
-- 后续新增测试类型时，只需要修改枚举值：
ALTER TABLE test_ref MODIFY COLUMN test_type ENUM('test_a','test_b','test_c','test_d') NOT NULL;

2. 保持测试表结构不变 #

你的test_a、test_b、test_c等测试表不需要做任何改动，只要保证每张表都有自增主键（比如id INT PRIMARY KEY AUTO_INCREMENT）即可，其他存储测试结果的字段保留原样。

数据插入与查询示例 #

插入数据 #

当用户完成一次测试后，先在对应测试表插入记录，再在test_ref中建立关联：

-- 假设用户ID为1001，完成了test_a的测试，test_a生成ID为2001的记录
INSERT INTO test_a (person_id, test_result, create_time) 
VALUES (1001, '合格', NOW());

-- 在test_ref中建立关联
INSERT INTO test_ref (person_id, test_type, test_record_id, create_time)
VALUES (1001, 'test_a', 2001, NOW());

-- 如果新增了test_d表，直接插入关联记录即可，无需修改test_ref结构
INSERT INTO test_d (person_id, test_result, create_time) 
VALUES (1001, '优秀', NOW());
INSERT INTO test_ref (person_id, test_type, test_record_id, create_time)
VALUES (1001, 'test_d', 3001, NOW());

查询数据 #

如果要查询某个用户的所有测试记录，可以用UNION ALL关联所有测试表：

-- 查询用户1001的所有测试结果
SELECT
  tr.person_id,
  tr.test_type,
  ta.test_result,
  ta.create_time AS test_time
FROM test_ref tr
JOIN test_a ta ON tr.test_type = 'test_a' AND tr.test_record_id = ta.id
WHERE tr.person_id = 1001

UNION ALL

SELECT
  tr.person_id,
  tr.test_type,
  tb.test_result,
  tb.create_time AS test_time
FROM test_ref tr
JOIN test_b tb ON tr.test_type = 'test_b' AND tr.test_record_id = tb.id
WHERE tr.person_id = 1001

UNION ALL

SELECT
  tr.person_id,
  tr.test_type,
  tc.test_result,
  tc.create_time AS test_time
FROM test_ref tr
JOIN test_c tc ON tr.test_type = 'test_c' AND tr.test_record_id = tc.id
WHERE tr.person_id = 1001;

如果只需要查询特定类型的测试，直接过滤test_type后关联对应表即可：

-- 查询用户1001的所有test_a测试记录
SELECT
  ta.*,
  p.name AS person_name
FROM test_a ta
JOIN test_ref tr ON ta.id = tr.test_record_id AND tr.test_type = 'test_a'
JOIN person p ON tr.person_id = p.id
WHERE p.id = 1001;

注意事项 #

1. 无原生外键约束：MySQL不支持动态外键（一个字段关联多张表），所以数据一致性需要在应用层保证，比如删除测试表记录时，要同步删除test_ref中的关联记录；也可以用触发器来实现自动同步。
2. 枚举类型的扩展性：如果用枚举类型存储test_type，新增测试类型时需要修改枚举值，但这比新增列要简单得多，而且不会导致表结构臃肿。
3. 联合索引的必要性：一定要给test_type和test_record_id加联合索引，否则多表关联查询时性能会大幅下降。

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