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