数值函数

导学

数值函数让你在 SQL 中直接完成数学运算，无需将数据拉回程序层。本节梳理 MySQL 5.7 中最常用的数值函数，包括取整、随机、幂运算和进制转换。

定义

数值函数：对数值型数据进行数学运算并返回结果的函数。MySQL 5.7 提供算术运算、取整、随机、三角函数、对数、进制转换等数十个数值函数。

常用数值函数

取整函数

ROUND、CEIL、FLOOR、TRUNCATE

当前数据状态：

以下用常量演示取整函数的效果：

SELECT
    3.14159 AS value,
    ROUND(3.14159, 2) AS round_2,
    ROUND(3.14159) AS round_int,
    CEIL(3.14159) AS ceil_val,
    FLOOR(3.14159) AS floor_val,
    TRUNCATE(3.14159, 2) AS truncate_2
UNION ALL
SELECT
    -2.71828,
    ROUND(-2.71828, 2),
    ROUND(-2.71828),
    CEIL(-2.71828),
    FLOOR(-2.71828),
    TRUNCATE(-2.71828, 2);

操作后的数据状态：

结果解读：

• ROUND(value, 2)：四舍五入到 2 位小数，3.14159 变为 3.14
• ROUND(value)：四舍五入到整数，采用"四舍六入五成双"规则
• CEIL(value)：向上取整，3.14159 变为 4
• FLOOR(value)：向下取整，3.14159 变为 3
• TRUNCATE(value, 2)：直接截断到 2 位小数，不进行四舍五入，3.14159 变为 3.14

Banker's Rounding 演示

SELECT
    ROUND(2.5) AS round_2_5,
    ROUND(3.5) AS round_3_5,
    ROUND(4.5) AS round_4_5;

结果：

结果解读：MySQL 的 ROUND 采用"四舍六入五成双"（Banker's Rounding）：当舍弃部分恰好为 0.5 时，向最近的偶数舍入。ROUND(2.5) = 2（2 是偶数），ROUND(3.5) = 4（4 是偶数）。

随机函数

RAND

当前数据状态：

CREATE TABLE employees (
    emp_id INT PRIMARY KEY AUTO_INCREMENT,
    emp_name VARCHAR(20),
    dept VARCHAR(20),
    score DECIMAL(5,2)
);

INSERT INTO employees (emp_name, dept, score) VALUES
('大翔', '技术部', 100),
('白歌', '技术部', NULL);

当前表数据：

操作语句：

-- 生成 0 ~ 1 之间的随机浮点数
SELECT RAND() AS random_float;

-- 生成 0 ~ 99 之间的随机整数
SELECT FLOOR(RAND() * 100) AS random_int_0_99;

-- 随机抽取 1 条记录
SELECT * FROM employees ORDER BY RAND() LIMIT 1;

操作后的数据状态（随机结果每次不同，以下为示例）：

随机抽取结果示例：

结果解读：

• RAND() 生成 [0, 1) 范围内的随机浮点数
• FLOOR(RAND() * 100) 生成 0 ~ 99 的随机整数
• ORDER BY RAND() LIMIT 1 随机排序后取前 1 条

性能警告：ORDER BY RAND() 需要为每行生成随机数并全排序，大数据量时极慢。替代方案：在程序层生成随机 ID 后用 WHERE id IN (...) 查询。

绝对值与符号

当前数据状态：

以下用常量演示绝对值和符号函数：

SELECT
    100.00 AS amount,
    ABS(100.00) AS abs_amount,
    SIGN(100.00) AS sign_val
UNION ALL
SELECT
    -50.50,
    ABS(-50.50),
    SIGN(-50.50);

操作后的数据状态：

结果解读：

• ABS(amount) 返回绝对值，负数转为正数
• SIGN(amount) 返回符号：正数为 1，负数为 -1，零为 0

幂与根

当前数据状态：

以下用常量演示幂与根函数：

SELECT
    2.00 AS base,
    10 AS exponent,
    POW(2.00, 10) AS power_result,
    SQRT(2.00) AS sqrt_result,
    LOG(2.00) AS natural_log,
    LOG10(2.00) AS log10_result
UNION ALL
SELECT
    16.00,
    0,
    POW(16.00, 0),
    SQRT(16.00),
    LOG(16.00),
    LOG10(16.00);

操作后的数据状态：

结果解读：

• POW(2, 10) = 1024，即 2 的 10 次方
• SQRT(16) = 4，16 的平方根
• LOG(10) 是自然对数（以 e 为底），约 2.3026
• LOG10(10) 是以 10 为底的对数，结果为 1

进制转换

当前数据状态：

以下用常量演示进制转换函数：

SELECT
    10 AS decimal_val,
    BIN(10) AS binary_str,
    HEX(10) AS hex_str,
    CONV(HEX(10), 16, 10) AS back_to_dec
UNION ALL
SELECT
    255,
    BIN(255),
    HEX(255),
    CONV(HEX(255), 16, 10);

操作后的数据状态：

结果解读：

• BIN(10) 将十进制 10 转为二进制字符串 '1010'
• HEX(255) 将十进制 255 转为十六进制字符串 'FF'
• CONV('FF', 16, 10) 将十六进制转为十进制，返回 '255'（字符串形式）

SQL 综合示例

场景一：金额四舍五入

当前数据状态：

以下用常量演示金额四舍五入：

SELECT
    199.9950 AS total_amount,
    ROUND(199.9950, 2) AS round_2,
    ROUND(199.9950 * 0.95, 2) AS discounted_amount
UNION ALL
SELECT
    88.8888,
    ROUND(88.8888, 2),
    ROUND(88.8888 * 0.95, 2);

操作后的数据状态：

结果解读：订单金额保留两位小数展示，同时计算 95 折优惠后的金额。注意 199.9950 四舍五入到两位小数时变为 200.00。

场景二：分页随机推荐

当前数据状态：复用上面的 employees 表。

低性能写法（大数据量禁用）：

SELECT * FROM employees ORDER BY RAND() LIMIT 1;

结果示例：

高性能替代方案（需有自增主键）：

SELECT s.* FROM employees s
JOIN (
    SELECT emp_id FROM employees
    ORDER BY RAND() LIMIT 1
) tmp ON s.emp_id = tmp.emp_id;

结果解读：第一种写法对整个表排序，时间复杂度 O(N log N)。第二种写法先对较小的主键列随机排序取 ID，再通过 JOIN 取完整数据，减少了排序的数据量。但大数据量下仍建议程序层生成随机 ID 再查询。

场景三：生成指定范围随机整数

-- 生成 10 ~ 20 之间的随机整数
SELECT FLOOR(10 + RAND() * 11) AS random_10_to_20;

结果示例：

结果解读：公式为 FLOOR(min + RAND() * (max - min + 1))。RAND() 生成 [0, 1)，乘以 11 得 [0, 11)，加 10 得 [10, 21)，FLOOR 后得到 10 ~ 20 的整数。

常见误区

面试考点

Q：ROUND(2.5) 和 ROUND(3.5) 的结果？

MySQL 的 ROUND 采用"四舍六入五成双"（Banker's Rounding）：ROUND(2.5) = 2，ROUND(3.5) = 4。与通常的"四舍五入"不同。

Q：如何生成指定范围内的随机整数？

FLOOR(min + RAND() * (max - min + 1))。例如 10~20 之间的随机整数：FLOOR(10 + RAND() * 11)。

Q：ORDER BY RAND() 为什么慢？如何优化？

需要为每行生成随机数并全排序，时间复杂度 O(N log N)。优化方案：1. 程序层生成随机 ID 列表再查询；2. 用范围随机 WHERE id > RAND() * MAX_ID LIMIT 1（不均匀但快）；3. 额外维护一个随机排序字段。

小结

• ROUND 四舍五入，TRUNCATE 直接截断
• CEIL 向上取整，FLOOR 向下取整
• RAND() 用于随机数，但 ORDER BY RAND() 大数据量时性能极差
• 数值函数减少程序层计算，让数据处理更接近数据源

下一章引子：数值之外，字符串操作是另一类高频需求——拼接、截取、替换、格式化。