问题描述
我有一个名为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?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！