ShardingSphere不支持的SQL

由于业务需要我们需要用sharding 进行分库分表，那么必须get到一些填坑指南，如下是其不支持的SQL。

1、VALUES语句不支持运算表达式

INSERT INTO tbl_name (col1, col2, …) VALUES(1+2, ?, …)

2、INSERT … SELECT

INSERT INTO tbl_name (col1, col2, …) SELECT col1, col2, … FROM tbl_name WHERE col3 = ?

3、HAVING

SELECT COUNT(col1) as count_alias FROM tbl_name GROUP BY col1 HAVING count_alias > ?

4、UNION

SELECT * FROM tbl_name1 UNION SELECT * FROM tbl_name2

5、UNION ALL

SELECT * FROM tbl_name1 UNION ALL SELECT * FROM tbl_name2

6、包含schema

SELECT * FROM ds.tbl_name1

7、同时使用普通聚合函数和DISTINCT聚合函数

SELECT SUM(DISTINCT col1), SUM(col1) FROM tbl_name

8、会导致全路由 >> 分库分表需要加上分库分表键

SELECT * FROM tbl_name WHERE to_date(create_time, ‘yyyy-mm-dd’) = ?

9、不支持跨库查询，一般不推荐跨库查询，如果业务需要手动封装分库分表查询sql即可

   <foreach collection="businessTable" item="i" separator=" UNION ALL " open="(" close=")">
            SELECT
               DISTINCT tcb.customer_business_id,info.mortgage_type,info.sign_time,tcb.create_time
            FROM t_customer_business_${i} tcb
            LEFT JOIN ${model.sourceNameByOrder}.t_order_sign sign on tcb.customer_business_id = sign.customer_business_id
            LEFT JOIN ${model.sourceNameByOrder}.t_order_info info on sign.order_info_id = info.order_info_id            
   </foreach>

10、嵌套/子查询不支持分表

select t1.name,(select t2.name from tbl_name_2 t2  where t2.id = t1.id ) as t2_name from tbl_name_1 t
select t1.name from tbl_name_1 t1 where t1.id in (select t2.id from tbl_name_2 t2)