SQLでの条件付きInsert

追記@2014年7月23日

無知でした。。。こういうことがしたいなら、UNIQUE 制約 を使えばよいのです。。。テーブル作成時に、unique (field1, field2, …)とすれば良いだけです。

sqlite> create table test2 (ipaddr text, port integer, unique (ipaddr, port));
sqlite> insert into test2 (ipaddr, port) values ("192.168.0.1", 6379);
sqlite> insert into test2 (ipaddr, port) values ("192.168.0.2", 6379);
sqlite> insert into test2 (ipaddr, port) values ("192.168.0.3", 6379);
sqlite> insert into test2 (ipaddr, port) values ("192.168.0.1", 6380);
sqlite> insert into test2 (ipaddr, port) values ("192.168.0.1", 6381);
sqlite> insert into test2 (ipaddr, port) values ("192.168.0.1", 6382);
sqlite> select * from test2;
192.168.0.1|6379
192.168.0.2|6379
192.168.0.3|6379
192.168.0.1|6380
192.168.0.1|6381
192.168.0.1|6382

# ↑にあると同じものをinsertしてエラーが出るか確かめる
sqlite> insert into test2 (ipaddr, port) values ("192.168.0.1", 6379);
Error: UNIQUE constraint failed: test2.ipaddr, test2.port

↑はい、狙った通りの動作をしてくれました。

無知を晒した感じになりましたが、反省してこのエントリは削除せずこのままにしておきます。



条件をつけたInsertが出来ないかと悩んでいました。

具体的には、あるテーブルにIPアドレスとポートのフィールドがあるとします。
そこに新しくIPアドレスとポートを挿入したいのですが、条件として、既存のレコードと重複しない、つまり、IPアドレスが異なるかポートが異なるかのどちらかのレコードであれば挿入して良いものとします。

つまり、
—–
192.168.0.1|6379
192.168.0.1|6380
192.168.0.1|6381
—–
↑このような既存のレコードに対して、192.168.100.100|6379なら存在していないので挿入する、192.168.0.1|6380ならすでにあるので挿入しない、という風にしたいわけです。

調査した結果、insertとselectとwhere notを使えば出来ることが分かりました。
以下、SQLite3で具体的に試してみます。

$ sqlite3 test.db

# テーブルの作成
sqlite> create table test (ipaddr text, port integer);

# データの挿入
sqlite> insert into test (ipaddr, port) values ("192.168.0.1", 6379);
sqlite> insert into test (ipaddr, port) values ("192.168.0.2", 6379);
sqlite> insert into test (ipaddr, port) values ("192.168.0.3", 6379);
sqlite> insert into test (ipaddr, port) values ("192.168.0.1", 6380);
sqlite> insert into test (ipaddr, port) values ("192.168.0.1", 6381);
sqlite> insert into test (ipaddr, port) values ("192.168.0.1", 6382);

# データを全件取得
sqlite> select * from test;
192.168.0.1|6379
192.168.0.2|6379
192.168.0.3|6379
192.168.0.1|6380
192.168.0.1|6381
192.168.0.1|6382

# "192.168.100.100" と 6379というレコードを追加します。挿入できればOKです。
sqlite> insert into test (ipaddr, port) select "192.168.100.100", 6379 from test where not exists (select * from test where ipaddr = "192.168.100.100" and port = 6379) limit 1;

# ↑のレコードが挿入されていますね
sqlite> select * from test;
192.168.0.1|6379
192.168.0.2|6379
192.168.0.3|6379
192.168.0.1|6380
192.168.0.1|6381
192.168.0.1|6382
192.168.100.100|6379

# もう一度同じSQLを実行してみます。これは挿入できないはずです、、、
sqlite> insert into test (ipaddr, port) select "192.168.100.100", 6379 from test where not exists (select * from test where ipaddr = "192.168.100.100" and port = 6379) limit 1;

# ↑のレコードが挿入されていません。OKですね。
sqlite> select * from test;
192.168.0.1|6379
192.168.0.2|6379
192.168.0.3|6379
192.168.0.1|6380
192.168.0.1|6381
192.168.0.1|6382
192.168.100.100|6379

insertの中にselectが二回もあって分かりにくいですがこんな感じで条件付きinsertが出来ました。
簡単な解説ですが、

select “192.168.100.100”, 6379 from test where not exists (select * from test where ipaddr = “192.168.100.100” and port = 6379) limit 1;


↑これを実行してみれば意味が分かります。このクエリ、すでに存在している場合は何もselectされません。一方、存在していない場合はselectされます。

アプリケーション側で素直に↑のクエリを実行して重複がないか確かめてからinsertするのも手だと思いますが、一貫性が崩れる可能性があると思います。