一:取自增ID方式
- 核心思想:在补入数据条数insert_num确认的情况下,查询出最大ID值max_id_num,将MYSQL的当前自增起始设置为稍大于(insert_num + max_id_num)= auto_increment_num 的值,预留足够的插入空间,再将固定ID的数据insert即可
例子:
1.插入条数
insert_num:100
2.查询最大id MAX(id)
max_id_num:3500
3.设置自增起始值
auto_increment_num:3700
4.设置插入固定ID起始值
fin_auto_increment_num: 3550
5.备份ID
6.执行脚本
INSERT INTO table_name (id, c_u, u_u, c_t, u_t )
SELECT
row_number() over(order by id) + fin_auto_increment_num as id,
'sys' AS c_u,
'sys' AS u_u,
unix_timestamp(now())* 1000 AS c_t,
unix_timestamp(now())* 1000 AS u_t
FROM table_name;
7.校验
二:取leafId方式
- 核心思想:根据插入条数申请固定长度leafId,将ID设置为固定值作为补入数据的ID
- 坑点:目前leafId的步长为1000,取值过程中可能出现断层情况
方案一:插入分步长处理
方案二:重新申请id
例子:
1.申请固定长度leafId
insert_num: 100
-- 申请 table_name ID
-- curl http://leaf-server/api/segment/batchGet/table_name?batchSize=insert_num
-- 第一个ID值为:first_id_num:30001
2.备份ID
3.执行脚本
SET @max_id_num = first_id_num - 1;
SET @row_num = 0;
INSERT INTO table_name (id, c_u, u_u, c_t, u_t )
SELECT
(@max_id + (@row_num := @row_num + 1)) AS id, temp.*
from (
'sys' AS c_u,
'sys' AS u_u,
unix_timestamp(now())* 1000 AS c_t,
unix_timestamp(now())* 1000 AS u_t
FROM table_name) temp;
4.校验