Let's have a simple table of products. Each produch has its unique ID and category. Users often search by category so I want to partition products by category. Each category in one partition e.g.
我们有一个简单的产品表。每个产品都有其独特的ID和类别。用户经常按类别搜索,因此我想按类别对产品进行分区。一个分区中的每个类别,
How do I do it? Because of course I have a primary key on my ID column and need my ID unique. Not unique in each category.
我该怎么做?因为我的ID列当然有一个主键,需要我的ID唯一。每个类别都不是唯一的。
However partitiong has this limitation that "every unique key on the table must use every column in the table's partitioning expression".
但是,partitiong具有这样的限制:“表中的每个唯一键必须使用表的分区表达式中的每一列”。
Well, doesn't this make partitioning a bit useless? Or am I missing something? What should I do?
那么,这不会使分区有点无用吗?或者我错过了什么?我该怎么办?
http://dev.mysql.com/doc/refman/5.1/en/partitioning-limitations-partitioning-keys-unique-keys.html
1 个解决方案
#1
10
The trick is to add the field category to your current primary key. (Your primary key will remain a primary key)
Then you can partition your table by category.
诀窍是将字段类别添加到当前主键。 (您的主键仍将是主键)然后您可以按类别对表进行分区。
Here is the code you may use:
以下是您可能使用的代码:
ALTER TABLE `products` DROP PRIMARY KEY , ADD PRIMARY KEY ( `id` , `category` );
ALTER TABLE `products` PARTITION BY KEY(category) PARTITIONS 6;
Add auto_increment option to the id if you want it to be really unique, and don't specify the id value when you insert data in the table. The id will be determined by the database server upon insertion.
如果您希望将auto_increment选项真正唯一,请在id中添加auto_increment选项,并且在表中插入数据时不要指定id值。 id将在插入时由数据库服务器确定。
Change field names and key names if necessary.
如有必要,请更改字段名称和键名称。
Documentation:
Partitioning types
KEY Partioning
文档:分区类型KEY Partioning
#1
10
The trick is to add the field category to your current primary key. (Your primary key will remain a primary key)
Then you can partition your table by category.
诀窍是将字段类别添加到当前主键。 (您的主键仍将是主键)然后您可以按类别对表进行分区。
Here is the code you may use:
以下是您可能使用的代码:
ALTER TABLE `products` DROP PRIMARY KEY , ADD PRIMARY KEY ( `id` , `category` );
ALTER TABLE `products` PARTITION BY KEY(category) PARTITIONS 6;
Add auto_increment option to the id if you want it to be really unique, and don't specify the id value when you insert data in the table. The id will be determined by the database server upon insertion.
如果您希望将auto_increment选项真正唯一,请在id中添加auto_increment选项,并且在表中插入数据时不要指定id值。 id将在插入时由数据库服务器确定。
Change field names and key names if necessary.
如有必要,请更改字段名称和键名称。
Documentation:
Partitioning types
KEY Partioning
文档:分区类型KEY Partioning