MySQL - INSERTのソースを表示

== 概要 ==
INSERT文は、MySQLのテーブルに新しい行を挿入するためのSQL文である。<br>
<br>
基本的な構文として、<code>VALUES</code> 形式、<code>SET</code> 形式、<code>VALUES ROW()</code> 形式が提供されており、用途に応じて使い分けることができる。<br>
INSERT文は、単一行だけでなく複数行を一度に挿入することも可能であり、パフォーマンスの観点からは複数行挿入が推奨される。<br>
<br>
また、<code>INSERT ... SELECT</code> 構文を使用することで、他のテーブルから取得したデータを直接挿入することができ、データ移行やバックアップ作成に有用である。<br>
<br>
重複キーの処理については、<code>INSERT ... ON DUPLICATE KEY UPDATE</code> 構文を使用することで、UPSERT (INSERT または UPDATE) 動作を実現できる。<br>
これにより、同一キーのレコードが存在する場合は更新、存在しない場合は挿入という処理を1つのSQL文で記述可能である。<br>
<br>
<code>AUTO_INCREMENT</code> 列への挿入時は、値を指定せずに自動採番させることが一般的であり、<code>LAST_INSERT_ID()</code> 関数で採番されたIDを取得できる。<br>
<br>
外部キー制約が設定されている場合は、参照整合性を維持するために、親テーブルに存在する値のみを挿入する必要がある。<br>
<br>
大量のデータを挿入する際は、バルクインサート (複数行を1文で挿入) や <code>LOAD DATA INFILE</code> 文の使用により、パフォーマンスを大幅に向上させることができる。<br>
InnoDBテーブルでは、UNIQUE制約チェックの無効化やPRIMARY KEY順序での挿入により、さらなる最適化が可能である。<br>
<br>
トランザクション内で実行することで、挿入の一貫性を保証し、エラー発生時にはロールバックにより挿入を取り消すことができる。<br>
<br><br>

== 基本構文 ==
==== VALUES構文 ====
最も一般的なINSERT文の構文は、<code>VALUES</code> 句を使用する形式である。<br>
<br>
 <syntaxhighlight lang="mysql">
 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]
 </syntaxhighlight>
<br>
基本的な使用例を以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 # 単一行の挿入
 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);
 </syntaxhighlight>
<br>
==== SET構文 ====
<code>SET</code> 句を使用する形式は、UPDATE文と同様の構文で挿入を行う。<br>
<br>
 <syntaxhighlight lang="mysql">
 INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE]
    [INTO] tbl_name
    SET assignment_list
    [ON DUPLICATE KEY UPDATE assignment_list]
 </syntaxhighlight>
<br>
使用例を以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 # SET構文での挿入
 INSERT INTO users
    SET name = 'Shiro Ito',
        email = 'shiro@example.com',
        age = 33;
 </syntaxhighlight>
<br>
<u>※注意</u><br>
<u>SET構文では、複数行の挿入はできない。</u><br>
<u>VALUES構文の方が柔軟性が高いため、一般的にはVALUES構文が推奨される。</u><br>
<br>
==== VALUES ROW()構文 ====
MySQL 8.0.19以降では、<code>VALUES ROW()</code> 構文が利用可能である。<br>
<br>
 <syntaxhighlight lang="mysql">
 # VALUES ROW()構文
 INSERT INTO users (name, email, age)
    VALUES ROW('Goro Nakamura', 'goro@example.com', 29);
 </syntaxhighlight>
<br>
VALUES ROW()構文の特徴を以下に示す。<br>
<br>
* テーブル値コンストラクタとして使用可能
*: JOINやUNIONと組み合わせ可能。
* ROW()は空にできない
*: 各スカラー値はNULL可。
* 出力列名は暗黙的に <u>column_0, column_1, column_2...</u> となる
*: エイリアスで明示的に指定可能。
<br>
==== 修飾子 ====
INSERT文では、下表に示す修飾子を使用して挿入動作を制御できる。<br>
<br>
<center>
{| class="wikitable"
|+ INSERT文で使用可能な修飾子
! 修飾子 !! 説明
|-
| LOW_PRIORITY || 他のクライアントがテーブルを読み取っていない時に挿入を実行する。<br>MyISAM、MEMORY、MERGEストレージエンジンで有効
|-
| HIGH_PRIORITY || 通常の挿入よりも優先的に実行する。<br><code>LOW_PRIORITY</code> との同時使用は不可
|-
| DELAYED || MySQL 8.0では無視される (廃止予定)。<br>MySQL 5.7以前では、挿入をキューイングして非同期実行
|-
| IGNORE || 重複キーエラーや制約違反エラーを <code>WARNING</code> に変換して処理を継続する。<br>エラーが発生した行はスキップされる
|}
</center>
<br>
使用例を以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 # 低優先度で挿入を実行
 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');
 </syntaxhighlight>
<br>
==== PARTITION句 ====
パーティション化されたテーブルでは、<code>PARTITION</code> 句を使用して特定のパーティションへの挿入を明示的に指定できる。<br>
<br>
 <syntaxhighlight lang="mysql">
 # 特定のパーティションに挿入
 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');
 </syntaxhighlight>
<br>
<u>※注意</u><br>
<u>指定したパーティションが、挿入するデータのパーティショニング条件と一致しない場合はエラーになる。</u><br>
<u>通常は、PARTITION句を省略して、MySQLに自動的にパーティションを決定させることが推奨される。</u><br>
<br><br>

== 複数行の挿入 ==
複数行を一度に挿入することで、パフォーマンスを大幅に向上させることができる。<br>
<br>
==== 基本的な複数行挿入 ====
VALUES句に複数の値リストをカンマ区切りで指定する。<br>
<br>
 <syntaxhighlight lang="mysql">
 # 複数行を一度に挿入
 INSERT INTO products (name, price, stock)
    VALUES
       ('Product A', 1000, 50),
       ('Product B', 1500, 30),
       ('Product C', 2000, 20),
       ('Product D', 2500, 10);
 </syntaxhighlight>
<br>
==== パフォーマンスの利点 ====
複数行挿入は、単一行挿入を繰り返すよりも大幅に高速である。<br>
<br>
理由を以下に示す。<br>
<br>
* ネットワークラウンドトリップの削減
*: 1回のSQL文送信で複数行を挿入できる。
* パースとプランニングのオーバーヘッド削減
*: SQL文の解析と実行計画の作成が1回で済む。
* ロックの取得と解放の回数削減
*: テーブルロックの取得が1回で済む。
* インデックス更新の最適化
*: 複数行のインデックス更新を一括で実行できる。
<br>
パフォーマンス比較の例を以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 # 低速：単一行挿入を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');
 </syntaxhighlight>
<br>
一般的に、複数行挿入は単一行挿入の繰り返しに比べて、数倍から数十倍の速度向上が期待できる。<br>
<br>
==== 推奨バッチサイズ ====
一度に挿入する行数の推奨値を以下に示す。<br>
<br>
* 推奨範囲
*: 1,000～10,000行
* 理由
*: SQL文のサイズとパフォーマンスのバランスが良い。
* 制限事項
*: <code>max_allowed_packet</code> の設定値を超えないようにする必要がある。
<br>
<code>max_allowed_packet</code> の確認と変更方法を以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 # 現在の設定値を確認 (デフォルトは4MBまたは16MB)
 SHOW VARIABLES LIKE 'max_allowed_packet';
 
 # セッション単位で変更 (例: 64MBに設定)
 SET GLOBAL max_allowed_packet = 67108864;  -- 64MB
 </syntaxhighlight>
<br>
<u>※注意</u><br>
<u><code>max_allowed_packet</code> を超える巨大なINSERT文を実行すると、エラーになる。</u><br>
<u>大量データの挿入時は、適切なバッチサイズに分割することが重要である。</u><br>
<br><br>

== INSERT ... SELECT ==
他のテーブルから取得したデータを直接挿入する構文である。<br>
<br>
==== 基本構文 ====
 <syntaxhighlight lang="mysql">
 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]
 </syntaxhighlight>
<br>
==== 基本的な使用例 ====
 <syntaxhighlight lang="mysql">
 # 他のテーブルから全データをコピー
 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';
 </syntaxhighlight>
<br>
==== JOINを使用した挿入 ====
複数のテーブルを結合してデータを挿入できる。<br>
<br>
 <syntaxhighlight lang="mysql">
 # 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;
 </syntaxhighlight>
<br>
==== 同一テーブルからの選択 ====
同じテーブルからSELECTして挿入する場合、MySQLは内部的に一時テーブルを作成する。<br>
<br>
 <syntaxhighlight lang="mysql">
 # 同一テーブルからの挿入 (内部的に一時テーブルを使用)
 INSERT INTO users (name, email, created_at)
    SELECT CONCAT(name, ' (copy)'), CONCAT('copy_', email), NOW()
       FROM users
       WHERE status = 'active';
 </syntaxhighlight>
<br>
<u>※注意</u><br>
<u>同一テーブルからのSELECTは、一時テーブルの作成によりパフォーマンスが低下する可能性がある。</u><br>
<br>
==== サブクエリとの組み合わせ ====
 <syntaxhighlight lang="mysql">
 # サブクエリを使用した挿入
 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;
 </syntaxhighlight>
<br><br>

== INSERT ... ON DUPLICATE KEY UPDATE ==
重複キーが検出された場合にUPDATE文を実行する構文である。<br>
これにより、UPSERT (INSERT または UPDATE) 動作を1つのSQL文で実現できる。<br>
<br>
==== 基本構文 ====
 <syntaxhighlight lang="mysql">
 INSERT INTO tbl_name (col_name [, col_name] ...)
    VALUES (value_list) [, (value_list)] ...
    ON DUPLICATE KEY UPDATE
       col_name = expression [, col_name = expression] ...
 </syntaxhighlight>
<br>
==== 基本的な使用例 ====
 <syntaxhighlight lang="mysql">
 # 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以前
 </syntaxhighlight>
<br>
==== VALUES()関数の非推奨化 ====
MySQL 8.0.20以降、<code>VALUES()</code> 関数は非推奨となった。<br>
代わりに、新しい値のエイリアスを使用する構文が推奨される。<br>
<br>
 <syntaxhighlight lang="mysql">
 # 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;
 </syntaxhighlight>
<br>
==== affected_rowsの値 ====
<code>ON DUPLICATE KEY UPDATE</code> の実行結果は、以下のように解釈される。<br>
<br>
<center>
{| class="wikitable"
|+ affected_rowsの値と意味
! affected_rows !! 意味
|-
| 1 || 新しい行が挿入された
|-
| 2 || 既存の行が更新された
|-
| 0 || 既存の行が存在したが、値が変更されなかった<br>(更新前と更新後が同じ値)
|}
</center>
<br>
確認方法を以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 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=変更なし
 </syntaxhighlight>
<br>
==== 計算式を使用した更新 ====
 <syntaxhighlight lang="mysql">
 # 既存の値に加算
 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();
 </syntaxhighlight>
<br><br>

== DEFAULT値とNULL ==
列にDEFAULT値が設定されている場合、または NULL が許可されている場合の挿入動作について説明する。<br>
<br>
==== DEFAULTキーワード ====
<code>DEFAULT</code> キーワードを使用して、列のデフォルト値を明示的に挿入できる。<br>
<br>
 <syntaxhighlight lang="mysql">
 # テーブル定義
 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');
 </syntaxhighlight>
<br>
==== NULL値の挿入 ====
NULL が許可されている列には、明示的に NULL を挿入できる。<br>
<br>
 <syntaxhighlight lang="mysql">
 # テーブル定義
 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');
 </syntaxhighlight>
<br>
==== STRICTモードと非STRICTモード ====
MySQLのSQLモード設定により、不正な値の挿入時の動作が異なる。<br>
<br>
<center>
{| class="wikitable"
|+ STRICTモードと非STRICTモードの違い
! 項目 !! STRICTモード !! 非STRICTモード
|-
| NOT NULL列への NULL挿入 || エラー || DEFAULT値または型の暗黙的なデフォルト値が挿入される
|-
| DEFAULT値がない列の省略 || エラー || 型の暗黙的なデフォルト値が挿入される
|-
| 範囲外の値 || エラー || 範囲の境界値に切り詰められる
|-
| 不正な日付 || エラー || '0000-00-00' が挿入される
|}
</center>
<br>
SQLモードの確認と変更方法を以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 # 現在のSQLモードを確認
 SELECT @@sql_mode;
 
 # STRICTモードを有効化
 SET sql_mode = 'STRICT_TRANS_TABLES';
 
 # STRICTモードを無効化
 SET sql_mode = '';
 </syntaxhighlight>
<br>
<u>※推奨</u><br>
<u>データの整合性を保つため、STRICTモード (STRICT_TRANS_TABLES) の使用が推奨される。</u><br>
<br><br>

== AUTO_INCREMENT ==
<code>AUTO_INCREMENT</code> 属性を持つ列は、値を指定しない場合に自動的に連番が採番される。<br>
<br>
==== 基本的な使用例 ====
 <syntaxhighlight lang="mysql">
 # テーブル定義
 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 が自動採番される
 </syntaxhighlight>
<br>
==== LAST_INSERT_ID()関数 ====
<code>LAST_INSERT_ID()</code> 関数を使用して、最後に採番されたAUTO_INCREMENT値を取得できる。<br>
<br>
 <syntaxhighlight lang="mysql">
 # 挿入後にIDを取得
 INSERT INTO users (name, email)
    VALUES ('User C', 'userc@example.com');
 
 SELECT LAST_INSERT_ID();
 -- 最後に挿入された行のid値を返す
 </syntaxhighlight>
<br>
<u>※重要</u><br>
<u>LAST_INSERT_ID() はセッション単位で管理される。</u><br>
<u>他のクライアントの挿入操作の影響を受けない。</u><br>
<br>
==== 複数行挿入時のLAST_INSERT_ID() ====
複数行を1度に挿入した場合、<code>LAST_INSERT_ID()</code> は最初の行のIDを返す。<br>
<br>
 <syntaxhighlight lang="mysql">
 # 複数行を挿入
 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 を返す
 </syntaxhighlight>
<br>
挿入された全てのIDを取得する方法を以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 # 挿入された行数を取得
 SELECT ROW_COUNT();  -- 3 を返す
 
 # 挿入されたIDの範囲
 # 開始ID: LAST_INSERT_ID()
 # 終了ID: LAST_INSERT_ID() + ROW_COUNT() - 1
 </syntaxhighlight>
<br>
==== AUTO_INCREMENT値の明示的な指定 ====
AUTO_INCREMENT列に明示的に値を指定することも可能である。<br>
<br>
 <syntaxhighlight lang="mysql">
 # 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
 </syntaxhighlight>
<br>
<u>※注意</u><br>
<u>既存のIDと重複する値を指定すると、重複キーエラーになる。</u><br>
<br>
==== AUTO_INCREMENT初期値の設定 ====
 <syntaxhighlight lang="mysql">
 # テーブル作成時に初期値を指定
 CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100)
 ) AUTO_INCREMENT = 1000;
 
 # 既存テーブルの初期値を変更
 ALTER TABLE products AUTO_INCREMENT = 2000;
 </syntaxhighlight>
<br><br>

== 外部キー制約と挿入 ==
外部キー制約が設定されている場合、参照整合性を維持するために、親テーブルに存在する値のみを挿入できる。<br>
<br>
==== 基本的な外部キー制約 ====
 <syntaxhighlight lang="mysql">
 # 親テーブル
 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);
 </syntaxhighlight>
<br>
==== ON DELETE CASCADE ====
親レコード削除時に、子レコードも自動的に削除される制約である。<br>
<br>
 <syntaxhighlight lang="mysql">
 # 外部キー制約の定義
 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 の行も自動削除される
 </syntaxhighlight>
<br>
==== 外部キーチェックの一時的な無効化 ====
データの一括挿入時に外部キーチェックを一時的に無効化する方法を以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 # 外部キーチェックを無効化
 SET FOREIGN_KEY_CHECKS = 0;
 
 # 外部キー制約を無視してデータ挿入
 INSERT INTO orders (id, user_id, amount)
    VALUES (102, 999, 3000);  -- user_id=999は親テーブルに存在しないがエラーにならない
 
 # 外部キーチェックを再度有効化
 SET FOREIGN_KEY_CHECKS = 1;
 </syntaxhighlight>
<br>
<u>※警告</u><br>
<u>外部キーチェックを無効にすると、データの整合性が失われる可能性がある。</u><br>
<u>使用後は必ず再度有効化し、データの整合性を確認することが重要である。</u><br>
<br><br>

== 挿入結果の確認 ==
INSERT文の実行結果は、挿入された行数として表示される。<br>
<br>
 <syntaxhighlight lang="mysql">
 INSERT INTO users (name, email)
    VALUES ('User A', 'usera@example.com');
 </syntaxhighlight>
<br>
 # 出力例：
 
 Query OK, 1 row affected (0.01 sec)
<br>
複数行挿入時の出力例を以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 INSERT INTO users (name, email)
    VALUES
       ('User B', 'userb@example.com'),
       ('User C', 'userc@example.com'),
       ('User D', 'userd@example.com');
 </syntaxhighlight>
<br>
 # 出力例：
 
 Query OK, 3 rows affected (0.02 sec)
 Records: 3  Duplicates: 0  Warnings: 0
<br>
==== ROW_COUNT()関数 ====
<code>ROW_COUNT()</code> 関数を使用して、直前のINSERT文で挿入された行数を取得できる。<br>
<br>
 <syntaxhighlight lang="mysql">
 INSERT INTO products (name, price)
    VALUES ('Product A', 1000);
 
 SELECT ROW_COUNT();
 </syntaxhighlight>
<br>
 # 出力例
 
 # +--------------+
 # | ROW_COUNT()  |
 # +--------------+
 # |            1 |
 # +--------------+
<br>
<u>※注意</u><br>
<u>ROW_COUNT()関数は、直後のSELECT文で実行する必要がある。</u><br>
<u>他のSQL文を実行すると、値がリセットされる。</u><br>
<br>
==== IGNORE修飾子使用時 ====
<code>IGNORE</code> 修飾子を使用した場合、エラーが発生した行はスキップされ、WARNINGとして記録される。<br>
<br>
 <syntaxhighlight lang="mysql">
 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');
 </syntaxhighlight>
<br>
 # 出力例：
 
 Query OK, 2 rows affected, 1 warning (0.01 sec)
 Records: 3  Duplicates: 1  Warnings: 1
<br>
WARNINGの内容を確認する方法を以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 SHOW WARNINGS;
 </syntaxhighlight>
<br>
 # 出力例
 
 # +---------+------+---------------------------------------+
 # | Level   | Code | Message                               |
 # +---------+------+---------------------------------------+
 # | Warning | 1062 | Duplicate entry '1' for key 'PRIMARY' |
 # +---------+------+---------------------------------------+
<br><br>

== パフォーマンス最適化 ==
大量のデータを挿入する際のパフォーマンス最適化手法について説明する。<br>
<br>
==== バルクインサート ====
最も効果的な最適化手法は、複数行を1つのINSERT文で挿入することである。<br>
<br>
 <syntaxhighlight lang="mysql">
 # 低速：単一行挿入を繰り返す
 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回繰り返す
 </syntaxhighlight>
<br>
挿入操作の時間配分を以下に示す。<br>
<br>
* 接続 : 3
*: データベースへの接続時間
* クエリ送信 : 2
*: SQL文のサーバへの送信時間
* パース : 2
*: SQL文の解析時間
* 行挿入: 1 × 行サイズ
*: 各行の挿入時間
* インデックス挿入 : 1 × インデックス数
*: B-treeインデックスの場合、テーブルサイズに対して対数的 (log N) に増加
* クローズ : 1
*: 接続のクローズ時間
<br>
複数行INSERT文は、接続・パース・クローズのオーバーヘッドを大幅に削減できる。<br>
<br>
==== 大量データの挿入 ====
===== LOAD DATA INFILE =====
<code>LOAD DATA INFILE</code> 文は、MySQLで最も高速なデータインポート方法である。<br>
通常のINSERT文よりも20倍以上高速な場合がある。<br>
<br>
 <syntaxhighlight lang="mysql">
 # 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);
 </syntaxhighlight>
<br>
<code>LOAD DATA INFILE</code> と <code>LOAD DATA LOCAL INFILE</code> の違いを以下に示す。<br>
<br>
<center>
{| class="wikitable"
|+ LOAD DATA INFILEとLOAD DATA LOCAL INFILEの比較
! 項目 !! LOAD DATA INFILE !! LOAD DATA LOCAL INFILE
|-
| ファイルの場所 || サーバ側 || クライアント側
|-
| 速度 || 高速 || サーバ側より低速 (ネットワーク転送あり)
|-
| エラー時の動作 || 即座に停止可能 || 転送開始後は途中停止不可
|-
| セキュリティ || サーバ側ファイルアクセス権限が必要 || <code>local_infile</code> 設定が必要
|}
</center>
<br>
セキュリティ設定の確認と変更方法を以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 # local_infile の設定を確認
 SHOW VARIABLES LIKE 'local_infile';
 
 # local_infile を有効化 (サーバ側)
 SET GLOBAL local_infile = 1;
 </syntaxhighlight>
<br>
<u>※重要</u><br>
<u>MySQL 8.0以降では、local_infile はデフォルトで無効化されている。</u><br>
<u>セキュリティリスクを理解した上で使用する必要がある。</u><br>
<br>
===== トランザクションの活用 =====
InnoDBテーブルでは、トランザクション内で複数の挿入を実行することで、パフォーマンスを向上できる。<br>
<br>
 <syntaxhighlight lang="mysql">
 # 自動コミットを無効化
 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;
 </syntaxhighlight>
<br>
理由を以下に示す。<br>
<br>
* トランザクションログの書き込みが1回で済む
*: 各挿入ごとにコミットするよりも高速
* ロックの取得と解放が1回で済む
*: オーバーヘッドが削減される
<br>
===== autocommitの無効化 =====
 <syntaxhighlight lang="mysql">
 # 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;
 </syntaxhighlight>
<br>
===== インデックスの無効化 =====
大量のデータを挿入する際は、インデックスを一時的に無効化することでパフォーマンスを向上できる。<br>
<br>
 <syntaxhighlight lang="mysql">
 # MyISAMテーブルの場合
 ALTER TABLE users DISABLE KEYS;
 
 # 大量データを挿入
 INSERT INTO users (name, email) VALUES ... ;
 
 # インデックスを再構築
 ALTER TABLE users ENABLE KEYS;
 </syntaxhighlight>
<br>
<u>※注意</u><br>
<u>この方法は MyISAM ストレージエンジンでのみ有効である。</u><br>
<u>InnoDBでは使用できない。</u><br>
<br>
InnoDBテーブルでは、データ挿入後にインデックスを作成する方法が有効である。<br>
<br>
 <syntaxhighlight lang="mysql">
 # インデックスなしでテーブルを作成
 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);
 </syntaxhighlight>
<br>
===== UNIQUE制約とインデックスの考慮 =====
UNIQUE制約やインデックスが多いテーブルでは、挿入時のパフォーマンスが低下する。<br>
<br>
InnoDBテーブルでは、以下の最適化手法が有効である。<br>
<br>
 <syntaxhighlight lang="mysql">
 # UNIQUE制約チェックの一時的な無効化 (InnoDB)
 SET unique_checks = 0;
 
 # 大量データを挿入
 INSERT INTO users (id, name, email) VALUES ... ;
 
 # UNIQUE制約チェックを再度有効化
 SET unique_checks = 1;
 </syntaxhighlight>
<br>
この設定により、セカンダリキーのディスクI/Oを大幅に削減できる。<br>
<br>
PRIMARY KEY順序での挿入最適化を以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 # 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');
 </syntaxhighlight>
<br>
AUTO_INCREMENT最適化を以下に示す。<br>
<br>
 <syntaxhighlight lang="mysql">
 # innodb_autoinc_lock_mode の確認
 SHOW VARIABLES LIKE 'innodb_autoinc_lock_mode';
 
 # interleavedモード (値: 2) に設定 (MySQL 8.0のデフォルト)
 # 並行挿入のパフォーマンスが向上
 </syntaxhighlight>
<br>
<u>※重要</u><br>
<u>innodb_autoinc_lock_mode=2 は、レプリケーション環境でROWベースのバイナリログ形式が必要である。</u><br>
<br>
===== 挿入後の最適化 =====
大量のデータを挿入した後は、<code>OPTIMIZE TABLE</code> 文でテーブルを最適化する。<br>
<br>
 <syntaxhighlight lang="mysql">
 # テーブルの最適化 (断片化の解消、領域の再利用)
 
 OPTIMIZE TABLE users;
 </syntaxhighlight>
<br>
OPTIMIZE TABLEの効果を以下に示す。<br>
<br>
* テーブルの断片化を解消
*: ディスクI/Oの効率化
* 未使用領域の再利用
*: ディスクスペースの最適化
* インデックスの再構築
*: クエリパフォーマンスの向上
<br>
<u>※注意</u><br>
<u>OPTIMIZE TABLEは、テーブルをロックするため、本番環境では低負荷時に実行することが推奨される。</u><br>
<br><br>

== INSERT文とREPLACE文の違い ==
<code>INSERT ... ON DUPLICATE KEY UPDATE</code> と <code>REPLACE</code> 文は、どちらも重複キーの処理を行うが、動作が異なる。<br>
<br>
<center>
{| class="wikitable"
|+ INSERT ON DUPLICATE KEY UPDATE と REPLACE の比較
! 項目 !! INSERT ON DUPLICATE KEY UPDATE !! REPLACE
|-
| 基本動作 || 重複時に既存行を更新 || 重複時に既存行を削除して新規行を挿入
|-
| 内部処理 || UPDATE処理 || DELETE → INSERT処理
|-
| 速度 || 高速 || 低速 (削除と挿入の2段階)
|-
| PRIMARY KEYの変更 || 変更されない || 変更される可能性がある<br>(AUTO_INCREMENTが再採番)
|-
| 外部キー制約 || 問題なし || DELETE時に制約違反の可能性
|-
| トリガー || UPDATEトリガーが起動 || DELETEとINSERTトリガーが起動
|-
| 部分的な更新 || 指定した列のみ更新可能 || 全列が新しい値に置き換わる
|-
| 推奨度 || 推奨 || 非推奨 (特殊な用途以外)
|}
</center>
<br>
==== REPLACE文の構文 ====
 <syntaxhighlight lang="mysql">
 # 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);
 </syntaxhighlight>
<br>
==== 動作の違いの例 ====
 <syntaxhighlight lang="mysql">
 # テーブル作成
 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カウンタが進む可能性がある
 </syntaxhighlight>
<br>
使い分けの指針を以下に示す。<br>
<br>
* 部分的な更新が必要な場合
*: 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 を使用する。
<br>
一般的には、<code>INSERT ... ON DUPLICATE KEY UPDATE</code> の使用が推奨される。<br>
<br><br>


{{#seo:
|title={{PAGENAME}} : Exploring Electronics and SUSE Linux | MochiuWiki
|keywords=MochiuWiki,Mochiu,Wiki,Mochiu Wiki,Electric Circuit,Electric,pcb,Mathematics,AVR,TI,STMicro,AVR,ATmega,MSP430,STM,Arduino,Xilinx,FPGA,Verilog,HDL,PinePhone,Pine Phone,Raspberry,Raspberry Pi,C,C++,C#,Qt,Qml,MFC,Shell,Bash,Zsh,Fish,SUSE,SLE,Suse Enterprise,Suse Linux,openSUSE,open SUSE,Leap,Linux,uCLnux,電気回路,電子回路,基板,プリント基板
|description={{PAGENAME}} - 電子回路とSUSE Linuxに関する情報 | This page is {{PAGENAME}} in our wiki about electronic circuits and SUSE Linux
|image=/resources/assets/MochiuLogo_Single_Blue.png
}}

__FORCETOC__
[[カテゴリ:MySQL]]