MariaDB講座 / CSVを正しいパス・型・文字コードで入出力する

MariaDB講座

CSVは区切り、引用符、改行と文字コードを合わせ、まず元文字列を検査してから保存型へ変換します。LOAD DATA LOCAL INFILEはクライアント側、SELECT INTO OUTFILEはサーバー側のファイルを扱うため、パスと権限が違います。

どのコンピューターが読むかを決める

LOCAL付きLOADはクライアントファイルで、両側がLOCALを許可する必要があります。LOCALなしLOADとOUTFILEはサーバー側で、FILE権限、secure_file_privとOS権限が関係します。信頼するサーバーにだけLOCALを使い、読めるローカルファイルの範囲を考えます。

エラーを消すために全体設定や全ディレクトリ権限を広げません。クライアントのパス、列と値の対応も確認します。

UTF-8ファイルと生文字列の表を準備する

クライアントのC:/mariadb-labに未使用のproducts.csvを作り、UTF-8で保存します。最初の名前は大きいノートを意味し、カンマを含むので引用符が必要です。他はマグカップとペンです。

product_id,product_name,price
1,"노트, 대형",3500.00
2,"머그컵",8000.00
3,"볼펜",1500.00

shopcourseと二表名を確認してから実行します。数値へ即変換せずTEXTで原文を受け取ります。

USE shopcourse;
CREATE TABLE csv45_stage(raw_id TEXT,raw_name TEXT,raw_price TEXT)
 ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE csv45_products(product_id INT PRIMARY KEY,
 product_name VARCHAR(100) NOT NULL,price DECIMAL(10,2) NOT NULL CHECK(price>=0))
 ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

LOCALで読み込み、形式を先に検査する

端末でLOCALを許可した実習接続を開きます。

mariadb --protocol=tcp --host=127.0.0.1 --port=3306 --user=course_user --password --local-infile=1 --default-character-set=utf8mb4 shopcourse

接続内で、読込直後の警告と元行を読みます。

LOAD DATA LOCAL INFILE 'C:/mariadb-lab/products.csv'
INTO TABLE csv45_stage CHARACTER SET utf8mb4
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n' IGNORE 1 LINES
(@id,@name,@price)
SET raw_id=@id,raw_name=@name,raw_price=TRIM(TRAILING '\r' FROM @price);
SHOW WARNINGS;
SELECT * FROM csv45_stage;
SELECT * FROM csv45_stage
WHERE raw_id IS NULL OR raw_id NOT REGEXP '^[0-9]+$'
 OR raw_price IS NULL OR raw_price NOT REGEXP '^[0-9]+([.][0-9]{1,2})?$'
 OR raw_name IS NULL OR raw_name='';
SELECT CAST(raw_id AS UNSIGNED) AS normalized_id,COUNT(*)
FROM csv45_stage GROUP BY CAST(raw_id AS UNSIGNED) HAVING COUNT(*)>1;
-- After format checks pass, verify numeric ranges and name length
SELECT * FROM csv45_stage
WHERE CAST(raw_id AS DECIMAL(65,0))<1
 OR CAST(raw_id AS DECIMAL(65,0))>2147483647
 OR CAST(raw_price AS DECIMAL(65,2))>99999999.99
 OR CHAR_LENGTH(raw_name)>100;

形式不正の結果が空であることを確認してから、正規化IDの重複と数値範囲を信頼します。strictモードだけでLOCALがすべての不正値を拒否するとは限りません。例はLFで分割し最終項目の末尾CRを除くため、単純なCRLFにも対応します。埋め込み改行や複雑なエスケープは生成ツールと規則を合わせる必要があります。

Ubuntuのパスは代替として使う

Ubuntuクライアントなら端末で作業先を確認します。

mkdir -p "$HOME/mariadb-lab"
printf '%s\n' "$HOME/mariadb-lab/products.csv"

その実パスへファイルを保存し、次のlearnerを実際の名前へ変えます。

LOAD DATA LOCAL INFILE '/home/learner/mariadb-lab/products.csv'
INTO TABLE csv45_stage CHARACTER SET utf8mb4
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n' IGNORE 1 LINES
(@id,@name,@price)
SET raw_id=@id,raw_name=@name,raw_price=TRIM(TRAILING '\r' FROM @price);
SHOW WARNINGS;

Windows版と両方を同じステージングへ実行すると行が追加され、重複します。どちらか一方を選びます。サーバー側OUTFILEのパスとは別です。

検証済みの値だけを型付き列へ移す

IDは正のSIGNED INT、価格はDECIMAL(10,2)、名前は100文字以内です。01と1は変換後同じIDになるため、文字列表記だけで重複を判断しません。異常結果がすべて空の場合だけ実行します。

INSERT INTO csv45_products(product_id,product_name,price)
SELECT CAST(raw_id AS UNSIGNED),raw_name,CAST(raw_price AS DECIMAL(10,2))
FROM csv45_stage;
SELECT product_id,product_name,price FROM csv45_products ORDER BY product_id;

三商品になり、最初の名前のカンマが残るか確認します。再読込で増えた行や失敗診断を確認せず、元ファイルと証拠を消さないでください。

許可されたサーバー側へCSVを書き出す

FILEを許可された管理・出力用アカウントで、許可先とOSの書込権限、新しいファイル名を確認します。

SELECT @@secure_file_priv;

Windowsサーバーの場合の例です。リモートならC:はクライアントでなくサーバーの領域です。

SELECT product_id,product_name,price FROM csv45_products ORDER BY product_id
INTO OUTFILE 'C:/mariadb-lab/products-export.csv'
CHARACTER SET utf8mb4
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n';

三レコード、ヘッダーなしで、既存ファイルは上書きしません。通常の--batchのタブ区切りへ.csvと名付けてもCSVにはなりません。Ubuntuサーバーで許可先が/var/lib/mysql-files/の場合の代替です。

SELECT product_id,product_name,price FROM csv45_products ORDER BY product_id
INTO OUTFILE '/var/lib/mysql-files/products-export-new.csv'
CHARACTER SET utf8mb4
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n';

実際の許可先へ変えて一度だけ実行します。既存ファイルを削除したりFILE権限を広げたりして通しません。

件数だけでなく項目の往復を確かめる

カンマ、Unicode、空文字、NULL、改行も照合します。CSVの空欄が空文字か欠損かはツール間で共通とは限らないため、規則を定めます。この例は三項目すべてを埋めています。ファイルの保管要否を確認し、不要な実習表だけを削除します。通常のROLLBACKでは戻せません。

DROP TABLE shopcourse.csv45_products;
DROP TABLE shopcourse.csv45_stage;