もしくはその逆で、あるアイテムを持っている人に追加でアイテムをあげたい！
そういうことってないでしょうか？
先日、僕がそのような状況になり、四苦八苦しておりました。
本日は復習をかねて、調べた内容を記述していきたいと思います。
内容
本日は主に下記の基本的なところを書きたいと思います。
- EXISTS / NOT EXISTS
- INSERT ... SELECT...
- 上記2つを合わせて
準備
まずはテスト用のテーブル、レコードを作成しておきます。
実は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;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);具体的には下記の組み合わせです。
| 渋谷 | セブン、ファミマ、ローソン |
|---|---|
| 新宿 | ローソン、ミニストップ、サークル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);| セブン、ファミマ、ローソン、ミニストップ、サークルK |
# 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;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 = 2);SELECT Syntax
私自身は特に省略しても違和感はありませんが、Oracle データベースなどを扱っていた方々は 記述するのが当たり前のようです。
とりあえずSQL文はできましたが、これだとレコード数が少ない場合は良いのですが、
レコード数が多くなると面倒なので、ストアドプロシージャを使用してループ文を作りたいと思います。
ストアドプロシージャは機会があればいろいろ試して、ブログにしたいと思いますが、 ←よく分かっていないだけ
参考資料を下記に載せておきます。
CREATE PROCEDURE and CREATE FUNCTION Syntax
はじめてのMySQL ストアドプロシージャ・ストアドファンクション
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 ;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;| id | cities.name | stores.name |
|---|---|---|
| 1 | 渋谷 | セブンイレブン |
| 2 | 渋谷 | ファミリーマート |
| 3 | 渋谷 | ローソン |
| 25 | 渋谷 | ミニストップ |
| 26 | 渋谷 | サークルK |
| 27 | 渋谷 | スーパー |
DROP PROCEDURE IF EXISTS RegisterAllStoresWithCity;
投稿
