【保存版】MySQL で CSV ファイルをテーブル化する 3 つの方法とそれぞれのメリット・デメリット

2024-06-21

CSV ファイルから直接 MySQL テーブルを作成する

メリット:

シンプル: テーブル定義とデータロードを 1 ステップで行えます。
高速: データのインポートが高速です。
柔軟: CSV ファイルの形式を柔軟に定義できます。

機能が限定: 一部の高度なテーブル機能は使用できません。
一時的な使用: 永続的なテーブルとしてではなく、一時的なデータロードに適しています。
可搬性が低い: CSV ストレージエンジンは MySQL 固有のものであり、他のデータベースシステムでは使用できません。

手順:

CSV ファイルを準備する: データをカンマ区切りの形式で保存した CSV ファイルを用意します。
MySQL に接続する: MySQL クライアントを使用して、データベースサーバーに接続します。
CREATE TABLE ステートメントを使用する: 以下の構文を使用して、CSV ファイルをテーブルとして定義します。

CREATE TABLE table_name (
  column1 datatype,
  column2 datatype,
  ...
)
ENGINE = CSV
(
  FIELDTERMINATOR = ','
  LINESTERMINATOR = '\n'
  LOCATION = '/path/to/file.csv'
);

オプション:

FIELDTERMINATOR: 列区切り文字を指定します。デフォルトはカンマです。
LOCATION: CSV ファイルのパスを指定します。

オプションでデータをロードする: 既存の CSV ファイルからデータをロードするには、LOAD DATA ステートメントを使用します。

LOAD DATA INFILE '/path/to/file.csv'
INTO TABLE table_name
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
(
  column1,
  column2,
  ...
);

例:

CREATE TABLE customers (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(255),
  email VARCHAR(255),
  city VARCHAR(255)
)
ENGINE = CSV
(
  FIELDTERMINATOR = ','
  LINESTERMINATOR = '\n'
  LOCATION = '/path/to/customers.csv'
);

この例では、customers.csv という名前の CSV ファイルから customers という名前のテーブルを作成します。CSV ファイルには、id, name, email, city という 4 つの列が含まれている必要があります。

注意事項:

CSV ストレージエンジンは、大規模なデータセットには適していない場合があります。
CSV ファイルは、MySQL サーバーと同じファイルシステムにある必要があります。
CSV ファイルは、テーブル定義と一致する形式でなければなりません。

CSV ファイル (customers.csv):

id,name,email,city
1,田中 太郎,[email protected],東京
2,佐藤 花子,[email protected],大阪
3,鈴木一郎,[email protected],名古屋

SQL コード:

CREATE TABLE customers (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(255),
  email VARCHAR(255),
  city VARCHAR(255)
)
ENGINE = CSV
(
  FIELDTERMINATOR = ','
  LINESTERMINATOR = '\n'
  LOCATION = '/path/to/customers.csv'
);

LOAD DATA INFILE '/path/to/customers.csv'
INTO TABLE customers
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
(
  id,
  name,
  email,
  city
);

説明:

CREATE TABLE ステートメント:
- このステートメントは、customers という名前のテーブルを作成します。
- id 列は、プライマリキーと自動インクリメント列として定義されています。
- name, email, city 列は、それぞれ VARCHAR(255) 型です。
- ENGINE = CSV句は、CSV ストレージエンジンを使用することを指定します。
- FIELDTERMINATOR = ',' 句は、列区切り文字をカンマに設定します。
- LOCATION = '/path/to/customers.csv' 句は、CSV ファイルのパスを指定します。
LOAD DATA ステートメント:
- このステートメントは、customers.csv ファイルから customers テーブルにデータを読み込みます。
- (id, name, email, city) 句は、CSV ファイルの列とテーブルの列をマッピングします。

実行方法:

上記の SQL コードをテキストエディタに保存します。
customers.csv ファイルを MySQL サーバーと同じファイルシステムに配置します。
テキストエディタに保存した SQL コードを実行します。

このコードは、MySQL 8.0 以降で使用できます。

CSV ファイルから MySQL テーブルを作成するその他の方法

LOAD DATA INFILE を使用する:

これは、CSV ファイルを直接 MySQL テーブルにインポートする古典的な方法です。LOAD DATA INFILE ステートメントを使用して、CSV ファイルの場所、テーブル名、および列のマッピングを指定します。

LOAD DATA INFILE '/path/to/file.csv'
INTO TABLE table_name
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
(
  column1,
  column2,
  ...
);

利点:

シンプルで使いやすい
大規模なデータセットにも適している

CSV ストレージエンジンほど高速ではない
テーブル定義を事前に作成する必要がある

MySQL Workbench は、GUI を備えた MySQL 管理ツールです。このツールを使用して、CSV ファイルから簡単にテーブルを作成できます。

「テーブル」>「テーブルを作成」に移動します。
「ファイルからテーブルを作成」オプションを選択します。
CSV ファイルを選択し、テーブル名と列のマッピングを指定します。
「適用」をクリックしてテーブルを作成します。

GUI を使用して簡単にテーブルを作成できる
CSV ファイルの形式をプレビューできる

コマンドラインツールほど柔軟ではない

Python スクリプトを使用する:

Python などのプログラミング言語を使用して、CSV ファイルから MySQL テーブルを作成するスクリプトを作成できます。これにより、処理を自動化したり、複雑な操作を実行したりすることができます。

import mysql.connector

# データベースへの接続
db = mysql.connector.connect(
  host="localhost",
  user="username",
  password="password",
  database="database_name"
)

# カーソルの作成
cursor = db.cursor()

# CSV ファイルの読み込み
with open('/path/to/file.csv', 'r') as csvfile:
  reader = csv.reader(csvfile)
  next(reader)  # ヘッダー行をスキップ

  # テーブルの作成
  cursor.execute("CREATE TABLE customers (id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(255), email VARCHAR(255), city VARCHAR(255))")

  # データの挿入
  for row in reader:
    cursor.execute("INSERT INTO customers (name, email, city) VALUES (%s, %s, %s)", row)

# コミットとクローズ
db.commit()
cursor.close()
db.close()