4.8　其他函数

本节将要介绍的函数不能笼统地归为一类，但是这些函数也非常有用，例如重复指定操作函数、改变字符集函数、IP地址与数字转换函数等。本节将介绍这些函数的作用和用法。

4.8.1　格式化函数FORMAT(x,n)

FORMAT(x,n)将数字x格式化，并以四舍五入的方式保留小数点后n位，结果以字符串的形式返回。若n为0，则返回结果函数不含小数部分。

【例4.100】使用FORMAT函数格式化数字，保留小数点位数为指定值，输入语句如下：

     MySQL> SELECT FORMAT(12332.123456, 4), FORMAT(12332.1,4), FORMAT(12332.2,0);
     +-------------------------+-------------------+-------------------+
     | FORMAT(12332.123456, 4) | FORMAT(12332.1,4) | FORMAT(12332.2,0) |
     +-------------------------+-------------------+-------------------+
     | 12,332.1235           | 12,332.1000      | 12,332          |
     +-------------------------+-------------------+-------------------+

由结果可以看到，FORMAT(12332.123456, 4)保留4位小数点后的数字，并进行四舍五入，结果为12332.1235；FORMAT(12332.1,4)保留4位小数值，位数不够的用0补齐；FORMAT(12332.2,0)不保留小数位值，返回结果为整数12332。

4.8.2　不同进制的数字进行转换的函数

CONV(N, from_base, to_base)函数进行不同进制数之间的转换，返回值为数值N的字符串表示，由from_base进制转化为to_base进制。如有任意一个参数为NULL，则返回值为NULL。自变量N被理解为一个整数，但是可以被指定为一个整数或字符串。进制数最小基数为2，最大基数为36。

【例4.101】使用CONV函数在不同进制数值之间转换，输入语句如下：

     mysql> SELECT CONV('a',16,2), CONV(15,10,2), CONV(15,10,8), CONV(15,10,16);
     +----------------+---------------+---------------+----------------+
     | CONV('a',16,2) | CONV(15,10,2) | CONV(15,10,8) | CONV(15,10,16) |
     +----------------+---------------+---------------+----------------+
     | 1010          | 1111         | 17          | F            |
     +----------------+---------------+---------------+----------------+

CONV('a',16,2) 将十六进制的a转换为二进制表示的数值，十六进制的a表示十进制的数值10，二进制的数值1010正好等于十进制的数值10；CONV(15,10,2)将十进制的数值15转换为二进制值，结果为1111；CONV(15,10,8)将十进制的数值15转换为八进制值，结果为17；CONV(15,10,16)将十进制的数值15转换为十六进制值，结果为F。

进制说明：

●　二进制，采用0和1两个数字来表示的数。它以2为基数，逢二进一。

●　八进制，采用0、1、2、3、4、5、6、7八个数字，以数字0开头，逢八进一。

●　十进制，采用0~9，共10个数字表示，逢十进一。

●　十六进制，由0~9、A~F组成，以数字0x开头。它与十进制的对应关系是：0~9对应0~9，A~F对应10~15。

4.8.3　IP地址与数字相互转换的函数

INET_ATON(expr)给出一个作为字符串的网络地址的点地址表示，返回一个代表该地址数值的整数。地址可以是4或8bit地址。

【例4.102】使用INET_ATON函数将字符串网络点地址转换为数值网络地址，输入语句如下：

     mysql> SELECT INET_ATON('209.207.224.40');
     +-----------------------------+
     | INET_ATON('209.207.224.40') |
     +-----------------------------+
     |            3520061480     |
     +-----------------------------+

INET_NTOA(expr)函数给定一个数字网络地址（4或8bit），返回作为字符串的该地址的点地址表示。

【例4.103】使用INET_NTOA函数将数值网络地址转换为字符串网络点地址，输入语句如下：

     mysql> SELECT INET_NTOA(3520061480);
     +-----------------------+
     | INET_NTOA(3520061480) |
     +-----------------------+
     | 209.207.224.40        |
     +-----------------------+

可以看到，INET_NTOA和INET_ATON互为反函数。

4.8.4　加锁函数和解锁函数

GET_LOCK(str,timeout)函数设法使用字符串str 给定的名字得到一个锁，超时为timeout秒。若成功得到锁，则返回1；若操作超时，则返回0；若发生错误，则返回NULL。假如有一个用GET_LOCK()得到的锁，当执行RELEASE_LOCK()函数或连接断开（正常或非正常）时，这个锁就会解除。

RELEASE_LOCK(str)函数解开被GET_LOCK()获取的、用字符串str 所命名的锁。若锁被解开，则返回1；若该线程尚未创建锁，则返回0（此时锁没有被解开）；若命名的锁不存在，则返回NULL。若该锁从未被GET_LOCK()的调用获取，或锁已经被提前解开，则该锁不存在。

IS_FREE_LOCK(str)函数检查名为str的锁是否可以使用（换言之，没有被封锁）。若锁可以使用，则返回1（没有人在用这个锁）；若这个锁正在被使用，则返回0；出现错误，则返回NULL（诸如不正确的参数）。

IS_USED_LOCK(str)函数检查名为str的锁是否正在被其他人使用（换言之，被封锁）。若被封锁，则返回使用该锁的客户端的连接标识符（connection ID），否则返回NULL。

【例4.104】使用加锁、解锁函数，输入语句如下：

        mysql> SELECT GET_LOCK('lock1',10) AS GetLock, IS_USED_LOCK('lock1') AS ISUsedLock,
     IS_FREE_LOCK('lock1') AS ISFreeLock, RELEASE_LOCK('lock1') AS ReleaseLock;
        +---------+------------+------------+-------------+
        | GetLock | ISUsedLock | ISFreeLock | ReleaseLock |
        +---------+------------+------------+-------------+
        |     1  |     23    |       0   |        1   |
        +---------+------------+------------+-------------+

GET_LOCK('lock1',10)返回结果为1，说明成功得到了一个名称为“lock1”的锁，持续时间为10秒。

IS_USED_LOCK('lock1')返回结果为当前连接ID，表示名称为“lock1”的锁正在被使用。

IS_FREE_LOCK('lock1')返回结果为0，说明名称为“lock1”的锁正在被使用。

RELEASE_LOCK('lock1')返回值为1，说明解锁成功。

4.8.5　重复执行指定操作的函数

BENCHMARK(count,expr)函数重复count次执行表达式expr。它可以用于计算MySQL处理表达式的速度。结果值通常为0（0只是表示处理过程很快，并不是没有花费时间）。另一个作用是它可以在MySQL客户端内部报告语句执行的时间。

【例4.105】使用BENCHMARK重复执行指定函数。

首先，使用PASSWORD函数加密密码，输入语句如下：

     mysql> SELECT SHA('newpwd');
     +------------------------------------------+
     | SHA('newpwd')                        |
     +------------------------------------------+
     | c7f005b657906521157aa3fc261afe886d51f792 |
     +------------------------------------------+
     1 row in set (0.00 sec)

可以看到，PASSWORD执行花费时间为0.00sec。下面使用BENCHMARK函数重复执行PASSWORD操作500000次：

     mysql> SELECT BENCHMARK(500000, SHA('newpwd'));
     +----------------------------------+
     | BENCHMARK(500000, SHA('newpwd')) |
     +----------------------------------+
     |                     0       |
     +----------------------------------+
     1 row in set (0.29 sec)

由此可以看出，使用BENCHMARK执行500000次的时间为0.29 sec，明显比执行一次的时间延长了。

提示： BENCHMARK报告的时间是客户端经过的时间，而不是在服务器端的CPU时间，每次执行后报告的时间并不一定相同。读者可以多次执行该语句进行验证。

4.8.6　改变字符集的函数

带有USING参数的CONVERT(...USING...)函数用来在不同的字符集之间转化数据。【例4.106】使用CONVERT()函数改变字符串的默认字符集，输入语句如下：

     MySQL> SELECT CHARSET('string'),CHARSET(CONVERT('string' USING latin1));
     +-------------------+-----------------------------------------+
     | CHARSET('string') | CHARSET(CONVERT('string' USING latin1)) |
     +-------------------+-----------------------------------------+
     | utf8mb4          | latin1                             |
     +-------------------+-----------------------------------------+

字符串“string”的字符集默认为utf8mb4，通过CONVERT将其默认字符集改为latin1。

4.8.7　改变数据类型的函数

CAST(x , AS type)和CONVERT(x, type)函数将一个类型的值转换为另一个类型的值，可转换的type有BINARY、CHAR(n)、DATE、TIME、DATETIME、DECIMAL、SIGNED、UNSIGNED。

【例4.107】使用CAST和CONVERT函数进行数据类型的转换，SQL语句如下：

     mysql> SELECT CAST(100 AS CHAR(2)), CONVERT('2018-10-01 12:12:12',TIME);
     +----------------------+-------------------------------------+
     | CAST(100 AS CHAR(2)) | CONVERT('2018-10-01 12:12:12',TIME) |
     +----------------------+-------------------------------------+
     | 10                | 12:12:12                        |
     +----------------------+-------------------------------------+

可以看到，CAST(100 AS CHAR(2))将整数数据100转换为带有两个显示宽度的字符串类型，结果为“10”；CONVERT('2018-10-01 12:12:12',TIME)将DATETIME类型的时间值转换为TIME类型，结果为“12:12:12”。 jhJfI/vHXGAsL1xS0VQMt6/wm+D3dnrLs6K9URmDb+YFdFhCrqWJPPJmfo5Xvae2