非分区表转换成分区表：range出错

OceanBase
,
#1

【 使用环境 】生产环境
【 OB or 其他组件 】
【 使用版本 】5.7.25-OceanBase_CE-v4.3.0.1
【问题描述】需要将一张非分区表改为range一级分区
【表结构】
CREATE TABLE memberconsumptionorders_copy1 (
Id int(11) NOT NULL AUTO_INCREMENT,
TenantId int(11) DEFAULT NULL,
OrderCode varchar(50) DEFAULT NULL,
ReceiptCode varchar(50) DEFAULT NULL,
TerminalNo longtext DEFAULT NULL,
CreatorTime datetime(6) NOT NULL,
OrganizationId char(36) NOT NULL,
OrganizationCode longtext DEFAULT NULL,
OrganizationName longtext DEFAULT NULL,
TransactionState int(11) NOT NULL,
CasherId char(36) NOT NULL,
CasherCode longtext DEFAULT NULL,
CasherName longtext DEFAULT NULL,
OrderAmount decimal(65,30) NOT NULL,
PayAmount decimal(65,30) NOT NULL,
DiscountsAmount decimal(65,30) NOT NULL,
CRMAmount decimal(65,30) NOT NULL,
MemberId char(36) DEFAULT NULL,
MemberName longtext DEFAULT NULL,
MemberCode varchar(50) DEFAULT NULL,
MemberPhone longtext DEFAULT NULL,
OrderSource int(11) NOT NULL,
ThirdpartyOrderCode longtext DEFAULT NULL,
ReturnOrderCode longtext DEFAULT NULL,
SnNo longtext DEFAULT NULL,
MerchantName longtext DEFAULT NULL,
MerchantId char(36) DEFAULT NULL,
MerchantCode longtext DEFAULT NULL,
ConsumptionOrderSource int(11) NOT NULL,
ThirdOrderTime datetime(6) DEFAULT NULL,
OrderScore decimal(65,30) NOT NULL,
PayScore decimal(65,30) NOT NULL,
Freight decimal(18,4) NOT NULL,
DepositOrderCode longtext DEFAULT NULL,
DepositOrderId int(11) DEFAULT NULL,
ArrearsState int(11) NOT NULL,
ConsumptionSaleOrderId int(11) DEFAULT NULL,
MarketingReturned tinyint(1) NOT NULL DEFAULT ‘0’,
PRIMARY KEY (Id, CreatorTime),
KEY IX_MemberConsumptionOrders_CreatorTime (CreatorTime) BLOCK_SIZE 16384 LOCAL,
KEY IX_MemberConsumptionOrders_MemberId (MemberId) BLOCK_SIZE 16384 LOCAL,
KEY IX_MemberConsumptionOrders_OrderCode (OrderCode) BLOCK_SIZE 16384 LOCAL,
KEY IX_MemberConsumptionOrders_OrganizationId (OrganizationId) BLOCK_SIZE 16384 LOCAL,
KEY IX_MemberConsumptionOrders_ReceiptCode (ReceiptCode) BLOCK_SIZE 16384 LOCAL
) AUTO_INCREMENT = 3020744 AUTO_INCREMENT_MODE = ‘ORDER’ DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = ‘zstd_1.3.8’ REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0

【执行分区语句】

ALTER TABLE memberconsumptionorders_copy1 PARTITION BY RANGE(UNIX_TIMESTAMP(CreatorTime))
(
PARTITION M1 VALUES LESS THAN(UNIX_TIMESTAMP(‘2023-01-01’)),
PARTITION M2 VALUES LESS THAN(UNIX_TIMESTAMP(‘2024-01-01’)),
PARTITION M3 VALUES LESS THAN(UNIX_TIMESTAMP(‘2025-01-01’))
);

【报错信息】
1486 - Constant or random or timezone-dependent expressions in (sub)partitioning function are not allowed

#3

您是按照那个文档：进行先创造 普通表 然后在进行改造成分区表的 ？？？

#4

这个表已经有将近一个亿的数据了，查询慢才想到要变更成分区表的

【用户实践】分区策略和管理分区计划的实践方案
#5

可以看看这个帖，目前应该是不支持这种用法

#6

那我现在面对这个大表想做成分区表，有没有什么其他办法呢，无解麽？

#8

ALTER TABLE memberconsumptionorders_copy1 PARTITION BY RANGE COLUMNS(CreatorTime)
(
PARTITION M1 VALUES LESS THAN (‘2023-01-01 00:00:00’),
PARTITION M2 VALUES LESS THAN (‘2024-01-01 00:00:00’),
PARTITION M3 VALUES LESS THAN MAXVALUE
);
你好，测试了一下使用这种方式创建成功了，建议你参考在测试环境先试试。

image
image846×155 42.8 KB

#9

定义：

Range 分区是最常见的分区类型，通常与日期一起使用。在进行 Range 分区时，数据库根据分区键的值范围将行映射到分区。

特点：

  • Range 分区的分区键只支持一列，并且只支持 INT 类型或 YEAR 类型
  • 如果要支持多列的分区键，或者其他数据类型，可以使用 Range Columns 分区。
  • RANGE分区可以新增、删除分区。如果最后一个 RANGE 分区指定了 MAXVALUE ，则不能新增分区。

#当使用 RANGE 分区时，需要遵守如下几个规则：

  • PARTITION BY RANGE ( expr ) 里的 expr 表达式的结果必须为整形。
  • 每个分区都有一个 VALUES LESS THAN 子句，它为分区指定一个非包含的上限值。分区键的任何值等于或大于这个值时将被映射到下一个分区中。
  • 除第一个分区外，所有分区都隐含一个下限值，即上一个分区的上限值。
  • 允许且只允许最后一个分区上限定义为 MAXVALUE ，这个值没有具体的数值，比其他所有分区的上限都要大，也包含空值。
  • RANGE分区可以新增、删除分区。如果最后一个 RANGE 分区指定了 MAXVALUE ，则不能新增分区。
  • RANGE 分区要求表拆分键表达式的结果必须为整型，如果要按时间类型列做 RANGE 分区，则必须使用 timestamp 类型，并且使用函数 UNIX_TIMESTAMP 将时间类型转换为数值。这个需求也可以使用 RANGE COLUMNS 分区实现，就没有整型这个要求。
#10

image
image1794×1116 110 KB

image
image1722×1016 254 KB

–参考 OceanBase 社区

1 个赞
#11

哈哈，用 to_days 代替 UNIX_TIMESTAMP 这个方法不错~

#12

您的分区语句中使用了UNIX_TIMESTAMP(CreatorTime)作为分区键，这在大多数情况下是合法的，因为它基于表中的列值CreatorTime，这通常是一个确定性的值。然而，错误1486通常意味着分区表达式中包含了不允许的元素，如随机函数或时区依赖的函数。

在您的情况下，问题可能不在于分区键本身，而是可能与CreatorTime列的数据类型或MySQL服务器的时区设置有关。如果CreatorTime列是DATETIME或TIMESTAMP类型，并且包含时区信息，那么在不同的时区下，UNIX_TIMESTAMP函数的输出可能会有所不同，这可能导致分区行为的不一致。

为了解决这个问题，您可以：

确保CreatorTime列不包含时区信息**：如果CreatorTime列是TIMESTAMP类型，它会自动调整为服务器的时区。您可以考虑将其转换为DATETIME类型，或者在计算UNIX_TIMESTAMP时使用UTC_TIMESTAMP函数，例如UNIX_TIMESTAMP(DATE_SUB(UTC_TIMESTAMP(), INTERVAL TIMESTAMPDIFF(HOUR, '1970-01-01 00:00:00', CreatorTime) HOUR))。

#13

这个问题解决了吗

#14

解决了，感谢