手动插mysql时id,MySQL的手动增量ID？

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:

Have a table with only ids (primary key, auto-increment) and do joins to a table that has all the item information.

Advantages:

easy to understand

hard for someone that comes after me to get confused

Disadvantages:

requires more debugging and coding since this system is already in use

seems weird to have a table with a single field

Have a single table using a sub-select to get the MAX value (+1) to apply to new items.

Advantages:

single table

only minor code adjustments (but not all that different, maybe)

Disadvantages:

prone to errors (manual increment, can't allow deletions or the MAX value might be off)

Thanks in advance!

解决方案

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;

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;

So now you have a concurrency-safe method to create new id's.

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 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.

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

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 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.