MYSQL 常用函数

海鲨数据库架构师 2021-04-02

388

字符串函数

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

合并字符串

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)

数值函数

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

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)

日期和时间函数

函数	功能
CURDATE()	获取当前日期
CURTIME()	获取当前时间
NOW()	获取当前的日期和时间
WEEK(date)	返回日期date为一年中的第几周
YEAR(date)	返回日期date的年份
HOUR(time)	返回时间time的小时值
MINUTE(time)	返回时间time的分钟值
MONTHNAME(time)	返回时间time的月份值

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)

mysql

文章转载自海鲨数据库架构师，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

MYSQL 常用函数

评论