使用unix_timestamp从变量进行mysql分区 [英] mysql partitioning with unix_timestamp from variable

问题描述

给出这个:

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//

我得到了:

ERROR 1064 (42000) : Constant, random, or timezone-dependent expression in (sub)partitioning function are not allowed

是否有任何方法可以使它正常工作，或者我必须对输入使用硬编码的字符串.即使用:unix_timestamp('2012-07-07 00:00:00')

Is there any way to get this to work, or do I have to use a hard-coded string for the input. ie use : unix_timestamp('2012-07-07 00:00:00')

推荐答案

要使解决方案保持完整的sql，这就是我所发现的.

To keep the solution in full sql this is what I have found.

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();

这篇关于使用unix_timestamp从变量进行mysql分区的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！