Chiave univoca mysql troppo lunga

Non troppo tempo fa mi sono trovato a dover convertire un database mysql da latin swedish ci al più prestante ed universale utf8 general ci.
A parte i vari problemi di conversione per via di charset salvati in maniera magari errata, mi sono ritrovato davanti ad un errore improvviso mysql:

"Specified key was too long; max key length is xxx bytes".

In buona sostanza, cercando su internet ho poi scoperto che le chiavi mysql, in questo caso una chiave univoca, possono essere lunghe massimo 1000 caratteri.
Il fatto che tutto prima del cambio di charset funzionasse è dovuto sostanzialmente al fatto che latin ed utf8 occupano diverso spazio in bytes per il salvataggio.

Comunque, cercando un po' su internet, ho per fortuna trovato la soluzione al problema della chiave mysql troppo lunga, vediamo subito di cosa si tratta.

Come risolvere il problema della chiave univoca mysql troppo lunga

Per poter evitare il terribile errore "Specified key was too long; max key length is xxx bytes" sulle chiavi mysql, si può agire sostanzialmente in tre modi.

La prima soluzione è la più semplice e forse veloce, anche se magari non sempre attuabile: bisogna ridurre la lunghezza dei campi che compongono la chiave.

Ad esempio, se un campo nome di una ipotetica tabella mysql è stato creato di tipo varchar(255), magari è il caso di ottimizzare le risorse e di farlo ad esempio varchar(100).

Se però non è possibile agire direttamente sulla lunghezza dei campi perchè sono già troppo corti, allora si può agire sulla lunghezza massima della chiave mysql generata.
Ecco un esempio di come poter fare:

ALTER TABLE `tabella` ADD UNIQUE ( campo1(50), campo2(100) );

Infine, un'altra possibile soluzione per questo antipatico errore mysql è quella di ridurre il numero di campi dell'indice univoco.
Anche in questo caso ovviamente, non è sempre possibile farlo.

A seconda dunque della versione di mysql in uso e della lunghezza massima degli indici, potrebbe essere necessario dover rivedere la logica su come sono stati costruiti i campi delle tabelle del database, o le key stesse.

MYSQL: ordinare risultati per valori campo

Una delle cose che sicuramente si impara subito quando si utilizzano i database relazionali come mysql, è come ordinare i risultati come meglio ci aggrada.
Che sia in ordine crescente o decrescente, per un campo piuttosto che due o tre, l'ordinamento dei dati presenti nelle tabelle è un requisito fondamentale per ogni app o sito che pesca i dati da un database.

Quello che però di solito non si fa, o meglio, che capita meno di dover fare, è di ordinare i risultati per valori.
In mysql infatti, è anche possibile ordinare i campi di una tabella, per uno specifico campo, ma non solo, anche per i valori contenuti in esso.
Mi spiego meglio.
Pensiamo ad esempio di avere una tabella con dentro un campo con lo stato dei record, il classico attivo, disattivo, archiviato, in lavorazione, ecc...
Se vogliamo ordinare i record presenti nella nostra tabella mysql, in ordine di stato, specificando però noi quale (ad esempio in lavorazione, attivo, disattivo), c'è una sintassi specifica che ci consente di fare ciò, vediamo subito quale.

Come ordinare i risultati di una tabella MySql per i valori contenuti in un campo

La sintassi che consente l'ordinamente dei record per valore in mysql, è la seguente:

SELECT campo FROM tabella ORDER BY find_in_set(campo,'valore1,valore2,valore3')

Tutto qui.
Grazie all'istruzione sql find_in_set, è infatti possibile ordinare per uno specifico campo, indicando i valori d'ordinamento.
Nell'esempio sopra indicato, il campo si chiama banalmente campo, ed i valori d'ordinamento sono valore1, valore2, valore3.
Ovviamente è possibile indicare quanti valori si vuole, ed accodare anche altri campi ordinati in maniera classica, con la sintassi che tutti conoscono.

Questa funzionalità di mysql è davvero potente, ma ovviamente non bisogna abusarne troppo.
Mettendo infatti troppi ordinamenti incrociati, si rischia di ritardare l'apparizione dei risultati della ricerca.
Eventualmente valutare l'ottimizzazione del db tramite gli indici.

Inserire dati da Sql Server a Mysql

Qualche tempo fa avevo scritto un post su come collegarsi da sql server a mysql, tramite la potentissima funzionalità chiamata open query.
Oggi invece vediamo come applicare quanto appreso in passato, per poter inserire dati da sql server a mysql, in maniera rapida ed indolore.

Ammettiamo ad esempio di avere una tabella su mysql che vogliamo popolare con dei dati presi su una tabella presente su sql server, ecco che quindi, oltre ad essere su database diversi, i dati delle rispettive tabelle sono anche su server diversi.
Nessun problema comunque, vediamo subito come fare.

Come inserire dati da Sql Server a Mysql

Innanzitutto, per poter fare query da un server ad un altro, bisogna prima linkare i due server, in questo caso in maniera monodirezionale, da sql server a mysql.
Tutta la sintassi e le operazioni da compiere per poter fare questo collegamento (linked server) l'ho già spiegata in questo post.

Vediamo invece la sintassi da utilizzare per inserire dati da un server ad un altro, in questo caso sql server che va a scrivere su mysql:

INSERT INTO OPENQUERY([NOME-SERVER-COLLEGATO], 'SELECT campo1,campo2 FROM nome-database.nome-tabella-destinazione') SELECT campo1,campo2 FROM tabella-origine WHERE eventuali-condizioni

Tutto molto semplice no?
Si tratta solo di usare una insert into select di sql server, una sintassi che già dovreste conoscere se smanettate da un po' con questo database.

I dati verranno dunque presi da sqlserver ed inseriti direttamente sul mysql presente su l'altro server.
Ovviamente, le tabelle d'origine e di destinazione, o meglio i campi che avete scelto di popolare, devono essere compatibili come formato, e perchè no, anche come charset impostato di default.

MYSQL: scoprire se un campo è un numero

La desiderata del giorno riguarda il database mysql e consiste nel voler sapere se un determinato campo di una tabella è di tipo numerico.
Per scoprire se un campo è un numero in mysql non esiste in realtà una specifica funzione, tuttavia è possibile ottenere questa informazione usando l'ingegno, vediamo subito di scoprire come fare.

Come scoprire se un campo è un numero in MYSQL

Per capire se un determinato campo di una tabella di un database mysql è di tipo numerico, basta usare la seguente sintassi:

SELECT campo FROM tabella WHERE campo REGEXP '^[0-9]+$';

Con le espressioni regolari infatti (regexp), è possibile utilizzare una condizione where per pescare solo le righe con specifici campi di tipo numerico, grazie alla particolare sintassi delle regexp ^[0-9]+$.

E' possibile usare questo metodo anche nella selezione dei campi, come IF.
In questo caso si può assegnare un valore diverso al risultato della query, in base al fatto che il campo analizzato sia di tipo numerico o meno.

Quindi, ricapitolando is_numeric mysql non esiste, ma per fortuna grazie alle espressioni regolari si può ottenere lo stesso il risultato atteso :)

MYSQL: Query tra tabelle su server diversi

Un'esigenza che prima o poi può capitare nella vita per chi gestisce database mysql, è quella di dover fare delle query tra tabelle presenti non solo su database differenti, ma addirittura su server diversi.
In sql server si chiamavano linked server, qui invece su mysql il loro nome è federated tables.

Vediamo subito cosa bisogna fare per poter pesare i dati su server diversi in mysql.

Come fare query tra tabelle su server diversi su mysql

Se abbiamo una tabella mysql in locale ed un'altra presente su un database collocato su un server remoto, il modo tipico per poter pescare questi dati con un'unica query sql, è quella di usare le federated tables.

Una tabella federata, se così la si può tradurre dall'inglese, è una sorta di tabella virtuale, una tabella che pesca i suoi dati direttamente da un'altra tabella presente su un altro server (ed un altro db).

Vediamo dunque come creare una federated table.
La sintassi per la creazione di queste tabelle è identica a qualsiasi altra tabella, è il solito create table infatti.
Quello che cambia è la parte finale, in cui si dice a mysql dove andare a prendere i dati.
Ecco un semplice esempio:

CREATE TABLE `tabella_federata` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `nome` varchar(64) DEFAULT NULL,
  `cognome` varchar(64) DEFAULT NULL,
  `email` varchar(128) DEFAULT NULL,
  PRIMARY KEY (`id`)
 ) ENGINE=FEDERATED DEFAULT CHARSET=utf8 CONNECTION='mysql://utente:password@ip-server/nome-database/nome-tabella'

La parte cruciale di questo esempio è l'engine, il motore della tabella, che ha una sintassi del genere:
ENGINE=FEDERATED DEFAULT CHARSET=utf8 CONNECTION='mysql://utente:password@ip-server/nome-database/nome-tabella'

Penso che non ci sia bisogno di altre spiegazioni.

Quando si vuole creare quindi una tabella federata mysql, il mio consiglio è quello di esportare lo script di creazione della tabella presente sul server remoto, e poi di modificarlo nella sua parte finale, nel motore.

Una volta creata questa tabella virtuale, pescare i dati sarà un gioco da ragazzi.
Se infatti abbiamo creato una tabella federata sullo stesso database mysql della tabella da legare, la sintassi da usare sarà un classico:
SELECT tabella1.campo,tabella2.campo FROM tabella1,tabella2 WHERE idtabella1=idtabella2
Dove ovviamente una delle due tabelle in questione è la tabella federata creata poco prima.

Il limite di questo metodo per pescare dati da server diversi in mysql è abbastanza evidente.
Se si modifica la struttura della tabella remota, bisogna ricordarsi di modificare anche quella della tabella federata... particolare non da poco!

MYSQL: come fare insert o update in un colpo solo

Quante volte in mysql ti è capitato di dover fare delle query di aggiornamento dati non sapendo se un record era già presente in una tabella oppure no?
Ecco, in questi casi solitamente ci tocca fare una bella select, e poi a seconda del risultato trovato, fare una insert o un update.

Grazie alla sintassi che ti sto per spiegare invece, è possibile fare quello che io definirei un insert update mysql in un colpo solo, ovvero verificare con un metodo alternativo la presenza di un record in una tabella, ed agire di conseguenza... il tutto in una singola query!

Come fare insert o update in un colpo solo in MYSQL

Bando alle ciance, la sintassi da utilizzare per fare una insertupdate mysql, è la seguente:

INSERT INTO tabella (campo1,campo2) VALUES ('campo1','campo2') ON DUPLICATE KEY UPDATE nome='nome', cognome='nome';

Con questa semplice riga di codice sql infatti, se in una tabella si tenta di fare un inserimento dati, se questo fallisce perchè il record esiste già, viene fatto automaticamente un aggiornamento dati.
Ovviamente bisogna quantomeno riscrivere tutti i campi da inserire/aggiornare due volte, dato che bisogna comporre comunque sia la sintassi dell'insert che della update mysql... ma questo mi sembra proprio il minor dei mali, dato che ci siamo risparmiati una select che avrebbe magari anche rallentato l'eseguzione dello script.

La domanda però ora sorge spontanea... come si fa a capire se un record è già presente in una tabella?
Semplice, con le chiavi univoce!
Bisogna infatti prima creare una chiave univoca, o indicare un campo come univoco, e poi provare l'inserimento, altrimenti verrà effettuata sempre e solo l'insert.

Se non sai come creare chiavi univoce in mysql leggi questo post.
Quello che dovrai fare è, ad esempio, creare una chiave nome+cognome per impedire l'inserimento di omonimi, e gestire l'aggiornamento in tal senso.
Se poi vuoi fare una cosa ancora più pulita, definisci come indice univoco un id della tabella, in modo tale da aggiornare la riga solo quando si sta provando realmente ad inserire un duplicato.

Bene, adesso sai come fare insert update mysql in un colpo solo.
Come al solito, a grandi poteri corrispondono grandi responsabilità (cit.).

MYSQL: query tra database diversi

Se hai alberato la struttura dei tuoi dati in database diversi, devi sapere che in mysql è possibile compiere query multidatabase, ovvero tra un database ed un altro.
Mettiamo ad esempio di avere tutto un elenco di nominativi dentro una tabella in un database specifico, e di avere invece i corrispettivi indirizzi in un'altra tabella, ma in un diverso database, ecco che sapere la giusta sintassi per agganciare questi due db può sicuramente fare la differenza in termini di semplicità di query, ma anche di prestazioni.

Vediamo dunque come pescare dati da tabelle su database diversi con mysql.

Come fare query tra database diversi in mysql

Per poter legare i dati presenti in tabelle su database diversi, ecco la sintassi da utilizzare in mysql:

SELECT t1.campo,t2.campo FROM database1.tabella1 AS t1, database2,tabella2 AS t2

La sintassi dunque per poter pescare i dati da un database ad un altro, è semplicemente: database.nometabella.
Tra l'altro, visto che già ci troviamo sul primo database, non è necessario richiamare la tabella con il prefisso del db in cui ci si trova, ma si può farlo solo per collegarsi al secondo db.

Per facilitare le query, o per meglio dire, per renderle meno lunghe, è molto utile dare un'abbreviazione alle tabelle usate, con il comando AS nomecorto.
Quest'abbreviazione può essere usata sia nella select, ma anche e soprattutto nella WHERE dove vengono inserite le condizioni necessarie per legare le due tabelle.

Bene, ora sai come fare select su database diversi in mysql.
Ovviamente, un filino tutto ciò influisce sulle prestazioni, rispetto ad avere le tabelle già nello stesso db, ma parliamo veramente di una manciata di millisecondi.