DELIMITER $$
CREATE
/*[DEFINER = { user | CURRENT_USER }]*/
TRIGGER `ce`.`myT` BEFORE INSERT
ON `ce`.`my3`
FOR EACH ROW BEGIN
SET new.id = CONCAT('CK',LPAD(((SELECT SUBSTRING(id,3,8) FROM my3 WHERE id = (SELECT id FROM my3 ORDER BY SUBSTRING(id,3,6) DESC LIMIT 1))+1),4,0));
END$$
DELIMITER ;
DELIMITER $$
实现初始化为CK1000
USE `ce`$$
/*
字符串拼接数字。实现数字的自增
*/
DROP TRIGGER /*!50032 IF EXISTS */ `MY3`$$
CREATE
/*!50017 DEFINER = 'root'@'localhost' */
TRIGGER `MY3` BEFORE INSERT ON `my3`
FOR EACH ROW BEGIN
SET new.id = IFNULL(CONCAT('CK',LPAD(((SELECT SUBSTRING(id,3,8) FROM my3 WHERE id = (SELECT id FROM my3 ORDER BY SUBSTRING(id,3,6) DESC LIMIT 1))+1),4,0)),"CK1000");
END;
$$
DELIMITER ;
因为用户表中用户编号不是int类型,想实现自动自增功能,使用内置的方法肯定是行不通的,所以,使用了复杂的查询方法及拼接方式,
此方法虽然比较笨,但还是可以运行的的。
采用触发器方式,详细思路:
1、使用查询语句查出表中最后一条数据的id:
select user_no from user order by user_no desc limit 1
2、使用substring函数截取最后一条BH-XXXXXXXX中数字部分:
SELECT substring(user_no,4,8) from user where user_no=(select user_no from user order by user_no desc limit 1)
上面的意思是从第4位开始截取,截到第8位
3、使用concat语句进行字符串连接:
concat('BH-',(SELECT substring(user_no,4,8) from user where user_no=(select user_no from user order by user_no desc limit 1) +1));
这里需要注意:我一开始认为只要在结果上 +1,然后拼接就行了,其实实际上并不是我想象中的 BH-00000002,而是BH-2,所以,这里采用LPAD函数进行优化,结果如下:
concat('BH-',lpad(((SELECT substring(user_no,4,8 from user where user_no=(select user_no from user order by user_no desc limit 1))+1),8,0));
小提示:8表示填充长度,0表示填充内容,不理解的可以百度搜索MySQL lpad函数
然后,完整的代码如下:
CREATE TRIGGER `T` BEFORE INSERT ON `user`
FOR EACH ROW begin
set new.user_no=concat('BH-',lpad(((SELECT substring(user_no,4,8 from user where user_no=(select user_no from user order by user_no desc limit 1))+1),8,0));
end;