Je garde ça ici parce que ça arrive mais pas suffisamment souvent pour que je m’en souvienne d’une fois sur l’autre.
Si dans votre table des champs ressemblent à “Très bon produit .J’en suis très satisfaite” c’est qu’il y a eu un soucis à un moment. Un import foireux, un script bugué peu importe.
Pour corriger ça il suffit de lancer les requêtes suivants :
UPDATE table SET field = REPLACE(field, CAST(_latin1'à' AS CHAR CHARACTER SET utf8) COLLATE utf8_bin, 'à'); UPDATE table SET field = REPLACE(field, CAST(_latin1'À' AS CHAR CHARACTER SET utf8) COLLATE utf8_bin, 'À'); UPDATE table SET field = REPLACE(field, CAST(_latin1'â' AS CHAR CHARACTER SET utf8) COLLATE utf8_bin, 'â'); UPDATE table SET field = REPLACE(field, CAST(_latin1'Â' AS CHAR CHARACTER SET utf8) COLLATE utf8_bin, 'Â'); UPDATE table SET field = REPLACE(field, CAST(_latin1'ä' AS CHAR CHARACTER SET utf8) COLLATE utf8_bin, 'ä'); UPDATE table SET field = REPLACE(field, CAST(_latin1'Ä' AS CHAR CHARACTER SET utf8) COLLATE utf8_bin, 'Ä'); UPDATE table SET field = REPLACE(field, CAST(_latin1'ç' AS CHAR CHARACTER SET utf8) COLLATE utf8_bin, 'ç'); UPDATE table SET field = REPLACE(field, CAST(_latin1'Ç' AS CHAR CHARACTER SET utf8) COLLATE utf8_bin, 'Ç'); UPDATE table SET field = REPLACE(field, CAST(_latin1'è' AS CHAR CHARACTER SET utf8) COLLATE utf8_bin, 'è'); UPDATE table SET field = REPLACE(field, CAST(_latin1'È' AS CHAR CHARACTER SET utf8) COLLATE utf8_bin, 'È'); UPDATE table SET field = REPLACE(field, CAST(_latin1'é' AS CHAR CHARACTER SET utf8) COLLATE utf8_bin, 'é'); UPDATE table SET field = REPLACE(field, CAST(_latin1'É' AS CHAR CHARACTER SET utf8) COLLATE utf8_bin, 'É'); UPDATE table SET field = REPLACE(field, CAST(_latin1'ê' AS CHAR CHARACTER SET utf8) COLLATE utf8_bin, 'ê'); UPDATE table SET field = REPLACE(field, CAST(_latin1'Ê' AS CHAR CHARACTER SET utf8) COLLATE utf8_bin, 'Ê'); UPDATE table SET field = REPLACE(field, CAST(_latin1'ë' AS CHAR CHARACTER SET utf8) COLLATE utf8_bin, 'ë'); UPDATE table SET field = REPLACE(field, CAST(_latin1'Ë' AS CHAR CHARACTER SET utf8) COLLATE utf8_bin, 'Ë'); UPDATE table SET field = REPLACE(field, CAST(_latin1'î' AS CHAR CHARACTER SET utf8) COLLATE utf8_bin, 'î'); UPDATE table SET field = REPLACE(field, CAST(_latin1'Î' AS CHAR CHARACTER SET utf8) COLLATE utf8_bin, 'Î'); UPDATE table SET field = REPLACE(field, CAST(_latin1'ï' AS CHAR CHARACTER SET utf8) COLLATE utf8_bin, 'ï'); UPDATE table SET field = REPLACE(field, CAST(_latin1'Ï' AS CHAR CHARACTER SET utf8) COLLATE utf8_bin, 'Ï'); UPDATE table SET field = REPLACE(field, CAST(_latin1'ô' AS CHAR CHARACTER SET utf8) COLLATE utf8_bin, 'ô'); UPDATE table SET field = REPLACE(field, CAST(_latin1'Ô' AS CHAR CHARACTER SET utf8) COLLATE utf8_bin, 'Ô'); UPDATE table SET field = REPLACE(field, CAST(_latin1'ö' AS CHAR CHARACTER SET utf8) COLLATE utf8_bin, 'ö'); UPDATE table SET field = REPLACE(field, CAST(_latin1'Ö' AS CHAR CHARACTER SET utf8) COLLATE utf8_bin, 'Ö'); UPDATE table SET field = REPLACE(field, CAST(_latin1'ù' AS CHAR CHARACTER SET utf8) COLLATE utf8_bin, 'ù'); UPDATE table SET field = REPLACE(field, CAST(_latin1'Ù' AS CHAR CHARACTER SET utf8) COLLATE utf8_bin, 'Ù'); UPDATE table SET field = REPLACE(field, CAST(_latin1'û' AS CHAR CHARACTER SET utf8) COLLATE utf8_bin, 'û'); UPDATE table SET field = REPLACE(field, CAST(_latin1'Û' AS CHAR CHARACTER SET utf8) COLLATE utf8_bin, 'Û'); UPDATE table SET field = REPLACE(field, CAST(_latin1'ü' AS CHAR CHARACTER SET utf8) COLLATE utf8_bin, 'ü'); UPDATE table SET field = REPLACE(field, CAST(_latin1'Ü' AS CHAR CHARACTER SET utf8) COLLATE utf8_bin, 'Ü'); UPDATE table SET field = REPLACE(field, CAST(_latin1'–' AS CHAR CHARACTER SET utf8) COLLATE utf8_bin, '–'); UPDATE table SET field = REPLACE(field, CAST(_latin1'‘' AS CHAR CHARACTER SET utf8) COLLATE utf8_bin, '‘'); UPDATE table SET field = REPLACE(field, CAST(_latin1'…' AS CHAR CHARACTER SET utf8) COLLATE utf8_bin, '…'); UPDATE table SET field = REPLACE(field, CAST(_latin1'•' AS CHAR CHARACTER SET utf8) COLLATE utf8_bin, '•');
“table” et “field” sont à remplacer bien sûr.