MySQL: gestione delle tabelle

Link copiato negli appunti

Chi usa database avrà avuto spesso la necessità di creare, eliminare e modificare tabelle.

Quest'articolo, suddiviso in due parti, tratterà la completa gestione delle tabelle in MySql, ovvero i comandi SQL per creare, eliminare e alterare tabelle, le tipologie di tabelle, la gestione degli indici ed altro ancora.

In questa prima parte spiegheremo come creare, eliminare e rinominare le tabelle.

Creare tabelle

Il comando CREATE TABLE e' usato su MySql (come anche su tutti i database che rispettano lo standard SQL) per definire come le tabelle devono essere create.

La sintassi del comando è la seguente:

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
[(create_definition,...)]
[table_options]

MySql supporta la possibilità di creare tabelle temporanee ovvero tabelle che esistono solo per la connessione corrente, usando la clausola TEMPORARY nell'istruzione CREATE TABLE. È importante notare che queste tabelle verranno automaticamente eliminate al momento della chiusura della connessione da parte del client, e, che nel caso di connessioni persistenti, le tabelle temporanee rimarranno visibili anche agli script che faranno uso di quelle connessione. L'istruzione CREATE TABLE supporta anche la clausola IF NOT EXIST, che come dice il nome stesso, fa si che la tabella venga creata solamente se questa non esiste e quindi evitando errori. Per creare una tabella è necessario definire l'elenco di colonne ed indici che questa conterrà.

Ogni colonna può essere strutturata come riportata qui di seguito

col_name type [NOT NULL | NULL] [DEFAULT default_value]
[AUTO_INCREMENT] [UNIQUE [KEY] | [PRIMARY] KEY]
[COMMENT 'string']

Il paramentro col_name corrisponde al nome della colonna, mentre type al tipo di dato.
Per i tipi di colonna è possibile visitare la documentazione ufficiale di MySql all'indirizzo: http://dev.mysql.com/doc/mysql/en/column-types.html

Gli indici delle tabelle sono importanti perché determinano l'efficienza e la velocità di lettura dei dati. Esistono 3 tipi di indici:

PRIMARY KEY, indici che permettono di definire quale campo o quali campi identificano in maniera univoca una riga
della tabella. Il valore (o la combinazione dei valori) può essere presente una sola volta.
UNIQUE KEY, chiavi che permettono di definire delle colonne come uniche e quindi, come per le PRIMARY KEY, i valori non possono ripetersi.
INDEX KEY, indici che permettono di eseguire ricerche più veloci sulla colonna o sulle colonne specificate.

È anche possibile definire una colonna che appartiene alla PRIMARY KEY come AUTO INCREMENT ottenendo quindi che la colonna contenga un valore progressivo che aumenterà all'inserimento di ogni riga e che sarà univoco per quella riga.

MySql supporta svariati tipi di tabelle, le più importanti sono:

MyIsam, usati di default in MySql per le loro caratteristiche di efficienza e compattezza
INNODB, utili per le feature che mettono a disposizione, ovvero le transazioni e le chiavi esterne
HEAP, o Memory, che, per via della propria natura, permettono l'esecuzione velocissima delle istruzioni SQL, infatti questo tipo di tabelle sono interamente gestite in memoria. È importante ricordare che queste tabelle vengono svuotate al riavvio di MySql, quindi possono contenere soltanto dati volatili

CREATE TABLE libri (
id INT(10) NOT NULL AUTO_INCREMENT,
autore VARCHAR(255) NOT NULL,
titolo VARCHAR(255) NOT NULL,
costo FLOAT(10,2) NOT NULL,
PRIMARY KEY (id)
) TYPE=MyISAM

Con quest'istruzione verrà creata una tabella di nome libri contenente 4 campi, id, autore, titolo e costo, e il campo id impostato come chiave primaria autoincrement.

Eliminare tabelle

Eliminare tabelle su MySql è estremamente semplice, è infatti possibile usare per lo scopo il comando DROP TABLE.

DROP [TEMPORARY] TABLE [IF EXISTS]
tbl_name [, tbl_name] ...

Com'è possibile vedere anche questo comando supporta la clausola IF EXISTS che permette di evitare errore in caso di tabella mancante. A partire di MySql 4.1 è possibile usare la parola chiave TEMPORARY per eliminare le tabelle temporanee.

Il parametro tbl_name corrisponde al nome della tabella che si vuole eliminare. Si possono anche eliminare più tabelle con un sol comando: è sufficente separarne i nomi con una virgola.

DROP TABLE tbl1, tbl2, tbl3

Con l'esempio sopra riportato si eliminerebberò 3 tabelle, tbl1, tbl2 e tbl3, con una sola istruzione SQL.

Rinominare tabelle

Come per l'eliminazione, rinominare una tabella è estremamente semplice:

RENAME TABLE tbl_name TO new_tbl_name
[, tbl_name2 TO new_tbl_name2] ...

Il parametro tbl_name corrisponde al nome della tabella che si deve rinominare mentre new_tbl_name al nuovo nome.
Anche con questo comando è possibile rinominare più tabelle contemporaneamente separando semplicemente questi blocchi di nomi con una virgola.

RENAME TABLE tbl1 TO tmp_tbl1,
tbl2 TO tbl1,
tmp_tbl1 TO tbl2;

Se ad esempio si volessero scambiare i nomi di due tabelle, tbl1 e tbl2, basterebbe lanciare il comando RENAME TABLE facendogli eseguire 3 scambi, utilizzando un nome temporaneo per una delle due tabelle e poi rinominando le tabelle per come serve.

Approfondimenti

Per chi volesse avere informazioni più dettagliate sull'argomento può andare sul manuale ufficiale di MySql a quest'indirizzi:

Cosa sono gli Indici

Gli indici sono la parte più importante della tabella, ne influenzano la dimensione e determinano la velocità di ricerca e quella di inserimento, di modifica ed eliminazione.

Un indice non è altro che un elenco di valori (o record) che indicano al database, in questo caso a MySql, dove si trova la riga che contiene ciò che cerchiamo: in parole semplici, facendo ricerche su campi impostati come indici la velocità delle query aumenta notevolmente ma, al tempo stesso, rallenta quando questi dati devono essere inseriti, modificati o eliminati.

Troppi indici causerebbero un eccessivo rallentamento nelle operazioni di modifica dell'elenco, troppo pochi comporterebbero un rallentamento eccessivo nella ricerca dei dati, che molto spesso avviene con maggiore frequenza, ed è quindi fondamentale impostarli bene per ottenere una tabella che funzioni bene.

Il tipo di indice usato influenza notevolmente la velocità delle operazioni, infatti un indice di tipo unico permette a MySql di recuperare i dati più velocemente di un indice normale perché l'indice unico assicura al database che può esserci un solo record associato a quel valore (la cosa cambia se nella ricerca è usato LIKE al posto dell'operatore.

Come detto nella prima parte esistono 3 tipi di indici:

PRIMARY KEY, chiamate anche PK, che, oltre ad essere chiavi uniche, forzano il database a organizzare i dati in base ai valori contenuti nelle colonne usate nell'indice
UNIQUE KEY, come dice il nome, chiavi uniche
INDEX KEY, chiavi senza nessun attributo speciale

Da notare che usando le Primary Key è possibile definire una ed una sola colonna della tabella, impostata come Primary Key, come Auto Increment, ovvero un valore che si incrementa da solo (il tipo del campo deve essere numerico). Inoltre gli indici possono essere applicati a più colonne contemporaneamente. In questo caso gli attributi dell'indice varranno per l'insieme delle colonne usate.

È estremamente consigliato, quando si fanno inserimenti in massa, di rimuovere e poi riaggiungere, al termine dell'operazione, gli indici della tabella in modo da far lavorare di meno MySql: mentre con un inserimento di massa vi sono svariate istruzioni INSERT che vengono eseguite separatamente e quindi gli indici vengono aggiornati ad ogni esecuzione, l'eliminazione e la successiva riaggiunta degli indici forzerà il database a ricreare l'elenco una sola volta.

Gestione degli Indici

La gestione degli indici può essere fatta in svariati modi:

Al momento della creazione della tabella
Tramite il comando sql ALTER TABLE, del quale parleremo nel paragrafo successivo

Inoltre è anche possibile utilizzare degli alias speciali di ALTER che permettono l'inserimento dell'indice senza ricorrere a quest'ultimo, ovvero:

CREATE INDEX, crea un indice
DROP INDEX, elimina un indice

Le rispettive sintassi sono riportate di seguito:

CREATE [UNIQUE] INDEX index_name
ON tbl_name (index_col_name,...)

index_col_name:
col_name [(length)]

Com'è possibile vedere, è indispensabile dare un nome all'indice, tramite il parametro index_name, ed è necessario definire la tabella sul quale questo verrà creato. Inoltre è anche necessario definire l'elenco delle colonne che appartengono all'indice.

Per le colonne di tipo VARCHAR e CHAR nell'indice è possibile usare anche il parametro lenght in modo da indicizzarne solo una parte, mentre per i tipi BLOB, TEXT e simili è obbligatorio specificarne la lunghezza.

DROP INDEX index_name ON tbl_name

La sintassi del comando DROP INDEX è molto più semplice, infatti richiede soltanto il nome dell'indice da eliminare ed il nome della tabella alla quale appartiene l'indice.

Il comando ALTER TABLE

Questo comando, come suggerisce il nome, permette l'alterazione, ovvero la modifica, delle tabelle. Tramite il comando ALTER TABLE è infatti possibile non soltanto gestire le informazioni sulla tabella, come il tipo di archiviazione o il nome, ma anche le colonne e le chiavi. La sintassi del comando è la seguente:

ALTER [IGNORE] TABLE tbl_name
alter_specification [, alter_specification] ...

alter_specification:
ADD [COLUMN] column_definition [FIRST | AFTER col_name ]
| ADD [COLUMN] (column_definition,...)
| ADD INDEX [index_name] (index_col_name,...)
| ADD PRIMARY KEY (index_col_name,...)
| ADD UNIQUE [index_name] (index_col_name,...)
| ADD [index_name] (index_col_name,...)
| ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}
| CHANGE [COLUMN] old_col_name column_definition
[FIRST|AFTER col_name]
| DROP [COLUMN] col_name
| DROP PRIMARY KEY
| DROP INDEX index_name
| RENAME [TO] new_tbl_name
| ORDER BY col_name

(La sintassi riportata non è quella integrale. Gli interessati alla sintassi completa troveranno il link nell'ultimo paragrafo dell'articolo)

Qui di seguito saranno trattate le varie possibilità offerte da ALTER TABLE raggruppando le sintassi. Se si devono eseguire modifiche multiple della tabella non è necessario lanciare comandi separati: basta dividerli con una virgola.

Aggiunta di una o più colonne

ADD [COLUMN] column_definition [FIRST | AFTER col_name ]
ADD [COLUMN] (column_definition,...)

Queste sintassi permettono di aggiungere una colonna, e di specificarne la posizione all'interno della tabella, o di aggiungere più colonne senza specificarne la posizione. Il campo column_definition deve contenere la definizione della colonna, ed è la stessa sintassi usata nel comando CREATE TABLE.

Aggiunta di un indice

ADD [INDEX] [index_name] (index_col_name,...)
ADD PRIMARY KEY (index_col_name,...)
ADD UNIQUE [index_name] (index_col_name,...)

Usando questa tipologia di comandi è possibile aggiungere degli indici alla tabella specificando l'elenco di campi ad esso associati. Si possono creare tutti i tipi di indici supportati da MySql, ovvero PRIMARY KEY, UNIQUE e INDEX.

Modifica di una colonna

ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}
CHANGE [COLUMN] old_col_name column_definition
[FIRST|AFTER col_name]

ALTER e CHANGE permettono di modificare le propietà di una colonna. Il primo consente di modificare o eliminare il valore di default, mentre il secondo consente di cambiare tutte le propietà compreso il valore di default.

Eliminazione di indici e colonne

DROP [COLUMN] col_name
DROP PRIMARY KEY
DROP INDEX index_name

I comandi sopra riportati permettono, rispettivamente, l'eliminazione di:

Colonne
Chiavi primarie
Indici (sia unici che normali)

Per eliminare la PRIMARY KEY è presente un comando apposito essendo questa unica nella tabella, mentre il comando DROP INDEX va usato per eliminare tutti gli altri tipi di indici.

Rinominare la tabella

RENAME [TO] new_tbl_name

La sintassi RENAME cambia il nome alla tabella.

Ordinare la tabella

ORDER BY col_name

Questo comando permette di cambiare l'ordinamento di default di una tabella in base alla colonna specificata in col_name.

Controllare e Riparare una tabella

A volte può succedere che una tabella si danneggi a seguito di un errore o di un'improvvisa interruzione del servizio. MySql, come anche altri database, mette a disposizione una serie di comandi per verificare la struttura delle tabelle ed eventualmente ripararle. Questi comandi sono estremamente semplici, infatti richiedono solo il nome della tabella e l'accuratezza del controllo o della riparazione.

CHECK TABLE tbl_name {QUICK | FAST | CHANGED | MEDIUM | EXTENDED}

Le modalità di verifica sono le seguenti:

QUICK, esegue un controllo estremamente veloce, non controlla la correttezza delle struttura dei record
FAST, verifica solamente se la tabella è stata chiusa correttamente
CHANGED, modalità più accurata della FAST
MEDIUM, controlla il contenuto della tabella, ma non in maniera accurata
EXTENDED, controlla il contenuto della tabella, la struttura, gli indici e quant'altro è possibile controllare

È importante eseguire un controllo di tipo EXTENDED poiché il risultato è accurato al 100%, anche se questo può richiedere svariato tempo.

Nel caso che CHECK TABLE riporti una tabella come danneggiata è sufficente eseguire il comando REPAIR TABLE per riparare la tabella.

REPAIR TABLE tbl_name [, tbl_name] ... [QUICK] [EXTENDED]

Esistono due modalità di riparazione e sono:

QUICK, vengono ricostruiti solamente gli indici
EXTENDED, il comando ricostruisce la tabella riga per riga

La seconda modalità è necessaria solo in casi critici: l'operazione di riparazione, se la tabella è grande, potrebbe impiegare molto tempo.

Ottimizzare le tabelle

MySql, tra le svariate funzionalità che mette a disposizione, permette anche di ottimizzare le tabelle. L'ottimizzazione delle tabelle consiste nell'eliminazione dello spazio vuoto che si viene a creare tra i record dopo operazioni di modifica ed eliminazione.

OPTIMIZE TABLE tbl_name [, tbl_name] ...

Il parametro tbl_name contiene semplicemente il nome della tabella. È possibile definire più tabelle separando i nomi tramite delle virgole.

Comunque non è sempre necessario eseguire l'ottimizzazione su tutte le tabelle, infatti in automatico le righe eliminate, e quindi lo spazio vuoto, viene riusato scrivendo i nuovi record inseriti nella tabella. È però importanto farlo quando vengono eliminate grosse quantità di dati o viene sensibilmente alterata la struttura della tabella.

Backup e Ripristini

È possibile anche eseguire dei backup integrali tramite comandi sql. Non è un metodo molto consigliato ma risulta estremamente utile in situazioni particolari.

BACKUP TABLE tbl_name [, tbl_name] ... TO '/path/to/backup/directory'

È possibile definire più tabelle da salvare ed è necessario specificare il percorso nel quale queste verrano salvate. Se si volesserò salvare più tabelle contemporaneamente, è conveniente usare il comando LOCK per bloccare tutte le tabelle interessate in modo da evitare che durante il backup su alcune venga scritto e si perdano dei riferimenti.

Attenzione: questo comando scrive direttamente su disco ed è quindi necessario avere il permesso file e aver impostato i permessi corretti alla cartella di destinazione, altrimenti si potrebberò verificare degli errori!

Ripristinare è altrettanto semplice:

RESTORE TABLE tbl_name [, tbl_name] ... FROM '/path/to/backup/directory'

La sintassi è identica a BACKUP TABLE. L'operazione di ripristino, a differenza di quella di backup, che si limita a copiare i file delle tabelle, ricostruisce anche gli indici, quindi su database di grandi dimensioni potrebbe impiegare molto tempo.

Per chi volesse avere informazioni più dettagliate sull'argomento si rimanda al manuale ufficiale di MySql.

Indice lezioni