MySQL常用系统函数

一、数学函数

数学函数主要用于处理数字，包括整型、浮点数等。

函数	说明	示例
abs(n)	返回n的绝对值	select abs(-1.5); – 1.5
ceil(n)	向上取整	select ceil(1.5); – 2
floor(n)	向下取整	select floor(1.6); – 1
rand()	返回0~1之间的随机数	select rand(); – 0.6390859146035145
rand(n)	返回0~1之间的随机数，n相同随机数相同	select rand(6); – 0.6563190842571847
round(n)	四舍五入保留整数	select round(1.5); – 2
round(x,y)	四舍五入保留y位小数	select round(1.345,2); – 1.35
format(x,y)	格式化为数字并四舍五入保留y位小数	select format(‘3.1415926’, 3); – 3.142
truncate(x,y)	返回x的y位小数	select truncate(1.345,2); – 1.34
sign(n)	n是负数、0、正数分别返回-1、0和1	select sign(0); – 0
pi()	返回圆周率	select pi(); – 3.141593
sqrt(n)	返回n的平方根	select sqrt(4); – 2
pow(x,y)	返回x的y次方	select pow(2,3); – 8
mod(x,y)	返回x除以y以后的余数	select mod(7,4); – 3

二、字符串函数

函数	说明	示例
char_length()	不管汉字还是数字或者是字母都算是一个字符。	select char_length(‘你好123’); – 5
length()	一个汉字算三个字符，一个数字或字母算一个字符。	select length(‘你好123’); – 9

• concat()

  将多个字符串连接成一个字符串,可以有一个或多个参数.

  MariaDB [test]> select * from Info;
  +-----+----------+------+
  | uid | username | age  |
  +-----+----------+------+
  |   1 | tom      |   20 |
  |   2 | esthor   |   18 |
  |   3 | frunk    |   18 |
  +-----+----------+------+
  
  -- 将多个字符串连接成一个字符串
  MariaDB [test]> select concat(uid,' ' ,username) from Info;        
  +---------------------------+
  | concat(uid,' ' ,username) |
  +---------------------------+
  | 1 tom                     |
  | 2 esthor                  |
  | 3 frunk                   |
  +---------------------------+
  
  -- 如有任何一个参数为NULL ，则返回值为 NULL。
  MariaDB [test]> select concat(uid,null,username) from Info;
  +---------------------------+
  | concat(uid,null,username) |
  +---------------------------+
  | NULL                      |
  | NULL                      |
  | NULL                      |
  +---------------------------+
  

• concat_ws()
  指定参数之间的分隔符,将多个字符串连接成一个字符串。

    -- 1、语法：concat_ws(separator,str1,str2,…) 
        -- 第一位参数是分隔符，用于连接字符串；分隔符可以是一个字符串，也可以是其它参数。
    -- 2、如果分隔符为 NULL，则结果为 NULL。
    -- 3、函数会忽略任何分隔符参数后的 NULL 值。
    -- 4、concat_ws()不会忽略任何空字符串。 (然而会忽略所有的 NULL）。
    
    MariaDB [test]> select concat_ws('_',uid,username) from Info;
    +-----------------------------+
    | concat_ws('_',uid,username) |
    +-----------------------------+
    | 1_tom                       |
    | 2_esthor                    |
    | 3_frunk                     |
    +-----------------------------+
    
    MariaDB [test]> select concat_ws('_',uid,null,username, null) from Info;
    +----------------------------------------+
    | concat_ws('_',uid,null,username, null) |
    +----------------------------------------+
    | 1_tom                                  |
    | 2_esthor                               |
    | 3_frunk                                |
    +----------------------------------------+
  

• group_concat()

    /* GROUP_CONCAT([DISTINCT] expr [,expr ...]
     * [ORDER BY {unsigned_integer | col_name | formula} [ASC | DESC] [,col ...]]
     * [SEPARATOR str_val])
     */
    -- 1、DISTINCT 排除重复值
    -- 2、ORDER BY 排序
    -- 3、SEPARATOR 指定分隔符，默认为"，"
    -- 4、通过变量 group_concat_max_len 设置一个最大的长度。在运行时执行的句法如下： SET [SESSION | GLOBAL] group_concat_max_len = unsigned_integer;
    
    
    MariaDB [test]> select group_concat(username),age from Info group by age;     
    +------------------------+------+
    | group_concat(username) | age  |
    +------------------------+------+
    | esthor,frunk           |   18 |
    | tom                    |   20 |
    +------------------------+------+
    
    
    MariaDB [test]> select group_concat(distinct username order by uid desc separator '_'),age from Info group by age;   
    +-----------------------------------------------------------------+------+
    | group_concat(distinct username order by uid desc separator '_') | age  |
    +-----------------------------------------------------------------+------+
    | frunk_esthor                                                    |   18 |
    | tom                                                             |   20 |
    +-----------------------------------------------------------------+------+
  

三、日期和时间函数

函数	说明
from_unixtime()	转为时间戳类型时间
unix_timestamp()	返回长整形类型时间
current_date()	返回当前日期
current_time()	返回当前时间

四、流程控制函数

• if(expr,v1,v2)
  如果表达式expr成立，返回结果v1；否则，返回结果v2。

  root@localhost (none) -> select if(1>0, 'True', 'False');
  +--------------------------+
  | if(1>0, 'True', 'False') |
  +--------------------------+
  | True                     |
  +--------------------------+
  

• ifnull(v1,v2)
  如果v1的值不为NULL，则返回v1，否则返回v2。

  root@localhost (none) -> select ifnull(null,'Hello Word');
  +---------------------------+
  | ifnull(null,'Hello Word') |
  +---------------------------+
  | Hello Word                |
  +---------------------------+
  

五、系统信息函数

系统信息函数用来查询MySQL数据库的系统信息。

函数	说明	示例
version()	返回当前系统版本号	select version();
connection_id()	返回服务器的连接数	select connection_id();
database()	返回当前数据库名	select database();
user()	返回当前session登录用户	select user();
now()	返回当前系统时间	select now()；
last_insert_id()	返回当前session最近生成的auto_increment值	select last_insert_id();
charset(str)	返回字符串str的字符集，一般为系统默认字符集	select charset(‘张三’);
collation(str)	返回字符串str的字符排列方式，一般为系统默认排序方式	select collation(‘张三’);

六、加密函数

• password()

  该函数可以对字符串str进行加密，一般情况下，PASSWORD(str)用于给用户的密码加密。

  root@localhost (none) -> select password('123');
  +-------------------------------------------+
  | password('123')                           |
  +-------------------------------------------+
  | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
  +-------------------------------------------+
  

• md5()

  MD5(str)函数可以对字符串str进行散列，可以用于一些普通的不需要解密的数据加密。

  
  root@localhost (none) -> select md5('123');
  +----------------------------------+
  | md5('123')                       |
  +----------------------------------+
  | 202cb962ac59075b964b07152d234b70 |
  +----------------------------------+
  

• encode() & decode()

  ENCODE函数可以使用加密密码pswd_str来加密字符串str，加密结果是二进制数，需要使用BLOB类型的字段保存。该函数与DECODE是一对，需要同样的密码才能够解密。

  root@localhost (none) -> select encode('123', 'xxoo');
  +-----------------------+
  | encode('123', 'xxoo') |
  +-----------------------+
  | ;vx                   |
  +-----------------------+
  
  root@localhost (none) -> select decode(';vx', 'xxoo');
  +-----------------------+
  | decode(';vx', 'xxoo') |
  +-----------------------+
  | 123                   |
  +-----------------------+
  

七、其它函数

• IPv4转换int

  select inet_aton('192.168.39.75');
  select inet_ntoa(3232245579);
  

• 指定顺序排序

  field()
  select * from ta order by field(name,'seiki','iris','xut');
  

• 进制转换

  函数	说明	示例
  ascii(s)	返回字符串s的第一个字符的ASCII码	select ascii(‘af’); – 97
  bin(x)	返回x的二进制	select bin(10); – 1010
  hex(x)	返回x的十六进制	select hex(10); – A
  oct(x)	返回x的八进制	select oct(10); – 12
  conv(x,f1,f2)	返回将数字x的f1进制转换为f2进制	select conv(1010,2,16); – A