在ID生成脚本中包括data.sql文件的内容,可以通过以下示例代码实现:
-- 自动生成ID的脚本
CREATE TABLE `id_generator` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
`business_type` varchar(32) NOT NULL COMMENT '业务类型',
`comment` varchar(255) DEFAULT NULL COMMENT '备注',
`gmt_create` datetime NOT NULL COMMENT '创建时间',
`gmt_modified` datetime NOT NULL COMMENT '更新时间',
PRIMARY KEY (`id`),
UNIQUE KEY `uk_id_generator_business_type` (`business_type`),
UNIQUE KEY `uk_id_generator_id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='ID生成器';
DELIMITER $$
CREATE PROCEDURE `next_id` (
IN `business_type` varchar(32)
)
BEGIN
DECLARE continue_flag INT DEFAULT 1;
DECLARE next_id BIGINT DEFAULT 0;
START TRANSACTION;
WHILE (continue_flag > 0) DO
SELECT `id` INTO next_id FROM `id_generator` WHERE `business_type` = business_type FOR UPDATE;
IF (next_id = 0) THEN
INSERT INTO `id_generator` (`id`, `business_type`, `gmt_create`, `gmt_modified`) VALUES (null, business_type, now(), now());
SET next_id = 6122185;
ELSE
UPDATE `id_generator` SET `id` = `id` + 1 WHERE `id` = next_id;
IF (ROW_COUNT() = 0) THEN
SET next_id = 0;
END IF;
END IF;
IF (next_id > 0) THEN
SET continue_flag = 0;
END IF;
END WHILE;
COMMIT;
SELECT next_id AS `id`;
END$$
DELIMITER ;
然后,在执行.sql文件时,将data.sql文件也包含在其中。例如,可在shell脚本中执行以下命令:
mysql -h $host -u $username -p$password $db_name < id_generator.sql
mysql -h $host -u $username -p$password $db