MySQL 8 - 插入时触发 - VCS 的重复 AUTO_INCREMENT id

Question

努力
创建在 INSERT 上调用的触发器并设置 originId = id (AUTO_INCREMENT)，
我用过SQL提示here in 1st block :

  CREATE TRIGGER insert_example
  BEFORE INSERT ON notes
  FOR EACH ROW 
  SET NEW.originId = (
        SELECT AUTO_INCREMENT 
        FROM information_schema.TABLES 
        WHERE TABLE_SCHEMA = DATABASE() 
        AND TABLE_NAME = 'notes'
  );  

由于 information_schema 缓存，我还设置了

information_schema_stats_expiry = 0

在 my.cnf 文件中。正如我所注意到的，现在信息几乎在每次插入时立即更新。

但是，通过控制台以大约 2 分钟的间隔执行“直接”插入，我一直没有更新 originId 中的 AUTO_INCREMENT 值。
^{(它们应该等于 id 字段)}
显式查询时，获取 AUTO_) 会导致更新的正确值。

因此我怀疑 SELECT AUTO_INCREMENT... 子查询的结果不知何故……什么？缓存？
如何解决这个问题？

谢谢。

---

编辑1

我打算以这种方式实现某种 VCS:

1. 用户创建 新注释，应用程序将其标记为"new"并在 MySQL 表中执行插入操作。这是“来源”注释。
2. 然后用户可以在 UI 中编辑此注释(完全)，应用程序会将其标记为“更新”并将其作为新行再次插入到 MySQL 表中。但是这次 originId 应该填充一个 id 的“origin”注解(通过应用程序逻辑)。等等。
3. 这允许通过 SELECT 上的 originId 进行分区，仅将最新版本获取到 UI。

初始问题:
如果“origin”Note 的 originId 为 NULL，则 MySQL 8 窗口函数默认(且仅？)RESPECT_NULL 模式执行的框架不符合预期(“好吧，呃，这都是关于您在分组列中的 NULL”)。

假设的解决方案:
将“origin”注释的 originId 设置为 id 在它们的initial and only INSERT 中，期望有 2 个好处:

• 通过 originId = id 轻松获取“来源”笔记，
• 通过 originId 执行正确的 PARTITION。

产生的问题:
id 是 AUTO_INCREMENT，因此无法(我知道)通过后端(即 PHP)在 INSERT 上获取其新值(对于新行)。

假设的解决方案:
所以，我希望找到一些 MySQL 机制来解决这个问题(避免对 id 字段进行操作)，TRIGGER 似乎是正确的方法...

---

编辑2

我相信在 MySQL 中自动复制 id AUTO_INCREMENT 字段(或任何字段)非常快速和 super 简单，但现在完全没有出现......

因此，可能更好的方法是使用 vcsGroupId UNSIGNED INT 字段，负责“关联”Note 的版本:

• 在 create 和“origin”上插入 - 用 MAX(vcsGroupId) + 1 填充它，
• 在 edit 和“版本”上插入 - 用“sibling”/“origin”vcsGroupId 值(通过 CTE 获取)填充它，
• 在 view 和“正常”SELECT 上 - 通过 PARTITION BY vcsGroupId、ORDER BY id 或时间戳 使用 Window Function 执行框架DESC，然后只使用第一行(或使用最后一行的升序)，
• 在 view 和“origin”SELECT 上 - 几乎相同，但相反..

这似乎更容易，不是吗？

Answer 1

你这是在玩火。我不知道你的触发器到底会出什么问题(除此之外它对你不起作用)，但我有一种强烈的感觉，很多事情都可能而且将会出错。例如:如果在单个语句中插入多行怎么办？我认为引擎不会为每一行更新 information_schema。如果您运行 INSERT ... SELECT 语句，情况会更糟。因此，将 information_schema 用于此任务是一个非常糟糕的主意。

但是 - 第一个问题是:您为什么需要它？如果您需要保存“原始 ID”，那么您可能打算更新 id 列。这已经是个坏主意了。并假设您会找到解决问题的方法 - 什么可以保证 originId 不会在触发器外更改？

但是 - 另一种方法是在插入时将 originId 列保留为空白，并改为在 UPDATE 触发器中更新它。

假设这是你的 table :

create table vcs_test(
  id int auto_increment,
  origin_id int null default null,
  primary key (id)
);

使用 UPDATE 触发器来保存原始 ID，当它第一次被改变时:

delimiter //
create trigger vcs_test_before_update before update on vcs_test for each row begin
  if new.id <> old.id then
    set new.origin_id = coalesce(old.origin_id, old.id);
  end if;
end;
delimiter //

您的 SELECT 查询将是这样的:

select *, coalesce(origin_id, id) as origin_id from vcs_test;

参见 demo on db-fiddle

您甚至可以使用以下架构保存完整的 ID 历史记录:

create table vcs_test(
  id int auto_increment,
  id_history text null default null,
  primary key (id)
);


delimiter //
create trigger vcs_test_before_update before update on vcs_test for each row begin
  if new.id <> old.id then
    set new.id_history = concat_ws(',', old.id_history, old.id);
  end if;
end;
delimiter //

下面的测试

insert into vcs_test (id) values (null), (null), (null);

update vcs_test set id = 5 where id = 2;
update vcs_test set id = 4 where id = 5;

select *, concat_ws(',', id_history, id) as full_id_history
from vcs_test;

会回来

| id  | id_history | full_id_history |
| --- | ---------- | --------------- |
| 1   |            | 1               |
| 3   |            | 3               |
| 4   | 2,5        | 2,5,4           |

View on DB Fiddle