常用基础 - 函数 ¶
作者：KK
发表日期：2018.06.25
更新日期：2019.08.29
字符串函数增加了
SUBSTR函数的介绍更新日期：2022.01.18
字符串函数增加了
LENGTH函数的介绍
介绍 ¶
相信你读到这篇文章的时候基本上已经是接触过mysql的函数了，就很简单，大部分函数使用起来就像编程语言的函数调用一样，函数名加个括号就行了，比如SELECT NOW();就是查询一个数据，这个数据并不是来自于数据表，而是一个函数的返回值，于是就得到了当前的时间，再比如还可以传参数SELECT MOD(29, 9);这是个模运算（求余）；然而也不是所有都是这样的，比如SELECT username REGEXP '^jay'这样一个正则匹配的REGEXP也是函数，只是写起来就好像是语法的一部分的样子而已。
首先其实开发过程中是提倡尽量不要使用函数的，因为它会影响 索引 的使用决策（绝大多情况无法使用索引），导致查询变慢；少数业务代码的程序语句确实需要用上函数，但大多数使用函数的情况都是在于数据统计和迁移维护的场景下执行的，函数还是需要懂的，只是无须懂得太多，有需要相关的处理就去 官方手册 查一下有没有你需要的函数就好了。
MySql里的函数范围覆盖了常规的数据处理需求，编程语言中经常写的函数功能，在这里也能找得到，不仿了解一下吧。
流程控制函数 ¶
流程控制函数共4个，这四个函数称为流程控制函数，虽然用起来跟普通函数没啥区别，但官方文档是这么分类的，个人估计这四个函数底层是按语法级别来解析的吧，而不是函数级别，但其实这也不一定，人家没准就只是个函数，但作用上来说是实现了流程控制而已（个人觉得只是IF和CASE比较有流程控制的意思）。下面看看它们怎么用的：
IF ¶
示例：
SELECT IF(age >= 18, 1, 0) AS xx FROM user;
/*SELECT IF(age >= 18, '已成年', '未成年') AS xx FROM user; 可是如果你复制这句粘贴到命令行应该是不能运行的，只是因为mysql终端不支持中文输入而已 */
以上语句从用户表查出数据并返回一个别名为xx的字段，这个字段的值是这样确定的：判断每一行的age字段，如果大于等于18就设为1，否则就设为0。
IF并不是一个常用函数，是个少用函数，但也是会用到的，了解一下比较好。
CASE ¶
就像编程语言的switch...case语法里有点像，如：
SELECT CASE type WHEN 1 THEN 'userType1' WHEN 2 THEN 'userType2' ELSE 'unknowUserType' END as userType FROM user;
/*下面美化一下排版：*/
SELECT
CASE type
WHEN 1 THEN 'userType1'
WHEN 2 THEN 'userType2'
ELSE 'unknowUserType'
END as userType
FROM user;
这个比IF还要少用，几乎不用，比如我工作了7年才用了2次吧，而且都是手动SQL统计查询时才用的。
IFNULL ¶
SELECT IFNULL(age, 0) AS age FROM user;
如果age字段的值为NULL那就返回第2个参数0的值作为age字段的值，否则就引用原来的age字段（第1个参数），我从来没用过这个函数。
NULLIF ¶
SELECT NULLIF(age, 0) AS age FROM user;
如果age字段的值为0，那么为返回NULL，否则就返回age的值，相当于：CASE WHEN age = 0 THEN NULL ELSE age END这样，我也从来没用过这个函数。
字符串函数 ¶
顾名思义就是处理字符串的函数啦，一共有60个左右，我这里只列举几个我用过的，以及大家可能用得上的：
CONCAT ¶
把所有参数都转成字符串并连接起来：SELECT CONCAT(username, email, ' time:', created_time) FROM .... WHERE ...就会出现类似wawa33113@qq.com time:2017-11-20 09:17:34，但是如果中间有一个参数是NULL的话只会返回null，数字会被转成字符串数字。
我使用过2次的场景就是基于一个已经存在的字段上补充特定的前缀或后缀内容，比如CONCAT(content, '。')这样是补了一个句号，具体场景我忘记了，大概就是这样，当然用编程语言写代码实现也行，就不必关心这个函数了。
FIND_IN_SET ¶
在一个逗号作为间隔的字符串里查找子字符串的位置。
比如有些小应用会设计一个permissions字符储存用户的权限，每个权限以逗号隔开，那么SELECT username FROM user WHERE FIND_IN_SET('permission1', permissions)就是在权限里查找有没有permission1这个权限了，没有的话FIND_IN_SET就会返回0，于是不成立，成立的就会被查询出来。
LIKE和NOT LIKE ¶
这个是字符串匹配查询，这里我就不啰嗦介绍了，大家肯定知道！
REGEXP ¶
用正则表达式匹配一个字符串：SELECT COUNT(1) as count FROM user WHERE email REGEXP '@qq.com$'统计@qq.com结尾的邮箱数量。但注意这个是不分大小写匹配的，所以@Q.cOm这样带大写字母的也会被匹配。其中MySql里的正则表达式和PHP、JS等语言的正则有少许不同，使用时具体参见 官方文档 - 函数与操作符 - 字符串函数 - 正则表达式 章节的正则符号说明。
- 添加
BINARY关键词声明大小写严格匹配：SELECT COUNT(1) as count FROM user WHERE email REGEXP BINARY '@qq.com$'
LENGTH ¶
计算字符的字节数，例如看一篇文章的字节数：SELECT id, title, LENGTH(content) AS content_bytes FROM article WHERE id = 123
或者用在条件比较的场景也可以：WHERE LENGTH(content) > 1024000
REPLACE ¶
替换字符串，这个跟大部分编程语言的函数一样，一般在数据库的某些数据出问题时，通过这个函数更新数据来去掉某个字段的某些字符，如去掉标题里的粗口词语：UPDATE article SET title = REPLACE(title, '他妈的', '') WHERE xxx
或者用在条件比较的场景也可以：WHERE REPLACE(username, 'xxxx', '') = 'abc'
SUBSTR ¶
裁剪字符串，也是和大部分编程语言一样，SUBSTR(被裁剪的字符串, 起始位置, 裁剪长度)，个人觉得缺点是裁剪长度不支持负数，另外要注意起始位置是从 1 开始的哦！
例如我有个 url 字段存的地址后面都有一个/号（如http://xxx.com/a/b/c/），要去掉它：
UPDATE 表名1, LENGTH(url) - 1)
第2个参数不支持负数的情况下，我是通过LENGTH函数算出长度再减1来得到需要保留的内容长度的
数学函数 ¶
数学函数我就不列举了，其实五年来也一直没用过，什么ABS、CEIL、COS、FLOOR、MOD、PI和POW等都是大部分编程语言的数学函数包里都带有的经典函数名，如果你需要在MySql里用的话就看 官方文档 - 函数与操作符 - 数学函数，然而实际上我五年来都没用过。
日期时间函数 ¶
FROM_UNIXTIME ¶
将一个 Unix 时间戳转换成日期时间格式:
SELECT FROM_UNIXTIME(1532751194) AS datetime;
/* 2018-07-28 12:13:14 */
这个函数大家比较常用，因为许多人以数字时间戳的形式来保存时间，但要查询时又想知道是什么日期时间，于是就用了这个函数，比如SELECT FROM_UNIXTIME(created_time) FROM user WHERE ...。
UNIX_TIMESTAMP ¶
返回当前时间的 Unix 时间戳，如果传入时间字符串则返回对应的时间戳：
SELECT UNIX_TIMESTAMP() as timeStamp;
/* 1533751194 根据你实际测试时的时间而定*/
SELECT UNIX_TIMESTAMP('2018-07-28 12:13:14') as timeStamp;
/* 1532751194 */
CURDATE ¶
获取当前日期（YYYY-MM-DD 格式）：
SELECT CURDATE();
//如： 2018-05-23
SELECT CURDATE() + 0;
//转数字，如： 20180523
SELECT CURDATE() + 40;
//20180661 认真一看你发现这不是40天后的日期，它只是全部转成数字后累加了40而已，所以在这样用加法的情况下要清楚你在查什么。
CURTIME ¶
获取当前时间（HH:mm:ss 格式）：
SELECT CURTIME();
ADDDATE ¶
向前推演日子：
SELECT ADDDATE('2018-02-28', INTERVAL 30 DAY);
/*推演日期： 2018-03-30 */
SELECT ADDDATE('2018-02-28', INTERVAL 2 MONTH);
/*推演月份： 2018-04-28 */
SELECT ADDDATE('2018-02-28', INTERVAL 2 YEAR);
/*推演年份： 2020-02-28 */
ADDTIME ¶
向前推演时间，以下是官方文档的例子：
SELECT ADDTIME('2007-12-31 23:59:59.999999', '1 1:1:1.000002');
/* 2008-01-02 01:01:01.000001 */
DATE ¶
从一个日期时间字符串中提取出日期部分：
SELECT DATE('2018-09-09 12:34:56');
/* 2018-09-09 */
DAY ¶
从一个日期时间或者日期字符串中提取出一个月中一日的数字：
SELECT DAY('2018-09-08 12:34:56');
/* 8 */
注意：非法的日期会提取出 NULL，比如 2018-02-29 这个年份的2月没有29号的，就会得 NULL。
DAYNAME ¶
返回日期时间或日期字符串中那一天的星期几名称：
SELECT DAYNAME('2018-09-08');
/* Saturday */
DAYOFYEAR ¶
返回日期时间或日期字符串中那一天是一年中的第几天：
SELECT DAYOFYEAR('2018-09-08');
/* 251 */
DATEDIFF ¶
计算第1个日期参数比第2个日期参数多了多少天：
SELECT DATEDIFF('2018-09-08', '2018-09-02');
/* 6 */
时间函数小结 ¶
还有许多时间函数，如果需要学习使用的话请到官方文档中找到Functions and Operators - Date and Time Functions章节，或通过 https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_unix-timestamp 来直达访问（链接多年后可能会失效）。
加密函数 ¶
MD5、SHA1、SHA2等，详见 https://dev.mysql.com/doc/refman/8.0/en/encryption-functions.html
聚合函数（重要） ¶
上面介绍的函数是针对一行数据里面的一列进行计算处理的，而聚合函数则是对一列数据里的多行数据进行处理的。普通函数的处理结果是你的条件查询到多少行，就返回多少行数据，并将指定列的计算结果呈现出来，每一行都有一个计算结果；而聚合函数是即使你查询了NNN行数据，但这些函数对这一列进行了计算后只会有1个结果，而不是返回对应行数量的结果，因此普通函数和聚合函数不能同时出现在 SELECT 输出字段列表里面。
COUNT ¶
这个大家肯定很熟悉，对查询结果的指定列进行统计，如SELECT COUNT(id) AS count FROM user这样来统计用户表的数量。其实一般获取一张表的记录条数并不建议针对某一列进行COUNT计算，而是直接SELECT COUNT(1) AS count FROM user这样就可以了，这样数据库引擎就不需要把这一列数据提取出来进行计算，只要临时产生个数字被计算就行了。
其中要注意的是如果被统计的列存在NULL的值，那这些值是不会被算进数量里的，比如用户的address字段2个NULL，那么100条用户数据里面COUNT(address)就会算出98个数量。
统计指定列的不同值的个数：
假如想知道用户们都来自多少个不同的国家：
SELECT COUNT(DISTINCT country) AS country_count FROM user，就是在被统计的列名前面加个DISTINCT关键字即可。
多结果聚合 ¶
虽然聚合函数是针对查询结果的指定列进行聚合得出1个值，可是如果加入了GROUP BY的时候，你可以认为同时查出了多个结果，每个结果集被分组临时存放在一个临时空间中，而此时SELECT COUNT(id)， gender FROM user GROUP BY gender这样的语句里面的 COUNT 其实就针对是以 gender 分出的各个组进行 id 的数量统计，所以你会发现查询结果里有多行结果，每一行都有一个 count。而后面介绍的聚合函数都可以这样用。
MAX和MIN ¶
取出某一列的最大和最小数字或者日期，如：
看看最年轻的用户是什么时候出生的：
SELECT MAX(birthday) FROM user看看最年长的用户是什么时候出生的：
SELECT MIN(birthday) FROM user
AVG求平均值 ¶
求指定列的数字的平均值，比如这个例子是求用户们平均有多少钱：SELECT AVG(money) FROM user其中NULL的值不会参与计算。
SUM求总和 ¶
求指定列的数字的总和，比如这个例子是求用户们一共有多少钱：SELECT SUM(money) FROM user其中NULL的值不会参与计算。
GROUP_CONCAT串连所有值 ¶
把指定列的每个值以逗号为间隔串连起来，比如：
SELECT GROUP_CONCAT(id) AS ids FROM user WHERE xxxx;
会得到类似2377812,312252,977353,47287这样逗号串起来的值。
其它函数 ¶
RAND产生一个随机浮点数 ¶
这个许多人是用来实现随机排序的，比如SELECT * FROM user ORDER BY RAND()，原理是这样的，比如ORDER BY id ASC的话假设有6、9、4这样三个ID则会按照它们的值小到大排序成4、6、9。而我个人目前粗浅地猜测，其实RAND函数为每一生产生一个随机数字后，把这些数字从小到大排序其实也是变得不确定了，因为每次随机出来的最小浮点数在哪一行都不确定。等以后有能力看MySql源码了再确定一下这个结论哈。
INET_ATON和INET_NTOA把IPv4地址和数字互转 ¶
/* 有些人会在入库时用函数把IP转换成数字 */
INSERT INTO user (register_ip) VALUES(INET_ATON('211.178.11.32'));
/* 然后在查出来时把数字转换成IP */
SELECT INET_NTOA(register_ip) AS ip FROM user WHERE ...;