mysql函数汇总_Mysql 常用函数汇总

☆日期函数

日期符号对应关系

年

月

日

时

分

秒

Y

m

d

H

i

s

获取当前日期 now()

>select now();+---------------------+

| now() |

+---------------------+

| 2020-06-02 22:21:20 |

+---------------------+

1 row in set (0.03 sec)

获取当前时间戳 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.03sec)> select substring('123456', 3, 2); #从第三个开始，只截2个+---------------------------+

| substring('123456', 3, 2) |

+---------------------------+

| 34 |

+---------------------------+

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

| substring('123456', -3) |

+-------------------------+

| 456 |

+-------------------------+

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

| substring('123456', -3, 2) |

+----------------------------+

| 45 |

+----------------------------+

1 row in set (0.03sec)> 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 |