在多个数据表上生成唯一的 AI ID [英] Generate Unique AI ID over multiple data tables

问题描述

我正在设计一个数据库，但此时我遇到了困难，

I am designing a database but I come to a struggle at this moment,

目前我有 3 张桌子:

Currently I have 3 tables:

1. ex_artists
2. ex_tracks
3. ex_labels

现在我希望在这 3 个(或更多)表中有一个唯一 ID

Now I wish to have a Unique ID throughout these 3(or more) tables

因此，如果示例艺术家"的 ID 为1"，则示例曲目"的 ID 不能也为1"但应该得到 ID '2'，因为 '1' 已经存在

So if 'Example Artist' had ID '1', then 'Example Track' can not also have ID '1' but should get ID '2' since '1' already exists

推荐答案

我理解你的顾虑.一旦您决定使用技术 ID 来设计您的数据库，总是存在混淆 ID 的危险.虽然

I understand your concerns. Once you decide to design your database with technical IDs, there is always the danger of confusing IDs. While

insert into album_track (album, artist, track, no) 
  values ('B0016991-00', 'JBIEBER', 'BOYFRIEND0001', 2);

代替

insert into album_track (album, artist, track, no) 
  values ('B0016991-00', 'BOYFRIEND0001', 'JBIEBER', 2);

可能会出错，

insert into album_track (album_id, artist_id, track_id, no) values (40, 22, 12, 2);

代替

insert into album_track (album_id, artist_id, track_id, no) values (40, 12, 22, 2);

可能不会，而且当您注意到程序错误时，再分辨坏记录和好的记录可能为时已晚.您的数据在技术上是一致的，但实际上是一团糟.

would probably not, and the time you notice your program error it may be too late to tell the bad records from the good ones. Your data would be technically consistent, but a mess really.

要解决这个问题，您需要一个来源来提取您的 ID.例如，在 Oracle 中，您将使用序列.在 MySQL 中，您可以为此创建一个 ID 表:

To overcome this problem, you need one source to pull your IDs from. In Oracle for instance you would use a sequence. In MySQL you can create an ID table for this only purpose:

create table ids(id int auto_increment primary key);
create table album(id int primary key, album_no text, album_name text,
  foreign key (id) references ids(id));
create table track(id int primary key, track_name text, record_date date, take int, 
  foreign key (id) references ids(id));
insert into ids values ();
insert into album (id, album_no, album_name) values
  ((select last_insert_id), 'B0016991-00', 'Justin Bieber – Believe - Deluxe Edition');

因此，每当您在其中一个表中插入一条记录时，您都必须指定一个 ID(因为它不会自动获得).您通过插入 ID 表获得 ID，然后调用 MySQL 的 LAST_INSERT_ID().

So whenever you insert a record in one of your tables, you must specify an ID (because it is not automatically got). You get the ID with an insert into your IDs table and then call MySQL's LAST_INSERT_ID().

一个不太安全但更简单的替代方法是以不同的偏移量开始 ID:

An less safe, but simpler alternative would be to start the IDs at different offsets:

create table album(id int auto_increment primary key, album_no text, album_name text);
create table track(id int auto_increment primary key, track_name text, record_date date);
alter table track auto_increment=10000001;
create table artist(id int auto_increment primary key, artist_name varchar(100));
alter table artist auto_increment=20000001;
insert into artist (artist_name) values ('Justin Bieber');

只要您的 ID 保持在所需的范围内，这就会起作用.

This works as long as your IDs stay in the desired range.

这篇关于在多个数据表上生成唯一的 AI ID的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！