MariaDB/データ保守用SQL（insert文）の作り方整理

はじめに

今回は、MariaDBの「insert」文だけとりあげます。
動作確認環境はWindowsのMariaDB10.3です。
イメージは複数の環境に展開しているマスターテーブルのデータ更新スクリプトです。
初期設定もあるので、全件のinsertスクリプトを用意しておく必要があるのですけど、既存環境だと重複エラーになるので差分だけ適用しないといけない・・でも、環境ごとに微妙に違うので、めんどくさい・・という状況で、いかに楽をするかがテーマです。

まずは普通のinsert文

サンプルに使うテーブルは以下のCreate文で作りました。

CREATE TABLE `test_master` (
	`id` INT(11) NOT NULL,
	`code` VARCHAR(20) NULL DEFAULT NULL,
	`name` VARCHAR(256) NULL DEFAULT NULL,
	`updated` TIMESTAMP NOT NULL,
	PRIMARY KEY (`id`) USING BTREE
);

ここにまず以下の3行をインサートします。

insert into test_master (id,code,name,updated) values (1,'t100','namet100',current_timestamp());
insert into test_master (id,code,name,updated) values (2,'t200','namet200',current_timestamp());
insert into test_master (id,code,name,updated) values (3,'t300','namet300',current_timestamp());

重複無視して追加だけinsert

上記に以下の1行を追加します。

insert into test_master (id,code,name,updated) values (4,'t400','namet400',current_timestamp());

このままだと、前の３行は重複エラーになるので、エラーを無視させるために、以下のように「IGNORE」キーワードを追加します。

insert IGNORE into test_master (id,code,name,updated) values (1,'t100','namet100',current_timestamp());
insert IGNORE into test_master (id,code,name,updated) values (2,'t200','namet200',current_timestamp());
insert IGNORE into test_master (id,code,name,updated) values (3,'t300','namet300',current_timestamp());
insert IGNORE into test_master (id,code,name,updated) values (4,'t400','namet400',current_timestamp());

こうすると重複が「Warning」になって、重複でも停止せずスキップされて、最後の追加行だけが「insert」されるようになります。

重複したら「update」したい行がある

ここで「id=2」の行だけnameを「updated_namet200」に変更しました・・というケースです。
ようするに、なければそのまま「insert」したいけど、重複する行があったら「name」だけ更新したい・・というパターンです。
2行目の「IGNORE」をとって、「ON DUPLICATE KEY UPDATE name='updated_namet200'」を追加して書き換えます。

insert into test_master (id,code,name,updated) values (2,'t200','updated_namet200',current_timestamp())ON DUPLICATE KEY UPDATE name='updated_namet200';

これによって、スクリプト全体は以下のようになります。

insert IGNORE into test_master (id,code,name,updated) values (1,'t100','namet100',current_timestamp());
insert into test_master (id,code,name,updated) values (2,'t200','updated_namet200',current_timestamp())ON DUPLICATE KEY UPDATE name='updated_namet200';
insert IGNORE into test_master (id,code,name,updated) values (3,'t300','namet300',current_timestamp());
insert IGNORE into test_master (id,code,name,updated) values (4,'t400','namet400',current_timestamp());

これでいつ流しても、Warningはでますが、なければ「insert」あれば、スキップもしくはupdateというスクリプトができました。
まあ、簡単なことですが・・。
こういう「ちょっと楽する」工夫が意外にきいてきます。
ではでは。