主页 / dba / 问题 / 12746
Accepted
Ran
Asked: 2012-02-14 01:35:48 +0800 CST

MySql - 按范围和唯一键分区

  • 772

我正在尝试创建下表：

CREATE TABLE `s_relations_with_partition` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `source_persona_id` int(11) NOT NULL,
  `relation_type` int(11) NOT NULL,
  `message_id` int(11) DEFAULT NULL,
  `reply_to_message_id` int(11) DEFAULT NULL,
  `reshare_of_message_id` int(11) DEFAULT NULL,
  `target_object_id` int(11) DEFAULT NULL,
  `target_persona_id` int(11) DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  `sent_at` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`,`sent_at`),
  UNIQUE KEY `unique_target_persona` (`source_persona_id`,`relation_type`,`message_id`,`target_persona_id`),
  UNIQUE KEY `unique_target_object` (`source_persona_id`,`relation_type`,`message_id`,`target_object_id`),
  KEY `message_id_index` (`message_id`),
  KEY `reshare_of_message_id_index` (`reshare_of_message_id`),
  KEY `reply_to_message_id_index` (`reply_to_message_id`),
  KEY `source_and_target_object_index` (`source_persona_id`,`target_object_id`),
  KEY `source_target_persona_index` (`source_persona_id`,`target_persona_id`),
  KEY `target_persona_relation_type_message_id_index` (`target_persona_id`,`relation_type`,`message_id`),
  KEY `sent_at_index` (`sent_at`),
  KEY `source_persona_sent_at_index` (`source_persona_id`,`sent_at`),
  KEY `target_persona_sent_at_index` (`target_persona_id`,`sent_at`),
  KEY `target_object_sent_at_index` (`target_object_id`,`sent_at`)
) ENGINE=InnoDB 
PARTITION BY RANGE (sent_at) (
    PARTITION p0 VALUES LESS THAN ( UNIX_TIMESTAMP('2010-01-01 00:00:00') ),
    PARTITION p1 VALUES LESS THAN ( UNIX_TIMESTAMP('2010-02-01 00:00:00') ),
    PARTITION p2 VALUES LESS THAN ( UNIX_TIMESTAMP('2010-03-01 00:00:00') ),
    PARTITION p3 VALUES LESS THAN ( UNIX_TIMESTAMP('2010-04-01 00:00:00') ),
    PARTITION p4 VALUES LESS THAN ( UNIX_TIMESTAMP('2010-05-01 00:00:00') ),
    PARTITION p5 VALUES LESS THAN ( UNIX_TIMESTAMP('2010-06-01 00:00:00') ),
    PARTITION p6 VALUES LESS THAN ( UNIX_TIMESTAMP('2010-07-01 00:00:00') ),
    PARTITION p7 VALUES LESS THAN ( UNIX_TIMESTAMP('2010-08-01 00:00:00') ),
    PARTITION p8 VALUES LESS THAN ( UNIX_TIMESTAMP('2010-09-01 00:00:00') ),
    PARTITION p9 VALUES LESS THAN ( UNIX_TIMESTAMP('2010-10-01 00:00:00') ),   
    PARTITION p10 VALUES LESS THAN (MAXVALUE)
);

我收到以下错误：

A UNIQUE INDEX must include all columns in the table's partitioning function

无法将sent_at（unix 时间戳）添加到唯一索引。

关于如何在该表上实现日期范围分区的任何想法？

mysql partitioning

1 个回答

  1. Best Answer

    然后错误消息本身就说明了问题。请阅读手册， http ://dev.mysql.com/doc/refman/5.1/en/partitioning-limitations-partitioning-keys-unique-k ...：

    “规则是：分区表的分区表达式中使用的所有列必须是该表可能具有的每个唯一键的一部分。简单来说，表上的每个唯一键都必须使用表的分区表达式中的每一列。”

    • 1

相关问题