数据库表字段创建nextval函数实现字符串数字自增

• 需求：表中的主键id是字符串类型的，要实现主键自增就需要用这这个函数啦。

首先要创建table_sequence表

CREATE TABLE `table_sequence` (
  `sequence_names` varchar(50) NOT NULL,  -- 序列名称
  `current_value` bigint(50) NOT NULL,  -- 当前值
  `increment` int(1) NOT NULL,  -- 步长(跨度)
  PRIMARY KEY (`sequence_names`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

sequence_names：代表主键自增的那张表名
current_value：代表当前主键的值，只能是数字类型，如果要带字母的主键，需要自己再代码中拼接
increment：代表每次自增的步长

下面需要创建两个函数

1.获取当前主键值

DELIMITER $$
USE `数据库名字`$$
DROP FUNCTION IF EXISTS `currval`$$
CREATE FUNCTION `currval`(`sequence_name` VARCHAR(50)) RETURNS BIGINT(50)
BEGIN
	DECLARE seq_current BIGINT(40);
  SET seq_current = 0;
  SELECT t.current_value INTO seq_current FROM table_sequence t WHERE t.sequence_names = sequence_name;
  RETURN seq_current;
END$$
DELIMITER ;

2.获取下一个自增主键值

DELIMITER $$
USE `数据库名字`$$
DROP FUNCTION IF EXISTS `nextval`$$
CREATE FUNCTION `nextval`(`sequence_name` VARCHAR(50)) RETURNS BIGINT(50)
BEGIN
	DECLARE seq_current BIGINT(20);
	DECLARE seq_increment INT(1);
  SET seq_current = 0;
  SET seq_increment = 0;
	SELECT current_value INTO seq_current FROM table_sequence WHERE sequence_names=sequence_name FOR UPDATE;
	SELECT increment INTO seq_increment FROM table_sequence WHERE sequence_names=sequence_name FOR UPDATE;
	UPDATE table_sequence SET current_value=seq_current+seq_increment WHERE sequence_names=sequence_name;
  SET seq_current = seq_current + seq_increment;
  RETURN seq_current;
END$$
DELIMITER ;

创建了两个自定义函数

下面是java代码部分

创建一个SequenceMapper.java的接口

import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;

@Mapper
public interface SequenceMapper {

     /**
      *  获取下一个主键值
     */
     String queryNextSequence(@Param("seq_table_name") String seq_table_name);

     /**
      * 获取当前主键值SequenceMapper.xml
      */
     String queryCurSequence(@Param("seq_table_name") String seq_table_name);
}

创建一个SequenceMapper.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.dowin.miniapp_admin.mapper.SequenceMapper">

    <select id="queryNextSequence" resultType="java.lang.String">
        select
        nextval(#{seq_table_name})
    </select>

    <select id="queryCurSequence" resultType="java.lang.String">
        select
        currval(#{seq_table_name})
    </select>

</mapper>

到此代码就结束了！！！
下面就是调用写的接口啦
1.先注入bean

@Resource
private SequenceMapper sequenceMapper;

2.调用接口

td_b_house_ad是表名

String curId = sequenceMapper.queryCurSequence("td_b_house_ad");
String nextId = sequenceMapper.queryNextSequence("td_b_house_ad");