将MySQL auto_increment设置为依赖于其他两个主键 [英] setting MySQL auto_increment to be dependent on two other primary keys

问题描述

我正在尝试建立一个MySQL数据库来存储生物数据.我必须从文件中提取此数据，并且为此有一个perl脚本.我的问题是我需要三个主键才能使它们唯一，并且我希望其中一个是自动递增整数.但是，我希望每次前两个键的组合发生更改时，自动递增的值都将重置.

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

等等

这是可能的还是我必须直接在脚本中实现?

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

谢谢

推荐答案

仅MyISAM表是可能的，并且在InnoDB或MySQL拥有的任何其他存储引擎中均无法使用.

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

只需在(col1，col2，id)上创建一个主键，并在id列上设置auto_increment标志.并确保没有对id的唯一约束. MyISAM将为每对唯一的(col1，col2)对生成一个新的值序列.

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

这篇关于将MySQL auto_increment设置为依赖于其他两个主键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！