mysql - 带手动增量的组合键

在多 session /事务环境中，我如何安全地在包含主复合键和(手动)增量键的表中插入一行。

以及如何获取column_c的最新增量值，LAST_INSERT_ID()不返回所需的值。

我已经研究了SELECT FOR UPDATE ... INSERT和INSERT INTO SELECT，但无法决定要使用哪个。

从事务安全性(锁定)，隔离级别和性能角度来看，实现此目标的最佳方法是什么。

更新-关于此问题的另一种看法

假设两个事务/ session 尝试同时插入相同的column_a，column_b对(示例1,1)。我如何能;

按顺序执行插入查询。第一个插入(事务1)应产生复合键1,1， 1 ，第二个插入(事务2)1,1， 2 。我需要某种锁定机制

检索插入的column_c值。我可能需要利用变量？

表定义

CREATE TABLE `table` (
        `column_a` int(11) unsigned NOT NULL,
        `column_b` int(11) unsigned NOT NULL,
        `column_c` int(11) unsigned NOT NULL,
        PRIMARY KEY (column_a, column_b, column_c)
 ) ENGINE=InnoDB;

示例数据

+----------+----------+----------+
| column_a | column_b | column_c |
+----------+----------+----------+
|        1 |        1 |        1 |
|        1 |        1 |        2 |
|        1 |        1 |        3 |
|        2 |        1 |        1 |
|        2 |        1 |        2 |
|        2 |        1 |        3 |
+----------+----------+----------+

在选择查询中插入

INSERT INTO `table` (`column_a`, `column_b`, `column_c`)
SELECT 2,1, IFNULL(MAX(`column_c`), 0) + 1 FROM `table`
WHERE `column_a` = 2 and `column_b` = 1;

最佳答案

您可以为此使用存储过程:

我从未遇到过此类问题，如果我这样做，我将按照以下步骤进行操作:

CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_insert_when_duplicate`(val1 int, val2 int, val3 int)
BEGIN

     -- catch duplicate insert error
     DECLARE EXIT HANDLER FOR 1062
     BEGIN
        -- we could recursively try to insert the same val1 and val2 but increasing val3 by 1
        call sp_insert_when_duplicate(val1,val2,val3+1);
     END;

     -- by default mysql recursive limit is 0, you could set as 10 or 100 as per your wish
    SET max_sp_recursion_depth=10;

     -- [Trying] to insert the values, if no duplicate this should continue and end the script.. if duplicate, above handler should catch and try to insert again with 1+ value for val3
    INSERT INTO `table` (`column_a`, `column_b`, `column_c`) values (val1,val2,val3);


END

用法是:

call sp_insert_when_duplicate(1,1,1);
call sp_insert_when_duplicate(1,1,1);
call sp_insert_when_duplicate(1,1,1);
call sp_insert_when_duplicate(2,1,1);
call sp_insert_when_duplicate(2,1,1);
call sp_insert_when_duplicate(2,2,1);
select * from `table`;

结果:

+----------+----------+----------+
| column_a | column_b | column_c |
+----------+----------+----------+
|        1 |        1 |        1 |
|        1 |        1 |        2 |
|        1 |        1 |        3 |
|        2 |        1 |        1 |
|        2 |        1 |        2 |
|        2 |        2 |        1 |
+----------+----------+----------+

在交易中也是如此:

start transaction;
call sp_insert_when_duplicate(1,1,1);
call sp_insert_when_duplicate(1,1,1);
call sp_insert_when_duplicate(1,1,1);
call sp_insert_when_duplicate(2,1,1);
call sp_insert_when_duplicate(2,1,1);
call sp_insert_when_duplicate(2,2,1);
commit;

select * from `table`;


+----------+----------+----------+
| column_a | column_b | column_c |
+----------+----------+----------+
|        1 |        1 |        1 |
|        1 |        1 |        2 |
|        1 |        1 |        3 |
|        2 |        1 |        1 |
|        2 |        1 |        2 |
|        2 |        2 |        1 |
+----------+----------+----------+

但是我还没有尝试过并行事务!

关于mysql - 带手动增量的组合键，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/40062440/