时间戳

首页 > 解决方案 > MySQL 8 - 触发 INSERT - VCS 的重复 AUTO_INCREMENT id

mysql - MySQL 8 - 触发 INSERT - VCS 的重复 AUTO_INCREMENT id

问题描述

试图
创建在 INSERT & sets 上调用的触发器originId = id (AUTO_INCREMENT)，
我在第一个块中使用了这里建议的 SQL ： 

  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文件中。现在，正如我所注意到的，每次 INSERT 几乎都会立即更新信息。

但是，通过控制台以大约 2 分钟的间隔执行“直接”插入，我一直AUTO_INCREMENT在originId. （它们应该等于字段） 虽然显式查询，获取 AUTO_）会导致更新的正确值。
几个 INSERT 后的表格示例 id

因此我怀疑SELECT AUTO_INCREMENT...子查询的结果以某种方式得到了......什么？缓存？
如何解决这个问题？

谢谢你。

编辑 1

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

  1. 用户创建新注释，应用程序将其标记为“新”并在 MySQL 表中执行 INSERT。它是“起源”注释。
  2. 然后用户可以在 UI 中编辑此注释（完全），应用程序将标记为“更新”并将其在 MySQL 表中再次插入为新行。但这一次originId应该填写一个id“起源”注释（通过应用程序逻辑）。等等。
  3. 这允许通过 SELECT 进行分区originId，仅获取 UI 的最新版本。

初始问题：
如果originId“原点”注释为 NULL，则 MySQL 8 窗口函数默认（且仅？）按列”）。

假定的解决方案：在其初始且唯一
的 INSERT 上 设置originId“起源”注释，期望有 2 个好处：id

产生的问题：
id是AUTO_INCREMENT，所以没有办法（我知道）通过后端（即PHP）在INSERT上获取它的新值（对于新行）。

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

编辑 2

我相信在 MySQL 中自动复制idAUTO_INCREMENT 字段（或任何字段）会非常快速且超级简单，但现在看起来完全不是这样。

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

这似乎更容易，不是吗？

标签： mysqlsql-insertdatabase-triggermysql-8.0

解决方案

你在做的是玩火。我不确切知道您的触发器会出现什么问题（除此之外，它已经不适合您了），但我有一种强烈的感觉，很多事情都可能而且将会出错。例如：如果您在单个语句中插入多行怎么办？我不认为，引擎会更新information_schema每一行。如果你运行一个INSERT ... SELECT声明，情况会更糟。所以使用information_schema这个任务是一个非常糟糕的主意。

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

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

假设这是你的桌子：

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;

请参阅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           |

在 DB Fiddle 上查看

推荐阅读