oracle Mysql 自定义序列生成器（如oracle）

Mysql custom sequence generator ( like oracle)

提问by Boolean

I want to have two auto_increment column's per table, but mysql allows only one auto_increment columns. So, I tried replicating the oracle sequence using my own table.

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

Here is the schema.

这是架构。

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)
);

So, the logical_id_seq table is used as a sequence generator.

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

creating new entity:

创建新实体：

1. Insert new entry into logical_id_seq.
2. Read the last_insert_id() from logical_id_seq.
3. Use the above value to insert a new row in the table.

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

Let me give you little more context on logical_id and physical_id. I want to design a time travel database, meaning I want the database state given any timestamp(now or past). So, I am having version_start_date and version_end_date.

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

Can you please tell me if there are side effects to my method of sequence generator.

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

回答by Mark Baker

Depending on your table type (IIRC it's MyISAM and BDB), there's a clever trick you can use with autoincrement.

根据您的表类型（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) 
); 

Insert into component_core when creating a brand new component, then use that autoincremented ID as the component_id when inserting into component_history and the version_id autoincrement field there will number from 1 upwards for each different component_id. When inserting a change to a component_history, use the original component_id, but allow the version_id to autoincrement normally. In this case, the generated value for the auto_increment column is calculated as MAX(auto_increment_column) + 1 WHERE component_id=given-component_id.

在创建一个全新的组件时插入到 component_core 中，然后在插入到 component_history 时使用该自动增加的 ID 作为 component_id，并且 version_id 自动增加字段对于每个不同的 component_id 都会从 1 向上编号。插入对 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

Tables will now contain

表格现在将包含

component_core

组件核心

component_id
1
2

component_history

组件历史

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

Not sure if the technique is of any help, especially as it is restricted to specific table types (I believe it works in MyISAM, but you lose out on transactional control with MyISAM)

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

EDIT

编辑

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

参考：http: //dev.mysql.com/doc/refman/5.1/en/example-auto-increment.html

回答by Pascal Thivent

So, I want to keep track of modifications to the rows (...)

所以，我想跟踪对行的修改 (...)

I suggest to have a look at Hibernate Enversbefore to implement your own solution:

我建议在实施您自己的解决方案之前先看看Hibernate Envers：

The Envers project aims to enable easy auditing/versioningof persistent classes. All that you have to do is annotate your persistent class or some of its properties, that you want to audit, with @Audited. For each audited entity, a table will be created, which will hold the history of changes made to the entity. You can then retrieve and query historical data without much effort.

Envers 项目旨在实现对持久类的轻松审计/版本控制。您所要做的就是使用@Audited. 对于每个被审计的实体，将创建一个表，该表将保存对实体所做更改的历史记录。然后，您可以毫不费力地检索和查询历史数据。