使用JSON值在数据库表上添加分区

我有一个包含数百万行但没有分区的现有数据库。 我可以改变它来添加分区,但是,我想要添加分区的列包含JSON值,我需要先提取它。 </ p>

  ALTER TABLE api_backup_records 
PARTITION BY RANGE(CAST(JSON_EXTRACT(custom_data,'$ .customer_id')AS UNSIGNED))(
PARTITION cus10000 VALUES(10000)以下,\ nPARTITION cus20000值不超过(20000),
预测cus30000值不超过(30000),
预测cus40000值不超过(40000),
预测cus50000值小于(50000),
预测cus60000值小于(60000),\ nPARTITION cus70000值小于(70000),
预测cus80000值小于(80000),
预测cus90000值小于(90000),
预测cusmax值小于MAXVALUE)
</ code> </ pre>

n

我收到错误#1564 - 不允许使用此分区功能</ strong>,我认为这是因为分区中不允许使用JSON_EXTRACT函数。 我尝试通过添加一个基于JSON_EXTRACT的生成值的新列来更改表,但MongoDB不支持。 </ p>

我想轻松搜索客户ID(客户ID位于custom_data列的JSON值上[这是一个自定义数据,因此我们需要将其作为JSON保留,因为这样 将是一个动态列])记录分区。 </ p>

非常感谢您的帮助。 </ p>
</ div>

展开原文

原文

I have an existing database with millions of rows which don't have a partition. I can alter it to add partition, however, the column that I wanted to add partition with contains JSON value and I need to extract that first.

ALTER TABLE api_backup_records 
PARTITION BY RANGE(CAST(JSON_EXTRACT(custom_data, '$.customer_id') AS UNSIGNED))(
PARTITION cus10000 VALUES LESS THAN (10000),
PARTITION cus20000 VALUES LESS THAN (20000),
PARTITION cus30000 VALUES LESS THAN (30000),
PARTITION cus40000 VALUES LESS THAN (40000),
PARTITION cus50000 VALUES LESS THAN (50000),
PARTITION cus60000 VALUES LESS THAN (60000),
PARTITION cus70000 VALUES LESS THAN (70000),
PARTITION cus80000 VALUES LESS THAN (80000),
PARTITION cus90000 VALUES LESS THAN (90000),
PARTITION cusmax VALUES LESS THAN MAXVALUE)

I'm getting an error #1564 - This partition function is not allowed, of which I believe is because of the JSON_EXTRACT function which is not allowed in partitioning. I tried to alter the table by adding a new column with generated value based off of the JSON_EXTRACT, however that isn't supported on MongoDB.

I am wanting to easily search a Customer ID's (customer id being located on the JSON value of custom_data column [this is a custom data, so we need this to remain as a JSON since this will be a dynamic column]) record by partitioning it.

Thank you so much for helping.

ds465456465
ds465456465 是的,我已经阅读了限制并认为JSON_EXTRACT在限制范围内。我试图弄清楚是否还有其他办法。我也尝试创建一个带有生成值的新列(生成列),但是在MongoDB上还没有支持...谢谢伙伴..
2 年多之前 回复
drrog9853
drrog9853 您已经尝试了生成列的内容,请参阅13.1.18.8CREATETABLE和GeneratedColumns,请参阅dbfiddleforideas。
2 年多之前 回复
dtlrp119999
dtlrp119999 检查22.6分区的限制和限制以及22.6.3与功能相关的分区限制。
2 年多之前 回复
Csdn user default icon
上传中...
上传图片
插入图片
抄袭、复制答案,以达到刷声望分或其他目的的行为,在CSDN问答是严格禁止的,一经发现立刻封号。是时候展现真正的技术了!
立即提问
相关内容推荐