MYSQL 常用函数
祖仙教小凡仙 海鲨数据库架构师
字符串函数
合并字符串
### v 方式1:CONCATE()函数
§ CONCAT(s1, s2, …, sn)
§ 将字符串合并起来,如果其中一个为NULL, 返回值为NULL
§ 可以接受非字符串参数
§ 例子:SELECT CONCAT(‘Hello’,‘ World’, 1234);
§ 例子:SELECT CONCAT(‘Hello’,’ World’, NULL);
mysql> select concat('hello','wold',1234);
+-----------------------------+
| concat('hello','wold',1234) |
+-----------------------------+
| hellowold1234 |
+-----------------------------+
1 row in set (0.00 sec)
mysql> select concat('hello','wold',NULL);
+-----------------------------+
| concat('hello','wold',NULL) |
+-----------------------------+
| NULL |
+-----------------------------+
1 row in set (0.00 sec)
v 方式2:CONCATE_WS()函数
§ CONCAT_WS(sep, s1, s2, …, sn)
§ 将字符串合并起来,并用sep作为分隔符
§ 例子:SELECT CONCAT_WS(‘%’,‘Hi’,‘World’);
mysql> select concat_ws('%','Hi','World');
+-----------------------------+
| concat_ws('%','Hi','World') |
+-----------------------------+
| Hi%World |
+-----------------------------+
1 row in set (0.00 sec)
比较字符串
v STRCMP(str1, str2) 函数
§ 如果str1大于str2,返回1
§ 如果str1等于str2,返回0
§ 如果str1小于str2,返回-1
例子:SELECT STRCMP(‘abc’,’abd’),
STRCMP(‘abc’,’abc’),
STRCMP(‘abc’,’abb’);
mysql> select strcmp('abc','abd'),strcmp('abc','abc'),strcmp('abc','abb');
+---------------------+---------------------+---------------------+
| strcmp('abc','abd') | strcmp('abc','abc') | strcmp('abc','abb') |
+---------------------+---------------------+---------------------+
| -1 | 0 | 1 |
+---------------------+---------------------+---------------------+
1 row in set (0.00 sec)
### 长度函数
v 获取字符串长度函数 LENGTH()
§ LENGTH(str) : 字节长度
§ 例子:SELECT LENGTH(‘MySQL’) ,
LENGTH(‘中国’) ;
mysql> select LENGTH('MySql'),LENGTH('中国');\G
+-----------------+------------------+
| LENGTH('MySql') | LENGTH('中国') |
+-----------------+------------------+
| 5 | 6 |
+-----------------+------------------+
1 row in set (0.01 sec)
v 获取字符函数 CHAR_LENGTH()
§ CHAR_LENGTH(str) : 字符长度
§ 例子:SELECT CHAR_LENGTH(‘MySQL’) ,
CHAR_LENGTH(‘中国’) ;
mysql> select char_length('MySql'),char_length('中国');
+----------------------+-----------------------+
| char_length('MySql') | char_length('中国') |
+----------------------+-----------------------+
| 5 | 2 |
+----------------------+-----------------------+
1 row in set (0.00 sec)
### 替换字符串
v INSERT(str, pos, len, newstr):
§ 将字符串str中从pos位置开始长度为len的字符串用newstr替换
§ 例子:SELECT ‘这是MySQL数据库管理系统’ 原字符串,
INSERT(‘这是MySQL数据库管理系统’ , 3, 5,‘ORACLE’, 2) 替换后;
mysql> SELECT '这是MySQL数据库管理系统' as 原字符串,
INSERT('这是MySQL数据库管理系统',3,5,'ORACLE') as 替换后;
+----------------------------------+-----------------------------------+
| 原字符串 | 替换后 |
+----------------------------------+-----------------------------------+
| 这是MySQL数据库管理系统 | 这是ORACLE数据库管理系统 |
+----------------------------------+-----------------------------------+
1 row in set (0.00 sec)
v REPLACE(str, substr, newstr):
§ 将str中substr替换为newstr
§ 例子:SELECT ‘这是SQL数据库管理系统MySQL’ 原字符串,
REPLACE(‘这是SQL数据库管理系统MySQL’ , ’SQL’, ‘ORACLE’) 替换后;
mysql> SELECT '这是MySQL数据库管理系统' as 原字符串,
-> REPLACE('这是MySQL数据库管理系统','SQL','ORACLE') as 替换后;
+----------------------------------+-------------------------------------+
| 原字符串 | 替换后 |
+----------------------------------+-------------------------------------+
| 这是MySQL数据库管理系统 | 这是MyORACLE数据库管理系统 |
+----------------------------------+-------------------------------------+
1 row in set (0.00 sec)
字母大小写转换
v 字母转大写:UPPER(s), UCASE(s)
§ 例子:SELECT UPPER(‘mysql’) ,
UCASE(‘mysql’) ;
mysql> SELECT UPPER('mysql'),ucase('mysql_small');
+----------------+----------------------+
| UPPER('mysql') | ucase('mysql_small') |
+----------------+----------------------+
| MYSQL | MYSQL_SMALL |
+----------------+----------------------+
1 row in set (0.00 sec)
v 字母转大写:LOWER(s), LCASE(s)
§ 例子:SELECT LOWER(‘MySQL’) ,
LCASE(‘MySQL’) ;
mysql> SELECT LOWER('MySQL'), LCASE('MySQL');
+----------------+----------------+
| LOWER('MySQL') | LCASE('MySQL') |
+----------------+----------------+
| mysql | mysql |
+----------------+----------------+
1 row in set (0.00 sec)
### 查找字符串
v 返回字符串位置:FIND_IN_SET(str1, str2)
§ 返回在str2中与str1相匹配的字符串的位置,str2包含若干个用逗号隔开的字符串
§ 例子:SELECT FIND_IN_SET(‘MySQL’,’ab,test,MySQL’);
mysql> SELECT FIND_IN_SET('MySQL','ab,test,MySQL');
+--------------------------------------+
| FIND_IN_SET('MySQL','ab,test,MySQL') |
+--------------------------------------+
| 3 |
+--------------------------------------+
1 row in set (0.00 sec)
v 返回指定字符串位置:FIELD(str, str1, str2)
§ 返回第一个与字符串str匹配的字符串的位置
§ 例子:SELECT FIELD(‘ab’,’a’,’ab’,’ac’,’ab’);
v 返回字符串相匹配的开始位置:
§ LOCATE(str1, str): 返回str中str1的开始位置
§ POSITION(str1 IN str)
§ INSTR(str, str1)
§ 例子:SELECT LOCATE(‘SQL’,’MySQL’),
POSITION(‘SQL’ IN ’MySQL’) ,
INSTR(‘MySQL’,’SQL’) ;
v 截取字符串:
§ LEFT(str, num) : 返回字符串str中左边num个字母的字符串
§ RIGHT(str, num):返回字符串str中右边num个字母的字符串
§ 例子:SELECT LEFT(‘MySQL’, 2) 前两个字符串,
RIGHT(‘MySQL’, 2) 后两个字符串;
v 截取指定位置和长度字符串:
§ SUBSTRING(str, num, len) :
§ MID(str, num, len)
§ 返回字符串str中第num个位置开始长度为len的字符串
§ 例子:SELECT SUBSTRING(‘MySQL’, 3,3) 截取的字符串,
MID(‘MySQL’, 3,3) 截取的字符串;
mysql> select field('ab','a','ab','ac','ab');
+--------------------------------+
| field('ab','a','ab','ac','ab') |
+--------------------------------+
| 2 |
+--------------------------------+
1 row in set (0.00 sec)
mysql> select locate('SQL','MySQL'), position('SQL' in 'MySQL'), instr('MySQL','SQL');
+-----------------------+----------------------------+----------------------+
| locate('SQL','MySQL') | position('SQL' in 'MySQL') | instr('MySQL','SQL') |
+-----------------------+----------------------------+----------------------+
| 3 | 3 | 3 |
+-----------------------+----------------------------+----------------------+
1 row in set (0.00 sec)
mysql> select left('MYSQL',2),RIGHT('MYSQL',2);
+-----------------+------------------+
| left('MYSQL',2) | RIGHT('MYSQL',2) |
+-----------------+------------------+
| MY | QL |
+-----------------+------------------+
1 row in set (0.00 sec)
mysql> select substring('MYSQL',3,3),mid('MYSQL',3,3);
+------------------------+------------------+
| substring('MYSQL',3,3) | mid('MYSQL',3,3) |
+------------------------+------------------+
| SQL | SQL |
+------------------------+------------------+
1 row in set (0.00 sec)
### 数值函数
![](https://s4.51cto.com/images/blog/202103/06/9ebc7cc423d494e3f61a6ee622dbffcf.png?x-oss-process=image/watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=)
mysql> select rand(),rand(1),rand(2),rand(3);
+--------------------+---------------------+--------------------+--------------------+
| rand() | rand(1) | rand(2) | rand(3) |
+--------------------+---------------------+--------------------+--------------------+
| 0.1878656774646283 | 0.40540353712197724 | 0.6555866465490187 | 0.9057697559760601 |
+--------------------+---------------------+--------------------+--------------------+
1 row in set (0.00 sec)
mysql> select ceil(2.1),ceil(-2.1),floor(2.1),floor(-2.1);
+-----------+------------+------------+-------------+
| ceil(2.1) | ceil(-2.1) | floor(2.1) | floor(-2.1) |
+-----------+------------+------------+-------------+
| 3 | -2 | 2 | -3 |
+-----------+------------+------------+-------------+
1 row in set (0.00 sec)
mysql> select round(2.3456),round(2.34567,2);
+---------------+------------------+
| round(2.3456) | round(2.34567,2) |
+---------------+------------------+
| 2 | 2.35 |
+---------------+------------------+
1 row in set (0.00 sec)
mysql> select truncate(122.23456,2),truncate(1223.3456,-1);
+-----------------------+------------------------+
| truncate(122.23456,2) | truncate(1223.3456,-1) |
+-----------------------+------------------------+
| 122.23 | 1220 |
+-----------------------+------------------------+
1 row in set (0.00 sec)
mysql> select mod(10,3);
+-----------+
| mod(10,3) |
+-----------+
| 1 |
+-----------+
1 row in set (0.00 sec)
### 日期和时间函数
![](https://s4.51cto.com/images/blog/202103/06/cd601ac60fd633df890b834d0f6e1bd1.png?x-oss-process=image/watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=)
v 获取当前日期和时间
§ NOW(), CURRENT_TIMESTAMP(),
§ LOCALTIME(), SYSDATE()
v 获取当前日期
§ CURDATE(), CURRENT_DATE()
v 获取当前时间
§ CURTIME(), CURRENT_TIME()
v 获取当前时间的年月季度周天时分秒
§YEAR(),QUARTER(),MONTH() ,WEEK(),DAYOFMONTH(),HOUR(),MINUTE(),SECOND()
获取当前日期和时间
mysql> SELECT NOW(),CURRENT_TIMESTAMP(),LOCALTIME(),SYSDATE();
+---------------------+---------------------+---------------------+---------------------+
| NOW() | CURRENT_TIMESTAMP() | LOCALTIME() | SYSDATE() |
+---------------------+---------------------+---------------------+---------------------+
| 2021-02-27 12:03:10 | 2021-02-27 12:03:10 | 2021-02-27 12:03:10 | 2021-02-27 12:03:10 |
+---------------------+---------------------+---------------------+---------------------+
1 row in set (0.02 sec)
#获取当前日期
mysql> SELECT CURDATE(),CURRENT_DATE();
+------------+----------------+
| CURDATE() | CURRENT_DATE() |
+------------+----------------+
| 2021-02-27 | 2021-02-27 |
+------------+----------------+
1 row in set (0.00 sec)
#获取当前时间
mysql> SELECT CURTIME(),CURRENT_TIME();
+-----------+----------------+
| CURTIME() | CURRENT_TIME() |
+-----------+----------------+
| 12:03:52 | 12:03:52 |
+-----------+----------------+
1 row in set (0.00 sec)
#获取当前时间的年月季度周 WEEK 是一年中第几周
mysql> SELECT NOW(),YEAR(NOW()),QUARTER(NOW()),MONTH(NOW()),WEEK(NOW());
+---------------------+-------------+----------------+--------------+-------------+
| NOW() | YEAR(NOW()) | QUARTER(NOW()) | MONTH(NOW()) | WEEK(NOW()) |
+---------------------+-------------+----------------+--------------+-------------+
| 2021-02-27 12:05:13 | 2021 | 1 | 2 | 8 |
+---------------------+-------------+----------------+--------------+-------------+
1 row in set (0.00 sec)
#天时分秒
mysql> SELECT DAYOFMONTH(NOW()),HOUR(NOW()),MINUTE(NOW()),SECOND(NOW());
+-------------------+-------------+---------------+---------------+
| DAYOFMONTH(NOW()) | HOUR(NOW()) | MINUTE(NOW()) | SECOND(NOW()) |
+-------------------+-------------+---------------+---------------+
| 27 | 12 | 5 | 53 |
+-------------------+-------------+---------------+---------------+
1 row in set (0.00 sec)
xxxNAME 表示时间名称 英语或中文
mysql> SELECT NOW(),MONTHNAME(NOW()),WEEKOFYEAR(NOW()) AS '年中第几周', dayname(now()),dayofweek(now());
+---------------------+------------------+-----------------+----------------+------------------+
| NOW() | MONTHNAME(NOW()) | 年中第几周 | dayname(now()) | dayofweek(now()) |
+---------------------+------------------+-----------------+----------------+------------------+
| 2021-02-27 12:08:16 | February | 8 | Saturday | 7 |
+---------------------+------------------+-----------------+----------------+------------------+
1 row in set (0.00 sec)
mysql> select now(),dayofyear(now()) 年中第几天 ,dayofmonth(now()) 月中第几天;
+---------------------+-----------------+-----------------+
| now() | 年中第几天 | 月中第几天 |
+---------------------+-----------------+-----------------+
| 2021-02-27 12:11:18 | 58 | 27 |
+---------------------+-----------------+-----------------+
1 row in set (0.00 sec)
mysql> select now(),week(now()),weekofyear(now()),dayofweek(now()), dayofyear(now()),dayofmonth(now());
+---------------------+-------------+-------------------+------------------+------------------+-------------------+
| now() | week(now()) | weekofyear(now()) | dayofweek(now()) | dayofyear(now()) | dayofmonth(now()) |
+---------------------+-------------+-------------------+------------------+------------------+-------------------+
| 2021-02-27 12:17:28 | 8 | 8 | 7 | 58 | 27 |
+---------------------+-------------+-------------------+------------------+------------------+-------------------+
1 row in set (0.00 sec)
v 计算日期和时间的函数
§ TO_DAYS(date):计算date与默认日期(0000年1月1日)之间的天数
§ FROM_DAYS(number):计算从默认日期开始number天后的日期
§ DATE(date1, date2):date1 与 date2 之间的相隔天数
§ ADDDATE(date, n):日期date加上n天后的日期
§ SUBDATE(date, n):日期date减去n天后的日期
mysql> select now() 当前时间,to_days(now()) 相隔天数, from_days(to_days(now())) 现在日期;
+---------------------+--------------+--------------+
| 当前时间 | 相隔天数 | 现在日期 |
+---------------------+--------------+--------------+
| 2021-02-27 12:22:41 | 738213 | 2021-02-27 |
+---------------------+--------------+--------------+
1 row in set (0.00 sec)
mysql> select adddate(curdate(),5) , subdate(curdate(),5);
+----------------------+----------------------+
| adddate(curdate(),5) | subdate(curdate(),5) |
+----------------------+----------------------+
| 2021-03-04 | 2021-02-22 |
+----------------------+----------------------+
1 row in set (0.00 sec)