分表 + pagehelper + group by 语句的问题
PageHelper为了算总记录条数,会改写原始sql,做1次count,比如:
select order_id, order_name, order_date from t_order_logic_0 where order_date='2020-09-06';
会首先被改写为:
select count(0) from t_order_logic_0 where order_date='2020-09-06';
然后再由sharding-jdbc改写成:(精确路由)
select count(0) from t_order_0 where order_date='2020-09-06';
对于简单语句,这样没什么问题。但是如果原始语句上,有一些聚合函数或group by,比如下面这样:
如上图,加了group by 后,下面的语句
SELECT count(0) FROM (SELECT order_id FROM t_order_logic WHERE order_date = ? GROUP BY order_id) table_count
sharding-jdbc并不能正确解析为t_order_0,仍然还是t_order_logic
SELECT count(0) FROM (SELECT order_id FROM t_order_logic WHERE order_date = ? GROUP BY order_id) table_count ::: [2020-09-06]
解决办法:pagehelper对于count语句,允许用户自定义,只要在原来的语句id,加上“_COUNT”
<select id="selectList2" resultMap="BaseResultMap">
select order_id from t_order_logic${tableIndex} where order_date=#{orderDate} group by order_id
</select>
<select id="selectList2_COUNT" resultType="long">
select count(1) from t_order_logic${tableIndex} where order_date=#{orderDate} group by order_id
</select>