MySQL内置函数

目录

日期函数

current_date()函数

current_time()函数

current_timestamp()函数

now()函数

date()函数

date_add()函数

date_sub()函数

datediff()函数

字符串函数

charset()函数

concat()函数

instr()函数

ucase()函数

lcase()函数?

left()函数

right()函数

length()函数

replace()函数

strcmp()函数

substring()函数

trim()、ltrim()和rtrim()函数

综合案例

数学函数

abs()函数

bin()函数

hex()函数

conv()函数

ceiling()函数

floor()函数?

format()函数

rand()函数

mod()函数

常见的其他函数

user()函数

md5()函数和password()函数

database()函数

ifnull()函数

---

日期函数

current_date()函数

如下图所示，current_date函数用于获取当前的日期。需要注意的是，该函数获取到的值的类型本质就是在<<MySQL数据类型>>一文中说的date类型，所以如下图红框处所示是可以通过current_date函数直接给date类型的属性time1赋值的。

current_time()函数

如下图所示，current_time函数用于获取当前的时间。

current_timestamp()函数

如下图所示，current_timestamp函数用于获取当前的时间戳。?需要注意的是，该函数获取到的值的类型本质就是在<<MySQL数据类型>>一文中说的datetime类型，所以如下图红框处所示是可以通过current_timestamp函数直接给datetime类型的属性time1和timestamp类型的属性time2赋值的（说一下，timestamp类型可以认为是特殊的datetime类型，二者的唯一区别是如果用户不主动对timestamp类型的值赋值，则MySQL会自动插入当前的时间戳，而datetime则不具备这样的特性。所以datetime类型的值是可以赋给timestamp类型的属性time2的）。

now()函数

如下图所示，now函数用于获取当前的日期时间。该函数的功能和current_timestamp()函数完全一致。

date()函数

如下图所示，date()函数的功能是：给date()函数传一个datetime类型的值（即完整的年月日时分秒）后，date()函数会在该值中截取一个date类型的值（即年月日）出来。

date_add()函数

如下图所示，date_add函数用于在日期的基础上添加日期或时间，增加时间的单位可以是year、day、minute、second。

注意如果在date_add函数中添加的日期/时间为负值，则相当于在日期的基础上减去日期/时间。如下：

最后要知道的是，date_add函数中是可以嵌套其他日期函数的，如下：

date_sub()函数

该函数的特性和date_add()函数完全一致，只不过作用是相反的，即date_sub()函数用于在日期的基础上减少日期或时间，减少时间的单位可以是year、day、minute、second。

因为【当前的date_sub()函数的特性和使用方式】和【上面的date_add()函数的特性和使用方式】完全一致，所以这里只演示一个例子，其他内容请参考上面的date_add()函数。date_sub()函数的案例如下图所示。

datediff()函数

如下图所示，datediff函数用于获取两个日期的差，单位是天。

然后注意，如果传给datediff()函数的日期精确到了时分秒，则datediff()函数会忽略时分秒，最后计算出的结果的单位依然是天，如下图演示。

字符串函数

charset()函数

该函数用于获取目标正在使用的字符集。目标可以是一张表中的某个属性，也可以是一个字符串，也可以是一个数字，演示如下。（注意整形数据123不是字符数字，所以MySQL存储123时不需要字符集这样的编码格式来将字符转换成二进制数字，所以下图中的值是binary）

额外说一下，如果查表时发现有乱码，则一定是因为曾经插入该数据时所用的字符集和如今取数据的校验规则不匹配，没有其他原因。

concat()函数

该函数用于拼接数据，数据的类型可以是各种各样的，演示如下。

这有什么用呢？举个例子，现有如下成绩表，要求以“XXX的语文是XX分，数学是XX分，英语是XX分”的格式显示成绩表中的信息时，我们就可以通过concat()函数实现这一点，演示如下：

instr()函数

instr(str1,str2)函数用于获取字符串str2在字符串str1中首次出现的位置，如果没有出现则返回0。

• 为什么没出现时会返回0呢？这是因为MySQL考虑到使用MySQL的用户可能并没有学过编程语言，可能并不明白数组以及数组下标的概念，为此，MySQL决定让MySQL中的字符串的下标从1开始，而不是从0开始，这也是为什么下图在hello中找字符 l 时，明明根据程序员的常识，字符 l 首次出现的位置应该是下标号为2的位置，但查询结果却显示的是3。

演示如下：

ucase()函数

ucase函数用于获取转换成大写后的字符串。如下：

lcase()函数?

lcase函数用于获取转换成小写后的字符串。如下：

left()函数

left函数用于从字符串的最左边开始，向右截取指定个数的字符。演示如下。

right()函数

right函数用于从字符串的最右边开始，向左截取指定个数的字符。演示如下。

length()函数

length函数用于获取字符串占用的字节数（注意是字节数，而不是字符个数）。演示如下：

注意，因为笔者当前的字符集是utf-8，所以一个汉字占3个字节，如果你采用其他的字符集，比如gbk，则一个汉字占2个字节，此时你的结果就会和下图不同。

replace()函数

replace(str，search_str，replace_str)函数用于将字符串str中的子串search_str替换成另一个字符串replace_str，例如下图中将员工表emp中所有员工的名字ename进行修改，即只要名字里有字符串“S”，则将字符串“S”替换成字符串“北京”。

需要注意的是，调用replace函数后，原表中的数据并不会真的被替换，如下图红框处所示，emp表中的每条数据的属性ename的值都还是保持着原样，没有被替换。

strcmp()函数

strcmp函数用于按照ASCII码逐字符比较两个字符串的大小，两个字符串大小相等返回0，前者大返回1，后者大返回-1。如下：

需要注意的是，strcmp函数在比较时是不区分大小写的。如下：?

substring()函数

substring(str，position，length)函数用于从字符串str的指定位置position开始，共向后截取length个字符（注意单位是字符，而不是字节）。注意，在上文讲解instr函数时说过【MySQL中的字符串的下标从1开始，而不是从0开始】，所以position为2表示从第二个字符的位置开始截取。

演示如下：

使用substring函数时，如果没有指定要截取的字符个数，则默认从指定位置开始截取到最后；如果指定要截取的字符个数为0，则不会进行截取。如下：

substring函数的应用场景：例如可以通过该函数截取员工表中每条数据的属性ename的值的第1到第2个字符。如下：?

trim()、ltrim()和rtrim()函数

trim函数用于去除字符串的前后空格。如下：

说一下，在使用trim函数完成前后空格的清除后，下图红框处的【你好啊!】的后面的一大块空格看上去并没有被清除，但注意实际上是被清除了的，红框处有一大块空格是因为蓝框处字符串太长，比如说给蓝框处的字符串重命名成【测试】后，如下图黄框处所示，黄框处的【你好啊!】的后面就没有空格了。

ltrim()和rtrim()函数分别用于去除字符串的左边空格和右边空格。如下：

综合案例

要求以首字母大写、其他字母小写的方式显示员工表中所有员工的姓名，思路如下：?

• 先使用substring函数截取员工姓名的第一个字符。
• 再使用substring函数截取员工姓名的第二个字符及其后续字符。
• 再使用lcase函数将第二次截取的字符串转换成小写。
• 最后使用concat函数，将第一次截取的首字母和第二次截取并转换成小写的的字符串进行连接。

数学函数

abs()函数

abs函数用于获取一个数的绝对值。如下：

bin()函数

bin函数用于将一个十进制数转换成二进制。如下：

hex()函数

hex函数用于将一个十进制数转换成十六进制。如下：

conv()函数

conv(num，from_base，to_base)函数用于将数字num从进制from_base转换成另一个进制to_base。举个例子，当数字num为10时，如果from_baes为2，则说明数字10是二进制数，如果to_base为10，则conv函数的作用就是将二进制10转换成十进制的数，二进制数字10转换成十进制后，结果当然就是2了。演示如下：

ceiling()函数

ceiling函数用于对一个数进行向上取整，向上取整本质是向正无穷方向取整，因此负数向上取整后得到的是第一个大于等于该数的整数。演示如下：

floor()函数?

floor函数用于对一个数进行向下取整，向下取整本质是向负无穷方向取整，因此负数向下取整后得到的是第一个小于等于该数的整数。演示如下：

format()函数

format函数用于对数值进行格式化，以四舍五入的方式保留指定位数的小数。如下：

rand()函数

rand函数用于生成0.0到1.0的随机浮点数。如下：

如果想要生成左闭右开区间的 [0，100)的随机数，可以用生成的随机浮点数乘以100，然后再以向下取整方式进行取整。如下：

mod()函数

mod函数用于对数值进行求余运算。如下：

常见的其他函数

user()函数

user函数用于让用户获取自己在连接使用MySQL时的用户名和主机名。如下：

md5()函数和password()函数

md5()函数和password()函数都用于把一个字符串通过哈希算法变成数据摘要，二者的区别在于，通过md5函数得到的数据摘要是一个32位的字符串，通过password()函数得到的数据摘要是一个41位的字符串。

我们在<<应用层协议——https协议>>一文中说过，数据摘要是一种加密方式，那么问题来了，在MySQL中为什么要通过md5()函数或者password()函数进行加密呢？

• 一般情况下，公司内部的数据库都不会存储用户的明文密码，而会将用户的密码通过md5()函数或者password()函数变成摘要后再存储对应的摘要，后序当用户登录账号时，会将用户输入的密码也通过md5()函数或者password()函数变成摘要，然后再与数据库中存储的数据摘要做对比，如果对比成功则允许登录。
• 像这样做的好处主要有两个，第一个好处就是因为公司内部数据库中存储的不是用户的明文信息，所以就算用户信息泄露了也不会产生太大影响，第二个好处就是通过md5()函数或者password()函数形成的摘要是定长的，这样有利于数据库表结构的设计。
• 这就是在MySQL中为什么要通过md5()函数或者password()函数进行加密的原因了。

database()函数

database函数用于显示当前正在使用的数据库。如下：

ifnull()函数

ifnull（val1， val2）的作用为：如果第一个参数val1不为null，则返回第一个参数的值；如果第一个参数val1为null，则返回第二个参数值。