在 MySQL 中定义具有自动增量的复合键
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18120088/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Defining Composite Key with Auto Increment in MySQL
提问by Nirav Zaveri
Scenario:
设想：
I have a table which references two foreign keys, and for each unique combination of these foreign keys, has its own auto_increment column. I need to implement a Composite Key that will help identify the row as unique using combination of these three (one foreign keys and one auto_increment column, and one other column with non-unique values)
我有一个引用两个外键的表，对于这些外键的每个唯一组合，都有自己的 auto_increment 列。我需要实现一个复合键，它将帮助使用这三者的组合（一个外键和一个 auto_increment 列，以及另一个具有非唯一值的列）将行标识为唯一的
Table:
桌子：
CREATE TABLE `issue_log` (
`sr_no` INT NOT NULL AUTO_INCREMENT ,
`app_id` INT NOT NULL ,
`test_id` INT NOT NULL ,
`issue_name` VARCHAR(255) NOT NULL ,
primary key (app_id, test_id,sr_no)
);
Of course, there has to be something wrong with my query, because of which the error thrown is:
当然，我的查询肯定有问题，因此抛出的错误是：
ERROR 1075: Incorrect table definition; there can be only one auto column and it must be defined as a key
ERROR 1075：表定义不正确；只能有一个自动列，并且必须将其定义为键
What I am trying to achieve:
我正在努力实现的目标：
I have an Application Table (with app_id as its primary key), each Application has a set of Issues to be resolved, and each Application has multiple number of tests (so the test_id col) The sr_no col should increment for unique app_id and test_id.
我有一个应用程序表（以 app_id 作为其主键），每个应用程序都有一组要解决的问题，每个应用程序都有多个测试（因此 test_id col） sr_no col 应该为唯一的 app_id 和 test_id 递增。
i.e. The data in table should look like:
即表中的数据应如下所示：
The database engine is InnoDB. I want to achieve this with as much simplicity as possible (i.e. avoid triggers/procedures if possible - which was suggested for similar cases on other Questions).
数据库引擎是 InnoDB。我想以尽可能简单的方式实现这一点（即，如果可能，避免触发/程序 - 建议用于其他问题的类似案例）。
回答by noz
You can't have MySQL do this for you automatically for InnoDB tables - you would need to use a trigger or procedure, or user another DB engine such as MyISAM. Auto incrementing can only be done for a single primary key.
对于 InnoDB 表，您不能让 MySQL 自动为您执行此操作 - 您需要使用触发器或过程，或者使用另一个数据库引擎，例如 MyISAM。只能对单个主键进行自动递增。
Something like the following should work
像下面这样的东西应该工作
DELIMITER $$
CREATE TRIGGER xxx BEFORE INSERT ON issue_log
FOR EACH ROW BEGIN
SET NEW.sr_no = (
SELECT IFNULL(MAX(sr_no), 0) + 1
FROM issue_log
WHERE app_id = NEW.app_id
AND test_id = NEW.test_id
);
END $$
DELIMITER ;
回答by Goran
You can do this with myISAM and BDB engines. InnoDB does not support this. Quote from MySQL 5.0 Reference Manual.
您可以使用 myISAM 和 BDB 引擎来做到这一点。InnoDB 不支持这一点。引自 MySQL 5.0 参考手册。
For MyISAM and BDB 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.
对于 MyISAM 和 BDB 表，您可以在多列索引中的辅助列上指定 AUTO_INCREMENT。在这种情况下，AUTO_INCREMENT 列的生成值计算为 MAX(auto_increment_column) + 1 WHERE prefix=given-prefix。
http://dev.mysql.com/doc/refman/5.0/en/example-auto-increment.html
http://dev.mysql.com/doc/refman/5.0/en/example-auto-increment.html
回答by Rolf NB
I don't fully understand your increment requirement on the test_idcolumn, but if you want an ~autoincrement sequence that restarts on every unique combination of (app_id, test_id), you can do an INSERT ... SELECT FROM the same table, like so:
我不完全理解您对test_id列的增量要求，但是如果您想要一个 ~autoincrement 序列在 ( app_id, test_id) 的每个唯一组合上重新启动，您可以执行 INSERT ... SELECT FROM 同一个表，如下所示：
mysql> INSERT INTO `issue_log` (`sr_no`, `app_id`, `test_id`, `issue_name`) SELECT
IFNULL(MAX(`sr_no`), 0) + 1 /* next sequence number */,
3 /* desired app_id */,
1 /* desired test_id */,
'Name of new row'
FROM `issue_log` /* specify the table name as well */
WHERE `app_id` = 3 AND `test_id` = 1 /* same values as in inserted columns */
This assumes a table definition with no declared AUTO_INCREMENT column. You're essentially emulating autoincrement behavior with the IFNULL(MAX()) + 1 clause, but the manual emulation works on arbitrary columns, unlike the built-in autoincrement.
这假定表定义没有声明的 AUTO_INCREMENT 列。您实际上是在使用 IFNULL(MAX()) + 1 子句模拟自动增量行为，但与内置自动增量不同，手动模拟适用于任意列。
Note that the INSERT ... SELECT being a single query ensures atomicity of the operation. InnoDB will gap-lock the appropriate index, and many concurrent processes can execute this kind of query while still producing non-conflicting sequences.
请注意， INSERT ... SELECT 作为单个查询可确保操作的原子性。InnoDB 将间隙锁定适当的索引，许多并发进程可以执行这种查询，同时仍然产生不冲突的序列。
回答by david strachan
You can use a uniquecomposite key for sr_no,app_id& test_id. You cannot use incremental in sr_noas this is not unique.
您可以为, &使用唯一的复合键。您不能使用增量输入，因为这不是唯一的。sr_noapp_idtest_idsr_no
CREATE TABLE IF NOT EXISTS `issue_log` (
`sr_no` int(11) NOT NULL,
`app_id` int(11) NOT NULL,
`test_id` int(11) NOT NULL,
`issue_name` varchar(255) NOT NULL,
UNIQUE KEY `app_id` (`app_id`,`test_id`,`sr_no`)
) ENGINE=InnoDB ;
I have commented out unique constraint violation in sql fiddleto demonstrate (remove # in line 22 of schema and rebuild schema )
我已经在sql fiddle 中注释掉了唯一的约束冲突以进行演示（删除第 22 行中的模式并重建模式）
