MySQL 函数

汇总函数 rollup

rollup是 SQL 关键字，在 MySQL 中得用with rollup。它是group by子句的扩展，用于统计后增加一行汇总数据。

举例，现有库存表，我们按仓库名称分组，统计每个仓库的产品总量，最后来一个汇总。

mysql> SELECT * FROM inventory;
+----+---------------+---------+----------+
| id | warehouse     | product | quantity |
+----+---------------+---------+----------+
|  1 | San Jose      | iPhone  |      100 |
|  2 | San Fransisco | iPhone  |       60 |
|  3 | San Jose      | huawei  |      200 |
|  4 | San Fransisco | huawei  |      100 |
+----+---------------+---------+----------+

SELECT warehouse, SUM(quantity) sum
FROM inventory
GROUP BY warehouse WITH ROLLUP;
+---------------+------+
| warehouse     | sum  |
+---------------+------+
| San Fransisco |  160 |
| San Jose      |  300 |
| NULL          |  460 |
+---------------+------+

最后，使用COALESCE()函数将NULL值替换成一个别名。COALESCE()的功能是返回第一个不为空的值。COALESCE可接受多个参数，当只有两个参数时，功能与IFNULL一致。

SELECT COALESCE(warehouse, 'warehouseAll') warehouse, SUM(quantity) sum
FROM inventory
GROUP BY warehouse WITH ROLLUP;
+---------------+------+
| warehouse     | sum  |
+---------------+------+
| San Fransisco |  160 |
| San Jose      |  300 |
| warehouseAll  |  460 |
+---------------+------+

获取一个月的天数

SELECT DAY(LAST_DAY('2022-10-01')) AS days_in_month;

LAST_DAY获取日期月份的最后一天‘2022-10-31’；DAY获取日期的天数。

limit用法

1. limit 1, 3: 从第索引为1的数据开始，取3条数据。
2. limit 1 offset 3: 取1条数据，从索引为3的数据开始。
3. limit 3: 取前3条。这个最简单常用，限制数据量返回。

SELECT * FROM employees LIMIT 1, 3; -- 取2， 3， 4条数据
SELECT * FROM employees LIMIT 1 OFFSET 3; -- 取第4条数据

find_in_set 判断字符串是否包含

find_in_set判断一个字串是否在目标字符串中，且目标字符串必须由逗号分隔的，函数返回在字符串中的第一次匹配的索引位置。

SELECT FIND_IN_SET('y', 'x,y,z'); -- 返回2
SELECT FIND_IN_SET('y', 'x'); -- 返回0
SELECT FIND_IN_SET('y', 'xyz'); -- 返回0，因为字符串不是由逗号连接的

有这么一种场景，在一个查询中，前端根据 userType 过滤。这个 userType 是字符串类型，比如{"userType": "1,2,3"}，同时查1，2，3这三种用户类型。一个用户有一或多种用户类型。

mysql> select user_type from users limit 6;
+-----------+
| user_type |
+-----------+
| 1         |
| 2         |
| 3         |
| 1,2       |
| 2,3       |
| 1,2,3     |
+-----------+

所以当查询条件是{"userType": "1,2,3"}，以上用户全部都要查到。
使用MyBatis时，<foreach>中用find_in_set结合or可以处理这种场景。

<select id="findUsers" resultType="com.springboot.demo.model.User">
    select t.id, t.user_type userType
    from `users` t
    where 1=1
    and
    <foreach item="item" collection="userType.split(',')" separator="or" open="(" close=")">
        find_in_set(#{item}, t.user_type)
    </foreach>
</select>

该语句在控制台打印为