同じレコードがないときだけインサートする！

はじめに

あるアイテムを持っていない人だけ、別のアイテムをあげたい！
もしくはその逆で、あるアイテムを持っている人に追加でアイテムをあげたい！

そういうことってないでしょうか？
先日、僕がそのような状況になり、四苦八苦しておりました。
本日は復習をかねて、調べた内容を記述していきたいと思います。

1 はじめに
- 1.1 実行環境
2 準備
3 EXISTS / NOT EXISTS
4 INSERT … SELECT…
5 同じレコードがないときだけインサートする！

実行環境

MySQL 5.7.14

準備

まずはテスト用のテーブル、レコードを作成しておきます。
実はMySQLのリファレンスを読んだのですが、いまいちピンとこなかったので、実際に似たテーブルを作成して試してみました。

 > CREATE TABLE cities(id int(11) NOT NULL AUTO_INCREMENT, > name varchar(32) NOT NULL DEFAULT '0', PRIMARY KEY (id) > ) DEFAULT CHARSET=utf8; > > CREATE TABLE stores(id int(11) NOT NULL AUTO_INCREMENT, > name varchar(32) NOT NULL DEFAULT '0', PRIMARY KEY (id) > ) DEFAULT CHARSET=utf8; > > CREATE TABLE cities_stores(id int(11) NOT NULL AUTO_INCREMENT, > city_id int(11) NOT NULL DEFAULT 0, store_id int(11) NOT NULL DEFAULT 0, > PRIMARY KEY (id)) DEFAULT CHARSET=utf8;

> CREATE TABLE cities(id int(11) NOT NULL AUTO_INCREMENT,

> name varchar(32) NOT NULL DEFAULT '0', PRIMARY KEY (id)

> ) DEFAULT CHARSET=utf8;

> CREATE TABLE stores(id int(11) NOT NULL AUTO_INCREMENT,

> name varchar(32) NOT NULL DEFAULT '0', PRIMARY KEY (id)

> ) DEFAULT CHARSET=utf8;

> CREATE TABLE cities_stores(id int(11) NOT NULL AUTO_INCREMENT,

> city_id int(11) NOT NULL DEFAULT 0, store_id int(11) NOT NULL DEFAULT 0,

> PRIMARY KEY (id)) DEFAULT CHARSET=utf8;

 > INSERT INTO cities (id, name) VALUES (1, '渋谷'),(2, '新宿'),(3, '池袋'); > > INSERT INTO stores (id, name) VALUES (1, 'セブンイレブン'),(2, 'ファミリーマート'), > (3, 'ローソン'),(4, 'ミニストップ'),(5, 'サークルK'), (6, 'スーパー'); > > INSERT INTO cities_stores (city_id, store_id) VALUES (1, 1), (1, 2), (1, 3), > (2, 3), (2, 4), (2, 5), (3, 1), (3, 3), (3, 5);

> INSERT INTO cities (id, name) VALUES (1, '渋谷'),(2, '新宿'),(3, '池袋');

> INSERT INTO stores (id, name) VALUES (1, 'セブンイレブン'),(2, 'ファミリーマート'),

> (3, 'ローソン'),(4, 'ミニストップ'),(5, 'サークルK'), (6, 'スーパー');

> INSERT INTO cities_stores (city_id, store_id) VALUES (1, 1), (1, 2), (1, 3),

> (2, 3), (2, 4), (2, 5), (3, 1), (3, 3), (3, 5);

cities（街）、stores（店）、cities_stores（街と店の紐付け）を作成し、それぞれの街にいくつかの店があるとします。
具体的には下記の組み合わせです。

渋谷	セブン、ファミマ、ローソン
新宿	ローソン、ミニストップ、サークルK
池袋	セブン、ローソン、サークルK
どこにもない	スーパー

EXISTS / NOT EXISTS

まずは、EXIST句、NOT EXISTS句です。
リファレンスはこちらになります。
これは、その条件のものが存在していればTRUEを、存在していなければFALSEを返し、TRUEのものだけ取得してくれます。
（NOT EXIST句は逆です。）
下記に例を示しますが、storesの中で街にないものは「スーパー」だけです。

 # 1. cities_storesに存在する場合 > SELECT DISTINCT name FROM stores > WHERE EXISTS (SELECT * FROM cities_stores > WHERE cities_stores.store_id = stores.id);

# 1. cities_storesに存在する場合

> SELECT DISTINCT name FROM stores

> WHERE EXISTS (SELECT * FROM cities_stores

> WHERE cities_stores.store_id = stores.id);

セブン、ファミマ、ローソン、ミニストップ、サークルK

 # 2. cities_storesに存在しない場合 > SELECT DISTINCT name FROM stores > WHERE NOT EXISTS (SELECT * FROM cities_stores > WHERE cities_stores.store_id = stores.id);

# 2. cities_storesに存在しない場合

> SELECT DISTINCT name FROM stores

> WHERE NOT EXISTS (SELECT * FROM cities_stores

> WHERE cities_stores.store_id = stores.id);

スーパー

INSERT … SELECT…

次に、INSERT … SELECT…構文です。
リファレンスはこちらになります。
これは、SELECTで引っ張ってきた値を使用してINSERT文を作成します。
具体的には下記です。
良い例が思いつかなかったため、storesから全件取得し、それをcitiesに突っ込みます。

 > INSERT INTO cities (name) SELECT stores.name FROM stores;

1	> INSERT INTO cities (name) SELECT stores.name FROM stores;

ご想像の通り、これでまるっとcitiesのレコードが増えます。

 > SELECT * FROM cities;

1	> SELECT * FROM cities;

id	name
1	渋谷
2	新宿
3	池袋
4	セブンイレブン
5	ファミリーマート
6	ローソン
7	ミニストップ
8	サークルK
9	スーパー

同じレコードがないときだけインサートする！

ここから本題になります。
想定としては、ある街に全ての店が進出してきた！ということにします。（意味が分かりませんが笑）
当然、既に存在している店もあるので、その店はインサートしないようにしたいです。
NOT EXISTS句とINSERT … SELECT …構文を併用して下記のように書きます。
（例として、渋谷にセブンイレブンとファミリーマートがきたということにします。セブンは既に存在しています。）

 # セブンの場合、もう渋谷にあるのでインサートされない > INSERT INTO cities_stores (city_id, store_id) > SELECT target_city_id, target_store_id FROM dual > WHERE NOT EXISTS(SELECT * FROM cities_stores > WHERE city_id = 1 AND store_id = 1);

# セブンの場合、もう渋谷にあるのでインサートされない

> INSERT INTO cities_stores (city_id, store_id)

> SELECT target_city_id, target_store_id FROM dual

> WHERE NOT EXISTS(SELECT * FROM cities_stores

> WHERE city_id = 1 AND store_id = 1);

 # ファミマの場合、まだ渋谷にないのでインサートされる > INSERT INTO cities_stores (city_id, store_id) > SELECT target_city_id, target_store_id FROM dual > WHERE NOT EXISTS(SELECT * FROM cities_stores > WHERE city_id = 1 AND store_id = 2);

# ファミマの場合、まだ渋谷にないのでインサートされる

> INSERT INTO cities_stores (city_id, store_id)

> SELECT target_city_id, target_store_id FROM dual

> WHERE NOT EXISTS(SELECT * FROM cities_stores

> WHERE city_id = 1 AND store_id = 2);

dualはテーブルを参照する必要のない場合に使用するダミーテーブルで、WHERE句を指定したい場合などに入れる必要があるそうです。
MySQLリファレンス-SELECT Syntax-
私自身は特に省略しても違和感はありませんが、Oracle データベースなどを扱っていた方々は記述するのが当たり前のようです。

とりあえずSQL文はできましたが、これだとレコード数が少ない場合は良いのですが、
レコード数が多くなると面倒なので、ストアドプロシージャを使用してループ文を作りたいと思います。
ストアドプロシージャは機会があればいろいろ試して、ブログにしたいと思いますが、今回はコードだけ載せておきます。
MySQLリファレンス-CREATE PROCEDURE and CREATE FUNCTION Syntax-

 DELIMITER // CREATE PROCEDURE RegisterAllStoresWithCity(IN target_city_id INT) BEGIN DECLARE target_store_id INT; DECLARE max_id_stores INT; SET target_store_id = 1; SET max_id_stores = (SELECT MAX(id) FROM stores); WHILE target_store_id <= max_id_stores DO INSERT INTO cities_stores (city_id, store_id) SELECT target_city_id, target_store_id FROM dual WHERE NOT EXISTS(SELECT * FROM cities_stores WHERE city_id = target_city_id AND store_id = target_store_id); SET target_store_id = target_store_id + 1; end WHILE; END // DELIMITER ;

DELIMITER //

CREATE PROCEDURE RegisterAllStoresWithCity(IN target_city_id INT)

BEGIN

DECLARE target_store_id INT;

DECLARE max_id_stores INT;

SET target_store_id = 1;

SET max_id_stores = (SELECT MAX(id) FROM stores);

WHILE target_store_id <= max_id_stores DO

INSERT INTO cities_stores (city_id, store_id)

SELECT target_city_id, target_store_id FROM dual

WHERE NOT EXISTS(SELECT * FROM cities_stores

WHERE city_id = target_city_id

AND store_id = target_store_id);

SET target_store_id = target_store_id + 1;

end WHILE;

END

DELIMITER ;

これでRegisterAllStoresWithCity()が登録されたので、下記で呼び出し、インサートしてみます。

 > CALL RegisterAllStoresWithCity(1);

1	> CALL RegisterAllStoresWithCity(1);

これで、渋谷に全ての店が出店できました！

 > SELECT cs.id, c.name, s.name FROM cities_stores cs > INNER JOIN cities c ON cs.city_id = c.id > INNER JOIN stores s ON cs.store_id = s.id;

> SELECT cs.id, c.name, s.name FROM cities_stores cs

> INNER JOIN cities c ON cs.city_id = c.id

> INNER JOIN stores s ON cs.store_id = s.id;

id	cities.name	stores.name
1	渋谷	セブンイレブン
2	渋谷	ファミリーマート
3	渋谷	ローソン
25	渋谷	ミニストップ
26	渋谷	サークルK
27	渋谷	スーパー

余談ですが、登録したRegisterAllStoresWithCityをリセットするには下記コマンドになるそうです。

 > DROP PROCEDURE IF EXISTS RegisterAllStoresWithCity;

1	> DROP PROCEDURE IF EXISTS RegisterAllStoresWithCity;

日	月	火	水	木	金	土
			1	2	3	4
5	6	7	8	9	10	11
12	13	14	15	16	17	18
19	20	21	22	23	24	25
26	27	28

同じレコードがないときだけインサートする！

はじめに

実行環境

準備

EXISTS / NOT EXISTS

INSERT … SELECT…

同じレコードがないときだけインサートする！

フォロー

アプリ情報