MySQL: INSERT INTO .. ON DUPLICATE KEY UPDATE で複数項目を更新する方法

2024-04-03

MySQL の INSERT INTO .. ON DUPLICATE KEY UPDATE を使って複数項目を更新する方法

INSERT INTO .. ON DUPLICATE KEY UPDATE は、レコードを挿入する際に、すでに存在するレコードがあれば更新する構文です。これは、UPSERT とも呼ばれます。

構文

INSERT INTO table_name (column_name, ...)
VALUES (value1, value2, ...)
ON DUPLICATE KEY UPDATE column_name = value, ...;

説明

table_name: 挿入するテーブル名
column_name: 挿入する列名
value: 挿入する値
ON DUPLICATE KEY UPDATE: 重複キーの場合の更新処理

例

INSERT INTO users (name, age)
VALUES ('山田太郎', 20)
ON DUPLICATE KEY UPDATE age = 21;

この例では、users テーブルに 山田太郎 という名前のレコードが存在する場合、年齢を 21 に更新します。

複数項目の更新

ON DUPLICATE KEY UPDATE 句では、複数の項目を更新することができます。

INSERT INTO users (name, age, email)
VALUES ('山田太郎', 20, '[email protected]')
ON DUPLICATE KEY UPDATE age = 21, email = '[email protected]';

この例では、users テーブルに 山田太郎 という名前のレコードが存在する場合、年齢を 21 に、メールアドレスを [email protected] に更新します。

VALUES() 関数

INSERT INTO users (name, age)
VALUES ('山田太郎', VALUES(age) + 1)
ON DUPLICATE KEY UPDATE age = VALUES(age) + 1;

注意点

ON DUPLICATE KEY UPDATE 句は、UNIQUE キーまたは PRIMARY キーにのみ適用されます。
重複キーが存在するかどうかは、インデックスによって決定されます。
VALUES() 関数は、INSERT ステートメント内でのみ使用できます。

上記は基本的な説明です。詳細については、MySQL のドキュメントを参照してください。

INSERT INTO users (name, age, email)
VALUES ('山田太郎', 20, '[email protected]'),
       ('佐藤花子', 21, '[email protected]')
ON DUPLICATE KEY UPDATE age = age + 1, email = CONCAT(email, '_updated');

このコードは、users テーブルに 2 つのレコードを挿入します。レコードが存在する場合は、年齢を 1 加算し、メールアドレスに _updated を追加します。

INSERT INTO products (name, price, stock)
VALUES ('商品1', 1000, 10),
       ('商品2', 2000, 20)
ON DUPLICATE KEY UPDATE price = price * 1.1, stock = stock - 1;

-- 商品1の価格を10%増額し、在庫を1減らす
INSERT INTO products (name, price, stock)
VALUES ('商品1', NULL, NULL)
ON DUPLICATE KEY UPDATE price = price * 1.1, stock = stock - 1;

-- 商品2の在庫数を取得
SELECT stock FROM products WHERE name = '商品2';

2 番目の INSERT ステートメントでは、name 以外を NULL にすることで、price と stock のみを更新します。

3 番目の SELECT ステートメントは、商品2 の在庫数を取得します。

主キーとユニークキー

-- 主キー
CREATE TABLE users (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(255) NOT NULL,
  age INT NOT NULL
);

-- ユニークキー
CREATE TABLE products (
  name VARCHAR(255) UNIQUE,
  price INT NOT NULL,
  stock INT NOT NULL
);

このコードは、主キーとユニークキーを持つテーブルを作成します。

users テーブル: 主キーは id
products テーブル: ユニークキーは name

その他

上記は基本的なサンプルです。
INSERT ステートメントに IGNORE キーワードを指定すると、重複キーの場合は何も行われません。

INSERT INTO .. ON DUPLICATE KEY UPDATE の代替方法

REPLACE INTO は、レコードを挿入または置き換える構文です。レコードが存在する場合は、新しい値で置き換えられます。

REPLACE INTO users (name, age, email)
VALUES ('山田太郎', 20, '[email protected]');

INSERT IGNORE は、レコードを挿入しようとしますが、重複キーの場合はエラーを発生させずに無視します。

INSERT IGNORE INTO users (name, age, email)
VALUES ('山田太郎', 20, '[email protected]');

SELECT .. INTO OUTFILE は、テーブルのデータをファイルに書き出す構文です。

SELECT * FROM users INTO OUTFILE '/tmp/users.csv';

このコードは、users テーブルのデータを /tmp/users.csv ファイルに書き出します。

LOAD DATA INFILE は、ファイルからデータをテーブルに読み込む構文です。

LOAD DATA INFILE '/tmp/users.csv' INTO TABLE users;

このコードは、/tmp/users.csv ファイルのデータを users テーブルに読み込みます。

アプリケーションロジックを使用して、レコードを挿入または更新することもできます。

def insert_or_update_user(name, age, email):
  """
  ユーザーを挿入または更新します。
  """
  user = User.query().filter(User.name == name).get()
  if user is None:
    user = User(name=name, age=age, email=email)
    user.put()
  else:
    user.age = age
    user.email = email
    user.put()

insert_or_update_user('山田太郎', 20, '[email protected]')