利用函数和触发器在入表时自动生成取餐码

今天小编遇到了一个需求，客户想在每次下订单的时候生成一个取餐码，要求是，取餐码在在同一个食堂下不能重复，且每周更新取餐码前缀

这种要求比较复杂的如果在Java代码中写也是可以的，但是接下来主要呈现怎么用函数和触发器来实现

步骤一：创建一个表（t_good_order，也就是你要加取餐码的表）

CREATE TABLE IF NOT EXISTS t_goods_order (
    id INT AUTO_INCREMENT PRIMARY KEY,
    canteen_id INT,
    pickup_code VARCHAR(255),
    -- 其他字段...
);

步骤二：创建一个计数器表

为了简化，我们将所有食堂的都记录在一个计数器表中，并记录下当前周的开始日期

CREATE TABLE IF NOT EXISTS pickup_code_counter (
    canteen_id INT PRIMARY KEY,
    counter INT DEFAULT 1,
    week_start DATE
);

步骤三：创建函数

我们需要创建一个函数用来计算当前周的开始日期

DELIMITER //
CREATE FUNCTION get_current_week_start() RETURNS DATE
BEGIN
    DECLARE week_start DATE;
    SET week_start = DATE_SUB(CURDATE(), INTERVAL (WEEKDAY(CURDATE())) DAY);
    RETURN week_start;
END //
DELIMITER ;

步骤四：创建触发器

我们需要创建一个触发器，在插入订单时自动填充pickup_code字段，并相应修改计数器中的数据

触发器逻辑：

1. 根据当前为第几个周确定字母前缀，从而达到每周字母不不一致（在此需要提示一下，因为当前需求是近半年不重复即可，所以只规定了24个大写字母，字母是循环使用的，但一年大概为52个整周，如有需要可以把小写字母也加上，或者看个人需求加）。
2. 从计数器表中获取或初始化计数器。
3. 将两者组合成取餐码。

实例代码：

BEGIN
	-- 设置变量
    DECLARE current_counter INT;
    DECLARE current_canteen_id INT;
		DECLARE prefix CHAR(1);
		DECLARE week_starts DATE;
		
	-- 获取当前周的起始日期
	SET week_starts = get_current_week_start();
	-- 确定前缀
	SET prefix = SUBSTRING('ABCDEFGHIJKLMNOPQRSTUVWXYZ', YEARWEEK(week_starts) % 26 + 1, 1);
	-- 赋值
	SET current_canteen_id = NEW.canteen_id;
	SET week_starts = (SELECT week_start FROM pickup_code_counter WHERE canteen_id = current_canteen_id);
	SET current_counter = (SELECT counter FROM pickup_code_counter WHERE canteen_id = current_canteen_id);
		
	-- 检查是否已有对应 canteen_id 的记录
	IF NOT EXISTS (SELECT 1 FROM pickup_code_counter WHERE canteen_id = NEW.canteen_id) THEN
	    -- 如果不存在，则插入新的记录
	    INSERT INTO pickup_code_counter (canteen_id, counter, week_start)
	    VALUES (NEW.canteen_id, 1, week_starts);
		ELSE
			-- 如果当前日期不是当前周的开始日期，则修改计数器日期并将数字重置为1
			IF week_starts != DATE_SUB(CURDATE(), INTERVAL (WEEKDAY(CURDATE())) DAY) THEN
					-- 更新计数器表中的周开始日期
					UPDATE pickup_code_counter
					SET week_start = DATE_SUB(CURDATE(), INTERVAL (WEEKDAY(CURDATE())) DAY),
							counter = 1
					WHERE canteen_id = current_canteen_id;
			ELSE
					-- 递增计数器
					UPDATE pickup_code_counter
					SET counter = counter + 1
					WHERE canteen_id = current_canteen_id;
			END IF;
	END IF;
	
	-- 获取当前周的标识符
	SET current_counter = (SELECT counter FROM pickup_code_counter WHERE canteen_id = current_canteen_id);
	-- 设置取餐码
	SET NEW.pickup_code = CONCAT(prefix, LPAD(IFNULL(current_counter, 1), 3, '0'));
END

 温馨提示：以上代码都是在查询中直接执行即可

到此结束，你就可以得到自己想要的东西了