核心内容摘要
节奏律动，永不落幕：探索“笔盒永久发布beatbox8”的无限可能
常用函数与操作符 —— SQL 的魔法工具箱核心摘要SQL 的强大不仅在于查数据还在于处理数据。
本章将为你打开 MySQL 的内置函数库从字符串处理的字节陷阱到日期计算的千年虫问题再到流程控制的逻辑艺术。
我们不仅教你怎么用更会告诉你哪些函数会让索引失效以及时区对时间函数的致命影响。
环境准备为了确保本章的所有示例特别是字符串处理和报表生成都能直接运行我们需要先完善数据模型。
本章我们将引入users表并对orders表进行扩展。
0 环境准备 (Data Setup)请务必执行以下 SQL以构建完整的测试环境。
我们将创建用户表并给订单表增加备注字段。
USEshop_biz;--
创建并初始化 users 表DROPTABLEIFEXISTSusers;CREATETABLEusers(user_idINTPRIMARYKEYAUTO_INCREMENT,usernameVARCHAR(
NOTNULL,emailVARCHAR(
,created_atDATETIMEDEFAULTCURRENT_TIMESTAMP)CHARSETutf8;INSERTINTOusers(user_id,username,email)VALUES(101,admin_zhang,zhangexample.com),(102,user_li,liexample.com),(103,adm_monitor,monitorsys.com),-- 用于测试前缀匹配(104,guest_wang,NULL);-- 用于测试 NULL 处理--
扩展 orders 表 (增加 user_note 字段用于字符串函数演示)-- 如果你沿用
的表需要执行 ALTER如果是新环境直接用下面的 CREATEDROPTABLEIFEXISTSorders;CREATETABLEorders(order_idINTPRIMARYKEYAUTO_INCREMENT,user_idINTNOTNULL,total_amountDECIMAL(10,
,user_noteVARCHAR(
COMMENT用户备注,created_atDATETIMEDEFAULTCURRENT_TIMESTAMP)CHARSETutf8;INSERTINTOorders(order_id,user_id,total_amount,user_note,created_at)VALUES(1,101,
5
00, 发顺丰快递 ,
10:00:
,-- 前后有空格用于测试 TRIM(2,101,
1
00,NULL,
11:00:
,(3,102,
3
00,请周末配送,
12:00:
;
1 字符串函数 (String Functions)处理文本数据是开发中最常见的需求。
但要注意MySQL 中的字符串函数通常是大小写不敏感的取决于 Collation。
7.
1 拼接与截取CONCAT(s1, s2, ...)作用将多个字符串连接成一个。
坑点只要有一个参数是 NULL结果就是 NULL-- 尝试拼接用户名和备注SELECTCONCAT(User:,username, Note:,NULL)FROMusersWHEREuser_id101;-- 结果NULL解决使用CONCAT_WS(separator, s1, s
(With Separator)它会自动跳过 NULL。
SELECTCONCAT_WS( ,User:,username,NULL)FROMusersWHEREuser_id101;-- 结果User: admin_zhangSUBSTRING(str, pos, len)作用截取字符串。
注意MySQL 的下标从 1 开始不是 0SELECTSUBSTRING(Hello World,1,
;-- 结果Hello
7.
2 长度计算的字节陷阱LENGTH(str)返回字符串的字节数。
CHAR_LENGTH(str)返回字符串的字符数。
实战演示UTF8 编码下一个汉字占 3 字节SELECTLENGTH(中国);-- 结果6 (
SELECTCHAR_LENGTH(中国);-- 结果2场景如果你的数据库字段定义是VARCHAR(
指的是 10 个字符不是字节。
但如果你在应用层限制输入长度请务必区分字节和字符。
7.
3 清理与转换TRIM(str): 去除首尾空格。
UPPER(str)/LOWER(str): 大小写转换。
-- 清理订单备注中的空格并转大写虽然后者对中文没用SELECTorder_id,TRIM(user_note)ASclean_noteFROMordersWHEREorder_id1;-- 结果发顺丰快递 (原本前后有空格)性能警示不要在 WHERE 条件的左侧使用函数-- 极慢会导致索引失效全表扫描-- 即使 username 上有索引因为对它做了 LEFT 操作数据库必须把每一行都拿出来算一遍SELECT*FROMusersWHERELEFT(username,
adm;-- 极快走索引范围查询SELECT*FROMusersWHEREusernameLIKEadm%;
2 数值函数 (Numeric Functions)虽然复杂的数学计算建议在应用层Java/Python做但基本的统计计算还得靠 SQL。
取整与四舍五入CEIL(x): 向上取整 (Ceiling) -CEIL(
1.
2FLOOR(x): 向下取整 (Floor) -FLOOR(
1.
1ROUND(x, d): 四舍五入保留 d 位小数 -ROUND(
58,
1)
6随机数RAND(): 返回 0 到 1 之间的随机浮点数。
坑点ORDER BY RAND()性能极差不要用它来随机抽取数据会把所有行加载到内存排序。
3 日期时间函数 (Date and Time Functions) —— 最复杂的领域时间处理是 Bug 的重灾区主要源于格式和时区。
7.
1 获取当前时间NOW(): 返回当前日期和时间 (YYYY-MM-DD HH:MM:SS)。
在语句开始执行时就固定了。
SYSDATE(): 返回函数执行时的实时时间。
区别如果一条 SQL 执行了 2 秒NOW()在这 2 秒内所有行是一样的SYSDATE()可能会变。
推荐使用NOW()。
7.
2 日期格式化 (Format)DATE_FORMAT(date, format)是报表统计的神器。
-- 将订单创建时间转换为 2023年01月 格式SELECTorder_id,DATE_FORMAT(created_at,%Y年%m月)ASmonth_strFROMorders;
7.
3 日期计算DATE_ADD(date, INTERVAL expr unit)-- 推算 30 天后的时间常用于会员过期计算SELECTDATE_ADD(NOW(),INTERVAL30DAY);DATEDIFF(date1, date
计算两个日期相差的天数date1 - date2。
7.
4 时区问题 (Timezone)MySQL 的TIMESTAMP类型会受时区影响而DATETIME不会。
最佳实践建议服务器和数据库统一设置为 UTC 或者业务所在地时区如 Asia/Shanghai并在应用层处理时区转换。
4 流程控制函数 (Flow Control) —— SQL 中的 IF-ELSE
7.
1IF(expr1, expr2, expr
逻辑如果expr1为真返回expr2否则返回expr3。
-- 检查订单是否有备注SELECTorder_id,IF(user_noteISNOTNULL,有,无)AShas_noteFROMorders;
7.
2IFNULL(expr1, expr
逻辑如果expr1不是 NULL返回expr1否则返回expr2。
必用场景聚合求和时防止返回 NULL。
-- 如果某个用户没有任何订单SUM 结果是 NULL。
用 IFNULL 转为 0。
SELECTIFNULL(SUM(total_amount),
FROMordersWHEREuser_id99999;
7.
3CASE WHEN—— 复杂的条件判断这是 SQL 中最强大的逻辑控制器。
-- 对订单金额进行分级SELECTorder_id,total_amount,CASEWHENtotal_amount10000THEN大额订单WHENtotal_amount1000THEN普通订单ELSE小额订单ENDASamount_levelFROMorders;
5 JSON 函数 (MySQL
7 特性)MySQL
7 引入了原生的 JSON 类型。
虽然我们这里的表没有定义 JSON 字段但可以直接演示函数的用法。
-- 模拟从 JSON 字符串中提取数据SELECTJSON_EXTRACT({id: 1, name: iPhone},$.name);-- 结果iPhone
6 综合实战生成复杂的订单报表需求生成一份报表包含订单号、下单日期格式
-
订单金额、金额等级5000为大额、是否有备注去空格后判断长度。
SELECTorder_id,DATE_FORMAT(created_at,%Y-%m-%d)ASorder_date,IFNULL(total_amount,
ASamount,-- 防止金额为 NULL-- 金额分级CASEWHENtotal_amount5000THEN大额订单ELSE普通订单ENDASamount_level,-- 备注状态先 TRIM 去空格再看 LENGTH 是否大于 0IF(LENGTH(TRIM(user_note))0,有备注,无备注)AShas_noteFROMorders;执行结果预期order_id1: 大额订单, 有备注 (因为 user_note 是 ’ 发顺丰快递 , TRIM 后有长度)order_id2: 大额订单, 无备注 (user_note 是 NULL, LENGTH 是 NULL, IF 判断为 False -注严格来说 LENGTH(NULL) 返回 NULL在 IF 中 NULL 被视为 False显示无备注符合逻辑)order_id3: 普通订单, 有备注通过本章你手中的 SQL 武器库已经非常丰富了。
下一章我们将进入数据库领域的深水区索引与性能优化。