ShardingSphere 针对SQL IN语法问题小记

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循环查询