Mysql使用序列
mysql官方目前仍不支持序列，当前docker镜像mysql最新版本为8.0.33，经测试不支持
解决方案一：自定义序列
通过创建一个序列表，定义有序列名，步长，当前值等，再创建自定义函数nextval, currval等
解决方案二：选择非官方版本mysql
1. mysql的阿里云版本alisql支持序列，对于使用阿里云RDS的数据库选择mysql即可
2. 选择mysql的替代版mariadb，mariadb10.4开始支持序列
不同版本的序列语法略有不同
mariadb和alisql8.0创建和删除序列的语法基本一致
```
-- 创建序列
CREATE SEQUENCE student_seq [STARTWITH 1] [MINVALUE 1] [MAXVALUE 999999] [INCREMENTBY 1] [CACHE 100| NOCACHE] [CYCLE|NOCYCLE];
-- 删除序列
DROP SEQUENCE IF EXISTS student_seq;
```
使用序列作为列默认值
mariadb支持DDL直接指定nextval作为默认值
```
CREATE TABLE `student` (
`id` bigint(20) NOT NULL DEFAULT NEXTVAL(student_seq) COMMENT 'ID',
`name` varchar(20) NOT NULL COMMENT '姓名',
PRIMARY KEY (`id`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='学生表';
```
mariadb也支持使用触发器的方式设置序列为默认值
注意：mariadb对于非空数值列插入前会自动使用默认值0，判断的时候不是null
```
CREATE TRIGGER student_sequence_trigger BEFORE INSERT
ON student FOR EACH ROW
IF new.id = 0 THEN
SET new.id = nextval(student_seq);
END IF;
```
alisql8.0只支持使用触发器的方式设置序列为默认值
```
CREATE TRIGGER student_sequence_trigger BEFORE INSERT
ON student FOR EACH ROW
IF new.id IS NULL THEN
SET new.id = nextval(student_seq);
END IF;
```
为了兼容本地mariadb和阿里云服务器的兼容性写法
```
CREATE TABLE `student` (
`id` bigint(20) NOT NULL COMMENT 'ID',
`name` varchar(20) NOT NULL COMMENT '姓名',
PRIMARY KEY (`id`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='学生表';
DELIMITER $$
CREATE TRIGGER student_sequence_trigger BEFORE INSERT
ON student FOR EACH ROW
-- id不可为空时，alisql的new.id是null，mariadb的new.id是0
IF (new.id IS NULL OR new.id = 0) THEN
SET new.id = nextval(student_seq);
END IF;
$$
DELIMITER ;
```
本想使用alisql的docker镜像本地搭建数据库测试，但奈何alisql的docker镜像版本目前最高只有5.6
另外，alisql还有诸多限制：
1. alisql不支持动态创建sequence，使用prepare对创建sequence的动态sql预处理会导致mysql服务器挂掉，不知道什么原因，联系阿里云的技术支持也没个结果。如果创建sequence需要动态指定初始值可以分为两步，先直接创建sequence，指定初始值为1，再更新
```
-- mariadb不支持update sequence，想要更新需要使用select setval(student_seq, maxId)
SET @seqStr ='UPDATE student_seq SET start = @maxId';
SET @sqlStr = REPLACE(@seqStr,'@maxId', maxId);
PREPARE sqlStrFROM @sqlStr;
EXECUTE sqlStr;
```
2. alisql不支持动态创建触发器，而且也不能在存储过程中创建触发器，这也是官方mysql的限制。mariadb创建触发器支持CREATE TRIGGER IF NOT EXISTS或者CREATE OR REPLACE TRIGGER，alisql只支持直接CREATE TRIGGER。
使用prepare动态创建触发器报错：
This command is not supported in the prepared statement protocol yet
在存储过程中创建触发器报错：
Can't create a TRIGGER from within another stored routine
想要使用动态sql创建触发器目前我想到的解决方案只有通过上层应用实现，如通过mybatis拼串后程序调用执行，又因为这是数据库结构变更，需要用flyway管理，采用java migration调用：
```
// TriggerMapper.xml
<select id="createTrigger">
CREATE TRIGGER student${suffix}_sequence_trigger BEFORE INSERT
ON student${suffix} FOR EACH ROW
IF (new.id IS NULL OR new.id = 0) THEN
SET new.id = nextval(student_seq);
END IF;
</select>
// java migration，和sql文件migration效果相同
@Component
public class R__Create_trigger extends BaseJavaMigration {
@Autowired
private TriggerMapper triggerMapper;
@Override
public void migrate(Context context) throws Exception {
// 获取动态参数后传给mapper拼串
triggerMapper.createTrigger(suffix)
}
}
```
测试环境：
alisql8.0.28（阿里云）
mariadb10.6.5
参考链接：
