Trovare ed eliminare duplicati

La duplicazione di dati non dovrebbe mai avvenire in un database bene organizzato, ma talvolta, quando si ha a che fare con dati ereditati da applicazioni poco accurate, può accadere di ritrovarsi con una tabella che contiene record duplicati.

In questa poco piacevole situazione, ci sono tre tipi di operazioni che si vorrebbero eseguire:

  • Elencare i record senza duplicati;
  • Elencare i valori duplicati;
  • Eliminare i duplicati.

Prendiamo ad esempio una tabella con qualche record duplicato (per esigenze di spazio, la facciamo corta, ma supponete di avere svariate migliaia di record).


mysql> SELECT * FROM condoppi;
+---+------+-----+
| i | a | b |
+---+------+-----+
| 1 | aaa | xxx |
| 2 | bbb | yyy |
| 3 | aaa | yyy |
| 4 | ccc | www |
| 5 | aaa | xxx |
| 6 | ddd | zzz |
| 7 | aaa | xxx |
| 8 | bbb | yyy |
| 9 | ccc | www |
+---+------+-----+
9 rows in set (0.18 sec)

La prima esigenza, vedere i record senza doppi, viene risolta facilmente usando la clausola DISTINCT:


mysql> SELECT DISTINCT a, b FROM condoppi;
+------+-----+
| a | b |
+------+-----+
| aaa | xxx |
| aaa | yyy |
| bbb | yyy |
| ccc | www |
| ddd | zzz |
+------+-----+
5 rows in set (0.12 sec)

Facilissimo. Basta premettere DISTINCT alla lista dei campi per avere un elenco pulito, senza duplicati.

Notate che se avessi inserito fra i campi anche la chiave primaria (campo “i”), il DBMS mi avrebbe stampato tutti i record, perché ovviamente sono differenziati grazie al campo univoco. La clausola DISTINCT è anche utile per avere un elenco dei valori distinti (appunto) per una data colonna. Per la seconda esigenza, vedere quali valori sono doppiati, non c’è formula magica immediata, ma bisogna sforzarsi un po’ di più.

Il metodo consiste nel raggruppare i valori con un conteggio, e considerare solo quelli maggiori di 1. Si noti la clausola HAVING che (a differenza di WHERE) viene valutata dopo l’operazone di raggruppamento.


mysql> SELECT a, b COUNT(*) AS quanti
FROM condoppi
GROUP BY a, b
HAVING quanti > 1;
+------+-----+--------+
| a | b | quanti |
+------+-----+--------+
| aaa | xxx | 2 |
| bbb | yyy | 2 |
| ccc | www | 2 |
+------+-----+--------+
3 rows in set (0.39 sec)

Così possiamo vedere quali sono i valori (o le combinazioni di valori) che sono registrati più di una volta.

Infine, se vogliamo cancellare dalla tabella i valori doppi, esiste un metodo molto semplice e pratico: aggiungere un indice univoco alla tabella, ma usando la clausola IGNORE, per far sì che il DBMS non si fermi al primo errore di record duplicato, ma vada avanti, di fatto saltando l’inserimento dei record con valori già inseriti.


mysql> ALTER IGNORE TABLE condoppi ADD UNIQUE KEY (a, b) ;
Query OK, 9 rows affected (0.54 sec)
Records: 9 Duplicati: 4 Avvertimenti: 0
mysql> SELECT * FROM condoppi;
+---+------+-----+
| i | a | b |
+---+------+-----+
| 1 | aaa | xxx |
| 2 | bbb | yyy |
| 3 | aaa | yyy |
| 4 | ccc | www |
| 6 | ddd | zzz |
+---+------+-----+
5 rows in set (0.02 sec)

Aggiornare più condizioni con una sola query

PW scrive che vorrebbe trovare un modo di eseguire diversi UPDATE su una tabella usando una sola query. Nella pratica, si trova ad avere record di prodotti di diverse aziende, che vuole aggiornare in maniera omogenea, ma senza dover ripetere la stessa query diverse volte.

Per esempio, vorrebbe cambiare lo sconto offerta sui libri di informatica, di finanze, di attualità, con quantità differenti per ogni categoria. Una possibilità è la seguente:

UPDATE libri
SET sconto =
CASE
WHEN sezione = 'informatica' THEN 12
WHEN sezione = 'attualita' THEN 10
WHEN sezione = 'finanze' THEN 8
ELSE sconto
END
WHERE id_editore IN (5,6,8,11);

Questa query agisce solo sui libri degli editori considerati (clausola WHERE) e al loro interno modifica il campo sconto solo nelle sezioni indicate. Per le altre sezioni, il valore viene lasciato uguale, grazie alla clausola ELSE della funzione CASE.

Comporre query di questo tipo può essere più difficile che comporre una query per ogni condizione, ma talora si guadagna in efficienza, ed è bene sapere che ci sono alternative!

Trigger su SELECT

I trigger sono aggeggi da usare con cautela. Se abusati, possono portare la vostra applicazione in ginocchio. Tuttavia, sono anche fantastici strumenti per raffinare il controllo in situazioni difficili. Secondo lo standard SQL, i trigger possono essere associati solo alle operazioni di modifica (INSERT, DELETE, UPDATE). E i SELECT, allora? Non sarebbe bello se si potesse instaurare un trigger anche per ogni SELECT?

Supponiamo di avere una tabella books e che vogliamo creare una lista personalizzata degli accessi solo per quella tabella. Ecco come si può fare:

  1. Create una tabella db_counter che conterrà il numero degli accessi, e una tabella db_log che conterrà i dettagli;
  2. Create una funzione che contenga tutte le operazioni che volete eseguire per ogni SELECT. Questa funzione deve restituire un intero positivo;
  3. Create una vista aggiornabile sulla vostra tabella, aggiungendo una clausola WHERE che usa la funzione appena creata.

Andiamo a incominciare:


-- ----------------------------------------------------
-- La tabella originale. Quella per cui vogliamo il log
-- La facciamo semplice per non appesantire l'esempio
-- ----------------------------------------------------
CREATE TABLE books (
book_id INT NOT NULL primary key,
title VARCHAR(40),
pages INT,
price DECIMAL(5,2),
author_id INT NOT NULL,
KEY (author_id)
-- ,FOREIGN KEY (author_id) REFERENCES authors (author_id) ON DELETE CASCADE
) ENGINE = InnoDB;

-- ----------------------------------------------------
-- Il contatore
-- Contiene solo una riga.
-- ----------------------------------------------------
CREATE TABLE db_counter (
id int(11) NOT NULL default '0',
counter bigint(20) default '0',
PRIMARY KEY (id)
) ENGINE=MyISAM;

-- ----------------------------------------------------
-- L'elenco di dettaglio.
-- Ogni riga contiene informazioni sull'orario ,
-- sull'operazione fatta e l'utente.
-- ----------------------------------------------------
CREATE TABLE db_log (
ts timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
user varchar(20) NOT NULL,
operation enum('select','insert','update','delete') default 'select',
KEY user_ts (user,ts),
KEY ts (ts)
) ENGINE=MyISAM;

Queste ultime due tabelle contengono le informazioni che ci servono. Prima di continuare, qualche ritocco per essere sicuri che la tabella del contatore possa contenere solo una riga:


CREATE TRIGGER counter_insert before INSERT on db_counter
for each row set new.id = 1;
CREATE TRIGGER counter_update before UPDATE on db_counter
for each row set new.id = 1;

E ora alcuni trigger per registrare le operazioni di modifica nella tabella books. (Sarebbe buffo che registriamo gli accessi in lettura e trascuriamo quelli in scrittura!)


CREATE TRIGGER book_insert after INSERT on books
for each row set @BOOKCOUNT = get_counter('insert');
CREATE TRIGGER book_delete after DELETE on books
for each row set @BOOKCOUNT = get_counter('delete');
CREATE TRIGGER book_update after UPDATE on books
for each row set @BOOKCOUNT = get_counter('update');

Siamo pronti per creare la funzione:


delimiter //
drop function if exists get_counter //
create function get_counter( oper varchar(10) )
returns int
deterministic
begin
if ( ( select count(*) from db_counter ) > 0 ) then
update db_counter set counter = counter + 1;
else
insert into db_counter (counter) values (1);
end if;
insert into db_log (ts, user, operation) values (NULL, user(), oper);
return (select counter from db_counter where id = 1);
end //
delimiter ;

Infine, creiamo la vista aggiornabile per la tabella books.


CREATE VIEW mybooks as
SELECT * from books WHERE get_counter() > 0;

Ora, usando mybooks invece di books, ogni accesso a books verrà registrato. Vediamo in azione il nostro meccanismo (notate che usiamo sempre la vista, non la tabella).


gmdesk [localhost] {gmax} (test1) > insert into mybooks values (1, 'MySQL', 1000,39.95,1);
Query OK, 1 row affected (0.02 sec)
gmdesk [localhost] {gmax} (test1) > insert into mybooks values (2, 'Programming Perl', 1095,39.95,2);
Query OK, 1 row affected (0.02 sec)


gmdesk [localhost] {gmax} (test1) > select * from db_counter;
+----+---------+
| id | counter |
+----+---------+
| 1 | 2 |
+----+---------+
1 row in set (0.00 sec)


gmdesk [localhost] {gmax} (test1) > select * from db_log;
+---------------------+----------------+-----------+
| ts | user | operation |
+---------------------+----------------+-----------+
| 2005-10-24 11:14:22 | gmax@localhost | insert |
| 2005-10-24 11:14:27 | gmax@localhost | insert |
+---------------------+----------------+-----------+
2 rows in set (0.00 sec)

Dopo due inserimenti, il contatore è a due, e la tabella dei dettagli ha due record. Proviamo con il nostro obiettivo principale, cioè una SELECT:

gmdesk [localhost] {gmax} (test1) > select count(*) from mybooks;
+----------+
| count(*) |
+----------+
| 2 |
+----------+
1 row in set (0.00 sec)

gmdesk [localhost] {gmax} (test1) > select * from db_log;
+---------------------+----------------+-----------+
| ts | user | operation |
+---------------------+----------------+-----------+
| 2005-10-24 11:17:11 | gmax@localhost | insert |
| 2005-10-24 11:17:23 | gmax@localhost | insert |
| 2005-10-24 11:18:08 | gmax@localhost | select |
+---------------------+----------------+-----------+
3 rows in set (0.01 sec)


gmdesk [localhost] {gmax} (test1) > select * from db_counter;
+----+---------+
| id | counter |
+----+---------+
| 1 | 3 |
+----+---------+
1 row in set (0.00 sec)

CVD