mysql函数全面总结

吾爱主题阅读：91 2024-04-02 08:07:05 评论：0

1、MySQL中常用字符串函数

函数	功能
CANCAT(S1,S2,…Sn)	连接 S1,S2,…Sn 为一个字符串
INSERT(str,x,y,instr)	将字符串 str 从第 x 位置开始，y 个字符长的子串替换为字符串 instr
LOWER(str)	将字符串 str 中所有字符变为小写
UPPER(str)	将字符串 str 中所有字符变为大写
LEFT(str ,x)	返回字符串 str 最左边的 x 个字符
RIGHT(str,x)	返回字符串 str 最右边的 x 个字符
LPAD(str,n ,pad)	用字符串 pad 对 str 最左边进行填充，直到长度为 n 个字符长度
RPAD(str,n,pad)	用字符串 pad 对 str 最右边进行填充，直到长度为 n 个字符长度
LTRIM(str)	去掉字符串 str 左侧的空格
RTRIM(str)	去掉字符串 str 行尾的空格
REPEAT(str,x)	返回 str 重复 x 次的结果
REPLACE(str,a,b)	用字符串 b 替换字符串 str 中所有出现的字符串 a
STRCMP(s1,s2)	比较字符串 s1 和 s2
TRIM(str)	去掉字符串行尾和行头的空格
SUBSTRING(str,x,y)	返回从字符串 str x 位置起 y 个字符长度的字串

1：CANCAT(S1,S2,…Sn)函数，把传入的参数连接成为一个字符串。

注意：和null拼接后直接返回NULL

1 2 3 4 5 6 7 mysql> select concat( 'aaa' , 'bbb' , 'd' ),concat( 'dd' , null ); + -------------------------+-------------------+ | concat( 'aaa' , 'bbb' , 'd' ) | concat( 'dd' , null ) | + -------------------------+-------------------+ | aaabbbd | NULL | + -------------------------+-------------------+ 1 row in set (0.00 sec)

2：INSERT(str ,x,y,instr)函数：将字符串 str 从第 x 位置开始，y 个字符长的子串替换为字符串 instr。

将字符串“123456”中从第3个字符开始，后面的3个字符替换成“me”

1 2 3 4 5 6 7 mysql> select insert ( '123456' ,3,3, 'me' ); + ---------------------------+ | insert ( '123456' ,3,3, 'me' ) | + ---------------------------+ | 12me6 | + ---------------------------+ 1 row in set (0.02 sec)

3：LOWER(str)和 UPPER(str)函数：把字符串转换成小写或大写。

1 2 3 4 5 6 7 mysql> select lower ( "ZHANG" ), upper ( "zhang" ); + ----------------+----------------+ | lower ( "ZHANG" ) | upper ( "zhang" ) | + ----------------+----------------+ | zhang | ZHANG | + ----------------+----------------+ 1 row in set (0.00 sec)

4：LEFT(str,x)和 RIGHT(str,x)函数：分别返回字符串最左边的 x 个字符和最右边的 x 个字符。注意:如果第二个参数是 NULL，那么将不返回任何字符串。

1 2 3 4 5 6 7 mysql> select left ( 'zhang' ,2), left ( 'zhang' , null ), right ( 'zhang' ,2), left ( 'zhang' ,10); + -----------------+--------------------+------------------+------------------+ | left ( 'zhang' ,2) | left ( 'zhang' , null ) | right ( 'zhang' ,2) | left ( 'zhang' ,10) | + -----------------+--------------------+------------------+------------------+ | zh | NULL | ng | zhang | + -----------------+--------------------+------------------+------------------+ 1 row in set (0.00 sec)

5:LPAD(str,n ,pad)和 RPAD(str,n ,pad)函数：用字符串 pad 对 str 最左边和最右边进行填充,直到长度为 n 个字符长度。

1 2 3 4 5 6 7 mysql> select lpad( 'bin' ,8, 'zhang' ),rpad( 'zhang' ,8, 'bin' ),rpad( 'beijing' ,20, '2008' ); + -----------------------+-----------------------+---------------------------+ | lpad( 'bin' ,8, 'zhang' ) | rpad( 'zhang' ,8, 'bin' ) | rpad( 'beijing' ,20, '2008' ) | + -----------------------+-----------------------+---------------------------+ | zhangbin | zhangbin | beijing2008200820082 | + -----------------------+-----------------------+---------------------------+ 1 row in set (0.00 sec)

6:LTRIM(str)和 RTRIM(str)函数：去掉字符串 str 左侧和右侧空格。

7:REPEAT(str,x)函数：返回 str 重复 x 次的结果。

1 2 3 4 5 6 7 mysql> select repeat( 'mysql' ,5); + ---------------------------+ | repeat( 'mysql' ,5) | + ---------------------------+ | mysqlmysqlmysqlmysqlmysql | + ---------------------------+ 1 row in set (0.00 sec)

8:REPLACE(str,a,b)函数：用字符串 b 替换字符串 str 中所有出现的字符串 a。

1 2 3 4 5 6 7 mysql> select replace ( 'mysql' , 'sql' , 'ddd' ); + ------------------------------+ | replace ( 'mysql' , 'sql' , 'ddd' ) | + ------------------------------+ | myddd | + ------------------------------+ 1 row in set (0.00 sec)

9:STRCMP(s1,s2)函数：比较字符串 s1 和 s2 的 ASCII 码值的大小。如果 s1 比 s2 小，那么返回-1；如果 s1 与 s2 相等，那么返回 0；如果 s1 比 s2 大，那么返回 1。

1 2 3 4 5 6 7 mysql> select strcmp( 'a' , 'b' ),strcmp( 'b' , 'b' ),strcmp( 'c' , 'b' ),strcmp( 'a' , 'A' ); + -----------------+-----------------+-----------------+-----------------+ | strcmp( 'a' , 'b' ) | strcmp( 'b' , 'b' ) | strcmp( 'c' , 'b' ) | strcmp( 'a' , 'A' ) | + -----------------+-----------------+-----------------+-----------------+ | -1 | 0 | 1 | 0 | + -----------------+-----------------+-----------------+-----------------+ 1 row in set (0.01 sec)

10:TRIM(str)函数：去掉目标字符串的开头和结尾的空格。

1 2 3 4 5 6 7 mysql> select trim( ' 111 $ mysql $ ' ); + -----------------------------+ | trim( ' 111 $ mysql $ ' ) | + -----------------------------+ | 111 $ mysql $ | + -----------------------------+ 1 row in set (0.01 sec)

11:SUBSTRING(str,x,y)函数：返回从字符串 str 中的第 x 位置起 y 个字符长度的字串。

1 2 3 4 5 6 7 mysql> select substring ( 'mysqlisdd' ,4,4); + ----------------------------+ | substring ( 'mysqlisdd' ,4,4) | + ----------------------------+ | qlis | + ----------------------------+ 1 row in set (0.00 sec)

2、数值函数

函数	功能
ABS(x)	返回 x 的绝对值
CEIL(x)	返回大于 x 的最大整数值
FLOOR(x)	返回小于 x 的最大整数值
MOD(x，y)	返回 x/y 的模
RAND()	返回 0 到 1 内的随机值
ROUND(x,y)	返回参数 x 的四舍五入的有 y 位小数的值
TRUNCATE(x,y)	返回数字 x 截断为 y 位小数的结果

1： ABS(x)函数：返回 x 的绝对值。

1 2 3 4 5 6 7 mysql> select abs (-0.3), abs (0.3); + -----------+----------+ | abs (-0.3) | abs (0.3) | + -----------+----------+ | 0.3 | 0.3 | + -----------+----------+ 1 row in set (0.36 sec)

2：CEIL（x）函数，返回大于x的最大整数

1 2 3 4 5 6 7 mysql> select ceil(-0.2),ceil(0.2); + ------------+-----------+ | ceil(-0.2) | ceil(0.2) | + ------------+-----------+ | 0 | 1 | + ------------+-----------+ 1 row in set (0.00 sec)

3：FLOOR（x）函数，返回小于x的最大整数，和CEIL刚好相反

1 2 3 4 5 6 7 mysql> select floor(-0.2),floor(0.2); + -------------+------------+ | floor(-0.2) | floor(0.2) | + -------------+------------+ | -1 | 0 | + -------------+------------+ 1 row in set (0.00 sec)

4：MOD(x，y)函数：返回 x/y 的模。和 x%y 的结果相同，模数和被模数任何一个为 NULL 结果都为 NULL。如下例所示：

1 2 3 4 5 6 7 mysql> 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)

5：RAND（）函数，返回0到1之间的随机数

1 2 3 4 5 6 7 mysql> select rand(),rand(); + -------------------+---------------------+ | rand() | rand() | + -------------------+---------------------+ | 0.541937319135235 | 0.10546984067696132 | + -------------------+---------------------+ 1 row in set (0.00 sec)

6：ROUND(x,y)函数，返回参数 x 的四舍五入的有 y 位小数的值。如果是整数，将会保留 y 位数量的 0；如果不写 y，则默认 y 为 0，即将 x 四舍五入后取整。

1 2 3 4 5 6 7 mysql> select round(1.2,2),round(1.3),round(1,2); + --------------+------------+------------+ | round(1.2,2) | round(1.3) | round(1,2) | + --------------+------------+------------+ | 1.20 | 1 | 1 | + --------------+------------+------------+ 1 row in set (0.00 sec)

7：TRUNCATE(x,y)函数：返回数字 x 截断为 y 位小数的结果。（注意 TRUNCATE 和 ROUND 的区别在于 TRUNCATE 仅仅是截断，而不进行四舍五入。）

1 2 3 4 5 6 7 8 mysql> 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.31 sec)

3、日期和时间函数

MySQL 中的常用日期时间函数：

函数	功能
CURDATE()	返回当前日期
CURTIME()	返回当前时间
NOW()	返回当前的日期和时间
UNIX_TIMESTAMP(date)	返回日期 date 的 UNIX 时间戳
FROM_UNIXTIME	返回 UNIX 时间戳的日期值
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)	返回一个日期或时间值加上一个时间间隔的时间值
DATEDIFF(expr,expr2)	返回起始时间 expr 和结束时间 expr2 之间的天数

1：CURDATE()函数：返回当前日期，只包含年月日

1 2 3 4 5 6 7 mysql> select curdate(); + ------------+ | curdate() | + ------------+ | 2021-05-25 | + ------------+ 1 row in set (0.00 sec)

2：CURTIME()函数：返回当前时间，只包含时分秒

1 2 3 4 5 6 7 mysql> select curtime(); + -----------+ | curtime() | + -----------+ | 14:07:06 | + -----------+ 1 row in set (0.00 sec)

3：NOW()函数：返回当前的日期和时间，年月日时分秒全都包含。

1 2 3 4 5 6 7 mysql> select now(); + ---------------------+ | now() | + ---------------------+ | 2021-05-25 14:07:33 | + ---------------------+ 1 row in set (0.00 sec)

4：UNIX_TIMESTAMP(date)函数：返回日期 date 的 UNIX 时间戳。

1 2 3 4 5 6 7 mysql> select unix_timestamp(now()); + -----------------------+ | unix_timestamp(now()) | + -----------------------+ | 1621922906 | + -----------------------+ 1 row in set (0.30 sec)

5：FROM_UNIXTIME （ unixtime ）函数：返回 UNIXTIME 时间戳的日期值，和UNIX_TIMESTAMP(date)互为逆操作。

1 2 3 4 5 6 7 mysql> select from_unixtime(1621922906); + ---------------------------+ | from_unixtime(1621922906) | + ---------------------------+ | 2021-05-25 14:08:26 | + ---------------------------+ 1 row in set (0.31 sec)

6：WEEK(DATE)和 YEAR(DATE)函数：前者返回所给的日期是一年中的第几周，后者返回所给的日期是哪一年。

1 2 3 4 5 6 7 mysql> select week(now()), year (now()); + -------------+-------------+ | week(now()) | year (now()) | + -------------+-------------+ | 21 | 2021 | + -------------+-------------+ 1 row in set (0.00 sec)

7：HOUR(time)和 MINUTE(time)函数：前者返回所给时间的小时，后者返回所给时间的分钟。

1 2 3 4 5 6 7 mysql> select hour (now()), minute (now()); + -------------+---------------+ | hour (now()) | minute (now()) | + -------------+---------------+ | 14 | 11 | + -------------+---------------+ 1 row in set (0.00 sec)

8：MONTHNAME(date)函数：返回 date 的英文月份名称。

1 2 3 4 5 6 7 mysql> select monthname(now()); + ------------------+ | monthname(now()) | + ------------------+ | May | + ------------------+ 1 row in set (0.30 sec)

9：DATE_FORMAT(date,fmt)函数：按字符串 fmt 格式化日期 date 值，此函数能够按指定的格式显示日期，可用到的格式符如表：

格式符	格式说明
%S,%s	两位数字形式的秒（00,01,...,59）
%i	两位数字形式的分（00,01,...,59）
%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	缩写的月名（January,February,...,December）
%m	两位数字表示的月份（01,02,...,12）
%c	数字表示的月份（1,2,...,12）
%Y	4 位数字表示的年份
%y	两位数字表示的年份
%%	直接值“%”

1 2 3 4 5 6 7 mysql> select date_format(now(), '%M,%D,%Y' ); + -------------------------------+ | date_format(now(), '%M,%D,%Y' ) | + -------------------------------+ | May,25th,2021 | + -------------------------------+ 1 row in set (0.00 sec)

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

其中 INTERVAL 是间隔类型关键字，expr 是一个表达式，这个表达式对应后面的类型，type是间隔类型，MySQL 提供了 13 种间隔类型，

如下表：MySQL 中的日期间隔类型：

表达式类型	描述	格式
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 列返回距离当前日期31 天后的日期时间，第 3 列返回距离当前日期一年两个月后的日期时间。

1 2 3 4 5 6 7 mysql> 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 | + ---------------------+---------------------+------------------------+ | 2021-05-25 14:32:30 | 2021-06-25 14:32:30 | 2022-07-25 14:32:30 | + ---------------------+---------------------+------------------------+ 1 row in set (0.03 sec)

同样也可以用负数让它返回之前的某个日期时间，如下第 1 列返回了当前日期时间，第 2列返回距离当前日期 31 天前的日期时间，第 3 列返回距离当前日期一年两个月前的日期时间。

1 2 3 4 5 6 7 mysql> select now() current ,date_add(now(),interval -31 day ) bef31days,date_add(now(),interval '-1_-2' year_month) bef_oneyear_twomonth; + ---------------------+---------------------+----------------------+ | current | bef31days | bef_oneyear_twomonth | + ---------------------+---------------------+----------------------+ | 2021-05-25 14:34:38 | 2021-04-24 14:34:38 | 2020-03-25 14:34:38 | + ---------------------+---------------------+----------------------+ 1 row in set (0.00 sec)

11：DATEDIFF（date1，date2）函数：用来计算两个日期之间相差的天数

1 2 3 4 5 6 7 8 mysql> select datediff( '2013-09-01' ,now()); + ------------------------------+ | datediff( '2013-09-01' ,now()) | + ------------------------------+ | -2823 | + ------------------------------+ 1 row in set (0.30 sec)

4、流程函数

MySQL中的流程函数：

函数	功能
IF(value,t f)	如果 value 是真，返回 t；否则返回 f
IFNULL(value1,value2)	如果 value1 不为空返回 value1，否则返回 value2
CASE WHEN [value1] THEN[result1]…ELSE[default]END	如果 value1 是真，返回 result1，否则返回 default
CASE [expr] WHEN [value1] THEN[result1]…ELSE[default]END	如果 expr 等于 value1，返回 result1，否则返回 default

1：IF(value,t f)，例子：创建并初始化一个职员薪水表，插入一些测试数据，我们认为月薪在 2000 元以上的职员属于高薪，用“high”表示；而2000 元以下的职员属于低薪，用“low”表示

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 mysql> create table salary(userid int ,salary decimal (9,2)); Query OK, 0 rows affected (0.47 sec) mysql> insert into salary values (1,1000),(2,2000),(3,3000),(4,4000),(5,5000),(1, null ); Query OK, 6 rows affected (0.00 sec) Records: 6 Duplicates: 0 Warnings: 0 mysql> select * from salary; + --------+---------+ | userid | salary | + --------+---------+ | 1 | 1000.00 | | 2 | 2000.00 | | 3 | 3000.00 | | 4 | 4000.00 | | 5 | 5000.00 | | 1 | NULL | + --------+---------+ 6 rows in set (0.00 sec) mysql> select if(salary>2000, 'high' , 'low' ) from salary; + ------------------------------+ | if(salary>2000, 'high' , 'low' ) | + ------------------------------+ | low | | low | | high | | high | | high | | low | + ------------------------------+ 6 rows in set (0.31 sec)

2：IFNULL(value1,value2)函数，一般用来替换 NULL 值的，我们知道 NULL 值是不能参与数值运算的，下面这个语句就是把 NULL 值用 0 来替换

1 2 3 4 5 6 7 8 9 10 11 12 mysql> select ifnull(salary,0) from salary; + ------------------+ | ifnull(salary,0) | + ------------------+ | 1000.00 | | 2000.00 | | 3000.00 | | 4000.00 | | 5000.00 | | 0.00 | + ------------------+ 6 rows in set (0.00 sec)

3:CASE WHEN [value1] THEN[result1]…ELSE[default]END 函数：我们也可以用 case when…then 函数实现上面例子中高薪低薪的问题

1 2 3 4 5 6 7 8 9 10 11 12 mysql> select case when salary<=2000 then 'low' else 'high' end from salary; + ---------------------------------------------------+ | case when salary<=2000 then 'low' else 'high' end | + ---------------------------------------------------+ | low | | low | | high | | high | | high | | high | + ---------------------------------------------------+ 6 rows in set (0.29 sec)

4:CASE [expr] WHEN [value1] THEN[result1]…ELSE[default]END 函数：这里还可以分多种情况把职员的薪水分多个档次，比如下面的例子分成高、中、低 3 种情况。

1 2 3 4 5 6 7 8 9 10 11 12 mysql> select case salary when 1000 then 'low' when 2000 then 'mid' else 'high' end from salary; + -----------------------------------------------------------------------+ | case salary when 1000 then 'low' when 2000 then 'mid' else 'high' end | + -----------------------------------------------------------------------+ | low | | mid | | high | | high | | high | | high | + -----------------------------------------------------------------------+ 6 rows in set (0.00 sec)

5、其他常用函数

MySQL 中的其他常用函数：

函数	功能
DATABASE()	返回当前数据库名
VERSION()	返回当前数据库版本
USER()	返回当前登录用户名
INET_ATON(IP)	返回 IP 地址的数字表示
INET_NTOA(num)	返回数字代表的 IP 地址
PASSWORD(str)	返回字符串 str 的加密版本
MD5()	返回字符串 str 的 MD5 值

1：DATABASE()函数：返回当前数据库名。

1 2 3 4 5 6 7 mysql> select database (); + ------------+ | database () | + ------------+ | test | + ------------+ 1 row in set (0.00 sec)

2：VERSION()函数：返回当前数据库版本。

1 2 3 4 5 6 7 mysql> select version(); + -----------+ | version() | + -----------+ | 5.7.26 | + -----------+ 1 row in set (0.00 sec)

3：USER()函数：返回当前登录用户名。

1 2 3 4 5 6 7 mysql> select user (); + ----------------+ | user () | + ----------------+ | root@localhost | + ----------------+ 1 row in set (0.00 sec)

4：INET_ATON(IP)函数：返回 IP 地址的网络字节序表示。

1 2 3 4 5 6 7 mysql> select inet_aton( '192.168.1.1' ); + --------------------------+ | inet_aton( '192.168.1.1' ) | + --------------------------+ | 3232235777 | + --------------------------+ 1 row in set (0.30 sec)

5：INET_NTOA(num)函数：返回网络字节序代表的 IP 地址。

1 2 3 4 5 6 7 mysql> select inet_ntoa( '3232235777' ); + -------------------------+ | inet_ntoa( '3232235777' ) | + -------------------------+ | 192.168.1.1 | + -------------------------+ 1 row in set (0.00 sec)

6：PASSWORD(str)函数：返回字符串 str 的加密版本，一个 41 位长的字符串。

1 2 3 4 5 6 7 mysql> select password ( '1223456' ); + -------------------------------------------+ | password ( '1223456' ) | + -------------------------------------------+ | *3B5C2394E86BB91F1D03C5A1F2D3962BB287590B | + -------------------------------------------+ 1 row in set , 1 warning (0.33 sec)

7：MD5(str)函数：返回字符串 str 的 MD5 值，常用来对应用中的数据进行加密。

1 2 3 4 5 6 7 mysql> select md5( '123456' ); + ----------------------------------+ | md5( '123456' ) | + ----------------------------------+ | e10adc3949ba59abbe56e057f20f883e | + ----------------------------------+ 1 row in set (0.30 sec)