- 触发器设置UUID
BEGIN
SET new.id = UUID();
END
- 触发器设置设置不带【-】的UUID
BEGIN
SET new.id=REPLACE(UUID(),'-','');
END
触发器设置如图所示:
- 触发器设置规则编码【根据需求修改:bh:编号字段(2处);ywgl_zhsk:知识库表(1处)】
SET new.bh = CONCAT(
'ZS_',
CURRENT_TIMESTAMP + 0,
'_',
(
SELECT
LPAD(SUBSTR(IFNULL(MAX(bh), '0000'), -4) + 1, 4, 0)
FROM
ywgl_zhsk
WHERE
bh LIKE CONCAT('%', CURRENT_TIMESTAMP + 0, '_%')
)
)
- SQL获取规则编码【根据需求修改:bh:编号字段(2处);ywgl_zhsk:知识库表(1处);zsbh:显示的字段名称】
SELECT
CONCAT(
'ZS_',
CURRENT_TIMESTAMP + 0,
'_',
(
SELECT
LPAD(SUBSTR(IFNULL(MAX(bh), '0000'), -4) + 1, 4, 0)
FROM
ywgl_zhsk
WHERE
bh LIKE CONCAT('%', CURRENT_TIMESTAMP + 0, '_%')
)
) zsbh
FROM
DUAL
规则编码如图所示: