如何通过触发器提取插入行作为主键的 uuid

发布于 2025-01-06 12:06:04 字数 1070 浏览 1 评论 0原文

考虑表：

CREATE TABLE `information` (
    `id` char(36) NOT NULL,
    `data` text NOT NULL,
    PRIMARY KEY (`id`)
)

使用触发器：

CREATE TRIGGER `information_uuid_ins_trg` 
BEFORE INSERT ON information 
FOR EACH ROW
BEGIN
    SET NEW.id = UUID();
END;

我希望能够做的是知道与上一个插入语句一起使用的 id。

INSERT INTO information ( data ) VALUES ('[some data]')

给定一个复制的主-主环境，其中 [some data] 不保证是唯一的。显然 last_insert_id()无法使用，因为该字段不是 auto_increment 字段。使用 LAST_INSERT_ID(UUID( )) 添加临时表的更新将不起作用，因为 LAST_INSERT_ID(expr) 需要一个整数值作为 expr。在 OracleRDBMS 中，我会使用以下方法来实现此目的：

INSERT INTO information 
(data) values ('[some data]') 
RETURNING id INTO var_id

或者使用 Microsoft SQL Server / Postgres，我会使用：

INSERT INTO information 
(data) values ('[some data]') 
OUTPUT INSERTED.*

这都将从运行在一组 Web 服务器上的 PHP 前端调用。

关于如何在 mysql 中返回 uuid pk 有什么想法吗？

原文

Consider the table:

CREATE TABLE `information` (
    `id` char(36) NOT NULL,
    `data` text NOT NULL,
    PRIMARY KEY (`id`)
)

with the trigger:

CREATE TRIGGER `information_uuid_ins_trg` 
BEFORE INSERT ON information 
FOR EACH ROW
BEGIN
    SET NEW.id = UUID();
END;

What I'd like to be able to do, is to know the id that was used with the previous insert statement.

INSERT INTO information ( data ) VALUES ('[some data]')

Given a replicated master-master environment where the [some data] is not guaranteed to be unique. Obviously the last_insert_id() can't be used since the field is not an auto_increment field. Adding an update of a temp table with LAST_INSERT_ID(UUID( )) won't work since LAST_INSERT_ID(expr) expects an integer value as expr. In OracleRDBMS I would approach this with:

INSERT INTO information 
(data) values ('[some data]') 
RETURNING id INTO var_id

or with Microsoft SQL Server / Postgres I would use:

INSERT INTO information 
(data) values ('[some data]') 
OUTPUT INSERTED.*

This would all be called from a PHP frontend running on a collection of webservers.

Any thoughts as to how to return the uuid pk in mysql?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

不念旧人 2025-01-13 12:06:04

由于 MySQL 似乎没有任何内置功能来帮助您，遗憾的是我想不出比这个“hack”更好的东西了；

DELIMITER //

CREATE TRIGGER `information_uuid_retrieve` 
AFTER INSERT ON information 
FOR EACH ROW 
BEGIN 
  SET @INSERT_ID=NEW.id; 
END;//

DELIMITER ;

INSERT INTO information (data) VALUES ('olle');

SELECT @INSERT_ID;
+--------------------------------------+
| @INSERT_ID                           |
+--------------------------------------+
| f42f4044-58d0-11e1-8f90-93738648d450 |
+--------------------------------------+

Since MySQL doesn't seem to have any built in functionality to help you out, I sadly can't think of anything better than this "hack";

DELIMITER //

CREATE TRIGGER `information_uuid_retrieve` 
AFTER INSERT ON information 
FOR EACH ROW 
BEGIN 
  SET @INSERT_ID=NEW.id; 
END;//

DELIMITER ;

INSERT INTO information (data) VALUES ('olle');

SELECT @INSERT_ID;
+--------------------------------------+
| @INSERT_ID                           |
+--------------------------------------+
| f42f4044-58d0-11e1-8f90-93738648d450 |
+--------------------------------------+

回复收藏 0 原文

~没有更多了~