MySQL 触发器自动插入指定格式的主键

╰半橙微兮° 2022-01-28 08:09 251阅读 0赞

## 用触发器解决 ##

drop database if exists mydb;
    create database mydb;
    use mydb;
    
    -- 注册需要：邮箱，密码，昵称。
    -- 登录需要：邮箱+密码
    drop table if exists user_account; 
    create table user_account (
        user_id char(30)  comment "标识符",
        nickname varchar(20) not null comment "昵称",
        email char(30) not null comment "邮箱(邮箱也应该unique)",
        password char(50) not null comment "密码",
        tel char(20) comment "手机",
        primary key (user_id)    
    ) comment="用户账号表，用于验证登录";
    
    delimiter $$
    drop trigger if exists auto_set_user_id;
    create trigger auto_set_user_id before insert on user_account for each row
    begin
        declare generate_id char(30);
        declare repeat_line int default 1;
        while repeat_line != 0 do
            set generate_id = concat(NEW.nickname, right(rand(), 10));
            select count(user_id) from user_account where user_id = generate_id into repeat_line;  -- 若有重复ID，循环
        end while;
        set NEW.user_id = generate_id;  -- 向user_account表插入记录之前，设置user_id字段。
    end $$
    delimiter ;
    
    
    insert into user_account (nickname, email, password) values ("zhangsan", " ", " ");
    select * from user_account;

## MySQL function ##

用于生成 user\_account\_user\_id字段的值(用户的nickname+8位随机数(保证不会重复))。

delimiter $$
    drop function if exists generate_user_id $$
    create function generate_user_id(name char(22)) returns char(30)
    begin
        declare result char(30);
        declare repeat_line int default 1;
        while repeat_line != 0 do
            set result = concat(name, left(crc32(concat(now(), name)), 8));
            select count(user_id) from user_account where user_id = result into repeat_line;
        end while;
        return result;
    end $$
    delimiter ;

`function`不如触发器使用简洁，其插入记录：  
`insert into user_account (user_id, nickname, email, password) values (generate_user_id("zhangsan"), "zhangsan", "11@qq.com", password("11"));`

## Tips ##

*  用查询语句的结果对变量赋值(结果集只有一行一列)  
    `set 变量名 = (select语句);` (一定要加括号)  
    `select语句 into 变量名;`
 *  MySQL function while if 测试

delimiter $$
    drop function if exists foo $$
    create function foo() returns varchar(1000)
    begin
        declare num int default 0;
        declare result varchar(50) default "";
        while num != 10 do
            set num = num + 1;
            if num = 5 then
                set result = concat(result, " five ");
            else 
                set result = concat(result, " ");
                set result = concat(result, num);
            end if;
        end while;
        return result;
    end $$
    delimiter ;
    select foo();

结果：

mysql> select foo();
    +---------------------------+
    | foo()                     |
    +---------------------------+
    |  1 2 3 4 five  6 7 8 9 10 |
    +---------------------------+
    1 row in set (0.00 sec)