并发安全的mysql序列实现

以你之姓@ 2024-02-17 22:20 7阅读 0赞

\--仿照序列表  
drop table if exists sequence;  
create table sequence (  
seq\_name VARCHAR(50) NOT NULL,  
current\_val INT NOT NULL,  
increment\_val INT NOT NULL DEFAULT 1,  
PRIMARY KEY (seq\_name)  
);  
\-- 增加序列  
INSERT INTO sequence(seq\_name,current\_val,increment\_val) VALUES ('SEQ\_R\_USER',1,1);  
INSERT INTO sequence(seq\_name,current\_val,increment\_val) VALUES ('SEQ\_R\_ROLE',1,1);  
INSERT INTO sequence(seq\_name,current\_val,increment\_val) VALUES ('SEQ\_TB\_GARAGE',1,1);  
INSERT INTO sequence(seq\_name,current\_val,increment\_val) VALUES ('SEQ\_TB\_CUSTOM',1,1);  
INSERT INTO sequence(seq\_name,current\_val,increment\_val) VALUES ('SEQ\_TB\_FIX',1,1);  
INSERT INTO sequence(seq\_name,current\_val,increment\_val) VALUES ('SEQ\_TB\_PARTS',1,1);  
  
\-- 当前序列值  
DROP FUNCTION IF EXISTS \`currval\`;  
create function currval(v\_seq\_name VARCHAR(50))  
returns integer  
begin  
declare value integer;  
set value = 0;  
select current\_val into value  
from sequence  
where seq\_name = v\_seq\_name;  
return value;  
end;  
  
  
\-- 下一个序列值 nextval 需要commit之后才会更新到表中。  
DROP FUNCTION IF EXISTS \`nextval\`;  
create function nextval (v\_seq\_name VARCHAR(50))  
returns integer  
begin  
DECLARE t\_error INTEGER DEFAULT 0;  
DECLARE EXIT HANDLER FOR SQLEXCEPTION return -1;  
update sequence  
set current\_val = current\_val + increment\_val  
where seq\_name = v\_seq\_name;  
return currval(v\_seq\_name);  
end;  
/\*  
\-- 设值 setval  
DROP FUNCTION IF EXISTS \`setval\`;  
create function setval(v\_seq\_name VARCHAR(50), v\_new\_val INTEGER)  
returns integer  
begin  
update sequence  
set current\_val = v\_new\_val  
where seq\_name = v\_seq\_name;  
return currval(v\_seq\_name);  
end;  
\*/  
\-- 测试  
\-- select setval('SEQ\_R\_USER',0);  
select currval('SEQ\_R\_USER');  
select nextval('SEQ\_R\_USER');  
  
  
\-- 事物 并发 关闭自动commit后用两个窗口测试  
select nextval('SEQ\_R\_USER');  
select nextval('SEQ\_R\_USER');  
  
\-- show function status;  
  
  
\-- 获取序列值的存储过程 p\_nextval  
DROP PROCEDURE IF EXISTS p\_nextval;  
CREATE PROCEDURE p\_nextval(IN v\_seq\_name VARCHAR(50),OUT o\_retsult VARCHAR(50))  
BEGIN  
DECLARE EXIT HANDLER FOR SQLEXCEPTION  
BEGIN  
ROLLBACK;  
SET o\_retsult=-1;  
END;  
START TRANSACTION;  
update sequence  
set current\_val = current\_val + increment\_val  
where seq\_name = v\_seq\_name;  
set o\_retsult= currval(v\_seq\_name);  
COMMIT;  
END;  
  
  
  
\-- -- END 仿照序列表  
\----------------------------------  
\-- 使用  
select \* from sequence;  
call p\_nextval('SEQ\_R\_USER',@o\_result);  
select @o\_result  
  
\-------------------------------------------------------

\-- java jdbc调用  
\-------------------------------------------------------

java jdbc这么调用；  
  
import java.sql.CallableStatement;  
import java.sql.Connection;  
import java.sql.DriverManager;  
  
public class TestMysqlProcedure \{  
public static void main(String\[\] args) throws Exception \{  
Connection cnn = null;  
try \{  
Class.forName("com.mysql.jdbc.Driver");  
cnn = DriverManager.getConnection(  
"jdbc:mysql://localhost:3306/xx", "root",  
"passswordxxxx");  
int status = -1;  
CallableStatement cs = cnn.prepareCall("\{ call p\_nextval (?,?) \}");  
int pos = 0;  
cs.setString(++pos, "nnid");  
cs.registerOutParameter(++pos, java.sql.Types.INTEGER);  
cs.execute();  
status = cs.getInt(2);  
System.out.println(status);  
\} catch (Exception e) \{  
e.printStackTrace();  
\} finally \{  
if (cnn != null) \{  
cnn.close();  
\}  
\}  
\}  
\}

\-------------------------------------------------------------

ibatis这么用：

/\*\*  
\* 获取序列  
\* @param seq  
\* @return  
\*/  
public String getSequenceByProcedure(String seq)  
\{  
try  
\{  
HashMap<String, Object> paramMap = new HashMap<String, Object>();  
paramMap.put("v\_seq\_name", seq);  
paramMap.put("o\_ret", "");  
updateObject("getNextval", paramMap);  
return String.valueOf(paramMap.get("o\_retsult"));  
\}  
catch (Exception e)  
\{  
System.out.println(e.getMessage());  
\}  
return -1l;  
\}

xml中配置：

<parameterMap class="java.util.Map" id="seqNameParameters">  
<parameter property="v\_seq\_name" javaType="java.lang.String" jdbcType="VARCHAR" mode="IN" />  
<parameter property="o\_retsult" javaType="java.lang.String" jdbcType="VARCHAR" mode="OUT" />  
</parameterMap>  
<procedure id="getNextval" parameterMap="seqNameParameters">  
\{call p\_nextval(?, ?)\}  
</procedure>