MySQL Forums
Forum List  »  Partitioning

Re: Why partition can not support auto_increment field now?
Posted by: Scott Noyes
Date: October 08, 2008 09:33AM

Glenn,

What you're seeing is a documented feature.

"For MyISAM tables you can specify AUTO_INCREMENT on a secondary column in a multiple-column index. In this case, the generated value for the AUTO_INCREMENT column is calculated as MAX(auto_increment_column) + 1 WHERE prefix=given-prefix. This is useful when you want to put data into ordered groups."

http://dev.mysql.com/doc/refman/5.0/en/example-auto-increment.html

To get the behavior you expected, just swap the fields in your primary key:

CONSTRAINT PK_t1 PRIMARY KEY (id, currTime)

Or, since auto_increment doesn't actually require a PRIMARY or even a UNIQUE key, don't include the id in the primary key:

CREATE TABLE t1 (
id INT NOT NULL AUTO_INCREMENT,
currTime DATETIME,
PRIMARY KEY (currTime),
INDEX(id)
) ENGINE = myISAM
PARTITION BY LINEAR KEY(currTime)...

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Why partition can not support auto_increment field now?
3849
October 08, 2008 09:33AM


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.