Le viste in MySQL

In questi giorni ho avuto a che fare con le viste in MySQL.

Detta in parole povere una view è una tabella virtuale formata dal risultato di una SELECT.

Una volta salvata una view è simile a una classica tabella, formata da righe e colonne ed è interrogata come una normale tabella.

L'utilizzo dell views porta con se diversi vantaggi:

• semplifica query complesse
• limita l'accesso ad alcuni dati che si vogliono tenere nascosti
• extra security derivata dal fatto che la view è una tabella a sola lettura; gli utenti possono solo visualizzare i dati
• ecc

Creare una view non è difficile.

La sintassi genereale è questa:

CREATE
[ALGORITHM = {MERGE | TEMPTABLE | UNDEFINED}]

VIEW [database_name]. [view_name]

AS
[SELECT statement]

Con MERGE la SELECT viene "mescolata" con l'istruzione che richiama la vista stessa.

Con TEMPTABLE la SELECT relativa alla vista viene utilizzata per creare una tabella temporanea, sulla quale viene poi eseguita l'istruzione richiesta.

Con UNDEFINED sceglierà MySQL cosa usare, ed in genere preferisce MERGE.

UNDEFINED è l'impostazione di default.

Come nome potete impostare sia il nome col prefisso del nome del db, che senza.

Infine abbiamo l'istruzione SELECT che segue alcune regole:

• non possono essere incluse subquery
• non possono essere usate variabili
• non possono essere usati prepared statement
• non possono essere usate altre view o tabella temporenee
• non possono essere associate a triggers

Vi mostro la mia view:

CREATE VIEW bookv

AS 

SELECT name, author_name, editor_name, price, isbn, note

FROM book

INNER JOIN author ON book.author_id=author.author_id

INNER JOIN editor ON book.editor_id=editor.editor_id;

Come potete vedere nulla di complicato e se andate a controllare le tabelle del vostro db comparirà anche la view:

mysql> show tables;
+-------------------+
| Tables_in_library |
+-------------------+
| author            |
| book              |
| bookv             |
| editor            |
+-------------------+

A questo punto vi basterà interrogare la view per recuperare quei dati:

SELECT * FROM bookv;

VISTE AGGIORNABILI

In verità ho scoperto che è possibile anche creare views aggiornabili.

La SELECT devo però soddisfare queste regole:

• SELECT non può riferirsi a più tabelle (quindi niente JOIN, UNION o altri FROM)
• non può usare GROUP BY o HAVING
• non può usare DISTINCT
• non può riferirsi a una view non aggiornabile
• non può contenere espressioni
• non può essere creata usando l'algoritmo TEMPTABLE (ma di questo non sono sicuro al 100%)

In pratica deve essere una semplice SELECT:

CREATE VIEW bookv_aggiornabile

AS

SELECT name, author, editor FROM book

A questo punto se vogliamo fare l'UPDATE della tabella:

UPDATE bookv SET name = 'ciao' WHERE id = 6;

Infine vi posto qualche altro comando utile.

Se volete vedere la vista che avete creato:

SHOW CREATE VIEW vostra_vista;

Se volete modificarla:

ALTER
[ALGORITHM = {MERGE | TEMPTABLE | UNDEFINED}]
VIEW [database_name]. [view_name]
AS
[SELECT statement]

ALTER VIEW bookv

AS

SELECT book_id, name, author_name, editor_name, price, isbn, note FROM book

INNER JOIN author ON book.author_id=author.author_id

INNER JOIN editor ON book.editor_id=editor.editor_id;

Nel mio caso ho aggiunto una colonna alla view.

In pratica si tratta di riscrivere l'istruzione SELECT.

Se volete rimuovere una view:

DROP VIEW IF EXIST vostra_view