MySQL - INSERT

提供: MochiuWiki : SUSE, EC, PCB

2026年2月8日 (日) 05:58時点におけるWiki (トーク | 投稿記録)による版 (→LOAD DATA INFILE)

概要

INSERT文は、MySQLのテーブルに新しい行を挿入するためのSQL文である。

基本的な構文として、VALUES 形式、SET 形式、VALUES ROW() 形式が提供されており、用途に応じて使い分けることができる。
INSERT文は、単一行だけでなく複数行を一度に挿入することも可能であり、パフォーマンスの観点からは複数行挿入が推奨される。

また、INSERT ... SELECT 構文を使用することで、他のテーブルから取得したデータを直接挿入することができ、データ移行やバックアップ作成に有用である。

重複キーの処理については、INSERT ... ON DUPLICATE KEY UPDATE 構文を使用することで、UPSERT (INSERT または UPDATE) 動作を実現できる。
これにより、同一キーのレコードが存在する場合は更新、存在しない場合は挿入という処理を1つのSQL文で記述可能である。

AUTO_INCREMENT 列への挿入時は、値を指定せずに自動採番させることが一般的であり、LAST_INSERT_ID() 関数で採番されたIDを取得できる。

外部キー制約が設定されている場合は、参照整合性を維持するために、親テーブルに存在する値のみを挿入する必要がある。

大量のデータを挿入する際は、バルクインサート (複数行を1文で挿入) や LOAD DATA INFILE 文の使用により、パフォーマンスを大幅に向上させることができる。
InnoDBテーブルでは、UNIQUE制約チェックの無効化やPRIMARY KEY順序での挿入により、さらなる最適化が可能である。

トランザクション内で実行することで、挿入の一貫性を保証し、エラー発生時にはロールバックにより挿入を取り消すことができる。


基本構文

VALUES構文

最も一般的なINSERT文の構文は、VALUES 句を使用する形式である。

 INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE]
    [INTO] tbl_name
    [PARTITION (partition_name [, partition_name] ...)]
    [(col_name [, col_name] ...)]
    VALUES (value_list) [, (value_list)] ...
    [ON DUPLICATE KEY UPDATE assignment_list]


基本的な使用例を以下に示す。

 # 単一行の挿入
 INSERT INTO users (name, email, age)
    VALUES ('Taro Yamada', 'taro@example.com', 30);
 
 # 列名を省略 (全列の値を順序通りに指定)
 INSERT INTO users
    VALUES (1, 'Hanako Suzuki', 'hanako@example.com', 25);
 
 # 複数行の挿入
 INSERT INTO users (name, email, age)
    VALUES
       ('Ichiro Sato', 'ichiro@example.com', 28),
       ('Jiro Tanaka', 'jiro@example.com', 35),
       ('Saburo Kato', 'saburo@example.com', 42);


SET構文

SET 句を使用する形式は、UPDATE文と同様の構文で挿入を行う。

 INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE]
    [INTO] tbl_name
    SET assignment_list
    [ON DUPLICATE KEY UPDATE assignment_list]


使用例を以下に示す。

 # SET構文での挿入
 INSERT INTO users
    SET name = 'Shiro Ito',
        email = 'shiro@example.com',
        age = 33;


※注意
SET構文では、複数行の挿入はできない。
VALUES構文の方が柔軟性が高いため、一般的にはVALUES構文が推奨される。

VALUES ROW()構文

MySQL 8.0.19以降では、VALUES ROW() 構文が利用可能である。

 # VALUES ROW()構文
 INSERT INTO users (name, email, age)
    VALUES ROW('Goro Nakamura', 'goro@example.com', 29);


VALUES ROW()構文の特徴を以下に示す。

  • テーブル値コンストラクタとして使用可能
    JOINやUNIONと組み合わせ可能。
  • ROW()は空にできない
    各スカラー値はNULL可。
  • 出力列名は暗黙的に column_0, column_1, column_2... となる
    エイリアスで明示的に指定可能。


修飾子

INSERT文では、下表に示す修飾子を使用して挿入動作を制御できる。

INSERT文で使用可能な修飾子
修飾子 説明
LOW_PRIORITY 他のクライアントがテーブルを読み取っていない時に挿入を実行する。
MyISAM、MEMORY、MERGEストレージエンジンで有効
HIGH_PRIORITY 通常の挿入よりも優先的に実行する。
LOW_PRIORITY との同時使用は不可
DELAYED MySQL 8.0では無視される (廃止予定)。
MySQL 5.7以前では、挿入をキューイングして非同期実行
IGNORE 重複キーエラーや制約違反エラーを WARNING に変換して処理を継続する。
エラーが発生した行はスキップされる


使用例を以下に示す。

 # 低優先度で挿入を実行
 INSERT LOW_PRIORITY INTO logs (message, level)
    VALUES ('System started', 'INFO');
 
 # エラーを無視して挿入を継続
 INSERT IGNORE INTO users (id, name, email)
    VALUES
       (1, 'User A', 'usera@example.com'),
       (1, 'User B', 'userb@example.com'),  -- 重複キーエラーはスキップ
       (2, 'User C', 'userc@example.com');


PARTITION句

パーティション化されたテーブルでは、PARTITION 句を使用して特定のパーティションへの挿入を明示的に指定できる。

 # 特定のパーティションに挿入
 INSERT INTO sales
    PARTITION (p_2024_q1)
    (product_id, amount, sale_date)
    VALUES (100, 5000, '2024-01-15');
 
 # 複数パーティションを指定
 INSERT INTO logs
    PARTITION (p0, p1)
    (message, level)
    VALUES ('Log entry', 'INFO');


※注意
指定したパーティションが、挿入するデータのパーティショニング条件と一致しない場合はエラーになる。
通常は、PARTITION句を省略して、MySQLに自動的にパーティションを決定させることが推奨される。


複数行の挿入

複数行を一度に挿入することで、パフォーマンスを大幅に向上させることができる。

基本的な複数行挿入

VALUES句に複数の値リストをカンマ区切りで指定する。

 # 複数行を一度に挿入
 INSERT INTO products (name, price, stock)
    VALUES
       ('Product A', 1000, 50),
       ('Product B', 1500, 30),
       ('Product C', 2000, 20),
       ('Product D', 2500, 10);


パフォーマンスの利点

複数行挿入は、単一行挿入を繰り返すよりも大幅に高速である。

理由を以下に示す。

  • ネットワークラウンドトリップの削減
    1回のSQL文送信で複数行を挿入できる。
  • パースとプランニングのオーバーヘッド削減
    SQL文の解析と実行計画の作成が1回で済む。
  • ロックの取得と解放の回数削減
    テーブルロックの取得が1回で済む。
  • インデックス更新の最適化
    複数行のインデックス更新を一括で実行できる。


パフォーマンス比較の例を以下に示す。

 # 低速：単一行挿入を1000回繰り返す
 INSERT INTO users (name, email) VALUES ('User 1', 'user1@example.com');
 INSERT INTO users (name, email) VALUES ('User 2', 'user2@example.com');
 # ... 1000回繰り返す
 
 # 高速：1000行を1度に挿入
 INSERT INTO users (name, email)
    VALUES
       ('User 1', 'user1@example.com'),
       ('User 2', 'user2@example.com'),
       # ... 1000行
       ('User 1000', 'user1000@example.com');


一般的に、複数行挿入は単一行挿入の繰り返しに比べて、数倍から数十倍の速度向上が期待できる。

推奨バッチサイズ

一度に挿入する行数の推奨値を以下に示す。

  • 推奨範囲
    1,000～10,000行
  • 理由
    SQL文のサイズとパフォーマンスのバランスが良い。
  • 制限事項
    max_allowed_packet の設定値を超えないようにする必要がある。


max_allowed_packet の確認と変更方法を以下に示す。

 # 現在の設定値を確認 (デフォルトは4MBまたは16MB)
 SHOW VARIABLES LIKE 'max_allowed_packet';
 
 # セッション単位で変更 (例: 64MBに設定)
 SET GLOBAL max_allowed_packet = 67108864;  -- 64MB


※注意
max_allowed_packet を超える巨大なINSERT文を実行すると、エラーになる。
大量データの挿入時は、適切なバッチサイズに分割することが重要である。


INSERT ... SELECT

他のテーブルから取得したデータを直接挿入する構文である。

基本構文

 INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE]
    [INTO] tbl_name
    [PARTITION (partition_name [, partition_name] ...)]
    [(col_name [, col_name] ...)]
    SELECT ...
    [ON DUPLICATE KEY UPDATE assignment_list]


基本的な使用例

 # 他のテーブルから全データをコピー
 INSERT INTO users_backup
    SELECT * FROM users;
 
 # 特定の列のみをコピー
 INSERT INTO active_users (name, email)
    SELECT name, email FROM users WHERE status = 'active';
 
 # WHERE句で条件を指定
 INSERT INTO premium_users (name, email, plan)
    SELECT name, email, 'premium'
       FROM users
       WHERE subscription_type = 'premium';


JOINを使用した挿入

複数のテーブルを結合してデータを挿入できる。

 # JOINを使用した挿入
 INSERT INTO user_orders (user_name, order_total)
    SELECT u.name, SUM(o.amount)
       FROM users u
       INNER JOIN orders o ON u.id = o.user_id
       GROUP BY u.id, u.name
       HAVING SUM(o.amount) > 10000;
 
 # 複数テーブルのJOIN
 INSERT INTO sales_summary (product_name, category, total_sales)
    SELECT p.name, c.name, SUM(s.amount)
       FROM products p
       INNER JOIN categories c ON p.category_id = c.id
       INNER JOIN sales s ON p.id = s.product_id
       WHERE s.sale_date >= '2024-01-01'
       GROUP BY p.id, p.name, c.name;


同一テーブルからの選択

同じテーブルからSELECTして挿入する場合、MySQLは内部的に一時テーブルを作成する。

 # 同一テーブルからの挿入 (内部的に一時テーブルを使用)
 INSERT INTO users (name, email, created_at)
    SELECT CONCAT(name, ' (copy)'), CONCAT('copy_', email), NOW()
       FROM users
       WHERE status = 'active';


※注意
同一テーブルからのSELECTは、一時テーブルの作成によりパフォーマンスが低下する可能性がある。

サブクエリとの組み合わせ

 # サブクエリを使用した挿入
 INSERT INTO top_customers (name, total_spent)
    SELECT u.name, (
       SELECT SUM(amount) FROM orders WHERE user_id = u.id
    ) AS total
       FROM users u
       WHERE u.status = 'active'
       HAVING total > 50000;



INSERT ... ON DUPLICATE KEY UPDATE

重複キーが検出された場合にUPDATE文を実行する構文である。
これにより、UPSERT (INSERT または UPDATE) 動作を1つのSQL文で実現できる。

基本構文

 INSERT INTO tbl_name (col_name [, col_name] ...)
    VALUES (value_list) [, (value_list)] ...
    ON DUPLICATE KEY UPDATE
       col_name = expression [, col_name = expression] ...


基本的な使用例

 # PRIMARY KEYまたはUNIQUE KEYが重複した場合にUPDATE
 INSERT INTO user_stats (user_id, login_count, last_login)
    VALUES (100, 1, NOW())
    ON DUPLICATE KEY UPDATE
       login_count = login_count + 1,
       last_login = NOW();
 
 # 複数行の挿入とUPDATE
 INSERT INTO inventory (product_id, stock)
    VALUES
       (1, 10),
       (2, 20),
       (3, 30)
    ON DUPLICATE KEY UPDATE
       stock = stock + VALUES(stock);  -- MySQL 8.0.19以前


VALUES()関数の非推奨化

MySQL 8.0.20以降、VALUES() 関数は非推奨となった。
代わりに、新しい値のエイリアスを使用する構文が推奨される。

 # MySQL 8.0.19以前の構文 (非推奨)
 INSERT INTO products (id, name, price)
    VALUES (1, 'Product A', 1000)
    ON DUPLICATE KEY UPDATE
       price = VALUES(price);
 
 # MySQL 8.0.20以降の推奨構文 (エイリアスを使用)
 INSERT INTO products (id, name, price)
    VALUES (1, 'Product A', 1000) AS new
    ON DUPLICATE KEY UPDATE
       price = new.price;
 
 # 複数列の更新
 INSERT INTO products (id, name, price, stock)
    VALUES (1, 'Product A', 1000, 50) AS new
    ON DUPLICATE KEY UPDATE
       name = new.name,
       price = new.price,
       stock = stock + new.stock;


affected_rowsの値

ON DUPLICATE KEY UPDATE の実行結果は、以下のように解釈される。

affected_rowsの値と意味
affected_rows 意味
1 新しい行が挿入された
2 既存の行が更新された
0 既存の行が存在したが、値が変更されなかった
(更新前と更新後が同じ値)


確認方法を以下に示す。

 INSERT INTO user_stats (user_id, login_count)
    VALUES (100, 1) AS new
    ON DUPLICATE KEY UPDATE
       login_count = login_count + 1;
 
 SELECT ROW_COUNT();  -- 1=新規挿入, 2=更新, 0=変更なし


計算式を使用した更新

 # 既存の値に加算
 INSERT INTO daily_sales (sale_date, total_amount)
    VALUES ('2024-01-15', 5000) AS new
    ON DUPLICATE KEY UPDATE
       total_amount = total_amount + new.total_amount;
 
 # 条件式を使用
 INSERT INTO products (id, name, price, discount_rate)
    VALUES (1, 'Product A', 1000, 0.1) AS new
    ON DUPLICATE KEY UPDATE
       price = new.price,
       discount_rate = IF(new.price > 5000, 0.2, 0.1);
 
 # タイムスタンプの更新
 INSERT INTO users (id, name, email, created_at)
    VALUES (100, 'User A', 'usera@example.com', NOW()) AS new
    ON DUPLICATE KEY UPDATE
       name = new.name,
       email = new.email,
       updated_at = NOW();



DEFAULT値とNULL

列にDEFAULT値が設定されている場合、または NULL が許可されている場合の挿入動作について説明する。

DEFAULTキーワード

DEFAULT キーワードを使用して、列のデフォルト値を明示的に挿入できる。

 # テーブル定義
 CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    status VARCHAR(20) DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
 );
 
 # DEFAULTキーワードを使用
 INSERT INTO users (name, status, created_at)
    VALUES ('User A', DEFAULT, DEFAULT);
 
 # 列を省略 (自動的にDEFAULT値が使用される)
 INSERT INTO users (name)
    VALUES ('User B');


NULL値の挿入

NULL が許可されている列には、明示的に NULL を挿入できる。

 # テーブル定義
 CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    description TEXT NULL
 );
 
 # NULL値を明示的に挿入
 INSERT INTO products (name, description)
    VALUES ('Product A', NULL);
 
 # 列を省略 (自動的にNULLが挿入される)
 INSERT INTO products (name)
    VALUES ('Product B');


STRICTモードと非STRICTモード

MySQLのSQLモード設定により、不正な値の挿入時の動作が異なる。

STRICTモードと非STRICTモードの違い
項目 STRICTモード 非STRICTモード
NOT NULL列への NULL挿入 エラー DEFAULT値または型の暗黙的なデフォルト値が挿入される
DEFAULT値がない列の省略 エラー 型の暗黙的なデフォルト値が挿入される
範囲外の値 エラー 範囲の境界値に切り詰められる
不正な日付 エラー '0000-00-00' が挿入される


SQLモードの確認と変更方法を以下に示す。

 # 現在のSQLモードを確認
 SELECT @@sql_mode;
 
 # STRICTモードを有効化
 SET sql_mode = 'STRICT_TRANS_TABLES';
 
 # STRICTモードを無効化
 SET sql_mode = '';


※推奨
データの整合性を保つため、STRICTモード (STRICT_TRANS_TABLES) の使用が推奨される。


AUTO_INCREMENT

AUTO_INCREMENT 属性を持つ列は、値を指定しない場合に自動的に連番が採番される。

基本的な使用例

 # テーブル定義
 CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100)
 );
 
 # AUTO_INCREMENT列に値を指定しない
 INSERT INTO users (name, email)
    VALUES ('User A', 'usera@example.com');
 -- id=1 が自動採番される
 
 INSERT INTO users (name, email)
    VALUES ('User B', 'userb@example.com');
 -- id=2 が自動採番される


LAST_INSERT_ID()関数

LAST_INSERT_ID() 関数を使用して、最後に採番されたAUTO_INCREMENT値を取得できる。

 # 挿入後にIDを取得
 INSERT INTO users (name, email)
    VALUES ('User C', 'userc@example.com');
 
 SELECT LAST_INSERT_ID();
 -- 最後に挿入された行のid値を返す


※重要
LAST_INSERT_ID() はセッション単位で管理される。
他のクライアントの挿入操作の影響を受けない。

複数行挿入時のLAST_INSERT_ID()

複数行を1度に挿入した場合、LAST_INSERT_ID() は最初の行のIDを返す。

 # 複数行を挿入
 INSERT INTO users (name, email)
    VALUES
       ('User D', 'userd@example.com'),  -- id=4 (これが返される)
       ('User E', 'usere@example.com'),  -- id=5
       ('User F', 'userf@example.com');  -- id=6
 
 SELECT LAST_INSERT_ID();  -- 4 を返す


挿入された全てのIDを取得する方法を以下に示す。

 # 挿入された行数を取得
 SELECT ROW_COUNT();  -- 3 を返す
 
 # 挿入されたIDの範囲
 # 開始ID: LAST_INSERT_ID()
 # 終了ID: LAST_INSERT_ID() + ROW_COUNT() - 1


AUTO_INCREMENT値の明示的な指定

AUTO_INCREMENT列に明示的に値を指定することも可能である。

 # AUTO_INCREMENT列に明示的に値を指定
 INSERT INTO users (id, name, email)
    VALUES (100, 'User G', 'userg@example.com');
 
 # 次の自動採番は101から開始される
 INSERT INTO users (name, email)
    VALUES ('User H', 'userh@example.com');  -- id=101


※注意
既存のIDと重複する値を指定すると、重複キーエラーになる。

AUTO_INCREMENT初期値の設定

 # テーブル作成時に初期値を指定
 CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100)
 ) AUTO_INCREMENT = 1000;
 
 # 既存テーブルの初期値を変更
 ALTER TABLE products AUTO_INCREMENT = 2000;



外部キー制約と挿入

外部キー制約が設定されている場合、参照整合性を維持するために、親テーブルに存在する値のみを挿入できる。

基本的な外部キー制約

 # 親テーブル
 CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(100)
 );
 
 # 子テーブル (外部キー制約あり)
 CREATE TABLE orders (
    id INT PRIMARY KEY,
    user_id INT,
    amount DECIMAL(10,2),
    FOREIGN KEY (user_id) REFERENCES users(id)
 );
 
 # 親テーブルにデータを挿入
 INSERT INTO users (id, name) VALUES (1, 'User A');
 
 # 子テーブルに正常に挿入 (user_id=1は親テーブルに存在)
 INSERT INTO orders (id, user_id, amount)
    VALUES (100, 1, 5000);
 
 # エラー：user_id=999は親テーブルに存在しない
 INSERT INTO orders (id, user_id, amount)
    VALUES (101, 999, 3000);


ON DELETE CASCADE

親レコード削除時に、子レコードも自動的に削除される制約である。

 # 外部キー制約の定義
 CREATE TABLE orders (
    id INT PRIMARY KEY,
    user_id INT,
    amount DECIMAL(10,2),
    FOREIGN KEY (user_id) REFERENCES users(id)
       ON DELETE CASCADE
 );
 
 # データ挿入
 INSERT INTO users (id, name) VALUES (1, 'User A');
 INSERT INTO orders (id, user_id, amount) VALUES (100, 1, 5000);
 
 # 親レコードを削除すると、子レコードも自動削除される
 DELETE FROM users WHERE id = 1;
 -- orders テーブルの user_id=1 の行も自動削除される


外部キーチェックの一時的な無効化

データの一括挿入時に外部キーチェックを一時的に無効化する方法を以下に示す。

 # 外部キーチェックを無効化
 SET FOREIGN_KEY_CHECKS = 0;
 
 # 外部キー制約を無視してデータ挿入
 INSERT INTO orders (id, user_id, amount)
    VALUES (102, 999, 3000);  -- user_id=999は親テーブルに存在しないがエラーにならない
 
 # 外部キーチェックを再度有効化
 SET FOREIGN_KEY_CHECKS = 1;


※警告
外部キーチェックを無効にすると、データの整合性が失われる可能性がある。
使用後は必ず再度有効化し、データの整合性を確認することが重要である。


挿入結果の確認

INSERT文の実行結果は、挿入された行数として表示される。

 INSERT INTO users (name, email)
    VALUES ('User A', 'usera@example.com');



# 出力例：

Query OK, 1 row affected (0.01 sec)


複数行挿入時の出力例を以下に示す。

 INSERT INTO users (name, email)
    VALUES
       ('User B', 'userb@example.com'),
       ('User C', 'userc@example.com'),
       ('User D', 'userd@example.com');



# 出力例：

Query OK, 3 rows affected (0.02 sec)
Records: 3  Duplicates: 0  Warnings: 0


ROW_COUNT()関数

ROW_COUNT() 関数を使用して、直前のINSERT文で挿入された行数を取得できる。

 INSERT INTO products (name, price)
    VALUES ('Product A', 1000);
 
 SELECT ROW_COUNT();



# 出力例

# +--------------+
# | ROW_COUNT()  |
# +--------------+
# |            1 |
# +--------------+


※注意
ROW_COUNT()関数は、直後のSELECT文で実行する必要がある。
他のSQL文を実行すると、値がリセットされる。

IGNORE修飾子使用時

IGNORE 修飾子を使用した場合、エラーが発生した行はスキップされ、WARNINGとして記録される。

 INSERT IGNORE INTO users (id, name, email)
    VALUES
       (1, 'User A', 'usera@example.com'),
       (1, 'User B', 'userb@example.com'),  -- 重複キーエラー (スキップ)
       (2, 'User C', 'userc@example.com');



# 出力例：

Query OK, 2 rows affected, 1 warning (0.01 sec)
Records: 3  Duplicates: 1  Warnings: 1


WARNINGの内容を確認する方法を以下に示す。

 SHOW WARNINGS;



# 出力例

# +---------+------+---------------------------------------+
# | Level   | Code | Message                               |
# +---------+------+---------------------------------------+
# | Warning | 1062 | Duplicate entry '1' for key 'PRIMARY' |
# +---------+------+---------------------------------------+



パフォーマンス最適化

大量のデータを挿入する際のパフォーマンス最適化手法について説明する。

バルクインサート

最も効果的な最適化手法は、複数行を1つのINSERT文で挿入することである。

 # 低速：単一行挿入を繰り返す
 INSERT INTO users (name, email) VALUES ('User 1', 'user1@example.com');
 INSERT INTO users (name, email) VALUES ('User 2', 'user2@example.com');
 # ... 10,000回繰り返す
 
 # 高速：1,000行ずつバッチ挿入
 INSERT INTO users (name, email)
    VALUES
       ('User 1', 'user1@example.com'),
       ('User 2', 'user2@example.com'),
       # ... 1,000行
       ('User 1000', 'user1000@example.com');
 # これを10回繰り返す


挿入操作の時間配分を以下に示す。

  • 接続 : 3
    データベースへの接続時間
  • クエリ送信 : 2
    SQL文のサーバへの送信時間
  • パース : 2
    SQL文の解析時間
  • 行挿入: 1 × 行サイズ
    各行の挿入時間
  • インデックス挿入 : 1 × インデックス数
    B-treeインデックスの場合、テーブルサイズに対して対数的 (log N) に増加
  • クローズ : 1
    接続のクローズ時間


複数行INSERT文は、接続・パース・クローズのオーバーヘッドを大幅に削減できる。

大量データの挿入

LOAD DATA INFILE

LOAD DATA INFILE 文は、MySQLで最も高速なデータインポート方法である。
通常のINSERT文よりも20倍以上高速な場合がある。

 # CSVファイルからデータをインポート (サーバ側ファイル)
 LOAD DATA INFILE '/path/to/users.csv'
    INTO TABLE users
    FIELDS TERMINATED BY ','
    ENCLOSED BY '"'
    LINES TERMINATED BY '\n'
    IGNORE 1 ROWS  -- ヘッダ行をスキップ
    (name, email, age);
 
 # ローカルファイルからインポート (クライアント側ファイル)
 LOAD DATA LOCAL INFILE '/path/to/users.csv'
    INTO TABLE users
    FIELDS TERMINATED BY ','
    ENCLOSED BY '"'
    LINES TERMINATED BY '\n'
    IGNORE 1 ROWS
    (name, email, age);


LOAD DATA INFILE と LOAD DATA LOCAL INFILE の違いを以下に示す。

LOAD DATA INFILEとLOAD DATA LOCAL INFILEの比較
項目 LOAD DATA INFILE LOAD DATA LOCAL INFILE
ファイルの場所 サーバ側 クライアント側
速度 高速 サーバ側より低速 (ネットワーク転送あり)
エラー時の動作 即座に停止可能 転送開始後は途中停止不可
セキュリティ サーバ側ファイルアクセス権限が必要 local_infile 設定が必要

サーバ側で設定する場合は、[mysqld] セクションに local_infile=1 を設定する。


セキュリティ設定の確認と変更方法を以下に示す。

 # local_infile の設定を確認
 SHOW VARIABLES LIKE 'local_infile';
 
 # local_infile を有効化 (サーバ側)
 SET GLOBAL local_infile = 1;


※重要
MySQL 8.0以降では、local_infile はデフォルトで無効化されている。
セキュリティリスクを理解した上で使用する必要がある。

トランザクションの活用

InnoDBテーブルでは、トランザクション内で複数の挿入を実行することで、パフォーマンスを向上できる。

 # 自動コミットを無効化
 START TRANSACTION;
 
 # 大量のINSERT文を実行
 INSERT INTO users (name, email) VALUES ('User 1', 'user1@example.com');
 INSERT INTO users (name, email) VALUES ('User 2', 'user2@example.com');
 # ... 1,000回繰り返す
 
 # コミット
 COMMIT;


理由を以下に示す。

  • トランザクションログの書き込みが1回で済む
    各挿入ごとにコミットするよりも高速
  • ロックの取得と解放が1回で済む
    オーバーヘッドが削減される


autocommitの無効化
 # autocommitを無効化
 SET autocommit = 0;
 
 # 大量のINSERT文を実行
 INSERT INTO users (name, email) VALUES ('User 1', 'user1@example.com');
 INSERT INTO users (name, email) VALUES ('User 2', 'user2@example.com');
 # ... 1,000回繰り返す

 # 手動でコミット
 COMMIT;
 
 # autocommitを再度有効化
 SET autocommit = 1;


インデックスの無効化

大量のデータを挿入する際は、インデックスを一時的に無効化することでパフォーマンスを向上できる。

 # MyISAMテーブルの場合
 ALTER TABLE users DISABLE KEYS;
 
 # 大量データを挿入
 INSERT INTO users (name, email) VALUES ... ;
 
 # インデックスを再構築
 ALTER TABLE users ENABLE KEYS;


※注意
この方法は MyISAM ストレージエンジンでのみ有効である。
InnoDBでは使用できない。

InnoDBテーブルでは、データ挿入後にインデックスを作成する方法が有効である。

 # インデックスなしでテーブルを作成
 CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100)
 );
 
 # 大量データを挿入
 INSERT INTO users (id, name, email) VALUES ... ;
 
 # 挿入後にインデックスを作成
 CREATE INDEX idx_email ON users(email);


UNIQUE制約とインデックスの考慮

UNIQUE制約やインデックスが多いテーブルでは、挿入時のパフォーマンスが低下する。

InnoDBテーブルでは、以下の最適化手法が有効である。

 # UNIQUE制約チェックの一時的な無効化 (InnoDB)
 SET unique_checks = 0;
 
 # 大量データを挿入
 INSERT INTO users (id, name, email) VALUES ... ;
 
 # UNIQUE制約チェックを再度有効化
 SET unique_checks = 1;


この設定により、セカンダリキーのディスクI/Oを大幅に削減できる。

PRIMARY KEY順序での挿入最適化を以下に示す。

 # InnoDBはクラスター化インデックスを使用
 # PRIMARY KEY順序での挿入が最適
 
 # 推奨：PRIMARY KEY順序でのインサート
 INSERT INTO users (id, name, email)
    VALUES
       (1, 'User 1', 'user1@example.com'),
       (2, 'User 2', 'user2@example.com'),
       (3, 'User 3', 'user3@example.com');
 
 # 非推奨：ランダム順序でのインサート (ページ分割が多発)
 INSERT INTO users (id, name, email)
    VALUES
       (100, 'User 100', 'user100@example.com'),
       (5, 'User 5', 'user5@example.com'),
       (75, 'User 75', 'user75@example.com');


AUTO_INCREMENT最適化を以下に示す。

 # innodb_autoinc_lock_mode の確認
 SHOW VARIABLES LIKE 'innodb_autoinc_lock_mode';
 
 # interleavedモード (値: 2) に設定 (MySQL 8.0のデフォルト)
 # 並行挿入のパフォーマンスが向上


※重要
innodb_autoinc_lock_mode=2 は、レプリケーション環境でROWベースのバイナリログ形式が必要である。

挿入後の最適化

大量のデータを挿入した後は、OPTIMIZE TABLE 文でテーブルを最適化する。

 # テーブルの最適化 (断片化の解消、領域の再利用)
 
 OPTIMIZE TABLE users;


OPTIMIZE TABLEの効果を以下に示す。

  • テーブルの断片化を解消
    ディスクI/Oの効率化
  • 未使用領域の再利用
    ディスクスペースの最適化
  • インデックスの再構築
    クエリパフォーマンスの向上


※注意
OPTIMIZE TABLEは、テーブルをロックするため、本番環境では低負荷時に実行することが推奨される。


INSERT文とREPLACE文の違い

INSERT ... ON DUPLICATE KEY UPDATE と REPLACE 文は、どちらも重複キーの処理を行うが、動作が異なる。

INSERT ON DUPLICATE KEY UPDATE と REPLACE の比較
項目 INSERT ON DUPLICATE KEY UPDATE REPLACE
基本動作 重複時に既存行を更新 重複時に既存行を削除して新規行を挿入
内部処理 UPDATE処理 DELETE → INSERT処理
速度 高速 低速 (削除と挿入の2段階)
PRIMARY KEYの変更 変更されない 変更される可能性がある
(AUTO_INCREMENTが再採番)
外部キー制約 問題なし DELETE時に制約違反の可能性
トリガー UPDATEトリガーが起動 DELETEとINSERTトリガーが起動
部分的な更新 指定した列のみ更新可能 全列が新しい値に置き換わる
推奨度 推奨 非推奨 (特殊な用途以外)


REPLACE文の構文

 # REPLACE文の基本構文
 REPLACE INTO products (id, name, price)
    VALUES (1, 'Product A', 1000);
 
 # 複数行のREPLACE
 REPLACE INTO products (id, name, price)
    VALUES
       (1, 'Product A', 1000),
       (2, 'Product B', 1500);


動作の違いの例

 # テーブル作成
 CREATE TABLE test_products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    price INT
 );
 
 # 初期データ挿入
 INSERT INTO test_products (id, name, price)
    VALUES (1, 'Product A', 1000);
 
 # INSERT ON DUPLICATE KEY UPDATE の場合
 INSERT INTO test_products (id, name, price)
    VALUES (1, 'Product A Updated', 1200) AS new
    ON DUPLICATE KEY UPDATE
       name = new.name,
       price = new.price;
 -- 結果: id=1 のまま、name と price のみ更新
 
 # REPLACE の場合
 REPLACE INTO test_products (id, name, price)
    VALUES (1, 'Product A Replaced', 1500);
 -- 結果 : 古い行が削除され、新しい行が挿入される
 -- AUTO_INCREMENTカウンタが進む可能性がある


使い分けの指針を以下に示す。

  • 部分的な更新が必要な場合
    INSERT ON DUPLICATE KEY UPDATE を使用する。
  • 全列を新しい値に置き換える必要がある場合
    REPLACE を使用できるが、DELETE → INSERT の動作に注意が必要である。
  • 外部キー制約がある場合
    INSERT ON DUPLICATE KEY UPDATE を使用する。 (REPLACEは制約違反の可能性がある)
  • パフォーマンスが重要な場合
    INSERT ON DUPLICATE KEY UPDATE を使用する。 (REPLACEより高速)
  • AUTO_INCREMENTカウンタの進行を避けたい場合
    INSERT ON DUPLICATE KEY UPDATE を使用する。


一般的には、INSERT ... ON DUPLICATE KEY UPDATE の使用が推奨される。


「https://mochiu.net/index.php?title=MySQL_-_INSERT&oldid=14260」から取得