时间戳

首页 > 解决方案 > 使用查询在 mysql 中创建事件？

mysql - 使用查询在 mysql 中创建事件？

问题描述

我想在 mysql 中创建一个事件，但想添加我在描述中放入的总和和累积查询

这是事件代码，但我不知道该怎么做：

CREATE EVENT `recurring data` 
  ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 MINUTE
  ON COMPLETION PRESERVE
  DO 
BEGIN
  INSERT INTO ....
END

这是查询：

SET @csum := 0, @product_id:=NULL;
   UPDATE sma_sale_items 
   SET acumulado = (@csum := if(product_id=@product_id,@csum, 00000.0000) + quantity), product_id=(@product_id:=product_id) 
   ORDER BY product_id, id, sale_id;

更新

我尝试创建此事件，但它不起作用，它告诉我一个错误

1   CREATE EVENT `recurring data` 
2     ON SCHEDULE 
3     EVERY 1 MINUTE
4     STARTS CURRENT_TIMESTAMP + INTERVAL 1 MINUTE
5   DO 
6     BEGIN
7     SET @csum := 0, @product_id:=NULL;
8     UPDATE sma_sale_items 
9     SET acumulado = (@csum := if(product_id=@product_id,@csum, 00000.0000) + quantity), product_id=(@product_id:=product_id) 
10    ORDER BY product_id, id, sale_id;
11    END

错误代码

#1064 - Something is wrong in its sintax near '' on line 7

标签： mysqlsqlmysql-5.7mysql-event

解决方案

CREATE EVENT `recurring data` 
  ON SCHEDULE 
  -- how often it must be executed
  EVERY 1 MINUTE
  -- when the first execution must be performed
  STARTS CURRENT_TIMESTAMP + INTERVAL 1 MINUTE
DO 
BEGIN
  -- perform the actions
END

测试

CREATE EVENT `recurring data` 
ON SCHEDULE 
EVERY 1 MINUTE
STARTS CURRENT_TIMESTAMP + INTERVAL 1 MINUTE
DO 
    BEGIN
    DECLARE var_csum INTEGER DEFALUT 0;
    DECLARE var_product_id DECIMAL(10,4) DEFAULT NULL;
    UPDATE sma_sale_items 
    SET acumulado = (var_csum := if(product_id=var_product_id,var_csum, 00000.0000) + quantity), 
        product_id = (var_product_id:=product_id) 
    ORDER BY product_id, id, sale_id;
END

推荐阅读