MySQL データベースデータのロード複数の用途

MySQL Load Dataの多様な用途

1. LOAD の基本的な背景

データベースの運用と保守のプロセスでは、テキストデータを処理してデータベースにインポートする必要が必然的に生じます。この記事では、例としてインポートとエクスポートの一般的なシナリオをいくつか紹介します。

2. 基本パラメータをロードする

この記事の以下の例では、サンプルデータを csv 形式でエクスポートするために次のコマンドを使用します (区切り文字としてコンマ、区切り文字として二重引用符を使用)。

-- 基本パラメータをエクスポートします。* を選択して、出力ファイル '/data/mysql/3306/tmp/employees.txt' にエクスポートします。
文字セット utf8mb4
フィールドは ',' で終了します
'"' で囲まれている
'\n' で終了する行
従業員から。従業員の上限は 10 です。

-- 基本パラメータをインポートし、ファイル '/data/mysql/3306/tmp/employees.txt' にデータをロードします
テーブルdemo.empに置き換えます
文字セット utf8mb4
フィールドは ',' で終了します
'"' で囲まれている
'\n' で終了する行
...

3. サンプルデータとサンプルテーブル構造をロードする

以下はサンプルデータ、テーブル構造、および対応する関係情報です。

--エクスポートされたファイルデータの内容 [root@10-186-61-162 tmp]# cat employees.txt
「10001」、「1953-09-02」、「ゲオルギ」、「ファチェッロ」、「M」、「1986-06-26」
「10002」、「1964-06-02」、「ベザレル」、「ジンメル」、「F」、「1985-11-21」
"10003","1959-12-03","Parto","Bamford","M","1986-08-28"
「10004」、「1954-05-01」、「クリスチャン」、「コブリック」、「M」、「1986-12-01」
「10005」、「1955-01-21」、「京一」、「マリニアック」、「M」、「1989-09-12」
"10006","1953-04-20","アンネケ","プロイジグ","F","1989-06-02"
"10007","1957-05-23","ツヴェタン","ジエリンスキー","F","1989-02-10"
「10008」、「1958-02-19」、「サニヤ」、「カルーフィ」、「M」、「1994-09-15」
「10009」、「1952-04-19」、「スマント」、「ピース」、「F」、「1985-02-18」
「10010」、「1963-06-01」、「ドゥアンケオ」、「ピヴェトー」、「F」、「1989-08-24」

-- サンプル テーブル構造 SQL > desc demo.emp;
+-------------+---------------+------+-----+--------+-------+
| フィールド | タイプ | Null | キー | デフォルト | 追加 |
+-------------+---------------+------+-----+--------+-------+
| emp_no | int | NO | PRI | NULL | |
| 生年月日 | 日付 | NO | | NULL | |
| first_name | varchar(16) | NO | | NULL | |
| last_name | varchar(16) | NO | | NULL | |
| fullname | varchar(32) | YES | | NULL | | -- エクスポートされたデータ ファイルに存在しない、テーブル内の新しいフィールド | gender | enum('M','F') | NO | | NULL | |
| hire_date | 日付 | NO | | NULL | |
| modify_date | datetime | YES | | NULL | | -- エクスポートされたデータ ファイルに存在しない新しいフィールドがテーブルに追加されます| delete_flag | char(1) | YES | | NULL | | -- エクスポートされたデータ ファイルに存在しない新しいフィールドがテーブルに追加されます+-------------+---------------+------+------+--------+--------+

-- エクスポートされたデータとフィールド emp_no birth_date first_name last_name gender hire_date 間の対応関係
「10001」「1953-09-02」「ゲオルギ」「ファチェッロ」「M」「1986-06-26」
「10002」「1964-06-02」「ベザレル」「ジンメル」「F」「1985-11-21」
「10003」「1959-12-03」「パルト」「バンフォード」「M」「1986-08-28」
「10004」「1954-05-01」「クリスチャン」「コブリック」「M」「1986-12-01」
「10005」「1955-01-21」「京一」「マリニアック」「M」「1989-09-12」
「10006」「1953-04-20」「アンネケ」「プロイジグ」「F」「1989-06-02」
「10007」「1957-05-23」「ツヴェタン」「ジエリンスキー」「F」「1989-02-10」
「10008」「1958-02-19」「サニヤ」「カルーフィ」「M」「1994-09-15」
「10009」「1952-04-19」「スマント」「ピース」「F」「1985-02-18」
「10010」「1963-06-01」「ドゥアンケーオ」「ピヴェトー」「F」「1989-08-24」

4. LOADシナリオの例

シナリオ 1. LOAD ファイルのフィールド数がデータテーブルの数より多い

テキストファイル内のデータの一部のみをデータテーブルにインポートする必要があります

-- 2 つのフィールドを持つテーブル構造を一時的に作成します。SQL > create table emp_tmp select emp_no,hire_date from emp;
SQL > desc emp_tmp;
+-----------+------+------+------+-------+-------+
| フィールド | タイプ | Null | キー | デフォルト | 追加 |
+-----------+------+------+------+-------+-------+
| emp_no | int | NO | | NULL | |
| hire_date | 日付 | NO | | NULL | |
+-----------+------+------+------+-------+-------+

-- データのインポート ステートメント load data infile '/data/mysql/3306/tmp/employees.txt'
テーブルdemo.emp_tmpに置き換えます
文字セット utf8mb4
フィールドは ',' で終了します
'"' で囲まれている
'\n' で終了する行
(@C1、@C2、@C3、@C4、@C5、@C6) -- この部分は、employees.txt ファイル内の 6 列のデータに対応します -- エクスポートされたデータで指定された 2 列のみがテーブル内のフィールドと一致します。マッピング関係で指定された順序は、インポート結果に影響しません。set hire_date=@C6、
    従業員番号=@C1; 

-- データのインポート結果例 SQL > select * from emp_tmp;
+--------+-------------+
|従業員番号 |雇用日 |
+--------+-------------+
| 10001 | 1986-06-26 |
| 10002 | 1985-11-21 |
| 10003 | 1986-08-28 |
| 10004 | 1986-12-01 |
| 10005 | 1989-09-12 |
| 10006 | 1989-06-02 |
| 10007 | 1989-02-10 |
| 10008 | 1994-09-15 |
| 10009 | 1985-02-18 |
| 10010 | 1989-08-24 |
+--------+-------------+
セット内の行数は 10 行 (0.0016 秒)

シナリオ 2. LOAD ファイルのフィールド数がデータテーブルより少ない

テーブルフィールドには、テキストファイル内のすべてのデータだけでなく、追加のフィールドも含まれます。

-- データのインポート ステートメント load data infile '/data/mysql/3306/tmp/employees.txt'
テーブルdemo.empに置き換えます
文字セット utf8mb4
フィールドは ',' で終了します
'"' で囲まれている
'\n' で終了する行
(@C1、@C2、@C3、@C4、@C5、@C6) -- この部分は、employees.txt ファイル内の 6 列のデータに対応します -- ファイル内のフィールドをテーブル内のフィールドにマップし、テーブル内の余分なフィールドは処理しません。set emp_no=@C1、
   生年月日=@C2,
   ファーストネーム=@C3,
   姓=@C4,
   性別=@C5,
   雇用日=@C6;

シナリオ3. LOADはカスタムフィールドデータを生成する

シナリオ 2 の検証から、emp テーブルに新しく追加されたフィールドfullname,modify_date,delete_flagインポート時に処理されず、NULL 値に設定されていることがわかります。処理する必要がある場合は、 MySQL支持的函數を使用して自分でデータを定義したり、LOAD 中に固定值ことができます。関数を使用してファイル内のフィールドを処理することもできます。インポートとエクスポートを組み合わせることで、以下に示すように、簡単な ETL 関数を実装できます。

-- データのインポート ステートメント load data infile '/data/mysql/3306/tmp/employees.txt'
テーブルdemo.empに置き換えます
文字セット utf8mb4
フィールドは ',' で終了します
'"' で囲まれている
'\n' で終了する行
(@C1、@C2、@C3、@C4、@C5、@C6) -- この部分は、employees.txt ファイル内の 6 列のデータに対応します -- 次の部分は、テーブル内のフィールドをデータ ファイルのフィールドに明確にマッピングします。存在しないデータは関数処理によって生成されます (固定値に設定することもできます)
emp_no=@C1 に設定し、
   生年月日=@C2,
   first_name=upper(@C3), -- インポートしたデータを大文字に変換します last_name=lower(@C4), -- インポートしたデータを小文字に変換します fullname=concat(first_name,' ',last_name), -- first_name と last_name を連結します gender=@C5,
   雇用日=@C6、
   modify_date=now(), -- 現在の時刻データを生成します delete_flag=if(hire_date<'1988-01-01','Y','N'); -- 特定の列に基づいて生成される値に対して条件付き操作を実行します

シナリオ4. 固定長データのロード

固定長データの特徴は以下のとおりです。関数を使用することで、文字列内の固定長を抽出し、指定した列データを生成することができます。

SQL > 選択 
    c1をsample_dataとして、
    substr(c1,1,3) を c1 として、
    substr(c1,4,3) を c2 として、
    substr(c1,7,2) を c3 として、
    substr(c1,9,5) を c4 として、
    substr(c1,14,3) を c5 として、
    t1からc6としてsubstr(c1,17,3)
    
************************** 1. 行 ****************************
sample_data: ABC Yu Zhenxing CD MySQL EF G データベース c1: ABC
         c2: ユ・ジェンシン c3: CD
         c4: MySQL
         c5: EFG
         c6: データベース

固定長データのインポートでは、データの各列が占める文字数を明確にする必要があります。次の例では、rpad を使用して既存のテーブルデータ内のスペースを埋め、固定長データを生成します。

-- 固定長データを生成するSQL > select 
    連結(rpad(emp_no,10,' '),
          rpad(生年月日,19,' '),
          rpad(first_name,14,' '),
          rpad(姓,16,' '),
          rpad(性別,2,' '),
          rpad(hire_date,19,' ')) を固定長データとして 
      従業員から。従業員の上限は 10 人です。

+--------------------------------------------------------------------------------------------------+
| 固定長データ |
+--------------------------------------------------------------------------------------------------+
| 10001 1953-09-02 ゲオルギ・ファセロ M 1986-06-26 |
| 10002 1964-06-02 ベザレル・シメル F 1985-11-21 |
| 10003 1959-12-03 パート バンフォード M 1986-08-28 |
| 10004 1954-05-01 クリスチャン・コブリック M 1986-12-01 |
| 10005 1955-01-21 キョウイチ マリニアック M 1989-09-12 |
| 10006 1953-04-20 アネケ・プロイジグ F 1989-06-02 |
| 10007 1957-05-23 ツヴェタン・ジエリンスキ F 1989-02-10 |
| 10008 1958-02-19 サニヤ・カルーフィ M 1994-09-15 |
| 10009 1952-04-19 スマント・ピース F 1985-02-18 |
| 10010 1963-06-01 ドゥアンカエウ・ピヴェトー F 1989-08-24 |
+--------------------------------------------------------------------------------------------------+

-- 固定長データをエクスポートする選択 
    連結(rpad(emp_no,10,' '),
          rpad(生年月日,19,' '),
          rpad(first_name,14,' '),
          rpad(姓,16,' '),
          rpad(性別,2,' '),
          rpad(hire_date,19,' ')) を固定長データとして 
出力ファイル '/data/mysql/3306/tmp/employees_fixed.txt' へ
文字セット utf8mb4
'\n' で終了する行
従業員から。従業員の上限は 10 人です。

--エクスポートデータの例 [root@10-186-61-162 tmp]# cat employees_fixed.txt
10001 1953-09-02 ゲオルギ・ファセロ M 1986-06-26
10002 1964-06-02 ベザレル・シメル F 1985-11-21
10003 1959-12-03 パート バンフォード M 1986-08-28
10004 1954-05-01 クリスチャン・コブリック M 1986-12-01
10005 1955-01-21 キョウイチ・マリニアック M 1989-09-12
10006 1953-04-20 アネケ・プロイジグ F 1989-06-02
10007 1957-05-23 ツヴェタン・ジエリンスキ F 1989-02-10
10008 1958-02-19 サニヤ・カルーフィ M 1994-09-15
10009 1952-04-19 スマント・ピース F 1985-02-18
10010 1963-06-01 ドゥアンカエウ・ピヴェトー F 1989-08-24

-- 固定長データをインポートします。load data infile '/data/mysql/3306/tmp/employees_fixed.txt'
テーブルdemo.empに置き換えます
文字セット utf8mb4
フィールドは ',' で終了します
'"' で囲まれている
'\n' で終了する行
(@row) -- データの行をセット全体として定義します emp_no = trim(substr(@row,1,10)), -- substr を使用して最初の 10 文字を取得し、先頭と末尾のスペースを削除します birth_date = trim(substr(@row,11,19)), -- 後続のフィールドも同様です first_name = trim(substr(@row,30,14)),
   last_name = トリム(substr(@row,44,16))、
   fullname = concat(first_name,' ',last_name), -- first_name と last_name を連結します。gender = trim(substr(@row,60,2)),
   雇用日 = トリム(substr(@row,62,19))、
   修正日 = now(),
   delete_flag = if(hire_date<'1988-01-01','Y','N'); -- 特定の列に基づいて生成される値に対して条件付き操作を実行します