mysql 主键依赖,将MySQL auto_increment设置为依赖于其他两个主键

i'm trying to set up a MySQL database for storing biological data. I have to extract this data from a file and i have a perl script for that. The problem i have is that i need three primary keys in order for them to be unique, and i want one of them to be an auto increment integer. I would like, however, the auto-incremented value to reset each time the combination of the first two keys changes.

sequence1 | hit1 | 1

sequence1 | hit1 | 2

sequence1 | hit2 | 1

sequence2 | something | 1

sequence2 | something | 2

sequence2 | something | 3

sequence3 | something | 1

etc. etc.

is that possible or do i have to implement that directly into the script?

thank you

解决方案

It is possible with MyISAM tables only and will not work in InnoDB or any other storage engine MySQL has.

Just create a primary key on (col1, col2, id) and set auto_increment flag on id column. And make sure there is no unique constraint on id alone. MyISAM will generate a new sequence of values per each unique pair of (col1, col2).