戰(zhàn)：常用命令與速查手冊)
作為軟件測試工程師日常工作里接觸最多、也最容易繞不開的就是 MySQL 查詢命令。不管是功能測試要造數(shù)據(jù)、接口測試要驗(yàn)證落庫結(jié)果還是排查線上問題時(shí)要去查一條訂單的狀態(tài)SQL 能力直接決定你的測試效率。這里說的不是讓你像 DBA 一樣做運(yùn)維優(yōu)化而是把最常用的查詢命令整理成一套能夠直接上手、直接執(zhí)行的實(shí)戰(zhàn)手冊。這篇文章會(huì)把 MySQL 查詢命令按測試實(shí)際使用場景重新組織從連接數(shù)據(jù)庫、單表查詢、條件過濾、聚合統(tǒng)計(jì)、多表關(guān)聯(lián)到測試數(shù)據(jù)造數(shù)和清理、常見連接報(bào)錯(cuò)排查全部用可復(fù)制的 SQL 示例展開。讀者如果是剛轉(zhuǎn)行做軟件測試、或?qū)?MySQL 半生不熟的開發(fā)轉(zhuǎn)測人員可以直接把本文當(dāng)成一份速查手冊。1. 測試工程師必備的 MySQL 查詢能力速覽測試和開發(fā)看數(shù)據(jù)庫的視角不同。開發(fā)更關(guān)心寫接口、調(diào)業(yè)務(wù)邏輯測試更關(guān)心數(shù)據(jù)是否按預(yù)期寫入、狀態(tài)字段是否流轉(zhuǎn)正確、測試環(huán)境的數(shù)據(jù)是否干凈。圍繞這個(gè)目標(biāo)測試工程師最常用的 MySQL 能力可以整理成下面這張表。能力項(xiàng)典型測試應(yīng)用場景使用頻率SELECT 單表查詢查用戶表、訂單表的基本數(shù)據(jù)每天使用WHERE 條件過濾按訂單號、用戶 ID、時(shí)間范圍篩數(shù)據(jù)每天使用ORDER BY 排序驗(yàn)證列表接口的排序邏輯高頻LIMIT 分頁查詢驗(yàn)證分頁接口的返回結(jié)果高頻LIKE 模糊查詢搜索功能測試、按名稱關(guān)鍵字查數(shù)據(jù)高頻CASE WHEN 條件分支把數(shù)據(jù)庫字段值轉(zhuǎn)成業(yè)務(wù)含義再核對中頻聚合函數(shù) COUNT/SUM/AVG驗(yàn)證統(tǒng)計(jì)報(bào)表、列表總條數(shù)高頻GROUP BY 分組統(tǒng)計(jì)驗(yàn)證按狀態(tài)、按品類分組的統(tǒng)計(jì)接口中高頻JOIN 多表關(guān)聯(lián)跨表核對業(yè)務(wù)數(shù)據(jù)完整性高頻子查詢復(fù)雜的多條件過濾、嵌套統(tǒng)計(jì)中頻UPDATE 數(shù)據(jù)訂正測試環(huán)境數(shù)據(jù)訂正、狀態(tài)復(fù)位中頻DELETE 數(shù)據(jù)清理清理測試臟數(shù)據(jù)中頻掌握以上能力之后再去應(yīng)對數(shù)據(jù)庫相關(guān)的測試面試題也會(huì)從容很多。MySQL 面試題里常問的SELECT執(zhí)行順序、WHERE和HAVING的區(qū)別、LEFT JOIN和INNER JOIN的差異本質(zhì)上就是這些基礎(chǔ)查詢命令的延伸。2. 環(huán)境準(zhǔn)備測試工程師怎么連接 MySQL寫查詢命令之前先確認(rèn)你能連上 MySQL。測試環(huán)境通常有開發(fā)或者運(yùn)維提供的數(shù)據(jù)庫賬號你只需要拿到三樣?xùn)|西數(shù)據(jù)庫地址、端口、賬號密碼。連接方式常見有兩種。第一種是命令行客戶端MySQL 安裝后自帶mysql命令。Windows 下需要先把 MySQL 的bin目錄加入系統(tǒng) PATH或者直接在 bin 目錄下打開終端執(zhí)行。Linux 和 macOS 一般可以直接執(zhí)行。mysql -h 192.168.1.100 -P 3306 -u test_user -p執(zhí)行后按提示輸入密碼看到mysql提示符就說明連接成功。這里的-h指定數(shù)據(jù)庫主機(jī)地址-P指定端口默認(rèn) 3306 可以省略-u指定用戶名-p表示需要輸入密碼。如果本地安裝的是 MySQL 8.0默認(rèn)認(rèn)證插件是caching_sha2_password某些老版本客戶端或者工具連接時(shí)會(huì)報(bào)認(rèn)證失敗后面常見問題部分會(huì)專門講。第二種是圖形化工具比如 Navicat、MySQL Workbench、DBeaver。新建連接時(shí)填主機(jī)、端口、用戶名、密碼即可。圖形化工具適合需要頻繁看表結(jié)構(gòu)、導(dǎo)出數(shù)據(jù)、可視化編輯的場景但對測試工程師來說命令行永遠(yuǎn)是兜底方案因?yàn)榫€上排查時(shí)不一定有圖形化工具。連接后先看當(dāng)前數(shù)據(jù)庫列表。SHOW DATABASES;再切換到目標(biāo)數(shù)據(jù)庫。USE test_db;查看當(dāng)前庫下的所有表。SHOW TABLES;查看某張表的結(jié)構(gòu)。DESC test_user;這套命令是測試環(huán)境查數(shù)前的固定熱身動(dòng)作。每次拿到新庫我都建議先跑一遍SHOW DATABASES和SHOW TABLES確認(rèn)環(huán)境沒有連錯(cuò)、表存在再開始寫具體查詢。3. 基礎(chǔ)查詢命令SELECT、WHERE、ORDER BY、LIMIT3.1 SELECT 查詢指定字段測試工程師查數(shù)據(jù)時(shí)最忌諱SELECT *一把梭不是說不能用它而是當(dāng)表字段很多、數(shù)據(jù)量很大時(shí)SELECT *會(huì)把無用字段全部撈出來輸出內(nèi)容太多反而看不清關(guān)鍵數(shù)據(jù)。更推薦的做法是只查自己關(guān)心的字段。SELECT id, user_name, mobile, status FROM test_user;這條命令查詢test_user表中的四個(gè)字段。從執(zhí)行效率看只查必要字段也能減少網(wǎng)絡(luò)傳輸?shù)臄?shù)據(jù)量。3.2 WHERE 條件過濾查詢測試數(shù)據(jù)時(shí)絕大多數(shù)情況都需要帶過濾條件。比如只查某個(gè)訂單號的數(shù)據(jù)、只查狀態(tài)為 1 的用戶、只查某個(gè)時(shí)間段內(nèi)的記錄。SELECT id, order_no, amount, status FROM test_order WHERE status 1;多條件組合時(shí)用AND和OR。注意AND優(yōu)先級高于OR如果條件邏輯比較復(fù)雜建議加括號明確優(yōu)先級。SELECT id, order_no, amount, status FROM test_order WHERE status 1 AND amount 100 AND create_time 2025-01-01;3.3 ORDER BY 排序列表類接口測試時(shí)前端展示的數(shù)據(jù)往往有排序規(guī)則。測試人員需要對照數(shù)據(jù)庫確認(rèn)排序結(jié)果是否符合接口文檔。排序用ORDER BY默認(rèn)是升序ASC需要降序時(shí)用DESC。SELECT id, user_name, create_time FROM test_user ORDER BY create_time DESC;多字段排序時(shí)先按第一個(gè)字段排相同再按第二個(gè)字段排。例如先按狀態(tài)升序再按創(chuàng)建時(shí)間降序。SELECT id, user_name, status, create_time FROM test_user ORDER BY status ASC, create_time DESC;這里有一個(gè)測試中容易踩的坑字符型字段排序不是數(shù)字排序。比如mobile字段如果存的是字符串排序結(jié)果和按數(shù)字排序可能不一致。驗(yàn)證排序邏輯時(shí)要先確認(rèn)字段類型。3.4 LIMIT 分頁查詢分頁接口測試是軟件測試工程師的高頻工作。前端傳page和pageSize后端返回對應(yīng)分頁數(shù)據(jù)。數(shù)據(jù)庫層一般用LIMIT實(shí)現(xiàn)。SELECT id, order_no, amount FROM test_order ORDER BY id LIMIT 0, 10;上面這行表示從第 0 條開始取 10 條對應(yīng)第一頁。第二頁就是LIMIT 10, 10第三頁是LIMIT 20, 10。MySQL 的 LIMIT 語法也可以簡寫成LIMIT 偏移量, 行數(shù)。在 MySQL 8.0 中還可以用LIMIT 行數(shù) OFFSET 偏移量的寫法兩種等價(jià)。SELECT id, order_no, amount FROM test_order ORDER BY id LIMIT 10 OFFSET 10;測試分頁接口時(shí)要注意邊界值是第一頁、最后一頁、頁碼超過總頁數(shù)、頁碼為 0 或負(fù)數(shù)。數(shù)據(jù)庫層面對應(yīng)驗(yàn)證的是LIMIT偏移量計(jì)算是否正確以及偏移量超過數(shù)據(jù)總量時(shí)返回空結(jié)果但不會(huì)報(bào)錯(cuò)。3.5 DISTINCT 去重查詢測試中需要確認(rèn)某張表中不同取值的數(shù)量時(shí)可以用DISTINCT去重。例如查看訂單表中存在哪些狀態(tài)值。SELECT DISTINCT status FROM test_order;也可以統(tǒng)計(jì)去重后的數(shù)量。SELECT COUNT(DISTINCT user_id) FROM test_order;這行命令可以快速判斷某個(gè)用戶是否下過單也是造數(shù)時(shí)檢查重復(fù)數(shù)據(jù)的常用手段。4. 條件過濾實(shí)戰(zhàn)LIKE、IN、BETWEEN、CASE WHEN4.1 LIKE 模糊查詢搜索功能測試時(shí)前端輸入關(guān)鍵字后端一般使用LIKE做模糊匹配。數(shù)據(jù)庫層面的驗(yàn)證就是看LIKE查詢能否返回符合條件的數(shù)據(jù)。SELECT id, user_name FROM test_user WHERE user_name LIKE 張%;%代表任意長度的字符_代表單個(gè)字符。張%表示以“張”開頭%張%表示包含“張”張_表示以“張”開頭且后面只有一個(gè)字符。測試搜索接口時(shí)需要關(guān)注大小寫敏感問題。MySQL 默認(rèn)的排序規(guī)則utf8_general_ci是不區(qū)分大小寫的LIKE abc%也能匹配ABC。如果業(yè)務(wù)要求區(qū)分大小寫需要使用utf8_bin排序規(guī)則或者BINARY關(guān)鍵字。SELECT id, user_name FROM test_user WHERE user_name LIKE BINARY Abc%;4.2 IN 和 BETWEENIN用來匹配多個(gè)值等價(jià)于多個(gè)OR條件。例如查詢狀態(tài)為 1、2、3 的訂單。SELECT id, order_no, status FROM test_order WHERE status IN (1, 2, 3);BETWEEN ... AND ...用來查詢一個(gè)范圍內(nèi)的值常用于時(shí)間范圍和數(shù)值范圍。SELECT id, order_no, amount FROM test_order WHERE amount BETWEEN 100 AND 500;時(shí)間范圍是測試中更常見的使用場景。注意BETWEEN包含邊界值。SELECT id, order_no, create_time FROM test_order WHERE create_time BETWEEN 2025-01-01 00:00:00 AND 2025-01-31 23:59:59;這里需要特別提醒如果只寫B(tài)ETWEEN 2025-01-01 AND 2025-01-31而create_time是datetime類型2025-01-31 00:00:00之后的數(shù)據(jù)不會(huì)被包含進(jìn)去。正確做法是結(jié)束時(shí)間寫到23:59:59或者把結(jié)束時(shí)間寫成下一天的零點(diǎn)再用比較。4.3 CASE WHEN 條件分支CASE WHEN可以在 SELECT 語句里做條件判斷把存儲(chǔ)層的數(shù)字狀態(tài)轉(zhuǎn)成業(yè)務(wù)含義。這在核對業(yè)務(wù)數(shù)據(jù)時(shí)尤其有用因?yàn)楹芏啾淼臓顟B(tài)字段是 0、1、2 這種數(shù)字直接看數(shù)字效率低。SELECT id, order_no, status, CASE WHEN status 0 THEN 待支付 WHEN status 1 THEN 已支付 WHEN status 2 THEN 已發(fā)貨 ELSE 未知狀態(tài) END AS status_name FROM test_order;這里的AS status_name是給結(jié)果列起別名。測試人員核對時(shí)一眼就能看出數(shù)據(jù)處于哪個(gè)業(yè)務(wù)環(huán)節(jié)不用再翻字典表。CASE WHEN還可以和聚合函數(shù)結(jié)合做條件統(tǒng)計(jì)比如統(tǒng)計(jì)已支付和已取消的訂單數(shù)。SELECT COUNT(CASE WHEN status 1 THEN 1 END) AS paid_count, COUNT(CASE WHEN status 4 THEN 1 END) AS canceled_count FROM test_order;這個(gè)寫法在驗(yàn)證統(tǒng)計(jì)報(bào)表類接口時(shí)很實(shí)用不需要寫多條 SQL 再手動(dòng)相加。4.4 多條件組合的綜合示例實(shí)際查詢中很少只有一個(gè)條件下面給一個(gè)組合條件示例覆蓋表中大部分測試場景。SELECT id, order_no, user_id, amount, status, create_time FROM test_order WHERE user_id 10086 AND status IN (1, 2) AND amount 50 AND create_time 2025-01-01 ORDER BY create_time DESC LIMIT 20;含義查詢用戶 10086 在 2025 年之后創(chuàng)建、金額大于等于 50、狀態(tài)為已支付或已發(fā)貨的最新 20 條訂單。這條 SQL 是典型的測試環(huán)境數(shù)據(jù)核驗(yàn)?zāi)０濉?. 聚合查詢與分組統(tǒng)計(jì)COUNT、SUM、AVG、GROUP BY、HAVING5.1 常用聚合函數(shù)聚合函數(shù)用于對一組數(shù)據(jù)做統(tǒng)計(jì)計(jì)算返回一行結(jié)果。測試工程師最常用的幾個(gè)函數(shù)如下。COUNT(*)統(tǒng)計(jì)行數(shù)COUNT(字段)統(tǒng)計(jì)某字段非 NULL 的行數(shù)SUM(字段)求和AVG(字段)求平均值MAX(字段)最大值MIN(字段)最小值統(tǒng)計(jì)訂單表總記錄數(shù)。SELECT COUNT(*) AS total_count FROM test_order;統(tǒng)計(jì)已支付訂單的總金額和平均金額。SELECT SUM(amount) AS total_amount, AVG(amount) AS avg_amount, COUNT(*) AS paid_count FROM test_order WHERE status 1;這里有一個(gè)容易混淆的點(diǎn)COUNT(*)和COUNT(字段)的區(qū)別。COUNT(*)會(huì)統(tǒng)計(jì)所有行包括字段全為 NULL 的行。COUNT(字段)只統(tǒng)計(jì)該字段不為 NULL 的行。如果字段有 NULL 值兩個(gè)結(jié)果可能不同。5.2 GROUP BY 分組統(tǒng)計(jì)分組統(tǒng)計(jì)通常對應(yīng)報(bào)表類接口。例如按訂單狀態(tài)統(tǒng)計(jì)每種狀態(tài)的訂單數(shù)。SELECT status, COUNT(*) AS order_count, SUM(amount) AS total_amount FROM test_order GROUP BY status;按天統(tǒng)計(jì)每天的訂單量是測試數(shù)據(jù)校驗(yàn)和報(bào)表驗(yàn)證的高頻操作。SELECT DATE(create_time) AS order_date, COUNT(*) AS order_count FROM test_order GROUP BY DATE(create_time) ORDER BY order_date DESC;這里的DATE()函數(shù)把datetime字段轉(zhuǎn)成日期格式然后按日期分組。MySQL 還支持DATE_FORMAT()做更靈活的格式化。SELECT DATE_FORMAT(create_time, %Y-%m-%d) AS order_date, COUNT(*) AS order_count FROM test_order GROUP BY DATE_FORMAT(create_time, %Y-%m-%d);5.3 HAVING 過濾分組結(jié)果WHERE是在分組前過濾行HAVING是在分組后過濾組。這個(gè)區(qū)別是 MySQL 面試題常客也是測試中容易用錯(cuò)的地方。例如只查詢訂單數(shù)大于 10 的狀態(tài)。SELECT status, COUNT(*) AS order_count FROM test_order GROUP BY status HAVING COUNT(*) 10;WHERE不能直接過濾聚合結(jié)果所以需要聚合后的條件只能寫在HAVING中。5.4 SELECT 執(zhí)行順序理解執(zhí)行順序?qū)ε挪椴樵兘Y(jié)果異常很重要。SQL 的邏輯執(zhí)行順序大致如下。FROM確定數(shù)據(jù)來源WHERE過濾原始行GROUP BY分組HAVING過濾分組結(jié)果SELECT選擇輸出字段ORDER BY排序LIMIT限制行數(shù)測試中遇到“為什么查出來的條數(shù)和預(yù)期不一致”的問題時(shí)按這個(gè)順序排查先看是不是WHERE過濾掉了數(shù)據(jù)再看GROUP BY分組邏輯是否正確最后看HAVING和LIMIT是否截?cái)嗔私Y(jié)果。6. 多表關(guān)聯(lián)查詢INNER JOIN、LEFT JOIN6.1 INNER JOIN 內(nèi)連接測試場景下業(yè)務(wù)數(shù)據(jù)經(jīng)常分散在多個(gè)表中。比如訂單表只存用戶 ID用戶姓名在用戶表中。要查詢訂單同時(shí)展示用戶姓名就需要關(guān)聯(lián)查詢。SELECT o.id, o.order_no, o.amount, u.user_name, u.mobile FROM test_order o INNER JOIN test_user u ON o.user_id u.id WHERE o.status 1;INNER JOIN只返回兩張表中匹配成功的行。訂單表中的user_id在用戶表找不到對應(yīng)記錄時(shí)這條訂單會(huì)被過濾掉。這里給表起了別名o和u后續(xù)查詢和 ORDER BY、LIMIT 中可以直接用別名引用字段。6.2 LEFT JOIN 左連接LEFT JOIN返回左表全部記錄右表沒有匹配時(shí)右表字段為 NULL。測試中常用于查詢“有訂單但用戶可能已刪除”的數(shù)據(jù)。SELECT o.id, o.order_no, o.user_id, u.user_name FROM test_order o LEFT JOIN test_user u ON o.user_id u.id;如果test_user表中沒有對應(yīng)的用戶數(shù)據(jù)u.user_name會(huì)顯示為NULL但訂單記錄仍然保留。這個(gè)特性非常適合排查臟數(shù)據(jù)、孤兒訂單問題。測試時(shí)可以通過WHERE u.id IS NULL找到?jīng)]有匹配用戶的訂單。SELECT o.id, o.order_no, o.user_id FROM test_order o LEFT JOIN test_user u ON o.user_id u.id WHERE u.id IS NULL;這條 SQL 很有價(jià)值它能快速定位測試環(huán)境中因?yàn)橛脩舯粍h除、數(shù)據(jù)被清庫導(dǎo)致的孤兒數(shù)據(jù)。6.3 多表關(guān)聯(lián)的綜合寫法業(yè)務(wù)系統(tǒng)通常不止兩張表可能是訂單表、用戶表、訂單明細(xì)表三張表關(guān)聯(lián)。測試核驗(yàn)時(shí)需要按業(yè)務(wù)邏輯逐步拼裝。SELECT o.order_no, u.user_name, od.product_name, od.product_count, od.product_price FROM test_order o INNER JOIN test_user u ON o.user_id u.id INNER JOIN test_order_detail od ON o.id od.order_id WHERE o.order_no TEST202501010001 ORDER BY od.id;這種查詢在核對訂單詳情接口、導(dǎo)出報(bào)表數(shù)據(jù)時(shí)非常有用。如果前端展示的訂單明細(xì)和數(shù)據(jù)庫對不上用這條 SQL 可以直接定位是哪張表的數(shù)據(jù)有問題。7. 子查詢嵌套查詢與 EXISTS 判斷子查詢是嵌套在外層查詢內(nèi)部的 SELECT 語句。測試中用于解決“先算出一個(gè)結(jié)果集再拿這個(gè)結(jié)果集去過濾主查詢”的場景。7.1 WHERE 條件中的子查詢查詢下單金額大于平均下單金額的訂單。SELECT id, order_no, amount FROM test_order WHERE amount (SELECT AVG(amount) FROM test_order);括號里的子查詢先執(zhí)行得到平均值外層查詢再取大于該值的訂單。7.2 IN 配合子查詢查找下過單的用戶信息。SELECT id, user_name, mobile FROM test_user WHERE id IN (SELECT DISTINCT user_id FROM test_order);這條 SQL 在測試中常用于驗(yàn)證“哪些用戶實(shí)際產(chǎn)生了訂單”。子查詢先返回所有下過單的user_id外層查詢再返回這些用戶的完整信息。7.3 EXISTS 子查詢EXISTS只關(guān)心子查詢是否有返回行。它的寫法和IN不同適合判斷關(guān)聯(lián)關(guān)系。SELECT id, user_name FROM test_user u WHERE EXISTS ( SELECT 1 FROM test_order o WHERE o.user_id u.id );這個(gè)查詢和上面的IN寫法結(jié)果類似。區(qū)別是EXISTS是逐行判斷是否存在IN是先算出子查詢結(jié)果集再匹配。數(shù)據(jù)量大的場景下兩者性能可能有差異但測試階段首先保證邏輯正確。測試 IN 子查詢時(shí)有一個(gè)經(jīng)典坑如果子查詢結(jié)果集中包含 NULLNOT IN會(huì)返回空結(jié)果。例如WHERE id NOT IN (SELECT user_id FROM test_order)當(dāng)test_order.user_id存在 NULL 時(shí)整個(gè)查詢不會(huì)返回任何行。排查這個(gè)問題的常用手段是把子查詢改成WHERE user_id IS NOT NULL或者改用NOT EXISTS。SELECT id, user_name FROM test_user u WHERE NOT EXISTS ( SELECT 1 FROM test_order o WHERE o.user_id u.id );這條 SQL 能查出來從未下過單的用戶測試數(shù)據(jù)清理時(shí)比NOT IN更穩(wěn)妥。8. 測試工程師實(shí)踐造數(shù)、查數(shù)、驗(yàn)證、清理8.1 造測試數(shù)據(jù)接口測試和功能測試經(jīng)常需要準(zhǔn)備指定狀態(tài)的數(shù)據(jù)。手工在頁面點(diǎn)擊生成太慢直接往數(shù)據(jù)庫插入是效率最高的方式。插入一條用戶數(shù)據(jù)。INSERT INTO test_user (user_name, mobile, status, create_time) VALUES (測試用戶A, 13800138000, 1, NOW());按批量方式插入多條數(shù)據(jù)。INSERT INTO test_user (user_name, mobile, status, create_time) VALUES (測試用戶B, 13800138001, 1, NOW()), (測試用戶C, 13800138002, 0, NOW()), (測試用戶D, 13800138003, 2, NOW());如果需要造大量數(shù)據(jù)可以用循環(huán)或者存儲(chǔ)過程。注意批量造數(shù)時(shí)要控制數(shù)據(jù)量避免給測試環(huán)境數(shù)據(jù)庫造成過大壓力。8.2 修改測試數(shù)據(jù)狀態(tài)狀態(tài)機(jī)流轉(zhuǎn)是業(yè)務(wù)測試的重點(diǎn)比如訂單從待支付改成已支付。正常通過頁面操作可能很慢直接更新數(shù)據(jù)庫狀態(tài)字段是測試圈常用的“捷徑”。UPDATE test_order SET status 1 WHERE order_no TEST202501010001;執(zhí)行 UPDATE 后可以再用 SELECT 確認(rèn)修改是否生效。SELECT id, order_no, status FROM test_order WHERE order_no TEST202501010001;這里有一個(gè)重點(diǎn)UPDATE 和 DELETE 都要先 SELECT 確認(rèn)條件再執(zhí)行更新。直接寫 UPDATE 語句一旦條件寫錯(cuò)可能批量修改誤傷大量數(shù)據(jù)。更穩(wěn)妥的做法是先把 WHERE 條件放到 SELECT 里查一遍確認(rèn)命中數(shù)據(jù)后再改成 UPDATE 執(zhí)行。8.3 清理測試臟數(shù)據(jù)測試完成后需要清理插入的臟數(shù)據(jù)避免影響后續(xù)測試。清理用 DELETE 命令。DELETE FROM test_user WHERE mobile LIKE 13800138000%;清理數(shù)據(jù)要注意外鍵約束。如果訂單表有外鍵指向用戶表直接刪除用戶可能報(bào)錯(cuò)。需要先清理子表數(shù)據(jù)再刪除主表數(shù)據(jù)或者按業(yè)務(wù)要求先確認(rèn)沒有依賴。DELETE FROM test_order WHERE user_id IN (SELECT id FROM test_user WHERE mobile LIKE 13800138000%); DELETE FROM test_user WHERE mobile LIKE 13800138000%;執(zhí)行 DELETE 后檢查影響行數(shù)確認(rèn)刪除的范圍正確。8.4 事務(wù)控制造數(shù)、修改、清理數(shù)據(jù)時(shí)建議先開啟事務(wù)確認(rèn)無誤后再提交。MySQL 默認(rèn)是自動(dòng)提交但測試中手動(dòng)控制更安全。START TRANSACTION; UPDATE test_order SET status 1 WHERE order_no TEST202501010001; SELECT id, order_no, status FROM test_order WHERE order_no TEST202501010001; COMMIT;如果發(fā)現(xiàn)數(shù)據(jù)修改不對可以用ROLLBACK回滾不產(chǎn)生實(shí)際影響。START TRANSACTION; DELETE FROM test_user WHERE mobile LIKE 13800138000%; -- 發(fā)現(xiàn)問題回滾 ROLLBACK;事務(wù)控制是測試操作數(shù)據(jù)庫最重要的安全機(jī)制之一比任何命令都值得養(yǎng)成習(xí)慣。9. MySQL 查詢常見問題與排查方法問題現(xiàn)象可能原因排查方式解決方案連接時(shí)報(bào) ERROR 2059MySQL 8.0 默認(rèn)認(rèn)證插件不支持老客戶端確認(rèn) MySQL 版本和客戶端版本升級客戶端或修改用戶認(rèn)證插件為 mysql_native_password連接時(shí)提示 Access denied賬號密碼錯(cuò)誤或沒有遠(yuǎn)程訪問權(quán)限確認(rèn)賬號密碼和授權(quán)范圍使用正確賬號或在服務(wù)端授權(quán)該賬號遠(yuǎn)程訪問中文亂碼客戶端字符集和數(shù)據(jù)庫字符集不一致查看連接字符集和表字段字符集執(zhí)行 SET NAMES utf8mb4; 或修改連接字符集查詢很慢沒有索引或走了全表掃描使用 EXPLAIN 查看執(zhí)行計(jì)劃根據(jù)查詢條件優(yōu)化索引避免 SELECT *執(zhí)行 UPDATE 一直卡住存在鎖表或鎖行沖突查看SHOW PROCESSLIST和鎖等待等待事務(wù)結(jié)束排查死鎖或長時(shí)間未提交的事務(wù)查詢結(jié)果和頁面不一致存在緩存、事務(wù)未提交、或多個(gè)環(huán)境庫確認(rèn)查詢的是哪個(gè)環(huán)境哪個(gè)庫切換正確環(huán)境確認(rèn)事務(wù)已提交ORDER BY 排序結(jié)果不符合預(yù)期字符串排序和數(shù)字排序差異確認(rèn)字段類型是 varchar 還是 int轉(zhuǎn)換類型后再排序或調(diào)整業(yè)務(wù)邏輯插入數(shù)據(jù)提示字段過長字段長度限制查看字段類型和長度調(diào)整測試數(shù)據(jù)長度或修改表結(jié)構(gòu)這里重點(diǎn)說一下 2059 連接錯(cuò)誤。MySQL 8.0 安裝后默認(rèn)創(chuàng)建的用戶使用caching_sha2_password插件而 Navicat 早期版本或者 MySQL 5.x 客戶端默認(rèn)使用mysql_native_password雙方認(rèn)證方式不兼容就會(huì)報(bào)Authentication plugin caching_sha2_password cannot be loaded。解決方法有兩種。一種是升級客戶端到支持 MySQL 8.0 的版本另一個(gè)是用管理員賬號登錄后修改用戶認(rèn)證插件。ALTER USER test_user% IDENTIFIED WITH mysql_native_password BY 你的密碼; FLUSH PRIVILEGES;鎖表問題也值得單獨(dú)說。測試環(huán)境經(jīng)常有人開著事務(wù)不提交或者后臺任務(wù)執(zhí)行時(shí)間過長導(dǎo)致你執(zhí)行 UPDATE 或者 DELETE 時(shí)一直卡住。遇到這種情況先看當(dāng)前進(jìn)程列表。SHOW PROCESSLIST;找到State為Waiting for table metadata lock或者Locked的連接確認(rèn)是哪個(gè)會(huì)話持有鎖。如果是自己開的線程可以把這個(gè)會(huì)話殺掉。KILL 12345;這里的12345是SHOW PROCESSLIST結(jié)果中的Id字段值。生產(chǎn)環(huán)境執(zhí)行 KILL 要格外謹(jǐn)慎測試環(huán)境可以放開操作。用 EXPLAIN 分析慢查詢也是排查索引問題的常用手段。EXPLAIN SELECT * FROM test_order WHERE user_id 10086 AND status 1;關(guān)注type字段和rows字段。type為ALL說明走了全表掃描rows數(shù)值很大則說明掃描了大量行。測試階段發(fā)現(xiàn)查詢慢最直接的建議是核對 where 條件的字段是否建了索引。10. 測試工程師建議養(yǎng)成的 MySQL 使用習(xí)慣本文最后一部分給出幾條實(shí)戰(zhàn)層面的建議。第一條操作數(shù)據(jù)庫前先確認(rèn)環(huán)境。測試環(huán)境、預(yù)發(fā)環(huán)境、生產(chǎn)環(huán)境的庫可能長得一模一樣但數(shù)據(jù)完全不同。連接前看清楚 host 和數(shù)據(jù)庫名避免連錯(cuò)環(huán)境。日常操作中把不同環(huán)境的連接信息分開存放不要混用。第二條UPDATE 和 DELETE 前先 SELECT。先把 WHERE 條件放到 SELECT 語句里執(zhí)行確認(rèn)命中的數(shù)據(jù)就是你要改的數(shù)據(jù)再把它改成 UPDATE 或 DELETE。這個(gè)習(xí)慣能避免大量誤操作。第三條寫完 SQL 先格式化。MySQL 命令不區(qū)分大小寫但項(xiàng)目和團(tuán)隊(duì)一般有規(guī)范的寫法關(guān)鍵字大寫、表名字段名小寫、縮進(jìn)對齊對排查問題更有幫助。自己維護(hù)一份常用 SQL 片段庫下次直接復(fù)制改條件。第四條多表查詢優(yōu)先用 JOIN而不是在 WHERE 里寫逗號關(guān)聯(lián)。JOIN 寫法更明確ON條件把關(guān)聯(lián)關(guān)系表達(dá)得很清楚可讀性更好排查問題時(shí)也更容易定位。第五條查數(shù)據(jù)時(shí)要有“結(jié)果導(dǎo)向”。不要為了查而查先想清楚要驗(yàn)證什么業(yè)務(wù)邏輯再?zèng)Q定查哪些字段、加哪些條件。這樣既能提高效率也能加深對業(yè)務(wù)的理解。第六條不要在生產(chǎn)環(huán)境隨意執(zhí)行 UPDATE、DELETE。如果確實(shí)需要必須經(jīng)過審批并盡量使用事務(wù)控制確認(rèn)影響行數(shù)后再提交。11. 總結(jié)與下一步軟件測試工程師的 MySQL 查詢命令實(shí)戰(zhàn)核心就是把 SELECT、WHERE、ORDER BY、LIMIT、聚合函數(shù)、JOIN 和子查詢用熟。這些命令本身不難難的是在不同測試場景下知道自己該查什么、怎么查、怎么驗(yàn)證結(jié)果。建議按照下面的順序做一輪實(shí)戰(zhàn)練習(xí)。第一步連上測試環(huán)境數(shù)據(jù)庫用 SHOW DATABASES 和 DESC 熟悉表結(jié)構(gòu)。第二步抄寫本文第 3 節(jié)的單表查詢示例改成你項(xiàng)目里的真實(shí)表名和字段名跑通一遍。第三步找一張有狀態(tài)字段的業(yè)務(wù)表用 GROUP BY 加 CASE WHEN 做一次狀態(tài)統(tǒng)計(jì)驗(yàn)證和前端報(bào)表數(shù)字是否一致。第四步找兩張有關(guān)聯(lián)關(guān)系的表用 LEFT JOIN 查一次包含 NULL 的數(shù)據(jù)判斷是否存在孤兒數(shù)據(jù)。第五步把 UPDATE、DELETE 放入事務(wù)中執(zhí)行練習(xí) COMMIT 和 ROLLBACK。完整跑完這幾步日常測試中涉及 MySQL 查詢命令的基本場景就都能覆蓋了。后面再遇到數(shù)據(jù)庫相關(guān)的測試任務(wù)可以直接翻這篇文章按圖索驥。建議收藏備用。 測試工程師MySQL查詢實(shí)戰(zhàn)：常用命令與速查手冊
作為軟件測試工程師日常工作里接觸最多、也最容易繞不開的就是 MySQL 查詢命令。不管是功能測試要造數(shù)據(jù)、接口測試要驗(yàn)證落庫結(jié)果還是排查線上問題時(shí)要去查一條訂單的狀態(tài)SQL 能力直接決定你的測試效率。這里說的不是讓你像 DBA 一樣做運(yùn)維優(yōu)化而是把最常用的查詢命令整理成一套能夠直接上手、直接執(zhí)行的實(shí)戰(zhàn)手冊。這篇文章會(huì)把 MySQL 查詢命令按測試實(shí)際使用場景重新組織從連接數(shù)據(jù)庫、單表查詢、條件過濾、聚合統(tǒng)計(jì)、多表關(guān)聯(lián)到測試數(shù)據(jù)造數(shù)和清理、常見連接報(bào)錯(cuò)排查全部用可復(fù)制的 SQL 示例展開。讀者如果是剛轉(zhuǎn)行做軟件測試、或?qū)?MySQL 半生不熟的開發(fā)轉(zhuǎn)測人員可以直接把本文當(dāng)成一份速查手冊。1. 測試工程師必備的 MySQL 查詢能力速覽測試和開發(fā)看數(shù)據(jù)庫的視角不同。開發(fā)更關(guān)心寫接口、調(diào)業(yè)務(wù)邏輯測試更關(guān)心數(shù)據(jù)是否按預(yù)期寫入、狀態(tài)字段是否流轉(zhuǎn)正確、測試環(huán)境的數(shù)據(jù)是否干凈。圍繞這個(gè)目標(biāo)測試工程師最常用的 MySQL 能力可以整理成下面這張表。能力項(xiàng)典型測試應(yīng)用場景使用頻率SELECT 單表查詢查用戶表、訂單表的基本數(shù)據(jù)每天使用WHERE 條件過濾按訂單號、用戶 ID、時(shí)間范圍篩數(shù)據(jù)每天使用ORDER BY 排序驗(yàn)證列表接口的排序邏輯高頻LIMIT 分頁查詢驗(yàn)證分頁接口的返回結(jié)果高頻LIKE 模糊查詢搜索功能測試、按名稱關(guān)鍵字查數(shù)據(jù)高頻CASE WHEN 條件分支把數(shù)據(jù)庫字段值轉(zhuǎn)成業(yè)務(wù)含義再核對中頻聚合函數(shù) COUNT/SUM/AVG驗(yàn)證統(tǒng)計(jì)報(bào)表、列表總條數(shù)高頻GROUP BY 分組統(tǒng)計(jì)驗(yàn)證按狀態(tài)、按品類分組的統(tǒng)計(jì)接口中高頻JOIN 多表關(guān)聯(lián)跨表核對業(yè)務(wù)數(shù)據(jù)完整性高頻子查詢復(fù)雜的多條件過濾、嵌套統(tǒng)計(jì)中頻UPDATE 數(shù)據(jù)訂正測試環(huán)境數(shù)據(jù)訂正、狀態(tài)復(fù)位中頻DELETE 數(shù)據(jù)清理清理測試臟數(shù)據(jù)中頻掌握以上能力之后再去應(yīng)對數(shù)據(jù)庫相關(guān)的測試面試題也會(huì)從容很多。MySQL 面試題里常問的SELECT執(zhí)行順序、WHERE和HAVING的區(qū)別、LEFT JOIN和INNER JOIN的差異本質(zhì)上就是這些基礎(chǔ)查詢命令的延伸。2. 環(huán)境準(zhǔn)備測試工程師怎么連接 MySQL寫查詢命令之前先確認(rèn)你能連上 MySQL。測試環(huán)境通常有開發(fā)或者運(yùn)維提供的數(shù)據(jù)庫賬號你只需要拿到三樣?xùn)|西數(shù)據(jù)庫地址、端口、賬號密碼。連接方式常見有兩種。第一種是命令行客戶端MySQL 安裝后自帶mysql命令。Windows 下需要先把 MySQL 的bin目錄加入系統(tǒng) PATH或者直接在 bin 目錄下打開終端執(zhí)行。Linux 和 macOS 一般可以直接執(zhí)行。mysql -h 192.168.1.100 -P 3306 -u test_user -p執(zhí)行后按提示輸入密碼看到mysql提示符就說明連接成功。這里的-h指定數(shù)據(jù)庫主機(jī)地址-P指定端口默認(rèn) 3306 可以省略-u指定用戶名-p表示需要輸入密碼。如果本地安裝的是 MySQL 8.0默認(rèn)認(rèn)證插件是caching_sha2_password某些老版本客戶端或者工具連接時(shí)會(huì)報(bào)認(rèn)證失敗后面常見問題部分會(huì)專門講。第二種是圖形化工具比如 Navicat、MySQL Workbench、DBeaver。新建連接時(shí)填主機(jī)、端口、用戶名、密碼即可。圖形化工具適合需要頻繁看表結(jié)構(gòu)、導(dǎo)出數(shù)據(jù)、可視化編輯的場景但對測試工程師來說命令行永遠(yuǎn)是兜底方案因?yàn)榫€上排查時(shí)不一定有圖形化工具。連接后先看當(dāng)前數(shù)據(jù)庫列表。SHOW DATABASES;再切換到目標(biāo)數(shù)據(jù)庫。USE test_db;查看當(dāng)前庫下的所有表。SHOW TABLES;查看某張表的結(jié)構(gòu)。DESC test_user;這套命令是測試環(huán)境查數(shù)前的固定熱身動(dòng)作。每次拿到新庫我都建議先跑一遍SHOW DATABASES和SHOW TABLES確認(rèn)環(huán)境沒有連錯(cuò)、表存在再開始寫具體查詢。3. 基礎(chǔ)查詢命令SELECT、WHERE、ORDER BY、LIMIT3.1 SELECT 查詢指定字段測試工程師查數(shù)據(jù)時(shí)最忌諱SELECT *一把梭不是說不能用它而是當(dāng)表字段很多、數(shù)據(jù)量很大時(shí)SELECT *會(huì)把無用字段全部撈出來輸出內(nèi)容太多反而看不清關(guān)鍵數(shù)據(jù)。更推薦的做法是只查自己關(guān)心的字段。SELECT id, user_name, mobile, status FROM test_user;這條命令查詢test_user表中的四個(gè)字段。從執(zhí)行效率看只查必要字段也能減少網(wǎng)絡(luò)傳輸?shù)臄?shù)據(jù)量。3.2 WHERE 條件過濾查詢測試數(shù)據(jù)時(shí)絕大多數(shù)情況都需要帶過濾條件。比如只查某個(gè)訂單號的數(shù)據(jù)、只查狀態(tài)為 1 的用戶、只查某個(gè)時(shí)間段內(nèi)的記錄。SELECT id, order_no, amount, status FROM test_order WHERE status 1;多條件組合時(shí)用AND和OR。注意AND優(yōu)先級高于OR如果條件邏輯比較復(fù)雜建議加括號明確優(yōu)先級。SELECT id, order_no, amount, status FROM test_order WHERE status 1 AND amount 100 AND create_time 2025-01-01;3.3 ORDER BY 排序列表類接口測試時(shí)前端展示的數(shù)據(jù)往往有排序規(guī)則。測試人員需要對照數(shù)據(jù)庫確認(rèn)排序結(jié)果是否符合接口文檔。排序用ORDER BY默認(rèn)是升序ASC需要降序時(shí)用DESC。SELECT id, user_name, create_time FROM test_user ORDER BY create_time DESC;多字段排序時(shí)先按第一個(gè)字段排相同再按第二個(gè)字段排。例如先按狀態(tài)升序再按創(chuàng)建時(shí)間降序。SELECT id, user_name, status, create_time FROM test_user ORDER BY status ASC, create_time DESC;這里有一個(gè)測試中容易踩的坑字符型字段排序不是數(shù)字排序。比如mobile字段如果存的是字符串排序結(jié)果和按數(shù)字排序可能不一致。驗(yàn)證排序邏輯時(shí)要先確認(rèn)字段類型。3.4 LIMIT 分頁查詢分頁接口測試是軟件測試工程師的高頻工作。前端傳page和pageSize后端返回對應(yīng)分頁數(shù)據(jù)。數(shù)據(jù)庫層一般用LIMIT實(shí)現(xiàn)。SELECT id, order_no, amount FROM test_order ORDER BY id LIMIT 0, 10;上面這行表示從第 0 條開始取 10 條對應(yīng)第一頁。第二頁就是LIMIT 10, 10第三頁是LIMIT 20, 10。MySQL 的 LIMIT 語法也可以簡寫成LIMIT 偏移量, 行數(shù)。在 MySQL 8.0 中還可以用LIMIT 行數(shù) OFFSET 偏移量的寫法兩種等價(jià)。SELECT id, order_no, amount FROM test_order ORDER BY id LIMIT 10 OFFSET 10;測試分頁接口時(shí)要注意邊界值是第一頁、最后一頁、頁碼超過總頁數(shù)、頁碼為 0 或負(fù)數(shù)。數(shù)據(jù)庫層面對應(yīng)驗(yàn)證的是LIMIT偏移量計(jì)算是否正確以及偏移量超過數(shù)據(jù)總量時(shí)返回空結(jié)果但不會(huì)報(bào)錯(cuò)。3.5 DISTINCT 去重查詢測試中需要確認(rèn)某張表中不同取值的數(shù)量時(shí)可以用DISTINCT去重。例如查看訂單表中存在哪些狀態(tài)值。SELECT DISTINCT status FROM test_order;也可以統(tǒng)計(jì)去重后的數(shù)量。SELECT COUNT(DISTINCT user_id) FROM test_order;這行命令可以快速判斷某個(gè)用戶是否下過單也是造數(shù)時(shí)檢查重復(fù)數(shù)據(jù)的常用手段。4. 條件過濾實(shí)戰(zhàn)LIKE、IN、BETWEEN、CASE WHEN4.1 LIKE 模糊查詢搜索功能測試時(shí)前端輸入關(guān)鍵字后端一般使用LIKE做模糊匹配。數(shù)據(jù)庫層面的驗(yàn)證就是看LIKE查詢能否返回符合條件的數(shù)據(jù)。SELECT id, user_name FROM test_user WHERE user_name LIKE 張%;%代表任意長度的字符_代表單個(gè)字符。張%表示以“張”開頭%張%表示包含“張”張_表示以“張”開頭且后面只有一個(gè)字符。測試搜索接口時(shí)需要關(guān)注大小寫敏感問題。MySQL 默認(rèn)的排序規(guī)則utf8_general_ci是不區(qū)分大小寫的LIKE abc%也能匹配ABC。如果業(yè)務(wù)要求區(qū)分大小寫需要使用utf8_bin排序規(guī)則或者BINARY關(guān)鍵字。SELECT id, user_name FROM test_user WHERE user_name LIKE BINARY Abc%;4.2 IN 和 BETWEENIN用來匹配多個(gè)值等價(jià)于多個(gè)OR條件。例如查詢狀態(tài)為 1、2、3 的訂單。SELECT id, order_no, status FROM test_order WHERE status IN (1, 2, 3);BETWEEN ... AND ...用來查詢一個(gè)范圍內(nèi)的值常用于時(shí)間范圍和數(shù)值范圍。SELECT id, order_no, amount FROM test_order WHERE amount BETWEEN 100 AND 500;時(shí)間范圍是測試中更常見的使用場景。注意BETWEEN包含邊界值。SELECT id, order_no, create_time FROM test_order WHERE create_time BETWEEN 2025-01-01 00:00:00 AND 2025-01-31 23:59:59;這里需要特別提醒如果只寫B(tài)ETWEEN 2025-01-01 AND 2025-01-31而create_time是datetime類型2025-01-31 00:00:00之后的數(shù)據(jù)不會(huì)被包含進(jìn)去。正確做法是結(jié)束時(shí)間寫到23:59:59或者把結(jié)束時(shí)間寫成下一天的零點(diǎn)再用比較。4.3 CASE WHEN 條件分支CASE WHEN可以在 SELECT 語句里做條件判斷把存儲(chǔ)層的數(shù)字狀態(tài)轉(zhuǎn)成業(yè)務(wù)含義。這在核對業(yè)務(wù)數(shù)據(jù)時(shí)尤其有用因?yàn)楹芏啾淼臓顟B(tài)字段是 0、1、2 這種數(shù)字直接看數(shù)字效率低。SELECT id, order_no, status, CASE WHEN status 0 THEN 待支付 WHEN status 1 THEN 已支付 WHEN status 2 THEN 已發(fā)貨 ELSE 未知狀態(tài) END AS status_name FROM test_order;這里的AS status_name是給結(jié)果列起別名。測試人員核對時(shí)一眼就能看出數(shù)據(jù)處于哪個(gè)業(yè)務(wù)環(huán)節(jié)不用再翻字典表。CASE WHEN還可以和聚合函數(shù)結(jié)合做條件統(tǒng)計(jì)比如統(tǒng)計(jì)已支付和已取消的訂單數(shù)。SELECT COUNT(CASE WHEN status 1 THEN 1 END) AS paid_count, COUNT(CASE WHEN status 4 THEN 1 END) AS canceled_count FROM test_order;這個(gè)寫法在驗(yàn)證統(tǒng)計(jì)報(bào)表類接口時(shí)很實(shí)用不需要寫多條 SQL 再手動(dòng)相加。4.4 多條件組合的綜合示例實(shí)際查詢中很少只有一個(gè)條件下面給一個(gè)組合條件示例覆蓋表中大部分測試場景。SELECT id, order_no, user_id, amount, status, create_time FROM test_order WHERE user_id 10086 AND status IN (1, 2) AND amount 50 AND create_time 2025-01-01 ORDER BY create_time DESC LIMIT 20;含義查詢用戶 10086 在 2025 年之后創(chuàng)建、金額大于等于 50、狀態(tài)為已支付或已發(fā)貨的最新 20 條訂單。這條 SQL 是典型的測試環(huán)境數(shù)據(jù)核驗(yàn)?zāi)０濉?. 聚合查詢與分組統(tǒng)計(jì)COUNT、SUM、AVG、GROUP BY、HAVING5.1 常用聚合函數(shù)聚合函數(shù)用于對一組數(shù)據(jù)做統(tǒng)計(jì)計(jì)算返回一行結(jié)果。測試工程師最常用的幾個(gè)函數(shù)如下。COUNT(*)統(tǒng)計(jì)行數(shù)COUNT(字段)統(tǒng)計(jì)某字段非 NULL 的行數(shù)SUM(字段)求和AVG(字段)求平均值MAX(字段)最大值MIN(字段)最小值統(tǒng)計(jì)訂單表總記錄數(shù)。SELECT COUNT(*) AS total_count FROM test_order;統(tǒng)計(jì)已支付訂單的總金額和平均金額。SELECT SUM(amount) AS total_amount, AVG(amount) AS avg_amount, COUNT(*) AS paid_count FROM test_order WHERE status 1;這里有一個(gè)容易混淆的點(diǎn)COUNT(*)和COUNT(字段)的區(qū)別。COUNT(*)會(huì)統(tǒng)計(jì)所有行包括字段全為 NULL 的行。COUNT(字段)只統(tǒng)計(jì)該字段不為 NULL 的行。如果字段有 NULL 值兩個(gè)結(jié)果可能不同。5.2 GROUP BY 分組統(tǒng)計(jì)分組統(tǒng)計(jì)通常對應(yīng)報(bào)表類接口。例如按訂單狀態(tài)統(tǒng)計(jì)每種狀態(tài)的訂單數(shù)。SELECT status, COUNT(*) AS order_count, SUM(amount) AS total_amount FROM test_order GROUP BY status;按天統(tǒng)計(jì)每天的訂單量是測試數(shù)據(jù)校驗(yàn)和報(bào)表驗(yàn)證的高頻操作。SELECT DATE(create_time) AS order_date, COUNT(*) AS order_count FROM test_order GROUP BY DATE(create_time) ORDER BY order_date DESC;這里的DATE()函數(shù)把datetime字段轉(zhuǎn)成日期格式然后按日期分組。MySQL 還支持DATE_FORMAT()做更靈活的格式化。SELECT DATE_FORMAT(create_time, %Y-%m-%d) AS order_date, COUNT(*) AS order_count FROM test_order GROUP BY DATE_FORMAT(create_time, %Y-%m-%d);5.3 HAVING 過濾分組結(jié)果WHERE是在分組前過濾行HAVING是在分組后過濾組。這個(gè)區(qū)別是 MySQL 面試題常客也是測試中容易用錯(cuò)的地方。例如只查詢訂單數(shù)大于 10 的狀態(tài)。SELECT status, COUNT(*) AS order_count FROM test_order GROUP BY status HAVING COUNT(*) 10;WHERE不能直接過濾聚合結(jié)果所以需要聚合后的條件只能寫在HAVING中。5.4 SELECT 執(zhí)行順序理解執(zhí)行順序?qū)ε挪椴樵兘Y(jié)果異常很重要。SQL 的邏輯執(zhí)行順序大致如下。FROM確定數(shù)據(jù)來源WHERE過濾原始行GROUP BY分組HAVING過濾分組結(jié)果SELECT選擇輸出字段ORDER BY排序LIMIT限制行數(shù)測試中遇到“為什么查出來的條數(shù)和預(yù)期不一致”的問題時(shí)按這個(gè)順序排查先看是不是WHERE過濾掉了數(shù)據(jù)再看GROUP BY分組邏輯是否正確最后看HAVING和LIMIT是否截?cái)嗔私Y(jié)果。6. 多表關(guān)聯(lián)查詢INNER JOIN、LEFT JOIN6.1 INNER JOIN 內(nèi)連接測試場景下業(yè)務(wù)數(shù)據(jù)經(jīng)常分散在多個(gè)表中。比如訂單表只存用戶 ID用戶姓名在用戶表中。要查詢訂單同時(shí)展示用戶姓名就需要關(guān)聯(lián)查詢。SELECT o.id, o.order_no, o.amount, u.user_name, u.mobile FROM test_order o INNER JOIN test_user u ON o.user_id u.id WHERE o.status 1;INNER JOIN只返回兩張表中匹配成功的行。訂單表中的user_id在用戶表找不到對應(yīng)記錄時(shí)這條訂單會(huì)被過濾掉。這里給表起了別名o和u后續(xù)查詢和 ORDER BY、LIMIT 中可以直接用別名引用字段。6.2 LEFT JOIN 左連接LEFT JOIN返回左表全部記錄右表沒有匹配時(shí)右表字段為 NULL。測試中常用于查詢“有訂單但用戶可能已刪除”的數(shù)據(jù)。SELECT o.id, o.order_no, o.user_id, u.user_name FROM test_order o LEFT JOIN test_user u ON o.user_id u.id;如果test_user表中沒有對應(yīng)的用戶數(shù)據(jù)u.user_name會(huì)顯示為NULL但訂單記錄仍然保留。這個(gè)特性非常適合排查臟數(shù)據(jù)、孤兒訂單問題。測試時(shí)可以通過WHERE u.id IS NULL找到?jīng)]有匹配用戶的訂單。SELECT o.id, o.order_no, o.user_id FROM test_order o LEFT JOIN test_user u ON o.user_id u.id WHERE u.id IS NULL;這條 SQL 很有價(jià)值它能快速定位測試環(huán)境中因?yàn)橛脩舯粍h除、數(shù)據(jù)被清庫導(dǎo)致的孤兒數(shù)據(jù)。6.3 多表關(guān)聯(lián)的綜合寫法業(yè)務(wù)系統(tǒng)通常不止兩張表可能是訂單表、用戶表、訂單明細(xì)表三張表關(guān)聯(lián)。測試核驗(yàn)時(shí)需要按業(yè)務(wù)邏輯逐步拼裝。SELECT o.order_no, u.user_name, od.product_name, od.product_count, od.product_price FROM test_order o INNER JOIN test_user u ON o.user_id u.id INNER JOIN test_order_detail od ON o.id od.order_id WHERE o.order_no TEST202501010001 ORDER BY od.id;這種查詢在核對訂單詳情接口、導(dǎo)出報(bào)表數(shù)據(jù)時(shí)非常有用。如果前端展示的訂單明細(xì)和數(shù)據(jù)庫對不上用這條 SQL 可以直接定位是哪張表的數(shù)據(jù)有問題。7. 子查詢嵌套查詢與 EXISTS 判斷子查詢是嵌套在外層查詢內(nèi)部的 SELECT 語句。測試中用于解決“先算出一個(gè)結(jié)果集再拿這個(gè)結(jié)果集去過濾主查詢”的場景。7.1 WHERE 條件中的子查詢查詢下單金額大于平均下單金額的訂單。SELECT id, order_no, amount FROM test_order WHERE amount (SELECT AVG(amount) FROM test_order);括號里的子查詢先執(zhí)行得到平均值外層查詢再取大于該值的訂單。7.2 IN 配合子查詢查找下過單的用戶信息。SELECT id, user_name, mobile FROM test_user WHERE id IN (SELECT DISTINCT user_id FROM test_order);這條 SQL 在測試中常用于驗(yàn)證“哪些用戶實(shí)際產(chǎn)生了訂單”。子查詢先返回所有下過單的user_id外層查詢再返回這些用戶的完整信息。7.3 EXISTS 子查詢EXISTS只關(guān)心子查詢是否有返回行。它的寫法和IN不同適合判斷關(guān)聯(lián)關(guān)系。SELECT id, user_name FROM test_user u WHERE EXISTS ( SELECT 1 FROM test_order o WHERE o.user_id u.id );這個(gè)查詢和上面的IN寫法結(jié)果類似。區(qū)別是EXISTS是逐行判斷是否存在IN是先算出子查詢結(jié)果集再匹配。數(shù)據(jù)量大的場景下兩者性能可能有差異但測試階段首先保證邏輯正確。測試 IN 子查詢時(shí)有一個(gè)經(jīng)典坑如果子查詢結(jié)果集中包含 NULLNOT IN會(huì)返回空結(jié)果。例如WHERE id NOT IN (SELECT user_id FROM test_order)當(dāng)test_order.user_id存在 NULL 時(shí)整個(gè)查詢不會(huì)返回任何行。排查這個(gè)問題的常用手段是把子查詢改成WHERE user_id IS NOT NULL或者改用NOT EXISTS。SELECT id, user_name FROM test_user u WHERE NOT EXISTS ( SELECT 1 FROM test_order o WHERE o.user_id u.id );這條 SQL 能查出來從未下過單的用戶測試數(shù)據(jù)清理時(shí)比NOT IN更穩(wěn)妥。8. 測試工程師實(shí)踐造數(shù)、查數(shù)、驗(yàn)證、清理8.1 造測試數(shù)據(jù)接口測試和功能測試經(jīng)常需要準(zhǔn)備指定狀態(tài)的數(shù)據(jù)。手工在頁面點(diǎn)擊生成太慢直接往數(shù)據(jù)庫插入是效率最高的方式。插入一條用戶數(shù)據(jù)。INSERT INTO test_user (user_name, mobile, status, create_time) VALUES (測試用戶A, 13800138000, 1, NOW());按批量方式插入多條數(shù)據(jù)。INSERT INTO test_user (user_name, mobile, status, create_time) VALUES (測試用戶B, 13800138001, 1, NOW()), (測試用戶C, 13800138002, 0, NOW()), (測試用戶D, 13800138003, 2, NOW());如果需要造大量數(shù)據(jù)可以用循環(huán)或者存儲(chǔ)過程。注意批量造數(shù)時(shí)要控制數(shù)據(jù)量避免給測試環(huán)境數(shù)據(jù)庫造成過大壓力。8.2 修改測試數(shù)據(jù)狀態(tài)狀態(tài)機(jī)流轉(zhuǎn)是業(yè)務(wù)測試的重點(diǎn)比如訂單從待支付改成已支付。正常通過頁面操作可能很慢直接更新數(shù)據(jù)庫狀態(tài)字段是測試圈常用的“捷徑”。UPDATE test_order SET status 1 WHERE order_no TEST202501010001;執(zhí)行 UPDATE 后可以再用 SELECT 確認(rèn)修改是否生效。SELECT id, order_no, status FROM test_order WHERE order_no TEST202501010001;這里有一個(gè)重點(diǎn)UPDATE 和 DELETE 都要先 SELECT 確認(rèn)條件再執(zhí)行更新。直接寫 UPDATE 語句一旦條件寫錯(cuò)可能批量修改誤傷大量數(shù)據(jù)。更穩(wěn)妥的做法是先把 WHERE 條件放到 SELECT 里查一遍確認(rèn)命中數(shù)據(jù)后再改成 UPDATE 執(zhí)行。8.3 清理測試臟數(shù)據(jù)測試完成后需要清理插入的臟數(shù)據(jù)避免影響后續(xù)測試。清理用 DELETE 命令。DELETE FROM test_user WHERE mobile LIKE 13800138000%;清理數(shù)據(jù)要注意外鍵約束。如果訂單表有外鍵指向用戶表直接刪除用戶可能報(bào)錯(cuò)。需要先清理子表數(shù)據(jù)再刪除主表數(shù)據(jù)或者按業(yè)務(wù)要求先確認(rèn)沒有依賴。DELETE FROM test_order WHERE user_id IN (SELECT id FROM test_user WHERE mobile LIKE 13800138000%); DELETE FROM test_user WHERE mobile LIKE 13800138000%;執(zhí)行 DELETE 后檢查影響行數(shù)確認(rèn)刪除的范圍正確。8.4 事務(wù)控制造數(shù)、修改、清理數(shù)據(jù)時(shí)建議先開啟事務(wù)確認(rèn)無誤后再提交。MySQL 默認(rèn)是自動(dòng)提交但測試中手動(dòng)控制更安全。START TRANSACTION; UPDATE test_order SET status 1 WHERE order_no TEST202501010001; SELECT id, order_no, status FROM test_order WHERE order_no TEST202501010001; COMMIT;如果發(fā)現(xiàn)數(shù)據(jù)修改不對可以用ROLLBACK回滾不產(chǎn)生實(shí)際影響。START TRANSACTION; DELETE FROM test_user WHERE mobile LIKE 13800138000%; -- 發(fā)現(xiàn)問題回滾 ROLLBACK;事務(wù)控制是測試操作數(shù)據(jù)庫最重要的安全機(jī)制之一比任何命令都值得養(yǎng)成習(xí)慣。9. MySQL 查詢常見問題與排查方法問題現(xiàn)象可能原因排查方式解決方案連接時(shí)報(bào) ERROR 2059MySQL 8.0 默認(rèn)認(rèn)證插件不支持老客戶端確認(rèn) MySQL 版本和客戶端版本升級客戶端或修改用戶認(rèn)證插件為 mysql_native_password連接時(shí)提示 Access denied賬號密碼錯(cuò)誤或沒有遠(yuǎn)程訪問權(quán)限確認(rèn)賬號密碼和授權(quán)范圍使用正確賬號或在服務(wù)端授權(quán)該賬號遠(yuǎn)程訪問中文亂碼客戶端字符集和數(shù)據(jù)庫字符集不一致查看連接字符集和表字段字符集執(zhí)行 SET NAMES utf8mb4; 或修改連接字符集查詢很慢沒有索引或走了全表掃描使用 EXPLAIN 查看執(zhí)行計(jì)劃根據(jù)查詢條件優(yōu)化索引避免 SELECT *執(zhí)行 UPDATE 一直卡住存在鎖表或鎖行沖突查看SHOW PROCESSLIST和鎖等待等待事務(wù)結(jié)束排查死鎖或長時(shí)間未提交的事務(wù)查詢結(jié)果和頁面不一致存在緩存、事務(wù)未提交、或多個(gè)環(huán)境庫確認(rèn)查詢的是哪個(gè)環(huán)境哪個(gè)庫切換正確環(huán)境確認(rèn)事務(wù)已提交ORDER BY 排序結(jié)果不符合預(yù)期字符串排序和數(shù)字排序差異確認(rèn)字段類型是 varchar 還是 int轉(zhuǎn)換類型后再排序或調(diào)整業(yè)務(wù)邏輯插入數(shù)據(jù)提示字段過長字段長度限制查看字段類型和長度調(diào)整測試數(shù)據(jù)長度或修改表結(jié)構(gòu)這里重點(diǎn)說一下 2059 連接錯(cuò)誤。MySQL 8.0 安裝后默認(rèn)創(chuàng)建的用戶使用caching_sha2_password插件而 Navicat 早期版本或者 MySQL 5.x 客戶端默認(rèn)使用mysql_native_password雙方認(rèn)證方式不兼容就會(huì)報(bào)Authentication plugin caching_sha2_password cannot be loaded。解決方法有兩種。一種是升級客戶端到支持 MySQL 8.0 的版本另一個(gè)是用管理員賬號登錄后修改用戶認(rèn)證插件。ALTER USER test_user% IDENTIFIED WITH mysql_native_password BY 你的密碼; FLUSH PRIVILEGES;鎖表問題也值得單獨(dú)說。測試環(huán)境經(jīng)常有人開著事務(wù)不提交或者后臺任務(wù)執(zhí)行時(shí)間過長導(dǎo)致你執(zhí)行 UPDATE 或者 DELETE 時(shí)一直卡住。遇到這種情況先看當(dāng)前進(jìn)程列表。SHOW PROCESSLIST;找到State為Waiting for table metadata lock或者Locked的連接確認(rèn)是哪個(gè)會(huì)話持有鎖。如果是自己開的線程可以把這個(gè)會(huì)話殺掉。KILL 12345;這里的12345是SHOW PROCESSLIST結(jié)果中的Id字段值。生產(chǎn)環(huán)境執(zhí)行 KILL 要格外謹(jǐn)慎測試環(huán)境可以放開操作。用 EXPLAIN 分析慢查詢也是排查索引問題的常用手段。EXPLAIN SELECT * FROM test_order WHERE user_id 10086 AND status 1;關(guān)注type字段和rows字段。type為ALL說明走了全表掃描rows數(shù)值很大則說明掃描了大量行。測試階段發(fā)現(xiàn)查詢慢最直接的建議是核對 where 條件的字段是否建了索引。10. 測試工程師建議養(yǎng)成的 MySQL 使用習(xí)慣本文最后一部分給出幾條實(shí)戰(zhàn)層面的建議。第一條操作數(shù)據(jù)庫前先確認(rèn)環(huán)境。測試環(huán)境、預(yù)發(fā)環(huán)境、生產(chǎn)環(huán)境的庫可能長得一模一樣但數(shù)據(jù)完全不同。連接前看清楚 host 和數(shù)據(jù)庫名避免連錯(cuò)環(huán)境。日常操作中把不同環(huán)境的連接信息分開存放不要混用。第二條UPDATE 和 DELETE 前先 SELECT。先把 WHERE 條件放到 SELECT 語句里執(zhí)行確認(rèn)命中的數(shù)據(jù)就是你要改的數(shù)據(jù)再把它改成 UPDATE 或 DELETE。這個(gè)習(xí)慣能避免大量誤操作。第三條寫完 SQL 先格式化。MySQL 命令不區(qū)分大小寫但項(xiàng)目和團(tuán)隊(duì)一般有規(guī)范的寫法關(guān)鍵字大寫、表名字段名小寫、縮進(jìn)對齊對排查問題更有幫助。自己維護(hù)一份常用 SQL 片段庫下次直接復(fù)制改條件。第四條多表查詢優(yōu)先用 JOIN而不是在 WHERE 里寫逗號關(guān)聯(lián)。JOIN 寫法更明確ON條件把關(guān)聯(lián)關(guān)系表達(dá)得很清楚可讀性更好排查問題時(shí)也更容易定位。第五條查數(shù)據(jù)時(shí)要有“結(jié)果導(dǎo)向”。不要為了查而查先想清楚要驗(yàn)證什么業(yè)務(wù)邏輯再?zèng)Q定查哪些字段、加哪些條件。這樣既能提高效率也能加深對業(yè)務(wù)的理解。第六條不要在生產(chǎn)環(huán)境隨意執(zhí)行 UPDATE、DELETE。如果確實(shí)需要必須經(jīng)過審批并盡量使用事務(wù)控制確認(rèn)影響行數(shù)后再提交。11. 總結(jié)與下一步軟件測試工程師的 MySQL 查詢命令實(shí)戰(zhàn)核心就是把 SELECT、WHERE、ORDER BY、LIMIT、聚合函數(shù)、JOIN 和子查詢用熟。這些命令本身不難難的是在不同測試場景下知道自己該查什么、怎么查、怎么驗(yàn)證結(jié)果。建議按照下面的順序做一輪實(shí)戰(zhàn)練習(xí)。第一步連上測試環(huán)境數(shù)據(jù)庫用 SHOW DATABASES 和 DESC 熟悉表結(jié)構(gòu)。第二步抄寫本文第 3 節(jié)的單表查詢示例改成你項(xiàng)目里的真實(shí)表名和字段名跑通一遍。第三步找一張有狀態(tài)字段的業(yè)務(wù)表用 GROUP BY 加 CASE WHEN 做一次狀態(tài)統(tǒng)計(jì)驗(yàn)證和前端報(bào)表數(shù)字是否一致。第四步找兩張有關(guān)聯(lián)關(guān)系的表用 LEFT JOIN 查一次包含 NULL 的數(shù)據(jù)判斷是否存在孤兒數(shù)據(jù)。第五步把 UPDATE、DELETE 放入事務(wù)中執(zhí)行練習(xí) COMMIT 和 ROLLBACK。完整跑完這幾步日常測試中涉及 MySQL 查詢命令的基本場景就都能覆蓋了。后面再遇到數(shù)據(jù)庫相關(guān)的測試任務(wù)可以直接翻這篇文章按圖索驥。建議收藏備用。