Geek asks and answers

Mysql splitting with unix_timestamp from variable

Considering this:

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

I get:

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

Is there a way to make this work, or do I need to use a gated string for input. those. use:unix_timestamp('2012-07-07 00:00:00')

+5
source share
2 answers

To maintain the solution in full sql, this is what I 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();
+5
source

The reason for this does not work is not so clear, I suspect an error in the documentation or in the error message. The error you get is inappropriate in my opinion.

According to the manual:

The following invalid constructs when splitting an expression:

  • Stored procedures, stored functions, UDF or plugins.
  • Declared variables or user variables.

, .

, ? :

CREATE TABLE s_time (
    completed INT UNSIGNED NOT NULL DEFAULT 0
)
PARTITION BY RANGE ( completed  ) (
    PARTITION p0 VALUES LESS THAN ( UNIX_TIMESTAMP() )
);

. MySQL :

VALUES LESS THAN. MySQL LESS THAN (<).

UNIX_TIMESTAMP() . , . , - .

, , LESS THAN " ", :

1064 (42000): , (sub) LESS THAN

"" , UNIX_TIMESTAMP().

, , , script ALTER TABLE.

1) :

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 (0),
    PARTITION p1 VALUES LESS THAN (1)
);

2) (, PHP):

<?php
    $query = 
        "ALTER TABLE s_time REORGANIZE PARTITION p0, p1 INTO (
            PARTITION p0 VALUES LESS THAN ($today),
            PARTITION p1 VALUES LESS THAN ($tomorrow)
        )";

.

+2

More articles:


All Articles