Mysql自定义序列生成器(如oracle)

Question

我希望每个表有两个 auto_increment 列，但是 mysql 只允许一个 auto_increment 列。因此，我尝试使用自己的表复制 oracle 序列。

这是架构。

create table logical_id_seq (
    logical_id int auto_increment,
    primary key(logical_id)
);

create table mytable (
    physical_id int auto_increment,
    logical_id int not null references parent(logical_id),
    data varchar(20),
    version_start_date datetime not null,
    version_end_date datetime not null,
    primary key(physical_id),
    foreign key (logical_id) references logical_id_seq(logical_id),
    unique key (logical_id,version_start_date,version_end_date)
);

因此，logical_id_seq 表用作序列生成器。

创建新实体:

1. 将新条目插入 logical_id_seq。
2. 从 logical_id_seq 读取 last_insert_id()。
3. 使用上面的值在表中插入一个新行。

让我为您提供更多有关 logical_id 和 physical_id 的背景信息。我想设计一个时间旅行数据库，这意味着我想要给定任何时间戳(现在或过去)的数据库状态。所以，我有 version_start_date 和 version_end_date。

能否请您告诉我我的序列生成器方法是否有副作用。

Answer 1

根据您的表类型(IIRC，它是 MyISAM 和 BDB)，您可以使用自动增量的巧妙技巧。

create table component_core ( 
    component_id int auto_increment, 
    primary key(component_id) 
); 

create table component_history ( 
    component_id int not null, 
    version_id int auto_increment, 
    data varchar(20), 
    version_start_date datetime not null, 
    version_end_date datetime not null, 
    primary key(component_id,version_id) 
); 

在创建一个全新的组件时插入到 component_core 中，然后在插入到 component_history 中时使用该自动递增的 ID 作为 component_id，version_id 自动递增字段将从 1 向上为每个不同的 component_id 编号。当插入对 component_history 的更改时，使用原始 component_id，但允许 version_id 正常自动递增。在这种情况下，auto_increment 列的生成值计算为 MAX(auto_increment_column) + 1 WHERE component_id=given-component_id。

Insert a new component_core
Retrieve the last autoincremented value from component_core (1)
Insert a new component_history using the component_id from component_core
Insert a new component_history using the same component_id
Insert a new component_history using the same component_id
Insert a new component_core
Retrieve the last autoincremented value from component_core (2)
Insert a new component_history using the component_id from component_core
Insert a new component_history using the same component_id

表格现在将包含

组件核心

component_id
1
2

组件历史

component_id version_id
1            1
1            2
1            3
2            1
2            2

不确定该技术是否有任何帮助，特别是因为它仅限于特定的表类型(我相信它在 MyISAM 中有效，但您失去了 MyISAM 的事务控制)

编辑

引用:http://dev.mysql.com/doc/refman/5.1/en/example-auto-increment.html