利用mysql行级锁创建数据库主键id

存储函数：

CREATE FUNCTION `getSerialNo`(`serialName` VARCHAR(50), `skip` INT) RETURNS bigint(20)
COMMENT '获取序列号'
BEGIN
DECLARE orderNum,loopSize, baseNum BIGINT DEFAULT 0;
SELECT loop_size, base_num INTO loopSize, baseNum FROM sys_serial_no where serial_name= serialName and version=0 FOR UPDATE;
SET orderNum = baseNum + loopSize;
UPDATE sys_serial_no SET loop_size = loopSize + skip where serial_name= serialName ;
RETURN orderNum;
END

表结构：

CREATE TABLE `sys_serial_no` (
`serial_name` varchar(100) NOT NULL COMMENT '序列名称',
`loop_size` bigint(20) unsigned DEFAULT '1',
`base_num` bigint(20) unsigned DEFAULT '100000000',
`version` int(11) unsigned DEFAULT '0',
PRIMARY KEY (`serial_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='序列号生成器表';

转载于:https://www.cnblogs.com/lvgg/p/6699704.html