置函數(shù)實(shí)戰(zhàn)指南：日期、字符串與流程控制函數(shù)核心用法解析)
1. 日期函數(shù)的正確打開方式別只沉浸在NOW()里做MySQL開發(fā)這些年我見過最多的SQL問題一半以上都出在日期處理上。很多人寫NOW()取當(dāng)前時(shí)間用得飛起但一遇到上周一的訂單量上個(gè)月的注冊用戶數(shù)這類需求就開始頭疼了。其實(shí)MySQL的日期函數(shù)遠(yuǎn)比你想象中強(qiáng)大但前提是你要理解它內(nèi)部的存儲(chǔ)邏輯。1.1 日期存儲(chǔ)的底層邏輯為什么建議用DATETIME而不是字符串先說一個(gè)最基礎(chǔ)也最關(guān)鍵的認(rèn)知MySQL的日期時(shí)間在底層是數(shù)字形式存儲(chǔ)的所謂2025-01-15 10:30:00只是它展示給你的樣子。所以日期函數(shù)干的事本質(zhì)上就是在各種數(shù)字格式之間做轉(zhuǎn)換。你應(yīng)該見過有人用字符串比較日期SELECT * FROM orders WHERE order_time 2025-01-15;這條SQL在大多數(shù)情況下能跑通但它有一個(gè)隱患如果order_time列是DATETIME類型而2025-01-15只是字符串MySQL需要先把字符串隱式轉(zhuǎn)換成日期再比較。隱式轉(zhuǎn)換一旦發(fā)生索引就失效了數(shù)據(jù)量大時(shí)全表掃描就是必然結(jié)局。正確做法是SELECT * FROM orders WHERE order_time 2025-01-15 00:00:00 AND order_time 2025-01-16 00:00:00;或者直接使用日期函數(shù)把列值規(guī)范化SELECT * FROM orders WHERE DATE(order_time) 2025-01-15;注意DATE(order_time)雖然寫法干凈但同樣無法使用索引因?yàn)槊恳恍卸家冗^一遍函數(shù)。如果表很大更推薦范圍寫法這類問題我在后面章節(jié)單獨(dú)展開講。1.2 高頻日期函數(shù)逐個(gè)拆解NOW、DATE_FORMAT、DATEDIFF在真實(shí)場景中的用法我把日常用得最多的日期函數(shù)整理成了下面這張速查表每個(gè)都附帶了使用場景。函數(shù)作用典型場景NOW()返回當(dāng)前日期時(shí)間記錄操作時(shí)間、更新updated_atCURDATE()返回當(dāng)前日期無時(shí)間統(tǒng)計(jì)當(dāng)天下單量DATE_FORMAT(date, fmt)按指定格式輸出日期前端展示、報(bào)表分組DATEDIFF(d1, d2)返回兩個(gè)日期相差的天數(shù)計(jì)算賬齡、會(huì)員活躍天數(shù)DATE_ADD(date, INTERVAL n unit)日期加減計(jì)算到期日、回溯統(tǒng)計(jì)窗口YEAR()/MONTH()/DAY()提取日期部分按月/年分組統(tǒng)計(jì)DAYOFWEEK()返回星期索引1周日判斷周末UNIX_TIMESTAMP()轉(zhuǎn)成時(shí)間戳與前端/其他系統(tǒng)對(duì)接這里重點(diǎn)說DATE_FORMAT它是報(bào)表開發(fā)中最常用的一個(gè)。比如要按小時(shí)統(tǒng)計(jì)訂單量SELECT DATE_FORMAT(create_time, %Y-%m-%d %H:00:00) AS hour_slot, COUNT(*) AS order_cnt FROM orders WHERE create_time DATE_SUB(NOW(), INTERVAL 24 HOUR) GROUP BY hour_slot ORDER BY hour_slot;格式化占位符有嚴(yán)格區(qū)分%Y和%y不一樣四位年份和兩位年份%m和%i也不一樣月份和分鐘首次用容易寫錯(cuò)。我建議直接記常用格式定期查閱官方文檔核對(duì)別憑印象寫。1.3 時(shí)區(qū)帶來的坑TIMESTAMP和DATETIME的選擇另一個(gè)高頻翻車點(diǎn)是TIMESTAMP和DATETIME的時(shí)區(qū)差異。TIMESTAMP是UTC存儲(chǔ)、查詢時(shí)按會(huì)話時(shí)區(qū)轉(zhuǎn)換而DATETIME是原樣存儲(chǔ)不帶時(shí)區(qū)信息。如果你的數(shù)據(jù)庫連接串設(shè)置了serverTimezoneAsia/Shanghai但部署服務(wù)器實(shí)際是UTC時(shí)區(qū)那NOW()取到的結(jié)果會(huì)憑空差8個(gè)小時(shí)。這個(gè)坑我實(shí)測遇到過不止一次排查時(shí)往往先懷疑代碼最后才發(fā)現(xiàn)是時(shí)區(qū)配置有偏差。我的建議是統(tǒng)一約定所有環(huán)境統(tǒng)一使用CST或UTC08:00時(shí)區(qū)連接串、服務(wù)器時(shí)區(qū)、MySQL的time_zone變量三者保持一致。表結(jié)構(gòu)選型如果需要跨時(shí)區(qū)使用或者對(duì)接的是全球化業(yè)務(wù)優(yōu)先TIMESTAMP如果只是國內(nèi)業(yè)務(wù)、要精確存儲(chǔ)用戶輸入的日期時(shí)間DATETIME更直觀。上線前自查寫一條SELECT NOW(), 連接到測試庫對(duì)比一下如果和服務(wù)器當(dāng)前時(shí)間不一致說明時(shí)區(qū)配置有問題。2. 字符串函數(shù)數(shù)據(jù)處理和報(bào)表清洗的核心彈藥庫日期函數(shù)解決時(shí)間怎么算的問題字符串函數(shù)則是解決字段怎么撕的問題。在實(shí)際業(yè)務(wù)里字符串處理幾乎每天都在發(fā)生——用戶昵稱帶特殊字符、手機(jī)號(hào)中間四位要脫敏、商品編碼要截取前綴分類、多張小表要拼接成一張寬表。2.1 CONCAT家族拼接的正確姿勢與NULL陷阱先看最基礎(chǔ)的拼接。常見的三種寫法是CONCAT、CONCAT_WS和直接在SQL里用||取決于sql_mode是否開啟了PIPES_AS_CONCAT。-- 常規(guī)拼接 SELECT CONCAT(first_name, last_name) AS full_name FROM users; -- 帶分隔符拼接第二個(gè)參數(shù)是分隔符 SELECT CONCAT_WS(-, province, city, district) AS full_address FROM users; -- 注意如果first_name為NULLCONCAT返回NULL SELECT CONCAT(first_name, COALESCE(last_name, )) FROM users;這里必須單獨(dú)說CONCAT和NULL的行為。很多人以為CONCAT(NULL, abc)會(huì)返回abc但MySQL的實(shí)際行為是返回NULL。要處理NULL要么用IFNULL包一層要么用CONCAT_WS——它有自動(dòng)跳過NULL的能力算是個(gè)冷門優(yōu)點(diǎn)。我在日常寫項(xiàng)目時(shí)喜歡用CONCAT_WS多于CONCAT因?yàn)樘幚磬]政編碼、地址這種帶分隔符的拼接時(shí)不用手動(dòng)處理NULL和多余的-。2.2 SUBSTRING、LEFT、RIGHT準(zhǔn)確裁出你需要的那段截取函數(shù)在脫敏、格式清洗里常駐。函數(shù)作用示例結(jié)果LEFT(s, n)取左側(cè)n個(gè)字符LEFT(13812345678, 3)138RIGHT(s, n)取右側(cè)n個(gè)字符RIGHT(13812345678, 4)5678SUBSTRING(s, pos, len)從位置pos開始取len個(gè)SUBSTRING(MySQL, 2, 3)ySQSUBSTRING_INDEX(s, delim, n)按分隔符截取SUBSTRING_INDEX(a,b,c, ,, 2)a,b脫敏的經(jīng)典寫法是SELECT CONCAT(LEFT(phone, 3), ****, RIGHT(phone, 4)) AS masked_phone FROM users;凡是涉及拼接和截取建議先在SELECT里跑一條不帶WHERE的語句驗(yàn)證結(jié)果再套進(jìn)UPDATE或大批量處理避免因?yàn)镹ULL或長度不足產(chǎn)生臟數(shù)據(jù)。2.3 REPLACE、TRIM和大小寫轉(zhuǎn)換清洗數(shù)據(jù)三件套從Excel導(dǎo)入的數(shù)據(jù)經(jīng)常帶空格、全角字符或者字段里混著不一致的大小寫。這時(shí)候用TRIM、REPLACE和大小寫函數(shù)組合就能快速清洗。-- 去除兩端空格 SELECT TRIM( hello ); -- 結(jié)果為 hello -- 去除兩端指定字符注意是去除兩端出現(xiàn)的所有該字符 SELECT TRIM(BOTH x FROM xxxMySQLxxx); -- 結(jié)果為 MySQL -- 替換字符串中的特定內(nèi)容 SELECT REPLACE(2025-01-15, -, /); -- 結(jié)果為 2025/01/15 -- 大小寫標(biāo)準(zhǔn)化 SELECT LOWER(MySQL), UPPER(mysql);還有LPAD和RPAD這類補(bǔ)位函數(shù)做流水號(hào)、訂單號(hào)格式化時(shí)很有用。比如把訂單號(hào)補(bǔ)齊到8位SELECT LPAD(order_id, 8, 0) FROM orders;2.4 字符串切分與正則匹配比LIKE更強(qiáng)大的REGEXP模糊匹配大家都會(huì)用LIKE但遇到以A開頭、中間包含數(shù)字、以B結(jié)尾這種組合條件時(shí)LIKE就很痛苦。MySQL的正則表達(dá)式函數(shù)REGEXP能幫你解決這類需求。-- 查找手機(jī)號(hào)以138開頭的用戶 SELECT * FROM users WHERE phone REGEXP ^138; -- 查找郵箱屬于任意主流免費(fèi)服務(wù)商163/qq/126的用戶 SELECT * FROM users WHERE email REGEXP (163|qq|126)\.com$;REGEXP在MySQL 8.0中升級(jí)成了REGEXP_LIKE、REGEXP_REPLACE、REGEXP_SUBSTR等新函數(shù)功能更強(qiáng)。但要注意正則匹配沒法像LIKE prefix%那樣利用普通索引數(shù)據(jù)量大了要謹(jǐn)慎。3. 數(shù)學(xué)函數(shù)報(bào)表和積分系統(tǒng)里的實(shí)用計(jì)算工具數(shù)學(xué)函數(shù)不像日期和字符串用得那么密但一碰上就會(huì)用到尤其是做報(bào)表、算金額、抽獎(jiǎng)、排名這些場景。3.1 ROUND、FLOOR、CEILING取整不只是四舍五入說到取整大多數(shù)人第一反應(yīng)是ROUND但業(yè)務(wù)中向下取整和向上取整的需求也不少見。-- 四舍五入到兩位小數(shù) SELECT ROUND(3.14159, 2); -- 3.14 -- 向下取整到整數(shù) SELECT FLOOR(3.999); -- 3 -- 向上取整到整數(shù) SELECT CEILING(3.001); -- 4 -- 截?cái)嘈?shù)直接丟掉指定位之后的內(nèi)容 SELECT TRUNCATE(3.14159, 2); -- 3.14一個(gè)容易踩的坑ROUND在MySQL里對(duì).5的處理遵循四舍五入在部分語言里是銀行家舍入所以ROUND(2.5)結(jié)果是3ROUND(3.5)結(jié)果是4這一點(diǎn)與Python的round不同。如果團(tuán)隊(duì)內(nèi)有多種語言混合開發(fā)務(wù)必確認(rèn)統(tǒng)一口徑。3.2 RAND()抽獎(jiǎng)和隨機(jī)取樣的玩法RAND()返回0到1之間的隨機(jī)小數(shù)常用于隨機(jī)排序和抽樣。-- 隨機(jī)取5條記錄 SELECT * FROM products ORDER BY RAND() LIMIT 5; -- 生成指定范圍的隨機(jī)整數(shù)比如1到100 SELECT FLOOR(RAND() * 100) 1;但是ORDER BY RAND()在大表上性能很糟糕因?yàn)樗鼤?huì)對(duì)全表每行生成隨機(jī)數(shù)再排序。數(shù)據(jù)量超過幾萬行時(shí)我更推薦先SELECT COUNT(*)得到總數(shù)然后在應(yīng)用層隨機(jī)取一個(gè)偏移量再LIMIT 1配合OFFSET取數(shù)。這是典型的看似簡單、實(shí)則費(fèi)性能的場景。3.3 聚合函數(shù)中的數(shù)學(xué)函數(shù)SUM、AVG遇上NULL的規(guī)則嚴(yán)格來說SUM和AVG不是數(shù)學(xué)函數(shù)而是聚合函數(shù)但它們離不開數(shù)學(xué)計(jì)算所以一起說。關(guān)鍵規(guī)則是聚合函數(shù)會(huì)忽略NULL值。AVG不會(huì)把NULL當(dāng)作0來算這一點(diǎn)很多人會(huì)搞錯(cuò)。比如一個(gè)班有10個(gè)人其中一個(gè)人缺考AVG(score)算的是9個(gè)人的平均分而不是總分除以10。另外一個(gè)高頻需求是按金額區(qū)間統(tǒng)計(jì)可以配合數(shù)學(xué)函數(shù)分組SELECT CASE WHEN amount 100 THEN 0-100 WHEN amount 500 THEN 100-500 ELSE 500 END AS amount_bucket, COUNT(*), SUM(amount) FROM orders GROUP BY amount_bucket;這種分組不直接用數(shù)學(xué)函數(shù)但CASE WHEN表達(dá)式本身就是一個(gè)轉(zhuǎn)譯函數(shù)配合聚合能做出非常靈活的分檔統(tǒng)計(jì)。4. 其他內(nèi)置函數(shù)控制流程、判空與類型轉(zhuǎn)換的組合藝術(shù)MySQL的函數(shù)體系里除了按數(shù)據(jù)類型劃分的日期、字符串、數(shù)學(xué)函數(shù)還有一大批通用函數(shù)負(fù)責(zé)流程控制、空值處理和類型轉(zhuǎn)換。它們是讓SQL從一個(gè)查詢工具變成業(yè)務(wù)邏輯引擎的關(guān)鍵。4.1 IF與IFNULL最簡單的二選一邏輯IF(expr, val1, val2)是SQL里最直白的條件函數(shù)。比如在查詢里標(biāo)記是否大客戶SELECT customer_name, total_amount, IF(total_amount 10000, VIP, 普通) AS customer_level FROM customer_summary;IFNULL(val1, val2)則專注于處理NULL。比如用戶表里nickname為空時(shí)顯示默認(rèn)名SELECT IFNULL(nickname, 匿名用戶) AS display_name FROM users;這里有個(gè)使用習(xí)慣問題我知道很多開發(fā)喜歡在SELECT里對(duì)NULL字段做處理但如果你在大批量導(dǎo)出或報(bào)表統(tǒng)計(jì)時(shí)習(xí)慣用IFNULL它會(huì)在每行上執(zhí)行一次判斷。量級(jí)小無所謂量級(jí)大就要評(píng)估。4.2 CASE WHEN比IF更靈活的多分支流程控制一旦條件超過兩個(gè)CASE WHEN就遠(yuǎn)比嵌套IF可讀性好。它還能和聚合函數(shù)配合做條件統(tǒng)計(jì)。SELECT store_id, SUM(CASE WHEN status completed THEN 1 ELSE 0 END) AS completed_orders, SUM(CASE WHEN status cancelled THEN 1 ELSE 0 END) AS cancelled_orders FROM orders GROUP BY store_id;這種寫法叫條件聚合是替代多條SQL各查一次的最高頻手段。很多人寫日?qǐng)?bào)、周報(bào)時(shí)會(huì)連發(fā)四五條查詢統(tǒng)計(jì)不同狀態(tài)的數(shù)據(jù)其實(shí)一條SQL就能解決。4.3 COALESCE一連串備胎中的第一個(gè)非NULL值COALESCE返回參數(shù)列表里第一個(gè)非NULL的值。它比IFNULL更靈活因?yàn)榭梢越o多個(gè)備選值。比如電商項(xiàng)目里一個(gè)商品可能會(huì)有多個(gè)維度的價(jià)格秒殺價(jià)、折扣價(jià)、原價(jià)。查詢時(shí)希望能拿到當(dāng)前有效的最低價(jià)SELECT product_name, COALESCE(seckill_price, discount_price, original_price) AS final_price FROM products;如果seckill_price為空就用折扣價(jià)再為空用原價(jià)。這個(gè)邏輯如果用CASE WHEN寫會(huì)非常啰嗦COALESCE一行搞定。4.4 CAST與CONVERT類型不對(duì)函數(shù)白學(xué)很多時(shí)候函數(shù)運(yùn)行結(jié)果和預(yù)期不符不是函數(shù)用錯(cuò)了而是數(shù)據(jù)類型根本不對(duì)。CAST和CONVERT就是用來做顯式類型轉(zhuǎn)換的。-- 字符串轉(zhuǎn)數(shù)字注意123abc會(huì)被轉(zhuǎn)成123而abc123會(huì)轉(zhuǎn)成0這個(gè)行為一定要知道 SELECT CAST(123 AS SIGNED); -- 123 SELECT CAST(123abc AS SIGNED); -- 123 SELECT CAST(abc123 AS SIGNED); -- 0 -- 日期轉(zhuǎn)字符串再格式化 SELECT CAST(NOW() AS CHAR); -- CONVERT的風(fēng)格略有不同 SELECT CONVERT(2025-01-15, DATE);轉(zhuǎn)換函數(shù)的隱式規(guī)則很容易埋雷尤其是字符串轉(zhuǎn)數(shù)字時(shí)MySQL會(huì)盡可能提取開頭的數(shù)字部分提取不到就返回0。在對(duì)接外部導(dǎo)入的數(shù)據(jù)時(shí)一定要先跑查詢檢查轉(zhuǎn)換結(jié)果否則容易把臟數(shù)據(jù)洗成看似正常的0。5. 內(nèi)置函數(shù)的組合實(shí)戰(zhàn)從需求描述到一條SQL的完整推導(dǎo)函數(shù)單獨(dú)講都很簡單真正考驗(yàn)功力的是把它們組合起來解決實(shí)際業(yè)務(wù)問題。這一節(jié)我用兩個(gè)真實(shí)案例完整走一遍從需求到SQL的推導(dǎo)過程。5.1 實(shí)戰(zhàn)案例一會(huì)員活躍周期統(tǒng)計(jì)需求描述統(tǒng)計(jì)每個(gè)會(huì)員在過去90天內(nèi)的活躍天數(shù)并且要區(qū)分工作日和周末活躍天數(shù)。先拆解過去90天要用DATE_SUB(NOW(), INTERVAL 90 DAY)作為起點(diǎn)活躍天數(shù)意味著要對(duì)每天的去重用COUNT(DISTINCT DATE(login_time))工作日/周末用DAYOFWEEK()判斷注意MySQL里DAYOFWEEK返回1周日2周一所以工作日是2到6。最終SQL大概長這樣SELECT user_id, COUNT(DISTINCT DATE(login_time)) AS active_days, SUM(CASE WHEN DAYOFWEEK(login_time) BETWEEN 2 AND 6 THEN 1 ELSE 0 END) AS weekday_active_days, SUM(CASE WHEN DAYOFWEEK(login_time) IN (1, 7) THEN 1 ELSE 0 END) AS weekend_active_days FROM login_log WHERE login_time DATE_SUB(CURDATE(), INTERVAL 90 DAY) GROUP BY user_id;注意CASE WHEN里用了SUM而不是COUNT因?yàn)橐葱欣奂訚M足條件的記錄數(shù)。這種寫法剛?cè)腴T的人容易寫成COUNT(CASE WHEN ...)結(jié)果永遠(yuǎn)返回總行數(shù)因?yàn)镃OUNT只看值是否非NULL對(duì)0和1都計(jì)數(shù)。要用SUM包CASE WHEN或者讓CASE WHEN返回NULL而不是0兩種選其一。5.2 實(shí)戰(zhàn)案例二商品價(jià)格脫敏與分檔需求描述管理后臺(tái)需要展示商品名稱、價(jià)格檔位、脫敏后的商品編碼。拆解思路商品編碼脫敏編碼規(guī)則是類別-流水號(hào)要把流水號(hào)中間兩位用*替換用SUBSTRING_INDEX和CONCAT組合。價(jià)格檔位用CASE WHEN分檔位。SELECT product_name, CASE WHEN price 50 THEN 廉價(jià) WHEN price 200 THEN 平價(jià) WHEN price 1000 THEN 中高端 ELSE 高端 END AS price_tier, CONCAT( SUBSTRING_INDEX(product_code, -, 1), -, REPLACE(SUBSTRING_INDEX(product_code, -, -1), SUBSTRING(SUBSTRING_INDEX(product_code, -, -1), 3, 2), **) ) AS masked_code FROM products;這段代碼的REPLACE部分有些繞但對(duì)這種固定格式編碼的脫敏非常有效而且不需要額外寫存儲(chǔ)過程。組合使用函數(shù)的原則是什么我的經(jīng)驗(yàn)是先在獨(dú)立的SELECT里驗(yàn)證每個(gè)函數(shù)的返回值確認(rèn)無誤再層層嵌套。SQL嵌套一旦超過三層可讀性和排錯(cuò)成本都會(huì)直線上升不要一上來就憋大招。5.3 函數(shù)嵌套的Debug技巧逐步拆解驗(yàn)證法寫復(fù)雜SQL時(shí)我?guī)缀醪挥胐ebugger就用拆解法。把整個(gè)表達(dá)式拆成幾步先在查詢里單獨(dú)看每一步的結(jié)果-- 第1步單獨(dú)看提取部分 SELECT product_code, SUBSTRING_INDEX(product_code, -, 1) AS part1, SUBSTRING_INDEX(product_code, -, -1) AS part2, SUBSTRING(SUBSTRING_INDEX(product_code, -, -1), 3, 2) AS mask_target FROM products LIMIT 10;跑到這一步你就能清楚看到每一層返回了什么。如果某一步返回NULL說明分割符或邊界條件與預(yù)期不符立刻就能定位。這比寫好一大段SQL然后對(duì)著報(bào)錯(cuò)信息猜高效得多。6. 避坑清單與性能紅線一次大查詢教會(huì)我的事最后這部分我把這些年踩過和見過的內(nèi)置函數(shù)相關(guān)的大坑打包整理一下。很多問題不是函數(shù)本身復(fù)雜而是大家默認(rèn)函數(shù)嘛隨手一寫不就行了結(jié)果一上線就出事。6.1 函數(shù)使用導(dǎo)致索引失效的情況這是性能問題里最高頻的一條。對(duì)索引列使用函數(shù)會(huì)導(dǎo)致索引失效。-- 全表掃描索引失效 SELECT * FROM orders WHERE DATE(create_time) 2025-01-15; -- 走索引的范圍查詢 SELECT * FROM orders WHERE create_time 2025-01-15 00:00:00 AND create_time 2025-01-16 00:00:00;同樣的問題也出現(xiàn)在字符串函數(shù)上。比如你想查姓氏為張的用戶如果寫了WHERE SUBSTRING(name, 1, 1) 張索引就廢了。正確做法是WHERE name LIKE 張%。帶REGEXP的查詢更是如此它基本沒有索引可走。大數(shù)據(jù)量下要做正則匹配應(yīng)盡量在應(yīng)用層處理或者引入搜索引擎而不是直接在MySQL里硬扛。6.2 隱式類型轉(zhuǎn)換函數(shù)參數(shù)的類型陷阱前面在CAST部分提到過字符串轉(zhuǎn)數(shù)字時(shí)會(huì)盡量解析數(shù)字前綴。這個(gè)特性在隱式轉(zhuǎn)換上會(huì)帶來很隱蔽的BUG。比如下面這條SELECT * FROM users WHERE phone 13812345678;如果phone列是VARCHAR類型MySQL會(huì)把右邊的數(shù)字轉(zhuǎn)成字符串去比較結(jié)果看起來沒問題。但一旦phonel列存的是138-1234-5678這類帶格式的數(shù)據(jù)比較結(jié)果就完全不可控了。所以開發(fā)規(guī)范里要有一條規(guī)定字符串列與數(shù)字值比較時(shí)永遠(yuǎn)顯式轉(zhuǎn)換別靠MySQL隱式處理。6.3 NULL參與運(yùn)算的結(jié)果COALESCE的進(jìn)一步運(yùn)用任何普通函數(shù)只要參數(shù)里帶NULL結(jié)果大概率是NULL。算術(shù)運(yùn)算也一樣NULL 1還是NULL。所以在做金額匯總時(shí)如果某個(gè)字段允許NULLSUM會(huì)自動(dòng)忽略但如果你在SELECT里用普通算術(shù)做了額外的計(jì)算比如SELECT amount * discount_rate FROM orders;一旦discount_rate為NULL整行金額都變成NULL。處理方式是COALESCE(discount_rate, 1)SELECT amount * COALESCE(discount_rate, 1) FROM orders;這算是一個(gè)黃金習(xí)慣涉及可能為空的字段做四則運(yùn)算之前先想一下要不要用COALESCE兜底。6.4 日期函數(shù)使用中的其他常見坑月末、閏年、夏令時(shí)日期函數(shù)還有個(gè)很少人提但很麻煩的點(diǎn)月末和夏令時(shí)。計(jì)算上個(gè)月最后一天如果用DATE_SUB(DATE_SUB(..., INTERVAL DAY(...)-1 DAY), INTERVAL 1 DAY)這類方式非常容易出錯(cuò)。MySQL 8.0提供了LAST_DAY函數(shù)直接返回所在月的最后一天SELECT LAST_DAY(2025-02-01); -- 2025-02-28 SELECT LAST_DAY(2024-02-01); -- 2024-02-29閏年夏令時(shí)影響的是TIMESTAMP類型的計(jì)算。如果業(yè)務(wù)服務(wù)器設(shè)定了非UTC時(shí)區(qū)且使用夏令時(shí)某些日期不存在跨時(shí)切換時(shí)刻處理起來極其復(fù)雜。國內(nèi)沒有夏令時(shí)問題但如果你維護(hù)的是海外業(yè)務(wù)一定要用UTC存儲(chǔ)、展示層再轉(zhuǎn)換不要在數(shù)據(jù)庫層做時(shí)區(qū)換算。6.5 存儲(chǔ)過程里使用函數(shù)的注意點(diǎn)最后提醒一句寫在存儲(chǔ)過程或觸發(fā)器里的人函數(shù)在存儲(chǔ)過程中每次調(diào)用都有開銷而且對(duì)NULL的處理邏輯不變。如果存儲(chǔ)過程里循環(huán)逐行調(diào)用函數(shù)性能會(huì)很難看。盡量把函數(shù)調(diào)用放到SQL語句本身的力量里靠集合操作而不是循環(huán)。比如你要給一批用戶更新最后登錄日期就寫一條UPDATE配合NOW()而不是開一個(gè)游標(biāo)逐行更新UPDATE users SET last_login_at NOW() WHERE user_id IN (...);這種集合式寫法既簡潔又高效也讓內(nèi)置函數(shù)的價(jià)值真正發(fā)揮出來。MySQL內(nèi)置函數(shù)這組工具說到底是讓你在工作中少寫幾百行Java或Python的日期處理、字符串處理代碼。掌握它們的正確姿勢不只是背函數(shù)名而是要理解數(shù)據(jù)類型、NULL語義和性能影響。希望這份拆解能幫你在日常SQL里少踩幾個(gè)坑。 MySQL內(nèi)置函數(shù)實(shí)戰(zhàn)指南：日期、字符串與流程控制函數(shù)核心用法解析
1. 日期函數(shù)的正確打開方式別只沉浸在NOW()里做MySQL開發(fā)這些年我見過最多的SQL問題一半以上都出在日期處理上。很多人寫NOW()取當(dāng)前時(shí)間用得飛起但一遇到上周一的訂單量上個(gè)月的注冊用戶數(shù)這類需求就開始頭疼了。其實(shí)MySQL的日期函數(shù)遠(yuǎn)比你想象中強(qiáng)大但前提是你要理解它內(nèi)部的存儲(chǔ)邏輯。1.1 日期存儲(chǔ)的底層邏輯為什么建議用DATETIME而不是字符串先說一個(gè)最基礎(chǔ)也最關(guān)鍵的認(rèn)知MySQL的日期時(shí)間在底層是數(shù)字形式存儲(chǔ)的所謂2025-01-15 10:30:00只是它展示給你的樣子。所以日期函數(shù)干的事本質(zhì)上就是在各種數(shù)字格式之間做轉(zhuǎn)換。你應(yīng)該見過有人用字符串比較日期SELECT * FROM orders WHERE order_time 2025-01-15;這條SQL在大多數(shù)情況下能跑通但它有一個(gè)隱患如果order_time列是DATETIME類型而2025-01-15只是字符串MySQL需要先把字符串隱式轉(zhuǎn)換成日期再比較。隱式轉(zhuǎn)換一旦發(fā)生索引就失效了數(shù)據(jù)量大時(shí)全表掃描就是必然結(jié)局。正確做法是SELECT * FROM orders WHERE order_time 2025-01-15 00:00:00 AND order_time 2025-01-16 00:00:00;或者直接使用日期函數(shù)把列值規(guī)范化SELECT * FROM orders WHERE DATE(order_time) 2025-01-15;注意DATE(order_time)雖然寫法干凈但同樣無法使用索引因?yàn)槊恳恍卸家冗^一遍函數(shù)。如果表很大更推薦范圍寫法這類問題我在后面章節(jié)單獨(dú)展開講。1.2 高頻日期函數(shù)逐個(gè)拆解NOW、DATE_FORMAT、DATEDIFF在真實(shí)場景中的用法我把日常用得最多的日期函數(shù)整理成了下面這張速查表每個(gè)都附帶了使用場景。函數(shù)作用典型場景NOW()返回當(dāng)前日期時(shí)間記錄操作時(shí)間、更新updated_atCURDATE()返回當(dāng)前日期無時(shí)間統(tǒng)計(jì)當(dāng)天下單量DATE_FORMAT(date, fmt)按指定格式輸出日期前端展示、報(bào)表分組DATEDIFF(d1, d2)返回兩個(gè)日期相差的天數(shù)計(jì)算賬齡、會(huì)員活躍天數(shù)DATE_ADD(date, INTERVAL n unit)日期加減計(jì)算到期日、回溯統(tǒng)計(jì)窗口YEAR()/MONTH()/DAY()提取日期部分按月/年分組統(tǒng)計(jì)DAYOFWEEK()返回星期索引1周日判斷周末UNIX_TIMESTAMP()轉(zhuǎn)成時(shí)間戳與前端/其他系統(tǒng)對(duì)接這里重點(diǎn)說DATE_FORMAT它是報(bào)表開發(fā)中最常用的一個(gè)。比如要按小時(shí)統(tǒng)計(jì)訂單量SELECT DATE_FORMAT(create_time, %Y-%m-%d %H:00:00) AS hour_slot, COUNT(*) AS order_cnt FROM orders WHERE create_time DATE_SUB(NOW(), INTERVAL 24 HOUR) GROUP BY hour_slot ORDER BY hour_slot;格式化占位符有嚴(yán)格區(qū)分%Y和%y不一樣四位年份和兩位年份%m和%i也不一樣月份和分鐘首次用容易寫錯(cuò)。我建議直接記常用格式定期查閱官方文檔核對(duì)別憑印象寫。1.3 時(shí)區(qū)帶來的坑TIMESTAMP和DATETIME的選擇另一個(gè)高頻翻車點(diǎn)是TIMESTAMP和DATETIME的時(shí)區(qū)差異。TIMESTAMP是UTC存儲(chǔ)、查詢時(shí)按會(huì)話時(shí)區(qū)轉(zhuǎn)換而DATETIME是原樣存儲(chǔ)不帶時(shí)區(qū)信息。如果你的數(shù)據(jù)庫連接串設(shè)置了serverTimezoneAsia/Shanghai但部署服務(wù)器實(shí)際是UTC時(shí)區(qū)那NOW()取到的結(jié)果會(huì)憑空差8個(gè)小時(shí)。這個(gè)坑我實(shí)測遇到過不止一次排查時(shí)往往先懷疑代碼最后才發(fā)現(xiàn)是時(shí)區(qū)配置有偏差。我的建議是統(tǒng)一約定所有環(huán)境統(tǒng)一使用CST或UTC08:00時(shí)區(qū)連接串、服務(wù)器時(shí)區(qū)、MySQL的time_zone變量三者保持一致。表結(jié)構(gòu)選型如果需要跨時(shí)區(qū)使用或者對(duì)接的是全球化業(yè)務(wù)優(yōu)先TIMESTAMP如果只是國內(nèi)業(yè)務(wù)、要精確存儲(chǔ)用戶輸入的日期時(shí)間DATETIME更直觀。上線前自查寫一條SELECT NOW(), 連接到測試庫對(duì)比一下如果和服務(wù)器當(dāng)前時(shí)間不一致說明時(shí)區(qū)配置有問題。2. 字符串函數(shù)數(shù)據(jù)處理和報(bào)表清洗的核心彈藥庫日期函數(shù)解決時(shí)間怎么算的問題字符串函數(shù)則是解決字段怎么撕的問題。在實(shí)際業(yè)務(wù)里字符串處理幾乎每天都在發(fā)生——用戶昵稱帶特殊字符、手機(jī)號(hào)中間四位要脫敏、商品編碼要截取前綴分類、多張小表要拼接成一張寬表。2.1 CONCAT家族拼接的正確姿勢與NULL陷阱先看最基礎(chǔ)的拼接。常見的三種寫法是CONCAT、CONCAT_WS和直接在SQL里用||取決于sql_mode是否開啟了PIPES_AS_CONCAT。-- 常規(guī)拼接 SELECT CONCAT(first_name, last_name) AS full_name FROM users; -- 帶分隔符拼接第二個(gè)參數(shù)是分隔符 SELECT CONCAT_WS(-, province, city, district) AS full_address FROM users; -- 注意如果first_name為NULLCONCAT返回NULL SELECT CONCAT(first_name, COALESCE(last_name, )) FROM users;這里必須單獨(dú)說CONCAT和NULL的行為。很多人以為CONCAT(NULL, abc)會(huì)返回abc但MySQL的實(shí)際行為是返回NULL。要處理NULL要么用IFNULL包一層要么用CONCAT_WS——它有自動(dòng)跳過NULL的能力算是個(gè)冷門優(yōu)點(diǎn)。我在日常寫項(xiàng)目時(shí)喜歡用CONCAT_WS多于CONCAT因?yàn)樘幚磬]政編碼、地址這種帶分隔符的拼接時(shí)不用手動(dòng)處理NULL和多余的-。2.2 SUBSTRING、LEFT、RIGHT準(zhǔn)確裁出你需要的那段截取函數(shù)在脫敏、格式清洗里常駐。函數(shù)作用示例結(jié)果LEFT(s, n)取左側(cè)n個(gè)字符LEFT(13812345678, 3)138RIGHT(s, n)取右側(cè)n個(gè)字符RIGHT(13812345678, 4)5678SUBSTRING(s, pos, len)從位置pos開始取len個(gè)SUBSTRING(MySQL, 2, 3)ySQSUBSTRING_INDEX(s, delim, n)按分隔符截取SUBSTRING_INDEX(a,b,c, ,, 2)a,b脫敏的經(jīng)典寫法是SELECT CONCAT(LEFT(phone, 3), ****, RIGHT(phone, 4)) AS masked_phone FROM users;凡是涉及拼接和截取建議先在SELECT里跑一條不帶WHERE的語句驗(yàn)證結(jié)果再套進(jìn)UPDATE或大批量處理避免因?yàn)镹ULL或長度不足產(chǎn)生臟數(shù)據(jù)。2.3 REPLACE、TRIM和大小寫轉(zhuǎn)換清洗數(shù)據(jù)三件套從Excel導(dǎo)入的數(shù)據(jù)經(jīng)常帶空格、全角字符或者字段里混著不一致的大小寫。這時(shí)候用TRIM、REPLACE和大小寫函數(shù)組合就能快速清洗。-- 去除兩端空格 SELECT TRIM( hello ); -- 結(jié)果為 hello -- 去除兩端指定字符注意是去除兩端出現(xiàn)的所有該字符 SELECT TRIM(BOTH x FROM xxxMySQLxxx); -- 結(jié)果為 MySQL -- 替換字符串中的特定內(nèi)容 SELECT REPLACE(2025-01-15, -, /); -- 結(jié)果為 2025/01/15 -- 大小寫標(biāo)準(zhǔn)化 SELECT LOWER(MySQL), UPPER(mysql);還有LPAD和RPAD這類補(bǔ)位函數(shù)做流水號(hào)、訂單號(hào)格式化時(shí)很有用。比如把訂單號(hào)補(bǔ)齊到8位SELECT LPAD(order_id, 8, 0) FROM orders;2.4 字符串切分與正則匹配比LIKE更強(qiáng)大的REGEXP模糊匹配大家都會(huì)用LIKE但遇到以A開頭、中間包含數(shù)字、以B結(jié)尾這種組合條件時(shí)LIKE就很痛苦。MySQL的正則表達(dá)式函數(shù)REGEXP能幫你解決這類需求。-- 查找手機(jī)號(hào)以138開頭的用戶 SELECT * FROM users WHERE phone REGEXP ^138; -- 查找郵箱屬于任意主流免費(fèi)服務(wù)商163/qq/126的用戶 SELECT * FROM users WHERE email REGEXP (163|qq|126)\.com$;REGEXP在MySQL 8.0中升級(jí)成了REGEXP_LIKE、REGEXP_REPLACE、REGEXP_SUBSTR等新函數(shù)功能更強(qiáng)。但要注意正則匹配沒法像LIKE prefix%那樣利用普通索引數(shù)據(jù)量大了要謹(jǐn)慎。3. 數(shù)學(xué)函數(shù)報(bào)表和積分系統(tǒng)里的實(shí)用計(jì)算工具數(shù)學(xué)函數(shù)不像日期和字符串用得那么密但一碰上就會(huì)用到尤其是做報(bào)表、算金額、抽獎(jiǎng)、排名這些場景。3.1 ROUND、FLOOR、CEILING取整不只是四舍五入說到取整大多數(shù)人第一反應(yīng)是ROUND但業(yè)務(wù)中向下取整和向上取整的需求也不少見。-- 四舍五入到兩位小數(shù) SELECT ROUND(3.14159, 2); -- 3.14 -- 向下取整到整數(shù) SELECT FLOOR(3.999); -- 3 -- 向上取整到整數(shù) SELECT CEILING(3.001); -- 4 -- 截?cái)嘈?shù)直接丟掉指定位之后的內(nèi)容 SELECT TRUNCATE(3.14159, 2); -- 3.14一個(gè)容易踩的坑ROUND在MySQL里對(duì).5的處理遵循四舍五入在部分語言里是銀行家舍入所以ROUND(2.5)結(jié)果是3ROUND(3.5)結(jié)果是4這一點(diǎn)與Python的round不同。如果團(tuán)隊(duì)內(nèi)有多種語言混合開發(fā)務(wù)必確認(rèn)統(tǒng)一口徑。3.2 RAND()抽獎(jiǎng)和隨機(jī)取樣的玩法RAND()返回0到1之間的隨機(jī)小數(shù)常用于隨機(jī)排序和抽樣。-- 隨機(jī)取5條記錄 SELECT * FROM products ORDER BY RAND() LIMIT 5; -- 生成指定范圍的隨機(jī)整數(shù)比如1到100 SELECT FLOOR(RAND() * 100) 1;但是ORDER BY RAND()在大表上性能很糟糕因?yàn)樗鼤?huì)對(duì)全表每行生成隨機(jī)數(shù)再排序。數(shù)據(jù)量超過幾萬行時(shí)我更推薦先SELECT COUNT(*)得到總數(shù)然后在應(yīng)用層隨機(jī)取一個(gè)偏移量再LIMIT 1配合OFFSET取數(shù)。這是典型的看似簡單、實(shí)則費(fèi)性能的場景。3.3 聚合函數(shù)中的數(shù)學(xué)函數(shù)SUM、AVG遇上NULL的規(guī)則嚴(yán)格來說SUM和AVG不是數(shù)學(xué)函數(shù)而是聚合函數(shù)但它們離不開數(shù)學(xué)計(jì)算所以一起說。關(guān)鍵規(guī)則是聚合函數(shù)會(huì)忽略NULL值。AVG不會(huì)把NULL當(dāng)作0來算這一點(diǎn)很多人會(huì)搞錯(cuò)。比如一個(gè)班有10個(gè)人其中一個(gè)人缺考AVG(score)算的是9個(gè)人的平均分而不是總分除以10。另外一個(gè)高頻需求是按金額區(qū)間統(tǒng)計(jì)可以配合數(shù)學(xué)函數(shù)分組SELECT CASE WHEN amount 100 THEN 0-100 WHEN amount 500 THEN 100-500 ELSE 500 END AS amount_bucket, COUNT(*), SUM(amount) FROM orders GROUP BY amount_bucket;這種分組不直接用數(shù)學(xué)函數(shù)但CASE WHEN表達(dá)式本身就是一個(gè)轉(zhuǎn)譯函數(shù)配合聚合能做出非常靈活的分檔統(tǒng)計(jì)。4. 其他內(nèi)置函數(shù)控制流程、判空與類型轉(zhuǎn)換的組合藝術(shù)MySQL的函數(shù)體系里除了按數(shù)據(jù)類型劃分的日期、字符串、數(shù)學(xué)函數(shù)還有一大批通用函數(shù)負(fù)責(zé)流程控制、空值處理和類型轉(zhuǎn)換。它們是讓SQL從一個(gè)查詢工具變成業(yè)務(wù)邏輯引擎的關(guān)鍵。4.1 IF與IFNULL最簡單的二選一邏輯IF(expr, val1, val2)是SQL里最直白的條件函數(shù)。比如在查詢里標(biāo)記是否大客戶SELECT customer_name, total_amount, IF(total_amount 10000, VIP, 普通) AS customer_level FROM customer_summary;IFNULL(val1, val2)則專注于處理NULL。比如用戶表里nickname為空時(shí)顯示默認(rèn)名SELECT IFNULL(nickname, 匿名用戶) AS display_name FROM users;這里有個(gè)使用習(xí)慣問題我知道很多開發(fā)喜歡在SELECT里對(duì)NULL字段做處理但如果你在大批量導(dǎo)出或報(bào)表統(tǒng)計(jì)時(shí)習(xí)慣用IFNULL它會(huì)在每行上執(zhí)行一次判斷。量級(jí)小無所謂量級(jí)大就要評(píng)估。4.2 CASE WHEN比IF更靈活的多分支流程控制一旦條件超過兩個(gè)CASE WHEN就遠(yuǎn)比嵌套IF可讀性好。它還能和聚合函數(shù)配合做條件統(tǒng)計(jì)。SELECT store_id, SUM(CASE WHEN status completed THEN 1 ELSE 0 END) AS completed_orders, SUM(CASE WHEN status cancelled THEN 1 ELSE 0 END) AS cancelled_orders FROM orders GROUP BY store_id;這種寫法叫條件聚合是替代多條SQL各查一次的最高頻手段。很多人寫日?qǐng)?bào)、周報(bào)時(shí)會(huì)連發(fā)四五條查詢統(tǒng)計(jì)不同狀態(tài)的數(shù)據(jù)其實(shí)一條SQL就能解決。4.3 COALESCE一連串備胎中的第一個(gè)非NULL值COALESCE返回參數(shù)列表里第一個(gè)非NULL的值。它比IFNULL更靈活因?yàn)榭梢越o多個(gè)備選值。比如電商項(xiàng)目里一個(gè)商品可能會(huì)有多個(gè)維度的價(jià)格秒殺價(jià)、折扣價(jià)、原價(jià)。查詢時(shí)希望能拿到當(dāng)前有效的最低價(jià)SELECT product_name, COALESCE(seckill_price, discount_price, original_price) AS final_price FROM products;如果seckill_price為空就用折扣價(jià)再為空用原價(jià)。這個(gè)邏輯如果用CASE WHEN寫會(huì)非常啰嗦COALESCE一行搞定。4.4 CAST與CONVERT類型不對(duì)函數(shù)白學(xué)很多時(shí)候函數(shù)運(yùn)行結(jié)果和預(yù)期不符不是函數(shù)用錯(cuò)了而是數(shù)據(jù)類型根本不對(duì)。CAST和CONVERT就是用來做顯式類型轉(zhuǎn)換的。-- 字符串轉(zhuǎn)數(shù)字注意123abc會(huì)被轉(zhuǎn)成123而abc123會(huì)轉(zhuǎn)成0這個(gè)行為一定要知道 SELECT CAST(123 AS SIGNED); -- 123 SELECT CAST(123abc AS SIGNED); -- 123 SELECT CAST(abc123 AS SIGNED); -- 0 -- 日期轉(zhuǎn)字符串再格式化 SELECT CAST(NOW() AS CHAR); -- CONVERT的風(fēng)格略有不同 SELECT CONVERT(2025-01-15, DATE);轉(zhuǎn)換函數(shù)的隱式規(guī)則很容易埋雷尤其是字符串轉(zhuǎn)數(shù)字時(shí)MySQL會(huì)盡可能提取開頭的數(shù)字部分提取不到就返回0。在對(duì)接外部導(dǎo)入的數(shù)據(jù)時(shí)一定要先跑查詢檢查轉(zhuǎn)換結(jié)果否則容易把臟數(shù)據(jù)洗成看似正常的0。5. 內(nèi)置函數(shù)的組合實(shí)戰(zhàn)從需求描述到一條SQL的完整推導(dǎo)函數(shù)單獨(dú)講都很簡單真正考驗(yàn)功力的是把它們組合起來解決實(shí)際業(yè)務(wù)問題。這一節(jié)我用兩個(gè)真實(shí)案例完整走一遍從需求到SQL的推導(dǎo)過程。5.1 實(shí)戰(zhàn)案例一會(huì)員活躍周期統(tǒng)計(jì)需求描述統(tǒng)計(jì)每個(gè)會(huì)員在過去90天內(nèi)的活躍天數(shù)并且要區(qū)分工作日和周末活躍天數(shù)。先拆解過去90天要用DATE_SUB(NOW(), INTERVAL 90 DAY)作為起點(diǎn)活躍天數(shù)意味著要對(duì)每天的去重用COUNT(DISTINCT DATE(login_time))工作日/周末用DAYOFWEEK()判斷注意MySQL里DAYOFWEEK返回1周日2周一所以工作日是2到6。最終SQL大概長這樣SELECT user_id, COUNT(DISTINCT DATE(login_time)) AS active_days, SUM(CASE WHEN DAYOFWEEK(login_time) BETWEEN 2 AND 6 THEN 1 ELSE 0 END) AS weekday_active_days, SUM(CASE WHEN DAYOFWEEK(login_time) IN (1, 7) THEN 1 ELSE 0 END) AS weekend_active_days FROM login_log WHERE login_time DATE_SUB(CURDATE(), INTERVAL 90 DAY) GROUP BY user_id;注意CASE WHEN里用了SUM而不是COUNT因?yàn)橐葱欣奂訚M足條件的記錄數(shù)。這種寫法剛?cè)腴T的人容易寫成COUNT(CASE WHEN ...)結(jié)果永遠(yuǎn)返回總行數(shù)因?yàn)镃OUNT只看值是否非NULL對(duì)0和1都計(jì)數(shù)。要用SUM包CASE WHEN或者讓CASE WHEN返回NULL而不是0兩種選其一。5.2 實(shí)戰(zhàn)案例二商品價(jià)格脫敏與分檔需求描述管理后臺(tái)需要展示商品名稱、價(jià)格檔位、脫敏后的商品編碼。拆解思路商品編碼脫敏編碼規(guī)則是類別-流水號(hào)要把流水號(hào)中間兩位用*替換用SUBSTRING_INDEX和CONCAT組合。價(jià)格檔位用CASE WHEN分檔位。SELECT product_name, CASE WHEN price 50 THEN 廉價(jià) WHEN price 200 THEN 平價(jià) WHEN price 1000 THEN 中高端 ELSE 高端 END AS price_tier, CONCAT( SUBSTRING_INDEX(product_code, -, 1), -, REPLACE(SUBSTRING_INDEX(product_code, -, -1), SUBSTRING(SUBSTRING_INDEX(product_code, -, -1), 3, 2), **) ) AS masked_code FROM products;這段代碼的REPLACE部分有些繞但對(duì)這種固定格式編碼的脫敏非常有效而且不需要額外寫存儲(chǔ)過程。組合使用函數(shù)的原則是什么我的經(jīng)驗(yàn)是先在獨(dú)立的SELECT里驗(yàn)證每個(gè)函數(shù)的返回值確認(rèn)無誤再層層嵌套。SQL嵌套一旦超過三層可讀性和排錯(cuò)成本都會(huì)直線上升不要一上來就憋大招。5.3 函數(shù)嵌套的Debug技巧逐步拆解驗(yàn)證法寫復(fù)雜SQL時(shí)我?guī)缀醪挥胐ebugger就用拆解法。把整個(gè)表達(dá)式拆成幾步先在查詢里單獨(dú)看每一步的結(jié)果-- 第1步單獨(dú)看提取部分 SELECT product_code, SUBSTRING_INDEX(product_code, -, 1) AS part1, SUBSTRING_INDEX(product_code, -, -1) AS part2, SUBSTRING(SUBSTRING_INDEX(product_code, -, -1), 3, 2) AS mask_target FROM products LIMIT 10;跑到這一步你就能清楚看到每一層返回了什么。如果某一步返回NULL說明分割符或邊界條件與預(yù)期不符立刻就能定位。這比寫好一大段SQL然后對(duì)著報(bào)錯(cuò)信息猜高效得多。6. 避坑清單與性能紅線一次大查詢教會(huì)我的事最后這部分我把這些年踩過和見過的內(nèi)置函數(shù)相關(guān)的大坑打包整理一下。很多問題不是函數(shù)本身復(fù)雜而是大家默認(rèn)函數(shù)嘛隨手一寫不就行了結(jié)果一上線就出事。6.1 函數(shù)使用導(dǎo)致索引失效的情況這是性能問題里最高頻的一條。對(duì)索引列使用函數(shù)會(huì)導(dǎo)致索引失效。-- 全表掃描索引失效 SELECT * FROM orders WHERE DATE(create_time) 2025-01-15; -- 走索引的范圍查詢 SELECT * FROM orders WHERE create_time 2025-01-15 00:00:00 AND create_time 2025-01-16 00:00:00;同樣的問題也出現(xiàn)在字符串函數(shù)上。比如你想查姓氏為張的用戶如果寫了WHERE SUBSTRING(name, 1, 1) 張索引就廢了。正確做法是WHERE name LIKE 張%。帶REGEXP的查詢更是如此它基本沒有索引可走。大數(shù)據(jù)量下要做正則匹配應(yīng)盡量在應(yīng)用層處理或者引入搜索引擎而不是直接在MySQL里硬扛。6.2 隱式類型轉(zhuǎn)換函數(shù)參數(shù)的類型陷阱前面在CAST部分提到過字符串轉(zhuǎn)數(shù)字時(shí)會(huì)盡量解析數(shù)字前綴。這個(gè)特性在隱式轉(zhuǎn)換上會(huì)帶來很隱蔽的BUG。比如下面這條SELECT * FROM users WHERE phone 13812345678;如果phone列是VARCHAR類型MySQL會(huì)把右邊的數(shù)字轉(zhuǎn)成字符串去比較結(jié)果看起來沒問題。但一旦phonel列存的是138-1234-5678這類帶格式的數(shù)據(jù)比較結(jié)果就完全不可控了。所以開發(fā)規(guī)范里要有一條規(guī)定字符串列與數(shù)字值比較時(shí)永遠(yuǎn)顯式轉(zhuǎn)換別靠MySQL隱式處理。6.3 NULL參與運(yùn)算的結(jié)果COALESCE的進(jìn)一步運(yùn)用任何普通函數(shù)只要參數(shù)里帶NULL結(jié)果大概率是NULL。算術(shù)運(yùn)算也一樣NULL 1還是NULL。所以在做金額匯總時(shí)如果某個(gè)字段允許NULLSUM會(huì)自動(dòng)忽略但如果你在SELECT里用普通算術(shù)做了額外的計(jì)算比如SELECT amount * discount_rate FROM orders;一旦discount_rate為NULL整行金額都變成NULL。處理方式是COALESCE(discount_rate, 1)SELECT amount * COALESCE(discount_rate, 1) FROM orders;這算是一個(gè)黃金習(xí)慣涉及可能為空的字段做四則運(yùn)算之前先想一下要不要用COALESCE兜底。6.4 日期函數(shù)使用中的其他常見坑月末、閏年、夏令時(shí)日期函數(shù)還有個(gè)很少人提但很麻煩的點(diǎn)月末和夏令時(shí)。計(jì)算上個(gè)月最后一天如果用DATE_SUB(DATE_SUB(..., INTERVAL DAY(...)-1 DAY), INTERVAL 1 DAY)這類方式非常容易出錯(cuò)。MySQL 8.0提供了LAST_DAY函數(shù)直接返回所在月的最后一天SELECT LAST_DAY(2025-02-01); -- 2025-02-28 SELECT LAST_DAY(2024-02-01); -- 2024-02-29閏年夏令時(shí)影響的是TIMESTAMP類型的計(jì)算。如果業(yè)務(wù)服務(wù)器設(shè)定了非UTC時(shí)區(qū)且使用夏令時(shí)某些日期不存在跨時(shí)切換時(shí)刻處理起來極其復(fù)雜。國內(nèi)沒有夏令時(shí)問題但如果你維護(hù)的是海外業(yè)務(wù)一定要用UTC存儲(chǔ)、展示層再轉(zhuǎn)換不要在數(shù)據(jù)庫層做時(shí)區(qū)換算。6.5 存儲(chǔ)過程里使用函數(shù)的注意點(diǎn)最后提醒一句寫在存儲(chǔ)過程或觸發(fā)器里的人函數(shù)在存儲(chǔ)過程中每次調(diào)用都有開銷而且對(duì)NULL的處理邏輯不變。如果存儲(chǔ)過程里循環(huán)逐行調(diào)用函數(shù)性能會(huì)很難看。盡量把函數(shù)調(diào)用放到SQL語句本身的力量里靠集合操作而不是循環(huán)。比如你要給一批用戶更新最后登錄日期就寫一條UPDATE配合NOW()而不是開一個(gè)游標(biāo)逐行更新UPDATE users SET last_login_at NOW() WHERE user_id IN (...);這種集合式寫法既簡潔又高效也讓內(nèi)置函數(shù)的價(jià)值真正發(fā)揮出來。MySQL內(nèi)置函數(shù)這組工具說到底是讓你在工作中少寫幾百行Java或Python的日期處理、字符串處理代碼。掌握它們的正確姿勢不只是背函數(shù)名而是要理解數(shù)據(jù)類型、NULL語義和性能影響。希望這份拆解能幫你在日常SQL里少踩幾個(gè)坑。