如何将MariaDB自定义版本控制（validFrom/validUntil）迁移至系统版本控制？

核心问题原因 #

MariaDB的系统版本控制列ROW_START/ROW_END默认是隐藏列，无法在普通UPDATE/INSERT语句中直接引用，这就是你执行UPDATE tableName SET ROW_START = validFrom时报错的原因。

分步迁移方案（单表示例，可批量脚本化） #

1. 先禁用已启用的系统版本控制（若已开启） #

如果目标表已经启用了系统版本控制，先临时关闭以便手动处理历史数据：

ALTER TABLE tableName DISABLE SYSTEM VERSIONING;

2. 迁移历史归档行到系统自动生成的历史表 #

MariaDB启用系统版本控制时会自动创建对应历史表（命名格式为tableName_history），我们需要把原表中validUntil IS NOT NULL的过期历史行迁移过去：

-- 将历史行插入系统历史表，映射自定义列到版本控制列
INSERT INTO tableName_history
SELECT 
  *, 
  validFrom AS ROW_START, 
  validUntil AS ROW_END
FROM tableName
WHERE validUntil IS NOT NULL;

-- 删除原表中的历史行（仅保留当前活跃行）
DELETE FROM tableName WHERE validUntil IS NOT NULL;

3. 启用系统版本控制并映射自定义列 #

通过SYSTEM VERSIONING USE COLUMNS子句，直接将现有validFrom/validUntil映射为系统版本控制的ROW_START/ROW_END列，无需手动修改隐藏列：

-- 保留历史表数据，避免被清空
SET @@system_versioning_alter_history = KEEP;

ALTER TABLE tableName
-- 可选：将自定义列修改为系统版本列默认类型（TIMESTAMP(6)），也可保留原DATETIME(3)类型
MODIFY COLUMN validFrom TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
MODIFY COLUMN validUntil TIMESTAMP(6) NULL DEFAULT '9999-12-31 23:59:59.999999',
-- 核心：映射自定义列到系统版本控制列
SYSTEM VERSIONING USE COLUMNS validFrom AS ROW_START, validUntil AS ROW_END;

4. 验证迁移结果 #

查询包含所有版本的数据（当前行+历史行）：

SELECT * FROM tableName FOR SYSTEM_TIME ALL;

确认所有原validFrom/validUntil的时间戳都正确对应到系统版本控制的时间列。

批量处理142张表的技巧 #

因为表数量多，建议通过脚本自动生成执行语句：

1. 筛选出包含validFrom和validUntil的表：

SELECT table_name 
FROM information_schema.columns 
WHERE table_schema = '你的数据库名' 
AND column_name IN ('validFrom', 'validUntil')
GROUP BY table_name 
HAVING COUNT(*) = 2;

2. 遍历查询结果，自动生成并执行上述ALTER、INSERT语句（可使用Shell脚本或存储过程实现）。

关键说明 #

• 无需手动修改ROW_START/ROW_END隐藏列，通过USE COLUMNS映射是官方支持的合规方式。
• 自定义列类型若为DATETIME(3)，无需强制修改为TIMESTAMP(6)，MariaDB 11.8版本支持直接兼容映射。
• 启用系统版本控制后，后续的更新/删除操作会自动维护validUntil（即ROW_END）列，无需手动干预。

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