最終讓我建造這個的任務很無聊,而且花了一個小時,但我沒有。一位客戶遞給我一個包含 1,800 個產品行的電子表格,並要求我在第二天早上演示之前將其加載到他們的暫存資料庫中。沒有 API,沒有匯入螢幕,只有一個 CSV 和一個 MySQL 表等待它。我做了每個人第一次都會做的事情:我打開文件,手寫幾個 INSERT 語句以獲得正確的形狀,然後開始複製其中的值。三行後,產品名稱中帶有撇號, O'Brien's Tools,打破了整個語句,因為單引號提前關閉了字串,而 MySQL 則阻塞了其餘部分。就在那時,您意識到從電子表格中手寫 SQL 不是一項 5 分鐘的工作,它是一個引用雷區,有 1,800 次出錯的機會。所以我建立了 CSV 到 SQL 轉換器,本指南是其中每個選項背後的推理。
TL;DR: CSV 到 SQL 轉換器讀取 CSV 檔案並產生
CREATE TABLE和INSERT聲明資料庫需要儲存它。 Toolz 工具使用 RFC 4180 狀態機解析文件,推斷每列是整數、十進位、布林值還是文本,引用 MySQL、PostgreSQL 或 SQLite 的標識符,正確轉義每個值,並將空單元格轉換為NULL. 貼上 CSV,選擇您的方言,然後複製一個可以立即運行的腳本。一切都運行客戶端:不上傳、不註冊、離線工作。
我在 Laravel 和 React 上建立 SaaS 產品,並運送 WordPress 插件,因此將表格資料載入到資料庫中每週都是一件苦差事,而不是一次性的。有時它是客戶端導出,有時是用於新鮮環境的種子文件,有時是測試的快速固定裝置。 CSV 始終是其到達的格式,SQL 始終是其需要結束的位置。手動從一個到另一個正是瀏覽器工具應該刪除的重複性、容易出錯的工作,所以這就是我希望在撇號破壞我的導入時擁有的指南。
什麼是 CSV 到 SQL 轉換器?
CSV 到 SQL 轉換器採用逗號分隔的值行並產生 SQL 語句,該語句建立表格並將資料載入到其中。 CSV 本身只是鬆散的標準化 - RFC 4180 描述常見的形式,並明確它記錄了現有的實踐而不是口述它。它將第一行讀作列名,將後面的每一行視為記錄,並發出兩個東西: CREATE TABLE 定義具有合理類型和一組列的語句 INSERT 攜帶值的語句。輸出是一個普通的 SQL 腳本,您可以將其貼上到資料庫用戶端(例如 TablePlus 或 DBeaver)中、放入遷移中或放入管道中 mysql, psql,或者 sqlite3 命令列一步建立和填充表。
這個類別存在的原因是翻譯的方式很繁瑣,很容易被低估。每個文字值都必須用單引號括起來。值內的每個引號都必須加倍,這樣它就不會終止字串。數字和布林值必須不引用,否則資料庫會將它們儲存為文字和您的 WHERE price > 100 會表現得很奇怪。通常需要變成空細胞 NULL 而不是空字串。帶有空格或保留字的列名稱必須使用資料庫的正確字元來引用。錯過數千行中的任何一個,整個批次都會失敗,通常會出現指向錯誤行的錯誤。轉換器每次都以相同的方式應用所有這些規則,這就是整個價值主張。
工具如何決定每一列's類型?
這是區分有用轉換器和啞轉換器的部分。每列都有一個簡單的工具 TEXT 並稱之為完成,這在技術上有效,但給你一個表格,其中數字排序為字串,並且你不能在不進行轉換的情況下進行算術。工具 CSV 到 SQL 工具 相反,掃描列中的每個值並選擇適合所有值的最窄類型。
一旦你看到它,邏輯就會很簡單。如果一列中的每個非空值都是整數,則該列變成整數類型。如果每個值都是數字,但有些值有小數點,則它變成浮動類型。如果每個值都是單字 true 或者 false,它變成了一個布林值。任何其他東西,包括混合內容和任何帶有字母的東西,都會回退到文本中。有一個故意的例外,它以一種很好的方式吸引了人們:像這樣的值 007 或者 00123 保持文字,而不是整數,因為前導零幾乎總是意味著該列是一個 ID、一個郵政編碼或一個電話號碼,其中零很重要,如果儲存為數字,就會遺失。這條規則使我免於默默地損壞多個產品 SKU 列。
如果您希望每一列都是文本,您可以關閉推理,這是您計劃在加載後自行更改類型時的安全選擇。但對於常見情況,讓工具推斷類型意味著生成 CREATE TABLE 匹配資料而不是將其扁平化,並且該表在存在時就可用。
我應該選擇哪種 SQL 方言?
SQL 是一個標準,就像英語拼字是一個標準一樣,也就是說每個資料庫都有自己的口音。您選擇的方言會改變輸出中的三件事:如何引用標識符、呼叫什麼類型名稱以及如何編寫 true 和 false。以下是四個選項的不同之處。
| 關心 | 我的SQL | PostgreSQL | SQLite | 標準sql |
|---|---|---|---|---|
| 標識符引用 | 退稅 `col` |
雙引號 "col" |
雙引號 "col" |
雙引號 "col" |
| 整數類型 | INT |
INTEGER |
INTEGER |
INTEGER |
| 十進制類型 | DOUBLE |
DOUBLE PRECISION |
REAL |
REAL |
| 布爾類型 | TINYINT(1) |
BOOLEAN |
INTEGER |
BOOLEAN |
| 文字類型 | VARCHAR(255) |
TEXT |
TEXT |
TEXT |
| 布爾值 | 1 / 0 |
TRUE / FALSE |
1 / 0 |
TRUE / FALSE |
實用指南是將方言與您實際載入的資料庫進行匹配,因為差異不是表面上的。除非您處於 ANSI 模式,否則 MySQL 將拒絕 PostgreSQL 想要的雙引號標識符,而 PostgreSQL 沒有 TINYINT. SQLite 根本沒有真正的布林類型,因此布林值變成整數,這就是工具寫入的原因 1 和 0 對於 MySQL 和 SQLite 但 TRUE 和 FALSE 對於 PostgreSQL 和標準 SQL。如果您不確定或正在編寫可移植的內容,標準 SQL 是最保守的選擇。產生語句後,您始終可以透過 運行它們 SQL 格式化程式 在輸出進入遷移檔案之前對其進行漂亮的列印。
如何使用該工具將 CSV 轉換為 SQL?
流程故意縮短。將 CSV 貼到輸入框中,或按一下「載入範例」以查看包含 ID、姓名、角色、布林值和薪資列的工作範例。
將表格名稱設定為應呼叫的任何目標表格,然後選擇您的 SQL 方言。然後決定輸出選項。離開並引用;包括建立表格並引用;如果該表尚不存在,則將其關閉以僅產生 INSERT 當您載入到已有的表中時的語句。保留並引用;推斷列類型和引用;開啟鍵入的表格,或關閉以使所有內容都成為文字。選擇並引用;多行插入並引用;對於具有多個值元組的緊湊語句,載入速度最快,或將其關閉以獲得一個 INSERT 每行,這對版本控制更友好,並且可以讓您單獨運行行。離開並引用;空單元格為 NULL"除非您特別希望儲存空字串,否則開啟。
分隔符號可以自動偵測或手動設定。自動偵測將逗號、分號、製表符和管道的分數,方法是將前幾行一致地分成相同數量的列,從而正確處理歐洲分號檔案和製表符分隔的匯出。按一下"轉換為 SQL",輸出將顯示行數和列數,以及有關重複列名稱或參差不齊行的任何警告。複製並運行它。如果匯出到達時沒有標題行,請關閉 "第一行是 header"工具為列命名 column_1, column_2,依此類推,然後將每一行視為資料。
為什麼正確的價值逃避如此重要?
因為不正確的轉義不僅僅是一個錯誤,它是一類安全漏洞。破壞我第一次手動導入的撇號與 SQL 注入背後的機制相同:值內的單一引號如果不轉義,則會提前結束字串,並讓後面的內容被解釋為 SQL。在應用程式中,您使用參數化查詢來解決此問題,其中資料庫驅動程式將值和程式碼嚴格分開。當您從 CSV 產生靜態 SQL 腳本時,您沒有這種分離,因此轉義必須在產生的文字本身中是正確的。
該工具遵循 ANSI SQL 規則:文字值被包裹在單引號中,並且該值內的任何單引號都會加倍。所以 O'Brien's Tools 成為 'O''Brien''s Tools',每個支援的資料庫都會將其讀回原始字串。數字和布林值在沒有引號的情況下發出,因此它們被儲存為正確的類型,並且標識符用方言和#39;自己的字符,所以稱為列 order 或者 select 不與保留字衝突。這正是人類在時間壓力下犯錯的乏味正確性,而工具每次都會正確,這就是自動化的全部意義。如果您好奇轉義如何跨格式進行比較,那麼 CSV 到 JSON 轉換器 JSON 方面也面臨同樣的問題,轉義字元是反斜線而不是雙引號。
在線上轉換敏感的 CSV 資料安全嗎?
對於這個工具,是的,原因是架構性的,而不是頁面上的承諾。解析 CSV、推斷列類型、轉義值和建立語句的每一步都在您自己的瀏覽器標籤中以 JavaScript 形式運行。沒有上傳,沒有伺服器往返,也沒有記錄或儲存任何內容。您可以透過開啟瀏覽器'來證明這一點;網路標籤並點擊「轉換:沒有請求離開頁面」。頁面載入後,您可以完全斷開與互聯網的連接,並且它會不斷產生 SQL。
這很重要,因為轉換為 SQL 的 CSV 通常是一個企業擁有的最敏感的檔案。客戶表、訂單歷史記錄、使用者記錄和定價資料在登陸資料庫之前都以 CSV 形式傳輸。將檔案上傳到伺服器的轉換器,無論多麼善意,都會將私人電子表格變成其他人'日誌條目。客戶端處理完全消除了這個問題:資料永遠不會離開它啟動的機器。 toolz。dev 上的每個工具都會故意執行此預設值,我在其中寫下了較長的參數 線上工具的資料隱私指南 對於任何想要完整推理的人。
CSV 到 SQL 在實際工作流程中的適合程度如何?
轉換很少是整個工作,它是一個管道中的一個站。我最常遇到的模式是播種:客戶端發送電子表格,我將其轉換為 SQL 腳本,然後運行該腳本來填充暫存或開發資料庫,以便應用程式有真實的數據可供處理。由於輸出是普通腳本而不是即時連接,因此很容易將回購作為種子文件檢查,在拉取請求中查看,然後在任何環境中重新運行。
圍繞它的工具取決於數據接下來要做什麼。如果我在轉換之前需要將文件視為可排序的網格,那麼 CSV 檢視器 將相同的 RFC 4180 解析為表,以便我可以在格式錯誤的行變成格式錯誤的行之前發現它 INSERT. 如果目的地是 API 或設定檔而不是資料庫,則 CSV 到 JSON 轉換器 相反,它是正確的跳躍,也是它的兄弟姊妹 json 到 csv 當需要將資料作為電子表格交回時,請處理相反的情況。一旦 SQL 存在, SQL 格式化程式 將其整理成可讀的遷移內容。這些都不會上傳您的數據,因此您可以不假思索地將它們連結到同一個私人文件上。如果您正在為此類數據工作組裝一個套件,我的 web 開發人員工具包指南 瀏覽各個部分如何連結。
值得了解的限制是什麼?
對限制的誠實是信任工具的一部分。轉換器產生標準 CREATE TABLE 和 INSERT 語句,這意味著它不會推斷主鍵、外鍵、索引或列約束,因為這些資訊都不存在於平面 CSV 中。它為每個列選擇一個合理的類型,但是 VARCHAR(255) 對於 MySQL 文本,它是預設文本,而不是測量文本;如果您有一列長描述,您可能需要將其加寬到 TEXT 載入後,如果您有一個應該的列 DATE 或者 DATETIME 您會想要更改它,因為該工具將日期視為文本以避免猜測格式錯誤。
多排 INSERT 緊湊且快速,但非常大的文件會產生一個非常長的語句,並且某些資料庫限制了一個語句的大小或佔位符的數量。如果您載入數萬行並達到限制,請切換到一行 INSERT 每行,用更大的腳本換取資料庫總是接受的語句。最後,該工具用於產生載入腳本,而不是用於串流多 GB 文件,因為整個輸入都存在於瀏覽器記憶體中。對於日常匯出、種子文件和固定裝置(這是大多數人實際擁有的),這些限制都不會被咬住。知道它們的存在只是很好地使用工具和對其感到驚訝之間的區別。
常見問題
如何將 CSV 檔案轉換為 SQL?
將 CSV 貼到 CSV 到 SQL 轉換器,設定表格名稱,然後選擇您的 SQL 方言。它將標題行讀取為列名稱,推斷每個列類型,並產生 a CREATE TABLE 加 INSERT 您可以複製並在資料庫中執行的語句。一切都發生在您的瀏覽器中,因此該文件永遠不會上傳。
它支援哪些 SQL 資料庫?
此轉換器輸出 MySQL、PostgreSQL、SQLite 和標準 SQL 的語句。您選擇的方言控制標識符引用以及類型和布林語法,因此腳本無需編輯即可在該資料庫中運行。 MySQL 使用反向勾選和 TINYINT(1) 布爾,而 PostgreSQL 和標準 SQL 使用雙引號和 TRUE 或者 FALSE.
它如何決定列類型?
每列都會根據資料中的每個值進行檢查。如果所有非空值都是整數,則該列變為整數類型,所有數字變為十進制類型,所有真值或假值變為布林類型,其他任何值變為文字。具有前導零的值,例如 007 保留文本,因為它通常是一個重要的標識符。您可以關閉推理以製作每個列文字。
它是創建表格還是僅創建插入內容?
預設情況下兩者都是。它發出a CREATE TABLE 帶有推斷列類型的語句,後面跟著 INSERT 陳述。你可以轉動 CREATE TABLE 當表已存在且您只需載入行時關閉。
引號和特殊字元如何處理?
文字值被包裹在單引號中,並且值內的任何單引號都會加倍,這是標準的 SQL 轉義,因此 O'Brien 成為 'O''Brien'. 數字和布林值不加引號,識別碼用 MySQL 的反引號或其他方言的雙引號引號,因此保留詞如 order 不違反聲明。
空細胞會發生什麼事?
預設情況下,空單元格變為 NULL,這通常是您想要的缺失值。如果您喜歡儲存空字串,請關閉「空即空」選項,空儲存格將成為兩個單引號。
我可以轉換沒有標頭行的 CSV 嗎?
是的。關閉標題選項,工具命名列 column_1, column_2,依此類推,然後將每一行視為資料。這對於沒有標題行的原始出口很有用。
我的 CSV 檔案上傳到任何地方嗎? 解析和 SQL 生成不會在瀏覽器中以 JavaScript 形式運行。任何內容都不會傳輸、記錄或存儲,並且該工具在加載後會保持離線工作,您可以在轉換時透過查看網路標籤來確認這一點。
