MySQL 報表用 NULLIF 處理零分母,分清真正的 0% 與缺少可用造訪數,並用總訂單和總造訪計算合計轉換率。
2026-11-14 0 0
GROUP_CONCAT 串出的標籤或附件清單不完整?用三筆示例核對位元組上限、截斷警告與同連線 SESSION 設定,分清來源列與輸出結果。
2026-11-04 0 0
訂單報表原本對得上,加入商品數量與付款金額後,營業額卻突然變多。這時先別改訂單資料,也別急著在SUM裡加DISTINCT。常見原因是同一筆訂單有多件商品、也有多筆付款;兩張明細表直接JOIN,會讓彼此的資料重複出現。 修正的關鍵是讓每張明細表先變成「一筆訂單一列」,再連回訂單表。下面用三筆虛構訂單比較原始總額、錯誤查詢與修正查詢,連沒有商品或付款的訂單也一起保留。 先算出三張表各自的答案 這組範例只放在自有測試資料庫。第一、第二筆訂單刻意使用相同金額120,第三筆為80;這樣才能看出按金額去重會漏算。items代表商品明細,qty是數量;payments代表付款紀錄,其amount與訂單表的amount是不同統計。 CREATE TABLE orders ( id INT PRIMARY KEY, amount DECIMAL(10,2) NOT NULL ); CREATE TABLE items ( id INT PRIMARY KEY, order_id INT NOT NULL, qty INT NOT NULL ); CREATE TABLE payments ( id INT PRIMARY KEY, order_id INT NOT NULL, amount DECIMAL(10,2) NOT NULL ); INSERT INTO orders VALUES (1,120), (2,120), (3,80); INSERT INTO items VALUES (11,1,2), (12,1,1), (21,2,1); INSERT INTO payments VALUES (31,1,60), (32,1,60), (33,2,120); 單看orders,三筆訂單總額是320;items的商品總數是4;payments的付款總額是240。第三筆訂單還沒有商品或付款明細,因此不能把訂單總額320與已付款240當成同一個數字。真實網站還要定義取消、退款與付款狀態,這裡先把連接造成的重複算清楚。 SELECT COUNT(*) AS orders_n, SUM(amount) AS order_total...
2026-10-05 138 0
測試腳本先START TRANSACTION,再插入一筆資料、加一個欄位,最後ROLLBACK。你以為全部會撤回,結果資料與欄位都還在。先查中間有沒有造成隱式提交的語句:MySQL的ALTER TABLE會打斷原本交易,不能只因腳本外面寫了交易,就把資料變更與結構變更視為一整包可回滾。 ROLLBACK只處理仍在交易中的變更 一般InnoDB資料操作可以在明確交易中由COMMIT提交或ROLLBACK回滾,但一些語句會自己形成提交邊界。MySQL官方文件列有ALTER TABLE等DDL語句;許多這類語句在執行前提交目前交易,並在執行後形成自己的提交。讀腳本時要查看整個語句序列,不能只找開頭與結尾。 這篇聚焦ALTER TABLE新增一般欄位的實測。不是所有SQL、所有DDL或所有資料庫引擎都用同一條規則;例如暫存表相關語句有文件列出的不同界線。碰到具體語句時,核對MySQL對應版本的隱式提交清單,不要只用「DDL都一樣」一句話跳過。 在自己的小表重現提交邊界 案例使用自有隔離MySQL 8.4.9,只有一張明確指定ENGINE=InnoDB的小表,沒有對外開放連接埠,也沒有讀寫正式網站資料。先在交易外建立原始表,再開始交易;這樣觀察點就是交易內的INSERT與ALTER,而不是把建立資料庫的影響混在一起。 CREATE TABLE records ( id INT PRIMARY KEY, label VARCHAR(30) ) ENGINE=InnoDB; START TRANSACTION; INSERT INTO records VALUES (1,'before-ddl'); ALTER TABLE records ADD COLUMN note VARCHAR(30) NULL; ROLLBACK; 接著查看資料與欄位。本次ROLLBACK之後,id 1的before-ddl仍存在,note欄位也存在且值為NULL。這不是只用介面快取推測:測試直接執行SELECT和SHOW COLUMNS,保存伺服器回傳的結果。 SELECT * FROM records ORDER BY id; SHOW COLUMNS FROM records; -- 仍有:1 / before-ddl / NULL -- 欄位仍有:id、label、note 在INSERT與ALTER之間沒有手動COMMIT。這正是案例要核對的地方:ALTER觸發提交邊界,讓先前那筆INSERT不再屬於最後ROLLBACK能撤回的未提交工作。若只說「新欄位沒被撤回」,還不足以指出資料操作也被提交;所以這裡同時檢查列與結構。 atomic DDL不等於你可手動回滾整段腳本 MySQL 8.4支援特定範圍的atomic DDL,官方用來描述DDL操作中資料字典、儲存引擎與二進位紀錄等更新的原子性。它讓受支援的DDL在完成或失敗時有一致的結果,但不把DDL變成能與先前DML放在同一使用者交易裡、最後一起ROLLBACK的語句。 兩個「原子」的對象不同:一個是在討論DDL這項操作如何完成,另一個是你希望整份腳本的多個步驟都一起撤回。這個小表沒有模擬當機或DDL執行中斷,也沒有驗證故障恢復;它只證明一般ALTER成功之後,最後ROLLBACK不會把這次結構與先前插入一起還原。 不要把這份結果寫成「InnoDB不支援交易」。表仍是InnoDB,問題是交易中插入了會提交的語句。也不要把SET autocommit=0當成通用解法;自動提交設定與特定語句的隱式提交規則是不同層次,關掉前者不會抹掉後者。 把資料修改與結構遷移分開規劃 如果工作只需修改既有資料,先使用明確交易包住需要一起成功的DML,再確認中間沒有隱式提交語句。需要加欄位、改索引或調整結構時,另立遷移步驟、驗收與恢復方式。不要把DDL藏在「若欄位不存在就加一下」的資料匯入程式裡,還對外保證整次匯入可以回滾。 對正式表的結構修改還牽涉資料量、鎖、執行算法與應用程式相容性。這篇一列表的ALTER很快,不代表正式大表不會阻塞,也沒有證明可以無停機部署。這些需要依實際ALTER、表與環境另外評估;本篇只處理交易邊界,不順手給出正式DDL命令。 遷移的回復方式也不一定是執行反向ALTER。新增欄位後如果已有程式寫入資料,直接刪除欄位可能丟失內容;修改型態後也未必能無損轉回。先保存必要備份、定義可接受的恢復點與相容期間,再決定回復設計,不能把「再改回去」等同交易回滾。 排查時重建真正的語句順序 先把測試的起始狀態也保存:原表只有id與label,沒有資料;結束時有一列與新增note。這樣能辨認變更究竟出自本次腳本,還是前一輪測試留下的內容。重跑時使用新測試表或新隔離資料庫,不要在已變更的表上再加同名欄位,然後把重複欄位錯誤混成原案例。 備份能協助恢復已提交變更,但它不是ROLLBACK的另一個名稱。恢復備份可能影響備份之後的其他合法資料,所以必須選定範圍與時間點。交易邊界寫清楚,才知道恢復方案需要處理哪些已經超出當前交易的工作。 先整理BEGIN或START TRANSACTION之後到COMMIT或ROLLBACK之前的每一條SQL,包含框架或工具自動送出的語句。應用程式裡有transaction函式,不代表函式內每個呼叫都遵守同一交易邊界;若中間執行ALTER,仍要按資料庫實際規則解讀。 保留相同連線中的版本、儲存引擎與最小重現結果。不同連線有各自的交易,不能在一個終端ROLLBACK後,到另一個應用程式畫面看到資料,就直接推論第一個交易沒有工作。這篇在同一個客戶端連線完成序列,再讀取結果,避免這種混淆。...
2026-10-05 49 0
網站報表選了某一天,結果只出現零點資料;把結束時間改到23:59:59,又少了最後一秒裡的小數時間。查詢先別怪資料沒寫入:DATETIME欄位包含時間,而日期輸入並不代表「整天的所有時刻」。用當日開始到次日開始的半開區間,可以把這個邊界寫清楚。 BETWEEN兩端都包含,但日期不是一整天 BETWEEN包含下限與上限。在本篇DATETIME案例中,兩端都寫同一個日期,得到的是同一天午夜到同一天午夜,不是從早到晚。日期與日期時間在比較時會涉及型態轉換,因此最好先寫出你實際要比較的兩個時刻,而不是靠「選了一天」這句介面文案猜測SQL意思。 SELECT id FROM events WHERE occurred_at BETWEEN '2026-09-20' AND '2026-09-20'; 這個查詢在本次資料只找到id 1,也就是00:00:00那筆。若表裡沒有午夜資料,可能一筆都找不到。它不代表BETWEEN不能用,而是兩個界線相同;即使使用者輸入同一天,應用程式仍要把需求轉成正確的時間區間。 用三筆邊界資料實測 案例使用自有隔離MySQL 8.4.9,欄位明確設定DATETIME(6),讓微秒能保留下來。三筆分別是當日午夜、當日最後一個微秒,以及次日午夜。所有字面值沿用同一個時間約定,沒有在案例裡做時區換算,也沒有操作正式報表資料。 CREATE TABLE events ( id INT PRIMARY KEY, occurred_at DATETIME(6) NOT NULL ); INSERT INTO events VALUES (1,'2026-09-20 00:00:00'), (2,'2026-09-20 23:59:59.999999'), (3,'2026-09-21 00:00:00'); 如果欄位只是DATETIME而沒有指定小數精度,不能直接宣稱同樣保存六位微秒。先用SHOW CREATE TABLE核對真實欄位,再選符合其精度的測試資料。本例刻意用DATETIME(6),就是為了讓「只寫到整秒的結束時間」與「該秒內稍後的資料」出現實際差異。 結束寫23:59:59,仍會漏掉小數秒 SELECT id FROM events WHERE occurred_at BETWEEN '2026-09-20 00:00:00' AND '2026-09-20 23:59:59' ORDER BY id; 這次仍只得到id 1。id 2的時間是23:59:59.999999,比23:59:59更晚,所以不在區間內。把上限寫成23:59:59.999999可以配合目前六位精度,但往後換欄位或組合不同時間來源時,仍得重新考慮精度。用次日開始作排他上限通常更容易表達整天的需求。 也不要直接把上限改成次日00:00:00後繼續用BETWEEN,因為上限仍會被包含,id 3就可能落入前一天的報表。如果隔天的報表又包含自己的午夜,同一筆資料可能被兩天重複計算。問題的關鍵是上限是否包含,不是換一個看起來更晚的日期就完成了。 改成大於等於開始,小於次日開始 SELECT id FROM events WHERE occurred_at >= '2026-09-20' AND occurred_at < '2026-09-21' ORDER BY...
2026-10-05 89 0