Mycat全局序列生成以及Redis自增主键

我会带着你远行 2023-10-11 14:25 63阅读 0赞

### 一、创建MyCat全局序列函数以及表 ###

**在已配好的数据节点DataNode上执行建表SQL**

CREATE TABLE MYCAT_SEQUENCE (NAME VARCHAR(50) NOT NULL,current_value INT NOT
    NULL,increment INT NOT NULL DEFAULT 100, PRIMARY KEY(NAME)) ENGINE=INNODB;

**在已配好的数据节点DataNode上创建以下函数**

DELIMITER $$
    CREATE FUNCTION mycat_seq_currval(seq_name VARCHAR(50)) RETURNS VARCHAR(64)
    DETERMINISTIC
    BEGIN
    DECLARE retval VARCHAR(64);
    SET retval="-999999999,null";
    SELECT CONCAT(CAST(current_value AS CHAR),",",CAST(increment AS CHAR)) INTO retval FROM
    MYCAT_SEQUENCE WHERE NAME = seq_name;
    RETURN retval;
    END $$
    DELIMITER ;
    DELIMITER $$
    CREATE FUNCTION mycat_seq_setval(seq_name VARCHAR(50),VALUE INTEGER) RETURNS
    VARCHAR(64)
    DETERMINISTIC
    BEGIN
    UPDATE MYCAT_SEQUENCE
    SET current_value = VALUE
    WHERE NAME = seq_name;
    RETURN mycat_seq_currval(seq_name);
    END $$
    DELIMITER ;
    DELIMITER $$
    CREATE FUNCTION mycat_seq_nextval(seq_name VARCHAR(50)) RETURNS VARCHAR(64)
    DETERMINISTIC
    BEGIN
    UPDATE MYCAT_SEQUENCE
    SET current_value = current_value + increment WHERE NAME = seq_name;
    RETURN mycat_seq_currval(seq_name);
    END $$
    DELIMITER ;

**初始化序列表记录**

INSERT INTO MYCAT_SEQUENCE(NAME,current_value,increment) VALUES ('ORDERS', 1,
    100);

**编辑Mycat的配置文件sequence\_db\_conf.properties**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzNzUwNjU2_size_16_color_FFFFFF_t_70_pic_center]  
**重启Mycat**

### 二、Redis生成主键 ###

**redis服务器创建一个序列变量**  
![在这里插入图片描述][20200821201902286.png_pic_center]  
**springboot方式获取自增序列**  
*依赖引入*

<dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter-test</artifactId>
            </dependency>
    
            <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter-data-redis</artifactId>
            </dependency>

*编写测试类*

@SpringBootTest
    @RunWith(SpringRunner.class)
    public class RedisIncrTest {
        
    
        @Autowired
        private StringRedisTemplate redisTemplate;
    
        @Test
        public void testIncr(){
        
    
            for (int i=0;i<10;i++){
        
                Long increment = redisTemplate.opsForValue().increment("seq");
                System.out.println(increment);
            }
    
        }
    
    }

*日志查看*  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzNzUwNjU2_size_16_color_FFFFFF_t_70_pic_center 1]  
*再次查看redis服务端值*  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzNzUwNjU2_size_16_color_FFFFFF_t_70_pic_center 2]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzNzUwNjU2_size_16_color_FFFFFF_t_70_pic_center]: https://img-blog.csdnimg.cn/20200821201543881.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzNzUwNjU2,size_16,color_FFFFFF,t_70#pic_center
[20200821201902286.png_pic_center]: https://img-blog.csdnimg.cn/20200821201902286.png#pic_center
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzNzUwNjU2_size_16_color_FFFFFF_t_70_pic_center 1]: https://img-blog.csdnimg.cn/20200821202224937.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzNzUwNjU2,size_16,color_FFFFFF,t_70#pic_center
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzNzUwNjU2_size_16_color_FFFFFF_t_70_pic_center 2]: https://img-blog.csdnimg.cn/2020082120241743.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzNzUwNjU2,size_16,color_FFFFFF,t_70#pic_center