MySQL Combinazione illegale di regole di confronto

Dopo aver visto i miei registri dei prodotti, ho qualche errore di citazione:

[2012-08-31 15:56:43] request.CRITICAL: Doctrine\DBAL\DBALException: An exception occurred while executing 'SELECT t0.username ....... FROM fos_user t0 WHERE t0.username = ?' with params {"1":"Nrv\u29e7Kasi"}: SQLSTATE[HY000]: General error: 1267 Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '=' 

Al contrario ho l’impostazione predefinita per UTF-8 sotto la docgine cfg:

 doctrine: dbal: charset: UTF8 

Sembra che tutte le mie tabelle MySQL siano in latin1_swedish_ci , quindi la mia domanda è:

Posso cambiare manualmente le regole di confronto in utf8_general_ci per tutte le mie tabelle senza complicazioni / precauzioni?

    È utile comprendere le seguenti definizioni:

    • Una codifica dei caratteri mostra in dettaglio come ciascun simbolo è rappresentato in binario (e quindi memorizzato nel computer). Ad esempio, il simbolo é (U + 00E9, piccola lettera latina E con acuta) è codificato come 0xc3a9 in UTF-8 (che MySQL chiama utf8 ) e 0xe9 in Windows-1252 (che MySQL chiama latin1 ).

    • Un set di caratteri è l’alfabeto di simboli che possono essere rappresentati utilizzando una determinata codifica di caratteri. Confusamente, il termine è anche usato per indicare la stessa codifica dei caratteri.

    • Un confronto è un ordinamento su un set di caratteri, in modo che le stringhe possano essere confrontate. Ad esempio: la collazione latin1_swedish_ci di MySQL tratta le variazioni più accentuate di un personaggio come equivalenti al carattere base, mentre la sua collazione latin1_general_ci le ordinerà prima del prossimo carattere base ma non equivalenti (ci sono anche altre differenze più significative: come l’ordine di caratteri come å , ä , ö e ß ).

    MySQL deciderà quali regole di confronto dovrebbero essere applicate a una determinata espressione come documentato in Collation of Expressions : in particolare, le regole di confronto di una colonna hanno la precedenza su quella di una stringa letterale.

    La clausola WHERE della tua query confronta le seguenti stringhe:

    1. un valore in fos_user.username , codificato nel set di caratteri della colonna (Windows-1252) ed esprimendo una preferenza per le sue regole di confronto latin1_swedish_ci (con un valore di coercibilità di 2); con

    2. la stringa letterale 'Nrv⧧Kasi' , codificata nel set di caratteri della connessione (UTF-8, come configurato da Doctrine) ed esprimendo una preferenza per le regole di confronto della utf8_general_ci (con un valore di coercibilità di 4).

    Poiché la prima di queste stringhe ha un valore di coercibilità inferiore al secondo, MySQL tenta di eseguire il confronto utilizzando le regole di confronto di quella stringa: latin1_swedish_ci . Per fare ciò, MySQL tenta di convertire la seconda stringa in latin1 poiché il carattere non esiste in quel set di caratteri, il confronto fallisce.


    avvertimento

    Uno dovrebbe sospendere per un momento per considerare come la colonna è attualmente codificata: si sta tentando di filtrare per i record dove fos_user.username è uguale a una stringa che contiene un carattere che non può esistere in quella colonna !

    Se si ritiene che la colonna contenga tali caratteri, probabilmente si è scritto sulla colonna mentre la codifica dei caratteri di connessione è stata impostata su qualcosa (ad esempio latin1 ) che ha causato a MySQL l’interpretazione della sequenza di byte ricevuta come caratteri che sono tutti in Windows-1252 set di caratteri.

    Se questo è il caso, prima di proseguire ulteriormente è necessario correggere i dati!

    1. convertire tali colonne nella codifica dei caratteri utilizzata per l’inserimento dei dati, se diversa dalla codifica incumbent:

       ALTER TABLE fos_users MODIFY username VARCHAR(123) CHARACTER SET foo; 
    2. rilasciare le informazioni di codifica associate a tali colonne convertendole nel set di caratteri binary :

       ALTER TABLE fos_users MODIFY username VARCHAR(123) CHARACTER SET binary; 
    3. associare a tali colonne la codifica in cui i dati sono stati effettivamente trasmessi convertendoli nel set di caratteri pertinente.

       ALTER TABLE fos_users MODIFY username VARCHAR(123) CHARACTER SET bar; 

    Si noti che, se si esegue la conversione da una codifica multibyte, potrebbe essere necessario aumentare la dimensione della colonna (o persino cambiarne il tipo) per far fronte alla lunghezza massima ansible della stringa convertita.


    Una volta che si è sicuri che le colonne siano codificate correttamente, si potrebbe forzare il confronto a essere condotto usando una collazione Unicode da entrambi-

    • conversione esplicita del valore fos_user.username in un set di caratteri Unicode:

       WHERE CONVERT(fos_user.username USING utf8) = ? 
    • forzare la stringa letterale ad avere un valore di coercibilità inferiore rispetto alla colonna (causerà una conversione implicita del valore della colonna in UTF-8):

       WHERE fos_user.username = ? COLLATE utf8_general_ci 

    O si potrebbe, come dici tu, convertire permanentemente le colonne in una codifica Unicode e impostarne le regole di confronto in modo appropriato.

    Posso cambiare manualmente le regole di confronto in utf8_general_ci per tutte le mie tabelle senza complicazioni / precauzioni?

    La considerazione principale è che le codifiche Unicode occupano più spazio rispetto ai set di caratteri a byte singolo, quindi:

    • potrebbe essere necessario più spazio di archiviazione;

    • i confronti potrebbero essere più lenti; e

    • È ansible che sia necessario regolare le lunghezze del prefisso dell’indice (notare che il massimo è express in byte, quindi potrebbe rappresentare un numero inferiore di caratteri rispetto al passato).

    Inoltre, si tenga presente che, come documentato in ALTER TABLE Sintassi :

    Per cambiare il set di caratteri di default della tabella e tutte le colonne di caratteri ( CHAR , VARCHAR , TEXT ) in un nuovo set di caratteri, usa una dichiarazione come questa:

      ALTER TABLE tbl_name CONVERT TO CHARACTER SET charset_name ; 

    Per una colonna con un tipo di dati VARCHAR o uno dei tipi TEXT , CONVERT TO CHARACTER SET modificherà il tipo di dati in base alle necessità per garantire che la nuova colonna sia abbastanza lunga da contenere tutti i caratteri della colonna originale. Ad esempio, una colonna TEXT ha due byte di lunghezza, che memorizzano la lunghezza in byte dei valori nella colonna, fino a un massimo di 65.535. Per una colonna TEXT latin1 , ogni carattere richiede un singolo byte, quindi la colonna può contenere fino a 65.535 caratteri. Se la colonna viene convertita in utf8 , ogni carattere potrebbe richiedere fino a tre byte, per una lunghezza massima ansible di 3 × 65.535 = 196.605 byte. Tale lunghezza non si adatta ai byte di lunghezza di una colonna TEXT , quindi MySQL convertirà il tipo di dati in MEDIUMTEXT , che è il tipo di stringa più piccolo per il quale i byte di lunghezza possono registrare un valore di 196.605. Allo stesso modo, una colonna VARCHAR potrebbe essere convertita in MEDIUMTEXT .

    Per evitare modifiche al tipo di dati del tipo appena descritto, non utilizzare CONVERT TO CHARACTER SET . Invece, usa MODIFY per cambiare le singole colonne.

    Giusto. Mi sono imbattuto in questo problema e la migliore soluzione rapida e veloce è

      CONVERT(fos_user.username USING utf8) 

    Basta convertire il set di caratteri della tabella per comando come segue,

     ALTER TABLE tbl_name CONVERT TO CHARACTER SET utf8;