如何在没有触发器的情况下在mysql上生成/自动递增guid? [英] How to generate/autoincrement guid on insert without triggers and manual inserts in mysql?

问题描述

我正在重新访问数据库，并发现我有一些类型为INT的主键.

Im revisiting my database and noticed I had some primary keys that were of type INT.

这还不够独特，所以我想我会有个向导.我来自Microsoft sql背景，在ssms中可以选择类型为"uniqeidentifier"并自动递增.

This wasn't unique enough so I thought I would have a guid. I come from a microsoft sql background and in the ssms you can choose type to "uniqeidentifier" and auto increment it.

但是在mysql中，我发现必须为要插入的表创建在插入时执行的触发器为其生成指南ID.示例:

In mysql however Ive found that you have to make triggers that execute on insert for the tables you want to generate a guide id for. Example:

表格:

CREATE TABLE `tbl_test` (
  `GUID` char(40) NOT NULL,
  `Name` varchar(50) NOT NULL,
  PRIMARY KEY (`GUID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

触发:

CREATE TRIGGER `t_GUID` BEFORE INSERT ON `tbl_test`
 FOR EACH ROW begin
 SET new.GUID = uuid();

或者，您必须将GUID自己插入后端.

Alternatively you have to insert the guid yourself in the backend.

我不是数据库专家，但仍然记得触发器会导致性能问题.

Im no DB expert but still remember that triggers cause performance problems.

上面是我在此处找到的东西，今年9岁，所以我希望有一些东西变了吗?

The above is something I found here and is 9 years old so I was hoping something has changed?

推荐答案

据

As far as stated in the documentation, you can use uid() as a column default starting version 8.0.13, so something like this should work:

create table tbl_test (
    guid binary(16) default (uuid_to_bin(uuid())) not null primary key,
    name varchar(50) not null
);

这几乎是从文档中复制的.我手头的MySQL版本不足以对此进行测试.

This is pretty much copied from the documentation. I don't have a recent enough version of MySQL at hand to test this.

这篇关于如何在没有触发器的情况下在mysql上生成/自动递增guid?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！