複数列が同じ値のレコードが重複するテーブルのデータを１つだけ削除フラグをＯＦＦにしたい。
HAVING count(*) > 1 を実行したくない。count(*) を実行してパフォーマンス劣化が心配だからである。
そんなに良いパフォーマンスではないかもしれないが以下のように考えてみた。
しかし、やっぱり遅いだろう。
（サンプル）
テーブルの定義、
DELIMITER //
DROP TABLE IF EXISTS gamma
//
CREATE TABLE gamma (
id INT NOT NULL AUTO_INCREMENT
, item_name VARCHAR(20) NOT NULL
, price INT
, mount INT
, memo VARCHAR(32)
, start_at DATE
, end_at DATE
, delete_flg INT
, PRIMARY KEY (id) )
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_bin
//
DELIMITER ;
「目的」
このテーブルの中で、id と mount 以外の列の値が、重複するレコードに、delete_flg = 1 にする。
１つの組み合わせだけ、delete_flg = 0 にする。
（データは、全て delete_flg = 0 になっていたとする）
方法は、以下のようなクエリの結果で delete_flg = 1 にすべきレコードの id を求めることで、
NULL のカラムを考慮する。
SELECT
x.id
FROM
(SELECT id,
CONCAT(item_name, '-', COALESCE(price, 'NULL'), '-', COALESCE(memo, 'NULL'), '-'
, COALESCE(start_at, 'NULL'), '-', COALESCE(end_at, 'NULL'), '-', delete_flg) AS catdata
FROM gamma
WHERE delete_flg = 0) x
INNER JOIN
(SELECT MAX(id) AS id,
CONCAT(item_name, '-', COALESCE(price, 'NULL'), '-', COALESCE(memo, 'NULL'), '-'
, COALESCE(start_at, 'NULL'), '-', COALESCE(end_at, 'NULL'), '-', delete_flg) AS catdata
FROM gamma
WHERE delete_flg = 0
GROUP BY catdata
HAVING COUNT(catdata) > 1) y
ON x.catdata = y.catdata
WHERE x.id <> y.id
ストアドプロシジャで以下のように定義する。
DELIMITER //
DROP PROCEDURE IF EXISTS sp_gamma_cleansing
//
CREATE PROCEDURE sp_gamma_cleansing()
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE target_id INT;
-- カーソル定義
DECLARE c1 CURSOR FOR
SELECT x.id FROM
(SELECT id, CONCAT(item_name,'-',COALESCE(price,'NULL'),'-',COALESCE(memo,'NULL'),'-',COALESCE(start_at,'NULL')
,'-',COALESCE(end_at,'NULL'),'-',delete_flg ) AS catdata
FROM gamma WHERE delete_flg = 0) x
INNER JOIN
(SELECT MAX(id) AS id, CONCAT(item_name,'-',COALESCE(price,'NULL'),'-',COALESCE(memo,'NULL'),'-',COALESCE(start_at,'NULL')
,'-',COALESCE(end_at,'NULL'),'-',delete_flg ) AS catdata
FROM gamma WHERE delete_flg = 0
GROUP BY catdata HAVING COUNT(catdata) > 1 ) y
ON x.catdata = y.catdata
WHERE x.id <> y.id ;
-- ハンドラ宣言
DECLARE EXIT HANDLER FOR NOT FOUND SET done = 0;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
OPEN c1;
REPEAT
FETCH c1 INTO target_id;
IF NOT done THEN
UPDATE gamma SET delete_flg = 1 WHERE id = target_id;
END IF;
UNTIL done END REPEAT;
CLOSE c1;
END
//
DELIMITER ;
実行は、call sp_gamma_cleansing();
ストアド実行後の検証は、以下クエリで０件であること。
SELECT MAX(id)
, CONCAT(item_name,'-',COALESCE(price,'NULL'),'-',COALESCE(memo,'NULL'),'-',COALESCE(start_at,'NULL')
,'-',COALESCE(end_at,'NULL'),'-',delete_flg ) AS catdata
FROM gamma WHERE delete_flg = 0
GROUP BY catdata HAVING COUNT(catdata) > 1
