如何在MySQL中的两个或多个表中具有唯一ID? [英] How to have Unique IDs across two or more tables in MySQL?

问题描述

我有一个名为events的表，所有新信息都存放在该表中.该表用作所有新闻源查询的参考，因此可以从那里选择事件项，并从正确的表中检索与该事件相对应的信息.

I have a table called events where all new information goes. This table works as a reference for all queries for news feed(s) so event items are selected from there and information corresponding to that event is retrieved from the correct tables.

现在，这是我的问题.我在事件表中有E_ID，它们与另一个表中的事件ID相对应，对于tracks来说是T_ID，对于status来说是S_ID，依此类推...这些ID可以是相同的，所以暂时我只是为每个表使用了不同的auto_increment值，所以status在500 tracks上从0开始，以此类推.显然，我不想这样做，因为我不知道哪个表中将包含最多的数据.我认为status将很快超过tracks.

Now, here's my problem. I have E_ID's in the events table which correspond to the ID of an event in a different table, be it T_ID for tracks, S_ID for status and so on... These ID's could be the same so for the time being I just used a different auto_increment value for each table so status started on 500 tracks on 0 etc. Obviously, I don't want to do that as I have no idea yet of which table is going to have the most data in it. I would assume status would quickly exceed tracks.

该信息通过触发器插入到event表中.这是一个例子.

The information is inserted into the event table with triggers. Here's an example of one;

BEGIN
INSERT INTO events (action, E_ID, ID)
VALUES ('has some news.', NEW.S_ID, NEW.ID);
END

那是他的状态表.

该触发器是否有附加功能，以确保NEW.S_ID！=当前在events中的E_ID，并且是否确实更改了S_ID.

Is there an addition to that trigger I can make to ensure the NEW.S_ID != an E_ID currently in events and if it does change the S_ID accordingly.

或者，当自动递增S_ID时，我可以使用某种键来引用事件，以便使S_ID不会递增到E_ID的值.

Alternatively, is there some kind of key I can use to reference events when auto incrementing the S_ID so that the S_ID is not incremented to a value of E_ID.

这些是我的想法，我认为后一种解决方案会更好，但我怀疑是否可能，但需要另一个参考表，而且过于复杂.

Those are my thoughts, I think the latter solution would be better but I doubt it is possible or it is but would require another reference table and would be too complex.

推荐答案

在表中要求唯一的ID确实很罕见，但是这里有一个解决方案.

It's really uncommon to require a unique id across tables, but here's a solution that will do it.

/* Create a single table to store unique IDs */
CREATE TABLE object_ids (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    object_type ENUM('event', ...) NOT NULL
) ENGINE=InnoDB;

/* Independent object tables do not auto-increment, and have a FK to the object_ids table */
CREATE TABLE events (
    id INT UNSIGNED NOT NULL PRIMARY KEY,
    ...
    CONSTRAINT FOREIGN KEY (id) REFERENCES object_ids (id)
) ENGINE=InnoDB;

/* When creating a new record, first insert your object type into the object_ids table */
INSERT INTO object_ids(object_type) VALUES ('event');
/* Then, get the auto-increment id. */
SET @id = LAST_INSERT_ID();
/* And finally, create your object record. */
INSERT INTO events (id, ...) VALUES (@id, ...);

很显然，您将为其他表复制events表的结构.

Obviously, you would duplicate the structure of the events table for your other tables.

这篇关于如何在MySQL中的两个或多个表中具有唯一ID?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！