mybatis 按照时间分表进行查询

日志表数据量巨大。需要进行分表存储，分表之后就需要查询

使用union方式查询

数据库中大约是这种情况

(select * FROM tb_log_20190917) UNION (SELECT * FROM tb_log_20190916) UNION (SELECT * FROM tb_log_20190915) ORDER BY create_time DESC LIMIT 10

 

我们需要加条件语句

 

select a.* from (

                       (select * FROM tb_log_20190917)

      UNION

                       (SELECT * FROM tb_log_20190916)

     UNION

                        (SELECT * FROM tb_log_20190915)

                        ) as a

                       where

                                  a.id = #{id} 

                       ORDER BY create_time DESC LIMIT 10

mybatis.xml中将 表名 传进去

    通过freach进行循环

<select id="selectListSplit" resultMap="BaseResultMap">
    select ORDER_UNION.*
    FROM (
    <foreach collection="dayTb" item="day_Tb" separator="union" open="(" close=")">
        SELECT
        <include refid="Base_Column_List"/>
        FROM ${day_Tb}
    </foreach>
    ) as ORDER_UNION
    WHERE 1=1
    <if test="deviceCode !=null and deviceCode!=''">
        AND ORDER_UNION.device_code=#{deviceCode}
    </if>
    <if test="valueType !=null and valueType !=''">
        AND ORDER_UNION.value_type=#{valueType}
    </if>
    <if test="pointCode !=null and pointCode !=''">
        AND ORDER_UNION.point_code=#{pointCode}
    </if>
    <if test="functionCode !=null and functionCode !=''">
        AND ORDER_UNION.function_code=#{functionCode}
    </if>
    <if test="startTime !=null and startTime !='' and endTime !=null and endTime !=''">
        AND ORDER_UNION.create_time BETWEEN to_timestamp(#{startTime}) AND to_timestamp(#{endTime})
    </if>
    ORDER BY ORDER_UNION.create_time DESC
</select>