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

用触发器解决

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)