mysql 行自动增量为23,非主键上的mysql自动增量

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;

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

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?

解决方案

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

;

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;

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

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