函数的定义
-- 订单号
delimiter $$
create function queryNo(pyear int) returns bigint(20)
begin
set @sum = 11;
UPDATE shop_order_no set `value` = `value`+1 where year =pyear;
set @sum = (SELECT value from shop_order_no where year = pyear limit 1);
if (@sum is null) THEN
INSERT INTO `shop_order_no `(year,value) VALUE (pyear, 1);
set @sum = 1;
end if ;
return @sum;
end;
$$
delimiter ;
订单号增加表 一年一个开始
DROP TABLE IF EXISTS `shop_order_no `;
CREATE TABLE `shop_order_no ` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`year` int(10) NOT NULL,
`value` bigint(20) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of shop_order_no
-- ----------------------------
INSERT INTO `shop_order_no ` VALUES ('1', '2019', '0');
INSERT INTO `shop_order_no ` VALUES ('2', '2020', '0');
INSERT INTO `shop_order_no ` VALUES ('3', '2021', '0');
INSERT INTO `shop_order_no ` VALUES ('4', '2022', '0');
INSERT INTO `shop_order_no ` VALUES ('5', '2023', '0');
INSERT INTO `shop_order_no ` VALUES ('6', '2024', '0');
INSERT INTO `shop_order_no ` VALUES ('7', '2025', '0');
INSERT INTO `shop_order_no` VALUES ('8', '2026', '0');
INSERT INTO `shop_order_no ` VALUES ('9', '2027', '0');