Mysql 分區分表遇到的問題

淡淡的烟草味﹌ 2022-01-06 01:35 256阅读 0赞

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.csdn.net/cleanfield/article/details/41011765][https_blog.csdn.net_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
    );

[https_blog.csdn.net_cleanfield_article_details_41011765]: https://blog.csdn.net/cleanfield/article/details/41011765