【MySQL】一些内置函数（时间函数、字符串函数、数学函数等，学会了有妙用）

前言

MySQL中内置类很多函数，这些函数可以让我们在进行数据统计、查表、建表的时候更加方便，本篇将MySQL中的内置函数分为四类，分别是：

• 时间函数

• 字符串函数

• 数学函数

• 其他函数

正式开始

时间函数

讲下面这些（先不用细看）：

简单介绍介绍。

首先声明一点，日期是指年月日，时间是指时分秒，日期时间是年月日时分秒。这在我前面的博客也是说过的。

显示当前日期、时间、日期时间的

current_date，当前日期，就是返回你调用这个函数时的日期:

不加括号也行，不过既然叫了函数那就最好把括号带上。

【注意】这些内置函数都要搭配着select使用，不然显示不出来：

关于select我就不说了，在前面博客已经详细说过了。

current_time，当前时间，就是返回你调用这个函数时的时间:

就不演示不带括号的了。

current_timestamp()，当前时间戳，时间和日期都会显示：
、
我前面在讲数据类型的时候，有个类型就叫做timestamp，默认值就是插入数据时的时间戳。

还有一个和这个时间戳很像，就是now：

其实就和current_timestamp一样的效果。

日期计算

两个，一个加一个减。

date_add，可以加年、月、日、时、分、秒。

还可以搭配前面的now来用：

会自动按照你给的类型来计算，类型不同结果也就不同。注意interval不能掉，还有类型也不能掉。

date_sub同理，就不说了。

相差多少天

date_diff，这个是计算两个日期之间相差多少天：

这个是用前者前去后者，故会出现负数的情况：

也可以嵌套调用：

统计工龄的时候就可以用这个函数。

时间的接口就讲这么些来两个例子。

示例

创建一张表，记录生日

生日一般精确到年月日就可以了，时分秒这里就不记录了。

创表：

里面的这些类型什么的我就不讲了，我前面的博客中有。

插入点数据：

因为id是自增的，所以不用手动插入。

再来嵌套调用：

能不能插入纯时分秒的呢？

竟然可以。

再来试一个：

竟然也可以。

我为啥说竟然呢？
因为我前面在讲数据类型的时候说过数据类型其实算是一种约束，MySQL对于数据匹配的要求是很严格的，但这里并没有那么严格，像隐式类型转换，把这当成例外。

其实可以理解成所有的时间函数底层用的都是一个函数，不过是要哪个显示哪个罢了。不过建议插入的时候类型为日期就用日期插，不要加时间。

留言表

平时很多软件都有评论区，评论有一个很重要的字段就是时间，比如说xxx发布于2023-10-23，其实原理就是客户点击发送，数据发送到后台服务器后，后台服务器将其转为sql语句对数据库做插入动作，这里就简单实现一个。

插入点数据：

这样就能把评论内容和评论时间拿到手。

如果显示时只想显示日期，就在sendtime外套个date就行：

再插入点：

来个问题：统计2分钟内发布过的帖子。
怎么确定表中的哪些数据是两分钟以内的？
找到当前时间的两分钟前的时间，假如说当前时间是$t_0$，当前时间的2分钟前是$t_1$，所以$t_1$的值就是$t_0?2$，对应图中就应该是这样：

此时在$t_1～t_0$之内的帖子就是我们想要的，那这之中的帖子在时间上有什么特点呢？假如说帖子的时间是$t$，那么就是这样：

这里t一定是小于$t_0$的，这点对于所有的帖子都会满足。

那么就好说了，只要让$t>t_1$就行了，也就是$t>t_0?2$，那么就会有两种解法：
$t>t_0?2$ 和 $t+2>t_0$

都来试一试，插入点数据：

然后筛选：

再重新插入点：

筛选：

筛选：

可以看到都是ok的。

字符串函数

讲这些：

charset

charset，查看字符集：

再用一下我前面博客定义的一张表：

都是utf8的。因为我的配置文件搞了默认的字符集。

再显示一下数字的：

concat

concat，追加的函数，只要是字符串就能追加，不管字符串的内容，有点像C中的strcat，来点例子：

里面可以有多个串：

没啥难的。

instr(string, substring)

这个函数是用来查询substring是否在string中，如果在就会返回其在第一个字符的位置，如果不在就返回0。

例子：

这里1234在abcde12345efg中，1在其中的第六个位置，这里是从1开始的，不是从0开始的。

再来个不存在的：

有点像find_in_set，这个是用来查找某一项是否在一个集合当中，如果在会返回其在集合中的位置，如果不在会返回0。想了解一下的可以看我这篇博客：【MySQL】数据类型

ucase和lcase

这两个是用来转大小写的，u是up，转大；l是low，转小。

转大写：

其中不是字母的就不会管。

转小写：

left(string, length)

这个函数用来取string左边的length个字符。

演示：

那有没有right？

有的。

length求字符串长度

这里编码会导致结果有点和你想的不一样：

因为utf8下一个汉字占用3个字节。而且utf8还是变长的编码，一部分是1字节的，比如说我在其中加上一点英文字母：

其中你好占用6字节，abc占用3字节。

replace(str, search_str, replace_str)

这个函数在str中找到searche_str字符串并将searche_str替换成replace_str。

例如：

这里将123456789中的567替换成了你好。

trim、ltrim、rtrim

这三个用来去掉左右两边字符串的空格。

trim是两边都去掉，ltrim是只去掉左边的，rtrim是只去掉右边的。

有时可能因为网络传输而导致传输的字符串左右两侧出现一些空格，此时不能让这些左右两侧的空格保存在数据库中，不然如果是用户的密码的话，用户后面就没法认证了，而且如果出现了这种问题还很难查出来问题所在，因为空格不可显。此时就可以用trim剪掉两边的空格，来避免这些问题。

一些示例

下面的所有示例都要用到刚刚讲的一些内置函数。

要求显示exam_result表中的信息，显示格式：“XXX的语文是XXX分，数学XXX分，英语XXX分"

下面的两个例子，基于这张表的：

就是改一下打印格式，打印的时候不要像上面表中那样分格打印，而是全部拼接到一块，假如说是为了发短信要这样搞。

直接用一个concat：

但是这样搞出来很难看，加点东西：

这样就可以将所有的内容拼接一块发出去，没人对应一条信息。

求学生表中学生姓名占用的字节数

其实就是用length。

还是因为utf8中一个汉字3个字节。

再强调一下：length函数返回字符串长度，以字节为单位。如果是多字节字符则计算多个字节数；如果是单字节字符则算作一个字节。比如：字母，数字算作一个字节，中文表示多个字节数（与字符集编码有关）

将EMP表中所有名字中有S的替换成’上海’

下面的三个例子基于我前一篇中搞的这张表：

将EMP表中所有名字中有S的替换成’上海’，就是用replace：

这些内置函数并不会影响原表中的数据。

截取EMP表中ename字段的第二个到第三个字符

不能用left或者right，二者取不到中间的数，不过嵌套调用也能实现，不过比较麻烦。

用substring，这个函数前面没说，其实就和C++中string的substr一样，取子串的。

以首字母小写的方式显示所有员工的姓名

嵌套调用一下：

其中substring如果没有给第三个参数，就会将指定子串位置后面的所有子串全部返回。

用left拿到第一个字符，然后转成小写，再和后面的字符串拼接。

数学函数

这些：

演示一下：

abs

求绝对值的：

bin

求二进制的：

hex

求十六进制的：

conv(num, from_base, to_base)

指定num从from_base进制转换成to_base进制：

其实这里覆盖了前面的两个进制转换。

ceiling 和 floor

一个向上取整（ceiling，天花板的意思），一个向下取整（floor，地板）。说说啥意思。这两个都是针对小数的。

向上取整：只要有小数部分，就直接取大的值，比如说2.1，就直接直接转成3。转成最近的大的数。再比如说-2.1，那就回转成-2，转成比原数大的最近的整数，也就是-2。看这张图：

如果是2.5，向上取整就是3，如果是-2.9，线上取整为-2。
这就是向上取整。

向下取整，只要有小数，就取小的值，比如说下图中2.1，取的是2，-2.1取的是-3：

如果是-2.9，向下取整为-3，如果是2.9，向下取整为2。

看这两个函数的例子，向上的：

向下的：

还有两中取整，一种是0向取整，还有一种是小学都学过的四舍五入。

0向取整，其实也很常见，就是直接舍弃掉小数部分，在C语言中很常见，比如两个整数的除法，9/2，得到的数应该是4.5，但是语言中是直接去掉0.5，得到4。再比如说-3.6，0向取整就是-3，图是这样的：

format(一个小数, 指定小数位数)

这个是对于小数有用，指定多少位小数：

上面就是保留两位小数。

再来个保留五位小数的：

mod

取模的函数：

关于负数取模咋做的我这里就不说了，重点不在这，不会的同学自行搜一搜。

rand

产生随机数的函数，产生的数的范围是[0, 1.0)：

如果你想要你指定范围的数，乘以几然后加加减减就行，比如说我想要0 ~ 100的：

如果不想要小数部分就直接用format：

其他函数

user

查询当前以什么身份登录：

database

当前选中的是哪个数据库：

md5

加密的东西，其实也不算加密，就是形成一个数据摘要，我前面在讲HTTPS的时候带过一嘴：

这个函数会形成一个摘要，所有的字符串形成的摘要都是等长的，但基本上不可能出现两个不同字符串产生相同摘要的情况，但是同一个字符串的摘要是相同的：

两个字符串哪怕是差了一个字符结果也都会相差很多：

一般数据库中不会直接保存用户的登录密码，一般都是先形成摘要然后再保存到数据库中，这样如果数据库被黑了，其中的数据黑客拿到后没法轻松的获取到用户的密码，这样用户的重要信息就不会泄漏。用户登录的时候是用密码先形成摘要，然后如果数据库有这个摘要就会登陆成功，如果没有这个摘要就认证失败。

还有一个password函数：

形成的摘要更长，password用的也是比md5多一点。

password形成的摘要也都是等长的。

ifnull(val1, val2)

这个函数和三木运算符有点像，如果val1是NULL就会返回val2，如果不是NULL就会返回val1，就好比val1 == NULL ? val2 ：val1。

例子：

这个函数有些地方还是很有用的，我下一篇讲符合查询的时候就会用到。

这篇就介绍这么多。

到此结束。。。