1、数学函数

名称 描述
CEIL() 进一取整
FLOOR() 舍一取整
MOD 取余数（取模）
POWER() 幂运算
ROUND() 四舍五入
TRUNCATE() 数字截取
ABS() 取绝对值
PI() 圆周率
RAND()和RAND(X) 返回0-1之间的随机数，RAND(X)X相同时返回的随机数相同
SIGN(X) 返回X的符号，X为负数、0、正数分别返回-1、0、1
EXP(X) 计算e的几次方 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
mysql> SELECT CEIL(1.2),CEILING(1.2);
+-----------+--------------+
| CEIL(1.2) | CEILING(1.2) |
+-----------+--------------+
|         2 |            2 |
+-----------+--------------+
1 row in set (0.00 sec)

mysql> SELECT FLOOR(3.14);
+-------------+
| FLOOR(3.14) |
+-------------+
|           3 |
+-------------+
1 row in set (0.00 sec)

mysql> SELECT MOD(3,8);
+----------+
| MOD(3,8) |
+----------+
|        3 |
+----------+
1 row in set (0.00 sec)

mysql> SELECT POW(2,3),POWER(3,3);
+----------+------------+
| POW(2,3) | POWER(3,3) |
+----------+------------+
|        8 |         27 |
+----------+------------+
1 row in set (0.01 sec)

mysql> SELECT ROUND(3.14567,2);
+------------------+
| ROUND(3.14567,2) |
+------------------+
|             3.15 |
+------------------+
1 row in set (0.00 sec)

mysql> SELECT TRUNCATE(3.14567,2);
+---------------------+
| TRUNCATE(3.14567,2) |
+---------------------+
|                3.14 |
+---------------------+
1 row in set (0.00 sec)

mysql> SELECT ABS(-12);
+----------+
| ABS(-12) |
+----------+
|       12 |
+----------+
1 row in set (0.00 sec)

mysql> SELECT PI();
+----------+
| PI()     |
+----------+
| 3.141593 |
+----------+
1 row in set (0.00 sec)

mysql> SELECT RAND();
+---------------------+
| RAND()              |
+---------------------+
| 0.09555218936461228 |
+---------------------+
1 row in set (0.00 sec)

mysql> SELECT RAND(1); #这样得到的数是一样的
+---------------------+
| RAND(1)             |
+---------------------+
| 0.40540353712197724 |
+---------------------+
1 row in set (0.00 sec)

mysql> SELECT SIGN(12),SIGN(0),SIGN(-12);
+----------+---------+-----------+
| SIGN(12) | SIGN(0) | SIGN(-12) |
+----------+---------+-----------+
|        1 |       0 |        -1 |
+----------+---------+-----------+
1 row in set (0.00 sec)

mysql> SELECT EXP(3);
+--------------------+
| EXP(3)             |
+--------------------+
| 20.085536923187668 |
+--------------------+
1 row in set (0.00 sec)

2、字符串函数

名称 描述
CHAR_LENGTH(S) 返回字符串的字符数
LENGTH 返回字符串的长度
CONCAT(S1,S2…) 将字符串合并为一个字符串
CONCAT_WS(X1,S1,S2…) 以指定分隔符连接字符串
UPPER(S)/UCASE(S) 将字符串转为大写
LOWER(S)/LCASE(S) 将字符串转为小写
LEFT(S,N)/RIGHT(S,N) 返回字符串的前/后n个字符
LPAD(S1,LEN,S2)/RPAD(S1,LEN,S2) 将字符串S1用S2填充到指定的LEN
LTRIM(S)/RTRIM(S)/TRIM(S) 去掉字符串中的空格
TRIM(S1 FROM S) 去掉字符串s中开始处和结尾处的字符串s1
REPEAT(S,N) 重复字符串指定次数
SPACE(N) 返回N个空格
REPLACE(S,S1,S2) 将字符串S中搜索S1替换成S2
STRCMP(S1,S2) 比较字符串，>=<分别返回1，0，-1
SUBSTRING(S,N,LEN) 截取字符串
REVERSE(S) 反转字符串
ELT(N,S1,S2…) 返回指定位置的字符串 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
mysql> SELECT CHAR_LENGTH('test'),LENGTH('test');
+---------------------+----------------+
| CHAR_LENGTH('test') | LENGTH('test') |
+---------------------+----------------+
|                   4 |              4 |
+---------------------+----------------+
1 row in set (0.00 sec)

mysql> SELECT CHAR_LENGTH('嘿'),LENGTH('嘿');
+--------------------+---------------+
| CHAR_LENGTH('嘿')  | LENGTH('嘿')  |
+--------------------+---------------+
|                  1 |             3 |
+--------------------+---------------+
1 row in set (0.00 sec)

需要注意的是在UTF8下一个汉字的长度为3，但字符数为1。
而CONCAT是将两个字符串拼接： 

1
2
3
4
5
6
7
mysql> SELECT CONCAT('HELLO','WORLD');
+-------------------------+
| CONCAT('HELLO','WORLD') |
+-------------------------+
| HELLOWORLD              |
+-------------------------+
1 row in set (0.00 sec)

例如将用户名后面拼接下划线：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
mysql> SELECT id,CONCAT(username,'_')FROM student;
+----+----------------------+
| id | CONCAT(username,'_') |
+----+----------------------+
|  1 | king_                |
|  2 | king1_               |
|  3 | king2_               |
|  4 | king3_               |
|  5 | king4_               |
|  6 | king5_               |
|  7 | king6_               |
|  8 | king7_               |
|  9 | king8_               |
+----+----------------------+
9 rows in set (0.00 sec)

注意：当拼接的字符中有null时，结果为 NULL：

1
2
3
4
5
6
7
mysql> SELECT CONCAT('a','b',null);
+----------------------+
| CONCAT('a','b',null) |
+----------------------+
| NULL                 |
+----------------------+
1 row in set (0.00 sec)

CONCAT_WS是以特定的字符串连接字符：

1
2
3
4
5
6
7
mysql> SELECT CONCAT_WS('-!-','a','b','c');
+------------------------------+
| CONCAT_WS('-!-','a','b','c') |
+------------------------------+
| a-!-b-!-c                    |
+------------------------------+
1 row in set (0.00 sec)

注意：当拼接的字符中有null时，结果为 NULL，但是如果是连接的内容中有null是结果不为NULL。
UPPER|UCASE和LOWER|UCASE，大小写转化：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
mysql> SELECT UPPER('this is a test'),UCASE('this is a test');
+-------------------------+-------------------------+
| UPPER('this is a test') | UCASE('this is a test') |
+-------------------------+-------------------------+
| THIS IS A TEST          | THIS IS A TEST          |
+-------------------------+-------------------------+
1 row in set (0.00 sec)

mysql> SELECT LOWER('HELLO WORLD'),LCASE('HELLO WORLD');
+----------------------+----------------------+
| LOWER('HELLO WORLD') | LCASE('HELLO WORLD') |
+----------------------+----------------------+
| hello world          | hello world          |
+----------------------+----------------------+
1 row in set (0.00 sec)

通过LEFT和RIGHT返回字符串的前几个或后几个字符：

1
2
3
4
5
6
7
mysql> SELECT LEFT('ABCDEF',2),RIGHT('ABCDEF',2);
+------------------+-------------------+
| LEFT('ABCDEF',2) | RIGHT('ABCDEF',2) |
+------------------+-------------------+
| AB               | EF                |
+------------------+-------------------+
1 row in set (0.00 sec)

通过LPAD或RPAD在指定位置填充字符：

1
2
3
4
5
6
7
mysql> SELECT LPAD('A',5,'?'),RPAD('A',5,'!'); # 填充到5个字符
+-----------------+-----------------+
| LPAD('A',5,'?') | RPAD('A',5,'!') |
+-----------------+-----------------+
| ????A           | A!!!!           |
+-----------------+-----------------+
1 row in set (0.00 sec)

通过TRIM()、LTRIM()、RTRIM()来去除空格、左空格、右空格

1
2
3
4
5
6
7
mysql> SELECT CONCAT('_',TRIM(' ABC '),'_'),CONCAT('_',LTRIM(' ABC '),'_'),CONCAT('_',RTRIM(' ABC '),'_');
+-------------------------------+--------------------------------+--------------------------------+
| CONCAT('_',TRIM(' ABC '),'_') | CONCAT('_',LTRIM(' ABC '),'_') | CONCAT('_',RTRIM(' ABC '),'_') |
+-------------------------------+--------------------------------+--------------------------------+
| _ABC_                         | _ABC _                         | _ ABC_                         |
+-------------------------------+--------------------------------+--------------------------------+
1 row in set (0.00 sec)

可以通过TRIM(S1 FROM S2)和REPEAT()来去除指定字符和重复指定字符：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
mysql> SELECT TRIM('A' FROM 'ABCBCA');
+-------------------------+
| TRIM('A' FROM 'ABCBCA') |
+-------------------------+
| BCBC                    |
+-------------------------+
1 row in set (0.00 sec)

mysql> SELECT REPEAT('H',5);
+---------------+
| REPEAT('H',5) |
+---------------+
| HHHHH         |
+---------------+
1 row in set (0.00 sec)
```	
`SPACE(n)`返回n分空格：

```SQL
mysql> SELECT CONCAT('_',SPACE(5),'_');
+--------------------------+
| CONCAT('_',SPACE(5),'_') |
+--------------------------+
| _     _                  |
+--------------------------+
1 row in set (0.00 sec)

REPLACE实现字符替换：

1
2
3
4
5
6
7
mysql> SELECT REPLACE('ABCBCA','A','_'); # 区分大小写
+---------------------------+
| REPLACE('ABCBCA','A','_') |
+---------------------------+
| _BCBC_                    |
+---------------------------+
1 row in set (0.00 sec)

STRCMP比较大小写：

1
2
3
4
5
6
7
mysql> SELECT STRCMP('A','A'),STRCMP('A','a'),STRCMP('B','A'); # 不区分大小写
+-----------------+-----------------+-----------------+
| STRCMP('A','A') | STRCMP('A','a') | STRCMP('B','A') |
+-----------------+-----------------+-----------------+
|               0 |               0 |               1 |
+-----------------+-----------------+-----------------+
1 row in set (0.00 sec)

SUBSTRING字符串截取：

1
2
3
4
5
6
7
mysql> SELECT SUBSTRING('ABCDEF',2,2);
+-------------------------+
| SUBSTRING('ABCDEF',2,2) |
+-------------------------+
| BC                      |
+-------------------------+
1 row in set (0.00 sec)

代表从第2个字符开始截取2位，字符串下标从1开始。
REVERSE实现字符串翻转：

1
2
3
4
5
6
7
mysql> SELECT REVERSE('ABC');
+----------------+
| REVERSE('ABC') |
+----------------+
| CBA            |
+----------------+
1 row in set (0.00 sec)

ELT返回指定位置的字符：

1
2
3
4
5
6
7
mysql> SELECT ELT(2,'A','B','C');
+--------------------+
| ELT(2,'A','B','C') |
+--------------------+
| B                  |
+--------------------+
1 row in set (0.00 sec)

3、日期时间函数

名称 描述
CURDATE(),CURRENT_DATE() 返回当前日期
CURTIME(),CURRENT_TIME() 返回当前时间
NOW() 返回当前日期和时间
MONTH(D) 返回日期中月份的值
MINTHNAME(D) 返回日期中月份名称，如：January
DAYNAME(D) 返回日期是几，如：Monday
DAYOFWEEK(D) 返回一周内的第几天，1代表星期日
WEEKDAY(D) 返回日期是星期几，0代表星期一
WEEK(D) 一年中的第几个星期
YEAR(D) 返回年份值
HOUR(T) 返回小时值
MINUTE(T) 返回分钟值
SECOND(T) 返回秒数
DATEDIFF(D1,D2) 计算两个日期之间相隔的天数 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
mysql> SELECT CURDATE(),CURRENT_DATE();
+------------+----------------+
| CURDATE()  | CURRENT_DATE() |
+------------+----------------+
| 2017-10-12 | 2017-10-12     |
+------------+----------------+
1 row in set (0.00 sec)

mysql> SELECT CURTIME(),CURRENT_TIME();
+-----------+----------------+
| CURTIME() | CURRENT_TIME() |
+-----------+----------------+
| 17:32:27  | 17:32:27       |
+-----------+----------------+
1 row in set (0.00 sec)

mysql> SELECT NOW();
+---------------------+
| NOW()               |
+---------------------+
| 2017-10-12 17:32:27 |
+---------------------+
1 row in set (0.00 sec)

mysql> SELECT MONTH('2015-1-3');
+-------------------+
| MONTH('2015-1-3') |
+-------------------+
|                 1 |
+-------------------+
1 row in set (0.00 sec)

mysql> SELECT MONTH(NOW());
+--------------+
| MONTH(NOW()) |
+--------------+
|           10 |
+--------------+
1 row in set (0.00 sec)

mysql> SELECT MONTHNAME(NOW());
+------------------+
| MONTHNAME(NOW()) |
+------------------+
| October          |
+------------------+
1 row in set (0.01 sec)

mysql> SELECT DAYNAME(NOW());
+----------------+
| DAYNAME(NOW()) |
+----------------+
| Thursday       |
+----------------+
1 row in set (0.00 sec)

mysql> SELECT DAYOFWEEK(NOW());
+------------------+
| DAYOFWEEK(NOW()) |
+------------------+
|                5 |
+------------------+
1 row in set (0.00 sec)

mysql> SELECT WEEKDAY(NOW());
+----------------+
| WEEKDAY(NOW()) |
+----------------+
|              3 |
+----------------+
1 row in set (0.00 sec)

mysql> SELECT WEEK(NOW());
+-------------+
| WEEK(NOW()) |
+-------------+
|          41 |
+-------------+
1 row in set (0.00 sec)

mysql> SELECT YEAR(NOW());
+-------------+
| YEAR(NOW()) |
+-------------+
|        2017 |
+-------------+
1 row in set (0.00 sec)

mysql> SELECT HOUR(NOW());
+-------------+
| HOUR(NOW()) |
+-------------+
|          17 |
+-------------+
1 row in set (0.00 sec)

mysql> SELECT MINUTE(NOW());
+---------------+
| MINUTE(NOW()) |
+---------------+
|            32 |
+---------------+
1 row in set (0.00 sec)

mysql> SELECT SECOND(NOW());
+---------------+
| SECOND(NOW()) |
+---------------+
|            27 |
+---------------+
1 row in set (0.00 sec)

mysql> SELECT DATEDIFF(CURRENT_DATE(),'1990-1-1');
+-------------------------------------+
| DATEDIFF(CURRENT_DATE(),'1990-1-1') |
+-------------------------------------+
|                               10146 |
+-------------------------------------+
1 row in set (0.00 sec)

4、条件判断函数

名称 描述
IF(EXPR,V1,V2) 如果表达式EXPR成立，返回结果V1，否则V2
IFNULL(V1,V2) 如果V1的不为空就显示V1的值，否则V2
CASE WHEN exp1 THEN v1[WHEN exp2 THEN v2][ELSE vn] END CASE表示函数开始，END表示函数介绍。如果表达式exp1成立时返回v1，如果表达式exp2成立时返回v2的值，以此类推，最后玉带ELSE时返回vn

IF(EXPR,V1,V2)以学员表为例，分数大于60输出及格，否则输出不及格：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
mysql> SELECT id,username,score,IF(score>=60,'及格','不及格') AS ’输出‘ FROM student;
+----+----------+-------+--------------+
| id | username | score | ’输出‘       |
+----+----------+-------+--------------+
|  1 | king     |    95 | 及格         |
|  2 | king1    |    35 | 不及格       |
|  3 | king2    |    45 | 不及格       |
|  4 | king3    |    55 | 不及格       |
|  5 | king4    |    65 | 及格         |
|  6 | king5    |    75 | 及格         |
|  7 | king6    |    80 | 及格         |
|  8 | king7    |    90 | 及格         |
|  9 | king8    |    25 | 不及格       |
+----+----------+-------+--------------+
9 rows in set (0.01 sec)

IFNULL(V1,V2)，在cms_user表中学员id为12的用户年龄为NULL，测试一下显示为100：

1
2
3
4
5
6
7
8
9
mysql> SELECT id,username,age,IFNULL(age,'100')FROM cms_user WHERE id>10;# WHERE 条件为了少显示一部分数据
+----+----------+------+-------------------+
| id | username | age  | IFNULL(age,'100') |
+----+----------+------+-------------------+
| 11 | john     |   65 | 65                |
| 12 | test1    | NULL | 100               |
| 13 | TEST2    |   18 | 18                |
+----+----------+------+-------------------+
3 rows in set (0.00 sec)
1
2
CASE WHEN exp1 THEN v1[WHEN exp2 THEN v2][ELSE vn] END
# CASE表示开始，END表示结束，exp1为真，返回v1，否则exp2，为真返回v2，否则返回vn

例如：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
mysql> SELECT id,username,score, CASE WHEN score>60 THEN '不错' WHEN score=60 THEN '刚及格' ELSE '没及格' END FROM student;
+----+----------+-------+--------------------------------------------------------------------------------------+
| id | username | score | CASE WHEN score>60 THEN '不错' WHEN score=60 THEN '刚及格' ELSE '没及格' END         |
+----+----------+-------+--------------------------------------------------------------------------------------+
|  1 | king     |    95 | 不错                                                                                 |
|  2 | king1    |    35 | 没及格                                                                               |
|  3 | king2    |    45 | 没及格                                                                               |
|  4 | king3    |    55 | 没及格                                                                               |
|  5 | king4    |    65 | 不错                                                                                 |
|  6 | king5    |    75 | 不错                                                                                 |
|  7 | king6    |    80 | 不错                                                                                 |
|  8 | king7    |    90 | 不错                                                                                 |
|  9 | king8    |    25 | 没及格                                                                               |
+----+----------+-------+--------------------------------------------------------------------------------------+
9 rows in set (0.00 sec)

5、系统信息函数

名称 描述
VERSION() 返回数据库的版本号
CONNECTION_ID 返回服务器的连接数
DATABASE(),SCHEMA() 返回当前数据库名
USER(),SYSTEM_USER() 返回当前用户
CURRENT_USER(),CURRENT_USER 返回当前用户
CHARSET(STR) 返回字符串STR的字符集
COLLATION(STR) 返回字符串STR的校验字符集
LAST_INSERT_ID() 返回最近生成的AUTO_INCREMENT值 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
mysql> SELECT VERSION();
+-----------+
| VERSION() |
+-----------+
| 5.7.19    |
+-----------+
1 row in set (0.00 sec)

mysql> SELECT CONNECTION_ID();
+-----------------+
| CONNECTION_ID() |
+-----------------+
|              12 |
+-----------------+
1 row in set (0.00 sec)

mysql> SELECT DATABASE(),SCHEMA();
+------------+----------+
| DATABASE() | SCHEMA() |
+------------+----------+
| cms        | cms      |
+------------+----------+
1 row in set (0.00 sec)

mysql> SELECT USER(),SYSTEM_USER();
+----------------+----------------+
| USER()         | SYSTEM_USER()  |
+----------------+----------------+
| root@localhost | root@localhost |
+----------------+----------------+
1 row in set (0.00 sec)

mysql> SELECT CURRENT_USER(),CURRENT_USER;
+----------------+----------------+
| CURRENT_USER() | CURRENT_USER   |
+----------------+----------------+
| root@localhost | root@localhost |
+----------------+----------------+
1 row in set (0.00 sec)

mysql> SELECT CHARSET('AAA');
+----------------+
| CHARSET('AAA') |
+----------------+
| utf8           |
+----------------+
1 row in set (0.00 sec)

mysql> SELECT COLLATION('AAA');
+------------------+
| COLLATION('AAA') |
+------------------+
| utf8_general_ci  |
+------------------+
1 row in set (0.00 sec)

mysql> SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
|                0 |
+------------------+
1 row in set (0.00 sec)

6、加密函数

名称 描述
MD5(str) 信息摘要算法
PASSWORD(str) 密码算法
ENCODE(str,pwd_str) 加密结果是一二进制数，必须使用BLOB类型字段保存
DECODE(crypt_str,pwd_str) 对通过ENCODE加密之后的内容解密

MD5(str)加密后会返回32位的字符串：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
mysql> SELECT MD5('ADMIN');
+----------------------------------+
| MD5('ADMIN')                     |
+----------------------------------+
| 73acd9a5972130b75066c82595a1fae3 |
+----------------------------------+
1 row in set (0.00 sec)

mysql> SELECT LENGTH(MD5('ADMIN'));
+----------------------+
| LENGTH(MD5('ADMIN')) |
+----------------------+
|                   32 |
+----------------------+
1 row in set (0.00 sec)

PASSWORD(str)主要是对密码进行加密：

1
2
3
4
5
6
7
mysql> SELECT PASSWORD('root');
+-------------------------------------------+
| PASSWORD('root')                          |
+-------------------------------------------+
| *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B |
+-------------------------------------------+
1 row in set, 1 warning (0.00 sec)

剩下两种用的少，暂不了解

7、其他常用函数

名称 描述
FORMAT(x,n) 将数字x进行格式化，将x保留到小数点后n位
ASCLL(s) 返回字符串s的第一个字符的ASCLL码
BIN(x) 返回x的二进制编码
HEX(x) 返回x的十六进制编码
OCT(x) 返回x的八进制编码
CONV(x,f1,f2) 将x从f1进制数变成f2进制数
INET_ATON(IP) 将IP地址转化为数字
INET_NTOA(n) 将数值转化成IP地址
GET_LOCT(name,time) 定义锁
RELEASE_LOCK(name) 解锁

FORMAT(x,n)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
mysql> SELECT FORMAT(3.14567,2);
+-------------------+
| FORMAT(3.14567,2) |
+-------------------+
| 3.15              |
+-------------------+
1 row in set (0.00 sec)

mysql> SELECT ASCII('abc');
+--------------+
| ASCII('abc') |
+--------------+
|           97 |
+--------------+
1 row in set (0.02 sec)

mysql> SELECT BIN(5),HEX(5),OCT(5);
+--------+--------+--------+
| BIN(5) | HEX(5) | OCT(5) |
+--------+--------+--------+
| 101    | 5      | 5      |
+--------+--------+--------+
1 row in set (0.02 sec)

mysql> SELECT CONV(5,10,2);
+--------------+
| CONV(5,10,2) |
+--------------+
| 101          |
+--------------+
1 row in set (0.00 sec)

mysql> SELECT INET_ATON('127.0.0.1');
+------------------------+
| INET_ATON('127.0.0.1') |
+------------------------+
|             2130706433 |
+------------------------+
1 row in set (0.06 sec)

mysql> SELECT INET_NTOA(2130706433);
+-----------------------+
| INET_NTOA(2130706433) |
+-----------------------+
| 127.0.0.1             |
+-----------------------+
1 row in set (0.02 sec)

mysql> SELECT GET_LOCK('KING',10);
+---------------------+
| GET_LOCK('KING',10) |
+---------------------+
|                   1 |
+---------------------+
1 row in set (0.05 sec)

mysql> SELECT IS_FREE_LOCK('KING');
+----------------------+
| IS_FREE_LOCK('KING') |
+----------------------+
|                    0 |
+----------------------+
1 row in set (0.00 sec)

mysql> SELECT RELEASE_LOCK('KING');
+----------------------+
| RELEASE_LOCK('KING') |
+----------------------+
|                    1 |
+----------------------+
1 row in set (0.00 sec)