10 内置函数下

内置函数下

1. 数学函数

函数名称功能描述参数说明示例
ABS(number)计算绝对值number：任意数字。SELECT ABS(-10); → 10
BIN(number)转换为二进制number：需要转换的数字。SELECT BIN(10); → 1010
HEX(number)转换为十六进制number：需要转换的数字。SELECT HEX(255); → FF
CONV(number，from_base，to_base)进制转换number：要转换的数字, from_base：源进制, to_base：目标进制。SELECT CONV('A', 16, 10); → 10
CEILING(number)向上取整number：需要取整的数字。SELECT CEILING(4.3); → 5
FLOOR(number)向下取整number：需要取整的数字。SELECT FLOOR(4.7); → 4
FORMAT(number，decimals)格式化数字number：需要格式化的数字, decimals：保留的小数位数SELECT FORMAT(12345.6789, 2); → 12,345.68
RAND()生成随机数无参数，返回 0 到 1 之间的随机数，但也可以传入随机种子来控制随机数。SELECT RAND(); → 0.123456
MOD(dividend，divisor)计算余数dividend：被除数, divisor：除数。SELECT MOD(10, 3); → 1

因为这部分比较简单，和 C/C++的数学库使用非常相似，下面仅展现一些示例：

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
# 1. ABS：取绝对值（正数/0不变，负数变正）
mysql> select abs(-10);		# 10 （负数取绝对值）
mysql> select abs(0);		# 0   （0的绝对值还是0）
mysql> select abs(10);		# 10  （正数绝对值不变）

# 2. BIN：转二进制字符串（先转十进制整数，非数字返回0，小数取整，0x=十六进制、0开头=八进制）
mysql> select bin(10);		# 1010 （十进制10→二进制）
mysql> select bin(0xF);		# 1111 （十六进制0xF=十进制15→二进制）
mysql> select bin('10');	# 1010 （数字字符串'10'转十进制10→二进制）
mysql> select bin('abc');	# 0    （非数字字符串返回0）
mysql> select bin(6.6);		# 110  （小数6.6取整为6→二进制）
mysql> select bin(012);		# 1100 （012 是八进制，等于十进制 10）

# 3. HEX：转十六进制（数字直接转，字符串转ASCII码的十六进制）
mysql> select hex(1100);	# 44C  （十进制1100→十六进制）
mysql> select hex('1100');	# 31313030 （字符串'1100'：1=31、0=30，拼接成十六进制）
mysql> select hex(10);		# A    （十进制10→十六进制A）
mysql> select hex('abc');	# 616263 （字符串'abc'：a=61、b=62、c=63，拼接成十六进制）

# 4. CONV：进制转换（conv(数值,原进制,目标进制)）
mysql> select conv(10,10,16);	# A    （十进制10→十六进制）
mysql> select conv(10,2,16);	# 2    （二进制10→十六进制）
mysql> select conv(10,8,16);	# 8    （八进制10→十六进制）
mysql> select conv(11,3,16);	# 4    （三进制11→十六进制）

# 5. CEILING：向上取整（向大的整数靠，负数也遵循）
mysql> select ceiling(3.5);		# 4    （3.5向上取整为4）
mysql> select ceiling(3);		# 3    （整数不变）
mysql> select ceiling(0.1);		# 1    （0.1向上取整为1）
mysql> select ceiling(-0.1);	# 0    （-0.1向上取整为0）
mysql> select ceiling(-2.1);	# -2   （-2.1向上取整为-2）

# 6. FLOOR：向下取整（向小的整数靠，负数也遵循）
mysql> select floor(2.1);		# 2    （2.1向下取整为2）
mysql> select floor(-2.1);		# -3   （-2.1向下取整为-3）
mysql> select floor(-2.9);		# -3   （-2.9向下取整为-3）
mysql> select floor(2.9);		# 2    （2.9向下取整为2）

# 7. FORMAT：格式化数字（保留指定小数位，千分位分隔，四舍五入）
mysql> select format(4154.16516,2);		# 4,154.17 （保留2位小数，四舍五入，加千分位）
mysql> select format(4154,2);			# 4,154.00 （整数补2位小数，加千分位）
mysql> select format(-44.4564,2);		# -44.46   （负数保留2位小数，四舍五入）

# 8. RAND：生成0-1随机浮点数（不传参=真随机，传种子=固定随机值）
mysql> select rand();			# 0.7702155623307596 （无种子，每次结果不同）
mysql> select rand();			# 0.24202322609911042 （无种子，结果变化）
mysql> select rand(10),rand(10);		# 两个rand(10)均返回0.6570515219653505（传相同种子，结果固定）

# 9. MOD：取模（求余数，符号与被除数一致）
mysql> select mod(9,3);			# 0    （9÷3余数0）
mysql> select mod(10,3);		# 1    （10÷3余数1）
mysql> select mod(-10,3);		# -1   （-10÷3余数-1，符号和被除数-10一致）

2. 其他函数

1. USER() —— 当前连接用户

查看当前用什么 MySQL 用户 + 从哪连进来的。

1
2
3
4
5
6
mysql> select user();
+----------------+
| user()         |
+----------------+
| root@localhost |	# root：mysql用户，localhost：来源主机
+----------------+

注意：这和 Linux 用户 没关系，是 MySQL 登录用户。

2. DATABASE() —— 当前使用的数据库

查看当前正在使用的是哪个数据库。

1
2
3
4
5
6
mysql> select database();
+------------+
| database() |
+------------+
| test2      |
+------------+

注意：需要先 USE 数据库名称 才有值！

3. MD5() —— 常见哈希函数（重点）

把字符串变成固定长度的不可逆摘要，特点：快速、不可逆（算不回原文）、相同输入 → 相同输出、输出长度固定（32 位十六进制）。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
MD5(str)	# MD5函数处理数字和数字字符串时，会先把数字自动转成对应的字符串，再计算哈希值，所以123456（数字）和'123456'（字符串）最终结果一样！
mysql> select md5(123456);
+----------------------------------+
| md5(123456)                      |
+----------------------------------+
| e10adc3949ba59abbe56e057f20f883e |
+----------------------------------+

mysql> select md5('123456');
+----------------------------------+
| md5('123456')                    |
+----------------------------------+
| e10adc3949ba59abbe56e057f20f883e |
+----------------------------------+

  • 一般情况下公司内部数据库不会存储用户的明文密码，而会将用户密码形成摘要后存储对应的摘要，当用户登录账号时，将用户输入的的密码形成摘要后与数据库中存储的摘要做对比，如果对比成功则允许登录。

  • 这么做的好处主要有两个，第一个好处就是公司内部数据库中存储的不是用户的明文信息，就算用户信息泄露了也不会产生太大影响，第二个好处就是形成的摘要是定长的，这样有利于数据库表结构的设计。

那么到底推不推荐把用户密码用 md5 形成摘要进行存储？

答案是 不推荐 的，因为 MD5 计算速度过快且已被破解，容易遭受暴力破解、彩虹表和字典等攻击，现代会更推荐适用 bcrypt、scrypt、argon2 等专门的密码哈希算法。

  • 上面提到 md5 的特点：快速，现如今计算机发展迅速，基本上每一代都是一个质的提升，CPU/GPU 每秒可算几十亿次 MD5。这意味着什么？一旦数据库泄露，攻击者可以用显卡，暴力枚举 + 字典攻击，很快撞出大量真实密码，对密码来说：快 = 不安全。

  • MD5 已被彻底破解（不是“理论不安全”）：已被证明存在 碰撞、已被收录进 彩虹表，常见弱密码（123456、password）秒出，攻击者甚至不需要算，查表就行。

  • 上面也提到：摘要定长，利于表结构设计，但是 定长不等于安全，所以这也是诸多哈希算法的一个折中缺陷选择，为什么说折中呢，是因为如果不定长涉及算法的加密解密难度、速度，定长又存在一点点安全隐患，虽然足够但并非主要，所以才刻意降低计算速度和引入可调计算成本来提升安全性。

关于字典就不多说了，了解 python 应该会熟悉一点，和 彩虹表 也是大同小异，下面简单了解一下彩虹表：我的理解是：一种经过特殊压缩优化文件，里面存储的是哈希值和明文密码的映射表，具有哈希表一样的高效查询功能，能够高效破解哈希加密的密码。

4. PASSWORD() —— 加密但不再使用（重点）

password 函数用于 给 MySQL 用户密码加密，以前用来生成 MySQL 密码的哈希值，比如：

1
2
3
4
5
6
mysql> select password('abc@123ABCxxx456');
+-------------------------------------------+
| password('abc@123ABCxxx456')               |
+-------------------------------------------+
| *38E2FF2391EFC14D9D5BB9CF939252E3994CFDE2 |
+-------------------------------------------+

但 MySQL 8 已经废弃 PASSWORD() 函数，这个函数：已经不推荐使用，甚至可能不存在，属于官方的安全机制升级，现在更推荐的方式是：

1
2
3
4
5
6
7
8
9
10
11
12
13
# 创建新用户（8.0 推荐写法）
# 'test'@'%' = 用户名 test + 允许全世界任何地方登录
# 'user'@'localhost' = 用户名 user + 只允许本机登录
CREATE USER 'test'@'%' IDENTIFIED BY '你的明文密码';
alter user 'user'@'localhost' Identified by '123456';

# 修改密码
ALTER USER 'test'@'%' IDENTIFIED BY '新密码';
#改密码 + 指定老方式（如果连接工具不支持新加密）
ALTER USER 'test'@'%' IDENTIFIED WITH mysql_native_password BY '123456';

# 或者用更现代的哈希函数
SELECT SHA2('我的密码', 256);

5. IFNULL() 函数

作用：如果第一个值是 NULL，就返回第二个值；否则返回第一个值，即把 NULL 替换成自己想要的默认值。

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
# 基本使用
SELECT IFNULL(列名, 0) FROM 表;      # 数字空 → 0
SELECT IFNULL(列名, '') FROM 表;      # 字符串空 → 空字符串
SELECT IFNULL(列名, '未知') FROM 表;      # 显示"未知"

mysql> select ifnull(null,0);
+----------------+
| ifnull(null,0) |
+----------------+
|              0 |
+----------------+

mysql> select ifnull(null,null);
+-------------------+
| ifnull(null,null) |
+-------------------+
|              NULL |
+-------------------+

mysql> select ifnull('abc','b');
+-------------------+
| ifnull('abc','b') |
+-------------------+
| abc               |
+-------------------+

mysql> select ifnull(null,'abc');
+--------------------+
| ifnull(null,'abc') |
+--------------------+
| abc                |
+--------------------+

6. 进阶扩展函数（了解，用的时候查）

控制流 / 空值处理（高频）

  • COALESCE() → 返回第一个非 NULL 值
  • NULLIF(expr1, expr2) → 相等则返回 NULL，否则返回 expr1

系统信息（调试/日志常用）

  • VERSION()
  • CONNECTION_ID()
  • LAST_INSERT_ID()

类型转换 / 比较（很实用）

  • CAST(expr AS type)
  • CONVERT(expr, type)
  • GREATEST() / LEAST() → 多值取最大/最小

JSON 函数

  • JSON_EXTRACT() / -> / ->>
  • JSON_ARRAY() / JSON_OBJECT()
  • JSON_CONTAINS() / JSON_SEARCH()
  • JSON_TABLE()（把 JSON 转成行，非常强）

窗口函数（分析查询神器，MySQL 8 新增）

  • ROW_NUMBER()
  • RANK() / DENSE_RANK()
  • NTILE()
  • LAG() / LEAD()
  • FIRST_VALUE() / LAST_VALUE()

其他高频杂项

  • FIND_IN_SET(str, list) → 检查字符串是否在逗号分隔列表中
  • FIELD(str, str1, str2, …) → 返回位置索引
  • RAND() → 随机排序/抽样
  • UUID() / UUID_SHORT() → 生成唯一 ID
  • BENCHMARK(count, expr) → 性能测试