mysql内置函数

时间

获取当前时间

mysql> select now(), curdate(), curtime();
+---------------------+------------+-----------+
| now()               | curdate()  | curtime() |
+---------------------+------------+-----------+
| 2022-05-30 01:44:23 | 2022-05-30 | 01:44:23  |
+---------------------+------------+-----------+

获取日期、时间指定部分的函数

mysql> select t, year(t), quarter(t), month(t), monthname(t), week(t)
    -> from (select '2022-05-01 23:58:59' as t) tmp_table;
+---------------------+---------+------------+----------+--------------+---------+
| t                   | year(t) | quarter(t) | month(t) | monthname(t) | week(t) |
+---------------------+---------+------------+----------+--------------+---------+
| 2022-05-01 23:58:59 |    2022 |          2 |        5 | May          |      18 |
+---------------------+---------+------------+----------+--------------+---------+

mysql> select t, dayname(t), dayofyear(t), dayofmonth(t), dayofweek(t), weekday(t)
    -> from (select '2022-05-01 23:58:59' as t) tmp_table;
+---------------------+------------+--------------+---------------+--------------+------------+
| t                   | dayname(t) | dayofyear(t) | dayofmonth(t) | dayofweek(t) | weekday(t) |
+---------------------+------------+--------------+---------------+--------------+------------+
| 2022-05-01 23:58:59 | Sunday     |          121 |             1 |            1 |          6 |
+---------------------+------------+--------------+---------------+--------------+------------+

mysql> select t, hour(t), minute(t), second(t)
    -> from (select '2022-05-01 23:58:59' as t) tmp_table;
+---------------------+---------+-----------+-----------+
| t                   | hour(t) | minute(t) | second(t) |
+---------------------+---------+-----------+-----------+
| 2022-05-01 23:58:59 |      23 |        58 |        59 |
+---------------------+---------+-----------+-----------+

DAYOFWEEK(DATE) 返回日期对应的一周中的索引，1表示周日，2表示周一
WEEK(DATE) 计算日期是一年中的第几周， 范围从1到52
WEEKDAY(DATE) 返回日期对应的工作日索引，0表示周一，1表示周二…6表示周六

日期计算

mysql> select t,
    -> datediff('2022-05-02','2022-05-01'),
    -> date_add(t,interval 1 year), date_add(t,interval 1 quarter), date_add(t,interval 1 month), date_add(t,interval 1 week),
    -> date_add(t, interval 1 hour), date_add(t,interval 1 minute)
    -> from (select '2022-05-01 00:00:00' as t) tmp_table;
+---------------------+-------------------------------------+-----------------------------+--------------------------------+------------------------------+-----------------------------+------------------------------+-------------------------------+
| t                   | datediff('2022-05-02','2022-05-01') | date_add(t,interval 1 year) | date_add(t,interval 1 quarter) | date_add(t,interval 1 month) | date_add(t,interval 1 week) | date_add(t, interval 1 hour) | date_add(t,interval 1 minute) |
+---------------------+-------------------------------------+-----------------------------+--------------------------------+------------------------------+-----------------------------+------------------------------+-------------------------------+
| 2022-05-01 00:00:00 |                                   1 | 2023-05-01 00:00:00         | 2022-08-01 00:00:00            | 2022-06-01 00:00:00          | 2022-05-08 00:00:00         | 2022-05-01 01:00:00          | 2022-05-01 00:01:00           |
+---------------------+-------------------------------------+-----------------------------+--------------------------------+------------------------------+-----------------------------+------------------------------+-------------------------------+

datediff(expr1,expr2)： 两个日期相减，和时间无关
date_add(date,interval expr unit)： 为日期增加一个时间间隔

字符串函数

mysql> select concat('a', 'b', 'c'), length('abc'),
    -> upper('abc'), lower('ABC'),
    -> ltrim(' abc'), rtrim('abc '), trim(' abc ');
+-----------------------+---------------+--------------+--------------+---------------+---------------+---------------+
| concat('a', 'b', 'c') | length('abc') | upper('abc') | lower('ABC') | ltrim(' abc') | rtrim('abc ') | trim(' abc ') |
+-----------------------+---------------+--------------+--------------+---------------+---------------+---------------+
| abc                   |             3 | ABC          | abc          | abc           | abc           | abc           |
+-----------------------+---------------+--------------+--------------+---------------+---------------+---------------+

mysql> select replace('abc', 'b', '+'), substring('abcd', 2, 2),
    -> strcmp('a', 'a'), strcmp('a', 'b'), strcmp('b', 'a');
+--------------------------+-------------------------+------------------+------------------+------------------+
| replace('abc', 'b', '+') | substring('abcd', 2, 2) | strcmp('a', 'a') | strcmp('a', 'b') | strcmp('b', 'a') |
+--------------------------+-------------------------+------------------+------------------+------------------+
| a+c                      | bc                      |                0 |               -1 |                1 |
+--------------------------+-------------------------+------------------+------------------+------------------+

STRCMP(S1,S2) 比较s1和s2，如果s1等于s2，返回0，如果s1小于s2，返回-1

ifnull 函数

select ifnull(a, '2'),  ifnull(b, '2')
FROM (SELECT NULL AS a, 1 AS b) tmp;

case函数

SELECT a, b, ( CASE a WHEN a IS NULL THEN b ELSE a END ) AS new
FROM (SELECT NULL AS a, 1 AS b) tmp;

系统函数

mysql> select version(), database();
+-----------+------------+
| version() | database() |
+-----------+------------+
| 8.0.27    | mysql      |
+-----------+------------+

其他

GROUP_CONCAT

select GROUP_CONCAT(MENU_ID) from t_sys_menu

FIND_IN_SET

select MENU_ID from t_sys_menu where FIND_IN_SET(MENU_CODE, @menu_code)

变量默认大小1024

SET GLOBAL group_concat_max_len=102400;
SET SESSION group_concat_max_len=102400;
set @menu_code = ‘testManage,memberManage,memberMgt,MEMPOINT,MEMCOPON’;