MySQL事件调度程序查询？

Question

当我在 phpmyadmin 的 SQL 选项卡上编写以下代码时:

    DELIMITER $$    
    CREATE 
        EVENT `near_end_warranty` 
        ON SCHEDULE EVERY 1 HOUR
        DO BEGIN

        DECLARE numrow int;
        SELECT COUNT(id) FROM equipments INTO numrow WHERE  
             TIMESTAMPDIFF(WEEK,NOW(),warranty_end_date)<5 AND                
             TIMESTAMPDIFF(WEEK,NOW(),warranty_end_date)>0;

        IF numrow>0 THEN
        INSERT INTO notifications 
            (eq_id,start_date,end_date,name,type,message,created_by,modified_by,
            created_on,modified_on)
        SELECT eq_id,warranty_start_date,warranty_end_date,'warranty','ending soon',
               'warranty ends soon for','System','System',NOW(),NOW()
        FROM equipments WHERE 
           TIMESTAMPDIFF(WEEK,NOW(),warranty_end_date)<5 AND  
           TIMESTAMPDIFF(WEEK,NOW(),warranty_end_date)>0;
        END IF;         
    END $$    
DELIMITER ;

它显示以下错误:

1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE TIMESTAMPDIFF(WEEK,NOW(),warranty_end_date)<5 AND TIMESTAMPDIFF(WEEK,NOW()' at line 7

我检查并试图找出错误，但找不到。请有人帮助我这里出了什么问题？

Answer 1

INTO 子句必须位于 WHERE 子句之后:

SELECT COUNT(id) 
FROM equipments 
WHERE  
    TIMESTAMPDIFF(WEEK,NOW(),warranty_end_date)<5 AND                
    TIMESTAMPDIFF(WEEK,NOW(),warranty_end_date)>0
INTO numrow;

查看语法 here (这是错误消息告诉您检查的手册)。

所有子句都必须按照指定的顺序排列，没有灵 active 。

实际上，INTO 子句有一个异常(exception)，它也可以紧接在 SELECT 列表之后。但它不能追随FROM。所以你也可以这样写:

SELECT COUNT(id) INTO numrows
FROM equipments 
WHERE  
    TIMESTAMPDIFF(WEEK,NOW(),warranty_end_date)<5 AND                
    TIMESTAMPDIFF(WEEK,NOW(),warranty_end_date)>0;