MySQL常用函数-字符串函数、数值函数、日期函数

90 阅读 0 评论 60 点赞

我是靠谱客的博主外向小鸽子，这篇文章主要介绍MySQL常用函数-字符串函数、数值函数、日期函数，现在分享给大家，希望可以做个参考。

一、字符串函数

（1）CONCAT(S1,S2,…Sn)：把传入的参数连接成一个字符串。
例如，将‘aaa’、‘bbb’、‘ccc‘ 三个字符连接一起，把‘aaa’ 与 null连接一起：

复制代码

1
2
3
4
5
6
7
8
9
10
mysql> select concat('aaa','bbb','ccc'),concat('aaa',null);
+---------------------------+--------------------+
| concat('aaa','bbb','ccc') | concat('aaa',null) |
+---------------------------+--------------------+
| aaabbbccc
| NULL
|
+---------------------------+--------------------+
1 row in set (0.00 sec)

注意：任何字符串与null进行连接的结果都是null。

（2）INSERT(str,x,y,instr)：将字符串str从第x个位置开始，y个字符长的子字符串替换成字符串instr：

复制代码

1
2
3
4
5
6
7
8
9
10
mysql> select insert('beijing2008you',12,3,'me');
+------------------------------------+
| insert('beijing2008you',12,3,'me') |
+------------------------------------+
| beijing2008me
|
+------------------------------------+
1 row in set (0.03 sec)
mysql>

（3）LOWER(str)和UPPER(str)：把字符串全部转换成小写或大写。

复制代码

1
2
3
4
5
6
7
8
9
10
11
mysql> select lower('BeiJing'),upper('beijing');
+------------------+------------------+
| lower('BeiJing') | upper('beijing') |
+------------------+------------------+
| beijing
| BEIJING
|
+------------------+------------------+
1 row in set (0.03 sec)
mysql>

（4）LEFT(str,x)和RIGHT(str,x)：分别返回字符串最左边x个字符和最右边的x个字符。（如果x为null，则没有结果）

复制代码

1
2
3
4
5
6
7
8
9
10
11
mysql> select left('beijing',3),right('beijing',4);
+-------------------+--------------------+
| left('beijing',3) | right('beijing',4) |
+-------------------+--------------------+
| bei
| jing
|
+-------------------+--------------------+
1 row in set (0.02 sec)
mysql>

（5）LPAD(str,n,pad)和RPAD(str,n,pad)：用字符串pad对字符串str最左边和最右边进行扩充，知道整个str长度为n：

复制代码

1
2
3
4
5
6
7
8
9
10
11
mysql> select lpad('aaa',6,'x'),rpad('aaa',6,'y');
+-------------------+-------------------+
| lpad('aaa',6,'x') | rpad('aaa',6,'y') |
+-------------------+-------------------+
| xxxaaa
| aaayyy
|
+-------------------+-------------------+
1 row in set (0.00 sec)
mysql>

（6）LTRIM(str)和RTRIM(str)：去掉字符串str左侧和右侧的空格：

复制代码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
mysql> select ltrim('
|aaa'),rtrim('bbb|
');
+-----------------+------------------+
| ltrim('
|aaa') | rtrim('bbb|
') |
+-----------------+------------------+
| |aaa
| bbb|
|
+-----------------+------------------+
1 row in set (0.00 sec)
mysql>

（7）TRIM(str)：去掉字符串两端的空格：

复制代码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
mysql> select trim('
aaa
'),trim('aaa
');
+-------------------+----------------+
| trim('
aaa
') | trim('aaa
') |
+-------------------+----------------+
| aaa
| aaa
|
+-------------------+----------------+
1 row in set (0.00 sec)
mysql>

（8）REPEAT(str,n)：将字符串str重复n次返回：

复制代码

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

（9）REPLACE(str,a,b)：将字符串str中出现的所有a都替换成b（常用于替换字符串中的空格或者特殊字符）：

复制代码

1
2
3
4
5
6
7
8
9
10
11
mysql> select replace('abccdefc','c','2'),replace('ab de f',' ','');
+-----------------------------+---------------------------+
| replace('abccdefc','c','2') | replace('ab de f',' ','') |
+-----------------------------+---------------------------+
| ab22def2
| abdef
|
+-----------------------------+---------------------------+
1 row in set (0.00 sec)
mysql>

注意：replace是替换所有符合条件的a，使用是要注意实际需求。

（10）STRCMP(s1,s2)：比较字符串s1与s2的ASCII码值的大小（s1比s2小返回-1；s1比s2大返回1；相等返回0）：

复制代码

1
2
3
4
5
6
7
8
9
10
11
12
mysql> select strcmp('a','b'),strcmp('a','a'),strcmp('c','b');
+-----------------+-----------------+-----------------+
| strcmp('a','b') | strcmp('a','a') | strcmp('c','b') |
+-----------------+-----------------+-----------------+
|
-1 |
0 |
1 |
+-----------------+-----------------+-----------------+
1 row in set (0.00 sec)
mysql>

（11）SUBSTR(str,x,y)：返回字符串str中第x位置起y个字符长度的字符串：

复制代码

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
mysql> select substr('beijing',2,3);
+-----------------------+
| substr('beijing',2,3) |
+-----------------------+
| eij
|
+-----------------------+
1 row in set (0.00 sec)
mysql> select substr('beijing',0,3);
+-----------------------+
| substr('beijing',0,3) |
+-----------------------+
|
|
+-----------------------+
1 row in set (0.00 sec)
mysql> select substr('beijing',1,3);
+-----------------------+
| substr('beijing',1,3) |
+-----------------------+
| bei
|
+-----------------------+
1 row in set (0.00 sec)
mysql>

MySQL中的用法与oracle不同，oracle可以从0开始，表示也是1开始。而MySQL则会不会返回结果。

二、数值函数

（2）ABS(x) ：返回x的绝对值。

复制代码

1
2
3
4
5
6
7
8
9
10
mysql> select abs(-2);
+---------+
| abs(-2) |
+---------+
|
2 |
+---------+
1 row in set (0.00 sec)
mysql>

（2）CEIL(x) ：返回大于x的最小整数值。

复制代码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
mysql> select ceil(2.4);
+-----------+
| ceil(2.4) |
+-----------+
|
3 |
+-----------+
1 row in set (0.00 sec)
mysql> select ceil(2.1);
+-----------+
| ceil(2.1) |
+-----------+
|
3 |
+-----------+
1 row in set (0.00 sec)
mysql>

（3）FLOOR(x)：返回小于x的最大整数值。

复制代码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
mysql> select floor(2.4);
+------------+
| floor(2.4) |
+------------+
|
2 |
+------------+
1 row in set (0.00 sec)
mysql> select floor(2.5);
+------------+
| floor(2.5) |
+------------+
|
2 |
+------------+
1 row in set (0.00 sec)
mysql>

（4）MOD(x,y) ：返回x/y的模。

复制代码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
mysql> select mod(4,3);
+----------+
| mod(4,3) |
+----------+
|
1 |
+----------+
1 row in set (0.00 sec)
mysql> select mod(3,3);
+----------+
| mod(3,3) |
+----------+
|
0 |
+----------+
1 row in set (0.00 sec)
mysql>

（5）RAND()：返回 0 ~ 1 内的随机值。

复制代码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
mysql> select rand();
+--------------------+
| rand()
|
+--------------------+
| 0.6023772308625068 |
+--------------------+
1 row in set (0.02 sec)
mysql> select rand();
+---------------------+
| rand()
|
+---------------------+
| 0.15371906492274168 |
+---------------------+
1 row in set (0.00 sec)
mysql>

如果想要保留 0 ~ 100内的随机值：

复制代码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
mysql> select ceil(100*rand());
+------------------+
| ceil(100*rand()) |
+------------------+
|
97 |
+------------------+
1 row in set (0.00 sec)
mysql> select ceil(100*rand());
+------------------+
| ceil(100*rand()) |
+------------------+
|
35 |
+------------------+
1 row in set (0.00 sec)
mysql>

（6）ROUND(x,y)：返回将x四舍五入保留y位小数的值。

复制代码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
mysql> select round(5.456,2);
+----------------+
| round(5.456,2) |
+----------------+
|
5.46 |
+----------------+
1 row in set (0.02 sec)
mysql> select round(5.453,2);
+----------------+
| round(5.453,2) |
+----------------+
|
5.45 |
+----------------+
1 row in set (0.00 sec)
mysql>

（7）TRUNCATE(x,y)：返回数字x截断保留y为小数的结果。不会四舍五入。

复制代码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
mysql> select truncate(5.456,2);
+-------------------+
| truncate(5.456,2) |
+-------------------+
|
5.45 |
+-------------------+
1 row in set (0.00 sec)
mysql> select truncate(5.456,1);
+-------------------+
| truncate(5.456,1) |
+-------------------+
|
5.4 |
+-------------------+
1 row in set (0.00 sec)
mysql>

三、日期和时间函数

（1）CURDATE()：返回当前时间，只有年月日。

复制代码

1
2
3
4
5
6
7
8
9
10
mysql> select curdate();
+------------+
| curdate()
|
+------------+
| 2021-03-22 |
+------------+
1 row in set (0.00 sec)
mysql>

（2）CURTIME()：返回当前时间，只包含时分秒。

复制代码

1
2
3
4
5
6
7
8
9
10
mysql> select curtime();
+-----------+
| curtime() |
+-----------+
| 18:31:27
|
+-----------+
1 row in set (0.00 sec)
mysql>

（3）NOW()：返回当前时间，包含年月日时分秒。

复制代码

1
2
3
4
5
6
7
8
9
10
mysql> select now();
+---------------------+
| now()
|
+---------------------+
| 2021-03-22 18:32:10 |
+---------------------+
1 row in set (0.00 sec)
mysql>

（4）UNIX_TIMESTAMCP(date)：返回日期date的UNIX时间戳。

复制代码

1
2
3
4
5
6
7
8
9
10
mysql> select unix_timestamp(now());
+-----------------------+
| unix_timestamp(now()) |
+-----------------------+
|
1616409203 |
+-----------------------+
1 row in set (0.00 sec)
mysql>

（5）FROM_UNIXTIME(unixtime)：返回unixtime时间戳的日期值。与UNIX_TIMESTAMP(date)互为逆向操作。

复制代码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
mysql> select unix_timestamp(now());
+-----------------------+
| unix_timestamp(now()) |
+-----------------------+
|
1616409203 |
+-----------------------+
1 row in set (0.00 sec)
mysql> select from_unixtime(1616409203);
+---------------------------+
| from_unixtime(1616409203) |
+---------------------------+
| 2021-03-22 18:33:23
|
+---------------------------+
1 row in set (0.02 sec)
mysql>

（6）WEEK(date)和YEAR(date)：前者返回日期date是一年中第几周，后者返回日期date是哪一年。

复制代码

1
2
3
4
5
6
7
8
9
10
11
mysql> select week(now()),year(now());
+-------------+-------------+
| week(now()) | year(now()) |
+-------------+-------------+
|
12 |
2021 |
+-------------+-------------+
1 row in set (0.28 sec)
mysql>

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

复制代码

1
2
3
4
5
6
7
8
9
10
11
mysql> select hour(curtime()),minute(curtime());
+-----------------+-------------------+
| hour(curtime()) | minute(curtime()) |
+-----------------+-------------------+
|
18 |
39 |
+-----------------+-------------------+
1 row in set (0.00 sec)
mysql>

（8）MONTHNAME(data)：返回date的英文月份名称：

复制代码

1
2
3
4
5
6
7
8
9
10
mysql> select monthname(now());
+------------------+
| monthname(now()) |
+------------------+
| March
|
+------------------+
1 row in set (0.03 sec)
mysql>

（9）DATE_FORMAT(date,fmt)：按照字符串fmt格式化日期date。

复制代码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
mysql> select date_format(now(),'%Y-%m-%d %H:%i:%s');
+----------------------------------------+
| date_format(now(),'%Y-%m-%d %H:%i:%s') |
+----------------------------------------+
| 2021-03-22 18:42:36
|
+----------------------------------------+
1 row in set (0.00 sec)
mysql> select date_format(now(),'%Y%m%d%H%i%s');
+-----------------------------------+
| date_format(now(),'%Y%m%d%H%i%s') |
+-----------------------------------+
| 20210322184246
|
+-----------------------------------+
1 row in set (0.00 sec)
mysql>

（10）DATE_ADD(date,INTERVAL expr type)：返回与所给日期date相差INTERVAL时间段的日期。其中INTERVAL是间隔类型的关键字，expr是一个表达式，表达式对应后面的类型，type是间隔类型。主要间隔类型如下：

表达式类型	描述	格式
HOUR	小时	hh
MINUTE	分	mm
SECOND	秒	ss
YEAR	年	YY
MONTH	月	MM
DAY	日	DD
YEAR_MONTH	年和月	YY-MM
DAY_HOUR	日和小时	DD hh
DAY_MONTH	日和分钟	DD hh:mm
DAY_SECOND	日和秒	DD hh:mm:ss
HOUR_MINUTE	小时和分钟	hh:mm
HOUR_SECOND	小时和秒	hh:ss
MINUTE_SECOND	分钟和秒	mm:ss

例如：

当前时间；举例当前日期31天后的日期；举例当前日期一年2个月后的日期。

复制代码

1
2
3
4
5
6
7
8
9
10
11
mysql> select now(),date_add(now(),INTERVAL 31 day),date_add(now(),INTERVAL '1_2' year_month);
+---------------------+---------------------------------+-------------------------------------------+
| now()
| date_add(now(),INTERVAL 31 day) | date_add(now(),INTERVAL '1_2' year_month) |
+---------------------+---------------------------------+-------------------------------------------+
| 2021-03-23 09:35:51 | 2021-04-23 09:35:51
| 2022-05-23 09:35:51
|
+---------------------+---------------------------------+-------------------------------------------+
1 row in set (0.01 sec)

也可以通过负数求当前日期之前的日期，例如，当前时间；当前日期前31天的日期；当前日期之前一年两个月的日期。

复制代码

1
2
3
4
5
6
7
8
9
10
11
12
mysql> select now(),date_add(now(),INTERVAL -31 day),date_add(now(),INTERVAL '-1_-2' year_month);
+---------------------+----------------------------------+---------------------------------------------+
| now()
| date_add(now(),INTERVAL -31 day) | date_add(now(),INTERVAL '-1_-2' year_month) |
+---------------------+----------------------------------+---------------------------------------------+
| 2021-03-23 09:39:12 | 2021-02-20 09:39:12
| 2020-01-23 09:39:12
|
+---------------------+----------------------------------+---------------------------------------------+
1 row in set (0.00 sec)
mysql>

（11）DATEDIFF(date1,date2)：求两个日期之间相差的天数。

复制代码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
mysql> select datediff('2008-08-08',now());
+------------------------------+
| datediff('2008-08-08',now()) |
+------------------------------+
|
-4610 |
+------------------------------+
1 row in set (0.04 sec)
mysql> select datediff('2021-01-01',now());
+------------------------------+
| datediff('2021-01-01',now()) |
+------------------------------+
|
-81 |
+------------------------------+
1 row in set (0.00 sec)
mysql>