Archives mensuelles: October 2016

Revue de presse

Correction : MySQL TP1

Schéma

Représentez graphiquement le schéma de cette base.

Création de la base

--
-- Structure de la table `composition`
--

CREATE TABLE IF NOT EXISTS `composition` (
`idParcours` int(11) NOT NULL,
`idEnigme` int(11) NOT NULL,
`ordre` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Structure de la table `enigme`
--

CREATE TABLE IF NOT EXISTS `enigme` (
`id` int(11) NOT NULL,
`texte` varchar(255) NOT NULL,
`idLieuDepart` int(11) NOT NULL,
`idLieuArrivee` int(11) NOT NULL,
`duree` time NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Structure de la table `indice`
--

CREATE TABLE IF NOT EXISTS `indice` (
`id` int(11) NOT NULL,
`idEnigme` int(11) NOT NULL,
`texte` varchar(255) NOT NULL,
`ordre` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Structure de la table `lieu`
--

CREATE TABLE IF NOT EXISTS `lieu` (
`id` int(11) NOT NULL,
`lat` double NOT NULL,
`lon` double NOT NULL,
`description` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Structure de la table `parcours`
--

CREATE TABLE IF NOT EXISTS `parcours` (
`id` int(11) NOT NULL,
`titre` varchar(255) NOT NULL,
`description` varchar(255) NOT NULL,
`duree` time NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Index pour les tables exportées
--

--
-- Index pour la table `composition`
--
ALTER TABLE `composition`
ADD PRIMARY KEY (`idParcours`,`idEnigme`);

--
-- Index pour la table `enigme`
--
ALTER TABLE `enigme`
ADD PRIMARY KEY (`id`);

--
-- Index pour la table `indice`
--
ALTER TABLE `indice`
ADD PRIMARY KEY (`id`);

--
-- Index pour la table `lieu`
--
ALTER TABLE `lieu`
ADD PRIMARY KEY (`id`);

--
-- Index pour la table `parcours`
--
ALTER TABLE `parcours`
ADD PRIMARY KEY (`id`);

--
-- AUTO_INCREMENT pour les tables exportées
--

--
-- AUTO_INCREMENT pour la table `enigme`
--
ALTER TABLE `enigme`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;
--
-- AUTO_INCREMENT pour la table `indice`
--
ALTER TABLE `indice`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;
--
-- AUTO_INCREMENT pour la table `lieu`
--
ALTER TABLE `lieu`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;
--
-- AUTO_INCREMENT pour la table `parcours`
--
ALTER TABLE `parcours`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

Après les imports, bien vérifier que les champs ont été remplis correctement. En particulier, il y avait une permutation de champs dans la table indice. La base complète est dans le fichier TP1_corrigé.sql

Requètes

Ecrivez et testez les requètes donnant les informations suivantes :

  1. Requètes simples (une seule table)
    • La liste des parcours disponibles, classés par durée

      SELECT * FROM parcours ORDER BY duree DESC;
    • La liste des lieux, avec leur description

      SELECT id,description FROM parcours;
    • La liste des énigmes de moins de 5 minutes.

      SELECT * FROM enigme
      WHERE duree < "00:05:00"

      Remarques :
      • quand les durées sont comparées à des chaînes, elles sont converties sous le format "HH:MM:SS" qui permet d'utiliser l'ordre lexicographique (celui du dictionnaire).
      • Le résultat est vide, ce qui normal vu la base. Essayez avec 15 au lieu de 5 pour avoir des résultats.
    • La description des trois lieux les plus à l'ouest (plus petites longitude).

      SELECT description FROM lieu
      ORDER BY lon DESC
      LIMIT 3

      Remarque : LIMIT permet de ne garder qu'un certain nombre de lignes (les premières).
  2. Jointures
    • Toutes les énigmes, avec la description du lieu de départ pour chacune.

    • SELECT enigme.texte, lieu.description FROM enigme
      JOIN lieu ON enigme.idLieuDepart = lieu.id;

    • Le texte de chaque énigme, avec tous les textes des indices affichés dans l'ordre.

    • SELECT enigme.texte, indice.texte, ordre FROM enigme
      JOIN indice ON indice.idEnigme = enigme.id
      ORDER BY enigme.id, ordre ASC;

      Remarque : trier par rapport à l'énigme puis l'ordre est nécessaire pour que les énigmes restent groupées.

    • La description de chaque parcours, avec la description du lieu de départ

    • SELECT parcours.description,lieu.description FROM parcours
      JOIN composition on composition.idParcours = parcours.id
      JOIN enigme on composition.idEnigme = enigme.id
      JOIN lieu on enigme.idLieuDepart = lieu.id
      WHERE composition.ordre = 1

      Remarque : on ne peut joindre parcours et énigme qu'en passant par composition.

    • Le lieu de départ et d'arrivée de toutes les énigmes ayant au moins un indice

    • SELECT DISTINCT enigme.id, lieuDepart.description, lieuArrivee.description FROM enigme
      JOIN lieu as lieuDepart ON enigme.idLieuDepart = lieuDepart.id
      JOIN lieu as lieuArrivee ON enigme.idLieuArrivee = lieuArrivee.id
      JOIN indice ON indice.idEnigme = enigme.id;

      Remarque : la jointure par défaut (INNER JOIN) garde une ligne par correspondance trouvée. Ainsi, le dernier JOIN permet d'éliminer les énigmes sans indices. Cela se fait au prix de la répétion des autres lignes (autant de fois qu'il y a des indices), mais un simple DISTINCT contourne ce problème.

    • Les lieux jamais utilisés

      SELECT lieu.description FROM lieu
      LEFT JOIN enigme ON lieu.id = enigme.idLieuDepart OR lieu.id=enigme.idLieuArrivee
      WHERE enigme.id IS NULL

      Remarques :

      • LEFT JOIN permet de garder les lignes de la colonne de gauche même si il n'y a aucune correspondance. Les valeurs manquantes sont mises à NULL
      • Le test de la valeur NULL se fait avec l'opérateur IS NULL, pas avec =
  3. Agrégation et groupements
    • Le nombre de parcours dans la base

      SELECT COUNT(*) FROM parcours;
    • Pour chaque parcours, le nombre d'énigme.

      SELECT parcours.description, MAX(ordre) FROM parcours
      JOIN composition ON composition.idParcours =parcours.id
      GROUP BY parcours.id

      Remarque : COUNT(idEnigme) fonctionne aussi. Dans les deux cas, on peut se dispenser de la jointure avec la table enigme.
    • Pour chaque énigme, le nombre d'indices.

      SELECT enigme.texte, COUNT(indice.id) FROM enigme
      LEFT JOIN indice ON indice.idEnigme = enigme.id
      GROUP BY enigme.id

      Remarque : certaines enigmes n'ayant pas d'indice, il faut utiliser LEFT JOIN et non JOIN, qui oublierait les énigmes sans indices. A noter aussi que COUNT ignore les valeurs à NULL dans son décompte.
    • Pour chaque parcours, la somme des durées des énigmes

      SELECT parcours.description,SEC_TO_TIME(SUM(TIME_TO_SEC(enigme.duree))) FROM parcours
      JOIN composition ON composition.idParcours = parcours.id
      JOIN enigme ON composition.idEnigme = enigme.id
      GROUP BY parcours.id

      A noter que pour additionner les durées, il faut les convertir en secondes (puis reconvertir en durées pour l'affichage).

    • Pour chaque lieu, le nombre de fois où il sert de point de départ pour une énigme

    • SELECT lieu.description,COUNT(enigme.id) FROM lieu
      LEFT JOIN enigme ON enigme.idLieuDepart=lieu.id
      GROUP BY lieu.id

      Encore une fois, omettre LEFT ignorerait les lignes avec un count à 0

  4. Sous-requètes
    • l'énigme avec le plus d'indices

      SELECT enigme.texte,ordre FROM enigme
      JOIN composition on enigme.id = composition.idEnigme
      WHERE ordre = (SELECT max(ordre) FROM composition)

    • le lieu d'arrivée de la dernière étape de chaque parcours

      SELECT parcours.description, lieu.description FROM parcours
      JOIN composition ON composition.parcoursId = parcours.id
      JOIN lieu ON lieu.id = composition.idLieuArrivee
      WHERE composition.ordre = (
      SELECT max(comp.ordre) FROM composition AS comp
      WHERE composition.idParcours = comp.idParcours AND composition.idEnigme =comp.idEnigme
      )

      La sous requète trouve le maximum d'énigmes pour le parcours. A noter que cette sous-requète dépend de la valeur d'un champ de la requète principale et doit donc être calculée sur chaque ligne, ce qui est (relativement) lent. On parle dans ce cas de sous-requète liée (à éviter si possible).
    • le parcours avec le plus d'étapes


      SELECT parcours.titre FROM parcours
      JOIN composition ON composition.idParcours = parcours.id
      WHERE ordre = (SELECT max(ordre) FROM composition)

Insertion

Ajoutez une énigme à la fin d'un parcours.

Le plus simple est d'ajouter l'énigme 2 à la fin du deuxième parcours. Pour cela, il suffit d'insérer une ligne dans la table composition :

INSERT INTO composition
VALUES (2,2,3);

La valeur 3 indique que cette énigme arrive en troisième position. On trouve cette valeur avec un
SELECT max(ordre) FROM composition where idParcours =2

Quelles difficultés poserait une insertion au milieu d'un parcours ?

Un ajout au milieu nécessite une étape de préparation pour décaler les énigmes suivantes, afin de faire une place pour celle à ajouter.

UPDATE composition
SET ordre = ordre +1
WHERE idParcours = [id] AND ordre >= [position];

Requètes plus complexes

Comment trouver dans la base (donnez la ou les requètes si possible, et sinon expliquez ce qui pose problème et comment le résoudre):

  • Si un parcours est une boucle ou pas ?

    Pour avoir une boucle, il faut que le départ et l'arrivée soient identiques, donc il faut idLieuDepart de la première énigme égal à idLieuArrivé de la dernière, pour un même parcours.
    On peut donc lister les parcours en boucle de la manièer suivante :

    SELECT c1.idParcours FROM enigme AS e1
    JOIN enigme AS e2 ON e1.idLieuDepart = e2.idLieuArrivee
    JOIN composition AS c1 ON e1.id = c1.idEnigme
    JOIN composition AS c2 ON e2.id = c2.idEnigme
    WHERE c1.idParcours = c2.idParcours AND c1.ordre = 1 AND c2.ordre =
    (SELECT MAX(ordre) FROM composition WHERE composition.idParcours = c1.idParcours)

    A noter que pour trouver l'ordre de la dernière énigme, une sous-requète est nécessaire.

  • Les différents “sauts” dans les parcours, c'est à dire toutes les énigmes qui ne partent pas du lieu d'arrivée de l'énigme précédente
    Le principe est le même que pour la question précédente. On cherche toutes les paires d'énigmes qui se suivent et dont les lieux de départ et d'arrivée ne correspondent pas.

    SELECT c1.* FROM composition AS c1
    JOIN composition AS c2 ON c1.idParcours = c2.idParcours
    JOIN enigme AS e1 ON c1.idEnigme = e1.id
    JOIN enigme AS e2 ON c2.idEnigme = e2.id
    WHERE c2.ordre = c1.ordre +1 AND e1.idLieuArrivee != e2.idLieuDepart

    A noter que dans la base fournie, la requète ne retourne rien. On peut essayer avec = au lieu du !=, et vérifier que l'on obtient toutes les étapes, sauf les dernières (puisqu'elles ne sont évidemment pas suivies d'une étape qui commence là où elles se terminent).

Changement de structure

On envisage de remplacer le champ ordre de la table composition par un champ idEnigmeSuivante.

  1. Peut-on toujours répondre aux mêmes questions qu'avec la structure précédente ? (en particulier, comment trouver la première et la dernière énigme de chaque parcours ?)

    Evidemment, seules les requètes utilisant le champ ordre sont affectées. Le calcul de la dernière énigme d'un parcours se trouve simplifié, puisqu'on aura alors simplement à tester si idEnigmeSuivante IS NULL.
    La première énigme est plus difficile à trouver, puisqu'il faut trouver celle qui n'a pas de prédécesseur.
    La solution passe par une auto-jointure de la table composition :

    SELECT * FROM composition as c1
    RIGHT JOIN composition as c2 where c1.idParcours = c2.idParcours AND c1.idEnigmeSuivante = c2.idEnigme

    Il est important d'utiliser un RIGHT JOIN pour conserver les énigme n'ayant pas de successeur (et donc pas de correspondance au niveau de la jointure).
    Si on ajoute ensuite un WHERE c2.idEnigme = null, on a alors toutes les premières énigmes.

    Avec cette technique, on peut réécrire les requètes des questions précédentes.

  2. Quelles question du 3) deviennent plus facile ? Plus difficiles ?
Revue de presse

MySql TP 3 — Améliorations et correction d’erreurs

L’objectif de ce TD de corriger un certains nombres d’erreurs fraquentes en bases de données, que ce soit au niveau de la conception, de la réalisation ou même l’utilisation.

Pour chaque question, après avoir identifié les problèmes (en vous aidant des questions), vous pouvez soit chercher par vous-même les solutions, soit vous aider des commandes indiquées à la fin de ce sujet (en fonction de vos connaissances).

Le fichier SQL à importer est dans /export/commun/src/Algo/BD/TP3.sql

Principes généraux

  1. Un champ doit contenir pour chaque ligne au plus une information, toujours de même type et toujours avec la même signification d’une ligne à l’autre.
  2. Si une valeur peut être calculée à partir des autres données, il est préférable d’éviter de la mémoriser (exception : information longue à calculer, devant être affichée souvent et rarement modifiée). En particulier, une clef ne doit pas être utilisée dans un caclul, ni comme donnée ni comme résultat.
  3. Toutes les informations utiles pour le projet doivent se trouver dans la base, et seulement elles. Il faut aussi s’assurer que ces informations peuvent être obtenues avant ou pendant le fonctionnement du projet.
  4. Quand la suppression de certaines données est possible, Il faut décider de la technique à utiliser. Par exemple on peut autoriser les clefs étrangère à null, ou marquer comme supprimées les lignes au lieu de les effacer réellement.

Avant de la conception, il est recommandé de lister les opérations qui devront être possibles. Pour valider un schéma, il faut vérifier comment écrire chacune des requêtes correspondant à ces informations.

Si une opération nécessite plusieurs requètes, ou une requète très complexe, il est recommandé d’envisager d’autres solutions et de comparer les avantages et inconvénients.

Boutique de vente d’articles de sport en ligne

article(id,attribut,valeur)
commande(id,id_client, id_article, quantité, date,statut)
client(id, age, jour_anniveraire, mois_anniversaire, addresse)

Questions

  • Comment afficher l’age moyen des clients pour un produit (comment mettre à jour les ages ?)
  • Comment changer le statut d’une commande de « préparée » à « envoyée » ? (nb lignes modifiées ?)
  • Comment ajouter des articles à une commande
  • Comment changer un article de catégorie ?

Système de commentaire sur des liens

link(id,url,id_comment_last)
user(id,login,password,email,addresse)
comment(id,idLink,idUser,date,texte)

Questions

  • Suppression d’un utilisateur (affichage du nom d’utilisateur supprimé couleur différente)
  • Afficher le dernier commentaire pour chaque site (Comment se passe l’ajout/suppression d’un nouveau commentaire ?)

Migration wordpress

On réalise la migration d’un site sous WordPress. L’ancienne url est www.home.hexagonal-games.com/wordpress, la nouvelle www.dubois.cours.iut-lens.univ-artois.fr. Pour alléger l’exercice, on s’intéresse uniquement à la table wp_post.

Corrigez les différents problèmes.

Commandes utiles

Remarque : PhpMyAdmin propose dans sa console SQL un canevas pour les principales commandes SQL, n’hésitez pas à vous en servir !

Donner ou changer une valeur d’un champ :

UPDATE table
SET champ=valeur
WHERE condition

Insérer dans une table table2 le résultat d’une requète sur table1 :

INSERT INTO table2
SELECT * FROM table1;

Pour remplacer une chaine dans une autre, on peut appeler la fonction REPLACE :

REPLACE('exemple','e','E') // retourne ExEmplE

Pour les problèmes d’encodage, une méthode de correction est proposée dans cet article.

Revue de presse

SQL — Encodage

L’encodage et de jeux de caractères est source de problèmes dans la gestion des bases de données, en particulier lorsque les bases sont utilisées pour du texte, comme c’est souvent le cas pour les sites web.

Principes

Pour comprendre la nature du problème, il faut se pencher sur les différentes techniques utilisées pour représenter les caractères, et les évolutions de ces techniques depuis les débuts de l’informatique.

Les ordinateurs manipulant des nombres, l’idée principale est de numéroter les caractères. On définit ainsi un code, qui fait correspondre à chaque caractère un nombre. Les problèmes proviennent des façons de représenter ces codes, et de la coexistence de plusieurs code. Dans le premier cas, on parle de différents encodages ; dans le second de différentes tables de caractères.

Code ASCII

Un des premiers codes utilisés est le code ASCII, qui comporte 128 caractères non accentués, les chiffres, les signes de ponctuation et des caractères de controle (fin de ligne par exemple). Sauf dans quelques applications particulières (comme la transmission), ce code utilise un octet par caractère.
Comme 128 caractères tiennent sur 7 bits, il reste donc un bit inutilisé.

Ce bit a donc été utilisé pour ajouter des caractères, ce qui donne l’ASCII étendu et permet par exemple d’ajouter les caractères accentués, des symboles ainsi que des bordures pour la construction de tableaux. Le problème, c’est que différentes tables de caractères ont vu le jour, pour des langages ou des systèmes différents.

Ainsi, un même octet (supérieur à 128) peut donc correspondre à un caractère ASCII différent en fonction de la table utilisée.

Unicode

Afin de résoudre ce problème, il a été décidé de normaliser l’ensemble des caractères existant (et même futurs…) dans une même table, appelée table Unicode. Pour simplifier les choses, les 128 premiers caractère restent ceux de la table ASCII.

Le problème, c’est que le nombre total de caractère dépasse largement 256, et ne peut donc être codé sur un seul octet. Il faut donc trouver une autre technique d’encodage qu’un caractère par octet.

Les techniques utilisées sont les suivantes :

  • UTF32 (équivalent à UCS4) : 32 bits (4 octets) pour chaque caractère
  • UTF16 (évolution de UCS2) : 16 à 32 bits (2 à 4 octets) minimum caractère. Les caractères les plus courants utilisent seulement 2 octets (ce sont ceux de UCS2), les autres 2 x 2 octets.
  • UTF8 : 1 à 4 octets par caractère. Les caractères de code <128 sont codés sur un octets, ce qui rend valide en UTF8 une chaîne ASCII (simple)

UTF8

UTF8 est actuellement l’encodage le plus utilisé sur internet, et pour de bonnes raisons :

  • Comme tous les encodages d’unicode, tous les caractères peuvent être représentés. Les problèmes de jeux de caractère n’ont pas lieu d’être en UTF8.
  • Pour la plupart des caractères latins, un octet suffit (contrairement à UTF32 par exemple).
  • Un texte en ASCII simple est parfaitement valide en UTF8, et ce sans aucun traitement. A noter néanmoins que les accents sont perdus

Néanmoins, UTF8 n’évite pas toutes les difficultés :

  • Un octet ne correspond pas exactement à un caractère. Il faut donc utiliser des fonctions pour le traitement des chaînes de caractères
    qui prennent spécifiquement en compte cet encodage. On risque sinon d’avoir des résultats erronés dès qu’un caractère de code >128 est présent…
  • Même si tous les caractères sont supportés, un certain nombre de traitements restent dépendant de la langue, comme par exemple l’ordre alphabétique (les règles diffèrent d’un pays à l’autre). Il faut donc préciser les règles de comparaison à utiliser. En bases de données, on parle de collation (interclassement en français)
  • Un caractère pouvant prendre jusqu’à 4 octets, il faut penser à réserver plus d’octets pour une chaîne (potentiellement x4)
  • Tous les caractères existants sont gérés par Unicode, mais cela ne veut pas dire qu’ils sont affichés correctement. Seuls les caractères prévu dans la police de caractère sont affichés. A noter que Google propose maintenant une police universelle pour unicode, mais que sa taille est dissuasive…

Pour limiter les problèmes, il est conseiller d’utiliser le même encodage (UTF8) de la production des caractères à l’affichage à l’écran. En développement Web, cela signifie généralement :

  • Le navigateur (saisie de valeur par exemple)
  • Le code serveur (php ou autre)
  • La base de donnée (mysql)
  • L’entête et le mime type html
  • Eventuellement le terminal

Il est à noter qu’il possible de convertir les caractères de n’importe quelle page de caractère vers utf8, mais à condition que l’encodage de la source soit bien identifié et précisé. Une des erreurs les plus fréquentes est par exemple d’interpréter de l’utf8 comme étant de l’iso-latin1, et donc de transformer tous les caractères…

Il est donc indispensable de s’assurer de la bonne configuration de tous les éléments d’une application web si l’on souhaite éviter les problèmes.

En particulier, sous MySQL, il ne faut pas oublier de configurer l’encodage et éventuellement l’interclassement. Attention, l’utf8 de Mysql est en fait par défaut une version limitée aux codes de 3 octets, et risque donc de poser problème si certains codes sont insérés (parfois même des failles de sécurité !). Utilisez donc utf8mb4, qui gère ce que utf8 devrait gérer…

Revue de presse

MySql TP 2 — Encodage et interclassement

Connection par mysql

Donnez-vous les droits en écriture sur le fichier .my.cnf (avec chmod u+w .my.cnf), puis corrigez votre mot de passe. Vous pouvez maintenant vous connecter au server sql directement avec la commande mysql.

Encodages disponibles

Affichez la liste des encodages/jeux de caractères disponibles avec la commande suivante :


SELECT * FROM Information_Schema.Character_Sets;

  1. Combien d’octets utilise au maximum un caractère en utf8 ? en latin1 ?
  2. Quel est l’interclassement par défaut pour latin1 ?
  3. Quels jeux de caractère unicodes sont proposés ? (utilisez like « %Unicode% »)
  4. Est-ce que le format utf8 proposé par MySql peut gérer tous les caractères unicodes ?
  5. Quel encodage est à préférer pour gérer tous les carcatères ? Combien d’octets par caractères au maximum ?

Il est possible d’afficher l’ensemble des interclassements possibles (collate en anglais) de la manière suivante :


SHOW COLLATION;

Vous pouvez restreindre l’affichage aux lignes concernant le jeu de caractère latin1 en ajoutant LIKE ‘latin1%’. Ceci devrait vous expliquer pourquoi, même pour des bases en Français, c’est généralement l’interclassement suédois qui s’affiche…

A noter que pour un respect strict des règles de collations en Français, il n’y a pas de solution parfaite sous MySQL. Donc, si vous avez un site officiel à développer pour l’administration Française, il est fort probable que l’utilisation de MySQL soit à exclure directement. Référence :
Article : MySQL et ses collations

Encodage de la console

Un moyen simple de tester l’encodage réel de la console est d’afficher une même chaîne tapée (ou collée) sur la console dans différents encodages.
Cela se fait en préfixant directement la chaîne par l’encodage souhaité, précédé d’un ‘_’ :


SELECT _latin1'été', _utf8'été', _cp850'été';

Si certains caractères sont encodés différemment dans les différents jeux de caractère, celui utilisé pour la saisie sera le seul correct à l’affichage.
A votre avis, que se passe-t-il pour les autres ? Avez-vous déjà rencontré ce genre d’affichage ?

Effet d’un mauvaise configuration

(Cette partie est largement inspirée de cet article)

La commande SET NAMES permet de définir le jeu de caractère des caractère saisis (ou envoyés) à MySQL. Les caractère des champs textes sont donc traduit du jeu indiqué avec SET NAME dans le jeu souhaité pour le champ, précisé dans la structure de la table.
Pour tester l’effet de d’une erreur de configuration, on va créer une table, avec deux champs encodé différemment, et on va insérer la même chaîne, avec accents, dans les deux champs. Celaa sera fait une fois avec utf8, et une fois avec latin.
(Le texte de test est un pangramme, trouvé sur Wikipédia )

DROP TABLE IF EXISTS test_strings;
CREATE TABLE test_strings (
    id int not null auto_increment primary key,
    latin_text  text charset latin1,
    utf_text    text charset utf8,
    comments    text
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;

SET NAMES latin1;
INSERT INTO test_strings (id, latin_text, utf_text, comments) VALUES
    (1, "Dès Noël où un zéphyr haï me vêt de glaçons würmiens je dîne d’exquis rôtis de bœuf au kir à l’aÿ",
 "Dès Noël où un zéphyr haï me vêt de glaçons würmiens je dîne d’exquis rôtis de bœuf au kir à l’aÿ",
"NAMES=latin1");

SET NAMES utf8;
INSERT INTO test_strings (id, latin_text, utf_text, comments) VALUES
    (2, "Dès Noël où un zéphyr haï me vêt de glaçons würmiens je dîne d’exquis rôtis de bœuf au kir à l’aÿ",
 "Dès Noël où un zéphyr haï me vêt de glaçons würmiens je dîne d’exquis rôtis de bœuf au kir à l’aÿ",
"NAMES=utf8");

Ensuite, on va consulter la table et afficher le contenu, en mode utf8 et en mode latin1, avec le code suivant :


SET NAMES latin1;
SELECT latin_text, utf_text, comments,
HEX(latin_text), HEX(utf_text), LENGTH(latin_text), LENGTH(utf_text)
FROM test_strings;

Puis la même chose en utf8


SET NAMES utf8;
SELECT latin_text, utf_text, comments,
HEX(latin_text), HEX(utf_text), LENGTH(latin_text), LENGTH(utf_text)
FROM test_strings;

On a ainsi obtenu 8 cas, correspondant aux combinaisons des paramètres suivants (pouvant chacun être latin8 ou utf8) : jeu de caractère supposé de l’entrée, jeu de caractère du champ, jeu de caractère de l’affichage.

  • Pour quelles conbinaisons est-ce que l’affichage était correct ?
  • Indiquez dans chaque cas les transcodages qui ont été effectués
  • Dans lequel(s) de ce(s) cas est-ce que l’ensemble des paramètres est corrects ?

Remarque : en théorie, il faudrait utiliser utf8mb4, mais pour simplifier un peu, et vu que le test ne comporte pas de caractères disponibles uniquement en utf8mb4, c’est utf8 qui a été utilisé.