如何在不修改base34函数的前提下用触发器生成复杂客户ID

我们之前用标识列生成唯一编号（比如customer_id），但内部审计认为这种方式存在安全风险，因此打算改用更复杂的ID。目前已找到base34编码函数，计划将随机数、时间戳片段与序列号拼接后传入该函数生成目标ID。

需确认：能否在CUSTOMERS表的BEFORE INSERT触发器中实现此逻辑，不修改现有base34函数，且触发器自动填充customer_id字段？

CUSTOMERS表结构 #

CREATE TABLE CUSTOMERS (
 customer_id VARCHAR2 (20),
 first_name VARCHAR2 (20),
 last_name VARCHAR2 (20));

参考测试代码 #

-- 创建测试表
create table t ( pk number);

-- 创建序列，起始值1000000，循环范围1000000-9999999
create sequence seq start with 1000000 minvalue 1000000 maxvalue 9999999 cycle;

-- 插入测试数据：随机数+时间戳片段+序列值拼接成数字
begin
     for i in 1 .. 10 loop
       insert into t values (to_number(trunc(dbms_random.value(1000,9999))||to_char(systimestamp,'FFSS')||seq.nextval));
     end loop;
end;
/

-- Base34编码函数：将数字转为排除易混淆字符的Base34字符串
create or replace function base34(p_num number) return varchar2 is
     l_dig varchar2(34) := 'AB0CD1EF2GH3JK4LM5NP6QR7ST8UV9WXYZ';
     l_num number := p_num;
     l_str varchar2(38);
begin
  loop
     l_str := substr(l_dig,mod(l_num,34)+1,1) || l_str ;
     l_num := trunc(l_num/34);
    exit when l_num = 0;
  end loop;
 return l_str;
end;
/

-- Base34解码函数：将Base34字符串转回数字
create or replace function dec34(p_str varchar2) return number is
  l_dig varchar2(34) := 'AB0CD1EF2GH3JK4LM5NP6QR7ST8UV9WXYZ';
  l_num number := 0;
begin
  for i in 1 .. length(p_str) loop
     l_num := l_num * 34 + instr(l_dig,upper(substr(p_str,i,1)))-1;
   end loop;
  return l_num;
end;
/

-- 测试编码结果
select base34(pk) from t where rownum <= 10;

-- 验证编码解码一致性
select to_char(pk) from t where rownum = 1
   union all
   select base34(pk) from t where rownum = 1
   union all
   select to_char(dec34(base34(pk))) from t where rownum = 1;

实现方案 #

完全可以通过BEFORE INSERT触发器实现需求，无需修改现有base34函数，步骤如下：

1. 创建专用序列 #

为CUSTOMERS表单独创建序列（避免与其他业务序列冲突）：

create sequence seq_customer_id 
start with 1000000 
minvalue 1000000 
maxvalue 9999999 
cycle;

2. 创建BEFORE INSERT触发器 #

触发器自动拼接随机数、时间戳片段、序列值，转为数字后调用base34函数生成customer_id：

create or replace trigger trg_customer_id
before insert on CUSTOMERS
for each row
begin
  -- 拼接4位随机数+时间戳FFSS（微秒后两位+秒）+序列值，转数字后执行base34编码
  :new.customer_id := base34(
    to_number(
      trunc(dbms_random.value(1000,9999)) || 
      to_char(systimestamp, 'FFSS') || 
      seq_customer_id.nextval
    )
  );
end;
/

3. 测试验证 #

插入数据时触发器会自动填充customer_id：

insert into CUSTOMERS(first_name, last_name) values ('John', 'Doe');
insert into CUSTOMERS(first_name, last_name) values ('Jane', 'Smith');

select customer_id, first_name, last_name from CUSTOMERS;

内容的提问来源于stack exchange，提问作者Pugzly