Other functions
(1)Formatting function format(x,n)
(2)Function for converting numbers in different bases
(3) Functions that convert IP addresses and numbers to each other
(4) Locking functions and unlocking functions
(5) Functions that repeatedly perform specified operations
(6) Functions that change character sets
( 7) Function to change data type
(free learning recommendation: mysql video tutorial)
format(x,n)Format the number x and round it to n digits after the decimal point. The result is returned in the form of a string. If n is 0, the returned result function does not contain the decimal part. 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 |+------------------------+-------------------+-------------------+1 row in set (0.00 sec)
conv(n,from_base,to_base)function Convert between different base numbers. If one parameter is null, the return value is null. [Example] Use the conv function to convert between different base values. The SQL statement is as follows:
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 |+----------------+---------------+---------------+----------------+1 row in set (0.01 sec)| base | Explanation |
|---|---|
| Binary | The number is represented by two numbers: 0 and 1, with 2 as the base, and every two is entered into one |
| Octal system | Uses eight digits from 0 to 7, every eight is entered as one, starting with the number 0 |
| Decimal system | Using ten digits from 0 to 9, each decimal number |
| Hexadecimal | is composed of 0-9, A-F, and is the same as the decimal system The corresponding relationship is: 0-9 corresponds to 0-9, A-F corresponds to 10-15, and the hexadecimal number starts with 0x |
1.inet_aton()
【例】使用inet_aton()函数将字符串网络点地址转换为数值网络地址,SQL语句如下:
209*256^3+207*256^2+224*256+40。mysql> select inet_aton('209.207.224.40');+-----------------------------+| inet_aton('209.207.224.40') |+-----------------------------+| 3520061480 |+-----------------------------+1 row in set (0.00 sec)2.inet_ntoa()
inet_ntoa(expr)给定一个数字网络地址(4bit或8bit)，返回作为字符串的该地址的点地址表示。【例】使用iner_ntoa函数将数值网络地址转换为字符串网络点地址，SQL语句如下：
mysql> select inet_ntoa(3520061480);+-----------------------+| inet_ntoa(3520061480) |+-----------------------+| 209.207.224.40 |+-----------------------+1 row in set (0.00 sec)
1.get_lock(str,timeout)设法使用字符串str给定的名字得到一个锁，超时为timeout秒。若成功得到锁，则返回1；若操作超时返回0；若发生错误，返回null。
假如有一个用get_lock()得到的锁，当执行release_lock()或链接断开(正常或非正常)时，这个锁就会解除。
2.release_lock(str)解开被get_lock()获取的，用字符串str所命名的锁。若锁被解开，则返回1；若该线程尚未创建锁，则返回0(此时锁没有被解开);若命名的锁不存在，则返回null。若该锁从未被get_lock()的调用获取，或锁已经被提前解开，则该锁不存在。
3.is_free_lock(str)检查名为str的锁是否可以使用(没有被封锁)。若锁可以使用，则返回1(没有人在使用这个锁);若这个锁正在被使用，则返回0；出现错误，则返回null(诸如不正确的参数)。
4.is_used_lock(str)检查名为str的锁是否正在被使用(被封锁)。若被封锁，则返回使用该锁的客户端的连接标识符(connectionID);否则，返回null。
【例】使用加锁、解锁函数，SQL语句如下：
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 | 21 | 0 | 1 |+---------+------------+------------+-------------+1 row in set (0.05 sec)- benchmark(count,expr)函数重复执行表达式(expr)count次。它可以用于计算MySQL处理表达式的速度。结果值通常为0，(0只是表示处理过程很快，并不是没有花时间)。另一个作用是它可以在MySQL客户端内部报告语句执行的时间。
【例】使用benchmark重复执行指定函数
mysql> select md5('Hudie');+----------------------------------+| md5('Hudie') |+----------------------------------+| 3fe2017e5cb984400c5271ef77a840f6 |+----------------------------------+1 row in set (0.00 sec)mysql> select benchmark(500000,md5('Hudie'));+--------------------------------+| benchmark(500000,md5('Hudie')) |+--------------------------------+| 0 |+--------------------------------+1 row in set (0.38 sec)注意：
benchmark报告的时间是客户端经过的时间，而不是在服务器端的CPU时间，每次执行后报告的时间并不一定是相同的。
convert(…using…)带有using的convert()函数被用来在不同的字符集之间转化数据。【例】使用convert()函数改变字符串的默认字符集，SQL语句如下;
mysql> select charset(' string '),charset( convert(' string ' using latin1 ) );+---------------------+----------------------------------------------+| charset(' string ') | charset( convert(' string ' using latin1 ) ) |+---------------------+----------------------------------------------+| gbk | latin1 |+---------------------+----------------------------------------------+1 row in set (0.00 sec)默认为gbk字符集，通过convert将字符串"strng"的默认字符集改为latin1。
- case(x,as type)和convert(x,type)函数将一个类型的值转换为另一个类型的值,可以转换的type值有binary、char(n)、date、time、datetime、decimal、signed、unsigned。
【例】使用case和convert函数进行数据类型的转换，SQL语句如下：
mysql> select cast(100 as char(2)),convert(' 2019-08-20 00:32:01 ',time);+----------------------+---------------------------------------+| cast(100 as char(2)) | convert(' 2019-08-20 00:32:01 ',time) |+----------------------+---------------------------------------+| 10 | 00:32:01 |+----------------------+---------------------------------------+1 row in set, 1 warning (0.05 sec)可以看到，case(100 as char(2))将整数数据类型100转换为带有两个显示宽度的字符串类型，结果为“10”；convert(‘2010-08-20 00:32:01’)将datetime类型的值转换为time类型，结果为00:32:01。
更多相关免费学习推荐：mysql教程(视频)
The above is the detailed content of MySQL introduces other functions. For more information, please follow other related articles on the PHP Chinese website!
![[Web front-end] Node.js quick start](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
