MySQL 轉換率遇到零分母?用 NULLIF 區分 0% 和無法計算
MySQL 報表用 NULLIF 處理零分母,分清真正的 0% 與缺少可用造訪數,並用總訂單和總造訪計算合計轉換率。
MySQL GROUP_CONCAT 清單只出一半:先查連線的長度上限
GROUP_CONCAT 串出的標籤或附件清單不完整?用三筆示例核對位元組上限、截斷警告與同連線 SESSION 設定,分清來源列與輸出結果。
MySQL訂單金額加總翻倍:兩張明細表先彙總再JOIN
訂單報表原本對得上,加入商品數量與付款金額後,營業額卻突然變多。這時先別改訂單資料,也別急著在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...
寶塔新增MySQL資料庫後連不上:資料庫名、使用者與密碼分開核
寶塔已新增MySQL資料庫,網站仍提示無法連線,先分清資料庫名稱、登入帳號、密碼與連線主機。它們可能在建立時使用相似字串,但各自負責不同工作;把某個欄位填對,不代表整組連線資訊已一致。 以下依寶塔與MySQL官方文件整理核對順序,沒有替你的面板建立帳號或修改密碼。先保留錯誤及現有設定,再查真正使用的服務入口,避免為了網站連線失敗就重設整台資料庫的root密碼。 資料庫名稱是資料位置,帳號是登入身分 寶塔新增資料庫頁把資料庫名、使用者名稱、密碼與訪問權限分開。資料庫名用來指定資料所在範圍;使用者名稱是登入資料庫的帳號,不應因面板預設或自己命名相同,就把兩個概念合併。 在程式配置中逐欄核對,不要只比對某一個名稱。也確認程式是否還在讀舊設定、另一個環境檔案或部署後的配置來源;磁碟上看到新值,和正在執行的程式實際使用它,是兩項不同事實。 新增資料庫可以選擇編碼,寶塔文件列出預設utf8mb4。編碼是資料保存及處理條件,不是連線帳號;不要看到編碼選項就以為它能修復密碼錯誤或網路連線問題。 先確認程式究竟連到哪台服務 MySQL官方連線文件把主機、帳號、密碼、端口及資料庫等選項分開。先核對程式指定的主機與入口,避免寶塔管理的是本機資料庫,程式卻仍連到舊主機或遠端服務。 寶塔也支援添加到本機或遠端伺服器,不能只因資料庫名稱出現在面板,就推斷它一定在同一台主機。把服務位置記清楚,再查看對應帳號與資料庫。 在Unix類環境,MySQL客戶端使用localhost通常會選Unix socket,使用127.0.0.1則可走TCP。程式驅動及設定也需要各自核對;不要把兩個字串看起來都代表本機,就一律認為它們使用完全相同的連線方式。 MySQL帳號還包含來源主機條件 MySQL官方帳號名稱文件說明,帳號由使用者名稱與主機名稱組成。因此,相同使用者名稱來自不同主機條件,可以是不同帳號;只知道名稱,不足以確認這次連線會匹配哪一組身分。 核對訪問來源時,要看資料庫服務實際看到的連線來源。容器、代理或不同主機部署可能影響它,不能直接拿你操作瀏覽器的電腦IP,替網站程式的資料庫連線作判斷。 MySQL連線驗證文件進一步說明,服務會按帳號資料與驗證條件處理連線。遇到拒絕時,保留錯誤中的使用者與來源資訊,再對照實際帳號,別先把所有來源都放開。 寶塔訪問權限不代替網路與資料權限 寶塔新增資料庫提供本地伺服器、所有人及指定IP等訪問選項,預設為本地伺服器;官方頁也把所有人標為不安全。若網站確實在遠端,按需要核對限定來源,而不是用全面開放作為通用修復。 帳號允許某個來源,也不表示資料庫服務已對該網路提供連線。服務監聽、網路路徑及既有防火牆仍是不同條件;連線逾時或拒絕時,不能只改帳號密碼。 同樣,通過登入不等於帳號具有應用需要的所有資料操作權限。依程式工作核對必要範圍,避免把一般網站帳號升到最高權限,只為消除某個操作錯誤。 網站帳號密碼與root密碼分開處理 寶塔root密碼文件明確提醒,root是最高權限帳號,需要謹慎操作。網站若使用獨立帳號,重設root不會自動替你修好那個帳號的密碼,也可能影響其他管理用途。 先確認程式使用哪個帳號,再對照相應密碼來源。不要把面板登入密碼、FTP密碼或另一個資料庫使用者的密碼填進網站配置;它們不是同一組身分。 若需要更換網站帳號密碼,先列出使用它的網站與任務,安排相應更新與驗收。只改資料庫端、未同步程式,或只改程式端、未更新真正帳號,都可能讓原本正常的連線失敗。 按錯誤現象查,不用一種操作解決全部 無法抵達服務時,先核對主機、連線方式與網路入口;驗證被拒絕時,查帳號來源與密碼;登入後找不到資料庫或操作被拒絕,則查資料庫名稱及相應權限。這個順序是診斷方向,仍需實際錯誤支持。 不要因為網站顯示通用資料庫錯誤,就直接刪除重建資料庫。資料可能仍完整存在,只是程式使用錯誤入口或身分;保留現場,才能讓維護者核對而不破壞原資料。 也不要把一次管理工具登入成功,當成網站程式一定成功。工具可能使用不同帳號、服務位置或連線方式;比較時把條件列清楚,不用不同環境的成功填補原問題。 核對時保護密碼與連線資訊 MySQL官方連線文件提醒,直接在命令參數提供密碼可能帶來暴露風險。求助時不需要貼完整密碼或可直接登入的連線字串;保存必要錯誤,敏感欄位按既有方式處理。 截圖也要看清楚面板是否顯示密碼。資料庫名稱、帳號來源與錯誤內容可協助診斷,但不代表所有配置都應公開。避免把密碼放進程式庫、文章留言或日誌旁的說明文字。 若多人共同維護,也註明哪次變更由誰安排,避免另一位維護者把已淘汰的密碼或主機地址填回去。可追蹤的更新紀錄,比重複猜測同一組連線資訊更有用。 最後以程式需要的功能驗收 確認連線條件一致後,用合法且範圍明確的網站功能核對。能登入資料庫只是其中一步;程式還需要讀到正確資料庫與內容,不應用另一個空資料庫的成功連線冒充原站恢復。 保存服務位置、資料庫名稱、帳號及限定來源等非敏感對應,另保護密碼來源與更新安排。之後搬站或改部署環境,就能知道哪些連線條件需要一起核對,不會再把資料庫名和登入身分混為一談。 參考資料寶塔官方:新增資料庫寶塔官方:root密碼MySQL:帳號名稱與HostMySQL:連線選項MySQL:連線驗證
寶塔備份完成還不能放心:網站檔案與資料庫要分別核對
寶塔備份清單顯示成功,先核對這次選了哪些資料、生成哪些檔案,以及下載後能否按預期還原。網站文件和資料庫是兩組不同內容;只取得網站目錄的壓縮包,不代表文章、訂單或帳號等資料也已包含。 以下依寶塔備份與MySQL官方文件整理檢查方法,重點放在備份產物及驗收範圍。這不是本篇已替你的網站做過備份或還原的報告,也不把一個成功狀態當成完整災難復原能力的證明。 先列出網站需要恢復的內容 把網站程式、上傳檔案、資料庫及必要設定分開列出。程式目錄可能包含圖片和設定,但資料庫通常由另外的服務管理;不要因為網站文件都在同一個寶塔站點,就推斷備份其中一項會自動包括全部。 寶塔官方建立備份頁明確分列網站資料、資料庫、應用與配置等可選項。建立之前看清楚勾選範圍,並保存當次選擇與時間;名稱叫「完整備份」,也仍需要實際內容支持。 如果網站使用外部資料庫或另一個物件儲存服務,核對它們是否在這次範圍內。不要把面板列出的本機資料庫,直接當成程式真正連線的資料來源;先查網站使用哪組服務,再安排對應備份。 按官方入口建立可辨認的備份 寶塔官方步驟從備份還原進入建立備份,設定名稱、儲存位置及執行時間,再選擇資料類型。使用有意義的名稱,讓你之後能辨認它屬於哪個網站、哪次變更及哪個時間點。 頁面會顯示預計需要的空間與剩餘磁碟空間。先核對能否完成這次備份,而不是等到磁碟用盡才發現產物不完整。備份工作本身也可能影響站點負載,應按既有維護安排處理。 本地與遠端儲存也要分開記錄。只留在同一台主機,主機故障時可能同時失去網站和備份;但選了遠端選項,也還要核對傳送結果及真正可取得的檔案,不能只看配置名稱。 清單成功之後,逐項核對產物 寶塔備份管理文件列出狀態、備份文件、大小與時間等資訊。確認對應紀錄完成,再核對實際生成的文件;不要只截一張成功標記,卻沒有保存可用產物。 下載後查看檔案是否取得完整,並記錄名稱、大小、時間與雜湊等辨認資訊。雜湊可以用來比較同一份檔案的位元組是否一致,但它本身不證明備份選對資料,也不證明資料在業務上可用。 打開網站文件的備份,核對必要目錄與上傳內容是否在裡面;再找資料庫備份,確認不是只看到網站目錄就停止。若產物格式需要專用還原工具,按該格式的文件處理,不任意改副檔名。 資料庫備份要辨認格式與內容 MySQL官方將實體與邏輯備份分開。實體備份涉及資料庫存放資料的文件;邏輯備份則保存結構和內容的邏輯表示。它們的建立與恢復方式不同,不能把普通網站檔案壓縮包當成任一種資料庫備份。 mysqldump是MySQL官方的邏輯備份工具,但不要因為檔名是.sql,就宣稱它一定由這個工具完整匯出。查看實際產物、生成方式及選項,確認你取得的內容與恢復方案相符。 也核對範圍是否包含需要的表結構、資料及其他物件。不同匯出選項和權限可能影響內容;不把一般資料表備份自動擴稱為所有使用者、權限、事件或伺服器配置都已保留。 網站文件與資料時間要一起考慮 網站持續接收上傳或寫入訂單時,文件和資料庫可能在不同時間取得。若資料庫記錄指向尚未備份的圖片,或文件中含有資料庫尚未保存的內容,還原後可能不一致。 因此,要按實際應用安排一致性策略。MySQL官方備份類型文件說明,運行中的備份仍需考慮資料修改與完整性;不能用「不用停機」直接推論任意複製都能得到一致資料。 記錄備份時間與當時使用的方案,不宣稱面板中兩個成功標記必定代表同一時間點。需要嚴格一致性的網站,應由維護者按資料庫引擎、工具及業務需求設計,不以通用教學替代。 先在合適環境核對還原,不覆蓋正式資料試運氣 寶塔備份管理文件提醒,還原會覆蓋現有資料,並要求先確認檔案完整可用。若要驗證恢復能力,應先安排獨立的目標環境與範圍,不拿正式網站當作隨意試驗的位置。 MySQL的SQL格式還原文件也區分匯出是否帶有建立及選擇資料庫的內容。目標資料庫、帳號與檔案範圍都需要核對,不能把一條網上命令直接貼進現有正式資料庫。 還原後檢查程式能否啟動、必要資料表與內容是否存在、上傳檔案能否讀取,以及關鍵功能是否符合預期。只看匯入工具沒有報錯,仍不足以代表網站已恢復到需要的狀態。 恢復結果和備份狀態分開保存 維護紀錄可分成建立成功、檔案取得、內容範圍核對及恢復驗證。每一項附上當次證據,沒有做過的部分就保留待確認,不把前面的成功自動擴成後面的通過。 若恢復時發現缺少圖片或資料,回到原備份選擇和時間定位問題;不要直接刪掉失敗紀錄,再建立一個同名產物,讓維護者失去比較依據。保留可追蹤的版本與失敗原因,才有機會改善方案。 最後才安排保存與刪除策略 保留期限應按網站資料變更與恢復需求安排,而不是只為節省空間留下唯一一份。刪除之前確認還有哪些可用版本,以及它們是否已取得到需要的位置,不因看到較新的檔名就直接清掉全部舊檔。 備份也可能含有密碼、使用者資料與私人文件,下載與保存位置需要相應保護。不要把備份放回網站公開目錄作為方便下載的捷徑,讓原本不公開的資料變成可直接取得的檔案。 最後把選擇範圍、產物清單、保存位置及恢復核對記錄放在一起。下次需要回復時,就能知道手上資料涵蓋什麼,而不是只有「寶塔曾顯示成功」這一句話。 參考資料寶塔官方:建立備份寶塔官方:備份管理與還原MySQL:備份類型與一致性MySQL:mysqldumpMySQL:SQL格式備份還原
MySQL upsert觸發更新:先看唯一鍵
會員匯入原本想新增一筆資料,結果卻改了舊會員的名稱。檢查SQL時不能只看新傳入的id,也要看資料表有哪些唯一鍵。INSERT ... ON DUPLICATE KEY UPDATE在新增資料碰到PRIMARY KEY或UNIQUE衝突時,會轉去更新既有列。這個分支是資料庫的鍵約束觸發,並不是程式先查到某個欄位就自動選對會員。 新id不代表這筆一定新增 以下例子用訂閱者表,id是主鍵,email另有UNIQUE約束。資料表先存一位id為1、email為[email protected]、name為old的訂閱者。接著送入id為2的新資料,但email仍是同一個地址。id沒有重複,不代表整列沒有唯一鍵衝突:email的UNIQUE仍會攔住新增。 CREATE TABLE subscribers ( id INT PRIMARY KEY, email VARCHAR(100) NOT NULL UNIQUE, name VARCHAR(30) NOT NULL ); INSERT INTO subscribers (id, email, name) VALUES (1, '[email protected]', 'old'); 先看表結構再讀upsert,會比只追應用程式傳入的id清楚。這裡email使用NOT NULL,避免把「沒有email」的NULL情況混進判斷;email比較仍受欄位型別與collation影響。這篇案例只用相同的小寫字串驗證衝突,沒有測試不同大小寫或所有郵件地址的相等規則,也不把資料庫字串相等當成完整郵件驗證。 用新列別名寫出更新來源 INSERT INTO subscribers (id, email, name) VALUES (2, '[email protected]', 'updated') AS incoming ON DUPLICATE KEY UPDATE name = incoming.name; AS incoming替原本嘗試新增的列取別名。更新分支的incoming.name是這次送入的updated,不是資料表目前的old。例子只更新name,沒有指定id或email的更新,因此舊列仍保留id為1與原email。使用新列別名也讓更新來源一眼可辨;MySQL文件已將更新分支中的VALUES()寫法列為棄用,不宜再把舊寫法當成新範例。這裡討論的是MySQL,不保證其他資料庫採用相同語法。 實際讀回是哪一列被更新 本地隔離案例使用MySQL 8.4.9,在新資料庫執行以上SQL後讀回整表。結果只有id為1的列,name已變成updated,沒有產生id為2的列。接著送入id為3、email為[email protected]、name為new,因兩個唯一鍵都不衝突,這次才走新增分支。兩次操作的差異是鍵是否衝突,不是名稱內容或輸入順序。 SELECT id, email, name FROM subscribers ORDER BY id; -- after same-email upsert -- 1 [email protected]...
MySQL加欄位後ROLLBACK沒撤回:DDL隱式提交的邊界
測試腳本先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後,到另一個應用程式畫面看到資料,就直接推論第一個交易沒有工作。這篇在同一個客戶端連線完成序列,再讀取結果,避免這種混淆。...
MySQL查整天少了晚間資料:DATETIME用半開區間
網站報表選了某一天,結果只出現零點資料;把結束時間改到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...
MySQL UNIQUE仍能插入多筆NULL:缺值與重複值怎麼分
優惠碼欄位加了UNIQUE,資料表卻還能存入兩筆NULL。這不是索引沒生效:在MySQL裡,可空欄位的唯一索引允許多個NULL。先確認你想防止的是「重複已填寫的代碼」,還是「每筆都一定要有唯一代碼」,兩種需求需要不同限制。 UNIQUE不等於每列必填 UNIQUE檢查的是唯一性;NULL代表缺少值,它與一般已填寫的字串不同。若欄位允許NULL,就不能期待UNIQUE同時要求每列都有代碼。MySQL的CREATE INDEX文件明確列出,允許NULL的UNIQUE索引可以包含多個NULL。 不要把這條規則延伸成所有資料庫都完全一樣,也不要把它說成「NULL等於NULL所以算一次」。NULL的一般比較有另外的語意,唯一索引的可空行為則是這裡實際要核對的規則。這篇用MySQL 8.4.9的單一字串欄位做直接插入,不混入其他引擎或複合鍵的例外。 兩個NULL成功,第二個A失敗 在自己的測試資料庫建立下面這張小表。id作為每筆資料的主鍵,code允許NULL且有UNIQUE。先一次放入兩筆沒有代碼的資料與一筆A,再嘗試新增第二筆A,才能同時看到可空行為與唯一限制是否生效。 CREATE TABLE codes ( id INT PRIMARY KEY, code VARCHAR(30) NULL UNIQUE ); INSERT INTO codes VALUES (1,NULL), (2,NULL), (3,'A'); INSERT INTO codes VALUES (4,'A'); 本次前三筆插入成功;第二個A被拒絕,錯誤碼1062。最後查詢id由小到大,結果仍是1=NULL、2=NULL、3=A,沒有id 4。這個對照說明唯一限制有工作,只是它不會把兩個NULL當成第二個A那樣的重複值。 SELECT id, code FROM codes ORDER BY id; -- 實測結果 -- 1 NULL -- 2 NULL -- 3 A 負測的錯誤訊息是案例的一部分。這次命令行讓後續查詢繼續執行,以便查看失敗後的資料,因此不能只看整個程序最後的退出碼就宣稱四筆都插入了。自己重現時,把每個INSERT的成功或1062和最後列數一起保存。 查沒有代碼的資料,要用IS NULL SELECT id, code FROM codes WHERE code IS NULL; 這個條件會找到兩筆沒有代碼的資料。不要改成code = NULL;一般等號比較遇到NULL,不會得到你要的真值。也不要寫code = 'NULL',那是四個字母構成的字串,與資料庫的空值完全不同。 管理工具常把NULL顯示成灰字或特殊標記,匯出工具又可能寫成空白。看到畫面空白時,先查IS NULL,再查等於空字串的情況;兩者不能只靠視覺判定。回到網站表單,也要確認未填欄位最後送入資料庫的是NULL、空字串,還是程式預設值。 MySQL也提供NULL安全的比較運算子,但那是在查詢中定義比較方式,不會把現有UNIQUE索引自動改成「只允許一個NULL」。查詢條件與資料限制分開處理,才不會因為某個SELECT能比對空值,就誤認為INSERT應有相同規則。 空字串不是NULL,必填也不是唯一 回傳JSON時也應保留這個差別。NULL可以對應JSON的null,空字串仍應是字串;如果程式為了方便顯示把兩者都轉成同一句「沒有資料」,只能把它當作顯示文字,不能再拿那句文字寫回代碼欄位。資料層的原始型態應留著,才能讓後面的檢查與更新作出正確選擇。 空字串是已存在的字串值;在一般單欄唯一限制下,重複的相同空字串仍會遇到唯一性檢查。這篇真插入案例只測了NULL與A,沒有另外插入兩筆空字串,所以不要把它的輸出冒充所有輸入型態都已實測。文件區分NULL與空字串的規則,足以提醒你先核對實際資料。 如果業務要求每筆都有代碼,欄位可考慮NOT NULL加UNIQUE,再由表單與後端處理是否允許空字串、只含空白或特定格式。NOT NULL不會自行禁止空字串,也不會完成格式驗證;欄位限制與輸入規則各有責任。...
MySQL GROUP BY選名字報錯:別讓非分組欄位隨機取值
分類列表想同時顯示每類筆數與最新一筆名稱,SQL卻回傳1055錯誤。別先關掉ONLY_FULL_GROUP_BY。這個錯誤通常是在問你:同一組裡有多個名稱,結果只留一列時,到底要顯示哪一個?先回答這個問題,比讓查詢勉強執行更有用。 分組後,一般欄位可能沒有唯一答案 先在出錯的同一條連線查SELECT @@SESSION.sql_mode;,確認目前是否包含ONLY_FULL_GROUP_BY。開發工具、應用程式連線與另一個終端可能使用不同連線設定;只讀了全域設定,未必解釋應用程式正在使用的模式。診斷時保留版本與當前連線值,不必先改伺服器設定。 GROUP BY category把同類資料集合成一組,COUNT(*)可以計算組內列數。但name沒有經過聚合,同一分類若有Alpha、Beta與Aardvark,就沒有一個自然且唯一的name可放到結果中。下列查詢在本案例MySQL 8.4.9啟用ONLY_FULL_GROUP_BY時被拒絕,錯誤碼是1055。 SELECT category, name, COUNT(*) FROM items GROUP BY category; 這不是所有未列在GROUP BY中的欄位都永遠禁止。MySQL也會判斷功能依賴,例如分組鍵能唯一識別一筆資料時,其他欄位可能有唯一答案;文件也列有WHERE限制為單一值的情況。本例category既不是唯一鍵,同類又有不同name,因此不能靠這些例外取得所需結果。 把name也加入GROUP BY,雖然可能讓查詢合法,卻把「每個分類一組」改成「每個分類與名稱的組合一組」。如果你要的是分類總數,這不是原來的需求。修正SQL前先列出結果應有幾列,以及每列代表什麼,避免只修掉錯誤訊息。 建立可分辨最大名稱與最新資料的案例 下面在自有隔離測試資料庫使用四筆資料。A分類有三個名稱,最新日期刻意配Aardvark;按目前測試排序規則比較字串時,最大名稱則是Beta。這樣兩種需求會得到不同答案,不會因為測試資料巧合而誤以為MAX等於最新。 CREATE TABLE items ( id INT PRIMARY KEY, category VARCHAR(10), name VARCHAR(30), created_at DATETIME ); INSERT INTO items VALUES (1,'A','Alpha','2026-09-01'), (2,'A','Beta','2026-09-02'), (3,'B','Gamma','2026-09-01'), (4,'A','Aardvark','2026-09-03'); 本次案例只在當前連線設定測試用sql_mode,再執行負測與兩種正確查詢;沒有修改正式伺服器的全域模式。負測輸出的1055保留在測試紀錄,命令行允許後續語句繼續執行,所以整段程序最後返回成功也不代表所有SQL都成功。 MAX(name)回答的是最大字串 SELECT category, MAX(name) AS greatest_name, COUNT(*) AS total FROM items GROUP BY category; 本次A組得到greatest_name=Beta、total=3;B組得到Gamma、1。查詢清楚地要求字串最大值,所以是合理的分組聚合。但它沒有查詢日期,不能回答「最新建立的名稱」。字串比較還受欄位排序規則影響,不要把MAX理解成插入順序或肉眼覺得較新的名稱。 如果需求確實只是統計每類筆數,直接選category與COUNT(*)就好,不必額外加一個沒有用途的名稱。若要每類最大金額,MAX(amount)也有明確意義;但最大金額與其他欄位分別聚合後,可能來自不同原始列。需要整列資料時,應另寫選列規則。 COUNT(*)計算列數,COUNT(name)則不計入名稱為NULL的列。本例每列都有名稱,所以沒有測出兩者差異;套用到實際報表時,別只因結果暫時相同就互換統計式。 同樣地,ANY_VALUE(name)只是讓你接受組內某個值,不是最新列的別名。在本題裡使用它會跳過「選哪一列」的責任。把ONLY_FULL_GROUP_BY關掉也無法替你補上日期規則,還可能讓讀者以為任意結果是業務要求。 在分組查詢最後加ORDER BY created_at DESC,也不能把組內的name變成最新資料。結果列的排序與組內取值是不同工作;先選出什麼結果,再排列結果,不能倒過來解讀。若錯誤查詢沒有得到合法結果,就更不能用後面的ORDER BY來證明某個名稱會被選中。 要最新整列,用視窗排序選第一列 SELECT category, name FROM ( SELECT category, name, ROW_NUMBER()...
MySQL翻頁同分資料順序變了:ORDER BY要加唯一鍵
網站列表依分數排序,翻到下一頁卻看到重複項目。先確認查詢有沒有把「同分時誰先顯示」寫清楚。ORDER BY score DESC只決定分數不同的先後;四筆資料都是一百分時,它們在排序條件中仍是平手。LIMIT與OFFSET只是切出位置,不會替你補齊平手規則。 先看排序是否能區分每一筆結果 常見查詢是SELECT ... ORDER BY score DESC LIMIT 2。它不等於「同分依id由小到大」,也不等於「依新增時間顯示」。若你需要其中一種規則,就必須把它放進ORDER BY;資料目前看起來剛好按照id,不能當成永久契約。 MySQL手冊明確指出,ORDER BY欄位值相同的資料,其餘欄位的先後可能不同,且LIMIT可能影響執行方式。因此不能根據這次兩次查詢結果一樣,就認定排序已完整。這篇案例也不刻意把資料庫弄成每次亂序:它要驗證的是明確排序後應取得哪一組id,不是假裝每個執行計畫都會重排。 用五筆資料重現同分邊界 下面只在自己的測試資料庫建立小表。案例使用MySQL 8.4.9,四筆一百分的id依4、2、3、1順序插入,最後再加入一筆九十分。插入順序特意不同於id順序,讓兩者容易區分;不要在正式列表資料表上刪除或重建資料。 CREATE TABLE scores ( id INT PRIMARY KEY, score INT NOT NULL ); INSERT INTO scores VALUES (4,100), (2,100), (3,100), (1,100), (5,90); SELECT id, score FROM scores ORDER BY score DESC LIMIT 2; 本次第一個查詢得到id 1與2,但這只是實際觀察值。SQL仍只要求分數由高到低,沒有要求同分的id順序。不要把這份輸出包裝成某種索引下的固定保證,也不要靠多跑幾次直到出現不同結果才承認問題。 加唯一鍵作最後一層排序 先問一個具體問題:四筆一百分的項目,產品希望哪兩筆在第一頁?若沒有答案,查詢也不可能自行猜中。可以依建立時間、人工優先序或唯一編號,但必須選定規則;只加一個非唯一欄位,遇到它也相同時仍然沒有解決平手。 SELECT id, score FROM scores ORDER BY score DESC, id ASC LIMIT 2 OFFSET 0; SELECT id, score FROM scores ORDER BY score DESC,...
MySQL NOT IN突然查不到資料:NULL與NOT EXISTS的差別
名單排除查詢本來正常,封鎖表多了一筆資料後,結果突然一列都沒有。你可能寫了WHERE id NOT IN子查詢,預期把封鎖的編號移除;真正改變結果的,卻是子查詢裡混入了一個NULL。 這不是MySQL把所有編號都視為相同。NULL代表未知,普通比較可能得到NULL這個未知結果;WHERE只保留條件為真的列。要修正查詢,先確認欄位是否允許NULL,再決定排除規則是在比較值,還是在問「有沒有一筆符合條件的紀錄」。 用三個編號重現空結果 以下在新的隔離資料庫中建立示範表。items的id是主鍵,不允許NULL;blocked的item_id刻意允許NULL,模擬一筆尚未填入編號的封鎖紀錄。不要在正式資料庫直接建立或覆蓋同名表。 CREATE TABLE items(id INT PRIMARY KEY); CREATE TABLE blocked(item_id INT NULL); INSERT INTO items VALUES (1),(2),(3); INSERT INTO blocked VALUES (2),(NULL); SELECT id FROM items WHERE id NOT IN ( SELECT item_id FROM blocked ) ORDER BY id; 在隔離MySQL 8.4.9實跑,這個查詢返回零列。封鎖編號只有2,卻連1和3也沒留下。先直接檢查子查詢結果,會看到2與NULL;不要先懷疑排序、頁面分頁或快取,這個最小案例在資料層就能重現。 NULL讓否定比較也無法成立 對一般非NULL值,可以把NOT IN的含意理解成「不同於名單裡每一個值」。對1而言,1不同於2是真的,但1與NULL的普通比較得到未知;兩者結合後無法成為真。對2而言,已經命中名單的2,否定結果為假。於是沒有任何一列通過WHERE。 SELECT 1 NOT IN (2, NULL) AS unknown_result, 2 NOT IN (2, NULL) AS blocked_result; -- unknown_result = NULL -- blocked_result = 0 這裡的NULL不是空字串,也不是0。不要把資料中的NULL直接換成某個你猜測「不會用到」的編號,可能日後和真資料衝突。也不要使用= NULL查找缺值,普通等號仍會得到未知;檢查NULL應使用IS NULL或IS NOT NULL。 子查詢的輸出即使只有一筆NULL,也足以影響其他沒有命中的值。這個現象不需要大量資料或特殊交易隔離才能發生,先把查詢縮成幾列,就比較容易看出哪個值帶來未知條件。 排除名單不應含NULL時,先過濾缺值...
- 1
- 2
