はじめに
趣味コードで問題起きたので、丁寧になおしてみました。
環境
Mac OS Mojave
MySQL 5.7
Docker 18.09.1
Docker compose 1.23.2
dockerを使って、MySQLをたてる
この記事
（ https://qiita.com/Manatee/items/58d0f98a15656ed65136 ）
を参考にして、MySQLをたてる
構成
stored_procedure_test/
docker/
| ├ cond.d/
| | └ my.cnf
| ├initdb.d/
| | ├ schema.sql
| | └ testdata.sql
| └ Dockerfile
└docker-compose.yml
stored_procedure_test の直下で以下を実行する
Terminal
$ docker-compose up -d
dockerのMySQLに入って、データを確認する
Terminal
$ docker exec -it stored_procedure_test_db_1 bash
$ mysql -uuser -ppassword
#ここから MySQL内
mysql> use stored_procedure_db;
Database changed
mysql> show tables;
+-------------------------------+
| Tables_in_stored_procedure_db |
+-------------------------------+
| user_tweet_relations |
+-------------------------------+
1 row in set (0.00 sec)
今回起きた課題
フロント側で重複が起きないように担保されていて、今まで問題がなかったのですが、フロントの不具合で
お気に入りみたいなリレーションテーブルにユニークキーとか主キー貼り忘れて、まったく同じデータが２つはいった。
こんなイメージ。
Terminal
mysql> select * from user_tweet_relations;
+---------+----------+---------------------+
| user_id | tweet_id | created_date |
+---------+----------+---------------------+
| 4 | 8464 | 2019-07-05 16:59:05 |
| 4 | 8464 | 2019-07-05 16:59:05 |
| 4 | 8219 | 2019-07-05 12:43:11 |
| 4 | 8219 | 2019-07-05 12:43:11 |
| 4 | 8274 | 2019-07-05 12:41:29 |
・
・
・
解決方法
重複しているデータを(user_id と tweet_id)のペアによって抽出後、各ペアに対して、重複分を削除。
これをストアドプロシージャで行った。
ストアドプロシージャとは
DB上での一連処理を定義して、関数みたいな感じで呼び出して使用するもの。
SQLは言語側のライブラリとかから使うことが多いと思うのですが、カーソルあてて、開いてとかしたので、今回使ってみました。
実際のコード
プロシージャの作成部分のみで、残りはgitを参考にしてください
（https://github.com/HHajimeW/stored_procedure_test）
stored_procedure.sql
-- delimiterを変更
delimiter //
create procedure sample_procedure(inout total_delete_num int)
begin
-- 削除するユーザIDとツイートIDを保存するようの変数を用意
declare _user_id int;
declare _tweet_id int;
declare done int;
declare duplicated_num int;
-- 重複するデータにカーソルをあてる
declare duplicated_list cursor for
select distinct
user_id, tweet_id
from
user_tweet_relations
where
(user_id, tweet_id)
in
(
select
user_id, tweet_id
from
user_tweet_relations
group by
user_id, tweet_id
having count(user_id) >= 2 and count(tweet_id) >= 2
);
declare exit handler for not found set done = 0;
set done = 1;
-- カーソルをひらく
open duplicated_list;
-- リストをとってきて、変数にいれる
fetch duplicated_list into _user_id, _tweet_id;
-- 削除を繰り返しで実行
while done do
select count(*) into duplicated_num from user_tweet_relations where user_id = _user_id and tweet_id = _tweet_id;
set duplicated_num = duplicated_num - 1;
delete from user_tweet_relations
where
user_id = _user_id and tweet_id = _tweet_id
limit duplicated_num;
set total_delete_num = total_delete_num + duplicated_num;
fetch duplicated_list into _user_id, _tweet_id;
end while;
end
//
実行結果
全体データ数：
count(user_id)
22
重複データ数：
@print_num
4
user_id tweet_id
4 8464
4 8219
4 8456
4 7909
削除した数：
@delete_num
4
全体データ数：
count(user_id)
18
おわりに
こういうことあまり起きないと思うのですが、誰かの役に立ったらいいな笑
丁寧なDB設計大事！！