MySQL 常用函数(关系型数据库)

前言

  • 在 MySQL 数据库中,函数可以用在 select 语句及其子句(例如 where、order by、having 等)中,也可以用在 update、delete 语句及其子句中。

1、字符串函数

  • 字符串函数是最常用的一种函数,在 MySQL 中,字符串函数同样是最丰富的一类函数。
  • 在程序上,字符串的编号从 0 开始,但在 MySQL 中,从 1 开始。
函数 功能
concat(s1, s2, …, sn) 连接 s1, s2, …, sn 为一个字符串, 如有任何一个参数为 NULL ,则返回值为 NULL
concat_ws(separator, s1, s2, …, sn) 代表 CONCAT With Separator ,是 concat() 的特殊形式。第一个参数是其它参数的分隔符。分隔符的位置放在要连接的两个字符串之间。分隔符可以是一个字符串,也可以是其它参数。如果分隔符为 NULL,则结果为 NULL。函数会忽略任何分隔符参数后的 NULL 值。但是 concat_ws() 不会忽略任何空字符串。
insert(str, x, y, instr) 将字符串 str 从 x 位置开始,y 个字符长的字串替换为字符串 instr
lower(str) 将字符串 str 中所有字符变为小写
upper(str) 将字符串 str 中所有字符变为大写
lpad(str, n, pad) 用字符串 pad 对 str 最左边进行填充,直到长度为 n 个字符长度
rpad(str, n, pad) 用字符串 pad 对 str 最右边进行填充,直到长度为 n 个字符长度
repeat(str, x) 返回 str 重复 x 次的结果
replace(str, a, b) 用字符串 b 替换字符串 str 中所有出现的字符串 a
strcmp(s1, s2) 比较字符串 s1 和 s2,s1 比 s2 小,返回 -1;s1 与 s2 相等,返回 0;s1 比 s2 大,返回 1
ltrim(str) 去掉字符串左侧的空格
rtrim(str) 去掉字符串右侧的空格
trim(str) 去掉字符串 str 行尾和行头的空格
left(str, x) 返回字符串 str 最左边的 x 个字符
right(str, x) 返回字符串 str 最右边的 x 个字符
substring(str, x, y) 返回从字符串 str x 位置起 y 个字符长度的子串
  • 示例

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    45
    46
    47
    48
    49
    50
    51
    52
    53
    54
    55
    56
    57
    58
    59
    60
    61
    62
    63
    64
    65
    66
    67
    68
    69
    70
    71
    72
    73
    74
    75
    76
    77
    78
    79
    80
    81
    82
    83
    84
    85
    86
    87
    88
    89
    90
    91
    92
    93
    94
    95
    96
    97
    98
    > select concat('aaa', 'bbb', 'ccc'), concat('aaa', null);

    +-----------------------------+---------------------+
    | concat('aaa', 'bbb', 'ccc') | concat('aaa', null) |
    +-----------------------------+---------------------+
    | aaabbbccc | NULL |
    +-----------------------------+---------------------+
    1 row in set (0.00 sec)

    > select insert('beijing2008you', 12, 3, 'me');

    +---------------------------------------+
    | insert('beijing2008you', 12, 3, 'me') |
    +---------------------------------------+
    | beijing2008me |
    +---------------------------------------+
    1 row in set (0.00 sec)

    > select lower('BEIJING2008'), upper('beijing2008');

    +----------------------+----------------------+
    | lower('BEIJING2008') | upper('beijing2008') |
    +----------------------+----------------------+
    | beijing2008 | BEIJING2008 |
    +----------------------+----------------------+
    1 row in set (0.00 sec)

    > select lpad('2008', 20, 'beijing'), rpad('beijing', 20, '2008');

    +-----------------------------+-----------------------------+
    | lpad('2008', 20, 'beijing') | rpad('beijing', 20, '2008') |
    +-----------------------------+-----------------------------+
    | beijingbeijingbe2008 | beijing2008200820082 |
    +-----------------------------+-----------------------------+
    1 row in set (0.00 sec)

    > select repeat('mysql ', 3);

    +---------------------+
    | repeat('mysql ', 3) |
    +---------------------+
    | mysql mysql mysql |
    +---------------------+
    1 row in set (0.00 sec)

    > select replace('beijing_2010', '_2010', '2008');

    +------------------------------------------+
    | replace('beijing_2010', '_2010', '2008') |
    +------------------------------------------+
    | beijing2008 |
    +------------------------------------------+
    1 row in set (0.00 sec)

    > select strcmp('a', 'b'), strcmp('b', 'b'), strcmp('c', 'b');

    +------------------+------------------+------------------+
    | strcmp('a', 'b') | strcmp('b', 'b') | strcmp('c', 'b') |
    +------------------+------------------+------------------+
    | -1 | 0 | 1 |
    +------------------+------------------+------------------+
    1 row in set (0.00 sec)

    > select ltrim(' |beijing'), rtrim('beijing| ');

    +--------------------+--------------------+
    | ltrim(' |beijing') | rtrim('beijing| ') |
    +--------------------+--------------------+
    | |beijing | beijing| |
    +--------------------+--------------------+
    1 row in set (0.00 sec)

    > select trim(' $ beijing2008 $ ');

    +---------------------------+
    | trim(' $ beijing2008 $ ') |
    +---------------------------+
    | $ beijing2008 $ |
    +---------------------------+
    1 row in set (0.00 sec)

    > select left('beijing2008', 7), left('beijing', null), right('beijing2008', 4);

    +------------------------+-----------------------+-------------------------+
    | left('beijing2008', 7) | left('beijing', null) | right('beijing2008', 4) |
    +------------------------+-----------------------+-------------------------+
    | beijing | NULL | 2008 |
    +------------------------+-----------------------+-------------------------+
    1 row in set (0.00 sec)

    > select substring('beijing2008', 8, 4), substring('beijing2008', 1, 7);

    +--------------------------------+--------------------------------+
    | substring('beijing2008', 8, 4) | substring('beijing2008', 1, 7) |
    +--------------------------------+--------------------------------+
    | 2008 | beijing |
    +--------------------------------+--------------------------------+
    1 row in set (0.00 sec)

2、数值函数

  • MySQL 中另外一类很重要的函数就是数值函数,这些函数能处理很多数值方面的运算。
函数 功能
abs(x) 返回 x 的绝对值
ceil(x) 返回大于 x 的最小整数值
floor(x) 返回小于 x 的最大整数值
mod(x, y) 返回 x/y 的模,模数和被模数任何一个为 NULL 结果都为 NULL
rand() 返回 0~1 内的随机值
round(x, y) 返回参数 x 四舍五入的有 y 位小数的值,如果不写 y,则默认 y 为 0,即将 x 四舍五入后取整
truncate(x, y) 返回数字 x 截断为 y 位小数的结果
  • 示例

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    45
    46
    47
    48
    49
    50
    51
    52
    53
    54
    55
    56
    57
    58
    59
    60
    61
    62
    > select abs(-0.8), abs(0.8);

    +-----------+----------+
    | abs(-0.8) | abs(0.8) |
    +-----------+----------+
    | 0.8 | 0.8 |
    +-----------+----------+
    1 row in set (0.00 sec)

    > select ceil(-0.8), ceil(0.8);

    +------------+-----------+
    | ceil(-0.8) | ceil(0.8) |
    +------------+-----------+
    | 0 | 1 |
    +------------+-----------+
    1 row in set (0.01 sec)

    > select floor(-0.8), floor(0.8);

    +-------------+------------+
    | floor(-0.8) | floor(0.8) |
    +-------------+------------+
    | -1 | 0 |
    +-------------+------------+
    1 row in set (0.00 sec)

    > select mod(15, 10), mod(1, 11), mod(null, 10);

    +-------------+------------+---------------+
    | mod(15, 10) | mod(1, 11) | mod(null, 10) |
    +-------------+------------+---------------+
    | 5 | 1 | NULL |
    +-------------+------------+---------------+
    1 row in set (0.00 sec)

    > select rand(), rand() * 100, ceil(rand() * 100);

    +--------------------+--------------------+--------------------+
    | rand() | rand() * 100 | ceil(rand() * 100) |
    +--------------------+--------------------+--------------------+
    | 0.6922199192384444 | 37.314064183564916 | 79 |
    +--------------------+--------------------+--------------------+
    1 row in set (0.00 sec)

    > select round(1.1), round(1.1, 2), round(1, 2);

    +------------+---------------+-------------+
    | round(1.1) | round(1.1, 2) | round(1, 2) |
    +------------+---------------+-------------+
    | 1 | 1.10 | 1 |
    +------------+---------------+-------------+
    1 row in set (0.00 sec)

    > select round(1.235, 2), truncate(1.235, 2);

    +-----------------+--------------------+
    | round(1.235, 2) | truncate(1.235, 2) |
    +-----------------+--------------------+
    | 1.24 | 1.23 |
    +-----------------+--------------------+
    1 row in set (0.00 sec)

3、日期和时间函数

  • 常用日期和时间函数
函数 功能
curdate() 返回当前日期,只包含年月日
curtime() 返回当前时间,只包含时分秒
now() 返回当前日期和时间,年月日时分秒全都包含
unix_timestamp(date) 返回日期 date 的 unix 时间戳
from_unixtime(timestamp) 返回 unix 时间戳的日期和时间值,和 unix_timestamp(date) 互逆
week(date) 返回日期 date 为一年中的第几周
year(date) 返回日期 date 的年份
hour(time) 返回 time 的小时值
minute(time) 返回 time 的分钟值
monthname(date) 返回 date 的月份名
date_format(date, fmt) 返回按字符串 fmt 格式化日期 date 值
date_add(date, interval expr type) 返回与 date 相差 interval 时间段的日期,interval 是间隔类型关键字,expr 是一个表达式,这个表达式对应后面的类型,type 是日期间隔类型
datediff(expr, expr2) 返回起始时间 expr 和结束时间 expr2 之间的天数
  • 日期和时间格式
格式符 格式说明
%S、%s 两位数字形式的秒(00, 01, …, 59)
%i 两位数字形式的分(00, 01, …, 59)
%H 两位数字形式的小时,24 小时(00, 01, …, 23)
%h、%I 两位数字形式的小时,12 小时(00, 01, …, 12)
%k 数字形式的小时,24 小时(0, 1, …, 23)
%l 数字形式的小时,12 小时(0, 1, …, 12)
%T 24 小时的时间形式(hh:mm:ss)
%r 12 小时的时间形式(hh:mm:ss AM\PM)
%p AM 或 PM
%W 一周中每一天的名称(Sunday, Monday, …, Saturday)
%a 一周中每一天的名称缩写(Sum, Mon, …, Sat)
%d 两位数字表示月中的天数(00, 01, …, 31)
%e 数字形式表示月中的天数(1, 2, …, 31)
%D 英文后缀表示月中的天数(1st, 2nd, 3rd, …)
%w 以数字形式表示周中的天数(0 = Sunday, 1 = Monday, …, 6 = Saturday)
%j 以 3 位数字表示年中的天数(001, 002, …, 366)
%U 周(0, 1, 52),其中 Sunday 为周中的第一天
%u 周(0, 1, 52),其中 Monday 为周中的第一天
%M 月名(January, February, …, December)
%b 缩写的月名(January, February, …, December)
%m 两位数字表示的月份(01, 02, …, 12)
%c 数字表示的月份(1, 2, …, 12)
%Y 四为数字表示的年份
%y 两位数字表示的年份
%% 直接值 “%”
  • 日期间隔类型
表达式类型 描述 格式
HOUR 小时 hh
MINUTE mm
SECOND ss
YEAR YY
MONTH MM
DAY DD
YEAR_MONTH 年和月 YY-MM
DAY_HOUR 日和小时 DD hh
DAY_MINUTE 日和分钟 DD hh:mm
DAY_SECOND 日和秒 DD hh:mm:ss
HOUR_MINUTE 小时和分 hh:mm
HOUR_SECOND 小时和秒 HH:ss
MINUTE_SECOND 分和秒 mm:ss
  • 示例

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    45
    46
    47
    48
    49
    50
    51
    52
    53
    54
    55
    56
    57
    58
    59
    60
    61
    62
    63
    64
    65
    66
    67
    68
    69
    70
    71
    72
    73
    74
    75
    76
    77
    78
    79
    80
    81
    82
    83
    84
    85
    86
    87
    88
    89
    90
    91
    92
    93
    94
    95
    96
    97
    98
    99
    100
    101
    102
    103
    104
    105
    106
    107
    108
    109
    110
    111
    112
    113
    114
    115
    > select curdate();

    +------------+
    | curdate() |
    +------------+
    | 2018-06-20 |
    +------------+
    1 row in set (0.00 sec)

    > select curtime();

    +-----------+
    | curtime() |
    +-----------+
    | 15:27:19 |
    +-----------+
    1 row in set (0.00 sec)

    > select now();

    +---------------------+
    | now() |
    +---------------------+
    | 2018-06-20 15:28:12 |
    +---------------------+
    1 row in set (0.00 sec)

    > select unix_timestamp(now());

    +-----------------------+
    | unix_timestamp(now()) |
    +-----------------------+
    | 1529479736 |
    +-----------------------+
    1 row in set (0.00 sec)

    > select from_unixtime(1529479736);

    +---------------------------+
    | from_unixtime(1529479736) |
    +---------------------------+
    | 2018-06-20 15:28:56 |
    +---------------------------+
    1 row in set (0.00 sec)

    > select week(now()), year(now());

    +-------------+-------------+
    | week(now()) | year(now()) |
    +-------------+-------------+
    | 24 | 2018 |
    +-------------+-------------+
    1 row in set (0.00 sec)

    > select hour(curtime()), minute(curtime());

    +-----------------+-------------------+
    | hour(curtime()) | minute(curtime()) |
    +-----------------+-------------------+
    | 15 | 37 |
    +-----------------+-------------------+
    1 row in set (0.00 sec)

    > select monthname(now());

    +------------------+
    | monthname(now()) |
    +------------------+
    | June |
    +------------------+
    1 row in set (0.00 sec)

    > select date_format(now(), '%M, %D, %Y');

    +----------------------------------+
    | date_format(now(), '%M, %D, %Y') |
    +----------------------------------+
    | June, 20th, 2018 |
    +----------------------------------+
    1 row in set (0.00 sec)

    # 第 1 列返回了当前日期时间,第 2 列返回距离当前日期 31 天后的日期时间,第 3 列返回距离当前日期一年两个月后的日期时间
    > select
    now() current,
    date_add(now(), interval 31 day) after31days,
    date_add(now(), interval '1_2' year_month) after_oneyear_twomonth;

    +---------------------+---------------------+------------------------+
    | current | after31days | after_oneyear_twomonth |
    +---------------------+---------------------+------------------------+
    | 2018-06-20 15:44:16 | 2018-07-21 15:44:16 | 2019-08-20 15:44:16 |
    +---------------------+---------------------+------------------------+
    1 row in set (0.00 sec)

    # 第 1 列返回了当前日期时间,第 2 列返回距离当前日期 31 天前的日期时间,第 3 列返回距离当前日期一年两个月前的日期时间
    > select
    now() current,
    date_add(now(), interval -31 day) after31days,
    date_add(now(), interval '-1_-2' year_month) after_oneyear_twomonth;

    +---------------------+---------------------+------------------------+
    | current | after31days | after_oneyear_twomonth |
    +---------------------+---------------------+------------------------+
    | 2018-06-20 15:46:56 | 2018-05-20 15:46:56 | 2017-04-20 15:46:56 |
    +---------------------+---------------------+------------------------+
    1 row in set (0.00 sec)

    > select datediff(now(), '2008-08-08');

    +-------------------------------+
    | datediff(now(), '2008-08-08') |
    +-------------------------------+
    | 3603 |
    +-------------------------------+
    1 row in set (0.00 sec)

4、流程函数

  • 流程函数也是很常用的一类函数,用户可以使用这类函数在一个 SQL 语句中实现条件选择,这样做能够提高语句的效率。
函数 功能
if(value, t f) 如果 value 是真,返回 t,否则返回 f
ifnull(value1, value2) 如果 value1 不为空,返回 value1,否则返回 value2
case when [value] then [result] … else [default] end 如果 value 是真,返回 result,否则返回 default
case [expr] when [value] then [result] … else [default] end 如果 expr 等于 value,返回 result,否则返回 default
  • 示例

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    45
    46
    47
    48
    49
    50
    51
    52
    53
    54
    55
    56
    57
    58
    59
    60
    61
    62
    63
    > select salary, if(salary > 2000, 'high', 'low') from salary;

    +---------+----------------------------------+
    | salary | if(salary > 2000, 'high', 'low') |
    +---------+----------------------------------+
    | 1000.00 | low |
    | 2000.00 | low |
    | 3000.00 | high |
    | 4000.00 | high |
    | 5000.00 | high |
    | NULL | low |
    +---------+----------------------------------+
    6 rows in set (0.00 sec)

    > select salary, ifnull(salary, 0) from salary;

    +---------+-------------------+
    | salary | ifnull(salary, 0) |
    +---------+-------------------+
    | 1000.00 | 1000.00 |
    | 2000.00 | 2000.00 |
    | 3000.00 | 3000.00 |
    | 4000.00 | 4000.00 |
    | 5000.00 | 5000.00 |
    | NULL | 0.00 |
    +---------+-------------------+
    6 rows in set (0.00 sec)

    > select
    salary,
    case when salary <= 2000 then 'low' else 'high' end
    from
    salary;

    +---------+-----------------------------------------------------+
    | salary | case when salary <= 2000 then 'low' else 'high' end |
    +---------+-----------------------------------------------------+
    | 1000.00 | low |
    | 2000.00 | low |
    | 3000.00 | high |
    | 4000.00 | high |
    | 5000.00 | high |
    | NULL | high |
    +---------+-----------------------------------------------------+
    6 rows in set (0.00 sec)

    > select
    salary,
    case salary when 1000 then 'low' when 2000 then 'mid' else 'high' end
    from
    salary;

    +---------+-----------------------------------------------------------------------+
    | salary | case salary when 1000 then 'low' when 2000 then 'mid' else 'high' end |
    +---------+-----------------------------------------------------------------------+
    | 1000.00 | low |
    | 2000.00 | mid |
    | 3000.00 | high |
    | 4000.00 | high |
    | 5000.00 | high |
    | NULL | high |
    +---------+-----------------------------------------------------------------------+
    6 rows in set (0.00 sec)

5、其它常用函数

  • MySQL 提供的函数很丰富,除了前面介绍的字符串函数、数字函数、日期函数、流程函数以外还有很多其他函数。
函数 功能
database() 返回当前数据库名
version() 返回当前数据库版本
user() 返回当前登录用户名
inet_aton(ip) 返回 IP 地址的数字表示,将字符串的IP地址转换为数字表示的网络字节序,这样可以更方便地进行 IP 或者网段的比较
inet_ntoa(num) 返回数字代表的 IP 地址
password(str) 返回字符串 str 的加密版本,一个 41 位长的字符串,Removed in MySQL 8.0.11.
md5() 返回字符串 str 的 MD5 值,常用来对应用中的数据进行加密
  • 示例

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    45
    46
    47
    48
    49
    50
    51
    52
    53
    54
    55
    56
    57
    58
    59
    60
    61
    62
    > select database();

    +------------+
    | database() |
    +------------+
    | test |
    +------------+
    1 row in set (0.00 sec)

    > select version();

    +-----------+
    | version() |
    +-----------+
    | 8.0.11 |
    +-----------+
    1 row in set (0.01 sec)

    > select user();

    +----------------+
    | user() |
    +----------------+
    | root@localhost |
    +----------------+
    1 row in set (0.00 sec)

    > select inet_aton('192.16.0.100');

    +---------------------------+
    | inet_aton('192.16.0.100') |
    +---------------------------+
    | 3222274148 |
    +---------------------------+
    1 row in set (0.00 sec)

    > select inet_ntoa(3222274148);

    +-----------------------+
    | inet_ntoa(3222274148) |
    +-----------------------+
    | 192.16.0.100 |
    +-----------------------+
    1 row in set (0.00 sec)

    > select PASSWORD('123456');

    +-------------------------------------------+
    | PASSWORD('123456') |
    +-------------------------------------------+
    | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
    +-------------------------------------------+
    1 row in set (0.08 sec)

    > select md5('12345');

    +----------------------------------+
    | md5('12345') |
    +----------------------------------+
    | 827ccb0eea8a706c4c34a16891f84e7b |
    +----------------------------------+
    1 row in set (0.00 sec)
文章目录
  1. 1. 前言
  2. 2. 1、字符串函数
  3. 3. 2、数值函数
  4. 4. 3、日期和时间函数
  5. 5. 4、流程函数
  6. 6. 5、其它常用函数
隐藏目录