Visualizzare ed eliminare foreign keys in MySQL

 13/10/2013

MySQL

Con l’avvento di InnoDB su MySQLci si trova sempre più spesso a dover trattare tabelle con chiavi esterne. A volte per modificare una tabella si rende indispensabile eliminare le chiavi prima di eseguire la modifica, chiavi che però non sono elencate insieme alla struttura della tabella. Per estrarre quindi la lista delle chiavi di una tabella la query da eseguire è quindi la seguente:

select * from KEY_COLUMN_USAGE WHERE TABLE_SCHEMA = '<em>database_name</em>' AND TABLE_NAME = '<em>table_name</em>'

Come si può intuire, al posto di database_name si dovrà inserire il nome del database in cui risiede la tabella, e al posto di table_namesi dovrà scrivere il nome della tabella dalla quale estrarre le chiavi.
Una volta ottenuta la lista di foreign key, per elimare una chiave il codice sql da eseguire è:

ALTER TABLE <em>table_name</em> DROP FOREIGN KEY <em>foreign_key_name</em>

Table_name è come sopra il nome della tabella in cui risiedono le chiavi esterne, mentre foreign_key_name è il nome della chiave ottenuto dalla lista estratta con la query precedente.

Differenze tra il tipo char e il varchar in MySQL

 30/05/2010

Prosegue il mio lavoro insieme a MySQL, ormai lo sto usando intensamente in molti programmi non solo in php, ma anche nella programmazione in java.

Usandolo così massicciamente sto continuando a scoprire cose interessanti. Probabilmente quella di cui vi parlo oggi, non è una novità per qualcuno, ma credo sia bene chiarirla per chi non lo sapesse.

Qualche giorno fa stavo lavorando su un database che non ho creato io e mi sono imbattuto in un campo CHAR. Subito non ci ho fatto caso, ma poi mi è venuto in mente che di solito quando devo dichiarare un campo stringa, uso il tipo VARCHAR. Mi sono così documentato un po’, cercando su google ho trovato diverse spiegazioni su forum, ma come sapete di quello che viene detto sui forum è sempre meglio non fidarsi troppo. Ho fatto quindi direttamente riferimento alla guida ufficiale MySQL (in inglese) e ho scoperto quello che mi interessava.

Innanzitutto prima di MySQL 5.0.3 sia il tipo char sia il varchar potevano essere dimensionati da 0 a 255 caratteri. Da MySQL 5.0.3 in poi il tipo varchar supporta lunghezza da 0 a 65535 caratteri. Oltre a questa differenza di dimensionamento c’è un’altra sostanziale differenza: quando dimensionate un campo con il tipo char, ad esempio char(4) verrà effettivamente occupato spazio per 4 caratteri, anche se il valore che inserirete nel campo sarà di un solo carattere. Lo spazio rimanente verrà riempito con degli spazi. A differenza del char, il varchar invece utilizza solamente lo spazio necessario, richiesto dal valore inserito nel campo più un byte (non sono riuscito a trovare il motivo, ma credo sia il famoso “tappo” in stile C).

Riepilogando quindi:

Valore CHAR(4) Spazio richiesto
 VARCHAR(4) Spazio richiesto
'' '    ' 4 bytes '' 1 byte
'ab' 'ab  ' 4 bytes 'ab' 3 bytes
'abcd' 'abcd' 4 bytes 'abcd' 5 bytes
'abcdefgh' 'abcd' 4 bytes 'abcd' 5 bytes

Per finire, come si può vedere nell’ultima riga dell’esempio, per tutti e due i tipi, se il valore supera la lunghezza effettiva dichiarata, esso verrà troncato.

In sostanza se sapete che nel campo che state dichiarando dovrete inserire valori sempre della stessa lunghezza, senza eccezioni, potrebbe essere intelligente dichiararlo char mentre invece se, come succede nella stragrande maggioranza dei casi, il campo che state dichiarando conterrà stringhe generiche, allora vi converrà utilizzare il tipo varchar.

Un tool open source per la progettazione dei database

 24/12/2009

Ogni buon programmatore che si rispetti sa che non si può creare un database dal nulla. Proprio i database sono alla base di qualsiasi software degno di nota, e sono indispensabili per la realizzazione di un sito web.

Da buon fanatico di progetti open source, ma soprattutto dell’ambiente php-mysql per la creazione di web application mi sono dato da fare nel mio sport preferito e ho trovato un tool davvero interessante.

Si chiama MySQL workbench e proviene dagli stessi laboratori dai quali ci viene fornito uno dei DBMS open source più conosciuti al mondo. E’ da qualche mese che lo uso ogni volta in cui ho bisogno di realizzare un db, a lavoro o a casa e lo trovo davvero utile e funzionale. Che cos’è workbench? Semplice! Un sw che consente di progettare un database, partendo dalla realizzazione dello schema E-R e risparmiando molto lavoro a noi poveri programmatori.

In sostanza basta disegnare lo schema del database inserendo tabelle, chiavi primarie, chiavi esterne, indici, trigger, viste e chi più ne ha più ne metta, e workbench farà per noi tutto il resto. Una volta disegnato lo schema infatti, sarà possibile esportare la struttura del db creato e reimportarla nel nostro DBMS. Io uso abitualmente MySQL e workbench mi permette di esportare in un file .sql le query di creazione della mia base dati, pienamente compatibili con questo DBMS, e di creare con una import il database vero e proprio.

Esiste in due versioni, la community e la standard, la prima rilasciata gratuitamente sotto licenza GPL mentre la seconda a pagamento (e con alcune funzionalità in più) al costo di 79$ per svillupatore per anno. Ovviamente non mi sono potuto permettere di acquistare la versione standard, ma la community svolge il suo egregio lavoro in maniera impeccabile.

Dimenticavo, workbench è multipiattaforma ossia esiste nella versione per Linux (comodo pacchetto debian autoinstallante, o rpm), nella versione per Mac OS X e nella versione per Windows in modo che nessuno possa lamentarsi e tutti possano progettare in tutta serenità i propri database sul proprio sistema preferito.

Direi che non vi resta che provarlo!
[ad]

Indici Full Text in MySQL

 23/10/2009

Post da nerd, astenersi perditempo… :-P

[NERD MODE ON]

Alcuni giorni fa sul lavoro mi sono cimentato in un nuovo esperimento.

Tutto è partito dal problema di dover indicizzare dei contenuti, sui quali poi eseguira una ricerca “generica” in stile google (ovvero un unico campo che riceve un insieme di parole, che possono corrispondere a diversi campi del db).

Bene non voglio stare qui a spiegare nei dettagli la soluzione che abbiamo deciso di adottare, ma in sostanza mi sono ritrovato con un campo testuale con dentro tutte le parole che corrispondono ad una chiave.

Ok, e ora? L’idea iniziale è stata quella di eseguire una query con ” LIKE ‘%parola%’ ” in OR con altre LIKE simili, per ogni parola inserita dall’utente. Una volta implementata, tutto funzionava (avevo 3 record in quella tabella). Siccome per scrupolo avevo anche implementato un timer che misurasse il tempo di esecuzione, ho provato a fare un test pesante… Con python mi sono creato un programma per scrivere su file qualche centinaia di query di inserimento su quella tabella, lo eseguo e popolo il db. Ora ci sono 1000 record (non sono ancora abbastanza ma rende già l’idea), rilancio la ricerca e il timer segna 0,3 secondi. Buono no? sì ma sono 1000 record, se mai i record dovessero essere 100K o ancora di più? Mmm non mi soddisfa questa cosa. *Googleing*….*Googleing*….*Googleing* FULL TEXT INDEX

Cos’è? In MySQL è possibile definire su un campo di una tabella, un indice FULL TEXT, ovvero una struttura che indicizza in maniera ottimale tutte le parole presenti in uno o più campi. Ci sono alcune restrizioni però. Quella più importante è che la tabella non può essere creata con il motore InnoDB (il più comune perchè supporta query multiple in una singola transazione). Inoltre le stringhe da cercare con le query dovranno avere più di 3 caratteri (altrimenti bisogna andare a modificare le impostazioni di MySQL, cosa impossibile se non si ha accesso al server, e comunque relativamente complicata da fare).

Detto ciò i passi da fare sono i seguenti:
Definiamo la tabella e l’indice:

CREATE TABLE test (
id INT UNSIGNED   AUTO_INCREMENT NOT NULL PRIMARY KEY,
keywords TEXT,
[keywords2] VARCHAR(255),
FULLTEXT (keywords[,keywords2])
);

[Tra le parentesi quadre ho messo il secondo campo, spero si capisca che è opzionale…]

Fatto questo e dopo aver popolato la tabella, possiamo provare la nostra ricerca con la seguente query:

SELECT * FROM test WHERE MATCH(keywords) AGAINST('pippo')

In questo modo andremo a dire a MySQL di cercarci l’esatta parola ‘pippo’ all’interno del campo keywords.

Una cosa che non si trova facilmente nelle guide che ho seguito (ho fatto perciò riferimento alla documentazione ufficiale di MySQL) è che così facendo si effettua una ricerca in linguaggio naturale, che è ottimale per indicizzare frasi di senso (più o meno) compiuto. Il “difetto” se può definirsi così, è che con questo tipo di ricerca alcune parole non verranno trovate, infatti MySQL utilizza un array di stopwords che verranno saltate a piè pari dalla ricerca. Per effettuare una ricerca su tutte le parole e utilizzando operatori logici (ad esempio “+” significa OR mentre “-” esclude la parole che segue questo simbolo) bisogna effettuare una ricerca IN BOOLEAN MODE, più lenta, ma più efficace. La nostra query dovrà quindi essere modificata in:

SELECT * FROM test WHERE MATCH(keywords) AGAINST('*pippo*,*pluto*' IN BOOLEAN MODE)

In questo modo si effettua una ricerca all’interno del campo keywords, delle parole che contengono al loro interno “pippo” o “pluto”. Il carattere “*” funziona un po’ come il “%” nelle LIKE.

A questo punto manca un ultima chicca. La funzione MATCH (..) AGAINST(..) ritorna un valore che rappresenta la pertinenza delle parole cercate nel campo. Nel BOOLEAN MODE sarà il numero di parole trovate nel campo, mentre con la ricerca in linguaggio naturale ci sarà un algoritmo che calcola la pertinenza con delle regole strane ma che funzionano ;)

Quindi per sfruttare al massimo questo algoritmo bisognerà scrivere la query con:

SELECT *, MATCH(keywords) AGAINST('*pippo*,*pluto*' IN BOOLEAN MODE) as pertinenza FROM test WHERE MATCH(keywords) AGAINST('*pippo*,*pluto*' IN BOOLEAN MODE) ORDER BY pertinenza DESC

Così facendo ordiniamo i risultati dal più pertinente, a quello meno.

Risultato? sui miei 1000 records il tempo si è dimezzato, nel caso peggiore… Risultato accettabile no? :-)

Non vi resta che provare per credere, buon divertimento!

[NERD MODE OFF]
[ad#ad-1]