MySQL转义字符+存储过程的使用

MySQL中大于，大于等于，小于，小于等于的转义写法

一、左边就是原来的符号，右边就是在mybatis中代替的符号

image

二、如何通过mysql的存储过程创建虚拟表（临时表），并插入1000条数据

这些表通常是用来做数据统计时用到的：比如：查询指定时间段内的每天的数据的总和，

指定时间包括：开始时间：2022-1-1、结束时间：2022-1-30

2022-1-1~2022-1-30,这个时间段内，要求获取每天的数据，但是数据库可能某天是没有数据的，这时，就需要一张有连续id/其他的字段的一张临时表

创建表很简单，插入1000条数据（id连续）具体方式如下（使用存储过程插入数据）：

1.创建存储过程p01

点击查看代码 
CREATE PROCEDURE p01 ()
BEGIN
declare i int;
set i=1;

while i<=1000 do

INSERT INTO help(id) VALUES (i);

set i=i+1;
end WHILE;

END;

2.呼叫存储过程p01

CALL p01;

3.移除存储过程p01

DROP PROCEDURE p01;

以上三步就可以把1000条数据（id连续）插入数据库了

三、mysql查询指定时间段内的每天的数据，查询语句示例如下：

1.controller层代码

点击查看代码 
 private HomePageService homePageService;

    @Autowired
    public void setHomePageService(HomePageService homePageService) {
        this.homePageService = homePageService;
    }

    @GetMapping("/order_home_page")
    @ApiOperation("【订单数据统计】")
    @ApiImplicitParams({
            @ApiImplicitParam(name = "startDate", value = "开始日期", paramType = "query", dataType="Date",defaultValue = "2022-1-1"),
            @ApiImplicitParam(name = "overDate", value = "结束日期", paramType = "query", dataType="Date",defaultValue = "2022-1-30")
    })
    public Result<OrderHomePageVO> orderHomePage(@ApiIgnore @RequestParam Map<String, Object> params){
        OrderHomePageVO vo = homePageService.orderHomePage(params);
        return new Result<OrderHomePageVO>().ok(vo);
    }

2.service层代码

点击查看代码 
/**
     * 订单数据统计
     * @param params
     * @return
     */
    OrderHomePageVO orderHomePage(Map<String, Object> params);

3.serviceImpl实现类代码

点击查看代码 
private HomePageDao homePageDao;

    @Autowired
    public void setHomePageDao(HomePageDao homePageDao) {
        this.homePageDao = homePageDao;
    }


    @Override
    public OrderHomePageVO orderHomePage(Map<String, Object> params) {
        OrderHomePageVO orderHomePageVO = new OrderHomePageVO();
        //1.查询指定时间段内的订单信息
        HomePageVo1 homePageVo1 = new HomePageVo1();
        Integer orderTotal=homePageDao.getOrderTotal(params);
        List<Map<String,Object>> map=homePageDao.getEveryMap(params);
        homePageVo1.setOrderTotal(orderTotal);
        homePageVo1.setMap(map);
        orderHomePageVO.setHomePageVo1(homePageVo1);
        log.info("【订单统计】-1.查询指定时间段内的订单信息"+homePageVo1);
        //2.各宾馆的订单数据
        List<Map<String,Object>> hotelMap=homePageDao.getHotelMap(params);
        orderHomePageVO.setHotelMap(hotelMap);
        log.info("【订单统计】-2.各宾馆的订单数据"+hotelMap);
        //3.各服务类型订单数据
        List<Map<String,Object>> serviceTypeMap=homePageDao.getServiceTypeMap(params);
        orderHomePageVO.setServiceTypeMap(serviceTypeMap);
        log.info("【订单统计】-3.各服务类型订单数据"+serviceTypeMap);
        //4.各房间类型的订单数据
        List<Map<String,Object>> roomTypeMap=homePageDao.getRoomTypeMap(params);
        orderHomePageVO.setRoomTypeMap(roomTypeMap);
        log.info("【订单统计】-4.各房间类型的订单数据"+roomTypeMap);
        //5.各服务星级的订单数据
        List<WaiterGradeOrderVO> waiterGradeOrderVOList=homePageDao.getWaiterGradeOrderVO();
        if (waiterGradeOrderVOList!=null){
            for (WaiterGradeOrderVO waiterGradeOrderVO:waiterGradeOrderVOList) {
                if (waiterGradeOrderVO.getStarName()!=null){
                    List<Long> ids=homePageDao.getIds(waiterGradeOrderVO.getStarName());
                    if (ids!=null){
                        Integer allTotal=0;
                        for (Long waiterGradeId:ids) {
                            List<Long> waiterUserIds=homePageDao.getWaiterUserIds(waiterGradeId);
                            if (waiterUserIds.isEmpty()){
                                waiterGradeOrderVO.setTotal(0);
                            }else {
                                    Integer waiterTotal =  homePageDao.getWaiterOrderTotal(waiterUserIds,params);
                                allTotal=waiterTotal+allTotal;
                            }
                        }
                        waiterGradeOrderVO.setTotal(allTotal);
                    }
                }
            }
            orderHomePageVO.setWaiterGradeOrderVOList(waiterGradeOrderVOList);
            log.info("【订单统计】-5.各服务星级的订单数据"+waiterGradeOrderVOList);
        }
        return orderHomePageVO;
    }

4.dao+XML代码

4.1某时间段内每日的订单统计信息

其中的help表，就是上面提到的临时表（具有连续id的一张表）
点击查看dao代码 
/**
     * 某时间段内每日的订单统计信息
     * @param params
     * @return
     */
    List<Map<String, Object>> getEveryMap(Map<String, Object> params);
点击查看dao.xml代码 
<select id="getEveryMap" resultType="java.util.Map">
        select t1.day, ifnull(u1.total, 0) as num  from
        (
        select date_format(date_sub(#{overDate},interval t.id day),'%Y-%m-%d') as day
        from help t,
        (
        select TIMESTAMPDIFF(day,#{startDate}, #{overDate})+1 as subday
        ) as subdayt
        where t.id &lt; subdayt.subday
        ) as t1  left join
        (
        select date_format(u.create_date, '%Y-%m-%d') as r_t , count(id) as total from user_order u WHERE u.status=1   group by r_t
        ) as u1 on u1.r_t = t1.day
        ORDER BY t1.day asc
    </select>

4.2各宾馆的订单数据

点击查看dao代码 
 /**
     * 各宾馆的订单数据
     * @param params
     * @return
     */
    List<Map<String, Object>> getHotelMap(Map<String, Object> params);
点击查看dao.xml代码 
<select id="getHotelMap" resultType="java.util.Map">
        SELECT
            A.hotel_name hotelName,
            IFNULL((SELECT COUNT(id) FROM user_order WHERE status=1 AND hotel_id=A.id AND create_date between #{startDate} AND #{overDate}),0) total
        FROM
            hotel A
        WHERE
            A.status = 1
        GROUP BY
            A.hotel_name
    </select>

四、mysql统计指定日期的数据（比如：统计一年内的所有数据，按照每天的日期分组查询，没有数据补0）：

该方式不需要创建辅助表（例如上面的help表），直接使用sql语句统计

MySQL代码如下：

SELECT A.dateTime,COALESCE(B.num, 0) AS num FROM
            (SELECT date_add(#{startDate}, interval row DAY) dateTime FROM
                (
                    SELECT @row := @row + 1 AS row FROM
                        (select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t,
                        (select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t2,
                        (select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t3,
                        (select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t4,
                        (SELECT @row:=-1) r
                ) se
             where date_add(#{startDate}, interval row DAY) &lt;= #{overDate}) A
                LEFT JOIN
            (SELECT DATE_FORMAT(bespeak_date, '%Y-%m-%d') dateTime, COUNT(id) num FROM yx_store_order WHERE is_del=0 GROUP BY DATE_FORMAT(bespeak_date, '%Y-%m-%d')) B
            ON A.dateTime= B.dateTime

五、使用存储过程生成大数据量的数据：

点击查看代码 
delimiter $$
CREATE PROCEDURE BatchInsert(IN initId INT, IN loop_counts INT)
BEGIN
    DECLARE Var INT;
    DECLARE ID INT;
    SET Var = 0;
    SET ID = initId;
    set autocommit=0; -- 关闭自动提交事务，提高插入效率
    WHILE Var < loop_counts DO
        INSERT INTO `demo` (`id`,`title`,`content`,`c1`,`c2`,`c3`,`c4`,`c5`,`create_date`) 
        VALUES (ID,'测试','测试','测试','测试','测试','测试','测试',NOW());
        SET ID = ID + 1;
        SET Var = Var + 1;
    END WHILE;
    COMMIT;
END$$;

delimiter ;  -- 界定符复原为默认的分号
CALL BatchInsert(1, 1000000);

以上，举了两个示例，后续有新的需求，及时补充，在这里记录一下，拜拜~

posted @ 2022-11-16 14:36  青喺半掩眉砂  阅读(413)  评论(0)  编辑  收藏  举报