MySQL のインポートとエクスポート操作

はじめに

この実験では、MySQL のインポートとエクスポート操作の実行方法を学びます。具体的には、LOAD DATA INFILE ステートメントを使用して CSV ファイルから MySQL テーブルにデータをインポートする方法を探ります。これには、社員データを含む employees.csv という名前の CSV ファイルを作成し、MySQL サーバーに接続し、データベースとテーブルを作成し、そして LOAD DATA INFILE コマンドを使用して CSV ファイルからデータをインポートする作業が含まれます。

この実験では、employees.csv ファイルの作成、root ユーザーとしての MySQL サーバーへの接続、company データベースと employees テーブルの作成、そして適切なパラメータを指定して LOAD DATA INFILE ステートメントを実行し、CSV ファイルからテーブルにデータをインポートするプロセスを案内します。また、この実験では LOAD DATA INFILE ステートメントの構成要素についても簡単に説明します。

Skills Graph

%%%%{init: {'theme':'neutral'}}%%%% flowchart RL mysql(("MySQL")) -.-> mysql/BasicKeywordsandStatementsGroup(["Basic Keywords and Statements"]) mysql/BasicKeywordsandStatementsGroup -.-> mysql/use_database("Database Selection") mysql/BasicKeywordsandStatementsGroup -.-> mysql/create_database("Database Creation") mysql/BasicKeywordsandStatementsGroup -.-> mysql/create_table("Table Creation") mysql/BasicKeywordsandStatementsGroup -.-> mysql/select("Data Retrieval") mysql/BasicKeywordsandStatementsGroup -.-> mysql/insert("Data Insertion") mysql/BasicKeywordsandStatementsGroup -.-> mysql/delete("Data Deletion") subgraph Lab Skills mysql/use_database -.-> lab-550909{{"MySQL のインポートとエクスポート操作"}} mysql/create_database -.-> lab-550909{{"MySQL のインポートとエクスポート操作"}} mysql/create_table -.-> lab-550909{{"MySQL のインポートとエクスポート操作"}} mysql/select -.-> lab-550909{{"MySQL のインポートとエクスポート操作"}} mysql/insert -.-> lab-550909{{"MySQL のインポートとエクスポート操作"}} mysql/delete -.-> lab-550909{{"MySQL のインポートとエクスポート操作"}} end

LOAD DATA INFILE を使用した CSV データのインポート

このステップでは、LOAD DATA INFILE ステートメントを使用して CSV ファイルから MySQL テーブルにデータをインポートする方法を学びます。これは、大量のデータをデータベースにロードする非常に効率的な方法です。

始める前に、社員データを含む employees.csv という簡単な CSV ファイルを作成しましょう。ターミナルを開き、nano を使用してファイルを作成します。

nano ~/project/employees.csv

次に、以下のデータを employees.csv ファイルに貼り付けます。

1,John,Doe,[email protected],Sales
2,Jane,Smith,[email protected],Marketing
3,Peter,Jones,[email protected],Engineering
4,Mary,Brown,[email protected],HR

Ctrl+X を押し、次に Y を押し、最後に Enter を押してファイルを保存します。

次に、MySQL サーバーに接続する必要があります。ターミナルを開き、以下のコマンドを実行して root ユーザーとして MySQL サーバーに接続します。ルートパスワードの入力を求められる場合がありますが、LabEx のデフォルトの仮想マシン設定では通常空白です。

mysql -u root -p

パスワードの入力を求められ、パスワードを設定していない場合は、Enter を押してください。

では、company という名前のデータベースと、そのデータベース内に employees という名前のテーブルを作成しましょう。MySQL シェルで以下の SQL ステートメントを実行します。

CREATE DATABASE IF NOT EXISTS company;
USE company;

CREATE TABLE IF NOT EXISTS employees (
    id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100),
    department VARCHAR(50)
);

データベースとテーブルが設定されたので、LOAD DATA INFILE ステートメントを使用して employees.csv ファイルからデータをインポートできます。MySQL シェルで以下の SQL ステートメントを実行します。

LOAD DATA INFILE '/home/labex/project/employees.csv'
INTO TABLE employees
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;

このステートメントを分解して説明しましょう。

LOAD DATA INFILE '/home/labex/project/employees.csv': CSV ファイルのパスを指定します。重要： ファイルの絶対パスを使用してください。
INTO TABLE employees: データをインポートするテーブルを指定します。
FIELDS TERMINATED BY ',': CSV ファイルのフィールドがカンマで区切られていることを指定します。
ENCLOSED BY '"': フィールドが二重引用符で囲まれていることを指定します（該当する場合）。今回の場合は、必ずしも必要ではありませんが、含めるのが良い習慣です。
LINES TERMINATED BY '\n': CSV ファイルの各行が新しい行を表し、行が改行文字で終わることを指定します。
IGNORE 1 ROWS: CSV ファイルにヘッダー行がある場合、これは重要です。MySQL にファイルの最初の行をスキップするように指示します。私たちの employees.csv ファイルにはヘッダー行がないので、この行を削除する必要があります。

IGNORE 1 ROWS 句を含まないで、LOAD DATA INFILE ステートメントを再度試してみましょう。

LOAD DATA INFILE '/home/labex/project/employees.csv'
INTO TABLE employees
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

データが正しくインポートされたことを確認するには、以下の SQL ステートメントを実行します。

SELECT * FROM employees;

employees.csv ファイルのデータが MySQL シェルに表示されるはずです。

最後に、MySQL シェルを終了します。

exit

クエリ結果を CSV ファイルにエクスポートする

このステップでは、MySQL クエリの結果を CSV ファイルにエクスポートする方法を学びます。これは、レポートの生成、他のアプリケーションとのデータ共有、またはデータのバックアップに役立ちます。

mysql コマンドラインクライアントの -e オプションを使用してクエリを実行し、出力をファイルにリダイレクトします。また、出力を CSV ファイル形式にするためのいくつかのオプションも使用します。

まず、MySQL サーバーに接続しましょう。ターミナルを開き、以下のコマンドを実行して root ユーザーとして MySQL サーバーに接続します。

mysql -u root -p

パスワードの入力を求められ、パスワードを設定していない場合は、Enter を押してください。

エクスポートするデータを増やすために、employees テーブルにさらにいくつかのデータを追加しましょう。MySQL シェルで以下の SQL ステートメントを実行します。

USE company;
INSERT INTO employees (id, first_name, last_name, email, department) VALUES
(5, 'Alice', 'Johnson', '[email protected]', 'Sales'),
(6, 'Bob', 'Williams', '[email protected]', 'Marketing');

次に、MySQL シェルを終了します。

exit

では、employees テーブルのすべてのデータを ~/project ディレクトリ内の employees_export.csv という名前の CSV ファイルにエクスポートしましょう。ターミナルで以下のコマンドを実行します。

mysql -u root -p -e "USE company; SELECT * FROM employees;" \
  --batch --raw --skip-column-names \
  | sed 's/\t/","/g; s/^/"/; s/$/"/;' > ~/project/employees_export.csv

このコマンドを分解して説明しましょう。

mysql -u root -p -e "USE company; SELECT * FROM employees;": この部分は、company データベースに対して SELECT * FROM employees という SQL クエリを実行します。MySQL のルートパスワードの入力を求められます。
--batch: このオプションは、mysql をバッチモードで実行するように指示します。これは非対話的な使用に適しています。
--raw: このオプションは、mysql にデータを整形せずに出力するように指示します。
--skip-column-names: このオプションは、mysql に出力から列名を省略するように指示します。
sed 's/\t/","/g; s/^/"/; s/$/"/;': この部分は、sed コマンドを使用して出力を CSV ファイル形式に整形します。
- s/\t/","/g: タブ（mysql 出力のデフォルトのフィールド区切り文字）を "," に置き換えます。
- s/^/"/: 各行の先頭に二重引用符を追加します。
- s/$/"/: 各行の末尾に二重引用符を追加します。
> ~/project/employees_export.csv: これは、コマンドの出力を ~/project ディレクトリ内の employees_export.csv という名前のファイルにリダイレクトします。

では、employees_export.csv ファイルの内容を確認しましょう。nano を使用してファイルを開きます。

nano ~/project/employees_export.csv

employees テーブルのデータが CSV ファイル形式で表示され、各フィールドが二重引用符で囲まれ、カンマで区切られているはずです。

Ctrl+X を押して nano を終了します。

インポート時のエンコーディング問題を解決する

このステップでは、CSV データを MySQL にインポートする際のエンコーディング問題の対処方法を学びます。CSV ファイルの文字エンコーディングが MySQL テーブルの文字エンコーディングと一致しない場合、エンコーディング問題が発生することがあります。これにより、文字化けや誤ったデータがインポートされることがあります。

まず、エンコーディング問題を引き起こす可能性のある特殊文字を含む CSV ファイルを作成しましょう。アクセント付きの文字を含む名前を使用します。ターミナルを開き、nano を使用してファイルを作成します。

nano ~/project/employees_encoding.csv

次に、以下のデータを employees_encoding.csv ファイルに貼り付けます。

1,René,Doe,[email protected],Sales
2,Jane,Smith,[email protected],Marketing

Ctrl+X を押し、次に Y を押し、最後に Enter を押してファイルを保存します。

では、エンコーディングを指定せずにこのデータを employees テーブルにインポートしてみましょう。まず、テーブル内の既存のデータをクリアする必要があります。MySQL サーバーに接続します。

mysql -u root -p

パスワードの入力を求められ、パスワードを設定していない場合は、Enter を押してください。

MySQL シェルで以下の SQL ステートメントを実行します。

USE company;
TRUNCATE TABLE employees;

これにより、employees テーブル内のすべての既存の行が削除されます。

次に、MySQL シェルを終了します。

exit

では、前回と同じ LOAD DATA INFILE コマンドを使用して employees_encoding.csv ファイルをインポートしてみましょう。

mysql -u root -p -e "USE company; LOAD DATA INFILE '/home/labex/project/employees_encoding.csv' INTO TABLE employees FIELDS TERMINATED BY ',' ENCLOSED BY '\"' LINES TERMINATED BY '\n';"

パスワードを入力した後、再度 MySQL サーバーに接続します。

mysql -u root -p

そして、データを確認します。

USE company;
SELECT * FROM employees;

最初の社員の first_name が正しく表示されないことがあります。これはエンコーディング問題によるものです。MySQL シェルを終了します。

exit

この問題を解決するには、LOAD DATA INFILE を使用する際に CSV ファイルの文字エンコーディングを指定する必要があります。一般的なエンコーディングは utf8 です。LOAD DATA INFILE ステートメントを修正して、CHARACTER SET utf8 句を含めます。

mysql -u root -p -e "USE company; LOAD DATA INFILE '/home/labex/project/employees_encoding.csv' INTO TABLE employees CHARACTER SET utf8 FIELDS TERMINATED BY ',' ENCLOSED BY '\"' LINES TERMINATED BY '\n';"

再度 MySQL サーバーに接続します。

mysql -u root -p

そして、データを確認します。

USE company;
SELECT * FROM employees;

これで、最初の社員の first_name が "René" と正しく表示されるはずです。

最後に、MySQL シェルを終了します。

exit

この例では utf8 を使用しましたが、CSV ファイルのエンコーディングに応じて、異なる文字セットを使用する必要がある場合があります。一般的なエンコーディングには、latin1、utf8mb4、gbk などがあります。

インポートしたデータを検証する

このステップでは、MySQL テーブルにインポートされたデータを検証する方法を学びます。データ検証は、データの正確性と整合性を保証するための重要なステップです。SQL クエリを使用した基本的な検証手法について説明します。

まず、employees テーブルに潜在的な問題のあるデータを追加しましょう。ターミナルを開き、nano を使用してファイルを作成します。

nano ~/project/employees_validation.csv

次に、以下のデータを employees_validation.csv ファイルに貼り付けます。最後の行には無効なメールアドレス形式と部署情報の欠落があることに注意してください。

3,Invalid,Email,invalid_email,Sales
4,Missing,Department,[email protected],

Ctrl+X を押し、次に Y を押し、最後に Enter を押してファイルを保存します。

では、このデータを employees テーブルにインポートしましょう。MySQL サーバーに接続します。

mysql -u root -p

パスワードの入力を求められ、パスワードを設定していない場合は、Enter を押してください。

MySQL シェルで以下の SQL ステートメントを実行します。

USE company;
LOAD DATA INFILE '/home/labex/project/employees_validation.csv'
INTO TABLE employees
CHARACTER SET utf8
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

データをインポートしたので、いくつかの検証チェックを行いましょう。

1. 無効なメールアドレス形式のチェック：

正規表現を使用して、無効なメールアドレス形式をチェックすることができます。以下の SQL クエリを実行します。

SELECT * FROM employees WHERE email NOT LIKE '%@%.%';

このクエリは、email 列に有効なメールアドレスに必須の @ と . 文字が含まれていない行を返します。結果に invalid_email の行が表示されるはずです。

2. 部署情報の欠落のチェック：

department 列の空文字列の有無を確認することで、部署情報の欠落をチェックすることができます。以下の SQL クエリを実行します。

SELECT * FROM employees WHERE department = '';

このクエリは、department 列が空の行を返します。結果に部署情報が欠落している行が表示されるはずです。

3. 重複する ID のチェック：

この例では重複する ID を導入していませんが、一般的な検証チェックです。以下のクエリを使用して重複する ID を見つけることができます。

SELECT id, COUNT(*) FROM employees GROUP BY id HAVING COUNT(*) > 1;

このクエリは、employees テーブルに複数回出現する ID を返します。

4. データ型の不一致のチェック：

MySQL は通常、インポートプロセス中にデータ型の不一致を処理しますが、チェックすることも良い考えです。たとえば、正の値のみを含むはずの数値列がある場合、負の値をチェックすることができます。

これらは、SQL クエリを使用してインポートされたデータを検証する方法のいくつかの例です。実行する必要のある具体的な検証チェックは、データの構造と内容によって異なります。

最後に、MySQL シェルを終了します。

exit

まとめ

この実験では、LOAD DATA INFILE 文を使用して CSV ファイルから MySQL テーブルにデータをインポートする方法を学びました。これには、社員データを含む CSV ファイル (employees.csv) を作成し、MySQL サーバーに接続し、データベース (company) とテーブル (employees) を作成し、その後 LOAD DATA INFILE コマンドを使用してデータをインポートすることが含まれます。このコマンドでは、ファイルパス、テーブル名、フィールドと行の区切り文字、および無視する行数を指定します。

この実験ではまた、正しいファイルパスを指定し、LOAD DATA INFILE 文内のさまざまな句を理解することが、データのインポートを成功させるために重要であることを強調しました。