ShardingSphere 针对SQL IN语法问题小记

怼烎@ 2023-02-13 03:37 2阅读 0赞

## ShardingSphere支持IN语法查询，但也存在问题。 ##

> 官网说明：https://shardingsphere.apache.org/document/current/cn/features/sharding/concept/sharding/

**当查询多条数据时，入参为分片键，进行测试**

*  当数据路由都在一个库同一张表时，会去相应表进行查询（分片键：1504, 1508）

2020-05-26 22:05:01.331  INFO 6476 --- [nio-8080-exec-8] ShardingSphere-SQL    ：
    Logic SQL: SELECT id, last_depot_id, current_depot_id, sku_id, entry_bill_id, entry_time, stock_status, entry_type,
               type, created_by, created_time, last_modified_by, last_modified_time, remarks, enabled, version, tenant_id
           	   FROM art_article WHERE enabled = 1 AND id IN(   ? ,  ? )
    
    2020-05-26 22:05:01.332  INFO 16076 --- [nio-8080-exec-2] ShardingSphere-SQL    : 
    Actual SQL: 
    article0 ::: SELECT id, last_depot_id, current_depot_id, sku_id, entry_bill_id, entry_time, stock_status, entry_type,
                 type, created_by, created_time, last_modified_by, last_modified_time, remarks, enabled, version, tenant_id
                 FROM art_article_0 WHERE enabled = 1 AND id IN (   ? , ? ) ::: [1504, 1508]

*  当数据路由在同一个库不同表时，会去相应表进行查询 （分片键：1504, 1507）

2020-05-27 09:02:32.336  INFO 10660 --- [nio-8080-exec-5] ShardingSphere-SQL   : 
    Logic SQL:  SELECT id, last_depot_id, current_depot_id, sku_id, entry_bill_id, entry_time, stock_status, entry_type,
                type, created_by, created_time, last_modified_by, last_modified_time, remarks, enabled, version, tenant_id
            	FROM art_article WHERE enabled = 1 AND id IN (   ? ,  ? )
    
    2020-05-27 09:02:32.337  INFO 10660 --- [nio-8080-exec-5] ShardingSphere-SQL   : 
    Actual SQL: 
    article0 ::: SELECT id, last_depot_id, current_depot_id, sku_id, entry_bill_id, entry_time, stock_status, entry_type, 		  
                 type, created_by, created_time, last_modified_by, last_modified_time, remarks, enabled, version, tenant_id
                 FROM art_article_0 WHERE enabled = 1 AND id IN (   ? ,  ? ) ::: [1504, 1507]
    2020-05-27 09:02:32.337  INFO 10660 --- [nio-8080-exec-5] ShardingSphere-SQL     : 
    Actual SQL: 
    article1 ::: SELECT id, last_depot_id, current_depot_id, sku_id, entry_bill_id, entry_time, stock_status, entry_type,
                 type, created_by, created_time, last_modified_by, last_modified_time, remarks, enabled, version, tenant_id
               	 FROM art_article_0  WHERE enabled = 1 AND id IN (   ? ,  ? ) ::: [1504, 1507]

*  当数据路由在不同库的相同表号时，会去相应表进行查询（分片键：1504, 1506）

2020-05-27 09:01:17.559  INFO 10660 --- [nio-8080-exec-5] ShardingSphere-SQL   : 
    Logic SQL:  SELECT id, last_depot_id, current_depot_id, sku_id, entry_bill_id, entry_time, stock_status, entry_type,
                type, created_by, created_time, last_modified_by, last_modified_time, remarks, enabled, version, tenant_id
            	FROM art_article WHERE enabled = 1 AND id IN (   ? ,  ? )
    
    2020-05-27 09:01:17.560  INFO 10660 --- [nio-8080-exec-5] ShardingSphere-SQL   : 
    Actual SQL: 
    article0 ::: SELECT id, last_depot_id, current_depot_id, sku_id, entry_bill_id, entry_time, stock_status, entry_type, 		  
                 type, created_by, created_time, last_modified_by, last_modified_time, remarks, enabled, version, tenant_id
                 FROM art_article_0 WHERE enabled = 1 AND id IN (   ? ,  ? ) ::: [1504, 1506]
    2020-05-27 09:01:17.560  INFO 10660 --- [nio-8080-exec-5] ShardingSphere-SQL     : 
    Actual SQL: 
    article1 ::: SELECT id, last_depot_id, current_depot_id, sku_id, entry_bill_id, entry_time, stock_status, entry_type,
                 type, created_by, created_time, last_modified_by, last_modified_time, remarks, enabled, version, tenant_id
               	 FROM art_article_0  WHERE enabled = 1 AND id IN (   ? ,  ? ) ::: [1504, 1506]

*  当数据路由跨库时，且表号不同时，会轮询所有库的所有表进行查询（分片键：1504, 1505）

2020-05-26 22:05:01.331  INFO 6476 --- [nio-8080-exec-8] ShardingSphere-SQL  
    Logic SQL: SELECT id, last_depot_id, current_depot_id, sku_id, entry_bill_id, entry_time, stock_status, entry_type,
               type, created_by, created_time, last_modified_by, last_modified_time, remarks, enabled, version, tenant_id
           	   FROM art_article WHERE enabled = 1 AND id IN(   ? ,  ? )
    
    2020-05-26 22:05:01.332  INFO 16076 --- [nio-8080-exec-2] ShardingSphere-SQL                       : 
    Actual SQL: 
    article0 ::: SELECT id, last_depot_id, current_depot_id, sku_id, entry_bill_id, entry_time, stock_status, entry_type,
                 type, created_by, created_time, last_modified_by, last_modified_time, remarks, enabled, version, tenant_id
                 FROM art_article_0 WHERE enabled = 1 AND id IN (   ? , ? ) ::: [1504, 1505]
    
    Actual SQL: 
    article0 ::: SELECT id, last_depot_id, current_depot_id, sku_id, entry_bill_id, entry_time, stock_status, entry_type,      
        		 type, created_by, created_time, last_modified_by, last_modified_time, remarks, enabled, version, tenant_id
                 FROM art_article_1 WHERE enabled = 1 AND id IN (   ? , ? ) ::: [1504, 1505]
    
    Actual SQL: 
    article1 ::: SELECT id, last_depot_id, current_depot_id, sku_id, entry_bill_id, entry_time, stock_status, entry_type,
                 type, created_by, created_time, last_modified_by, last_modified_time, remarks, enabled, version, tenant_id
                 FROM art_article_0 WHERE enabled = 1 AND id IN (   ? , ? ) ::: [1504, 1505]
    
    Actual SQL: 
    article1 ::: SELECT id, last_depot_id, current_depot_id, sku_id, entry_bill_id, entry_time, stock_status, entry_type,
                 type, created_by, created_time, last_modified_by, last_modified_time, remarks, enabled, version, tenant_id
                 FROM art_article_1 WHERE enabled = 1 AND id IN (   ? , ?) ::: [1504, 1505]

**对于少量的批量查询可以考虑for循环查询**