sequence配置
非数据库
优缺点
- MyCat重启之后sequence会恢复到初始值
- 本地加载，读取速度快
配置sequnceHandlerType
server.xml 配置
0
配置 sequence_conf.properties
GLOBAL.HISIDS=GLOBAL.MINID=10001GLOBAL.MAXID=20000GLOBAL.CURID=10000// MINID 表示最小 ID 值;// MAXID 表示最多 ID 值;// CURID 表示弼前 ID 值;// HISIDS表示使用过的历史分段，一般无特殊需要可不配置
数据库
配置sequnceHandlerType
server.xml 配置
1
创建相关的表和function
//-------创建表CREATE TABLE MYCAT_SEQUENCE ( NAME VARCHAR (50) NOT NULL, current_value INT NOT NULL, increment INT NOT NULL DEFAULT 1, PRIMARY KEY (NAME)) ENGINE = INNODB;INSERT INTO MYCAT_SEQUENCE ( NAME, current_value, increment)VALUES ('GLOBAL', 0, 1);//---- 创建mycat_seq_currvalDROP FUNCTION IF EXISTS mycat_seq_currval;DELIMITER / CREATE FUNCTION mycat_seq_currval (seq_name VARCHAR(50))RETURNS VARCHAR(64)DETERMINISTICBEGINDECLARE retval VARCHAR(64);SET retval = "- 999999999,null";SELECT concat( CAST(current_value AS CHAR), ",", CAST(increment AS CHAR) ) INTO retvalFROM MYCAT_SEQUENCEWHERE NAME = seq_name;RETURN retval;END;//---- 创建 mycat_seq_setvalDROP FUNCTION IF EXISTS mycat_seq_setval;DELIMITER / CREATE FUNCTION mycat_seq_setval(seq_name VARCHAR (50),VALUE INTEGER)RETURNS VARCHAR(64)DETERMINISTICBEGIN UPDATE MYCAT_SEQUENCE SET current_value=VALUE WHERE NAME=seq_name;RETURN mycat_seq_currval(seq_name);END;//---- 创建 mycat_seq_nextvalDROP FUNCTION IF EXISTS mycat_seq_nextval;DELIMITER /CREATE FUNCTION mycat_seq_nextval(seq_name VARCHAR(50)) RETURNS VARCHAR(64) DETERMINISTICBEGIN UPDATE MYCAT_SEQUENCE SET current_value=current_value+increment WHERE NAME=seq_name;RETURN mycat_seq_currval(seq_name);END; 配置sequence_db_conf.properties
指定时在哪个节点上执行sequence
GLOBAL=dn0
修改mysql的配置文件my.cnf
//-- [mysqld]下添加如下配置//-- 如果不添加如下配置并发下会取重复...log_bin_trust_function_creators=1
测试代码
public static void testSeq(DataSourceFactory ds) { PreparedStatement pst = null; Connection con = null; try { con = ds.getConnection(); String sql = "select next value for MYCATSEQ_GLOBAL as nextval"; pst = con.prepareStatement(sql); ResultSet result = pst.executeQuery(); while (result.next()) { String id = result.getString("nextval"); System.out.println(id); } } catch (Exception e) { e.printStackTrace(); } finally { try { pst.close(); con.close(); } catch (SQLException e) { e.printStackTrace(); } } }