mysql 常用函数

前言： 可以用这样的命令查看帮助

mysql> ? from_unixtime
mysql> ? select
//查看表信息的命令：
mysql>desc table_name
mysql>show create table table_name \G;   \G选项的使得记录能够按照字段竖向排列，以便更好地显示较长内容。

字符串函数

函数	功能
CONCAT(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个字符长度的子串
REPEAT(str,count)	返回一个由字符串str重复count次数组成的字符串

CONCAT(S1,S2,...Sn) 函数：把传入的参数连接成一个字符串

mysql> select concat('aaa','bbb','ccc'),concat('aaa',null);
//任何字符串与NULL进行连接的结果都将是NULL
+---------------------------+--------------------+
| concat('aaa','bbb','ccc') | concat('aaa',null) |
+---------------------------+--------------------+
| aaabbbccc                 | NULL               |
+---------------------------+--------------------+

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

mysql>select INSERT('beijing2017you',12,3,'me');
+------------------------------------+
 | INSERT('beijing2017you',12,3,'me') |
 +------------------------------------+
 | beijing2017me                      |
 +------------------------------------+

LOWER(str)和UPPER(str) 函数：将字符串转换为小写或大写

mysql>select LOWER('BEIJING2017'),UPPER('beijing2017');
+----------------------+----------------------+
| LOWER('BEIJING2017') | UPPER('beijing2017') |
+----------------------+----------------------+
| beijing2017          | BEIJING2017          |
+----------------------+----------------------+

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

mysql> select LEFT('beijing2017',7),LEFT('beijing',null),RIGHT('beijing2017',4);
+-----------------------+----------------------+------------------------+
| LEFT('beijing2017',7) | LEFT('beijing',null) | RIGHT('beijing2017',4) |
+-----------------------+----------------------+------------------------+
| beijing               | NULL                 | 2017                   |
+-----------------------+----------------------+------------------------+

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

mysql> select LPAD('2017',20,'beijing'),RPAD('beijing',20,'2017');
+---------------------------+---------------------------+
| LPAD('2017',20,'beijing') | RPAD('beijing',20,'2017') |
+---------------------------+---------------------------+
| beijingbeijingbe2017      | beijing2017201720172      |
+---------------------------+---------------------------+

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

mysql> select LTRIM('  |beijing'),RTRIM('beijing|   ');
+---------------------+----------------------+
| LTRIM('  |beijing') | RTRIM('beijing|   ') |
+---------------------+----------------------+
| |beijing            | beijing|             |
+---------------------+----------------------+

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

mysql> select REPEAT('mysql',3);
+-------------------+
| REPEAT('mysql',3) |
+-------------------+
| mysqlmysqlmysql   |
+-------------------+

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

mysql> select REPLACE('beijing_2017','_2017','2008');
+----------------------------------------+
| REPLACE('beijing_2017','_2017','2008') |
+----------------------------------------+
| beijing2008                            |
+----------------------------------------+

STRCMP(s1,s2)函数：比较字符串s1和s2的ASCII码值大小

mysql> select STRCMP('a','b'),STRCMP('b','b'),STRCMP('c','b');
+-----------------+-----------------+-----------------+
| STRCMP('a','b') | STRCMP('b','b') | STRCMP('c','b') |
+-----------------+-----------------+-----------------+
|              -1 |               0 |               1 |
+-----------------+-----------------+-----------------+

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

mysql> select TRIM('  $ beijing2017  $   ');
+-------------------------------+
| TRIM('  $ beijing2017  $   ') |
+-------------------------------+
| $ beijing2017  $              |
+-------------------------------+

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

mysql> select SUBSTRING('beijing2017',8,4),SUBSTRING('beijing2017',1,7);
+------------------------------+------------------------------+
| SUBSTRING('beijing2017',8,4) | SUBSTRING('beijing2017',1,7) |
+------------------------------+------------------------------+
| 2017                         | beijing                      |
+------------------------------+------------------------------+

数值函数

函数	功能
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位小数的结果

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

mysql> select ABS(-0.8),ABS(0.8);
+-----------+----------+
| ABS(-0.8) | ABS(0.8) |
+-----------+----------+
|       0.8 |      0.8 |
+-----------+----------+

CEIL(x)函数：返回大于x的最小整数

mysql> select CEIL(-0.8),CEIL(0.8); 
+------------+-----------+
| CEIL(-0.8) | CEIL(0.8) |
+------------+-----------+
|          0 |         1 |
+------------+-----------+

FLOOR(x)函数：返回小于x的最大整数

mysql> select FLOOR(-0.8),FLOOR(0.8);
+-------------+------------+
| FLOOR(-0.8) | FLOOR(0.8) |
+-------------+------------+
|          -1 |          0 |
+-------------+------------+

MOD(x,y)：返回x/y的模

mysql> select MOD(15,10),MOD(1,11),MOD(NULL,10);
+------------+-----------+--------------+
| MOD(15,10) | MOD(1,11) | MOD(NULL,10) |
+------------+-----------+--------------+
|          5 |         1 |         NULL |
+------------+-----------+--------------+

RAND()：返回0~1内的随机数

mysql> select RAND(),RAND();
+--------------------+--------------------+
| RAND()             | RAND()             |
+--------------------+--------------------+
| 0.6605393799585657 | 0.6978235959055122 |
+--------------------+--------------------+
mysql> select CEIL(100*RAND()),CEIL(100*RAND());
+------------------+------------------+
| CEIL(100*RAND()) | CEIL(100*RAND()) |
+------------------+------------------+
|               51 |               45 |
+------------------+------------------+

ROUND(x,y)：返回参数x的四舍五入的有y位小数的值

mysql> 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 |
+------------+--------------+------------+

TRUNCATE(x,y)：返回数字x截断y位小数的结果

mysql> select ROUND(1.235,2),TRUNCATE(1.235,2);
+----------------+-------------------+
| ROUND(1.235,2) | TRUNCATE(1.235,2) |
+----------------+-------------------+
|           1.24 |              1.23 |
+----------------+-------------------+

日期函数

函数	功能
CURDATE()	返回当前日期
CURTIME()	返回当前时间
NOW()	返回当前的日期和时间
UNIX_TIMESTAMP(date)	返回日期date的UNIX的时间戳
FROM_UNIXTIME	返回UNIX时间戳的日期值
WEEK(date)	返回日期date为一年中的第几周
YEAR(date)	返回日期date的年份
HOUR(time)	返回time的小时值
MMINUTE(time)	返回time的分钟值
MONTHNAME(date)	返回date的月份名
DATE_FORMAT(date,fmt)	返回按字符串fmt格式化日期的date值
DATE_ADD(date,INTERVAL expr type)	返回一个日期或时间加上一个时间间隔的时间值
DATEDIFF(expr,expr2)	返回起始时间expr和结束时间expr2之间的天数

CURDATE()，CURTIME()，NOW()

mysql> select CURDATE(),CURTIME(),NOW();
+------------+-----------+---------------------+
| CURDATE()  | CURTIME() | NOW()               |
+------------+-----------+---------------------+
| 2017-03-15 | 17:35:44  | 2017-03-15 17:35:44 |
+------------+-----------+---------------------+

SYSDATE 与 NOW 比较

mysql> SELECT SYSDATE(), NOW();
+---------------------+---------------------+
| SYSDATE()           | NOW()               |
+---------------------+---------------------+
| 2017-08-10 20:44:38 | 2017-08-10 20:44:38 |
+---------------------+---------------------+
# 似乎`SYSDATE()`和[NOW()](http://www.yiibai.com/mysql/now.html)函数都返回一个相同的值，它是执行语句时当前日期和时间。

# 然而，`SYSDATE()`函数实际上返回执行时的时间，而`NOW()`函数返回一个常量时间，该语句开始执行

mysql> SELECT NOW(), SLEEP(5), NOW();
+---------------------+----------+---------------------+
| NOW()               | SLEEP(5) | NOW()               |
+---------------------+----------+---------------------+
| 2017-08-10 20:46:51 |        0 | 2017-08-10 20:46:51 |
+---------------------+----------+---------------------+

mysql>  SELECT SYSDATE(), SLEEP(5), SYSDATE();
+---------------------+----------+---------------------+
| SYSDATE()           | SLEEP(5) | SYSDATE()           |
+---------------------+----------+---------------------+
| 2017-08-10 20:48:14 |        0 | 2017-08-10 20:48:19 |
+---------------------+----------+---------------------+

UNIX_TIMESTAMP(date)，FROM_UNIXTIME

mysql> select UNIX_TIMESTAMP(NOW()),FROM_UNIXTIME(1489570679);
+-----------------------+---------------------------+
| UNIX_TIMESTAMP(NOW()) | FROM_UNIXTIME(1489570679) |
+-----------------------+---------------------------+
|            1489570846 | 2017-03-15 17:37:59       |
+-----------------------+---------------------------+

WEEK(date) ，MONTHNAME(date),YEAR(date)

mysql> select WEEK(NOW()),MONTHNAME(NOW()),YEAR(NOW());
+-------------+------------------+-------------+
| WEEK(NOW()) | MONTHNAME(NOW()) | YEAR(NOW()) |
+-------------+------------------+-------------+
|          11 | March            |        2017 |
+-------------+------------------+-------------+

DATE_FORMAT(date,fmt)

更详细的

mysql> select DATE_FORMAT(NOW(),'%M,%D,%Y');
+-------------------------------+
| DATE_FORMAT(NOW(),'%M,%D,%Y') |
+-------------------------------+
| March,15th,2017               |
+-------------------------------+
1 row in set (0.01 sec)
//接一个常用的转换例子
mysql>SELECT DATE_FORMAT(NOW(),'%Y-%m-%d');

DATE_ADD(date,INTERVAL expr type)

期中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

示例：

第一列返回了当前日期时间，第二列返回距离当前日期31天后的日期时> 间，第三列返回距离当前日期一年两个月后的日期时间

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 |
+---------------------+---------------------+------------------------+
| 2017-03-15 19:03:33 | 2017-04-15 19:03:33 | 2018-05-15 19:03:33    |
+---------------------+---------------------+------------------------+

第一列返回当期日期时间，第二列返回距离当前日期31天前的日期时间，第三列返回距离当前日期一年两个月前的日期时间

mysql> select NOW() current,DATE_ADD(NOW(),INTERVAL -31 day) before31days,DATE_ADD(NOW(),INTERVAL '-1_-2' year_month) before_oneyear_twomoth; 
+---------------------+---------------------+------------------------+
| current             | before31days        | before_oneyear_twomoth |
+---------------------+---------------------+------------------------+
| 2017-03-15 19:06:55 | 2017-02-12 19:06:55 | 2016-01-15 19:06:55    |
+---------------------+---------------------+------------------------+

DATEDIFF(expr,expr2)

mysql> select DATEDIFF('2017-01-01',NOW());
+------------------------------+
| DATEDIFF('2017-01-01',NOW()) |
+------------------------------+
|                          -73 |
+------------------------------+

流程函数

函数	功能
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等于valule1，返回result1，否则返回default

create table salary(userid int,salary decimal(9,2));
insert into salary values(1,1000),(2,2000),(3,3000),(4,4000),(5,5000),(1,null);

IF(value,t,f)

mysql> select IF(salary>2000,'high','low') from salary;
+------------------------------+
| IF(salary>2000,'high','low') |
+------------------------------+
| low                          |
| low                          |
| high                         |
| high                         |
| high                         |
| low                          |
+------------------------------+

IFNULL(value1,value2)

mysql> select IFNULL(salary,0) from salary;
+------------------+
| IFNULL(salary,0) |
+------------------+
|          1000.00 |
|          2000.00 |
|          3000.00 |
|          4000.00 |
|          5000.00 |
|             0.00 |
+------------------+

case when函数

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                                               |
+----------------------------------------------------+
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                                                                  |
+-----------------------------------------------------------------------+

case when 更多示例

其他常用函数

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

mysql> select DATABASE(),VERSION(),USER();
+------------+-----------+----------------+
| DATABASE() | VERSION() | USER()         |
+------------+-----------+----------------+
| test       | 5.7.17    | root@localhost |
+------------+-----------+----------------+

mysql> select INET_ATON('192.168.1.1'),INET_NTOA(3232235777);
+--------------------------+-----------------------+
| INET_ATON('192.168.1.1') | INET_NTOA(3232235777) |
+--------------------------+-----------------------+
|               3232235777 | 192.168.1.1           |
+--------------------------+-----------------------+

mysql> select PASSWORD('adf_2345DD'),MD5('123456');
+-------------------------------------------+----------------------------------+
| PASSWORD('adf_2345DD')                    | MD5('123456')                    |
+-------------------------------------------+----------------------------------+
| *BD1258F8130C30F4EA90715B5CE817C6D7773FE2 | e10adc3949ba59abbe56e057f20f883e |
+-------------------------------------------+----------------------------------+

最后编辑于：2019.09.18 14:55:01

人面猴
序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
沈念sama阅读 194,670评论 5赞 460
死咒
序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
沈念sama阅读 81,928评论 2赞 371
救了他两次的神仙让他今天三更去死
文/潘晓璐我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
开封第一讲书人阅读 141,926评论 0赞 320
道士缉凶录：失踪的卖姜人
文/不坏的土叔我叫张陵，是天一观的道长。经常有香客问我，道长，这世上最难降的妖魔是什么？我笑而不...
开封第一讲书人阅读 52,238评论 1赞 263
港岛之恋（遗憾婚礼）
正文为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
茶点故事阅读 61,112评论 4赞 356
恶毒庶女顶嫁案：这布局不是一般人想出来的
文/花漫我一把揭开白布。她就那样静静地躺着，像睡着了一般。火红的嫁衣衬着肌肤如雪。梳的纹丝不乱的头发上，一...
开封第一讲书人阅读 46,138评论 1赞 272
城市分裂传说
那天，我揣着相机与录音，去河边找鬼。笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。我是一名探鬼主播，决...
沈念sama阅读 36,545评论 3赞 381
双鸳鸯连环套：你想象不到人心有多黑
文/苍兰香墨我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
开封第一讲书人阅读 35,232评论 0赞 253
万荣杀人案实录
序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
沈念sama阅读 39,496评论 1赞 290
护林员之死
正文独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋以下内容为张勋视角年9月15日...
茶点故事阅读 34,596评论 2赞 310
白月光启示录
正文我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
茶点故事阅读 36,369评论 1赞 326
活死人
序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
沈念sama阅读 32,226评论 3赞 313
日本核电站爆炸内幕
正文年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
茶点故事阅读 37,600评论 3赞 299
男人毒药：我在死后第九天来索命
文/蒙蒙一、第九天我趴在偏房一处隐蔽的房顶上张望。院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
开封第一讲书人阅读 28,906评论 0赞 17
一桩弑父案，背后竟有这般阴谋
文/苍兰香墨我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。一阵脚步声响...
开封第一讲书人阅读 30,185评论 1赞 250
情欲美人皮
我被黑心中介骗来泰国打工，没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。一个月前我还...
沈念sama阅读 41,516评论 2赞 341
代替公主和亲
正文我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
茶点故事阅读 40,721评论 2赞 335

mysql 常用函数

字符串函数

CONCAT(S1,S2,...Sn) 函数：把传入的参数连接成一个字符串

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

LOWER(str)和UPPER(str) 函数：将字符串转换为小写或大写

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

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

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

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

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

STRCMP(s1,s2)函数：比较字符串s1和s2的ASCII码值大小

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

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

数值函数

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位小数的结果

日期函数

CURDATE()，CURTIME()，NOW()

SYSDATE 与 NOW 比较

UNIX_TIMESTAMP(date)，FROM_UNIXTIME

WEEK(date) ，MONTHNAME(date),YEAR(date)

DATE_FORMAT(date,fmt)

DATE_ADD(date,INTERVAL expr type)

DATEDIFF(expr,expr2)

流程函数

IF(value,t,f)

IFNULL(value1,value2)

case when函数

其他常用函数

推荐阅读更多精彩内容

ROUND(x,y)：返回参数x的四舍五入的有y位小数的值

TRUNCATE(x,y)：返回数字x截断y位小数的结果