Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

分库分表场景下,不支持跨分片insert,支持跨分片update和delete,这样是否合理?辛苦指导 #228

Open
ximenluffy opened this issue Jun 15, 2022 · 0 comments

Comments

@ximenluffy
Copy link

//跨分片insert报错
mysql> insert into sbtest(id,k,c,pad) values(101,101,'aaa','aaa'),(102,102,'aaa','aaa'),(103,103,'aaa','aaa'),(104,104,'aaa','aaa'),(105,105,'aaa','aaa');
ERROR 1105 (HY000): unknown error: get plan error, db: dba_shard_test, sql: insert into sbtest(id,k,c,pad) values(101,101,'aaa','aaa'),(102,102,'aaa','aaa'),(103,103,'aaa','aaa'),(104,104,'aaa','aaa'),(105,105,'aaa','aaa'), err: create select plan error: handleInsertValues error: batch insert has cross slice values or no route found

// 但是支持跨分片update和delete
mysql> update sbtest set c='ccc' where k in (101,102,103,104,105);
Query OK, 5 rows affected (0.00 sec)

mysql> explain update sbtest set c='ddd' where k in (101,102,103,104,105);
+-------+---------+------------------+------------------------------------------------------+
| type | slice | db | sql |
+-------+---------+------------------+------------------------------------------------------+
| shard | slice-0 | dba_shard_test_0 | UPDATE sbtest SET c='ddd' WHERE k IN (101,104) |
| shard | slice-0 | dba_shard_test_1 | UPDATE sbtest SET c='ddd' WHERE k IN (102,103) |
| shard | slice-1 | dba_shard_test_3 | UPDATE sbtest SET c='ddd' WHERE k IN (105) |
+-------+---------+------------------+------------------------------------------------------+
3 rows in set (0.00 sec)

mysql> delete from sbtest where k in (101,102,103,104,105);
Query OK, 5 rows affected (0.00 sec)

mysql> explain delete from sbtest where k in (101,102,103,104,105);
+-------+---------+------------------+---------------------------------------------+
| type | slice | db | sql |
+-------+---------+------------------+---------------------------------------------+
| shard | slice-1 | dba_shard_test_3 | DELETE FROM sbtest WHERE k IN (105) |
| shard | slice-0 | dba_shard_test_1 | DELETE FROM sbtest WHERE k IN (102,103) |
| shard | slice-0 | dba_shard_test_0 | DELETE FROM sbtest WHERE k IN (101,104) |
+-------+---------+------------------+---------------------------------------------+
3 rows in set (0.00 sec)

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

No branches or pull requests

1 participant