使用 Navicat 匯入 Excel 檔案到 MySQL 資料庫，出現資料總筆數不一致的問題。 解決方式：刪除空白行、檢查是否有主鍵重複的資料。
 |
| Photo by Ian Livesey on StockSnap |
問題狀況
Navicat 匯入 Excel 檔案到 MySQL 資料庫後，發現資料總筆數不一致。在訊息視窗中發現許多 INSERT INTO my_table (column1, column2) VALUES (NULL, NULL) 等錯誤語法。
問題原因
1. 空白列問題
在 Excel 檔案中，底部通常存在許多空白列，使用 NaviCat 進行資料匯入時可能會遇到問題。NaviCat 有時無法自動忽略這些空白列，反而會嘗試將它們作為資料進行匯入。由於這些空白列中的所有欄位均為 NULL，會造成生成 VALUES (NULL, NULL, ...) 的指令。錯誤訊息摘錄：
[ERR] INSERT INTO `my_data_base`.`my_table ` (`id`, `content`) VALUES ('', ''),('', ''),('', ''),('', ''),('', ''),('', ''),('', ''),('', ''),('', ''),('', ''),('', ''),('', ''),('', ''),('', ''),('', ''),('', ''),('', ''),('', ''),('', ''),('', ''),('', ''),('', ''),('', ''),('', ''),('', ''),('', ''),('', ''),('', ''),('', ''),('', ''),('', ''),('', ''),('', ''),('', ''),('', ''),('', ''),('', ''),('', ''),('', ''),('', ''),('', ''),('', ''),('', ''),('', ''),('', ''),('', ''),('', ''),('', ''),('', ''),('', ''),('', ''),('', ''),('', ''),('', ''),('', ''),('', ''),('', ''),('', ''),('', ''),('', ''),('', ''),('', ''),('', '')
此外，NaviCat 在匯入資料時並非逐筆處理，而是採用批量匯入的方式。若在最後一批資料匯入過程中發生錯誤，則可能導致總資料筆數不一致。
2. 重複資料問題
當 Excel 檔案包含重複的資料，違反資料表定義，也會導致部分資料無法成功匯入。
解決方式
方法1. 刪除 Excel 中的空白列和重複資料
1. 清空目標資料表
2. 手動刪除 Excel 檔案後面的空白列
3. 手動刪除 Excel 檔案主鍵重複的資料
如果主鍵在 Excel A 欄，在儲存格輸入以下公式：
= COUNTIF(A:A, A1)
此公式用於計算 A 欄中特定值的出現次數。若結果大於等於 2，則表示存在主鍵重複。
公式說明：
- 使用的函數：COUNTIF 函數。用於計數在符合單一條件的範圍內的儲存格數量。
- 指定的範圍：A:A 指的是整個 A 列。代表該函數將檢視 A 欄中的所有儲存格。
- 指定的條件：A1 是查詢條件。該函數將計算 A1 儲存格中的值在 A 欄出現了多少次。
4. 重新匯入資料
方法2. 修改資料表欄位，允許 NULL 值和主鍵重複
1. 修改目標資料庫欄位定義 (schema)，使所有欄位允許 NULL 值和主鍵重複。
2. 清空目標資料表
3. 重新匯入資料
4. 清除所有欄位均為 NULL 的資料。
5. 建立一個新的資料表，其中主鍵不允許 NULL 值，並將資料從原資料表複製過去，使用此方式刪除重複的主鍵資料。
留言
張貼留言