Manuel MySQL pdf enjeux et pratiques


Télécharger Manuel MySQL pdf enjeux et pratiques
3.53.5 étoiles sur 5 a partir de 1 votes.
Votez ce document:

Télécharger aussi :


Manuel MySQL pdf enjeux et pratiques

...

1.7.3 Extensions de MySQL à la norme ANSI SQL92

Le serveur MySQL inclut des extensions que vous ne trouverez probablement pas dans les autres bases de données. Soyez prévenus que si vous les utilisez, votre code ne sera probablement pas portable sur d’autres serveurs SQL. Dans certains cas, vous pouvez écrire du code qui inclut des spécificités de MySQL, mais qui restent portables, en les incluant dans des commentaires de la forme /*! ... */. Dans ce cas, le serveur MySQL va analyser la

chaine et exécuter le code à l’intérieur de ces commentaires comme une commande normale, mais d’autres serveurs ignoreront ces commentaires. Par exemple :

SELECT /*! STRAIGHT_JOIN */ col_name FROM table1,table2 WHERE ...

Si vous ajoutez le numéro de version après le point d’exclamation '!', la syntaxe sera exécutée uniquement si la version du serveur MySQL est égale ou plus récente que le numéro de version utilisé.

CREATE /*!32302 TEMPORARY */ TABLE t (a int);

Cela signifie que si vous avez la version 3.23.02 ou plus récente, le serveur MySQL va utiliser le mot réserv´ TEMPORARY.

Voici une liste des apports spécifiques de MySQL :

²  Les types de colonnes MEDIUMINT, SET, ENUM et les types BLOB et TEXT.

²  Les attributs de champs AUTO_INCREMENT, BINARY, NULL, UNSIGNED et ZEROFILL.

² Toutes les comparaisons de chaines sont insensibles à la casse par défaut, et l’ordre de tri est détermin´ par le jeu de caractères courant (ISO-8859-1 Latin1 par défaut). Si vous en souhaitez un autre, il faut déclarer les colonnes avec l’attribut BINARY ou utiliser l’opérateur BINARY pour forcer les comparaisons à prendre en compte la casse, en fonction du jeu de caractères utilisé sur l’hôte du serveur MySQL.

² le serveur MySQL fait correspondre à chaque base un dossier dans le système de fichiers, et à chaque table, il fait correspondre un fichier, placé dans le dossier de base.

Ceci a quelques conséquences :

¡ Les noms des bases de données et des tables sont sensibles à la casse sur les systèmes d’exploitation qui ont des systèmes de fichiers sensibles à la casse (comme la plupart des systèmes Unix). Voir Section 6.1.3 [Name case sensitivity], page 408.

¡ Les bases de données, les tables, les index, les colonnes et les alias peuvent com-mencer par un chiffre (mais ne peuvent pas être constitués uniquement de chiffres).

¡ Vous pouvez utiliser les commandes systèmes standard pour sauver, renommer, déplacer, effacer et copier des tables. Par exemple, pour renommer une table, il suffit de renommer les fichiers ‘.MYD’, ‘.MYI’ et ‘.frm’ et de leur donner un nouveau nom.

² Dans une requête SQL, vous pouvez accéder à des tables situées dans différentes bases de données, avec la syntaxe db_name.tbl_name. Certains serveurs SQL fournissent la même fonctionnalité, mais l’appellent un User space. Le serveur MySQL ne supporte par les espaces de nom de tables, comme dans : create table ralph.my_table...IN my_tablespace.

² LIKE est possible avec des colonnes numériques.

² Utilisez INTO OUTFILE et STRAIGHT_JOIN dans les requêtes SELECT. Voir Section 6.4.1 [SELECT], page 481.

²  L’option SQL_SMALL_RESULT de la commande SELECT.

²  La commande EXPLAIN SELECT pour avoir le détail des jointures de tables.

²  L’utilisation de noms d’index, de préfixes d’index, et l’utilisation des mots-clés INDEX or KEY dans une commande de création de table CREATE TABLE. Voir Section 6.5.3 [CREATE TABLE], page 504.

²  L’utilisation des clauses TEMPORARY et IF NOT EXISTS avec CREATE TABLE.

²  L’utilisation de COUNT(DISTINCT list) où list contient plus d’un elément.

²  L’utilisation de CHANGE col_name, DROP col_name ou DROP INDEX, IGNORE ou RENAME dans une commande ALTER TABLE. Voir Section 6.5.4 [ALTER TABLE], page 512.

²  L’utilisation de RENAME TABLE. Voir Section 6.5.5 [RENAME TABLE], page 516.

²  L’utilisation de multiple ADD, ALTER, DROP et CHANGE dans les clauses de la commande

ALTER TABLE.

²  L’utilisation de DROP TABLE avec les mots-clés IF EXISTS.

²  Vous pouvez effacer plusieurs tables avec une seule commande DROP TABLE.

²  La clause LIMIT de la commande DELETE.

²  La clause DELAYED des commandes INSERT et REPLACE.

²  La clause LOW_PRIORITY des commandes INSERT, REPLACE, DELETE et UPDATE.

²  L’utilisation de la commande LOAD DATA INFILE. Dans de nombreuses situations, cette syntaxe est compatible avec la commande d’Oracle LOAD DATA INFILE. Voir Sec-tion 6.4.9 [LOAD DATA], page 497.

²  Les commandes ANALYZE TABLE, CHECK TABLE, OPTIMIZE TABLE et REPAIR TABLE.

²  La commande SHOW. Voir Section 4.5.6 [SHOW], page 267.

² Les chaines de caractères peuvent être soit délimitées par ‘"’, soit par ‘'’. Pas seulement par ‘'’.

² L’utilisation du caractère de protection ‘\’.

² La commande SET. Voir Section 5.5.6 [SET], page 396.

² Vous n’êtes pas obligé de nommer toutes les colonnes que vous sélectionnez dans la clause GROUP BY. Cela donne de meilleures performances pour certaines situations spécifiques, mais classiques. Voir Section 6.3.7 [Group by functions], page 479.

² Vous pouvez spécifier ASC ou DESC dans la clause GROUP BY.

² Pour aider les utilisateurs qui viennent d’autres environnements SQL, le serveur MySQL

supporte des alias de nombreuses fonctions. Par exemple, toutes les fonctions de chaines de caractères supportent simultanément les syntaxes ANSI SQL et ODBC.

²  Le serveur MySQL comprend les opérateurs || et && comme opérateurs logiques OR et AND, comme en langage C. Pour le serveur MySQL, les opérateurs || et OR sont synonymes, ainsi que && et AND. En conséquence, MySQL ne supporte pas l’opérateur de

concaténation de chaines ANSI SQL ||. Utilisez plutôt la fonction CONCAT(). Comme CONCAT() prend un nombre illimité d’arguments, il est facile de convertir des expressions utilisant ||, pour qu’elles fonctionnent sur le serveur MySQL.

²  CREATE DATABASE et DROP DATABASE. Voir Section 6.5.1 [CREATE DATABASE], page 503.

²  L’opérateur % est synonyme de MOD(). C’est à dire que N % M est équivalent à MOD(N,M). % est supporté pour les programmeurs C, et pour la compatibilité avec PostgreSQL.

²  Les opérateurs =, <>, <= ,<, >=,>, <<, >>, <=>, AND, OR ou LIKE peuvent être utilisés pour les comparaisons de colonnes à gauche de la clause FROM dans les commandes SELECT. Par exemple :

mysql> SELECT col1=1 AND col2=2 FROM tbl_name;

²  La fonction LAST_INSERT_ID(). Voir Section 8.4.3.30 [mysql_insert_id()], page 635.

²  Les opérateurs d’expressions régulières étendus REGEXP et NOT REGEXP.

²  CONCAT() et CHAR() avec un argument ou plus de deux arguments. Avec le serveur MySQL, ces fonctions peuvent prendre n’importe quel nombre d’arguments.

²  Les fonctions BIT_COUNT(), CASE, ELT(), FROM_DAYS(), FORMAT(), IF(), PASSWORD(), ENCRYPT(), MD5(), ENCODE(), DECODE(), PERIOD_ADD(), PERIOD_DIFF(), TO_DAYS() et WEEKDAY().

² L’utilisation de la fonction TRIM() pour réduire les chaines. L’ANSI SQL ne supporte que les suppressions de caractères uniques.

² Les fonctions de groupe de la clause GROUP BY STD(), BIT_OR() et BIT_AND().

² L’utilisation de REPLACE a` la place de DELETE + INSERT. Voir Section 6.4.8 [REPLACE], page 496.

² Les commandes FLUSH, RESET et DO.

² La possibilité de modifier les variables dans les commandes avec l’opérateur := :

SELECT @a:=SUM(total),@b=COUNT(*),@ a/@b AS avg FROM test_table; SELECT @t1:=(@t2:=1) [email protected]:=4,@t1,@t2,@t3;

1.7.4 Différences de MySQL avec ANSI SQL92

Nous tâchons de rendre le serveur MySQL compatible avec le standard ANSI SQL, et le standard ODBC SQL, mais dans certains cas, MySQL se comporte différemment.

²  Pour les colonnes de type VARCHAR, les espaces terminaux sont supprimés lors du stock-age de la valeur. Voir Section 1.7.5 [Bugs], page 43.

²  Dans certains cas, les colonnes CHAR sont transformées automatiquement en colonnes VARCHAR. Voir Section 6.5.3.1 [Silent column changes], page 511.

²  Les droits d’un utilisateur sur une table ne sont pas supprimés si la table est détruite. Vous devez explicitement utiliser la commande REVOKE pour supprimer les droits d’un utilisateur sur une table. Voir Section 4.3.1 [GRANT], page 226.

²  NULL AND FALSE donnera la valeur NULL et non pas FALSE. Nous pensons que dans cette situation, il n’est pas bon d’évaluer de nombreuses conditions supplémentaires.

1.7.4.1 Sous sélections (SubSELECTs)

Jusqu’à la version 4.0, le serveur MySQL ne prenait en charge que les requêtes imbriquées de la forme INSERT ... SELECT ... et REPLACE ... SELECT .... Vous pouvez néanmoins

utiliser la fonction IN() dans d’autres contextes. Les sous-requêtes sont implémentées dans l’arborescence de développement 4.1.

En attendant, vous pourrez dans la plupart des cas réécrire la requête sans sous-requêtes :

SELECT * FROM table1 WHERE id IN (SELECT id FROM table2);

Ceci peut être réécrit de la façon suivante :

SELECT table1.* FROM table1,table2 WHERE table1.id=table2.id;

Les requêtes :

SELECT * FROM table1 WHERE id NOT IN (SELECT id FROM table2); SELECT * FROM table1 WHERE NOT EXISTS (SELECT id FROM table2

WHERE table1.id=table2.id);

peuvent être réécrites ainsi :

SELECT table1.* FROM table1 LEFT JOIN table2 ON table1.id=table2.id WHERE table2.id IS NULL;

Pour des requêtes plus complexes, vous pouvez généralement créer des tables temporaires contenant les sous-requêtes. Cependant, dans certains cas cette option ne fonctionnera pas. Le cas le plus fréquent se produit avec les instructions DELETE, pour lesquelles le SQL standard ne prend pas en charge les jointures (sauf dans les sous Select). Dans une telle situation, en attendant que les sous-requêtes soient prises en charge par MySQL Server, vous disposez de deux options.

La première option consiste à utiliser un langage de programmation procédural (tel que Perl ou PHP) afin de soumettre une requête SELECT pour obtenir les clés primaires des enregistrements à supprimer. On utilise ensuite ces valeurs pour construire l’instruction DELETE (DELETE FROM ... WHERE ... IN (key1,key2, ...)).



La seconde option consiste à utiliser le SQL interactif pour construire automatiquement un ensemble d’instructions DELETE, grâce à l’extension MySQL CONCAT() (à la place de l’opérateur standard ||).

Par exemple :

SELECT CONCAT('DELETE FROM tab1 WHERE pkid = ', "'", tab1.pkid, "'", ';') FROM tab1, tab2

WHERE tab1.col1 = tab2.col2;

Vous pouvez placer cette requête dans un fichier script et rediriger son entrée vers l’interpréteur de ligne de commande mysql, tout en ouvrant un canal de communication (pipe) pour diriger sa sortie vers une seconde instance de l’interpréteur :

shell> mysql --skip-column-names mydb < myscript.sql | mysql mydb

La version 4.0 de MySQL prend en charge les instructions DELETE sur plusieurs tables pouvant être utilisées afin de supprimer efficacement des lignes à partir d’informations issues d’une table, voire de plusieurs tables en même temps.

1.7.4.2 SELECT INTO TABLE

 Le serveur MySQL ne supporte pas encore l’extension Oracle SQL : SELECT ... INTO TABLE

.... A la place, le serveur MySQL supporte la syntaxe ANSI SQL INSERT INTO ... SELECT

..., qui revient au même. Voir Section 6.4.3.1 [INSERT SELECT], page 491.

INSERT INTO tblTemp2 (fldID) SELECT tblTemp1.fldOrder_ID FROM tblTemp1 WHERE tblTemp1.fldOrder_ID > 100;

Vous pouvez aussi utiliser SELECT INTO OUTFILE... ou CREATE TABLE ... SELECT.

1.7.4.3 Transactions et opérations atomiques

Le serveur MySQL support les transactions avec les gestionnaires de tables InnoDB et BDB.

Voir Chapitre 7 [Table types], page 531. InnoDB dispose aussi de la compatibilité ACID.

Toutefois, les tables non transactionnelles de MySQL telles que MyISAM exploitent un autre concept pour assurer l’intégrité des données, appel´ “opérations atomiques”. Les opérations atomiques disposent d’une bien meilleure protection des données pour des performances également accrues. Comme MySQL supporte les deux méthodes, l’utilisateur est capable de choisir celle qui correspond à ses besoins, suivant qu’il a besoin de vitesse ou de sécurit´. Ce choix peut être fait table par table.

Comment exploiter les capacités de MySQL pour protéger l’intégrit´ des données, et comment ces fonctionnalités se comparent-elles avec les méthodes transactionnelles ?

  1. En mode transactionnel, si votre application a et´ écrite en dépendant de l’appel de ROLLBACK au lieu de COMMIT dans les situations critiques, les transactions sont plus plus pratiques. Les transactions s’assurent que les modifications non achevées ou les activités corrosives ne sont pas archivées dans la base. Le serveur a l’opportunité d’annuler automatiquement l’opération, et votre base de données est sauve.

Le serveur MySQL, dans la plupart des cas, vous permet de résoudre les problèmes potentiels en incluant de simples vérifications avant les modifications, et en exécutant des scripts simples pour vérifier l’intégrit´ de vos bases de données, ainsi que les in-cohérences, et pour réparer automatiquement les problèmes, ou encore vous alerter si une erreur est identifiée. Notez qu’en utilisant simplement le log de MySQL, ou en util-isant un log supplémentaire, vous pouvez normalement réparer à la perfection toutes les tables, sans aucune perte de données.

  1. Souvent, les modifications de données transactionnelles fatales peuvent être réécrites de manière atomique. En général, tous les problèmes d’intégrit´ que les transactions résolvent peuvent être corrigés avec la commande LOCK TABLES ou des modifications atomiques, qui assurent que vous n’aurez jamais d’annulation automatique de la base, ce qui est un problème commun des bases transactionnelles.
  2. Même un système transactionnel peut perdre des données si le serveur s’arrête. La différence entre les systèmes repose alors dans ce petit laps de temps où ils peuvent perdre des données. Aucun système n’est sécuris´ à 100 %, mais simplement “suff-isament sécurisé”. Même Oracle, réput´ pour être la plus sˆure des bases de données transactionnelles, est montré du doigt pour perdre des données dans ces situations.

Pour être tranquille avec MySQL, que vous utilisiez les tables transactionnelles ou pas, vous n’avez besoin que de sauvegardes et de logs de modifications. Avec ces deux outils, vous pourrez vous protéger de toutes les situations que vous pourriez rencontrer avec d’autres bases de données transactionnelles. De toute manière, il est bon d’avoir des sauvegardes, indépendamment de la base que vous utilisez.

La méthode transactionnelle a ses avantages et ses inconvénients. De nombreux utilisateurs et développeurs d’applications dépendent de la facilité de pallier un problème lorsqu’une annulation semble nécessaire ou presque. Cependant, même si vous êtes néophyte des opérations atomiques, ou plus familier avec les transactions, prenez en considération le gain de vitesse que les tables non transactionnelles offrent. Ces gains vont de 3 a 5 fois la vitesse des tables transactionnelles les plus rapides et les mieux optimisées.

Dans des situations où l’intégrit´ est de la plus grande importance, le serveur MySQL assure une intégrit´ du niveau des transactions, ou encore mieux avec les tables non trans-actionnelles. Si vous verrouillez les tables avec LOCK TABLES, toutes les modifications seront bloquées jusqu’à ce que la vérification d’intégrit´ soit faite (à comparer avec un verrou en écriture), les lectures et insertions sont toujours possibles. Les nouvelles lignes ne seront pas accessibles en lecture tant que le verrou n’aura pas et´ lev´. Avec INSERT DELAYED, vous pouvez faire attendre les insertions dans une pile, jusqu’à ce que les verrous soit levés, sans que le client n’attende cette levée de verrou. Voir Section 6.4.4 [INSERT DELAYED], page 491.

“Atomique”, avec le sens que nous lui donnons, n’a rien de magique. Ce terme signifie simplement que vous pouvez être certain que lorsque vous modifiez des données dans une table, aucun autre utilisateur ne peut interférer avec votre opération, et qu’il n’y aura pas d’annulation automatique (ce qui pourrait arriver avec des tables transactionnelles si nous ne sommes pas trop soigneux). Le serveur MySQL garantit aussi qu’il n’y aura pas de lectures erronées.

Voici quelques techniques pour travailler avec des tables non transactionnelles :

²  Les boucles qui requièrent les transactions peuvent normalement être implémentées avec la commande LOCK TABLES, et vous n’avez nul besoin de curseur lorsque vous modifiez des lignes à la volée.

²  Pour éviter d’utiliser l’annulation ROLLBACK, vous pouvez adopter la stratégie suivante :

  1. Utilisez la commande LOCK TABLES ... pour verrouiller toutes les tables que vous voulez utiliser.
  2. Testez vos conditions.
  3. Modifiez si tout est correct.
  4. Utilisez UNLOCK TABLES pour libérer vos tables.

Ceci est probablement une méthode bien plus rapide que ne le proposent les transac-tions, avec des annulations ROLLBACK possibles mais pas certaines. La seule situation que ce cas ne prend pas en compte est l’interruption du processus au milieu d’une mise a` jour. Dans ce cas, tous les verrous seront levés, mais certaines modifications peuvent ne pas avoir et´ exécutées.

²  Vous pouvez aussi utiliser des fonctions pour modifier des lignes en une seule opération. Vous pouvez créer une application très efficace en utilisant cette technique :

²  Modifiez les champs par rapport à leur valeur actuelle.

²  Modifiez uniquement les champs que vous avez réellement changé.

Par exemple, lorsque nous modifions les données d’un client, nous ne modifions que les données du client qui ont changé et nous vérifions uniquement si les données modifiées ou les données qui en dépendent ont changé comparativement aux données originales. Les tests sur les données modifiées sont faits avec la clause WHERE dans la commande

UPDATE. Si la ligne a et´ modifiée, nous indiquons au client : "Some of the data you have changed has been changed by another user". En français : "certaines données que vous voulez modifier ont et´ modifiées par un autre utilisateur". Puis nous affichons l’ancienne ligne et la nouvelle ligne, pour laisser l’utilisateur décider quelle version il veut utiliser.

Cela nous conduit à un résultat proche du verrouillage de ligne, mais en fait, c’est bien mieux, car nous ne modifions que les colonnes qui en ont besoin, en utilisant des valeurs relatives. Cela signifie qu’une commande UPDATE typique ressemble à ceci :

UPDATE tablename SET pay_back=pay_back+'relative change';

UPDATE customer

SET

customer_date='current_date',

address='new address',

phone='new phone',

dette=dette+'emprunt'

WHERE

customer_id=id AND address='old address' AND phone='old phone';

Comme vous pouvez le voir, c’est très efficace, et fonctionne même si un autre client a modifié la valeur pay_back ou dette.

²  Dans de nombreuses situations, les utilisateurs ont souhaité les commandes ROLLBACK et/ou LOCK TABLES afin de gérer des identifiant uniques pour certaines tables. Ils peu-vent être gérés bien plus efficacement en utilisant une colonne de type AUTO_INCREMENT, en corrélation avec la fonction LAST_INSERT_ID() ou la fonction C mysql_insert_ id(). Voir Section 8.4.3.30 [mysql_insert_id()], page 635.

Vous pouvez éviter le verrouillage de ligne. Certaines situations le requièrent vraiment, mais elles sont rares. Les tables InnoDB supportent le verrouillage de ligne. Avec les tables MyISAM, vous pouvez utiliser une colonne de type flag, et faire ceci :

UPDATE tbl_name SET row_flag=1 WHERE id=ID;

MySQL retournera 1 pour le nombre de lignes affectées si la ligne a et´ trouvée, car row_flag ne vaut pas déjà 1 dans la ligne originale.

Vous pouvez comprendre la requête ci-dessus comme si le serveur MySQL avait utilisé la commande suivante :

UPDATE tbl_name SET row_flag=1 WHERE id=ID AND row_flag <> 1;

1.7.4.4 Procédures stockées et triggers

Une procédure stockée est une liste de commandes SQL qui peuvent être compilées et stockées sur le serveurs. Une fois que cela est fait, les clients n’ont pas besoin de soumettre a` nouveau toute la commande, mais font simplement référence à la procédure stockée. Cela conduit à des performances bien meilleures, car les commandes n’ont pas à être analysées plusieurs fois, et que bien moins d’informations transitent sur le réseau. Vous pouvez aussi élevez le niveau conceptuel de votre application en mettant en place des librairies de fonctions sur le serveur.

Un trigger est une procédure stockée qui est activée lorsqu’un evénement particulier survient. Par exemple, vous pouvez installer une procédure stockée qui est déclenchée dès qu’une ligne est effacée dans une table d’achat, pour que le client soit automatiquement effacé si tous ses achats sont effacés.



La future version du langage sera capable de supporter les procédures stockées. Notre objectif est d’impolémenter les procédures stockées pour la version 5.0 du serveur MySQL. Nous nous pencherons aussi sur les triggers.

1.7.4.5 Les clés étrangères

Notez que les clés en SQL ne sont pas utilisées pour joindre des tables, mais pour assurer l’intégrité référentielle (contraintes de clés étrangères). Si vous voulez obtenir des résultats issus de tables multiples avec une commande SELECT, vous allez joindre les tables comme ceci :

SELECT * FROM table1,table2 WHERE table1.id = table2.id;

Voir Section 6.4.1.1 [JOIN], page 486. Voir Section 3.5.6 [example-Foreign keys], page 182.

En MySQL version 3.23.44 et plus récentes, les tables InnoDB supportent les vérifications d’intégrit´ référentielles. Voir Section 7.5 [InnoDB], page 544. Pour les autres types de tables, le serveur mySQL accepte la syntaxe FOREIGN KEY dans la commande CREATE TABLE, mais ne la prend pas en compte.

La syntaxe FOREIGN KEY dans la clause ON DELETE ... est principalement utilisée pour des raisons de documentation. Certaines applications ODBC peuvent utiliser cette clause pour produire des conditions WHERE automatiques, mais c’est généralement simple à éviter. FOREIGN KEY est parfois utilisé pour vérifier une contrainte, mais cette vérification n’est pas nécessaire en pratique, si les lignes sont insérées dans la table dans le bon ordre.

Avec le serveur MySQL, vous pouvez contourner le problème de l’absence de la clause ON DELETE ... en ajoutant la commande DELETE appropriée dans l’application, lorsque vous effacez une ligne dans une table qui a une clé étrangère. En pratique, cette technique est aussi rapide (et même parfois plus rapide), et bien plus portable que l’utilisation des clés étrangères.

Avec le serveur MySQL 4.0, vous pouvez utiliser les commandes d’effacement multi-tables pour effacer des lignes dans plusieurs tables en une seule commande. Voir Section 6.4.6 [DELETE], page 494.

Dans un futur proche, nous allons étendre l’implémentation de la clause FOREIGN KEY pour que cette information soit stockée dans le fichier de spécification des tables et puisse être lu par mysqldump et ODBC. Dans une étape ultérieure, nous allons implémenter les contraintes de clé étrangère pour les applications qui ne peuvent pas être codées sans.

Gardez bien en tête que les clés étrangères sont souvent méconnues, ce qui peut causer de graves problèmes. Même lorsqu’elles sont utilisées correctement, ce n’est pas une solution magique pour les problèmes d’intégrité référentielle, même si cela simplifie parfois les choses.

Voici des avantages aux contraintes de clés étrangères :

²  En supposant que les relations soient proprement conçues, les clés étrangères rendent plus difficile pour un programmeur d’insérer des valeurs incohérentes dans la base.

²  L’utilisation des modifications et effacement en cascade simplifie le code du client.

 42  MySQL Technical Reference for Version 5.0.0-alpha

²  Les règles de clés étrangères proprement conçues aident à la documentation des relations entre les tables.

Inconvénients :

²  Les erreurs, qui sont faciles à faire durant la conception des relations, peuvent causer des problèmes graves : par exemple, des règles de contrainte circulaires, ou des combi-naisons erronées d’effacement.

²  Une application correctement écrite s’assure d’elle-même de l’intégrit´ référentielle des données avant d’exécuter une requête. De ce fait, les vérifications supplémentaires de la base sont inutiles et réduisent les performances de l’application.

²  Il n’est pas rare pour un administrateur de bases de données de faire une topologie complexe des relations entre tables qui rendent très complexe, voire impossible de restaurer des tables.

1.7.4.6 Les vues

Il est prévu d’implémenter les vues dans la version 5.0 du serveur MySQL.

Les vues sont la plupart du temps utiles pour donner accès aux utilisateurs à un ensemble de relations représentées par une table (en mode inaltérable). Beaucoup de bases de données SQL ne permettent pas de mettre à jour les lignes dans une vue, vous devez alors faire les mises à jour dans les tables séparées.

Vu que le serveur MySQL est la plupart du temps utilisé dans des applications où le développeur à un contrôle total sur l’utilisation de la base de données, la plupart de nos utilisateurs n’ont pas considér´ les vues comme très importantes (du moins, personne n’a et´ assez intéressé à ce sujet pour être prêt à financer l’implémentation des vues).

On n’a pas forcément besoin des vues pour restreindre l’accès aux colonnes, et ce, parce que MySQL a un système de droits très sophistiqué. Voir Section 4.2 [Privilege system], page 203.

1.7.4.7 ‘--’ comme début de commentaire

Certaines bases de données SQL utilisent ‘--’ comme début de commentaire. Le serveur MySQL utilise ‘#’. Vous pouvez aussi utiliser la syntaxe du langage C /* ceci est un commentaire */. Voir Section 6.1.6 [Comments], page 413.

MySQL version 3.23.3 et plus récent supporte les commentaires de type ‘--’, si ce commen-taire est suivi d’un espace. Ceci est dˆu au fait que ce type de commentaire a causé beaucoup de problèmes avec les requêtes générées automatiquement, qui contiennent du code tel que celui-ci, où nous insérons automatiquement la valeur du paiement dans la table !payment! :

UPDATE tbl_name SET credit=credit-!payment!

Pensez à ce qui se passe lorsque la valeur de payment est négative. Comme 1--1 est valide en SQL, la conséquence est de commencer un commentaire ‘--’.

En utilisant notre implémentation des commentaires avec le serveur MySQL version 3.23.3 et plus récent, 1-- ceci est un commentaire ne pose pas ce type de problème.

Une autre fonctionnalité supplémentaire est que le client en ligne de commande mysql supprime toutes les lignes qui commencent par ‘--’.

Les informations suivantes sont destinées aux utilisateurs de MySQL avec des versions antérieures à la version 3.23.3 :

Si vous avez un programme SQL dans un fichier texte qui contient des commentaires au format ‘--’, il est recommand´ d’utiliser :

shell> replace " --" " #" < text-file-with-funny-comments.sql \

|  mysql database

au lieu du classique :

shell> mysql database < text-file-with-funny-comments.sql

Vous pouvez aussi éditer le fichier de commande “lui-même” pour remplacer les commen-taires ‘--’ par des commentaires ‘#’ :

shell> replace " --" " #" -- text-file-with-funny-comments.sql

Puis, rétablissez-les avec :

shell> replace " #" " --" -- text-file-with-funny-comments.sql

1.7.5 Erreurs connues et problèmes de conceptions de MySQL

Les problèmes suivants sont connus, et sont en tête de liste pour être corrigés :

²  ANALYZE TABLE, sur une table de type BDB, peut rendre la table inutilisable, dans certains cas, jusqu’au prochain redémarrage de mysqld. Lorsque cela survient, vous rencontrez les erreurs suivantes dans le fichier d’erreur MySQL :

²  N’exécutez pas de commande ALTER TABLE sur une table BDB sur laquelle vous avez exécut´ des transactions à plusieurs commandes, jusqu’à ce que ces transactions soient achevées : la transaction sera probablement ignorée.

²  ANALYZE TABLE, OPTIMIZE TABLE et REPAIR TABLE peuvent causer des problèmes sur les tables avec lesquelles vous utilisez la commande INSERT DELAYED.

²  Faire un LOCK TABLE ... et FLUSH TABLES ... ne vous garantit pas qu’il n’y a pas une transaction en court sur la table.

²  Les tables BDB sont lentes à ouvrir. Si vous avez de nombreuses tables BDB dans une base, cela prendra du temps au client mysql pour accéder à la base si vous n’utilisez pas l’option -A, ou si vous utilisez la commande rehash. C’est particulièrement vrai si vous n’avez pas de cache de table important.

Les problèmes suivants sont connus et seront corrigés en leur temps :

²  Lorsque vous utilisez la commande SET CHARACTER SET, il n’est pas possible d’utiliser les caractères traduits dans les noms de bases, de tables ou de colonnes.

²  Il n’est pas possible d’utiliser _ ou % avec la commande ESCAPE dans la clause LIKE...

ESCAPE.

²  Si vous avez une colonne de type DECIMAL avec un nombre stocké dans un autre format (+01.00, 1.00, 01.00), GROUP BY peut considérer ces valeurs comme différentes.

²  Lorsque DELETE FROM merge_table est utilisé sans la clause WHERE, elle va simplement effacer le fichier de la table, et ne pas effacer les tables associées.

²  Vous ne pouvez pas compiler le serveur dans un autre dossier lorsque vous utilisez les MIT-pthreads. Comme cela requiert une modification des MIT-pthreads, nous ne corrigerons pas ce problème. Voir Section 2.3.6 [MIT-pthreads], page 95.

²  Les valeurs de type BLOB ne peuvent pas être utilisées “correctement” dans les clauses GROUP BY ou ORDER BY ou DISTINCT. Seuls, les max_sort_length premiers octets (par défaut, 1024) seront utilisés pour les comparaisons de BLOB. Ceci peut être modifié avec l’option -O max_sort_length de mysqld. Un palliatif à ce problème est d’utiliser

une sous partie de chaine : SELECT DISTINCT LEFT(blob,2048) FROM tbl_name.

²  Les calculs sont faits avec des BIGINT ou DOUBLE (les deux sont normalement de 64 bits). La précision dépend alors de la fonction utilisée. La règle générale est que les fonctions de bits utilisent la précision des BIGINT, IF et ELT() utilisent la précision des BIGINT ou DOUBLE, et les autres utilisent la précision des DOUBLE. Il faut donc éviter d’utiliser les entiers non signés de grande taille, surtout s’ils dépassent la taille de 63 bits (9223372036854775807) pour toute autre fonction que les champs de bits ! La version 4.0 gère bien mieux les BIGINT que la 3.23.

² Toutes les colonnes de type chaines, hormis les BLOB et TEXT, voient automatiquement leurs caractères blancs finaux supprimés. Pour le type CHAR c’est correct, et c’est considér´ comme une fonctionnalité par la norme ANSI SQL92. Le hic est que pour le serveur MySQL les colonnes VARCHAR sont traitées de la même façon.

² Vous ne pouvez avoir que des colonnes de taille 255 pour les ENUM et SET.

² Avec les fonctions d’agrégation MIN(), MAX() et compagnie, MySQL compare actuellement les colonnes de type ENUM et SET par leur valeur de chaine, plutôt que par leur position relative dans l’ensemble.

²  safe_mysqld redirige tous les messages de mysqld vers le log mysqld. Le problème est que si vous exécutez mysqladmin refresh pour fermer et ouvrir à nouveau l’historique, stdout et stderr sont toujours redirigés vers l’ancien log. Si vous utilisez --log extensivement, vous devriez éditer safe_mysqld pour loger vers ‘'hostname'.err’ au lieu de ‘'hostname'.log’, de façon à pouvoir facilement récupérer la place de l’ancien log, en effaçant les vieux, et en exécutant mysqladmin refresh.



²  Dans la commande UPDATE, les colonnes sont modifiées de gauche à droite. Si vous faite référence à une colonne modifiée, vous obtiendrez sa valeur modifiée, plutôt que sa valeur originale. Par exemple :

mysql> UPDATE tbl_name SET KEY=KEY+1,KEY=KEY+1; Cette commande va modifier la colonne KEY avec 2 au lieu de 1.

²  Vous ne pouvez pas utiliser les tables temporaires plus d’une fois dans la même requête. Par exemple, cette commande ne fonctionne pas :

mysql> SELECT * FROM temporary_table, temporary_table AS t2;

²  RENAME ne fonctionne pas avec les tables TEMPORARY, ou les tables utilisées dans un rassemblement (MERGE).

²  L’optimiseur peut gérer la clause DISTINCT différemment si vous utilisez des colonnes cachées dans une jointure. Dans une jointure, les colonnes cachées sont comptées comme une partie du résultat (même si elles ne sont pas montrées), tandis que dans les requêtes normales, les colonnes cachées ne participent pas aux DISTINCT. Nous allons probablement modifier ceci dans le futur, pour ne jamais exploiter les colonnes cachées avec DISTINCT.

Voici un exemple :

SELECT DISTINCT mp3id FROM band_downloads WHERE userid = 9 ORDER BY id DESC;

et

SELECT DISTINCT band_downloads.mp3id

FROM band_downloads,band_mp3

WHERE band_downloads.userid = 9

AND band_mp3.id = band_downloads.mp3id

ORDER BY band_downloads.id DESC;

Dans le second cas, MySQL 3.23.x pourrait vous donner deux lignes identiques dans le résultat (car les lignes cachées id diffèrent).

Notez que cela n’arrive que pour les requêtes où vous n’avez pas de colonnes de la clause ORDER BY dans le résultat, ce que vous ne pourriez pas faire en ANSI SQL.

²  Comme le serveur MySQL vous permet de travailler avec des tables qui ne supportent pas les transactions, et donc, l’annulation rollback, certains comportements sont déférents avec MySQL d’avec d’autres serveurs SQL. C’est nécessaire pour s’assurer que MySQL n’a jamais besoin d’annuler une commande SQL. Cela peut sembler un peu étrange au moment ou les colonnes doivent être vérifiées par l’application, mais cela vous fournit une accélération notable, à cause d’optimisations qui ne pourraient pas avoir lieu ailleurs.

Si vous donnez une valeur incorrecte à une colonne, MySQL va stocker le meilleur code possible dans la colonne, au lieu d’annuler la transaction :

¡  Si vous essayez de stocker une valeur qui est hors de l’intervalle de validité dans une colonne numérique, MySQL va stocker la plus petite ou la plus grande valeur qu’il connaisse dans cette colonne.

¡ Si vous essayez de stocker une chaine qui ne commence pas pas un chiffre dans une colonne numérique, MySQL va stocker 0.

¡ Si  vous  essayez de stocker la valeur NULL  dans une  colonne  qui n’accepte pas la valeur  NULL, le serveur MySQL  va  stocker 0    ˆ

            ou '' (chaine vide) à

la place :  ce    comportement peut être  modifié avec  l’option  de compilation

-DDONT    USE    DEFAULT    FIELDS).       

¡  MySQL vous autorise le stockage de dates erronées dans les colonnes de type DATE et DATETIME (comme 2000-02-31 ou 2000-02-00). L’idée est que ce n’est pas au serveur SQL de faire le travail de validation. Si MySQL peut stocker une date, et relire exactement cette date, alors MySQL va stocker cette date. Si la date est totalement fausse (hors de l’intervalle de validité du serveur), la valeur spéciale 0000-00-00 sera utilisée.

¡  Si vous utilisez une valeur non supportée avec une colonne de type ENUM, la valeur stockée sera la chaine vide, de valeur numérique 0.

¡  Si vous utilisez une valeur invalide dans une colonne de type SET, la valeur sera ignorée.

²  Si vous exécutez une PROCEDURE sur une requête qui retourne un résultat vide, dans certains cas, PROCEDURE ne transformera pas les colonnes.

²  La création de table de type MERGE ne vérifie pas si les tables sous-jacentes sont de type compatible.

²  Le serveur MySQL ne supporte pas encore les valeurs Server NaN, -Inf et Inf pour les doubles. Utiliser ces valeurs générera des problèmes lorsque vous essayerez d’exporter et d’importer des données. Comme solution temporaire, vous pouvez remplacer NaN par NULL (si possible) et -Inf et Inf par les valeurs maximales possibles des colonnes double.

²  Les valeurs négatives passées à LIMIT sont traitées comme des valeurs positives.

²  Si vous utilisez la commande ALTER TABLE pour ajouter un index de type UNIQUE à un table utilisée dans un rassemblement de tables MERGE, puis que vous utilisez ALTER TABLE pour ajouter un index normal à la table MERGE, l’ordre des clés sera différent pour les tables s’il y avait déjà une ancienne clé qui n’était pas unique. Ceci est dˆu au fait que ALTER TABLE place les clés UNIQUE avant les clés normales, pour être capable de détecter les clés doublons plus vite.

Les bogues suivants sont connus dans les anciennes versions de MySQL :

²  Vous pouvez obtenir un thread gel´ si vous utilisez la commande DROP TABLE sur une table qui fait partie des tables verrouillées par LOCK TABLES.

²  Dans les cas suivants, vous pouvez obtenir un crash :

¡  Le gestionnaire d’insertions retardées a déjà des insertions en attente pour une table.

¡  LOCK table avec WRITE.

¡  FLUSH TABLES.

²  Pour les versions de MySQL avant la 3.23.2, une commande UPDATE qui modifiait une clé avec la clause WHERE sur la même clé, pouvait échouer car la même clé était utilisée pour rechercher les lignes et la même ligne pouvait être trouvée plusieurs fois :

UPDATE tbl_name SET KEY=KEY+1 WHERE KEY > 100; Un palliatif est :

mysql> UPDATE tbl_name SET KEY=KEY+1 WHERE KEY+0 > 100;

Cela fonctionnera, car MySQL ne va pas utiliser d’index sur une expression dans la clause WHERE.

²  Avant la version 3.23 de MySQL, tous les types numériques étaient traités comme des champs à virgule fixe. Cela signifie que vous deviez spécifier le nombre de décimales que le champ devait avoir. Tous les résultats étaient retournés avec le nombre correct de décimales.

Pour les bogues spécifiques aux systèmes d’exploitation, voyez la section sur la compilation et le port.

1.8 Les évolutions de MySQL (la liste des tâches)

 Cette section liste les fonctionnalités que nous prévoyons d’ajouter à MySQL.

La liste est grossièrement réalisée dans l’ordre de priorité. Si vous voulez modifier cet ordre, achetez une licence de support, et dites-nous ce que vous souhaitez voir en premier. Voir Section 1.4 [Licensing and Support], page 16.

L’objectif est de supporter complètement la norme ANSI SQL99, tout en lui ajoutant de nombreuses améliorations. Le défi est de faire tout cela sans sacrifier la vitesse d’exécution, ni la qualité du code.

1.8.1 Ce que devrait inclure la version 4.0

Tout est fait. Nous ne fournissons plus que des corrections de bogues pour MySQL 4.0. Voir Section D.2 [News-4.0.x], page 727. Le développement est passé à la version 4.1

1.8.2 Ce qui est prévu pour la version 4.1

Les fonctionnalités suivantes sont prévues pour être ajoutées dès la version 4.1. Notez que comme nous avons de nombreux développeurs qui travaillent sur différents projets, il y aura aussi de nombreuses améliorations. Il y a aussi de faibles chances pour que ces fonctionnalités soient ajoutées à la version 4.0. Certaines fonctionnalités sont déjà installées dans la version MySQL 4.1.

²  Sous-sélections.

SELECT id FROM t WHERE grp IN (SELECT grp FROM g WHERE u > 100);

²  Un nouveau format de définition de table (fichiers ‘.frm’). Il nous permettra de ne pas être à court de place lors de l’ajout de nouvelles options. Les anciens fichiers sont toujours compatibles avec la version 4.0. Toutes les nouvelles tables utiliseront ce nouveau format.

Le nouveau format de fichier nous permettra d’ajouter de nouveaux types de colonnes, plus d’options pour les clés, et alloueront de la place pour les définitions des FOREIGN KEY.

²  SHOW COLUMNS FROM table_name (utilisé par le client mysql pour afficher le détail des colonnes) ne devrait pas ouvrir la table, mais uniquement le fichier de définition. Cela consommera moins de mémoire, et sera plus rapide.

²  Le support des clés étrangères pour les tables MyISAM, incluant les effacement en cas-cade.

²  La réplication infaillible.

²  La réplication devrait fonctionner avec la fonction RAND() et la variable utilisateur @var.

²  Sauvegarde à chaud, avec faible réduction des performances. La sauvegarde en ligne

rendra simple le nouveau système de réplication, sans éteindre le serveur maitre.

²  Tables dérivées :

SELECT a.col1, b.col2

FROM (SELECT MAX(col1) AS col1 FROM root_table) a, other_table b

WHERE a.col1=b.col1;



425