MSSQL 和 MariaDB 寫入大量資料

一直以來，我很少遇到需要一次性大量寫入資料的需求，印象中只有幾次需要將備份的歷史數據大量的轉回正式環境，那次因為不是很緊急，所以就直接下 SQL 讓他慢慢地寫（老實說真他 ○ × 的久），但在某次機會下，我查到 MSSQL 本身有提供批次寫入的語法（不好意思直白的說自己 SQL 不太好 XD），可以快速寫入大量的數據，因此這篇文章我要記錄一下如何在 MSSQL 和 MariaDB 批次寫入大量的資料。

MSSQL 批次寫入

Bulk Insert 是 MSSQL 提供給大量寫入資料庫的一個好用的語法，適用於一次性寫入數十萬甚至數千萬筆的資料，其主要由底下兩個實體檔案所組成。

Format File（格式檔）：用來定義要寫入的數據結構。
資料文字檔：實際要導入的數據，例如 CSV 或 TXT 檔案。

Format File

SQL Server 支援兩種類型的格式檔案：非 XML 格式和 XML 格式。非 XML 格式是舊版 SQL Server 所支援的原始格式，詳細的說明可參考官網，這邊我就用非 XML 格式，也就是文字檔的格式來說明。

圖片來源 : 使用非 XML 格式檔案 (SQL Server)

第一行 9.0 表示 bcp 公用程式的版本號碼版本（9.0 代表 SQL Server 2005），如果你使用的是 2008、2012、2019 等更新的版本，他是可以下向相容的。
還有最後一行要記得根據資料檔案內實際的換行符號來定義，Windows 通常是 \r\n。

MSSQL 範例檔案

建立資料表

CREATE TABLE MyTable (
   PersonID varchar(10),
   UserName nvarchar(10),
   Memo nvarchar(15)
);

Format File

本範例儲存在 C:\BulkInserPOC\MsSql.fmt

9.0
3
1       SQLCHAR     0       5       ""       1     PersonID     ""
2       SQLCHAR     0       10      ""       2     Name         ""
3       SQLCHAR     0       15      "\r\n"   3     Memo         ""

資料文字檔案

本範例儲存在 C:\BulkInserPOC\MsSqlData.txt，還有舊版在 Windows 環境下僅支援 ANSI，請務必小心編碼，不要改成 UTF-8，編碼錯誤會踩到雷的【SQL Server 2016 (13.x) 之前的版本不支援字碼頁 65001 (UTF-8 編碼)】。

1345612345678  abcedfgh La
2345 1234567890張 小 藍
3345 123456789 Lawrence 沈

執行語法

在 SQL Server 的環境下實際測試，執行 100,737 筆，僅需 0.476 秒。

BULK INSERT MyTable
FROM 'C:\BulkInserPOC\MsSqlData.txt'
WITH (
    FORMATFILE = 'C:\BulkInserPOC\MsSql.fmt'
	-- , CODEPAGE = '950' -- 非必要
);

MariaDB 批次寫入

MariaDB 也支援大量寫入的語法 LOAD DATA INFILE ，但比較可惜的是它沒有類似 Format File 的檔案可以定義資料結構，而是使用指定的分隔符號（預設是 Tab）來切割各欄位，因此若資料內容有包含到指定的分隔符號，可能會導致切割錯誤，這點在使用上需要注意一下。

MariaDB 範例檔案

建立資料表

CREATE TABLE MyTable (
   PersonID VARCHAR(10),
   UserName VARCHAR(10),
   Memo VARCHAR(15)
) CHARACTER SET utf8mb4;

資料文字檔案

本範例儲存在 C:\BulkInserPOC\MySqlData.txt，我的案例因為某種因素無法改變檔案編碼，該文字檔編碼為 big-5，所以在執行批次寫入的時候記得指定編碼。

13456~~12345678  ~~abcedfgh La
22345~~1233545~~張 小 藍
3345~~123456789~~Lawrence 沈

執行語法

在 MariaDB 的環境下實際測試，執行 100,737 筆，僅需 0.9 秒。

LOAD DATA LOCAL INFILE 'C:/BulkInserPOC/MySqlData.txt'
INTO TABLE MyTable
CHARACTER SET big5 -- 根據需求調整
FIELDS TERMINATED BY '~~' -- 使用 ~~ 來分隔欄位
LINES TERMINATED BY '\r\n'

可能遇到的問題

在 MariaDB 中 LOAD DATA LOCAL INFILE 預設是被停用，因此執行上述語法可能會收到某些錯誤，若是此問題的話，必須在連線字串中設定 AllowLoadLocalInfile=true。

另外如果你剛好跟我一樣使用 MySqlConnector 這個套件來連接 MariaDB 的話，或許也可以直接使用該套件提供的 MySqlBulkLoader 類別來達成批次寫入目的，雖然我沒有特別去查看該套件的原始碼，但我猜背後的原理應該也是協助我們完成上述的 SQL 語法吧!!

// MySqlConnector 套件官方 Sample Code 
using var connection = new MySqlConnection("...;AllowLoadLocalInfile=True");
await connection.OpenAsync();
var bulkLoader = new MySqlBulkLoader(connection)
{
	FileName = @"C:\Path\To\file.csv",
	TableName = "destination",
	CharacterSet = "UTF8",
	NumberOfLinesToSkip = 1,
	FieldTerminator = ",",
	FieldQuotationCharacter = '"',
	FieldQuotationOptional = true,
	Local = true,
}
var rowCount = await bulkLoader.LoadAsync();

搜尋此網誌

Lawrence Blog