本文共 1360 字,大约阅读时间需要 4 分钟。
由于在MySQL中没有类似Oracle的序列的概念,因此决定通过一个表和两个存储过程来模拟这个功能。
我希望来自不同服务器的连接调用时,会有同步功能。
Create Table tbSequence(Id Int Unsigned Not Null Auto_Increment Primary Key, SequenceName Varchar(50) Not Null, SequenceValue Bigint Not Null);
Delimiter ;
Drop Function If Exists `fnNextSequence`;
Delimiter ;;
Create Function `fnNextSequence`(
p_SequenceName Varchar(50)
) Returns Bigint
Begin
Declare v_Result Bigint Default Null;
-- Lock Tables tbSequence Write;
-- Set Autocommit = 0;
-- Start Transaction;
Select SequenceValue Into v_Result From tbSequence Where SequenceName = p_SequenceName;
If v_Result Is Not Null Then
Set v_Result = v_Result + 1;
Update tbSequence Set SequenceValue = v_Result Where SequenceName = p_SequenceName;
Else
Set v_Result = 1;
Insert Into tbSequence(SequenceName, SequenceValue) Values(p_SequenceName, v_Result);
End If;
-- Unlock Tables tbSequence;
-- Commit;
Return v_Result;
End;;
Delimiter ;
Drop Function If Exists `fnCurrentSequence`;
Delimiter ;;
Create Function `fnCurrentSequence` (
p_SequenceName Varchar(50)
) Returns Bigint
Begin
Declare v_Result Bigint Default Null;
-- Lock Tables tbSequence Write;
-- Set AutoCommit = 0;
-- Start Transaction;
Select SequenceValue Into v_Result From tbSequence Where SequenceName = p_SequenceName;
-- Unlock Tables tbSequence;
-- Commit;
Return v_Result;
End;;
但是MySQL提示在存储过程中不允许锁表,也不允许启动事务。
该如何解决啊?
转载地址:https://blog.csdn.net/weixin_33865450/article/details/113219787 如侵犯您的版权,请留言回复原文章的地址,我们会给您删除此文章,给您带来不便请您谅解!