mysql自定义函数实现查询主键并自增长_mysql中 function currval-优快云博客

本文链接：https://blog.youkuaiyun.com/datszhang/article/details/106649375

本文详细介绍了在SQL中创建自定义序列函数currval和nextval的方法，以及如何使用触发器在更新操作后修改相关表的状态。通过这些函数，可以实现序列号的生成和管理，而触发器则确保了业务逻辑的自动执行。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >


 structure for currval
-- ----------------------------
DROP FUNCTION IF EXISTS `currval`;
DELIMITER ;;
CREATE DEFINER=`root`@`%` FUNCTION `currval`(seq_name VARCHAR(50)) RETURNS int(11)
BEGIN
    DECLARE
        current INTEGER;


SET current = 0;

SELECT
    currentValue INTO current
FROM
    t_sequence
WHERE
    seqname = seq_name;

RETURN current;

END
;;
DELIMITER ;

-- ----------------------------
-- Function structure for nextval
-- ----------------------------
DROP FUNCTION IF EXISTS `nextval`;
DELIMITER ;;
CREATE DEFINER=`root`@`%` FUNCTION `nextval`(seq_name VARCHAR(50)) RETURNS varchar(50) CHARSET utf8
BEGIN  
   DECLARE currentval INTEGER;
   select currentValue INTO currentval from t_sequence where seqname=seq_name;
   IF currentval >= 99999999 THEN 
     UPDATE t_lincomb_sequence  
     SET currentValue = 0 
     WHERE seqname = seq_name;
   ELSE 
     UPDATE t_sequence  
     SET currentValue = currentValue + increment  
     WHERE seqname = seq_name;
   END IF;
   RETURN CONCAT(date_format(SYSDATE(),'%Y%m%d%H%i%S'),LPAD(currval(seq_name),8,'0'));  
END
;;
DELIMITER ;
DROP TRIGGER IF EXISTS `t_pro_product_push_status`;
DELIMITER ;;
CREATE TRIGGER `t_pro_product_push_status` AFTER UPDATE ON `t_pro_product` FOR EACH ROW BEGIN
	UPDATE t_tss_push SET `status`=1,`push_num`=0
	WHERE `business_id` = NEW.store_id;
END
;;
DELIMITER ;