mysql - 如何创建复合自动增量列，其中之一是分组方式

Question

父表-sectionId是主键

|    sectionId     |     Name   |
|       1          |  Section1  |  
|       2          |  Section2  |  
|       3          |  Section3  | 

子表 - 复合键(sectionId 和 valueId)

|   sectionId    |     valueId    |      LectureValue        |
|    1           |      1         |        somevalue1        |  
|    1           |      2         |        somevalue2        |  
|    2           |      1         |        somevalue3        |  
|    2           |      2         |        somevalue4        |  
|    1           |      3         |        somevalue5        |  
|    2           |      3         |        somevalue6        |  
|    3           |      1         |        somevalue7        |  
|    3           |      2         |        somevalue8        | 

我希望根据sectionId填充valueId，例如

1-1
1-2
1-3  

如果sectionId 2出现则

2-1
2-2

如果我第一次插入任何sectionId，相应的valueID应该为1，然后序列应该继续

有什么可能的方法可以在 MYSQL 本身中做到这一点吗？

Answer 1

不要存储像 2-1 这样的值。相反，当您需要这样的时候，请执行

SELECT CONCAT(sectionId, '-', valueId) AS my_fancy_valueId FROM table...

然后您可以通过使用 MyISAM 存储引擎的表来实现这一点。需要注意的是，它(尚)不支持外键或事务。

来自manual :

For MyISAM tables, you can specify AUTO_INCREMENT on a secondary column in a multiple-column index. In this case, the generated value for the AUTO_INCREMENT column is calculated as MAX(auto_increment_column) + 1 WHERE prefix=given-prefix. This is useful when you want to put data into ordered groups.

CREATE TABLE animals (
    grp ENUM('fish','mammal','bird') NOT NULL,
    id MEDIUMINT NOT NULL AUTO_INCREMENT,
    name CHAR(30) NOT NULL,
    PRIMARY KEY (grp,id)
) ENGINE=MyISAM;

INSERT INTO animals (grp,name) VALUES
    ('mammal','dog'),('mammal','cat'),
    ('bird','penguin'),('fish','lax'),('mammal','whale'),
    ('bird','ostrich');

SELECT * FROM animals ORDER BY grp,id;

Which returns:

+--------+----+---------+
| grp    | id | name    |
+--------+----+---------+
| fish   |  1 | lax     |
| mammal |  1 | dog     |
| mammal |  2 | cat     |
| mammal |  3 | whale   |
| bird   |  1 | penguin |
| bird   |  2 | ostrich |
+--------+----+---------+

In this case (when the AUTO_INCREMENT column is part of a multiple-column index), AUTO_INCREMENT values are reused if you delete the row with the biggest AUTO_INCREMENT value in any group. This happens even for MyISAM tables, for which AUTO_INCREMENT values normally are not reused.