复合主键和Auto_Increment [英] Composite Primary Key and Auto_Increment

问题描述

我正在尝试使用其中一个自动递增来执行复合键，但是当我尝试输入新行时，只需继续执行顺序即可.

I'm trying to do the composite key with one of them auto incrementing, but when I try to enter a new row it just continue the sequential.

这是发生的情况的示例:

Here's the example of what happens:

Item_1 | Item_2
1     |   1    
1     |   2
2     |   3
2     |   4
2     |   5

这是我想要的例子:

Item_1 | Item_2
1     |   1    
1     |   2
2     |   1
2     |   2
2     |   3

我以这种方式创建表:

CREATE TABLE IF NOT EXISTS `usuarios` (
  `cod_user` int(11) NOT NULL AUTO_INCREMENT,
  `cod_user_emp` int(11) NOT NULL,
  PRIMARY KEY (`cod_user`,`cod_user_emp`),
  UNIQUE KEY `user` (`user`),
  KEY `cod_user` (`cod_user`)
  );

编辑

我通过服务器端php验证解决了该问题.

Edit

I resolved the problem doing a server sided php validation.

$result = $db->query("SELECT * FROM usuarios WHERE cod_user_emp=\"$emp\" ORDER BY cod_user DESC LIMIT 1");
while($row=$result->fetch_array()){
   $cod2 = $row['cod_user']+1;
}

推荐答案

删除该AUTO_INCREMENT列，

CREATE TABLE IF NOT EXISTS `usuarios` 
(
  `cod_user` int(11) NOT NULL,
  `cod_user_emp` int(11) NOT NULL,
  PRIMARY KEY (`cod_user`,`cod_user_emp`) -- <<== this is enough
);

并且可以创建一个Stored Procedure，该Stored Procedure为每个Item_1递增Item_2.

And can create a Stored Procedure that increments Item_2 for every Item_1.

DELIMITER $$
CREATE PROCEDURE InsertRecord(IN ItemA INT)
BEGIN
    SET @max_id =  (
                    SELECT COALESCE(MAX(Item_2), 0) + 1
                    FROM TableName
                    WHERE   Item_1 = ItemA
                    );
    INSERT INTO tableName(Item_1, Item_2)
    VALUES(ItemA, @max_id)
END $$
DELIMITER ;

并这样称呼它，

CALL InsertRecord(2);

这篇关于复合主键和Auto_Increment的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！