基于年份在MYISAM中为mysql中的表分区

I am trying to partition the table based on the year range.

我正在尝试根据年份范围对表进行分区。

but i'm getting the following error "#1503 - A PRIMARY KEY must include all columns in the table's partitioning function".

但是我收到以下错误“#1503 - PRIMARY KEY必须包括表格分区功能中的所有列”。

Following is my query

以下是我的查询

CREATE TABLE IF NOT EXISTS `t2123` (
  `j_id` int(11) NOT NULL AUTO_INCREMENT,
  `u_id` int(11) NOT NULL,
  `w_id` int(5) NOT NULL,
  `p_id` int(5) NOT NULL,
  `s_p_id` int(5) NOT NULL,
  `p_t` tinyint(3) unsigned NOT NULL,
  `a_n` int(5) NOT NULL,    
  `type` enum('GP','NGP','PGP','PAGP') NOT NULL,
  `p_p` int(11) NOT NULL,
  `s_p` int(11) NOT NULL,
  `p_c` float NOT NULL,
  `s_c` float NOT NULL,
  `s_p_p_c` float NOT NULL DEFAULT '0',
  `g_d` date NOT NULL,
 `datetimes` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00',
  `c_c` double  DEFAULT '0',
  `c_m` double DEFAULT '0',
  `c_y` double  DEFAULT '0',
  `c_k` double  DEFAULT '0' ,
  `c_total` double  DEFAULT '0' ,
  `p_a` float  DEFAULT '0',
  PRIMARY KEY (`j_id`),
  UNIQUE KEY(j_id, g_d),

  KEY `u_id` (`u_id`),
  KEY `w_id` (`w_id`),
  KEY `p_id` (`p_id`),
  KEY `s_p_id` (`s_p_id`),
  KEY `a_n` (`a_n`),
  KEY `g_d` (`g_d`)  
) engine=myisam  PARTITION BY RANGE  (j_id + year(g_d)) (
    PARTITION t2_2012 VALUES LESS THAN (2012),
    PARTITION t2_2013 VALUES LESS THAN (2013)
);

Please suggest me how can I create the partition?

请建议我如何创建分区?

1 个解决方案

#1

As the error message advises:

正如错误消息建议:

A PRIMARY KEY must include all columns in the table's partitioning function

PRIMARY KEY必须包含表的分区功能中的所有列

Your partitioning function refers to j_id and g_d while your primary key only covers j_id.

您的分区功能是指j_id和g_d,而主键仅涵盖j_id。

By the way, your UNIQUE constraint is useless (UNIQUE KEY(j_id, g_d)) because j_id is already unique by definition (it is primary key).

顺便说一句,你的UNIQUE约束是无用的(UNIQUE KEY(j_id,g_d))因为j_id根据定义已经是唯一的(它是主键)。

In fact you probably want your primary key on (j_id, g_d)

实际上你可能想要你的主键(j_id,g_d)

#1