Mysql 常用函数汇总


☆日期函数

日期符号对应关系

Y m d H i s

获取当前日期 now()

> select now();
+---------------------+
| now()               |
+---------------------+
| 2020-06-02 22:21:20 |
+---------------------+
1 row in set (0.03 sec)

昨天

DATE_FORMAT(CURDATE()-1,'%Y-%m-%d')

获取当前时间戳 current_timestamp

> select current_timestamp, current_timestamp();
+---------------------+---------------------+
| current_timestamp   | current_timestamp() |
+---------------------+---------------------+
| 2020-06-02 22:23:27 | 2020-06-02 22:23:27 |
+---------------------+---------------------+

格式转换: 时间->字符串 date_format

> select date_format('2020-10-18 22:23:01', '%Y-%m-%d %H%i%s');
+-------------------------------------------------------+
| date_format('2020-10-18 22:23:01', '%Y-%m-%d %H%i%s') |
+-------------------------------------------------------+
| 2020-10-18 222301                                     |
+-------------------------------------------------------+

格式转换: 字符串->时间 str_to_date

> select str_to_date('08/09/2008', '%m/%d/%Y');
+---------------------------------------+
| str_to_date('08/09/2008', '%m/%d/%Y') |
+---------------------------------------+
| 2008-08-09                            |
+---------------------------------------+
1 row in set (0.03 sec)

星期几DAYOFWEEK 

周日1,周一2...周六7

> select now(), dayofweek(now());
+---------------------+------------------+
| now()               | dayofweek(now()) |
+---------------------+------------------+
| 2020-06-03 21:48:03 |                4 |

获取年月日时分秒周

> select now(), dayofweek(now()), year(now()), month(now()), day(now()), hour(now()), minute(now()), second(now()),week(now());
+---------------------+------------------+-------------+--------------+------------+-------------+---------------+---------------+-------------+
| now()               | dayofweek(now()) | year(now()) | month(now()) | day(now()) | hour(now()) | minute(now()) | second(now()) | week(now()) |
+---------------------+------------------+-------------+--------------+------------+-------------+---------------+---------------+-------------+
| 2020-06-03 21:50:13 |                4 |        2020 |            6 |          3 |          21 |            50 |            13 |          22 |

☆字符串处理

字符串拼接 concat

将多个字符串连接成一个字符串

> select concat('2020', '/', '12', '/', '20');
+--------------------------------------+
| concat('2020', '/', '12', '/', '20') |
+--------------------------------------+
| 2020/12/20                           |
+--------------------------------------+

以第一个字符为拼接符:concat_ws

> select concat_ws('/', '2020', '12', '20');
+------------------------------------+
| concat_ws('/', '2020', '12', '20') |
+------------------------------------+
| 2020/12/20                         |
+------------------------------------+

字符串截取:left, right, substring

> select left('123456', 3), right('123456', 3), left('123', 100);
+-------------------+--------------------+------------------+
| left('123456', 3) | right('123456', 3) | left('123', 100) |
+-------------------+--------------------+------------------+
| 123               | 456                | 123              |
+-------------------+--------------------+------------------+

substring

用法:substring(字符串, 位置, 长度)

> select substring('123456', 3);   # 从第3个开始截取
+------------------------+
| substring('123456', 3) |
+------------------------+
| 3456                   |
+------------------------+
1 row in set (0.03 sec)

> select substring('123456', 3, 2);   #从第三个开始,只截2个
+---------------------------+
| substring('123456', 3, 2) |
+---------------------------+
| 34                        |
+---------------------------+
1 row in set (0.03 sec)

> select substring('123456', -3);   #从倒数第3个开始
+-------------------------+
| substring('123456', -3) |
+-------------------------+
| 456                     |
+-------------------------+
1 row in set (0.03 sec)

> select substring('123456', -3, 2);  # 从倒数第3个开始,只截2个
+----------------------------+
| substring('123456', -3, 2) |
+----------------------------+
| 45                         |
+----------------------------+
1 row in set (0.03 sec)

> select substring('123456', -3, -2);  # 第三个参数是长度,为正有意义
+-----------------------------+
| substring('123456', -3, -2) |
+-----------------------------+
|                             |
+-----------------------------+

按关键字截取 substring_index

用法substring_index(str, delim, count),即:substring_index(被截取字符串,关键字,关键字出现的次数)

> select substring_index('123aaa3bb3dd', '3', 1);
+-----------------------------------------+
| substring_index('123aaa3bb3dd', '3', 1) |
+-----------------------------------------+
| 12                                      |
+-----------------------------------------+

> select substring_index('123aaa3bb3dd', '3', 2);
+-----------------------------------------+
| substring_index('123aaa3bb3dd', '3', 2) |
+-----------------------------------------+
| 123aaa                                  |
+-----------------------------------------+

最左边字符的ascii码

>select ASCII(2), ASCII('2'), ASCII('22');
+----------+------------+-------------+
| ASCII(2) | ASCII('2') | ASCII('22') |
+----------+------------+-------------+
|       50 |         50 |          50 |

字符串长度 LENGHT

>select length('abc'), length(333333);
+---------------+----------------+
| length('abc') | length(333333) |
+---------------+----------------+
|             3 |              6 |

一字符串在另一个字符串中的位置 locate(substr, str), instr(str, substr) 

返回substr在str出现的第一个位置,如果在则>0;否则为0

> select locate('abc', 'mmabcmm'), locate('abc', 'aaaaaa');
+--------------------------+-------------------------+
| locate('abc', 'mmabcmm') | locate('abc', 'aaaaaa') |
+--------------------------+-------------------------+
|                        3 |                       0 |
+--------------------------+-------------------------+


> select instr('abc', 'a'), locate('abc', 'aa');
+-------------------+---------------------+
| instr('abc', 'a') | locate('abc', 'aa') |
+-------------------+---------------------+
|                 1 |                   0 |
+-------------------+---------------------+

替换REPLACE

> select replace('www.baidu.com.ww', 'ww', '&&');
+-----------------------------------------+
| replace('www.baidu.com.ww', 'ww', '&&') |
+-----------------------------------------+
| &&w.baidu.com.&&                        |

指定位置字符替换

insert(con, pos, len, anotherstr), 把字符con, 从第pos(从1开始计数)的len个字符由anotherstr替换

> select insert('123456', 3, 3, 'aaaaa');
+---------------------------------+
| insert('123456', 3, 3, 'aaaaa') |
+---------------------------------+
| 12aaaaa6                        |

重复REPEAT

> select repeat('abc', 3);
+------------------+
| repeat('abc', 3) |
+------------------+
| abcabcabc        |

翻转REVERSE

> select reverse('abc');
+----------------+
| reverse('abc') |
+----------------+
| cba            |

☆数学函数

绝对值ABS, 取余MOD

> select abs(-3.2), mod(3, 4);
+-----------+-----------+
| abs(-3.2) | mod(3, 4) |
+-----------+-----------+
|       3.2 |         3 |
+-----------+-----------+

四舍五入 ROUND

select round(1.49), round(1.50);
+-------------+-------------+
| round(1.49) | round(1.50) |
+-------------+-------------+
|           1 |           2 |
+-------------+-------------+

不大于x的最大整数FLOOR,不小于x的最小整数CEILING

> select FLOOR(-1.23), FLOOR(1.23), CEILING(-1.23), CEILING(1.23);
+--------------+-------------+----------------+---------------+
| FLOOR(-1.23) | FLOOR(1.23) | CEILING(-1.23) | CEILING(1.23) |
+--------------+-------------+----------------+---------------+
|           -2 |           1 |             -1 |             2 |

☆控制流程函数 

条件控制 case when then 

> select case when 1 < 0 then 'a' when 2 > 1 then 'b' else 'c' end;
+-----------------------------------------------------------+
| case when 1 < 0 then 'a' when 2 > 1 then 'b' else 'c' end |
+-----------------------------------------------------------+
| b                                                         |

☆加密函数 

password 一般对用户密码加密

> select password('a');
+-------------------------------------------+
| password('a')                             |
+-------------------------------------------+
| *667F407DE7C6AD07358FA38DAED7828A72014B4E |

md5一般对普通数据加密

> select md5('abc');
+----------------------------------+
| md5('abc')                       |
+----------------------------------+
| 900150983cd24fb0d6963f7d28e17f72 |

加密ENCODE, 解密DECODE

encode(str, pwd_str), decode(str, pwd_str), 使用pwd_str对str进行加密、解密

select encode('abc', '123'), decode(encode('abc', '123'), '123');
+----------------------+-------------------------------------+
| encode('abc', '123') | decode(encode('abc', '123'), '123') |
+----------------------+-------------------------------------+
| ???                  | abc                                 |