前言

在一些业务场景中，需要在数据库层生成有一定规律的编码，此时可以使用 SQL 中的变量来进行操作。

实现

建表

首先我们创建一个如下的表结构，并写入15条数据：

CREATE TABLE `user202411` (
  `user_id` int NOT NULL AUTO_INCREMENT COMMENT '用户ID',
  `user_index` varchar(100) COLLATE utf8mb4_general_ci NOT NULL COMMENT '用户索引',
  `user_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '用户名',
  `password` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '密码',
  `age` tinyint DEFAULT NULL COMMENT '年龄',
  `status` tinyint NOT NULL DEFAULT '1' COMMENT '状态：1-正常；99-删除',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='用户信息表';

业务编码生成

此时有如下业务需求：

根据 user_name、age 及 user_id 顺序生成 user_code，且仅生成 age 大于 30 的数据。
例如第一个 user_code 应为 郎家岭11310001，第二个 user_code 应为 郎家岭12320002，依此类推。

1. 首先我们在修改表结构，新增 user_code 字段：

ALTER table user202405 ADD COLUMN user_code VARCHAR(255) DEFAULT NULL COMMENT '用户编码';

2. SQL 功能实现：

SET @rownum := 0;-- 初始化变量
UPDATE user202405 
SET user_code = CONCAT( user_name, age, LPAD( @rownum := @rownum + 1, 4, 0 ) ) -- 对变量赋值
WHERE
   age > 30;

拓展：LPAD()函数

SQL 中 LPAD() 函数，完整用法 LPAD(str,len,padstr)。

简而言之就是限制字符串 str 的长度为 len，如果长度不够 len 则在 str 左侧补 padstr，如果超过则截取 str 的左侧 len 位。

例如上面示例中 LPAD( @rownum := @rownum + 1, 4, 0 )，它的功能是取4位行号编码，如果 行号@rownum 的位数小于4，则在行号左侧补 0(行号@rownum 超过4为则会从左截取4位)。

总结

记录下 MySQL 中变量的使用。