Mysql 分區分表遇到的問題

最新推荐文章于 2025-02-22 10:19:50 发布

losedguest

最新推荐文章于 2025-02-22 10:19:50 发布

阅读量1.5k

点赞数

CC 4.0 BY-SA版权

分类专栏： Mysql

本文链接：https://blog.youkuaiyun.com/losedguest/article/details/93470848

Mysql 专栏收录该内容

29 篇文章

订阅专栏

博客主要讲述了MySQL分区时遇到的问题。一是表的主键字段必须包含分区字段；二是按DECIMAL类型的mail_no字段分区时，Range仅支持整形数值；三是Range不支持cast函数，分区字段一般用int和日期类字符串，其他字段难处理。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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

错误的原因：表的主键字段(PRIMARY Key和Unique key)必须包含分区字段。为什么？

如下,表結構關鍵字段為uid和lremailstate_2,即lremailstate_2需要包含uid欄位

CREATE TABLE `lremailstate` (
  `uid` BIGINT(28) UNSIGNED NOT NULL AUTO_INCREMENT,
  `ie_ymd` VARCHAR(10) COLLATE utf8mb4_bin NOT NULL DEFAULT '',
  `ie_user` VARCHAR(50) COLLATE utf8mb4_bin NOT NULL DEFAULT '',
  `ie_time` VARCHAR(11) COLLATE utf8mb4_bin NOT NULL DEFAULT '',
  `ie_lymd` VARCHAR(10) COLLATE utf8mb4_bin NOT NULL DEFAULT '',
  `ie_luser` VARCHAR(50) COLLATE utf8mb4_bin NOT NULL DEFAULT '',
  `ie_ltime` VARCHAR(11) COLLATE utf8mb4_bin NOT NULL DEFAULT '',
  `mail_no` DECIMAL(28,0) NOT NULL DEFAULT 0,
  `mail_to` VARCHAR(50) COLLATE utf8mb4_bin NOT NULL DEFAULT '',
  `mail_state` VARCHAR(1) COLLATE utf8mb4_bin NOT NULL DEFAULT '',
  `user_no` VARCHAR(50) COLLATE utf8mb4_bin NOT NULL DEFAULT '',
  `mail_guid` VARCHAR(32) COLLATE utf8mb4_bin NOT NULL DEFAULT '',
  `log_time` VARCHAR(20) COLLATE utf8mb4_bin NOT NULL DEFAULT '',
  PRIMARY KEY (`uid`),
  UNIQUE KEY `lremailstate_2` (`mail_to`,`mail_no`,`mail_state`),
  KEY `lremailstate_3` (`mail_no`,`mail_to`,`mail_state`),
  KEY `lremailstate4` (`mail_state`,`mail_no`,`mail_to`),
  KEY `lremailstate5` (`mail_to`,`mail_guid`),
  KEY `lremailstate_1` (`mail_to`,`mail_state`,`mail_no`)
)
PARTITION BY RANGE (uid)
( PARTITION lremailstate0 VALUES LESS THAN (2000000),
PARTITION lremailstate1 VALUES LESS THAN (4000000),
PARTITION lremailstate2 VALUES LESS THAN (6000000),
PARTITION lremailstate3 VALUES LESS THAN (8000000),
PARTITION lremailstate4 VALUES LESS THAN (10000000),
PARTITION lremailstate5 VALUES LESS THAN maxvalue
);

2.Field 'mail_no' is of a not allowed type for this type of partitioning

想按照DECIMAL mail_no字段分區,Range支持整形數值

CREATE TABLE `lremailstate` (
  `uid` BIGINT(28) UNSIGNED NOT NULL AUTO_INCREMENT,
  `ie_ymd` VARCHAR(10) COLLATE utf8mb4_bin NOT NULL DEFAULT '',
  `ie_user` VARCHAR(50) COLLATE utf8mb4_bin NOT NULL DEFAULT '',
  `ie_time` VARCHAR(11) COLLATE utf8mb4_bin NOT NULL DEFAULT '',
  `ie_lymd` VARCHAR(10) COLLATE utf8mb4_bin NOT NULL DEFAULT '',
  `ie_luser` VARCHAR(50) COLLATE utf8mb4_bin NOT NULL DEFAULT '',
  `ie_ltime` VARCHAR(11) COLLATE utf8mb4_bin NOT NULL DEFAULT '',
  `mail_no` DECIMAL(28,0) NOT NULL DEFAULT 0,
  `mail_to` VARCHAR(50) COLLATE utf8mb4_bin NOT NULL DEFAULT '',
  `mail_state` VARCHAR(1) COLLATE utf8mb4_bin NOT NULL DEFAULT '',
  `user_no` VARCHAR(50) COLLATE utf8mb4_bin NOT NULL DEFAULT '',
  `mail_guid` VARCHAR(32) COLLATE utf8mb4_bin NOT NULL DEFAULT '',
  `log_time` VARCHAR(20) COLLATE utf8mb4_bin NOT NULL DEFAULT '',
  PRIMARY KEY (`uid`),
  UNIQUE KEY `lremailstate_2` (`mail_to`,`mail_no`,`mail_state`),
  KEY `lremailstate_3` (`mail_no`,`mail_to`,`mail_state`),
  KEY `lremailstate4` (`mail_state`,`mail_no`,`mail_to`),
  KEY `lremailstate5` (`mail_to`,`mail_guid`),
  KEY `lremailstate_1` (`mail_to`,`mail_state`,`mail_no`)
)
PARTITION BY RANGE (mail_no)
( PARTITION lremailstate0 VALUES LESS THAN (2000000),
PARTITION lremailstate1 VALUES LESS THAN (4000000),
PARTITION lremailstate2 VALUES LESS THAN (6000000),
PARTITION lremailstate3 VALUES LESS THAN (8000000),
PARTITION lremailstate4 VALUES LESS THAN (10000000),
PARTITION lremailstate5 VALUES LESS THAN maxvalue
);

3.This partition function is not allowed

Range不支持cast函數 ,參考https://blog.youkuaiyun.com/cleanfield/article/details/41011765

分區字段一般用int,和日期類字符串,其他字段很難處理

CREATE TABLE `lremailstate` (
  `uid` BIGINT(28) UNSIGNED NOT NULL AUTO_INCREMENT,
  `ie_ymd` VARCHAR(10) COLLATE utf8mb4_bin NOT NULL DEFAULT '',
  `ie_user` VARCHAR(50) COLLATE utf8mb4_bin NOT NULL DEFAULT '',
  `ie_time` VARCHAR(11) COLLATE utf8mb4_bin NOT NULL DEFAULT '',
  `ie_lymd` VARCHAR(10) COLLATE utf8mb4_bin NOT NULL DEFAULT '',
  `ie_luser` VARCHAR(50) COLLATE utf8mb4_bin NOT NULL DEFAULT '',
  `ie_ltime` VARCHAR(11) COLLATE utf8mb4_bin NOT NULL DEFAULT '',
  `mail_no` DECIMAL(28,0) NOT NULL DEFAULT 0,
  `mail_to` VARCHAR(50) COLLATE utf8mb4_bin NOT NULL DEFAULT '',
  `mail_state` VARCHAR(1) COLLATE utf8mb4_bin NOT NULL DEFAULT '',
  `user_no` VARCHAR(50) COLLATE utf8mb4_bin NOT NULL DEFAULT '',
  `mail_guid` VARCHAR(32) COLLATE utf8mb4_bin NOT NULL DEFAULT '',
  `log_time` VARCHAR(20) COLLATE utf8mb4_bin NOT NULL DEFAULT '',
  PRIMARY KEY (`uid`),
  UNIQUE KEY `lremailstate_2` (`mail_to`,`mail_no`,`mail_state`),
  KEY `lremailstate_3` (`mail_no`,`mail_to`,`mail_state`),
  KEY `lremailstate4` (`mail_state`,`mail_no`,`mail_to`),
  KEY `lremailstate5` (`mail_to`,`mail_guid`),
  KEY `lremailstate_1` (`mail_to`,`mail_state`,`mail_no`)
)
PARTITION BY RANGE (CAST(mail_no AS SIGNED))
( PARTITION lremailstate0 VALUES LESS THAN (2000000),
PARTITION lremailstate1 VALUES LESS THAN (4000000),
PARTITION lremailstate2 VALUES LESS THAN (6000000),
PARTITION lremailstate3 VALUES LESS THAN (8000000),
PARTITION lremailstate4 VALUES LESS THAN (10000000),
PARTITION lremailstate5 VALUES LESS THAN maxvalue
);