ユニークキーとか主キーをはり忘れて、データが重複したのを、ストアドプロシージャで一意にした

はじめに

趣味コードで問題起きたので、丁寧になおしてみました。

環境

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設計大事！！

ソースコード