MySql 常用函数-白红宇

MySql 常用函数

阅读量：7126 次

发布时间：2019-06-28

本文共 6276 字，大约阅读时间需要 20 分钟。

一、字符串函数

contact(s1,s2,s3...) : 把传入的参数连接成字符串

mysql> select concat('a','b','c');

+---------------------+

| concat('a','b','c') |

+---------------------+

| abc |

+---------------------+

任何字符串和 null 拼接结果都是 null

mysql> select concat('a','b','c',null);

+--------------------------+

| concat('a','b','c',null) |

+--------------------------+

| NULL |

+--------------------------+

INSERT(str,pos,len,newstr) ：将字符串 str 从第 pos 位置开始，len 字符长的子串替换为 newstr

mysql> select INSERT('abcd',1,2,'!!');

+-------------------------+

| INSERT('abcd',1,2,'!!') |

+-------------------------+

| !!cd |

+-------------------------+

LOWER(str),UPPER(str): 将字符串转为小写/大写

mysql> select LOWER('ABC'),UPPER('abc');

+--------------+--------------+

| LOWER('ABC') | UPPER('abc') |

+--------------+--------------+

| abc | ABC |

+--------------+--------------+

LEFT(str,len),RIGHT(str,len)：返回字符串最左边/最右边 len 个字符，如果 len 为 null，结果返回 null

mysql> select LEFT('abcd',1),RIGHT('abcd',1);

+----------------+-----------------+

| LEFT('abcd',1) | RIGHT('abcd',1) |

+----------------+-----------------+

| a | d |

+----------------+-----------------+

LTRIM(str),RTRIM(str)：去除 str 左边/右边空格

mysql> select LTRIM(' abc '),RTRIM(' abc ');

+-------------------+-------------------------+

| LTRIM(' abc ') | RTRIM(' abc ') |

+-------------------+-------------------------+

| abc | abc |

+-------------------+-------------------------+

REPEAT(str,count)：返回 str 重复 count 次数结果

mysql> select REPEAT('ad',3);

+----------------+

| REPEAT('ad',3) |

+----------------+

| adadad |

+----------------+

REPLACE(str,from_str,to_str)：to_str 替换 str 中出现的 from_str

mysql> select REPLACE('abcd','ab','!!');

+---------------------------+

| REPLACE('abcd','ab','!!') |

+---------------------------+

| !!cd |

+---------------------------+

STRCMP(expr1,expr2):比较 expr1 和 expr2 的 ascii 值的大小，如果相等返回 0 ，如果 expr1 比 expr2 小返回 -1，如果 expr1 比 expr2 大返回 1

mysql> select STRCMP('a','a'),STRCMP('a','b'),STRCMP('b','a');

+-----------------+-----------------+-----------------+

| STRCMP('a','a') | STRCMP('a','b') | STRCMP('b','a') |

+-----------------+-----------------+-----------------+

| 0 | -1 | 1 |

+-----------------+-----------------+-----------------+

trim(str)：去除 str 左右的空格

mysql> select trim(' aaa ');

+----------------+

| trim(' aaa ') |

+----------------+

| aaa |

+----------------+

SUBSTRING(str,pos,len)：截取 str 中从 pos 位置起的 len 个字符

mysql> select SUBSTRING('abcdefg',1,3);

+--------------------------+

| SUBSTRING('abcdefg',1,3) |

+--------------------------+

| abc |

+--------------------------+

二、数值函数

select ABS(X)：获取 X 的绝对值

select CEIL(X)：获取大于 X 的最小整数

select FLOOR(X)：获取小于 X 的最大整数

select MOD(N,M)：返回 n%m 结果

select RAND()：返回 0~1 之间的随机值

select ROUND(X,D)：返回 X 的四舍五入的有 D 位小数的值，D默认为0，如果返回结果为整数，则D=0

mysql> select ROUND(1.1),ROUND(1.1,1),ROUND(1.1,2),ROUND(1,2);

+------------+--------------+--------------+------------+

+------------+--------------+--------------+------------+

| 1 | 1.1 | 1.10 | 1 |

+------------+--------------+--------------+------------+

select TRUNCATE(X,D)：返回 X 截取 D 位小数的结果,注意和 ROUND 的函数区别

mysql> select TRUNCATE(1.238,2),ROUND(1.238,2);

+-------------------+----------------+

| TRUNCATE(1.238,2) | ROUND(1.238,2) |

+-------------------+----------------+

| 1.23 | 1.24 |

+-------------------+----------------+

三、日期和时间函数

select now();获取当前时间

select DATE_FORMAT(date,format)：按照 format 格式化 date ，关于 mysql 中时间的格式 ,参考 https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html

mysql> select DATE_FORMAT(now(),'%Y%m%d');

+-----------------------------+

| DATE_FORMAT(now(),'%Y%m%d') |

+-----------------------------+

| 20190218 |

+-----------------------------+

select DATE_ADD(date,INTERVAL expr unit)：返回一个时间或日期加上一个时间间隔

当前日期、当前日期向后一天，当前日期向前一天

mysql> select NOW(),DATE_ADD(now(),INTERVAL 1 day),DATE_ADD(now(),INTERVAL -1 day);

+---------------------+--------------------------------+---------------------------------+

| NOW() | DATE_ADD(now(),INTERVAL 1 day) | DATE_ADD(now(),INTERVAL -1 day) |

+---------------------+--------------------------------+---------------------------------+

| 2019-02-18 16:30:05 | 2019-02-19 16:30:05 | 2019-02-17 16:30:05 |

+---------------------+--------------------------------+---------------------------------+

返回距离当前日期一年两个月的日期

mysql> select DATE_ADD(now(),INTERVAL '1_2' year_month);

+-------------------------------------------+

| DATE_ADD(now(),INTERVAL '1_2' year_month) |

+-------------------------------------------+

| 2020-04-18 16:31:57 |

+-------------------------------------------+

select DATEDIFF(expr1,expr2)：返回 expr1- expr2表示为从一个日期到另一个日期的天数值

mysql> select now(),DATEDIFF(now(),'2019-01-01');

+---------------------+------------------------------+

| now() | DATEDIFF(now(),'2019-01-01') |

+---------------------+------------------------------+

| 2019-02-18 16:34:40 | 48 |

+---------------------+------------------------------+

select TIMESTAMPDIFF(unit,datetime_expr1,datetime_expr2)：返回datetime_expr2- datetime_expr1，结果的单位由unit参数给出

mysql> select TIMESTAMPDIFF(MINUTE,'2019-02-18 11:00:00','2019-02-18 12:00:00') as result;

+--------+

| result |

+--------+

| 60 |

+--------+

mysql> select TIMESTAMPDIFF(HOUR,'2019-02-18 11:00:00','2019-02-18 12:00:00') as result;

+--------+

| result |

+--------+

| 1 |

+--------+

四、流程控制函数

IF(expr1,expr2,expr3)：如果expr1是TRUE （expr1 <> 0和expr1 <> NULL），则IF() 返回expr2。否则，它返回expr3

IFNULL(expr1,expr2)：如果expr1不是 NULL，则 IFNULL()返回 expr1; 否则它会返回 expr2

CASE WHEN [condition1] THEN result1 [WHEN [condition2] THEN result2 ...] [ELSE defultresult] END：如果 condition1 为真则返回 result1，如果 condition2 为真则返回 result2，否则返回 defultresult

mysql> SELECT CASE WHEN 1>0 THEN 'true' ELSE 'false' END as colvalue;

+----------+

| colvalue |

+----------+

| true |

+----------+

CASE value WHEN [compare_value1] THEN result1 [WHEN [compare_value2] THEN result2 ...] [ELSE result] END：如果 value=compare_value1 返回 result1,如果 value=compare_value2 返回 result2

mysql> SELECT CASE 1 WHEN 1 THEN 'one' WHEN 2 THEN 'two' ELSE 'more' END as colvalue;

+----------+

| colvalue |

+----------+

| one |

+----------+