Gaea
Gaea copied to clipboard
XiaoMi
分库分表场景下，不支持跨分片insert，支持跨分片update和delete，这样是否合理？辛苦指导
//跨分片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)
