MySQL常用函数

摘要:

这是一篇关于MySQL的文章，主要介绍 MySQL 常用函数

---

1. 字符串函数

• 合并字符串函数: CONCAT()

mysql> select concat('love', ' cs');+------------------------+| concat('love', ' cs') |+------------------------+| love cs               |+------------------------+1 row in set (0.01 sec)-- 遇到空值，整个结果为NULLmysql> select concat('love', ' cs', NULL);+------------------------------+| concat('love', ' cs', NULL) |+------------------------------+| NULL                         |+------------------------------+1 row in set (0.00 sec)

• 合并字符串函数: CONCAT_WS(),可以指定分隔符

-- 第一个参数为分隔符mysql> select concat_WS('-', 'love', 'cs');+-------------------------------+| concat_WS('-', 'love', 'cs') |+-------------------------------+| love-cs                      |+-------------------------------+1 row in set (0.00 sec)

• 字符串比较函数: STRCMP()

mysql> select STRCMP('ab', 'aa'),        STRCMP('ab', 'ab'),        STRCMP('ab', 'ac');+--------------------+--------------------+--------------------+| STRCMP('ab', 'aa') | STRCMP('ab', 'ab') | STRCMP('ab', 'ac') |+--------------------+--------------------+--------------------+|                  1 |                  0 |                 -1 |+--------------------+--------------------+--------------------+1 row in set (0.00 sec)

• 获取字符串长度: LENGTH()

mysql>  select length('xff');+---------------+| length('xff') |+---------------+|             3 |+---------------+1 row in set (0.00 sec)

• 大小写转换: UPPER(), LOWER()

mysql> select UPPER('xff'), LOWER('XFF');+--------------+--------------+| UPPER('xff') | LOWER('XFF') |+--------------+--------------+| XFF          | xff          |+--------------+--------------+1 row in set (0.00 sec)

• 字符串位置查找: FIND_IN_SET()

mysql> select FIND_IN_SET('BEIJING', 'HONGKANG,BEIJING');+--------------------------------------------+| FIND_IN_SET('BEIJING', 'HONGKANG,BEIJING') |+--------------------------------------------+|                                          2 |+--------------------------------------------+1 row in set (0.00 sec)

• 截取字符串函数: LEFT(), RIGHT(), SUBSTRING(), MID()

mysql> select left('beijing', 3), right('beijing', 4), mid('beijing welcome you', 9, 7);+--------------------+---------------------+----------------------------------+| left('beijing', 3) | right('beijing', 4) | mid('beijing welcome you', 9, 7) |+--------------------+---------------------+----------------------------------+| bei                | jing                | welcome                          |+--------------------+---------------------+----------------------------------+1 row in set (0.00 sec)-- SUBSTRING函数和MID函数相同

• 字符串去除空格函数: LTRIM, RTRIM, TRIM

-- 三个函数分别为去除左边空格，右边空格和两边空格mysql> select concat('|', LTRIM('     xff        '), '|'),    ->        concat('|', RTRIM('     xff        '), '|'),    ->        concat('|', TRIM('     xff        '), '|')\G*************************** 1. row ***************************concat('|', LTRIM('     xff        '), '|'): |xff        |concat('|', RTRIM('     xff        '), '|'): |     xff| concat('|', TRIM('     xff        '), '|'): |xff|1 row in set (0.00 sec)

• 字符串替换函数: REPLACE

mysql> select replace('Beijing welcome you', 'Beijing', 'MySQL');+----------------------------------------------------+| replace('Beijing welcome you', 'Beijing', 'MySQL') |+----------------------------------------------------+| MySQL welcome you                                  |+----------------------------------------------------+1 row in set (0.00 sec)

2. 数值函数

• 获取随机数: RAND()

-- 返回0 ~ 1之间的随机数mysql> select RAND();+---------------------+| RAND()              |+---------------------+| 0.25700983522181386 |+---------------------+1 row in set (0.00 sec)

• 四舍五入函数(圆整): ROUND()

-- 第二个参数为精度，如果没有指定精度则返回的是整数mysql> select ROUND(3.14159), ROUND(3.145927, 3);+----------------+--------------------+| ROUND(3.14159) | ROUND(3.145927, 3) |+----------------+--------------------+|              3 |              3.146 |+----------------+--------------------+1 row in set (0.00 sec)

3. 时间和日期函数

• 当前时间和日期

-- 分别为获取当前日期，时间，日期和时间mysql> select CURDATE(), CURTIME(), NOW();+------------+-----------+---------------------+| CURDATE()  | CURTIME() | NOW()               |+------------+-----------+---------------------+| 2016-02-26 | 08:14:05  | 2016-02-26 08:14:05 |+------------+-----------+---------------------+1 row in set (0.00 sec)

• 获取时间和日期各部分

-- 顾名思义mysql> select  now(),               year(now()),               quarter(now()), -- 季度               month(now()),               week(now()),    -- 第几周               dayofmonth(now()),               hour(now()),               minute(now()),               second(now())\G*************************** 1. row ***************************            now(): 2016-02-26 08:17:02      year(now()): 2016   quarter(now()): 1     month(now()): 2      week(now()): 8dayofmonth(now()): 26      hour(now()): 8    minute(now()): 17    second(now()): 21 row in set (0.00 sec)

4. 系统信息函数

mysql> select version(),    ->        database(),    ->        user(),    ->        last_insert_id(); --最近一次auto_increment的数值+-------------------------+------------+----------------+------------------+| version()               | database() | user()         | last_insert_id() |+-------------------------+------------+----------------+------------------+| 5.6.28-0ubuntu0.14.04.1 | NULL       | root@localhost |                0 |+-------------------------+------------+----------------+------------------+1 row in set (0.00 sec)