如何在MySQL中的两个或多个表中具有唯一ID

如何在MySQL中的两个或多个表中具有唯一ID

本文介绍了如何在MySQL中的两个或多个表中具有唯一ID?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

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

08-13 16:37