【SQL初心者向け】MySQLでテーブル間のデータ連携をマスターしよう!挿入テクニック徹底解説
MySQLで別のテーブルからテーブルに挿入する方法
INSERT ... SELECT ステートメントを使う
これは、最も一般的で汎用性の高い方法です。構文は以下の通りです。
INSERT INTO ターゲットテーブル (カラム1, カラム2, ...)
SELECT 取得カラム1, 取得カラム2, ...
FROM ソーステーブル;
例:
-- 商品テーブル (products) から、在庫管理テーブル (inventory) に商品IDと在庫数を挿入する
INSERT INTO inventory (product_id, stock_count)
SELECT product_id, quantity
FROM products;
別のテーブルからデータを1行だけ挿入したい場合などに便利です。構文は以下の通りです。
INSERT INTO ターゲットテーブル (カラム1, カラム2, ...)
VALUES (
(SELECT 取得カラム1 FROM ソーステーブル WHERE 条件),
(SELECT 取得カラム2 FROM ソーステーブル WHERE 条件),
...
);
-- 注文ID 1234 の注文詳細を、注文詳細履歴テーブル (order_details_history) に挿入する
INSERT INTO order_details_history (order_id, product_id, quantity)
VALUES (
(SELECT order_id FROM orders WHERE order_id = 1234),
(SELECT product_id FROM order_details WHERE order_id = 1234),
(SELECT quantity FROM order_details WHERE order_id = 1234)
);
補足:
- ターゲットテーブルとソーステーブルのカラム数は一致する必要があります。
- カラム名と取得カラム名が一致しない場合は、エイリアスを使用できます。
- WHERE句を使用して、挿入するデータの条件を絞り込むことができます。
- INSERT ... SELECTステートメントは、複数行のデータを挿入できます。
-- 商品テーブル (products) から、在庫管理テーブル (inventory) に商品IDと在庫数を挿入する
CREATE TABLE IF NOT EXISTS products (
product_id INT PRIMARY KEY AUTO_INCREMENT,
product_name VARCHAR(255) NOT NULL,
price DECIMAL(10,2) NOT NULL
);
CREATE TABLE IF NOT EXISTS inventory (
product_id INT PRIMARY KEY,
stock_count INT NOT NULL,
FOREIGN KEY (product_id) REFERENCES products(product_id)
);
INSERT INTO products (product_name, price)
VALUES
('Tシャツ', 1200),
('パンツ', 2500),
('靴', 3800);
INSERT INTO inventory (product_id, stock_count)
SELECT product_id, quantity
FROM products;
-- 注文ID 1234 の注文詳細を、注文詳細履歴テーブル (order_details_history) に挿入する
CREATE TABLE IF NOT EXISTS orders (
order_id INT PRIMARY KEY AUTO_INCREMENT,
customer_id INT NOT NULL,
order_date DATE NOT NULL
);
CREATE TABLE IF NOT EXISTS order_details (
order_id INT NOT NULL,
product_id INT NOT NULL,
quantity INT NOT NULL,
FOREIGN KEY (order_id) REFERENCES orders(order_id),
FOREIGN KEY (product_id) REFERENCES products(product_id)
);
CREATE TABLE IF NOT EXISTS order_details_history (
order_id INT NOT NULL,
product_id INT NOT NULL,
quantity INT NOT NULL,
order_date DATE NOT NULL,
FOREIGN KEY (order_id) REFERENCES orders(order_id),
FOREIGN KEY (product_id) REFERENCES products(product_id)
);
INSERT INTO orders (customer_id, order_date)
VALUES (1, '2024-04-19');
INSERT INTO order_details (order_id, product_id, quantity)
VALUES
(1, 1, 2),
(1, 2, 1);
INSERT INTO order_details_history (order_id, product_id, quantity, order_date)
VALUES (
(SELECT order_id FROM orders WHERE order_id = 1),
(SELECT product_id FROM order_details WHERE order_id = 1),
(SELECT quantity FROM order_details WHERE order_id = 1),
(SELECT order_date FROM orders WHERE order_id = 1)
);
説明:
- 上記のコードは、商品テーブル (products)、在庫管理テーブル (inventory)、注文テーブル (orders)、注文詳細テーブル (order_details)、注文詳細履歴テーブル (order_details_history) の5つのテーブルを作成し、それぞれにデータ挿入を行うものです。
- 各テーブルのスキーマは、説明のために簡略化されています。実際の運用では、必要に応じてカラムを追加したり、データ型を変更したりする必要があります。
- サンプルコードは、MySQL 8.0を使用することを想定しています。他のバージョンを使用している場合は、構文が多少異なる場合があります。
MySQLで別のテーブルからテーブルに挿入するその他の方法
LOAD DATA INFILE ステートメントを使う
テキストファイルからデータをテーブルに挿入する場合に便利です。構文は以下の通りです。
LOAD DATA INFILE 'ファイルパス'
INTO TABLE ターゲットテーブル
(カラム1, カラム2, ... )
(
FIELD TERMINATOR='\t' # 区切り文字を指定
LINES TERMINATED='\n' # 行末文字を指定
);
-- products.csv というテキストファイルから、products テーブルに商品データを読み込む
LOAD DATA INFILE '/path/to/products.csv'
INTO TABLE products
(product_name, price)
(
FIELD TERMINATOR=',',
LINES TERMINATED='\n'
);
トリガーを使う
あるテーブルでデータが更新されたときに、別のテーブルに自動的にデータを挿入するトリガーを作成できます。
-- orders テーブルに新しい注文が追加されたときに、order_details テーブルに注文詳細を挿入するトリガー
CREATE TRIGGER insert_order_details
AFTER INSERT ON orders
FOR EACH ROW
BEGIN
INSERT INTO order_details (order_id, product_id, quantity)
VALUES (NEW.order_id, NEW.product_id, NEW.quantity);
END;
ストアドプロシージャを使う
複雑なデータ挿入処理をカプセル化するために、ストアドプロシージャを作成できます。
-- 注文IDと商品IDを指定して、order_details テーブルに注文詳細を挿入するストアドプロシージャ
CREATE PROCEDURE insert_order_detail(
IN order_id INT,
IN product_id INT,
IN quantity INT
)
BEGIN
INSERT INTO order_details (order_id, product_id, quantity)
VALUES (order_id, product_id, quantity);
END;
-- ストアドプロシージャを呼び出す
CALL insert_order_detail(1234, 1, 2);
- 上記以外にも、MySQLでデータを挿入する方法はいくつかあります。
- 複雑な処理を行う場合は、ストアドプロシージャを使用すると、コードをよりわかりやすく、保守しやすくなります。
sql mysql