MySQL的手动增量ID? [英] mysql manual increment ids?
问题描述
我有一个表，里面有项目(ID，名称等)，我希望某种数据库方案能够拥有该项目的多个副本，同时仍然能够增加ID.将存在一个名为startdate或date_from_which_this_entry_should_be_used的字段.
I have a table with items in it (id, name, etc) and I want some kind of database scheme to be able to have multiple copies of the item while still being able to increment the ids. There will be a field called startdate or date_from_which_this_entry_should_be_used.
我想到了两种实现方法:
I've thought of two ways of implementing this:
-
只有一个具有ID(主键，自动递增)的表，并且要联接到具有所有项目信息的表.
Have a table with only ids (primary key, auto-increment) and do joins to a table that has all the item information.
优势:
- 容易理解
- 为跟在我后面的人感到困惑
缺点:
- 由于该系统已在使用中，因此需要更多的调试和编码
- 似乎很奇怪有一个带有单个字段的表
通过子选择具有一个表，以将MAX值(+1)应用于新项目.
Have a single table using a sub-select to get the MAX value (+1) to apply to new items.
优势:
- 单张桌子
- 仅进行少量代码调整(但可能并没有那么大的不同)
缺点:
- 容易出错(手动递增，不允许删除，或者MAX值可能关闭)
提前谢谢！
推荐答案
您应该创建一个名为item_ids的表或生成ID值的表.没关系，它只有一列.
You should create a table called item_ids or something to generate id values. It's okay that this has only a single column.
CREATE TABLE item_ids (
item_id INT AUTO_INCREMENT PRIMARY KEY
) ENGINE=InnoDB;
您甚至不需要向其提交任何数据.您只需使用它来生成ID值:
You don't even need to commit any data to it. You just use it to generate id values:
START TRANSACTION;
INSERT INTO item_ids DEFAULT VALUES;
SET @id = LAST_INSERT_ID();
ROLLBACK;
因此，现在您有了一种并发安全的方法来创建新的ID.
So now you have a concurrency-safe method to create new id's.
然后为items表创建一个复合主键.您必须为此使用MyISAM.
Then you make a compound primary key for your items table. You must use MyISAM for this.
CREATE TABLE items (
item_id INT,
seq_id INT AUTO_INCREMENT,
name VARCHAR(20),
etc VARCHAR(20),
PRIMARY KEY (item_id, seq_id)
) ENGINE=MyISAM;
MyISAM在复合主键中支持自动递增列，该列将从每个新的item_id开始于值1.*它也使用MAX(item_id)+1，因此如果删除最后一个，则其值将被重新分配.这与AUTO_INCREMENT的其他用法不同，在AUTO_INCREMENT中，已删除的值不会重复使用.
MyISAM supports an auto-increment column in a compound primary key, which will start over at value 1 for each new item_id.* It also uses MAX(item_id)+1 so if you delete the last one, its value will be reallocated. This is unlike other use of AUTO_INCREMENT where a deleted value is not re-used.
无论是插入新项目还是插入现有项目的新副本，都可以使用类似的INSERT:
Whether you insert a new item, or whether you insert a new copy of an existing item, you use a similar INSERT:
INSERT INTO items (item_id, name, etc) VALUES (@id, 'Stephane', 'etc');
@id参数是现有项目的值，或者是您从item_ids表中获得的自动生成的值.
The @id parameter is either a value of an existing item, or else the auto-generated value you got from the item_ids table.
* InnoDB仅将自动增量作为主键或唯一键的第一列支持，并且不会从另一列的每个不同值的计数开始.
* InnoDB supports auto-increment only as the first column of a primary or unique key, and it does not start over the count for each distinct value of the other column.
这篇关于MySQL的手动增量ID?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
