使用来自变量的 unix_timestamp 进行 mysql 分区

Question

鉴于此:

delimiter //
create procedure setup()
begin
  declare d datetime;
  set d = rounddate(now());

  create table s_time (req_id int not null,
                       ser_id int not null,
                       hel_id int not null,
                       posted int unsigned not null,
                       completed int unsigned not null default 0
                      )
  partition by range (completed) (partition p0 values less than ( unix_timestamp(d) ),
                                  partition p1 values less than ( unix_timestamp(d + interval 1 day) )
                                 );
end//

我明白了:

错误 1064 (42000):不允许在(子)分区函数中使用常量、随机或时区相关的表达式

有什么方法可以让它工作，还是我必须使用硬编码字符串作为输入。即使用:unix_timestamp('2012-07-07 00:00:00')

Answer 1

为了使解决方案保持在完整的 sql 中，这就是我所发现的。

delimiter //
create procedure setup()
begin
  declare d, d2 int;
  set d = unix_timestamp();
  set d2 = unix_timestamp(now() + interval 1 day);

  create table s_time (req_id int not null,
                       ser_id int not null,
                       hel_id int not null,
                       posted int unsigned not null,
                       completed int unsigned not null default 0
                      );

  SET @stmt = concat('alter table s_time PARTITION BY RANGE (completed) (
                      partition p0 values less than (', d, '),
                      partition p1 values less than (', d2, '))');
  PREPARE pStmt FROM @stmt;
  EXECUTE pStmt;
  DEALLOCATE PREPARE pStmt;

end//
delimiter ;
call setup();