maria-discuss team mailing list archive

[Justin Swanhart <greenlion@xxxxxxxxx>]

Oops.  PRIMARY KEY should be just KEY on that second example with RANGE COLUMNS.  :)

Sent from my iPhone

> On Mar 12, 2016, at 11:01 AM, Justin Swanhart <greenlion@xxxxxxxxx> wrote:
> 
> 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