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

一、字符串函数

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

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：

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)：把字符串全部转换成小写或大写。

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，则没有结果）

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：

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左侧和右侧的空格：

mysql> select ltrim('  |aaa'),rtrim('bbb|   ');
+-----------------+------------------+
| ltrim('  |aaa') | rtrim('bbb|   ') |
+-----------------+------------------+
| |aaa            | bbb|             |
+-----------------+------------------+
1 row in set (0.00 sec)

mysql> 

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

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次返回：

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（常用于替换字符串中的空格或者特殊字符）：

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）：

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个字符长度的字符串：

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的绝对值。

mysql> select abs(-2);
+---------+
| abs(-2) |
+---------+
|       2 |
+---------+
1 row in set (0.00 sec)

mysql>

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

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的最大整数值。

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的模。

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 内的随机值。

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内的随机值：

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位小数的值。

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为小数的结果。不会四舍五入。

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()：返回当前时间，只有年月日。

mysql> select curdate();
+------------+
| curdate()  |
+------------+
| 2021-03-22 |
+------------+
1 row in set (0.00 sec)

mysql> 

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

mysql> select curtime();
+-----------+
| curtime() |
+-----------+
| 18:31:27  |
+-----------+
1 row in set (0.00 sec)

mysql> 

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

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2021-03-22 18:32:10 |
+---------------------+
1 row in set (0.00 sec)

mysql> 

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

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)互为逆向操作。

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是哪一年。

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)：前者返回所给时间的小时，后者返回所给时间的分钟。

mysql> select hour(curtime()),minute(curtime());
+-----------------+-------------------+
| hour(curtime()) | minute(curtime()) |
+-----------------+-------------------+
|              18 |                39 |
+-----------------+-------------------+
1 row in set (0.00 sec)

mysql> 

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

mysql> select monthname(now());
+------------------+
| monthname(now()) |
+------------------+
| March            |
+------------------+
1 row in set (0.03 sec)

mysql>

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

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个月后的日期。

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天的日期；当前日期之前一年两个月的日期。

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)：求两个日期之间相差的天数。

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> 

过去的日期和现在日期计算返回负天数，现在日期和将来日期计算返回正天数。