【MySQL基础-17】MySQL数字函数详解：从基础到高级应用

MySQL提供了丰富的数字处理函数，这些函数在数据分析、报表生成和业务逻辑实现中扮演着重要角色。本文将全面介绍MySQL中的数字函数，包括基础运算、数值处理、数学函数以及高级应用技巧。

1. 基础算术运算函数

1.1 基本运算符

MySQL支持标准的算术运算符：

SELECT 10 + 5 AS addition,      -- 加法 (15)10 - 5 AS subtraction,   -- 减法 (5)10 * 5 AS multiplication,-- 乘法 (50)10 / 5 AS division,      -- 除法 (2.0)10 DIV 5 AS int_division,-- 整除 (2)10 % 3 AS modulus;       -- 取模 (1)

1.2 ABS() - 绝对值

SELECT ABS(-123);  -- 返回123
SELECT ABS(0);     -- 返回0
SELECT ABS(456);   -- 返回456

1.3 SIGN() - 符号函数

SELECT SIGN(-10),  -- 返回-1SIGN(0),    -- 返回0SIGN(10);   -- 返回1

2. 数值舍入与截断函数

2.1 ROUND() - 四舍五入

SELECT ROUND(123.456),      -- 123ROUND(123.456, 1),   -- 123.5ROUND(123.456, 2),   -- 123.46ROUND(123.456, -1);  -- 120

2.2 TRUNCATE() - 数值截断

SELECT TRUNCATE(123.456, 0),  -- 123TRUNCATE(123.456, 1),  -- 123.4TRUNCATE(123.456, 2),  -- 123.45TRUNCATE(123.456, -1); -- 120

2.3 CEIL()和FLOOR() - 向上/向下取整

SELECT CEIL(123.456),   -- 124CEIL(-123.456),  -- -123FLOOR(123.456),  -- 123FLOOR(-123.456); -- -124

3. 数学函数

3.1 幂运算函数

SELECT POWER(2, 3),     -- 8 (2的3次方)SQRT(16),        -- 4 (平方根)EXP(1),          -- 2.718281828459045 (e的1次方)LN(10),          -- 2.302585092994046 (自然对数)LOG10(100);      -- 2 (以10为底的对数)

3.2 三角函数

SELECT SIN(PI()/2),     -- 1COS(PI()),       -- -1TAN(PI()/4),     -- 0.9999999999999999 (近似1)ASIN(1),         -- 1.5707963267948966 (π/2)ACOS(0),         -- 1.5707963267948966 (π/2)ATAN(1);         -- 0.7853981633974483 (π/4)

3.3 角度与弧度转换

SELECT DEGREES(PI()),    -- 180RADIANS(180);     -- 3.141592653589793 (π)

4. 随机数与数值生成

4.1 RAND() - 随机数生成

SELECT RAND();              -- 0到1之间的随机数
SELECT FLOOR(RAND() * 100); -- 0到99的随机整数-- 获取随机排序的结果集
SELECT * FROM products ORDER BY RAND() LIMIT 5;

4.2 生成序列技巧

-- 生成1到10的序列
SELECT n FROM (SELECT 1 AS n UNION SELECT 2 UNION SELECT 3 UNIONSELECT 4 UNION SELECT 5 UNION SELECT 6 UNIONSELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10
) numbers;-- 使用变量生成序列
SET @row = 0;
SELECT (@row:=@row+1) AS row_number FROM table LIMIT 10;

5. 数值比较与条件函数

5.1 GREATEST()和LEAST()

SELECT GREATEST(10, 20, 30),  -- 30LEAST(10, 20, 30);     -- 10

5.2 条件数值函数

SELECT IF(score > 60, '及格', '不及格') AS result,CASE WHEN score >= 90 THEN '优秀'WHEN score >= 80 THEN '良好'WHEN score >= 60 THEN '及格'ELSE '不及格'END AS grade
FROM students;

6. 高级数值处理技巧

6.1 数值格式化

SELECT FORMAT(1234567.89, 2);  -- '1,234,567.89'

6.2 进制转换

SELECT BIN(10),    -- '1010' (二进制)OCT(10),    -- '12' (八进制)HEX(255);   -- 'FF' (十六进制)

6.3 位运算函数

SELECT 5 & 3,      -- 1 (位与)5 | 3,      -- 7 (位或)5 ^ 3,      -- 6 (位异或)~5,         -- -6 (位取反)5 << 1,     -- 10 (左移)5 >> 1;     -- 2 (右移)

7. 数值聚合函数

7.1 基本聚合函数

SELECT SUM(sales) AS total_sales,AVG(price) AS average_price,MIN(price) AS min_price,MAX(price) AS max_price,COUNT(*) AS product_count
FROM products;

7.2 高级聚合函数

SELECT VARIANCE(score) AS score_variance,STDDEV(score) AS score_stddev,GROUP_CONCAT(DISTINCT category ORDER BY category SEPARATOR ', ') AS categories
FROM products
GROUP BY department;

8. 性能优化与最佳实践

避免在WHERE子句中使用函数：这会导致索引失效

-- 不推荐
SELECT * FROM orders WHERE YEAR(order_date) = 2023;-- 推荐
SELECT * FROM orders WHERE order_date BETWEEN '2023-01-01' AND '2023-12-31';

使用适当的数值类型：根据数据范围选择TINYINT、INT、BIGINT或DECIMAL
批量处理代替循环：尽量减少在SQL中使用循环逻辑

注意浮点数精度问题：

-- 浮点数比较要小心
SELECT * FROM products WHERE ABS(price - 19.99) < 0.001;

9. 实际应用案例

9.1 分页计算

-- 计算总页数
SELECT CEIL(COUNT(*) / 20) AS total_pages FROM products;

9.2 价格区间统计

SELECT FLOOR(price/10)*10 AS price_range,COUNT(*) AS product_count
FROM products
GROUP BY price_range
ORDER BY price_range;

9.3 数据标准化

-- 将分数标准化到0-100范围
SELECT student_id,score,(score - MIN(score) OVER()) / (MAX(score) OVER() - MIN(score) OVER()) * 100 AS normalized_score
FROM exam_results;