MySQL Forums :: Partitioning :: No other way than delete primary key to create partitions by single datetime columns?


Advanced Search

Re: No other way than delete primary key to create partitions by single datetime columns?
Posted by: Jonathan Stephens ()
Date: August 21, 2015 02:53AM

Every column referenced in a table's partitioning expression must be part of that table's PK/UKs. Since you've only the single PK, any columns used in your partitioning expression must also be part of that PK (and we needn't worry about it being part of any unique keys as well).

Make createDate part of the table's PK. Then you can use it in the partitioning expression, like so:

mysql> CREATE TABLE t1 (
    ->   id VARCHAR(64) NOT NULL,
    ->   createDate DATETIME DEFAULT NULL,
    ->   col3 INT(11) DEFAULT NULL,
    ->   col4 INT(11) DEFAULT NULL,
    ->   PRIMARY KEY (id, createDate)
    -> ) 
    -> PARTITION BY RANGE COLUMNS (createDate)(
    ->   PARTITION p20150601 VALUES LESS THAN ('2015-06-01'),
    ->   PARTITION p20150701 VALUES LESS THAN ('2015-07-01'),
    ->   PARTITION pmax VALUES LESS THAN (MAXVALUE)
    -> ); 
Query OK, 0 rows affected (0.74 sec)

As you can see, you do not need to include every column from the PK in the partitioning key.

See https://dev.mysql.com/doc/refman/5.6/en/partitioning-limitations-partitioning-keys-unique-keys.html for more info and examples.

Good luck!

Jon Stephens
MySQL Documentation Team @ Oracle
Stockholm, Sweden

MySQL Dev Zone
MySQL Server Documentation
Oracle



Edited 1 time(s). Last edit at 08/21/2015 03:59AM by Jonathan Stephens.

Options: ReplyQuote


Subject Views Written By Posted
No other way than delete primary key to create partitions by single datetime columns? 1251 Sam Young 08/20/2015 08:00PM
Re: No other way than delete primary key to create partitions by single datetime columns? 779 Jonathan Stephens 08/21/2015 02:53AM
Re: No other way than delete primary key to create partitions by single datetime columns? 690 Sam Young 08/23/2015 08:57PM
Re: No other way than delete primary key to create partitions by single datetime columns? 681 Rick James 08/22/2015 03:35PM
Re: No other way than delete primary key to create partitions by single datetime columns? 631 Sam Young 08/23/2015 09:23PM
Re: No other way than delete primary key to create partitions by single datetime columns? 605 Rick James 08/24/2015 12:36PM
Re: No other way than delete primary key to create partitions by single datetime columns? 671 Sam Young 08/26/2015 12:53AM
Re: No other way than delete primary key to create partitions by single datetime columns? 671 Rick James 08/26/2015 10:02AM
Re: No other way than delete primary key to create partitions by single datetime columns? 629 Sam Young 08/26/2015 07:42PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.