使用某些逻辑不工作的情况创建mysql事件

Question

我试图在mysql中创建一个事件。

模式：

create event alert_2 ON SCHEDULE EVERY 300 SECOND DO 
BEGIN
DECLARE current_time DATETIME;
DECLARE attempted INT;
DECLARE completed INT;
DECLARE calc_value DECIMAL;
set @current_time = CONVERT_TZ(NOW(), @@session.time_zone, '+0:00');
select count(uniqueid) as @attempted,SUM(CASE WHEN seconds > 0 THEN 1 ELSE 0 END) as @completed from callinfo where date >= DATE_SUB(@current_time, INTERVAL 300 SECOND) AND date <= @current_time;
SET @calc_value = (ROUND((@completed/@attempted)*100,2);
IF @calc_value <= 10.00 THEN
     INSERT INTO report(value1) value (@calc_value);
END IF;
END;

问题:事件不会创建

需要建议：

• 这会在callinfo表上创建重载吗？
• 如果是的话，你愿意提出其他方法来实现同样的目标吗？
• 请允许我创建类似的，但在50左右的倍数。它会创造巨大的负载上呼叫信息表。 呼叫信息模式： 创建表callinfo ( uniqueid varchar(60) NULL ''，accountid int(11)默认'0'，type tinyint(1) NULL默认值'0'，callerid varchar(120) NULL默认值‘，callednum varchar(30) NULL’，seconds smallint(6) NULL '0'，trunk\_id smallint(6) NULL '0'，trunkip varchar(15) NULL‘，callerip varchar(15)非空默认值’，disposition varchar(45) NULL默认值''，date datetime NULL默认值‘0000-00: 00:00:00'，debit十进制(20，6)非空默认值'0.000000'，cost十进制(20，6)非空默认值'0.000000'，provider\_id int(11)非空默认值'0'，pricelist\_id smallint(6)非空默认值'0'，package\_id int(11)非空默认值'0'，pattern varchar(20) NOT，NULL，NULL。notes varchar(80) NOT NULL，invoiceid int(11) NULL默认值'0'，rate\_cost十进制(20，6)非空默认值'0.000000'，reseller\_id int(11)非空默认值'0'，reseller\_code varchar(20) NOT NULL，reseller\_code\_destination varchar(80)默认值NULL，reseller\_cost十进制(20，6)非空默认值'0.000000'，provider\_code varchar(20) NULL，provider\_code\_destination varchar(80) NULL，provider\_cost十进制(20，6)非空默认值“0.000000”，provider\_call\_cost小数(20，6) NULL，call\_direction枚举(“出站”，“入站”) NULL，calltype枚举(“标准”，“DID”，“空闲”，“呼叫卡”)非空默认值“标准”，profile\_start\_stamp datetime非空默认值'0000-00- 00 :00:00'，answer\_stamp datetime非空默认值‘0000- 00:00:00'，bridge\_stamp日期时间不为空默认值“0000-00-00:00:00”，progress\_stamp日期时间不为空默认值“0000-00-00:00:00”，progress\_media\_stamp日期时间不为空默认值'0000-00- 00 :00:00'，end\_stamp datetime不为空默认值'0000-00- 00 :00:00'，billmsec int(11)非空默认值'0'，answermsec int(11)非空默认值'0'，waitmsec int(11)非空默认值'0'，progress\_mediamsec int(11)非空默认值'0'，flow\_billmsec int(11)非空默认值'0'，is\_recording tinyint(1)非空缺省'1‘注释'0表示On,1表示Off’) ENGINE=InnoDB默认CHARSET=utf8注释=‘callinfo’；ALTER callinfo添加唯一键uniqueid (uniqueid)，添加键user\_id (accountid)；

有关callinfo表的更多信息：

在呼叫信息表中插入约20K/小时的线路。如果需要在模式中应用任何索引以获得良好的性能，请提出建议。

Answer 1

一些建议：

• 用户定义的变量(以@字符命名的变量)是独立的，与局部变量不同。
• 不需要声明没有引用的局部变量
• 使用局部变量以利于用户定义变量。
• 以@字符开头的列别名(标识符)需要转义(否则MySQL将引发语法错误)
• 分配一个看起来像用户定义变量的列别名(标识符)只是一个列别名；它不是对用户定义变量的引用。
• 使用SELECT ... INTO将语句返回的标量值赋值给局部变量和/或用户定义的变量。
• 声明数据类型DECIMAL等同于指定DECIMAL(10,0)
• 在INSERT ... VALUES语句中，关键字是VALUES而不是VALUE
• 最佳实践是给出与列名不同的局部变量名称。
• 最佳实践是限定所有列引用。
• 在没有其他标识值的情况下，只在表中插入单个列，计算值，这有点奇怪(这不是非法的。这可能正是规范所要求的。我觉得有点奇怪。我是根据编写的代码提出来的，因为代码的作者似乎不熟悉MySQL。
• 使用CONVERT_TZ有点奇怪；考虑到在SQL语句中引用的任何日期时间值都将在当前会话时区中进行解释；我们假设date列是DATETIME数据类型，但这只是猜测。
• 若要创建包含分号的MySQL存储程序，会话的DELIMITER需要更改为未出现在存储程序定义中的字符。

与其解决存储程序中的每一个问题，我还将建议做一个看起来像原始代码所想做的修改：

DELIMITER $$

CREATE EVENT alert_2 ON SCHEDULE EVERY 300 SECOND DO
BEGIN
   DECLARE ld_current_time DATETIME;
   DECLARE ln_calc_value   DECIMAL(20,2);
-- DECLARE li_attempted    INT;
-- DECLARE li_completed    INT;

   SET ld_current_time = CONVERT_TZ(NOW(), @@session.time_zone, '+0:00');

   SELECT ROUND( 100.0
               * SUM(CASE WHEN c.seconds > 0 THEN 1 ELSE 0 END)
               / COUNT(c.uniqueid)
          ,2) AS calc_value
 --     , COUNT(c.uniqueid) AS attempted
 --     , SUM(CASE WHEN c.seconds > 0 THEN 1 ELSE 0 END) AS completed
     FROM callinfo c
    WHERE c.date >  ld_current_time + INTERVAL -300 SECOND
      AND c.date <= ld_current_time
     INTO ln_calc_value
 --     , li_attempted
 --     , li_completed
   ;
   IF ln_calc_value <= 10.00 THEN
     INSERT INTO report ( value1 ) VALUES ( ln_calc_value );
   END IF;
END$$

DELIMITER ;

为了提高性能，我们希望有一个以日期作为前导列的索引。

... ON `callinfo` (`date`, ...)

理想情况下(对于这个存储程序中的查询)，带有前一列日期的索引应该是一个覆盖索引(包括查询中引用的所有列)。

... ON `callinfo` (`date`,`seconds`,`uniqueid`)

问:这会在callinfo表上造成重载吗？

因为这会对callinfo表运行一个查询，所以它需要获得共享锁。有了一个合适的索引，并且假设5分钟的call info是一组很小的行，我不认为这个查询会对性能问题或争用问题造成很大影响。如果它确实导致了一个问题，我希望这个存储程序中的查询不是问题的根源，它只会加剧一个已经存在的问题。

问:如果是，你是否愿意提出其他方法来实现同样的目标？

当我们还没有定义我们想要实现的“事情”时，很难提出替代“事情”的建议。

问:我可以创建类似的，但倍数在50左右。它会在callinfo表上造成巨大的负载吗？

答:只要查询有效，通过适当的索引选择一小部分行，并且运行迅速，我就不会期望这个查询会产生巨大的负载，没有。

随访

为了获得最佳性能，我们肯定需要一个带有date领先列的索引。

我会删除查询中对uniqueid的引用。也就是说，将COUNT(c.uniqueid)替换为SUM(1)。这些结果是等价的(假设uniqueid保证为非空)，除非没有行，COUNT()将返回0，SUM()将返回空。

因为我们用这个表达式除以，在“没有行”的情况下，这是“除以零”和“除以null”之间的区别。而“除以零”操作会在sql_mode的某些设置中引发错误。如果我除以COUNT()，那么在进行除法之前，我需要将零转换为空。

   ... / NULLIF(COUNT(...),0)

或者更符合ansi标准

   ... / CASE WHEN COUNT(...) = 0 THEN NULL ELSE COUNT(...) END 

但是我们可以通过使用SUM(1)来避免这种繁琐，这样我们就没有任何特殊的处理“除以零”的情况了。但真正让我们信服的是，我们删除了对唯一列的引用。

然后，查询的“覆盖索引”只需要两列。

... ON `callinfo` (`date`,`seconds`)

(即EXPLAIN将在额外列中显示“使用索引”，并显示用于访问的“范围”)

而且，我也不想让我的大脑被CONVERT_TZ的需求所困扰。