Mysql表分区

发布于 2024-12-02 03:37:47 字数 392 浏览 2 评论 0原文

我正在尝试创建分区但未能成功。这是我的表结构

CREATE  TABLE `bb2`.`new_table` (

  `id` INT NOT NULL AUTO_INCREMENT ,

  `dt` DATE NOt NULL ,

  PRIMARY KEY (`id`) ,

  UNIQUE INDEX `date_UNIQUE` (`dt`) ) 

partition by range (to_days(dt))(
partition p0 values less than ( to_days('2011-01-01') ),
partition p1 values less than MAXVALUE
)

您知道如何在不删除主表结构或更改表结构的情况下创建任何类型的分区吗？

原文

I am trying to create partition but couldn't succeeded.
Here is my table structure

CREATE  TABLE `bb2`.`new_table` (

  `id` INT NOT NULL AUTO_INCREMENT ,

  `dt` DATE NOt NULL ,

  PRIMARY KEY (`id`) ,

  UNIQUE INDEX `date_UNIQUE` (`dt`) ) 

partition by range (to_days(dt))(
partition p0 values less than ( to_days('2011-01-01') ),
partition p1 values less than MAXVALUE
)

Have u any idea how can I create any type of partition without removing primary or changing table structure.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

梦萦几度 2024-12-09 03:37:47

主键必须包含分区中使用的所有列。请参阅此处。

这可行：

CREATE  TABLE `test`.`new_table2` (
  `id` INT NOT NULL AUTO_INCREMENT ,
  `dt` DATE NOt NULL ,
  PRIMARY KEY (`id`, `dt`) , <-- updated primary key
  UNIQUE INDEX `date_UNIQUE` (`dt`) ) 
     partition by range (to_days(dt))(
     partition p0 values less than ( to_days('2011-01-01') ),
     partition p1 values less than MAXVALUE
)

它通过增加主键来保持表结构相同。

A primary key must include all columns used in your partitions. See here.

This would work:

CREATE  TABLE `test`.`new_table2` (
  `id` INT NOT NULL AUTO_INCREMENT ,
  `dt` DATE NOt NULL ,
  PRIMARY KEY (`id`, `dt`) , <-- updated primary key
  UNIQUE INDEX `date_UNIQUE` (`dt`) ) 
     partition by range (to_days(dt))(
     partition p0 values less than ( to_days('2011-01-01') ),
     partition p1 values less than MAXVALUE
)

It keeps your table structure the same by augmenting the primary key.

回复收藏 0 原文

~没有更多了~