由于业务需要我们需要用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)
版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 举报，一经查实，本站将立刻删除。
文章由极客之音整理，本文链接：https://www.bmabk.com/index.php/post/154709.html
