Corrigé un problème de jeux de caractères

**splifferwolf** · 06/08/2020, 15h08

Bonjour à tous.

Tout d'abord voici le problème que je cherche à régler.

Faire un select dans MySql sans différence des accents et majuscule (case sensitive).

Tout d'abord ma base de données est configurée en utf8_general_ci (qui est sensé être non sensible à la case), les tables sont toutes en utf8_general_ci et chaque colonne des table est en utf8_general_ci, Le servuer apache à bien le charset utf8 de définit.

Dans la logique utf8_general_ci est insensible à la case pourtant un

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

SELECT * FROM clients WHERE nom LIKE '%Gerard%';

Me retourne bien les Gerard mais étrangement certain Gérard ne sont pas retourner.

J'ai donc fait une recherche manuelle et en effet il y'a des problèmes.

C'est à dire qu'à certains moment les accentué sont différent, via phpmyadmin je parcours les clients et je tombe sur des

Gérard , gérard , Gerard , gerard , GÃ©rard , gÃ©rard

J'ai donc identifier plusieurs cause possible
- Le navigateur utilisateur n'est pas mit sur le bon encodage
- Lors de l'importations des données depuis un ancien système a réencodé en utf8 ce qui était déjà encodé en utf8

Bref le problème est donc bien lié au données et nom à la structure.

Comment puis-je corrigé c'est problème d'accent sur les données en sachant que la base de données contient maintenant 200 000 référence et que je ne peux pas les traitées une à une.
J'aimerai donc que le jeux de caractère soit identique pour toute les données où comment corriger les gÃ©rard en gérard en masse car il n'y à pas que les gérard qui ont ce problème et c'est comme ça dans toutes mes tables, ce qui forcément lors d'une recherche ne retourne pas tous les résultats.

Pour les majuscule j'ai dû ajouter la fonction UPPER sur mes requêtes sinon avec une recherche gérard je n'avait pas les Gérard..... et pourtant tout est bien mis en general_ci

D'avance merci des solution que vous pourriez m'apporter.

Belle journée à tous
Spliffer

[EDIT]
dans l'immédiat et pour que se problème ne survient plus, j'ai créer une fonction qui parse une chaîne en utf8 si elle est dans un autres jeux de caractère (donc même si le navigateur force un jeux de caractère l'ut8 sera appliqué).

Code php :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
<?php
/**
 * @methode : setUTF8
 * @str (string) : Chaîne à passée en utf8
 * @decode (booléen) : [false=Encode || true=Decode]
 * @descr : Vérifie si une chaîne de caractère est en UTF8, sinon la converti en UTF8
 *          encode ou decode
**/
function setUTF8($str=false,$decode=false){
    if($str == false){ return ''; }
    if($decode === false){ return (mb_detect_encoding($str, 'UTF-8', true) == false) ? utf8_encode($str) : $str; }
    else{ return (mb_detect_encoding($str, 'UTF-8', true) == false) ? $str : utf8_decode($str); }
}
 
// Exemple : 
$nom = (isset($_POST['nom'])) ? addslashes(setUTF8($_POST['nom'])) : '';
 
// Et pour le décodé il suffit de passer le paramètre true en plus (chez moi tout est en utf8 donc pas besoin du décode)
$nom = stripslashes(setUTF8($db['nom'],true));
?>

Avec celà je pense que je limite encore un peu le risque de jeux différent.
Il me faut encore corrigé le problème existant qui empêche une recherche correcte
[FIN EDIT]

**Artemus24** · 07/08/2020, 06h59

Salut splifferwolf.

Si je comprends bien, dans ta colonne, tu as deux codifications, l'un en latin1 et l'autre en utf8.

1) le choix du bon jeu de caractères et du collate.
Il est recommandé d'utiliser "utf8mb4" à la place de "utf8".
Et d'utiliser "utf8mb4_unicode_ci" au lieu de "utf8_general_ci".

2) vérifiez que la base de données a bien été codifiée.

Code mysql :

Sélectionner tout - Visualiser dans une fenêtre à part

ALTER DATABASE __votre_base_de_données__ CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

3) idem sur la table.

Code mysql :

Sélectionner tout - Visualiser dans une fenêtre à part

ALTER TABLE __votre_table__ CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

4) vérifier aussi votre fichier "my.ini" partie "client".

Code mysql :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
[client]
port   = 3306
socket = mysql
 
# ----------------------- #
#     access features     #
# ----------------------- #
 
host     = ???
user     = ???
password = ???
 
# --------------- #
#     Charset     #
# --------------- #
 
default-character-set = utf8mb4

Le client doit se connecter avec le bon jeu de caractères !

5) vérifier aussi votre fichier "my.ini" partie "mysqld".

Code mysqld :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
[mysqld]
port     = 3306
socket   = mysql
 
# --------------- #
#     Charset     #
# --------------- #
 
character-set-server     = utf8mb4
collation-server         = utf8mb4_unicode_ci
character-set-filesystem = utf8mb4
 
init-connect             = 'SET collation_connection = utf8mb4_unicode_ci; SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci'

L'oubli le plus fréquent est "SET NAMES ...".
Ce qui fait que la conversion se fait en "latin1".

6) vérification dans le SGBDR MySql.

Code mysql :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
SHOW global  Variables where Variable_name LIKE 'character\_set\_%';
SHOW session Variables where Variable_name LIKE 'character\_set\_%';
 
SHOW global  Variables where Variable_name LIKE 'collation%';
SHOW session Variables where Variable_name LIKE 'collation%';
 
show global  Variables where Variable_name like 'init_connect';
show session Variables where Variable_name like 'init_connect';

Normalement tout doit être dans le même jeu de caractères.

7) comme j'utilse un script pour faire ce test, je suis obligé de vérifier que j'envoie bien des caractères dans le bon jeu de caractères.
J'utilise NotePad++. Après avoir créé le fichier, je le convertis en "UTF8" par l'onglet "encodage".

En espérant arrivé à ce point, que la configuration de vos tables et de votre base de données est maintenant correcte.

8) votre problème est que vous avez dans votre colonne, deux codifications différentes.
Pour résoudre ce problème, je suis obligé d'utiliser coup sur coup deux astuces.

8-a) l'astuce est de forcer la conversion en passant par le type "binary".

Code mysql :

Sélectionner tout - Visualiser dans une fenêtre à part

convert(cast(convert(`chaine` using latin1) as binary) using utf8mb4)

Deux cas peuvent arriver :
--> la conversion se fait de latin1 vers utf8.
--> la conversion ne se fait pas parce que c'est déjà dans le bon jeu de caractères.

8-b) la seconde astuce est de reprendre le contenu de la colonne avant conversion, quand la fonction "convert()" retourne un NULL.
Pourquoi ? Parce que le contenu est déjà convertit. Ce qui donne :

Code mysql :

Sélectionner tout - Visualiser dans une fenêtre à part

coalesce(convert(cast(convert(`chaine` using latin1) as binary) using utf8mb4), chaine);

9) il n'est pas nécessaire de faire un "chaine like "%gerard%"" si la colonne contient que le nom "gérard".
Il suffit de remplacer le "like" par un "=" : "chaine = "%gerard%"".

10) Une liste non exhaustive des caractères mal encodés :
--> https://www.neosama-consulting.com/r...latin1-resolu/
Ca peut toujours être utilie !

11) le résultat sur un exemple :

Code mysql :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
--------------
START TRANSACTION
--------------
 
--------------
DROP DATABASE IF EXISTS `base`
--------------
 
--------------
CREATE DATABASE IF NOT EXISTS `base`
        DEFAULT CHARACTER SET `utf8`
        DEFAULT COLLATE       `utf8_general_ci`
--------------
 
--------------
DROP TABLE IF EXISTS `test`
--------------
 
--------------
create table `test`
(  `id`       integer unsigned not null auto_increment primary key,
   `chaine`   varchar(255)         null
) ENGINE=InnoDB
  DEFAULT CHARSET=`utf8` COLLATE=`utf8_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
insert into `test` (`chaine`) values
  ('gerard'),
  ('Gerard'),
  ('gérard'),
  ('Gérard'),
  ('GÃ©rard'),
  ('gÃ©rard'),
  ('forêt'),
  ('forÃªt')
--------------
 
--------------
select * from `test`
--------------
 
+----+-----------+
| id | chaine    |
+----+-----------+
|  1 | gerard    |
|  2 | Gerard    |
|  3 | gérard    |
|  4 | Gérard    |
|  5 | GÃ©rard   |
|  6 | gÃ©rard   |
|  7 | forêt     |
|  8 | forÃªt    |
+----+-----------+
--------------
commit
--------------
 
--------------
SHOW CREATE DATABASE `base`
--------------
 
+----------+--------------------------------------------------------------------------------------------------+
| Database | Create Database                                                                                  |
+----------+--------------------------------------------------------------------------------------------------+
| base     | CREATE DATABASE `base` /*!40100 DEFAULT CHARACTER SET utf8 */ /*!80016 DEFAULT ENCRYPTION='N' */ |
+----------+--------------------------------------------------------------------------------------------------+
--------------
ALTER DATABASE `base` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci
--------------
 
--------------
SHOW CREATE DATABASE `base`
--------------
 
+----------+--------------------------------------------------------------------------------------------------------------------------------+
| Database | Create Database                                                                                                                |
+----------+--------------------------------------------------------------------------------------------------------------------------------+
| base     | CREATE DATABASE `base` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci */ /*!80016 DEFAULT ENCRYPTION='N' */ |
+----------+--------------------------------------------------------------------------------------------------------------------------------+
--------------
commit
--------------
 
--------------
SHOW CREATE TABLE `test`
--------------
 
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                              |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test  | CREATE TABLE `test` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `chaine` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
--------------
ALTER TABLE `test` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci
--------------
 
--------------
SHOW CREATE TABLE `test`
--------------
 
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                               |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test  | CREATE TABLE `test` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `chaine` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=COMPRESSED |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
--------------
commit
--------------
 
--------------
select * from `test`
--------------
 
+----+-----------+
| id | chaine    |
+----+-----------+
|  1 | gerard    |
|  2 | Gerard    |
|  3 | gérard    |
|  4 | Gérard    |
|  5 | GÃ©rard   |
|  6 | gÃ©rard   |
|  7 | forêt     |
|  8 | forÃªt    |
+----+-----------+
--------------
update `test` set chaine = coalesce(convert(cast(convert(`chaine` using latin1) as binary) using utf8mb4), chaine)
--------------
 
--------------
select * from `test`
--------------
 
+----+---------+
| id | chaine  |
+----+---------+
|  1 | gerard  |
|  2 | Gerard  |
|  3 | gérard  |
|  4 | Gérard  |
|  5 | Gérard  |
|  6 | gérard  |
|  7 | forêt   |
|  8 | forêt   |
+----+---------+
--------------
COMMIT
--------------
 
Appuyez sur une touche pour continuer...