maria-discuss team mailing list archive

[Justin Swanhart <greenlion@xxxxxxxxx>]

You can use no primary key, and just put a regular key (not unique key) on the auto_increment column.  You must make sure that manual insertions don't create a duplicate if value.  

>> CREATE TABLE new (
>> id INT NOT NULL AUTO_INCREMENT,
>> name VARCHAR(50),
>> added DATE,
>> KEY (id),
>> )
>> PARTITION BY RANGE( TO_DAYS(added) )
>> ( PARTITION p0 VALUES LESS THAN (1990),
>> PARTITION p1 VALUES LESS THAN (2000),
>> PARTITION p2 VALUES LESS THAN MAXVALUE
>> ) ;
>> 

Look at RANGE COLUMNS for a more natural way to partition by date:
>> CREATE TABLE new (
>> id INT NOT NULL AUTO_INCREMENT,
>> name VARCHAR(50),
>> added DATE,
>> PRIMARY KEY (id),
>> )
>> PARTITION BY RANGE COLUMNS (added)
>> ( PARTITION p0 VALUES LESS THAN ('1990-01-01'),
>> ....
>> PARTITION pmax VALUES LESS THAN MAXVALUE
>> ) ;
Sent from my iPhone

> On Mar 12, 2016, at 5:16 AM, Guillaume Lefranc <guillaume.lefranc@xxxxxxxxxxx> wrote:
> 
> The only solution is to make (id, added) a PK, because the partitioning column must always belong to unique key, if there is such.
> In your case I would change added type to DATETIME to avoid collision.
> 
> Regards
> 
>> On Sat, Mar 12, 2016 at 2:10 PM Ghazi Btissam <btissam.ghazi@xxxxxxxxx> wrote:
>> Hi,
>> 
>> 1- I have a table with a unique key which is calculated by the AUTO_INCREMENT feature, and I need to range partition it using a date field , is it possible and/or how to do it?
>> 
>> 2- Is there a way to implement sequences for the primary key (like it exists in Oracle) or the AUTO_INCREMENT is the only one in MariaDb?
>> 
>> For example:
>> 
>>  CREATE TABLE new (
>>          id INT NOT NULL AUTO_INCREMENT,
>>          name VARCHAR(50),
>>         added DATE,
>>          PRIMARY KEY (id),
>>      )     
>>     PARTITION BY RANGE( TO_DAYS(added) ) 
>>   ( PARTITION p0 VALUES LESS THAN (1990),
>>    PARTITION p1 VALUES LESS THAN (2000),
>>    PARTITION p2 VALUES LESS THAN MAXVALUE  
>> 	 ) ;     
>> Thanks
>> 
>> _______________________________________________
>> Mailing list: https://launchpad.net/~maria-discuss
>> Post to     : maria-discuss@xxxxxxxxxxxxxxxxxxx
>> Unsubscribe : https://launchpad.net/~maria-discuss
>> More help   : https://help.launchpad.net/ListHelp
> 
> -- 
> Guillaume Lefranc
> Remote DBA Services Manager
> MariaDB Corporation
> _______________________________________________
> Mailing list: https://launchpad.net/~maria-discuss
> Post to     : maria-discuss@xxxxxxxxxxxxxxxxxxx
> Unsubscribe : https://launchpad.net/~maria-discuss
> More help   : https://help.launchpad.net/ListHelp