mysql - 尝试编写一个带有开始时间和结束时间的存储过程

Question

我有一个包含这些表的数据库:

drop table if exists events1;
create table if not exists events1(
    time_stamp          decimal(16,6),     
    message             char(90)        not null default 'defult message'
);

drop table if exists events2;
create table if not exists events2(
    time_stamp          decimal(16,6),     
    message             char(90)        not null default 'defult message'
);

drop table if exists events3;
create table if not exists events3(
    time_stamp          decimal(16,6),     
    message             char(90)        not null default 'defult message'
);

insert into events1 (time_stamp,message) values (1485193300,"a1");
insert into events1 (time_stamp,message) values (1485193600,"b1");
insert into events1 (time_stamp,message) values (1485193900,"c1");

insert into events2 (time_stamp,message) values (1485193300,"a1");
insert into events2 (time_stamp,message) values (1485193600,"b2");
insert into events2 (time_stamp,message) values (1485193900,"c3");

insert into events3 (time_stamp,message) values (1485193300,"a1");
insert into events3 (time_stamp,message) values (1485193600,"b2");
insert into events3 (time_stamp,message) values (1485193900,"c3");

我正在尝试构建一个具有 3 个参数的存储过程:

start_time (int) (in epoch) 
end_time   (int) (in epoch) 
interval   (int) (in epoch) 

我不希望这个过程将迭代start_time，以interval为步长直到end_time，并在每次迭代时获得三个表的并集，根据步长和间隔。

类似于:

DELIMITER //
CREATE PROCEDURE get_events
(IN start_time INT,
 IN end_time INT,
 IN interval INT,
)
BEGIN
      while [ start_time <= end_time]
          do
             next_time = start_time + interval
             select * 
             from  events1
             where start_time <= time_stamp and time_stamp <= next_time

             union 

             select * 
             from  events2
             where start_time <= time_stamp and time_stamp <= next_time

             union 

             select * 
             from  events3
             where start_time <= time_stamp and time_stamp <= next_time

             start_time = start_time + interval


      end while

END //
DELIMITER ;

我知道我有语法错误，我不知道如何返回结果并在 WHERE 情况下以给定间隔迭代 3 个表的并集。

请指教。

Answer 1

什么是语法错误？

[ 语法无效；也许(在WHILE？

a = a + b 语法无效；请参阅 SET 命令。

拥有 3 个具有相同架构的表通常是一个糟糕的设计。

使用CHAR而不是VARCHAR通常是一个坏主意。