MySQL内置函数全解析

MySQL内置函数是数据库开发中不可或缺的工具，它们能够简化数据处理、优化查询性能并提升开发效率。本文将全面解析MySQL中各类内置函数的用法、示例及注意事项，帮助开发者更好地掌握这些强大的工具。

一、字符串函数

字符串函数用于处理文本数据，包括拼接、截取、转换大小写等操作。

1. CONCAT(str1, str2, ...)

将多个字符串连接成一个字符串。

SELECT CONCAT('Hello', ' ', 'World'); -- 结果：'Hello World'
SELECT CONCAT('姓名：', name) FROM users; -- 拼接字段与固定文本

2. SUBSTRING(str, pos, len)

从字符串str的pos位置开始截取长度为len的子串（pos从1开始计数）。

SELECT SUBSTRING('MySQL Tutorial', 1, 5); -- 结果：'MySQL'
SELECT SUBSTRING('MySQL Tutorial', 7); -- 从第7位截取到末尾，结果：'Tutorial'

3. UPPER(str) / LOWER(str)

将字符串转换为大写或小写。

SELECT UPPER('hello'); -- 结果：'HELLO'
SELECT LOWER('WORLD'); -- 结果：'world'

4. TRIM([remstr FROM] str)

去除字符串首尾的指定字符（默认为空格）。

SELECT TRIM('   MySQL   '); -- 结果：'MySQL'
SELECT TRIM(BOTH 'x' FROM 'xxMySQLxx'); -- 去除首尾的'x'，结果：'MySQL'

5. REPLACE(str, from_str, to_str)

将字符串中的from_str替换为to_str。

SELECT REPLACE('I like cats', 'cats', 'dogs'); -- 结果：'I like dogs'

二、数值函数

数值函数用于数学计算，包括取整、四舍五入、随机数生成等。

1. ROUND(num, decimals)

对num进行四舍五入，保留decimals位小数。

SELECT ROUND(123.456, 2); -- 结果：123.46
SELECT ROUND(123.454, 2); -- 结果：123.45

2. CEILING(num) / FLOOR(num)

CEILING返回大于等于num的最小整数（向上取整），FLOOR返回小于等于num的最大整数（向下取整）。

SELECT CEILING(123.45); -- 结果：124
SELECT FLOOR(123.99); -- 结果：123

3. ABS(num)

返回num的绝对值。

SELECT ABS(-123); -- 结果：123
SELECT ABS(45.67); -- 结果：45.67

4. RAND()

生成一个0到1之间的随机浮点数。

SELECT RAND(); -- 结果：0.123456789（每次执行不同）
SELECT FLOOR(RAND() * 100); -- 生成0-99之间的随机整数

5. POWER(num, exponent)

返回num的exponent次幂。

SELECT POWER(2, 3); -- 结果：8（2的3次方）
SELECT POWER(10, -2); -- 结果：0.01（10的-2次方）

三、日期和时间函数

日期和时间函数用于处理日期时间数据，包括获取当前时间、日期格式化、日期计算等。

1. NOW() / CURDATE() / CURTIME()

NOW()返回当前日期和时间，CURDATE()返回当前日期，CURTIME()返回当前时间。

SELECT NOW(); -- 结果：'2023-10-01 15:30:45'
SELECT CURDATE(); -- 结果：'2023-10-01'
SELECT CURTIME(); -- 结果：'15:30:45'

2. DATE_FORMAT(date, format)

将日期按照指定格式转换为字符串。

SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s'); -- 结果：'2023-10-01 15:30:45'
SELECT DATE_FORMAT(NOW(), '%Y年%m月%d日'); -- 结果：'2023年10月01日'

常用格式符：%Y（四位年份）、%y（两位年份）、%m（月份）、%d（日期）、%H（24小时制小时）、%h（12小时制小时）、%i（分钟）、%s（秒）。

3. DATEDIFF(date1, date2)

返回date1与date2之间的天数差（date1 - date2）。

SELECT DATEDIFF('2023-10-05', '2023-10-01'); -- 结果：4

4. DATE_ADD(date, INTERVAL expr unit)

在date基础上增加指定的时间间隔。

SELECT DATE_ADD(NOW(), INTERVAL 1 DAY); -- 加1天
SELECT DATE_ADD(NOW(), INTERVAL -1 MONTH); -- 减1个月
SELECT DATE_ADD('2023-02-28', INTERVAL 1 DAY); -- 结果：'2023-03-01'（自动处理跨月）

5. EXTRACT(unit FROM date)

从日期中提取指定的部分（如年、月、日）。

SELECT EXTRACT(YEAR FROM NOW()); -- 提取年份，结果：2023
SELECT EXTRACT(MONTH FROM NOW()); -- 提取月份，结果：10
SELECT EXTRACT(DAY FROM NOW()); -- 提取日期，结果：1

四、聚合函数

聚合函数用于对一组值进行计算并返回单个值，通常与GROUP BY子句一起使用。

1. COUNT()

统计行数或非NULL值的数量。

SELECT COUNT(*) FROM users; -- 统计所有行数
SELECT COUNT(email) FROM users; -- 统计email非NULL的行数

2. SUM(column)

计算某列的总和。

SELECT SUM(amount) FROM orders WHERE status = 'completed'; -- 计算已完成订单的总金额

3. AVG(column)

计算某列的平均值。

SELECT AVG(score) FROM students; -- 计算学生的平均分

4. MAX(column) / MIN(column)

返回某列的最大值或最小值。

SELECT MAX(price) FROM products; -- 最高价格
SELECT MIN(create_time) FROM logs; -- 最早的日志时间

5. GROUP_CONCAT(column)

将分组中的某列值连接成一个字符串。

SELECT dept_id, GROUP_CONCAT(name) FROM employees GROUP BY dept_id;
-- 结果示例：dept_id | name_list
--          1      | '张三,李四,王五'

五、条件判断函数

条件判断函数用于根据条件返回不同的值。

1. IF(expr, true_val, false_val)

如果expr为真，返回true_val，否则返回false_val。

SELECT name, IF(score >= 60, '及格', '不及格') AS result FROM students;

2. CASE WHEN ... THEN ... ELSE ... END

多条件分支判断，类似编程语言中的switch-case。

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

3. COALESCE(val1, val2, ...)

返回参数列表中第一个非NULL的值。

SELECT COALESCE(NULL, '默认值', '其他值'); -- 结果：'默认值'
SELECT COALESCE(mobile, email, '无联系方式') FROM users; -- 优先取手机号，再取邮箱，都没有则显示默认文本

4. NULLIF(expr1, expr2)

如果expr1等于expr2，返回NULL，否则返回expr1。

SELECT NULLIF(10, 10); -- 结果：NULL
SELECT NULLIF(10, 20); -- 结果：10

六、系统信息函数

系统信息函数用于获取数据库服务器的相关信息。

1. VERSION()

返回MySQL服务器版本号。

SELECT VERSION(); -- 结果：'8.0.32'

2. DATABASE()

返回当前使用的数据库名。

SELECT DATABASE(); -- 结果：'my_database'

3. USER() / CURRENT_USER()

USER()返回当前连接的用户和主机信息，CURRENT_USER()返回当前授权的用户和主机信息。

SELECT USER(); -- 结果：'root@localhost'
SELECT CURRENT_USER(); -- 结果：'root@localhost'

4. CONNECTION_ID()

返回当前连接的ID。

SELECT CONNECTION_ID(); -- 结果：123（具体数字取决于当前连接）

七、其他实用函数

1. MD5(str)

返回字符串str的MD5哈希值（常用于密码加密）。

SELECT MD5('password123'); -- 结果：'482c811da5d5b4bc6d497ffa98491e38'

2. JSON_EXTRACT(json_doc, path)

从JSON文档中提取指定路径的值（MySQL 5.7+支持）。

SELECT JSON_EXTRACT('{"name": "John", "age": 30}', '$.name'); -- 结果：'"John"'
SELECT JSON_UNQUOTE(JSON_EXTRACT('{"name": "John"}', '$.name')); -- 去除引号，结果：'John'

3. INSTR(str, substr)

返回substr在str中第一次出现的位置（从1开始计数，未找到返回0）。

SELECT INSTR('MySQL Tutorial', 'SQL'); -- 结果：3
SELECT INSTR('MySQL Tutorial', 'Java'); -- 结果：0

4. LEFT(str, len) / RIGHT(str, len)

LEFT返回str左边len个字符，RIGHT返回右边len个字符。

SELECT LEFT('MySQL', 2); -- 结果：'My'
SELECT RIGHT('MySQL', 3); -- 结果：'SQL'

八、函数使用注意事项

1. 性能影响：函数会对每行数据进行处理，在WHERE子句中使用函数可能导致索引失效，降低查询性能。例如，应避免使用WHERE UPPER(name) = 'JOHN'，而应使用WHERE name = 'JOHN'（假设存储时已统一为大写）。

2. NULL值处理：大多数函数在输入为NULL时会返回NULL，需注意COALESCE、IFNULL等函数的使用来处理空值情况。

3. 数据类型兼容性：确保函数参数的数据类型匹配，例如字符串函数应传入字符串类型，数值函数传入数值类型，否则可能导致意外结果或错误。

4. 版本差异：部分函数是特定MySQL版本引入的（如JSON函数需5.7+），使用时需注意数据库版本兼容性。

5. 嵌套使用：函数可以嵌套使用，但过度嵌套会降低可读性和维护性，建议适当拆分复杂表达式。

九、总结

MySQL内置函数覆盖了字符串处理、数值计算、日期时间操作、聚合分析等多个方面，熟练掌握这些函数能显著提升数据库开发和查询的效率。在实际使用中，应根据具体场景选择合适的函数，并注意性能优化和版本兼容性。通过灵活运用这些工具，开发者可以更高效地处理数据，构建出更强大、更灵活的数据库应用。

MySQL内置函数 字符串函数 数值函数 日期函数 聚合函数