mysql - 带手动增量的复合键

Question

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

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

我查看了 SELECT FOR UPDATE ... INSERT 和 INSERT INTO SELECT 但无法决定使用哪个。

在事务安全(锁)、隔离级别和性能方面实现此目标的最佳方法是什么。

更新 - 对问题的另一种看法

---

假设两个事务/ session 尝试同时插入相同的 column_a、column_b 对(示例 1,1)。我该怎么做；

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

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;

Answer 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 |
+----------+----------+----------+

不过我还没有尝试过并行交易!