文章目录

  • 1 数学函数
  • 1.1 ABS
  • 1.2 CEIL和CEILIN
  • 1.3 FLOOR
  • 1.4 MOD
  • 1.5 ROUND
  • 1.6 TRUNCATE
  • 2 字符串函数
  • 2.1 CONCAT
  • 2.2 CONCAT_WS
  • 2.3 INSERT
  • 2.4 LOWER和UPPER
  • 2.5 LEFT和RIGHT
  • 2.6 LPAD和RPAD
  • 2.7 LTRIM和RTRIM
  • 2.8 REPEAT
  • 2.9 REPLACE
  • 2.10 STRCMP
  • 2.11 SUBSTRING
  • 3 日期和时间函数
  • 3.1 CURDATE
  • 3.2 CURTIME
  • 3.3 NOW
  • 3.4 UNIX_TIMESTAMP
  • 3.5 FROM_UNIXTIME
  • 3.6 DATE_FROMAT
  • 3.7 DATE_ADD
  • 3.8 DATEDIFF
  • 4 条件判断函数
  • 5 系统信息函数
  • 6 加密函数
  • 7 其他函数
  • 7.1 INET_ATON和INET_NTOA


MySQL数据库中提供了很丰富的函数。MySQL函数包括数学函数、字符串函数、日期和时间函数、条件判断函数、系统信息函数、加密函数、格式化函数等。函数的执行速度非常快，可以提高MySQL的处理速度，简化用户的操作。本章将详细介绍MySQL函数的相关知识

MySQL函数是MySQL数据库提供的内置函数。这些内置函数可以帮助用户更加方便地处理表中的数据。主要包括：

函数

作用

数学函数

用于处理数字，这类函数包括绝对值函数，正弦函数，余弦函数和获取随机数等。

字符串函数

用于处理字符串，其中包括字符串连接函数，字符串比较函数，字符串中字母大小写转换函数等。

日期和时间函数

用于处理日期和时间，其中包括获取当前时间的函数，获取当前日期的函数，返回年份的函数和返回日期的函数等。

条件判断函数

用于在SQL语句中控制条件选择，其中包括IF语句，CASE语句和WHERE语句等。

系统信息函数

用于获取MySQL数据库的系统信息，其中包括获取数据库名的函数，获取当前用户的函数和获取数据库版本的函数等

加密函数

用于对字符串进行加密解密，其中包括字符串加密函数和解密函数等

其他函数

包括格式化函数和锁函数等

MySQL的内置函数不但可以在SELECT查询语句中应用，同样也可以在INSERT、UPDATE和DELECT等语句中应用。例如，在INSERT语句中，应用日期时间函数获取系统的当前时间，并且将其添加到数据表中。MySQL内置函数可以对表中数据进行相应的处理，以便得到用户希望得到的数据。

1 数学函数

数学函数是MySQL中常用的一类函数，主要用于处理数字，包括整型和浮点数等。

函数

作用

ABS(x)

返回x的绝对值

CEIL(x),CEILIN(x)

返回不小于x的最小的整数值

FLOOR(x)

返回不大于x的最大整数值

RAND()

返回0~1的随机数

RAND(x)

返回0~1的随机数，x值相同时返回的随机数相同

SIGN(x)

返回参数作为-1，0或1的符号，该符号取决于x的值为负，0或正

PI()

返回圆周率的值。默认的显示小数位7位，然后MySQL内部会使用完全双精度

TRUNCATE(x,y)

返回数值x保留到小数点后y位的值,截断时不进行四舍五入

ROUND(x)

返回离x最近的整数

ROUND(x,y)

保留x小数点后y位的值，但截断时要进行四舍五入

POW(x,y),POWER(x,y)

返回x的y乘方的结果值

SORT(x)

返回非负数x的二次方根

EXP(x)

返回e的x乘方后的值（自然对数的底）

MOD(x,y)

返回x除以y以后的余数。x或y为null时，则返回null

LOG(x)

返回x的基数为2的对数

LOG10(x)

返回x的基数为10的对数

RADIANS(x)

将角度转为弧度

DEGREES(x)

将弧度转换为角度

SIN(x)

返回x的正弦，其中x在为弧度值

ASIN(x)

返回x的反正弦，即正弦为x的值。若x不在-1~1之间，则返回NULL

COS(x)

返回x的余弦，x为弧度值

ACOS(x)

返回x的反余弦，即余弦是x的值，若x不在-1~1之间，则返回NULL

TAN(x)

返回x的正切值，x 为给定的弧度值

ATAN(x),ATAN2(x,y)

返回两个变量x及y的反正切，它类似于y或x的反正切计算。除非两个参数的符号均用于确定结果所在象限

COT(x)

返回x的余切

1.1 ABS

mysql> select abs(-0.1),abs(0.1),abs(0),abs(-0),abs(null);
+-----------+----------+--------+---------+-----------+
| abs(-0.1) | abs(0.1) | abs(0) | abs(-0) | abs(null) |
+-----------+----------+--------+---------+-----------+
|       0.1 |      0.1 |      0 |       0 |      NULL |
+-----------+----------+--------+---------+-----------+
1 row in set (0.00 sec)

mysql>

1.2 CEIL和CEILIN

mysql> select ceil(1.5),ceil(1.9),ceil(1.4),ceil(0),ceil(null);
+-----------+-----------+-----------+---------+------------+
| ceil(1.5) | ceil(1.9) | ceil(1.4) | ceil(0) | ceil(null) |
+-----------+-----------+-----------+---------+------------+
|         2 |         2 |         2 |       0 |       NULL |
+-----------+-----------+-----------+---------+------------+
1 row in set (0.00 sec)

mysql>

1.3 FLOOR

mysql> select floor(1.5),floor(1.9),floor(1.4),floor(0),ceil(null);
+------------+------------+------------+----------+------------+
| floor(1.5) | floor(1.9) | floor(1.4) | floor(0) | ceil(null) |
+------------+------------+------------+----------+------------+
|          1 |          1 |          1 |        0 |       NULL |
+------------+------------+------------+----------+------------+
1 row in set (0.00 sec)

mysql>

1.4 MOD

mysql> select mod(9,2),mod(null,2),mod(2,null),mod(0,9),mod(9,0);
+----------+-------------+-------------+----------+----------+
| mod(9,2) | mod(null,2) | mod(2,null) | mod(0,9) | mod(9,0) |
+----------+-------------+-------------+----------+----------+
|        1 |        NULL |        NULL |        0 |     NULL |
+----------+-------------+-------------+----------+----------+
1 row in set, 1 warning (0.01 sec)

mysql>

1.5 ROUND

mysql> select round(1.45),round(1.45,1),round(1.45,3),round(1.45,null);
+-------------+---------------+---------------+------------------+
| round(1.45) | round(1.45,1) | round(1.45,3) | round(1.45,null) |
+-------------+---------------+---------------+------------------+
|           1 |           1.5 |         1.450 |             NULL |
+-------------+---------------+---------------+------------------+
1 row in set (0.00 sec)

mysql>

1.6 TRUNCATE

当y值是小数时，会进行四舍五入。例如y为1.6时，默认为2。

mysql> select truncate(1.45,0),truncate(1.45,1),truncate(1.45,1.6),truncate(1.45,-1.6),truncate(1.45,null);
+------------------+------------------+--------------------+---------------------+---------------------+
| truncate(1.45,0) | truncate(1.45,1) | truncate(1.45,1.6) | truncate(1.45,-1.6) | truncate(1.45,null) |
+------------------+------------------+--------------------+---------------------+---------------------+
|                1 |              1.4 |               1.45 |                   0 |                NULL |
+------------------+------------------+--------------------+---------------------+---------------------+
1 row in set (0.00 sec)

mysql>

2 字符串函数

字符串函数是MySQL中最常用的一类函数，主要用于处理表中的字符串

函数

作用

CHAR_LENGTH(s)

返回字符串s的字符数

LENGTH(s)

返回值为字符串s的长度，单位为字节。一个多字节字符算作多字节。这意味着对于一个包含5个2字节字符的字符串，LENGTH()的返回值为10，而CHAR_LENGTH()的返回值为5

CONCAT(s1,s2,...)

返回结果为连接参数产生的字符串。如有任何一个参数为NULL，则返回值为NULL。或许有一个或多个参数。

CONCAT_WS(x,s1,s2,...)

同CONCAT(s1,s2,…)函数，但是每个字符串直接要加上x

INSERT(s1,x,len,s2)

将字符串s1从第x位置开始，len个字符长的子串替换为字符串s2

UPPER(s),UCASE(s)

将字符串s的所有字母都变成大写字母

LOWER(s),LCASE(s)

将字符串s的所有字母都变成小写字母

`LEFT(s,n)

`返回从字符串s开始的最左n个字符。如果n为NULL，则返回NULL

RIGHT(s,n)

从字符串是开始，返回最右n个字符。如果n为NULL，则返回NULL

LPAD(s1,len,s2)

返回字符串s1,其左边由字符串s2填补到len字符长度。假如s1的长度大于len，则返回值被缩短至len字符相同长度

RPAD(s1,len,s2)

返回字符串s1，其右边被字符串s2填补至len字符长度。假如字符串s1的长度大于len，则返回值被缩短到与len字符相同长度

LTRIM(s)

返回字符串s，其引导空格字符被删除

RTRIM(s)

返回字符串s，其结尾空格字符被删除

TRIM(s)

去掉字符串s开始处和结尾处的空格

TRIM(s1 FROM s)

去掉字符串s开始处和结尾处的字符串s1

REPEAT(s,n)

将字符串s重复n次

SPACE(n)

返回n个空格

REPLACE(s,s1,s2)

用字符串s2替代字符串s中的字符串s1

STRCMP(s1,s2)

比较字符串s1和s2的ASCII码值大小

SUBSTRING(s,n,len)

获取从字符串s中的第n个位置开始长度为len的字符串

MID(s,n,len)

同SUBSTRING(s,n,len)

LOCATE(s1,s),POSITION(s1 IN s)

从字符串s中获取s1的开始位置

INSTR(s,s1)

查找字符串s1在s中的位置，返回首次出现位置的索引值

REVERSE(s)

将字符串s的顺序反过来

ELT(n,s1,s2,...)

返回第n个字符串

EXPORT_SET(bits,on,off[,separator[,number_of_bits]])

返回一个字符串，生成规则如下：针对bits的二进制格式，如果其位为1，则返回一个on值；如果其位为0，则返回off值。每个字符串使用separator进行分隔，默认值为“.”。number_of_bits参数指定bits可用的位数，默认为64位。例如：生成数字182的二进制（10110110）替换格式，以“@”作为分隔符，设置有效位为6位。其语句如下：select EXPORT_SET(182,'Y','N','@',6);其运行结果为：N@Y@Y@N@Y@Y

FIELD(s,s1,s2,...)

返回第一个与字符串s匹配的字符串的位置

FIND_IN_SET(s1,s2)

返回在字符串s2中与s1匹配的字符串的位置

MAKE_SET(x,s1,s2,...)

按x的二进制数从s1,s2,…sn中选取字符串

2.1 CONCAT

mysql> select concat('ab','cd'),concat('ab',null),concat(1,'ab'),concat(1,2),concat(2,bin(10));
+-------------------+-------------------+----------------+-------------+-------------------+
| concat('ab','cd') | concat('ab',null) | concat(1,'ab') | concat(1,2) | concat(2,bin(10)) |
+-------------------+-------------------+----------------+-------------+-------------------+
| abcd              | NULL              | 1ab            | 12          | 21010             |
+-------------------+-------------------+----------------+-------------+-------------------+
1 row in set (0.00 sec)

mysql>

2.2 CONCAT_WS

该函数可以实现指定分隔符并拼接字符串。

mysql> select concat_ws('^','a','b','c');
+----------------------------+
| concat_ws('^','a','b','c') |
+----------------------------+
| a^b^c                      |
+----------------------------+
1 row in set (0.00 sec)

mysql>

2.3 INSERT

字符串的位置从1开始。

mysql> select insert('12345678',2,3,'abc');
+------------------------------+
| insert('12345678',2,3,'abc') |
+------------------------------+
| 1abc5678                     |
+------------------------------+
1 row in set (0.00 sec)

mysql> select insert('12345678',2,3,'abcdef');
+---------------------------------+
| insert('12345678',2,3,'abcdef') |
+---------------------------------+
| 1abcdef5678                     |
+---------------------------------+
1 row in set (0.00 sec)

mysql>

2.4 LOWER和UPPER

mysql> select LOWER('aBcD'),UPPER('aBcD'),LCASE('aBcD'),UCASE('aBcD');
+---------------+---------------+---------------+---------------+
| LOWER('aBcD') | UPPER('aBcD') | LCASE('aBcD') | UCASE('aBcD') |
+---------------+---------------+---------------+---------------+
| abcd          | ABCD          | abcd          | ABCD          |
+---------------+---------------+---------------+---------------+
1 row in set (0.00 sec)

mysql>

2.5 LEFT和RIGHT

mysql> select LEFT('123456',3),RIGHT('123456',3),LEFT('123456',null),RIGHT('123456',null),LEFT('123456',0),RIGHT('123456',0);
+------------------+-------------------+---------------------+----------------------+------------------+-------------------+
| LEFT('123456',3) | RIGHT('123456',3) | LEFT('123456',null) | RIGHT('123456',null) | LEFT('123456',0) | RIGHT('123456',0) |
+------------------+-------------------+---------------------+----------------------+------------------+-------------------+
| 123              | 456               | NULL                | NULL                 |                  |                   |
+------------------+-------------------+---------------------+----------------------+------------------+-------------------+
1 row in set (0.00 sec)

mysql>

2.6 LPAD和RPAD

mysql> select lpad('123',5,'a'),rpad('123',5,'b'),lpad('123',0,'a'),lpad('123',5,null),lpad('123',4,'aaa');
+-------------------+-------------------+-------------------+--------------------+---------------------+
| lpad('123',5,'a') | rpad('123',5,'b') | lpad('123',0,'a') | lpad('123',5,null) | lpad('123',4,'aaa') |
+-------------------+-------------------+-------------------+--------------------+---------------------+
| aa123             | 123bb             |                   | NULL               | a123                |
+-------------------+-------------------+-------------------+--------------------+---------------------+
1 row in set (0.01 sec)

mysql>

2.7 LTRIM和RTRIM

mysql> select ltrim('  abcd'),rtrim('abcd  '),trim(' abcd '),trim('a' from 'aabcdaa');
+-----------------+-----------------+----------------+--------------------------+
| ltrim('  abcd') | rtrim('abcd  ') | trim(' abcd ') | trim('a' from 'aabcdaa') |
+-----------------+-----------------+----------------+--------------------------+
| abcd            | abcd            | abcd           | bcd                      |
+-----------------+-----------------+----------------+--------------------------+
1 row in set (0.00 sec)

mysql>

2.8 REPEAT

mysql> select repeat('abc',3),repeat('abc',0),repeat('abc',null);
+-----------------+-----------------+--------------------+
| repeat('abc',3) | repeat('abc',0) | repeat('abc',null) |
+-----------------+-----------------+--------------------+
| abcabcabc       |                 | NULL               |
+-----------------+-----------------+--------------------+
1 row in set (0.00 sec)

mysql>

2.9 REPLACE

mysql> select replace('abcd','a','b'),replace('abcd','a',null),replace('abcd',null,'a');
+-------------------------+--------------------------+--------------------------+
| replace('abcd','a','b') | replace('abcd','a',null) | replace('abcd',null,'a') |
+-------------------------+--------------------------+--------------------------+
| bbcd                    | NULL                     | NULL                     |
+-------------------------+--------------------------+--------------------------+
1 row in set (0.00 sec)

mysql>

2.10 STRCMP

STRCMP(s1,s2)，比较字符串s1和s2的ASCII码值大小。如果s1比s2小，则返回-1；如果s1和s2相等返回0；如果s1比s2大，则返回1。

mysql> select strcmp('a','b'),strcmp('c','b'),strcmp('c','abcd'),strcmp(null,'z'),strcmp('z',null);
+-----------------+-----------------+--------------------+------------------+------------------+
| strcmp('a','b') | strcmp('c','b') | strcmp('c','abcd') | strcmp(null,'z') | strcmp('z',null) |
+-----------------+-----------------+--------------------+------------------+------------------+
|              -1 |               1 |                  1 |             NULL |             NULL |
+-----------------+-----------------+--------------------+------------------+------------------+
1 row in set (0.00 sec)

mysql>

2.11 SUBSTRING

字符串起始位置从1开始。

mysql> select substring('123456',2,3),substring('123456',0,3),substring('123456',null,3),substring('123456',2,0);
+-------------------------+-------------------------+----------------------------+-------------------------+
| substring('123456',2,3) | substring('123456',0,3) | substring('123456',null,3) | substring('123456',2,0) |
+-------------------------+-------------------------+----------------------------+-------------------------+
| 234                     |                         | NULL                       |                         |
+-------------------------+-------------------------+----------------------------+-------------------------+
1 row in set (0.00 sec)

mysql>

3 日期和时间函数

日期和时间函数是MySQL中另一最常用的函数，主要用于对表中的日期和时间数据的处理。

函数

作用

CURDATE(),CURRENT_DATE()

返回当前日期

CURTIME(),CURRENT_TIME()

返回当前时间

NOW(),CURRENT_TIMESTAMP(),LOCALTIME(),SYSDATE(),LOCALTIMESTAMP()

返回当前日期与时间

UNIX_TIMESTAMP()

以UNXI时间戳的形式返回当前时间

UNIX_TIMESTAMP(d)

将时间d以UNIX时间戳的形式返回

FROM_UNIXTIME(d)

把UNIX时间戳的时间转换为普通格式的时间

UTC_DATE()

返回UTC(Universal Coordinated Time 国际协调时间)日期

UTC_TIME()

返回UTC时间

MONTH(d)

返回日期d中的月份值，范围时1~12

MONTHNAME(d)

返回日期d中的月份名称，如January，February等

DAYNAME(d)

返回日期d是星期几，如Monday，Tuesday等

DAYOFWEEK(d)

返回日期d是星期几，1表示星期日，2表示星期一等

WEEKDAY(d)

返回日期d是星期几，0表示星期一，1表示星期二等

WEEK(d)

计算日期d是本年的第几个星期，范围是0~53

WEEKOFYEAR(d)

计算日期d是本年的第几个星期，范围时1~53

DAYOFYEAR(d)

计算日期d是本年的第几天

DAYOFMONTH(d)

计算日期d是本月的第几天

YEAR(d)

返回日期d中的年份值

QUARTER(d)

返回日期d是第几季度，范围是1~4

HOUR(t)

返回时间t中的小时值

MINUTE(t)

返回时间t中的分钟值

SECOND(t)

返回时间t中的秒钟值

EXTRACT(type FROM d)

从日期d中获取指定的值，type指定返回的值，如YEAR，HOUR等

TIME_TO_SEC(t)

将时间转换为秒

SEC_TO_TIME(s)

将以秒为单位的时间s转换为时分秒的格式

TO_DAYS(d)

计算日期d~0000年1月1日的天数

FROM_DAYS(d)

计算从0000年1月1日开始n天后的日期

DATEDIFF(d1,d2)

计算日期d1~d2之间相隔的天数

ADDDATE(d,n)

计算起始日期d加上n天的日期

ADDDATE(d,INTERVAL expr type)

计算起始日期d加上一个时间段后的日期select ADDDATE('2011-07-01'，INTERVAL，'12' YEAR_MONTH);

DATE_ADD(d,INTERVAL expr type)

同ADDDATE(d,INTERVAL expr type)

SUBDATE(d,n)

计算起始日期d减去n天后的日期

SUBDATE(d,INTERVAL expr type)

计算起始日期d减去一个时间段后的日期

ADDTIME(t,n)

计算起始时间t加上n秒的时间

SUBTIME(t,n)

计算起始时间t减去n秒的时间

DATE_FROMAT(d,f)

按照表达式f的要求显示日期d

TIME_FORMAT(t,f)

按照表达式f的要求显示时间t

GET_FORMAt(type,s)

根据字符串s获取type类型数据的显示格式

STR_TO_DATE(str, format)

根据fmt格式字符串将str字符串转换为日期值。可能会根据输入和格式字符串返回DATE，TIME或DATETIME值。如果输入字符串是非法的，则返回NULL。

3.1 CURDATE

mysql> select curdate();
+------------+
| curdate()  |
+------------+
| 2022-07-08 |
+------------+
1 row in set (0.00 sec)

mysql>

3.2 CURTIME

mysql> select curtime();
+-----------+
| curtime() |
+-----------+
| 15:41:28  |
+-----------+
1 row in set (0.00 sec)

mysql>

3.3 NOW

mysql> select now(),CURRENT_TIMESTAMP(),LOCALTIME(),SYSDATE(),LOCALTIMESTAMP();
+---------------------+---------------------+---------------------+---------------------+---------------------+
| now()               | CURRENT_TIMESTAMP() | LOCALTIME()         | SYSDATE()           | LOCALTIMESTAMP()    |
+---------------------+---------------------+---------------------+---------------------+---------------------+
| 2022-07-08 15:43:20 | 2022-07-08 15:43:20 | 2022-07-08 15:43:20 | 2022-07-08 15:43:20 | 2022-07-08 15:43:20 |
+---------------------+---------------------+---------------------+---------------------+---------------------+
1 row in set (0.01 sec)

mysql>

3.4 UNIX_TIMESTAMP

mysql> select unix_timestamp(now()),unix_timestamp('2022/01/01 11:11:11'),unix_timestamp('2022-01-01 11:11:11');
+-----------------------+---------------------------------------+---------------------------------------+
| unix_timestamp(now()) | unix_timestamp('2022/01/01 11:11:11') | unix_timestamp('2022-01-01 11:11:11') |
+-----------------------+---------------------------------------+---------------------------------------+
|            1657266313 |                            1641006671 |                            1641006671 |
+-----------------------+---------------------------------------+---------------------------------------+
1 row in set (0.00 sec)

mysql>

3.5 FROM_UNIXTIME

mysql> select from_unixtime(1641006671);
+---------------------------+
| from_unixtime(1641006671) |
+---------------------------+
| 2022-01-01 11:11:11       |
+---------------------------+
1 row in set (0.00 sec)

mysql>

3.6 DATE_FROMAT

DATE_FROMAT(data,fmt)按照字符串fmt格式化日期date值，其中fmt的日期格式可以为：

格式符

格式说明

%S,%s

两位数字形式的秒（00，01，…59）

%i

两位数字形式的分（00，01，…59）

%H

两位数字形式的小时，24小时（00，01，…23）

%H

两位数字形式的小时，24小时（00，01，…23）

%h,%I

两位数字形式的小时，12小时（01，02，…12）

%k

数字形式的小时，24小时（0，1，…23）

%l

数字形式的小时，12小时（1，2，…12）

%T

24小时的时间形式（hh:mm:ss）

%r

12小时的时间形式（hh:mm:ssAM 或 hh:mm:ssPM）

%p

AM或PM

%W

一周中每一天的名称（Sunday,Monday,…Saturday）

%a

一周中每一天名称的缩写（Sun,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

缩写的月名（Jan,Feb,…Dec）

%m

两位数字表示的月份（01，02，…12）

%c

数字表示的月份（1，2，…12）

%Y

4位数字表示的年份

%y

两位数字表示的年份

%%

直接值“%”

mysql> select date_format(now(),'%Y-%m-%d'),date_format('2022-01-01','%Y/%m/%d %H');
+-------------------------------+-----------------------------------------+
| date_format(now(),'%Y-%m-%d') | date_format('2022-01-01','%Y/%m/%d %H') |
+-------------------------------+-----------------------------------------+
| 2022-07-08                    | 2022/01/01 00                           |
+-------------------------------+-----------------------------------------+
1 row in set (0.00 sec)

mysql>

3.7 DATE_ADD

DATE_ADD(date,INTERVAL expr type)函数，返回与所给日期date相差INTERVAL时间段的日期。其中：

  • INTERVAL是间隔类型关键字；
  • expr是一个表达式，这个表达式对应后面的类型；
  • type是间隔类型；

MySQL提供了13种间隔类型：

表达式类型

描述

格式

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

mysql> select now() current,date_add(now(),INTERVAL 31 day) after31days,date_add(now(),INTERVAL '-1_-2' year_month);
+---------------------+---------------------+---------------------------------------------+
| current             | after31days         | date_add(now(),INTERVAL '-1_-2' year_month) |
+---------------------+---------------------+---------------------------------------------+
| 2022-07-08 16:38:50 | 2022-08-08 16:38:50 | 2021-05-08 16:38:50                         |
+---------------------+---------------------+---------------------------------------------+
1 row in set (0.00 sec)

mysql>

3.8 DATEDIFF

DATEDIFF(date1,date2)函数，用来计算两个日期之间相差的天数。

mysql> select datediff('2021-01-01',now()),datediff('2022-01-02','2021-07-06');
+------------------------------+-------------------------------------+
| datediff('2021-01-01',now()) | datediff('2022-01-02','2021-07-06') |
+------------------------------+-------------------------------------+
|                         -553 |                                 180 |
+------------------------------+-------------------------------------+
1 row in set (0.01 sec)

mysql>

4 条件判断函数

条件函数用来在SQL语句中进行条件判断。根据不同的条件，执行不同的SQL语句。

函数

作用

IF(expr,v1,v2)

如果表达式expr成立，则执行v1，否则执行v2

IFNULL(v1,v2)

如果v1不为空，则显示v1的值，否则显示v2的值

CASE WHEN expr1 THEN v1 [WHEN expr2 THEN v2...][ELSE vn]END

CASE表示函数开始，END表示函数结束。如果表达式expr1成立，则返回v1的值；如果表达式expr2成立，则返回v2的值。以此类推，最后遇到else时，返回vn的值。

CASE expr WHEN e1 THEN v1 [WHEN e2 THEN v2...][ELSE vn]END

CASE表示函数开始，END表示函数结束。如果表达式expr取值为e1,则返回v1的值；如果表达式expr取值为e2，则返回v2的值，以此类推，最后遇到ELSE，则返回vn的值。

5 系统信息函数

系统信息函数用来查询MySQL数据库的系统信息。

函数

作用

示例

VERSION()

获取数据库的版本号

select VERSION();

CONNECTION_ID()

获取服务器的连接数

select CONNECTION_ID();

DATABASE(),SCHEMA()

获取当前数据库名

select DATABASE(),SCHEMA();

USER(),SYSTEM_USER(),SESSION_USER()

获取当前用户

select USER(),SYSTEM_USER(),SESSION_USER();

CURRENT_USER()

获取当前用户

select CURRENT_USER();

CHARSET(str)

获取字符串str的字符集

select CHARSET('mrsoft');

COLLATION(str)

获取字符串str的字符排列方式

select COLLATION('mrsoft');

LAST_INSERT_ID()

获取最近生成的AUTO_INCREMENT值

select LAST_INSERT_ID();

6 加密函数

加密函数是MySQL中用来对数据进行加密的函数。因为数据库中有些很敏感的信息不希望被其他人看到，所以就可以通过加密的方式来使这些数据变成看似乱码的数据。

函数

作用

示例

PASSWORD(str)

对字符串str进行加密。经此函数加密后的数据是不可逆的。其经常用于对用户注册的密码进行加密处理

对字符串mrsoft进行加密，其语句如下：select PASSWORD('mrsoft');

MD5(str)

对字符串str进行加密。经常用于对普通数据进行加密。

select MD5('mrsoft');

ENCODE(str,pswd_str)

使用字符串pswd_str来加密字符串str。加密的结果时一个二进制数，必须使用BLOB类型的字段来保存它

使用字符串mr对mrsoft进行加密处理，select ENCODE('mrsoft','mr');

DECODE(crypt_str,pswd_str)

使用字符串pswd_str来为crypt_str解密。crypt_str是通过ENCODE(str,pswd_str)加密后的二进制数据。字符串pswd_str应该与加密时的字符串pswd_str是相同的。

select DECODE(ENCODE('mrsoft','mr'),'mr');

  • PASSWORD(str)函数经常用来给密码加密。MySQL用户需要设置密码，用户不能将未加密的密码直接存储到MySQL的user表中。因为登录MySQL数据库时，数据库系统会将输入的密码先通过PASSWORD(str)函数加密，然后与数据库中的密码进行比较，匹配成功后才可以登录。

7 其他函数

MySQL中除了上述内置函数以外，还包含很多函数。例如，数字格式化函数FORMAT(x，n)，IP地址与数字的转换函数INET_ATON(ip)，还有加锁函数GET_LOCT(name，time)、解锁函数RELEASE_LOCK(name)等。

函数

作用

示例

FORMAT(x,n)

将数字x进行格式化，将x保留到小数点后n位。这个过程需要四舍五入

ASCII(s)

返回字符串s的第一个字符的ASCII码

BIN(x)

返回x的二进制编码

HEX(x)

返回x的十六进制编码

OCT(x)

返回x的八进制编码

CONV(x,f1,f2)

将x从f1进制变成f2进制数

INET_ATON(IP)

可以将IP地址转换为数字表示

select INET_ATON('192.168.30.1');

INET_NTOA(N)

可以将数字n转换成IP的形式

select INET_NTOA(3232243201);

GET_LOCT(name,time)

定义一个名称为name，持续时间长度为tiime秒的锁。锁定成功，返回1；如果尝试超时，返回0；如果遇到错误，返回NULL。

RELEASE_LOCK(name)

接触名称为name的锁。如果解锁成功，返回1；如果尝试超时，返回0；如果解锁失败，返回NULL

IS_FREE_LOCK(name)

判断是否使用名为name的锁。如果使用，返回0；否则，返回1

BENCHMARK(count,expr)

将表达式expr重复执行count次，然后返回执行时间。该函数可以用来判断MySQL处理表达式的速度

CONVERT(s USING cs)

将字符串s的字符集变成cs

CAST(x AS type)

将x变成type类型，CAST(x AS type)和CONVERT(x，type)这两个函数只对BINARY，CHAR，DATE，DATATIME，TIME，SIGNED INTEGER，UNSIGNED INTEGER这些类型起作用。但两种方法只是改变了输出值的数据类型，并没有改变表中字段的类型。

7.1 INET_ATON和INET_NTOA

INET_ATON和INET_NTOA函数主要的用途是将字符串的IP地址转换为数字表示的网络字节序，这样可以更方便地进行IP或者网段的比较。

mysql> select * from tip;
+--------------+
| ip           |
+--------------+
| 192.168.1.1  |
| 192.168.1.3  |
| 192.168.1.6  |
| 192.168.1.10 |
| 192.168.1.20 |
| 192.168.1.30 |
+--------------+
6 rows in set (0.00 sec)

mysql> select * from tip where ip>='192.168.1.3' and ip <='192.168.1.20';
Empty set (0.00 sec)

mysql> select * from tip where inet_aton(ip)>=inet_aton('192.168.1.3') and inet_aton(ip) <=inet_aton('192.168.1.20');
+--------------+
| ip           |
+--------------+
| 192.168.1.3  |
| 192.168.1.6  |
| 192.168.1.10 |
| 192.168.1.20 |
+--------------+
4 rows in set (0.00 sec)

mysql>