非主键上的mysql自动增量 [英] mysql auto-increment on a non-primary key

问题描述

今天另一个问题:)
这次，我想知道是否/如何使每个主键的第二列自动递增:

Another question today :)
This time I'd like to know if/how it's possible to make a second column auto-increment for each primary key:

CREATE TABLE test (
    `id` INTEGER UNSIGNED NOT NULL,
    `subId` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
    `text` VARCHAR(45) NOT NULL,
    PRIMARY KEY (`id`, `subId`)
)
ENGINE = InnoDB;

不幸的是，只有当我将ID指定为主键，将subId指定为索引键时，此创建操作才起作用(但我需要同时使用它们，并且ID可以重复...)
示例数据(我需要):

This creation, unfortunately, doesn't work, only if I specify ID as primary key and subId as index key (but I need them both together and ID can repeat...)
Example data (what I need):

1, 1
1, 2
1, 3
2, 1
2, 2
3, 1

将ID用作主索引和subId索引的问题是subId将独立于ID递增.
如何实现这一目标，甚至有可能吗?

The problem with making ID primary and subId index is that subId will increment independently of ID.
How to achieve this and is it even possible?

推荐答案

我不得不处理类似的问题，即不自然地排序类别树. 如果您要一次插入一个ID的所有行，则可以对每个subId执行以下操作:

I had to deal with a similar problem ordering a category tree unnaturally. If you are inserting all the rows for one id at one time, you could do something like this for each subId:

SET @seq = 0;
INSERT INTO test
  (id,  subId,            text) VALUES
  (_id, @seq := @seq + 1, 'Some text')
;

如果您需要在ID中添加"一行，则可以使用@peq设置

If you need to "add" a row to an id, you can set @seq with

SELECT IFNULL(MAX(subId), 0) INTO @seq FROM test WHERE id = _id;

这当然需要由应用程序而不是mySQL管理id.

This would of course require management of id by the application and not mySQL.

您可以执行与最后一个代码块相同的操作，以获取下一个可用的ID.

You could do that same as the last code block to get the next available id as well.

这篇关于非主键上的mysql自动增量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！