sandynz opened a new issue #13407:
URL: https://github.com/apache/shardingsphere/issues/13407
## Feature Request
### Is your feature request related to a problem?
No
Environment:
ShardingSphere Proxy, 5.0.0.
Commit: bf143245090a3b18589402d81ccddc68eab2e5c7
`scaling` is not enabled in `server.yaml`.
Register center (ZooKeeper) path `/metadata/sharding_db/rules` value:
```
- !SHARDING
bindingTables:
- t_order,t_order_item
defaultDatabaseStrategy:
standard:
shardingAlgorithmName: database_inline
shardingColumn: user_id
defaultTableStrategy:
none: ''
keyGenerators:
snowflake:
props:
worker-id: 123
type: SNOWFLAKE
shardingAlgorithms:
database_inline:
props:
algorithm-expression: ds_${user_id % 2}
type: INLINE
t_order_inline:
props:
algorithm-expression: t_order_${order_id % 2}
type: INLINE
t_order_item_inline:
props:
algorithm-expression: t_order_item_${order_id % 2}
type: INLINE
tables:
t_order:
actualDataNodes: ds_${0..1}.t_order_${0..1}
keyGenerateStrategy:
column: order_id
keyGeneratorName: snowflake
logicTable: t_order
tableStrategy:
standard:
shardingAlgorithmName: t_order_inline
shardingColumn: order_id
t_order_item:
actualDataNodes: ds_${0..1}.t_order_item_${0..1}
keyGenerateStrategy:
column: order_item_id
keyGeneratorName: snowflake
logicTable: t_order_item
tableStrategy:
standard:
shardingAlgorithmName: t_order_item_inline
shardingColumn: order_id
```
### Describe the feature you would like.
Summary: For more convenient usage.
If I want to modify sharding algorithm of `t_order` and `t_order_item`,
since there's no `ALTER SHARDING ALGORITHM` statement, I tried `DROP SHARDING
ALGORITHM` first, it respond error message:
```
mysql> DROP SHARDING ALGORITHM t_order_inline;
ERROR 1116 (C1116): Sharding algorithms `[t_order_inline]` in schema
`sharding_db` are still in used.
```
I got a sugguestion from @RaigorJiang , and it works.
```
mysql> CREATE SHARDING ALGORITHM t_order_inline2 (
->
TYPE(NAME=INLINE,PROPERTIES("algorithm-expression"="t_order_${order_id % 4}"))
-> );
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE SHARDING ALGORITHM t_order_item_inline2 (
->
TYPE(NAME=INLINE,PROPERTIES("algorithm-expression"="t_order_item_${order_id %
4}"))
-> );
Query OK, 0 rows affected (0.01 sec)
mysql> ALTER SHARDING TABLE RULE t_order (
-> DATANODES("ds_${2..4}.t_order_${0..1}"),
->
DATABASE_STRATEGY(TYPE=standard,SHARDING_COLUMN=user_id,SHARDING_ALGORITHM=database_inline),
->
TABLE_STRATEGY(TYPE=standard,SHARDING_COLUMN=order_id,SHARDING_ALGORITHM=t_order_inline2),
->
GENERATED_KEY(COLUMN=order_id,TYPE(NAME=snowflake,PROPERTIES("worker-id"=123)))
-> ), t_order_item (
-> DATANODES("ds_${2..4}.t_order_item_${0..1}"),
->
DATABASE_STRATEGY(TYPE=standard,SHARDING_COLUMN=user_id,SHARDING_ALGORITHM=database_inline),
->
TABLE_STRATEGY(TYPE=standard,SHARDING_COLUMN=order_id,SHARDING_ALGORITHM=t_order_item_inline2),
->
GENERATED_KEY(COLUMN=order_item_id,TYPE(NAME=snowflake,PROPERTIES("worker-id"=123)))
-> );
Query OK, 0 rows affected (0.25 sec)
mysql> DROP SHARDING ALGORITHM t_order_inline;
Query OK, 0 rows affected (0.02 sec)
mysql> DROP SHARDING ALGORITHM t_order_item_inline;
Query OK, 0 rows affected (0.01 sec)
```
And @RaigorJiang memtioned `ALTER SHARDING ALGORITHM` too. If it could run
following DistSQL with no error message, it could be much more convenient for
users:
```
ALTER SHARDING ALGORITHM t_order_inline (
TYPE(NAME=INLINE,PROPERTIES("algorithm-expression"="t_order_${order_id %
4}"))
);
ALTER SHARDING ALGORITHM t_order_item_inline (
TYPE(NAME=INLINE,PROPERTIES("algorithm-expression"="t_order_item_${order_id
% 4}"))
);
```
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
