Chapitre 13. Syntaxe des commandes SQL

Table des matières

13.1. Manipulation de données : SELECT, INSERT, UPDATE, DELETE
13.1.1. Syntaxe de DELETE
13.1.2. Syntaxe de DO
13.1.3. Syntaxe de HANDLER
13.1.4. Syntaxe de INSERT
13.1.5. Syntaxe de LOAD DATA INFILE
13.1.6. Syntaxe de REPLACE
13.1.7. Syntaxe de SELECT
13.1.8. Sous-sélections (SubSELECT)
13.1.9. Syntaxe de TRUNCATE
13.1.10. Syntaxe de UPDATE
13.2. Définition de données : CREATE, DROP, ALTER
13.2.1. Syntaxe de ALTER DATABASE
13.2.2. Syntaxe de ALTER TABLE
13.2.3. Syntaxe de CREATE DATABASE
13.2.4. Syntaxe de CREATE INDEX
13.2.5. Syntaxe de CREATE TABLE
13.2.6. Syntaxe de DROP DATABASE
13.2.7. Syntaxe de DROP INDEX
13.2.8. Syntaxe de DROP TABLE
13.2.9. Syntaxe de RENAME TABLE
13.3. Commandes de bases de l'utilisateur de MySQL
13.3.1. Syntaxe de DESCRIBE (obtenir des informations sur les colonnes)
13.3.2. Syntaxe de USE
13.4. Commandes relatives aux verrous et aux transactions
13.4.1. Syntaxes de START TRANSACTION, COMMIT et ROLLBACK
13.4.2. Commandes qui ne peuvent pas être annulées
13.4.3. Commandes qui peuvent causer une validation implicite
13.4.4. Syntaxe de SAVEPOINT et ROLLBACK TO SAVEPOINT
13.4.5. Syntaxe de LOCK TABLES/UNLOCK TABLES
13.4.6. Syntaxe de SET TRANSACTION
13.5. Référence de langage d'administration de la base de données
13.5.1. Commande de gestion des comptes utilisateurs
13.5.2. Commandes d'entretien des tables
13.5.3. Syntaxe de SHOW
13.5.4. Autres commandes d'administration
13.6. Commandes de réplication
13.6.1. Requêtes SQL pour contrôler les maîtres de réplication
13.6.2. Commandes SQL de contrôle des esclaves de réplication
13.7. Syntaxe SQL pour les commandes préparées

Ce chapitre décrit la syntaxe des commandes que MySQL supporte.

13.1. Manipulation de données : SELECT, INSERT, UPDATE, DELETE

13.1.1. Syntaxe de DELETE

DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM table_name
       [WHERE where_definition]
       [ORDER BY ...]
       [LIMIT row_count]

Syntaxe multi-tables :

DELETE [LOW_PRIORITY] [QUICK] [IGNORE] table_name[.*] [, table_name[.*] ...]
       FROM table-references
       [WHERE where_definition]

ou :

DELETE [LOW_PRIORITY] [QUICK] [IGNORE]
       FROM table_name[.*] [, table_name[.*] ...]
       USING table-references
       [WHERE where_definition]

DELETE efface les enregistrements de nom_de_table qui satisfont la condition donnée par clause_where, et retourne le nombre d'enregistrements effacés.

Si vous exécutez un DELETE sans clause WHERE, tous les enregistrements sont effacés. Si vous le faites en mode AUTOCOMMIT cela aura le même effet qu'un TRUNCATE. See Section 13.1.9, « Syntaxe de TRUNCATE ».

Avec MySQL 3.23, DELETE sans clause WHERE retournera zéro comme nombre d'enregistrements affectés.

Si vous voulez vraiment savoir combien d'enregistrements ont été effacés quand vous videz une table, et que vous êtes prêts à souffrir d'un léger ralentissement, vous pouvez utiliser une requête DELETE de ce genre :

mysql> DELETE FROM nom_de_table WHERE 1>0;

Notez que c'est plus lent que DELETE FROM nom_de_table sans clause WHERE, parce que cela efface un enregistrement à la fois.

Si vous effacez des lignes contenant la valeur maximum d'une colonne AUTO_INCREMENT, la valeur sera réutilisée pour par une table ISAM ou BDB, mais pas pour une table MyISAM ou InnoDB. Si vous effacez toutes les lignes dans une table avec une commande DELETE FROM tbl_name (avec une clause WHERE) avec le mode AUTOCOMMIT, la séquence redémarrer à zéro pour tous les types de table sauf InnoDB et, depuis MySQL 4.0, MyISAM. Il y a des exceptions à ce comportement pour les tables InnoDB, qui sont présentées dans la section Section 15.7.3, « Comment les colonnes AUTO_INCREMENT fonctionnent avec InnoDB ».

Pour les tables MyISAM et BDB, vous pouvez spécifier une autre colonne AUTO_INCREMENT dans une clé multi-colonnes. Dans ce cas, la réutilisation des clés à partir de la fin de la séquence se fait aussi pour les tables MyISAM. See Section 3.6.9, « Utiliser AUTO_INCREMENT ».

La commande DELETE supporte les clauses suivantes :

  • Si vous spécifiez le mot clé LOW_PRIORITY, l'exécution de la commande DELETE est repoussée jusqu'à ce qu'aucun client ne soit en train de lire la table.

  • Pour les tables MyISAM, si vous spécifiez l'option QUICK, le moteur de stockage ne compacte pas les index durant l'effacement, ce qui peut accélérer certains effacements.

  • L'option IGNORE fait que MySQL ignore les erreurs durant le traitement des lignes. Les erreurs rencontrées durant la phase d'analyse sont traitées comme d'habitude. Les erreurs qui sont ignorées grâce à cette options sont listées comme des alertes. Cette option a été ajoutée en MySQL 4.1.1.

La vitesse d'exécution des opérations de suppressions peut être affectées par les facteurs présentés dans la section Section 7.2.16, « Rapidité des requêtes DELETE ».

Dans les tables de type MyISAM, les enregistrements effacés sont maintenus dans une liste liée et les requêtes INSERT suivantes réutilisent les vieux emplacements. Pour recouvrir l'espace inutilisé ou réduire la taille des fichiers, utilisez la commande OPTIMIZE TABLE ou l'utilitaire myisamchk pour réorganiser les tables. OPTIMIZE TABLE est plus simple, mais myisamchk est plus rapide. Voyez Section 13.5.2.5, « Syntaxe de OPTIMIZE TABLE » et Section 5.7.3.10, « Optimisation de table ».

La clause spécifique MySQL LIMIT row_count de la commande DELETE indique au serveur le nombre maximal de ligne à supprimer avant de rendre le contrôle au client. Elle peut être utilisée pour s'assurer qu'une commande DELETE ne prend pas trop de temps. Vous pouvez simplement répéter la commande DELETE jusqu'à ce que le nombre de lignes effacées est inférieure à la valeur de LIMIT.

Si la commande DELETE inclut la clause ORDER BY, les lignes sont effacées dans l'ordre spécifiée par cette clause. Elle n'est vraiment utilise que lorsqu'elle est couplée avec la clause LIMIT. Par exemple, la commande suivante applique la condition WHERE, trie les lignes avec la colonne timestamp, et efface la ligne la plus ancienne :

DELETE FROM somelog
WHERE user = 'jcole'
ORDER BY timestamp
LIMIT 1

ORDER BY peut être utilisée avec DELETE depuis MySQL version 4.0.0.

Depuis MySQL version 4.0, vous pouvez spécifier plusieurs tables dans la commande DELETE, pour effacer des lignes dans plusieurs tables, en fonction d'une condition de liaison. Cependant, vous ne pouvez pas utiliser les clauses ORDER BY et LIMIT dans une suppression DELETE multi-tables.

La première syntaxe de DELETE multi-table est supportée depuis MySQL 4.0.0. La deuxième syntaxe de DELETE multi-table est supportée depuis MySQL 4.0.2. La partie table_references liste les tables impliquées dans la jointure. Sa syntaxe est décrite dans la section Section 13.1.7.1, « Syntaxe de JOIN ».

L'idée est que seul les lignes concordante dans les tables énumérées avant le FROM ou avant la clause USING sont effacés. Le but est de pouvoir effacer des lignes de plusieurs tables en même temps tout en ayant d'autres tables pour les recherches.

Le code .* après les noms de tables n'est présent que pour assurer la compatibilité avec Access :

DELETE t1,t2 FROM t1,t2,t3 WHERE t1.id=t2.id AND t2.id=t3.id

ou :

DELETE FROM t1,t2 USING t1,t2,t3 WHERE t1.id=t2.id AND t2.id=t3.id

Dans les cas précédents, nous n'avons supprimé les lignes correspondantes que dans les tables t1 et t2.

Les exemples ci-dessus présente des jointures internes, en utilisant l'opérateur virgule, mais les DELETE multi-tables peuvent utiliser n'importe quel type de jointure qu'une commande SELECT accepte, comme un LEFT JOIN.

La syntaxe autorise .* après le nom de la table pour assurer la compatibilité avec Access.

Si vous utilisez une commande DELETE multi-tables avec des tables InnoDB pour lesquelles il y a des contraintes de clés étrangères, l'optimiseur MySQL risque de traiter les tables dans un ordre qui diffère de celui des relations parent/enfant de la clé. Dans ce cas, la commande échouera, et s'annulera. Pour résoudre ce problème, effacez les lignes tables par table, et utilisez les fonctionnalités ON DELETE que InnoDB fournit pour que les autres tables soient correctement traitées.

Note : en MySQL 4.0, vous devez utiliser le véritable nom de table. En MySQL 4.1, vous devez utiliser l'alias éventuel, lorsque vous nommez la table :

En MySQL 4.0 :

DELETE test FROM test AS t1, test2 WHERE ...

En MySQL 4.1 :

DELETE t1 FROM test AS t1, test2 WHERE ...

La raison qui nous a poussé à ne pas faire ce changement en version 4.0, est la compatibilité ascendante avec les vieilles applications 4.0, qui utilisent la vieille syntaxe.

13.1.2. Syntaxe de DO

DO expression, [expression, ...]

Exécute l'expression mais ne retourne aucun résultat. C'est un alias de SELECT expression, expression, mais il a l'avantage d'être plus rapide quand on n'a pas besoin du résultat.

Cela s'avère très utile avec les fonctions qui ont des effets secondaires, comme RELEASE_LOCK.

13.1.3. Syntaxe de HANDLER

HANDLER tbl_name OPEN [ AS alias ]
HANDLER tbl_name READ index_name { = | >= | <= | < } (value1,value2,...)
    [ WHERE ... ] [LIMIT ... ]
HANDLER tbl_name READ index_name { FIRST | NEXT | PREV | LAST }
    [ WHERE ... ] [LIMIT ... ]
HANDLER tbl_name READ { FIRST | NEXT }
    [ WHERE ... ] [LIMIT ... ]
HANDLER tbl_name CLOSE

La commande HANDLER fournit un accès direct à l'interface de gestion de la table MyISAM.

La première forme de HANDLER ouvre la table, la rendant accessible via la requête HANDLER ... READ qui la suit. Cette objet table n'est pas partagé par les autres threads et ne sera refermé que si le thread appelle HANDLER nom_de_table CLOSE ou que celui ci se termine.

La seconde forme récupère une ligne (ou plus, à spécifier dans la clause LIMIT) où l'index spécifié remplit les conditions et où la clause WHERE est répondue. Si l'index se compose de plusieurs parties, (s'étend sur plusieurs colonnes) les valeurs sont spécifiées dans une liste séparée par des virgules, fournir des valeurs pour quelques premières colonnes est possible. Par exemple :

HANDLER ... index_name = (col_a_val,col_b_val,col_c_val) ...
HANDLER ... index_name = (col_a_val,col_b_val) ...
HANDLER ... index_name = (col_a_val) ...

La troisième forme récupère une ligne (ou plus, à spécifier dans la clause LIMIT) de la table dans l'ordre de l'index, qui répond à la clause WHERE.

La quatrième forme (sans spécifications relatives à l'index) récupère une ligne (ou plus, à spécifier dans la clause LIMIT) de la table dans un ordre naturel des lignes (comme stocké dans le fichier de données) qui correspond à la condition WHERE. C'est plus rapide que HANDLER nom_de_table READ nom_index quand une lecture entière de la table est requise. See Section 13.1.7, « Syntaxe de SELECT ».

HANDLER ... CLOSE ferme une table qui a été ouverte avec HANDLER ... OPEN.

Note : pour utiliser l'interface HANDLER avec la clé primaire d'une table PRIMARY KEY, utilisez l'identifiant entre guillemets obliques `PRIMARY` :

HANDLER tbl_name READ `PRIMARY` > (...);

HANDLER est en quelque sorte une commande bas-niveau. Par exemple, elle ne propose pas de consistance. En clair, HANDLER ... OPEN ne se base pas sur une image de la table, et ne verrouille pas la table. Cela signifie qu'après l'exécution d'une requête HANDLER ... OPEN, les données de la table peuvent être modifiées (par ce ou un autre thread) et ces modifications peuvent apparaître partiellement dans les lectures de HANDLER ... NEXT ou HANDLER ... PREV.

Les raisons d'utiliser cette interface plutôt que les commandes MySQL usuelles sont :

  • Plus rapide qu'un SELECT car :

    • Un pointeur sur table dédié est alloué au thread dans HANDLER open.

    • Il y a moins de traitements.

    • Pas de pertes de temps en optimisation ou vérifications de requêtes.

    • La table utilisée n'a pas besoin d'être verrouillée entre deux requêtes de gestion.

    • L'interface de gestion n'a pas à fournir une vue consistante des données (par exemple, les lectures corrompues sont autorisées), ce qui permet au gestionnaire d'effectuer des optimisations que SQL ne permet pas.

  • Cela facilite le port des applications qui utilisent l'interface ISAM pour MySQL.

  • Cela permet de traverse plus facilement la base de données qu'avec SQL (dans certains cas, cette opération est impossible avec SQL). L'interface de gestion amène une fa¸on plus naturelle de manipuler les données lorsque vous travaillez avec des applications qui proposent une interface interactive entre l'utilisateur et la base de données.

13.1.4. Syntaxe de INSERT

INSERT [LOW_PRIORITY | DELAYED] [IGNORE]
    [INTO] tbl_name [(col_name,...)]
    VALUES ({expr | DEFAULT},...),(...),...
    [ ON DUPLICATE KEY UPDATE col_name=expr, ... ]

ou :

INSERT [LOW_PRIORITY | DELAYED] [IGNORE]
    [INTO] tbl_name
    SET col_name={expr | DEFAULT}, ...
    [ ON DUPLICATE KEY UPDATE col_name=expr, ... ]

ou :

INSERT [LOW_PRIORITY | DELAYED] [IGNORE]
    [INTO] tbl_name [(col_name,...)]
    SELECT ...

INSERT insère une nouvelle ligne dans une table existante. La syntaxe INSERT ... VALUES insère une ligne à partir de valeurs explicitement fournies. La syntaxe INSERT ... SELECT insère des valeurs à partir d'une autre table. La syntaxe INSERT ... VALUES avec plusieurs valeurs est supportées à partir de MySQL 3.22.5 ou supérieure. la syntaxe nom_colonne=expression est supportée à partir de la version 3.22.10 de MySQL.

INSERT ... SELECT est présenté plus en détails : See Section 13.1.4.1, « Syntaxe de INSERT ... SELECT ».

nom_de_table est le nom de la table dans laquelle les valeurs seront insérées. La liste de noms de colonne ou la clause SET indiquent les colonnes qui seront affectées:

  • La liste des noms de colonnes ou la clause SET indique explicitement les colonnes utilisées.

  • Si vous ne spécifiez pas de liste de colonnes avec INSERT ... VALUES ou INSERT ... SELECT, les valeurs pour toutes les colonnes doivent être fournies dans la clause VALUES() ou par la commande SELECT. Si vous ne connaissez pas l'ordre des colonnes, utilisez la commande DESCRIBE nom_de_table pour le connaître.

Les valeurs des colonnes peuvent être spécifiées de plusieurs fa¸ons :

  • A chaque fois qu'on ne donne pas explicitement une valeur pour une colonne, celle prend la valeur par défaut. Par exemple, si on définit une liste de colonnes qui ne compte pas toutes les colonnes de la tables, toutes les colonnes qui ne sont pas nommées prendront leur valeur par défaut. La définition de la valeur par défaut se fait avec Section 13.2.5, « Syntaxe de CREATE TABLE ».

    MySQL a toujours une valeur par défaut pour chaque champs. C'est obligatoire pour MySQL pour pouvoir fonctionner aussi bien avec des tables supportant les transactions qu'avec des tables ne les supportant pas.

    Nous pensons que le contrôle du contenu des champs devrait être fait pas l'application et non par le serveur de base de données.

    Note : si vous voulez que les commandes INSERT génèrent une erreur si vous ne spécifiez pas explicitement de valeur pour toutes les colonnes qui requièrent des valeurs non-nulles (NULL), vous pouvez aussi configurer MySQL avec l'option DONT_USE_DEFAULT_FIELDS. Ce comportement n'est pas disponible si vous compilez MySQL depuis le source. See Section 2.4.2, « Options habituelles de configure ».

  • Vous pouvez utiliser le mot clé DEFAULT pour donner explicitement à une colonne sa valeur par défaut. Cette fonctionnalité a été ajoutée en MySQL version 4.0.3. Cela rend plus simple l'écriture de commandes INSERT lors de l'assignation de quelques colonnes, sans écrire de valeurs VALUES incomplètes. Sinon, il faut écrire la liste des colonnes utilisées pour chaque valeur de la liste VALUES.

  • Si la liste de colonnes et de valeurs VALUES sont vides, INSERT crée une ligne pour chaque colonne avec sa valeur par défaut :

    mysql> INSERT INTO tbl_name () VALUES();
    
  • Une expression peut faire référence à n'importe quelle colonne qui a été définie précédemment dans une liste de valeurs. Par exemple, on peut dire ceci :

    mysql> INSERT INTO nom_de_table (col1,col2) VALUES(15,col1*2);
    

    Mais vous ne pouvez pas faire cela, car la valeur de col1 fait référence à col2, qui est assigné après col1 :

    mysql> INSERT INTO nom_de_table (col1,col2) VALUES(col2*2,15);
    

Les commandes INSERT supportent les options suivantes :

  • Si vous spécifiez l'option DELAYED, le serveur met la ligne ou les lignes à insérer dans un tampon, et le client qui a émis la commande INSERT DELAYED est immédiatement libéré. Si la table est occupée, le serveur conserve les lignes. Lorsque la table se libère, il va insérer les lignes, tout en vérifiant périodiquement s'il n'y a pas de lectures dans la table. Si une lecture arrive, l'insertion est suspendue jusqu'à la prochaine libération. See Section 13.1.4.2, « Syntaxe de INSERT DELAYED ».

  • Si on spécifie le mot LOW_PRIORITY, l'exécution de INSERT sera retardé jusqu'à ce qu'il n'y ait plus de clients qui lisent la table. Dans ce cas le client doit attendre jusqu'à la fin de l'opération d'insertion, ce qui peut prendre beaucoup de temps si la table est fréquemment accédée. C'est la grande différence avec INSERT DELAYED, qui laisse le client continuer tout de suite. See Section 13.1.4.2, « Syntaxe de INSERT DELAYED ». On peut remarquer que, en principe, LOW_PRIORITY ne devrait pas être utilisé avec des tables de type MyISAM, étant donné que celles-ci n'autorisent pas les insertions simultanées. See Section 14.1, « Le moteur de tables MyISAM ».

  • Si on spécifie le mot IGNORE dans un INSERT avec les valeurs de plusieurs lignes, chaque ligne qui qui ferait doublon avec une clé PRIMARY ou UNIQUE existante dans la table sera ignoré et ne sera pas insérée. Si on ne spécifie pas IGNORE, l'insertion est abandonnée si quelque ligne que ce soit fait doublon avec une clé existante. La fonction mysql_info() de l'API C permet de savoir combien de lignes ont été insérées dans la table.

Si vous spécifiez la clause ON DUPLICATE KEY UPDATE (nouveau en MySQL 4.1.0), et qu'une ligne insérée engendre un doublon pour une clé PRIMARY ou UNIQUE, une commande UPDATE sera faite à la place de l'insertion. Par exemple, les commandes ont le même effet :

mysql> INSERT INTO table (a,b,c) VALUES (1,2,3)
    -> ON DUPLICATE KEY UPDATE c=c+1;

mysql> UPDATE table SET c=c+1 WHERE a=1;

Note : si la colonne b est aussi unique, la commande UPDATE sera réécrite telle que

mysql> UPDATE table SET c=c+1 WHERE a=1 OR b=2 LIMIT 1;

Si a=1 OR b=2 trouve plusieurs lignes, uniquement une ligne sera mise à jour! En général, il faut éviter d'utiliser la clause ON DUPLICATE KEY sur des tables avec des clés UNIQUE multiples.

Depuis MySQL version 4.1.1, on peut utiliser la fonction VALUES(col_name) pour faire référence à la valeur de la colonne dans la clause INSERT d'une commande INSERT ... UPDATE : c'est la valeur qui sera insérée s'il n'y a pas de conflit de clé. Cette valeur est particulièrement utile dans les commandes INSERT ... UPDATE et retourne NULL sinon.

Exemple :

mysql> INSERT INTO table (a,b,c) VALUES (1,2,3),(4,5,6)
    -> ON DUPLICATE KEY UPDATE c=VALUES(a)+VALUES(b);

La commande ci-dessus est identique à :

mysql> INSERT INTO table (a,b,c) VALUES (1,2,3)
    -> ON DUPLICATE KEY UPDATE c=3;
mysql> INSERT INTO table (a,b,c) VALUES (4,5,6)
    -> ON DUPLICATE KEY UPDATE c=9;

Lors de l'utilisation de ON DUPLICATE KEY UPDATE, l'option DELAYED est ignorée.

Vous pouvez trouver la valeur utilisée pour une colonne AUTO_INCREMENT en utilisant la fonction LAST_INSERT_ID(). Depuis l'interface C, utilisez la fonction mysql_insert_id(). Cependant, notez que les deux fonctions ne se comportement pas de la la même fa¸ons dans toutes les circonstances. Le comportement des commandes INSERT avec les colonnes AUTO_INCREMENT sont décrites dans la section Section 12.8.3, « Fonctions d'informations » et Section 24.2.3.33, « mysql_insert_id() ».

Si vous utilisez une commande INSERT ... VALUES avec plusieurs listes de valeurs ou INSERT ... SELECT, la commande retourne les informations sous ce format :

Records: 100 Duplicates: 0 Warnings: 0

Records indique le nombre de ligne qui ont été traitées par cette commande. Ce n'est pas forcément le nombre de ligne insérées. Duplicates peut être non-nulle. Duplicates indique le nombre de lignes qui n'ont pas peut être insérées pour cause de conflit avec une clé unique existante. Warnings indique le nombre de tentatives d'insertion de valeurs dans une colonne qui ont généré des problèmes. Les Warnings peuvent apparaître dans les conditions suivantes:

  • Insertion de NULL dans une colonne déclarée NOT NULL. Pour les commandes d'insertions multiples INSERT ou les commandes INSERT ... SELECT, la colonne prend la valeur par défaut adaptée au type de colonne. C'est 0 pour les types numériques, la chaîne vide pour les textes et la valeur ``zéro'' pour les types temporels

  • Enregistrement dans une colonne numérique d'une valeur qui déborde de la taille de la colonnes. Cette valeur a été tronquée à l'extrémité la plus adaptée de la colonne.

  • Attribution à une colonne numérique d'une valeur telle que '10.34 a'. Celle valeur refusée est séparée, et la partie numérique résultante est insérée. Si cette valeur n'a pas une valeur numérique sensée, la valeur 0 est insérée.

  • L'insertion d'une chaîne dans une colonne CHAR, VARCHAR, TEXT, ou BLOB qui dépasse la taille maximale de la colonne. La valeur est tronquée à la taille maximale de la colonne.

  • L'insertion d'une valeur illégale pour une colonne de type DATE ou TIME. La colonne est alors enregistrée avec la valeur de zéro appropriée pour le type.

Si vous utilisez l'interface C, la chaîne d'information peut être obtenue en invoquant la fonction mysql_info(). See Section 24.2.3.31, « mysql_info() ».

13.1.4.1. Syntaxe de INSERT ... SELECT

INSERT [LOW_PRIORITY] [IGNORE] [INTO] nom_de_la_table [(liste des colonnes)] SELECT ...

La requête INSERT ... SELECT permet de rapidement insérer dans une table un grand nombre de lignes d'une ou plusieurs autres tables.

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

Les conditions suivantes s'appliquent à la requête INSERT ... SELECT:

  • Avant MySQL version 4.0.1, INSERT ... SELECT opérait implicitement en mode IGNORE. Depuis MySQL version 4.0.1, vous devez spécifier le mode IGNORE explicitement, pour ignorer les lignes qui causeront des erreurs de doublons pour les index uniques.

  • N'utilisez pas DELAYED avec INSERT ... SELECT.

  • Avant MySQL version 4.0.14, la table de destination de la requête INSERT ne peut apparaître dans la clause FROM de la partie SELECT de la requête car il est interdit par le ANSI SQL de lire la table dans laquelle on est en train de faire un insert. (le problème est que le SELECT pourrait trouver des enregistrements qui aurait été insérés auparavant dans la même exécution. L'utilisation de "subselect" peut rendre la situation confuse !)

  • Les colonnes AUTO_INCREMENT fonctionnent comme d'habitude.

  • Pour s'assurer que les journaux des modifications ou les journaux binaires puissent être utilisés pour re-créer les tables originales, MySQL n'autorise pas les inserts concurrents pendant INSERT ... SELECT.

Il est bien sûr possible d'utiliser REPLACE à la place de INSERT pour remplacer les anciennes lignes.

13.1.4.2. Syntaxe de INSERT DELAYED

INSERT DELAYED ...

L'option DELAYED de la commande INSERT est une option spécifique à MySQL très utile si vos clients ne peuvent pas attendre que INSERT se termine. C'est un problème fréquent quand on utilise MySQL pour des logs, mais aussi quand on utilise souvent des commandes SELECT ou UPDATE qui prennent beaucoup de temps. DELAYED a été ajouté à MySQL dans la version 3.22.15. C'est une extension de MySQL au ANSI SQL 92.

En utilisant INSERT DELAYED, le client re¸oit immédiatement un aquitement, et la ligne sera insérée quand la table ne sera plus utilisée par un autre thread.

Un autre avantage de INSERT DELAYED est que les insertions des clients sont regroupés, et écrits d'un seul bloc. C'est beaucoup plus rapide que de faire des insertions séparés.

Il y a quelques contraintes à l'utilisation de DELAYED :

  • INSERT DELAYED ne fonctionne qu'avec les tables MyISAM et ISAM. Pour les tables MyISAM, s'il n'y a plus de blocs libres au milieu du fichier de données, les SELECT et INSERT simultanés sont supportés. Dans ces circonstances, vous n'aurez que très rarement besoin de INSERT DELAYED avec MyISAM. See Section 14.1, « Le moteur de tables MyISAM ».

  • INSERT DELAYED doit être utilisé uniquement avec les commandes INSERT qui spécifie une liste de valeur. C'est le cas depuis MySQL 4.0.18. Le serveur ignore DELAYED pour les commandes INSERT DELAYED ... SELECT.

  • Le serveur ignore DELAYED dans les commandes INSERT DELAYED ... ON DUPLICATE UPDATE.

  • Comme la commande s'exécute immédiatement, sans que la ligne ne soit insére, vous ne pouvez pas utiliser LAST_INSERT_ID() pour lire la valeur que la colonne AUTO_INCREMENT va générer.

  • Les lignes DELAYED ne sont visibles par les commandes SELECT que lorsqu'elles ont été réellement insérées.

Actuellement, les lignes en attente sont uniquement stockées en mémoire tant qu'elle ne sont pas insérées dans la table. Cela signifie que si on tue mysqld violemment, (kill -9) ou si mysqld meurt accidentellement, toutes les lignes en attente qui n'auront pas été écrites sur le disque seront perdues !

Les paragraphes suivants décrivent en détail ce qu'il se passe quand on utilise l'option DELAYED dans une requête INSERT ou REPLACE. Dans cette description, ``thread'' est un thread qui re¸oit une commande INSERT DELAYED ans ``handler'' est un thread qui gère toutes les opérations de INSERT DELAYED pour une table donnée.

  • Quand un thread exécute une opération DELAYED sur une table, un thread de gestion est créé pour exécuter toutes les opérations DELAYED pour cette table - si ce thread de gestion n'existe pas.

  • Le thread vérifie que a déjà re¸u un verrou DELAYED; sinon, il dit au thread de gestion de le faire. le verrou DELAYED peut être obtenu même si d'autres threads ont des verrous READ ou WRITE sur la table. Cependant le gestionnaire attendra que tous les verrous ALTER TABLE ou FLUSH TABLES soient finis pour s'assurer que la structure de la table est à jour.

  • Le thread exécute une opération INSERT, mais plutôt que d'écrire la ligne dans la table, il va placer une copie de la ligne finale dans une file d'attente gérée par le thread de gestion. Le programme client est avertit de toutes les erreurs de syntaxe.

  • Le client ne peut pas faire de rapport sur le nombre de duplicata ou sur la valeur de AUTO_INCREMENT de la ligne enregistrée; il ne peut pas les obtenir du serveur, car le INSERT est validé avant que l'opération d'insert n'ait été effectuée. Si vous utilisez l' API C, la fonction mysql_info() ne retourne pas de valeur intéressante, pour la même raison.

  • Le journal de modification est mis à jour par le thread de gestion au moment où la ligne est insérée dans la table. Si plusieurs lignes sont insérées en même temps, le journal des modifications est mis à jour quand la première ligne est insérée.

  • Une fois que toutes les lignes delayed_insert_limit sont écrites, le gestionnaire vérifie si des requêtes SELECT sont en attente, et si c'est le cas, il leur permet de s'exécuter avant de continuer.

  • Quand le thread de gestion n'a plus de ligne dans sa file, la table est déverrouillée. Si aucun INSERT DELAYED n'est re¸u avant delayed_insert_timeout secondes, le gestionnaire s'arrête.

  • Si plus de delayed_queue_size lignes sont déjà en attente d'un gestionnaire de file donné, le thread qui demande le INSERT DELAYED doit attendre qu'il y ait une place dans la file. Cela permet d'être sûr que mysqld n'utilisera pas toute la mémoire pour la mémoire des files d'attente d'insertions retardés.

  • Le thread de gestion apparaîtra dans la liste des processus de MySQL avec delayed_insert dans la colonne Command. Il sera tué si on exécute une commande FLUSH TABLES ou si on le tue avec KILL thread_id. Cependant, il commencera par stocker toutes les lignes en attente dans la table avant de sortir. Pendant ce temps, il n'acceptera aucune commande INSERT d'aucun autre thread. Si on exécute une commande INSERT DELAYED après cela, un nouveau thread de gestion sera créé.

    Il faut noter que les commandes INSERT DELAYED ont une plus grande priorité que les commandes INSERT normales si un gestionnaire de INSERT DELAYED existe déjà! les autres commandes de modification devront attendre que la file d'attente de INSERT DELAYED soit vide, que quelqu'un tue le gestionnaire (avec KILL thread_id), ou que quelqu'un exécute FLUSH TABLES..

  • Les variables suivantes fournissent des informations relatives à la commande INSERT DELAYED :

    VariableSignification
    Delayed_insert_threadsNombre de threads de gestion
    Delayed_writesNombre de lignes écrites avec INSERT DELAYED
    Not_flushed_delayed_rowsNombre de lignes en attente d'être écrites.

    On peut voir ces variables avec la commande SHOW STATUS ou en exécutant la commande mysqladmin extended-status.

Il faut noter que INSERT DELAYED est plus lent qu'un INSERT normal si la table n'est pas utilisée. L'utilisation d'un thread de gestion séparé pour chaque table sur lesquelles on utilise INSERT DELAYED rajoute également une surcharge au serveur. Ce qui signifie qu'il vaut mieux utiliser INSERT DELAYED uniquement quand c'est vraiment nécessaire!

13.1.5. Syntaxe de LOAD DATA INFILE

LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name.txt'
    [REPLACE | IGNORE]
    INTO TABLE tbl_name
    [FIELDS
        [TERMINATED BY '\t']
        [[OPTIONALLY] ENCLOSED BY '']
        [ESCAPED BY '\\' ]
    ]
    [LINES 
        [STARTING BY '']    
        [TERMINATED BY '\n']
    ]
    [IGNORE number LINES]
    [(col_name,...)]

La commande LOAD DATA INFILE lit les lignes dans un fichier texte et les insère à très grande vitesse. Pour plus d'informations sur l'efficacité des commandes INSERT comparativement à LOAD DATA INFILE et pour accélérer les commandes LOAD DATA INFILE, voyez Section 7.2.14, « Vitesse des requêtes INSERT ».

Vous pouvez aussi charger des fichiers de données en utilisant l'utilitaire mysqlimport; Il opère en envoyant la commande LOAD DATA INFILE au serveur. L'option --local fais que mysqlimport lit les fichiers de données chez le client. Vous pouvez spécifier l'option --compress pour avoir de meilleurs performances avec les connexions lentes si le client et le serveur supportent le protocole compressé. See Section 8.10, « mysqlimport, importer des données depuis des fichiers texte ».

Si vous spécifiez le mot clef LOW_PRIORITY, l'exécution de la commande LOAD DATA est ajournée jusqu'à ce qu'aucun client ne lise plus de la table.

Si vous spécifiez le mot clef CONCURRENT avec un table au format MyISAM, les autres threads pourront accéder à la table durant l'exécution de la commande LOAD DATA. L'utilisation de cette option ralentira un peu les performances de LOAD DATA même si aucun thread n'utilise la table en même si aucun autre thread n'accède à la table en même temps.

Si le mot clé LOCAL est spécifié, il est interprété en suivant les règles suivantes :

  • Si LOCAL est spécifié, le fichier est lu par le programme client, et envoyé vers l'hôte.

  • Si LOCAL n'est pas spécifiée, le fichier doit être sur le serveur hôte, et sera lu directement par le serveur.

LOCAL est disponible depuis MySQL 3.22.6 ou plus récent.

Pour des raisons de sécurité, lorsque les fichiers sont lus sur le serveur, ils doivent se trouver dans le répertoire de la base de données courante, ou bien être lisible par tous. Pour utiliser la commande LOAD DATA INFILE sur des fichiers du serveur, vous devez avoir le droit de FILE sur le serveur. See Section 5.5.3, « Droits fournis par MySQL ».

Utiliser LOCAL est plus lent que de laisser le serveur accéder directement aux fichiers, car le contenu du fichier doit être envoyé via le réseau au serveur. D'un autre coté, vous n'aurez pas besoin de droits de FILE pour faire un chargement local.

Depuis MySQL 3.23.49 et MySQL 4.0.2 (4.0.13 sur Windows), LOCAL fonctionne uniquement si votre serveur et votre client ont été configuré pour. Par exemple, si mysqld a été lancé avec --local-infile=0, LOCAL ne fonctionnera pas. See Section 5.4.4, « Problèmes de sécurité avec LOAD DATA LOCAL ».

Si vous avez besoin de lire des données LOAD DATA depuis un pipe, vous devez utiliser la technique suivante :

mkfifo /mysql/db/x/x
chmod 666 /mysql/db/x/x
cat < /dev/tcp/10.1.1.12/4711 > /mysql/db/x/x
mysql -e "LOAD DATA INFILE 'x' INTO TABLE x" x

Si vous utilisez une version de MySQL plus ancienne que 3.23.25, vous pouvez uniquement utiliser cette technique avec LOAD DATA LOCAL INFILE.

Si vous utilisez une version de MySQL antérieure à la 3.23.24 vous ne pouvez lire à partir d'un FIFO avec LOAD DATA INFILE. Si vous avez besoin de lire à partir d'un FIFO (par exemple la sortie de gunzip), utilisez LOAD DATA LOCAL INFILE.

Lorsque les fichiers de données sont sur le serveur, celui-ci utilise les règles suivantes :

  • Si un chemin absolu est fourni, le serveur utilise le chemin tel quel.

  • Si un chemin relatif est fourni, avec un ou plusieurs éléments de dossiers, le serveur recherche le fichier relativement à son dossier de données.

  • Si le fichier n'a pas d'éléments de dossier, le serveur recherche les données dans le dossier de base de données courante.

Notez que ces règles font qu'un fichier tel que ./myfile.txt est lu dans le dossier de données du serveur, alors que s'il est nommé myfile.txt, il sera lu dans le dossier de base de données courante. Par exemple, la commande LOAD DATA suivante lit le fichier donnees.txt dans le dossier de la base db1 car db1 est la base de données courante, même si la commande charge explicitement le fichier dans la base de données db2 :

mysql> USE db1;
mysql> LOAD DATA INFILE "donnees.txt" INTO TABLE db2.ma_table;

Les mots réservés REPLACE et IGNORE contrôlent la méthode d'insertion de lignes lorsque des doublons apparaissent pour les clés uniques.

Si vous spécifiez REPLACE, les nouvelles lignes remplaceront les anciennes. See Section 13.1.6, « Syntaxe de REPLACE ».

Si vous spécifiez IGNORE, les nouvelles lignes seront ignorées. Si vous ne spécifiez pas cette option, une erreur sera générée à chaque doublon, et le reste du fichier sera ignoré. Avec l'option LOCAL, le comportement par défaut est le même que si IGNORE est spécifié : ceci est dû au fait que le serveur n'a pas moyen de stopper la transmission du fichier au milieu de l'opération.

Si vous chargez un fichier sur votre machine client avec l'option LOCAL, le serveur ne peut pas interrompre la transmission du fichier au milieu de l'opération : par défaut, il utilisera l'option IGNORE.

Si vous voulez ignorer les clés étrangères le temps du chargement du fichier, utilisez la commande SET FOREIGN_KEY_CHECKS=0 avant d'exécuter LOAD DATA.

Si vous utilisez LOAD DATA INFILE sur une table vide de type MyISAM, tous les index non-uniques seront créés dans un processus séparé (tout comme REPAIR). Cela rend LOAD DATA INFILE beaucoup plus rapide si vous avez plusieurs index. See Section 5.7.3, « Utilisation de myisamchk pour la maintenance des tables et leur recouvrement ».

LOAD DATA INFILE est le complémentaire de SELECT ... INTO OUTFILE. See Section 13.1.7, « Syntaxe de SELECT ». Pour écrire des données depuis une table dans un fichier, utilisez SELECT ... INTO OUTFILE. Pour lire les données dans la table, utilisez LOAD DATA INFILE. La syntaxe des clauses FIELDS et LINES est la même pour les deux commandes. Ces deux clauses sont optionnelles, mais FIELDS doit précéder LINES, si les deux sont spécifiées.

Si vous spécifiez la clause FIELDS, les sous-clauses TERMINATED BY, [OPTIONALLY] ENCLOSED BY, et ESCAPED BY sont aussi optionnelles, mais vous devez en spécifier au moins une.

Si vous ne spécifiez par de clause FIELDS, les valeurs par défaut sont :

FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\'

Si vous ne spécifiez par de clause LINES, les valeurs par défaut sont :

LINES TERMINATED BY '\n'

En d'autres termes, les valeurs par défaut font que LOAD DATA INFILE lit les données comme suit :

  • Recherche des limites de lignes parmi les nouvelles lignes.

  • Si LINES STARTING BY prefix est utilisé, lit jusqu'au préfixe, et commence à lire après le préfixe. Si la ligne n'inclut pas de préfixe, elle sera ignorée.

  • Scinde les lignes en champs avec les tabulations.

  • Ne suppose pas que les champs sont entourés de guillemets.

  • Interprète les occurrences de tabulation, nouvelle ligne, ‘'\'’ précédées par ‘'\'’ comme des caractères littéraux qui font partie de la valeur d'un champs.

A l'inverse, les valeurs par défaut font que SELECT ... INTO OUTFILE écrit les données comme ceci :

  • Ecrivez des tabulations entre les champs.

  • N'entourez pas les champs de guillemets.

  • Utilisez ‘\’ pour échapper les occurrences de tabulation, nouvelle ligne, ‘\’ trouvées dans les valeurs.

  • Insère une nouvelle ligne entre les lignes.

Notez que pour utiliser FIELDS ESCAPED BY '\\', vous devez spécifier deux anti-slash pour que cette valeur soit interprétée comme un anti-slash simple.

Note : si vous avez généré le fichier sur Windows, vous devrez peut-être utiliser LINES TERMINATED BY '\r\n' pour lire le fichier correctement, car les programmes Windows utilisent généralement deux caractères comme fin de ligne. Certains programmes, comme WordPad, peuvent utiliser \r comme terminateur de ligne lors de l'écriture. Pour lire ces fichiers, utilisez LINES TERMINATED BY '\r.

L'option IGNORE nombre LINES sert à ignorer une en-tête de fichier, telle que des noms de colonnes, qui débutent parfois un fichier à charger :

mysql> LOAD DATA INFILE "/tmp/nom_fichier" INTO TABLE test IGNORE 1 LINES;

Lorsque vous utilisez SELECT ... INTO OUTFILE conjointement avec LOAD DATA INFILE pour écrire des données dans un fichier et les relire dans une table, les options de FIELDS et LINES doivent être identiques. Sinon, LOAD DATA INFILE ne pourra pas interpréter le contenu du fichier correctement. Supposez que la commande SELECT ... INTO OUTFILE ait écrit un fichier délimité par des virgules :

mysql> SELECT * INTO OUTFILE 'donnees.txt'
    ->          FIELDS TERMINATED BY ','
    ->          FROM ...;

Pour lire ce fichier, la commande correcte serait :

mysql> LOAD DATA INFILE 'donnees.txt' INTO TABLE table2
    ->           FIELDS TERMINATED BY ',';

Si au contraire, vous essayez de lire le fichier avec la commande ci-dessous, cela ne fonctionnera pas, car la commande LOAD DATA INFILE essaie de lire des tabulations entre les champs :

mysql> LOAD DATA INFILE 'donnees.txt' INTO TABLE table2
    ->           FIELDS TERMINATED BY '\t';

Il est probable que chaque ligne d'entrée sera interprétée que comme un seul champ.

La commande LOAD DATA INFILE peut être utilisée pour lire des données issues d'autres sources. Par exemple, un fichier au format dBASE présente des champs séparés par des virgules, et entourés de guillemets doubles. Si les lignes sont terminées par de nouvelles lignes, la commande ci-dessous illustre la relecture d'un tel fichier avec MySQL :

mysql> LOAD DATA INFILE 'donnees.txt' INTO TABLE nom_de_table
    ->           FIELDS TERMINATED BY ',' ENCLOSED BY '"'
    ->           LINES TERMINATED BY '\n';

Les clauses FIELDS et LINES peuvent prendre des chaînes vides comme valeur. S'il la chaîne n'est pas vide, FIELDS [OPTIONALLY] ENCLOSED BY et FIELDS ESCAPED BY ne doivent avoir qu'un seul caractère. Les valeurs de FIELDS TERMINATED BY et LINES TERMINATED BY peuvent avoir plus d'un caractère. Par exemple, pour écrire des lignes terminées par le couple retour chariot/nouvelle ligne, ou pour lire un tel fichier, spécifiez la clause LINES TERMINATED BY '\r\n'.

Par exemple, pour charger un fichier de blagues, qui sont séparées par une ligne de %%, dans une table vous pouvez faire :

CREATE TABLE blagues (
   a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, 
   blague TEXT NOT NULL
   );
LOAD DATA INFILE "/tmp/blagues.txt" INTO TABLE blagues FIELDS TERMINATED BY ""
LINES TERMINATED BY "\n%%\n" (blague);

FIELDS [OPTIONALLY] ENCLOSED BY contrôle la mise entre guillemets des champs. Pour l'écriture de fichier (SELECT ... INTO OUTFILE), si vous omettez le mot OPTIONALLY, tous les champs seront entourés par le caractère spécifié dans la clause ENCLOSED BY. Par exemple, si la virgule est utilisée comme séparateur de champs :

"1","une chaîne","100.20"
"2","une chaîne contenant une , virgule","102.20"
"3","une chaîne contenant un \" guillemet","102.20"
"4","une chaîne contenant un \", guillemet et une virgule","102.20"

Si vous spécifiez OPTIONALLY, le caractère ENCLOSED BY n'est utilisé que pour protéger les colonnes de types CHAR et VARCHAR :

1,"une chaîne",100.20
2,"une chaîne contenant une , virgule",102.20
3,"une chaîne contenant un \" guillemet",102.20
4,"une chaîne contenant un \", guillemet et une virgule",102.20

Notez que les occurrences du caractère ENCLOSED BY dans un champs sont échappée en les préfixant avec le caractère ESCAPED BY. Notez aussi que si vous spécifiez un caractère d'échappement vide, il n'est pas possible de garantir que les champs seront correctement relus par LOAD DATA INFILE. Par exemple, l'exemple ci-dessus apparaîtra comme montré ci-dessous. Notez que le second champ de la quatrième ligne comporte une virgule suivant un guillemet qui semble (mais c'est faux) terminer la ligne :

1,"une chaîne",100.20
2,"une chaîne contenant une , virgule",102.20
3,"une chaîne contenant un " guillemet",102.20
4,"une chaîne contenant un ", guillemet et une virgule",102.20

Lors des lectures, le caractère ENCLOSED BY, s'il est présent, est supprimé des extrémités de la valeur du champ. (ce qui est vrai, qu'il y ait l'option OPTIONALLY ou pas). Les occurrences du caractère ENCLOSED BY, précédées par le caractère ESCAPED BY sont interprétées comme faisant partie de la valeur du champ. Les caractères ENCLOSED BY doublées, apparaissant dans la chaîne, sont interprétés comme le caractère ENCLOSED BY lui-même. Par exemple, si ENCLOSED BY '"' est spécifié, les guillemets sont gérés comme ceci :

"Le ""GRAND"" chef"  -> Le "GRAND" chef
Le "GRAND" chef      -> Le "GRAND" chef
Le ""GRAND"" chef    -> Le ""GRAND"" chef

FIELDS ESCAPED BY contrôle les caractères spéciaux. Si le caractère FIELDS ESCAPED BY n'est pas vide, il est utilisé pour préfixer les caractères suivants en écriture :

  • La caractère FIELDS ESCAPED BY

  • Le caractère FIELDS [OPTIONALLY] ENCLOSED BY

  • Le premier caractère des valeurs de FIELDS TERMINATED BY et LINES TERMINATED BY

  • ASCII 0 (en fait, ce qui est écrit après le caractère d'échappement est le caractère ASCII '0', et non pas le code ASCII de zéro)

Si le caractère FIELDS ESCAPED BY est vide, aucun caractère ne sera échappé. Ce n'est probablement pas une bonne idée de spécifier un caractère d'échappement vide, en particulier si les valeurs dans vos champs risquent d'utiliser l'un des caractères de la liste ci-dessus.

En lecture, si le caractère FIELDS ESCAPED BY n'est pas vide, les occurrences de ce caractère sont supprimées, et le caractère suivant est lu littéralement. Les exceptions à cette règle sont ‘0’ ou ‘N’ (par exemple, 0 ou \N si le caractère d'échappement est ‘\’). Ces séquences sont interprétées comme l'octet nul (ASCII 0) et la valeur NULL. Voyez plus bas pour la gestion des valeurs NULL.

Pour plus d'informations sur la syntaxe avec les caractères d'échappement ‘\’, consultez Section 9.1, « Littéraux : comment écrire les chaînes et les nombres ».

Dans certains cas, les options de FIELDS et LINES interfèrent entre elles :

  • Si le caractère de LINES TERMINATED BY est une chaîne vide et que celui de FIELDS TERMINATED BY ne l'est pas, ce dernier sera celui utilisé pour LINES TERMINATED BY.

  • Si les valeurs FIELDS TERMINATED BY et FIELDS ENCLOSED BY sont vides toutes les deux (''), un format à taille de champ fixe est utilisé. Avec ce format, aucun délimiteur n'est utilisé entre les champs. Au lieu de cela, les valeurs des colonnes sont écrites avec leur configuration d'affichage. Par exemple, si une colonne a été déclarée INT(7), la valeur de cette colonne sera écrite avec 7 caractères. Lors de la relecture, la valeur de la colonne sera obtenue en lisant à nouveau 7 caractères. Ce format à taille fixe affecte la gestion de la valeur NULL; voyez plus loin pour cela. Notez que ce format ne fonctionne pas avec les jeux de caractères multi-octets.

La gestion des valeurs NULL dépend des options FIELDS et LINES que vous utilisez :

  • Pour les valeurs par défaut de FIELDS et LINES, NULL est écrit \N et \N est lu NULL (en supposant que le caractère d'échappement est ‘\’).

  • Si FIELDS ENCLOSED BY n'est pas vide, un champ contenant le mot NULL comme valeur sera lu comme la valeur NULL (ce qui diffère du mot NULL, entouré du caractère FIELDS ENCLOSED BY, qui sera lu comme le mot 'NULL').

  • Si FIELDS ESCAPED BY est vide, NULL est écrit comme le mot 'NULL'.

  • Avec le format à taille fixe (ce qui arrive si FIELDS TERMINATED BY et FIELDS ENCLOSED BY sont tous les deux vides), les valeurs NULL sont écrites sous forme de chaîne vide. Notez que cela fait que NULL et les chaînes vides seront représentées par une valeur qui ne les distingue pas l'une de l'autre. Si vous avez besoin de différencier entre les deux, n'utilisez par ce format !

Certains cas ne sont pas supportés par LOAD DATA INFILE:

  • Lignes à tailles fixes (FIELDS TERMINATED BY et FIELDS ENCLOSED BY sont tous les deux vides) et les types de colonne BLOB ou TEXT.

  • Si vous spécifiez un séparateur qui est le même qu'un autre préfixe, LOAD DATA INFILE ne sera pas capable de relire proprement le résultat. Par exemple, la clause FIELDS suivante posera sûrement des problèmes :

    FIELDS TERMINATED BY '"' ENCLOSED BY '"'
    
  • Si FIELDS ESCAPED BY est vide, une valeur de colonne qui contient une occurrence de FIELDS ENCLOSED BY ou de LINES TERMINATED BY suivi du caractère FIELDS TERMINATED BY interrompra la lecture de LOAD DATA INFILE trop tôt. Cela est dû au fait que LOAD DATA INFILE ne peut pas faire la différence entre la valeur dans le champ et la fin de la ligne.

L'exemple suivant charge toutes les colonnes de la table persondata :

mysql> LOAD DATA INFILE 'persondata.txt' INTO TABLE persondata;

Aucun champ n'est spécifié, ce qui fait que LOAD DATA INFILE s'attend à ce que les lignes lues contiennent le bon nombre de champs. Les valeurs par défaut de FIELDS et LINES sont utilisées.

Si vous voulez charger uniquement quelques colonnes dans une table, spécifiez la liste des champs :

mysql> LOAD DATA INFILE 'persondata.txt'
    ->           INTO TABLE persondata (col1,col2,...);

Vous devez aussi spécifier les champs si l'ordre dans lequel ils seront lus diffère de l'ordre des colonnes dans la table. Sinon, MySQL ne pourra pas savoir à quelle colonne correspond une valeur.

Si une ligne a trop peu de champs, les colonnes omises prendront leur valeur par défaut. Les affectations de valeurs par défaut sont décrites dans Section 13.2.5, « Syntaxe de CREATE TABLE ».

Une valeur de champs vide et un champ manquant ne seront pas interprétés de la même fa¸on :

  • Pour les types chaîne, la colonne est remplie avec la chaîne vide.

  • Pour les types numériques, la colonne est mise à 0.

  • Pour les types dates et heures, la colonne est mise au zéro approprié pour le type. See Section 11.3, « Les types date et heure ».

Notez que vous obtiendrez le même résultat en assignant à ces différents types de champs la chaîne vide dans une commande INSERT ou UPDATE.

Les colonnes TIMESTAMP prendront la date et l'heure courante uniquement si on leur affecte la valeur NULL, ou (pour la première colonne TIMESTAMP seulement) si la colonne TIMESTAMP est ignorée de la liste des colonnes spécifiée.

Si une ligne d'entrée comporte trop de colonnes, les champs en trop sont ignorés, et le nombre d'alertes est incrémenté.

LOAD DATA INFILE considère toutes les valeurs lues comme des chaînes de caractères : vous ne pourrez donc pas utiliser la forme numérique des colonnes ENUM ou SET, comme d'habitude. Toutes les colonnes ENUM et SET doivent être spécifiée comme des chaînes ! Si vous utilisez l'API C, vous pouvez obtenir des informations à propos de la requête en utilisant la fonction mysql_info() quand LOAD DATA INFILE se termine. Le format de la chaîne d'informations est le suivant :

Records: 1  Deleted: 0  Skipped: 0  Warnings: 0

Les alertes sont générées dans les mêmes circonstances que pour la commande INSERT (see Section 13.1.4, « Syntaxe de INSERT »), excepté que LOAD DATA INFILE génère aussi des alertes s'il y a trop peu ou trop de champs dans une ligne. Les alertes ne sont pas stockées; le nombre d'alertes est la seule indication. Si vous recevez des alertes et vous voulez savoir exactement ce qui s'est passé, exécutez une commande SELECT ... INTO OUTFILE dans un autre fichier et comparez le avec le fichier original.

En MySQL version 4.1.1 vous pouvez utiliser SHOW WARNINGS pour obtenir la liste des premières max_error_count alertes. See Section 13.5.3.19, « SHOW WARNINGS | ERRORS ».

Pour plus d'informations sur les performances de INSERT comparées à LOAD DATA INFILE et accélérer LOAD DATA INFILE : See Section 7.2.14, « Vitesse des requêtes INSERT ».

13.1.6. Syntaxe de REPLACE

REPLACE [LOW_PRIORITY | DELAYED]
    [INTO] tbl_name [(col_name,...)]
    VALUES ({expr | DEFAULT},...),(...),...

ou :

REPLACE [LOW_PRIORITY | DELAYED]
    [INTO] tbl_name
    SET col_name={expr | DEFAULT}, ...

ou :

REPLACE [LOW_PRIORITY | DELAYED]
    [INTO] tbl_name [(col_name,...)]
    SELECT ...

REPLACE fonctionne exactement comme INSERT, sauf que si une vieille ligne dans la table à la même valeur qu'une nouvelle pour un index UNIQUE ou une PRIMARY KEY, la vielle ligne sera effacée avant que la nouvelle ne soit insérée. See Section 13.1.4, « Syntaxe de INSERT ».

En d'autres termes, vous ne pouvez pas accéder aux valeurs de l'ancienne ligne à partir d'une requête REPLACE. Dans quelques vieilles versions de MySQL, il apparaît que c'était possible, mais c'etait un dysfonctionnement qui a été corrigé depuis.

Pour utiliser REPLACE vous devez avoir les privilèges INSERT et DELETE sur la table.

Quand vous utilisez une commande REPLACE, mysql_affected_rows() retournera 2 si une nouvelle ligne en remplace une existante, et cela parce qu'il y aura eu une insertion puis une suppression.

Cela aide à savoir si REPLACE a ajouté ou a remplacé une ligne : Testez si le nombre de lignes affectées est égal à 1 (ajout) ou s'il est égal à 2 (remplacement).

Notez que si vous n'utilisez pas un index UNIQUE ou une PRIMARY KEY, utiliser un REPLACE n'a pas de sens vu que cela revient à utiliser un INSERT. Il devient équivalent à INSERT, car il n'y a pas d'index à utiliser pour déterminer si un nouvelle ligne est un double d'une autre.

Voici quelques détails sur l'algorithme utilisé : Il est aussi utilisé par LOAD DATA ... REPLACE.

  1. Insertion de la ligne dans la table

  2. Si une erreur de clé dupliqué ou de clé unique ou de clé primaire survient :

    1. Annuler les changements de clés

    2. Essayer à nouveau d'insérer la clé primaire et unique dans la table

13.1.7. Syntaxe de SELECT

SELECT [STRAIGHT_JOIN]
       [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
       [SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS] [HIGH_PRIORITY]
       [DISTINCT | DISTINCTROW | ALL]
    select_expression,...
    [INTO {OUTFILE | DUMPFILE} 'nom_fichier' export_options]
    [FROM table_references
      [WHERE where_definition]
      [GROUP BY {unsigned_integer | nom_de_colonne | formula} [ASC | DESC], ...
      [HAVING where_definition]
      [ORDER BY {unsigned_integer | nom_de_colonne | formula} [ASC | DESC] ,...]
      [LIMIT [offset,] lignes]
      [PROCEDURE procedure_name(argument_list)]
      [FOR UPDATE | LOCK IN SHARE MODE]]

SELECT est utilisé pour obtenir des enregistrements venant d'une ou plusieurs tables. Le support des commandes UNION et des sous-requêtes est disponibles depuis MySQL 4.0 et 4.1, respectivement. Voir Section 13.1.7.2, « Syntaxe de UNION » et Section 13.1.8, « Sous-sélections (SubSELECT) ».

  • Chaque select_expr indique une colonne à lire.

  • table_references indique la ou les tables à utiliser. La syntaxe est décrite dans Section 13.1.7.1, « Syntaxe de JOIN ».

  • where_definition indique les conditions que les lignes sélectionnées doivent satisfaire.

SELECT peut aussi être utilisée pour lire des lignes calculées, sans référence à une table.

Par exemple :

mysql> SELECT 1 + 1;
         -> 2

Tous les mots-clés utilisés doivent être donnés exactement dans le même ordre que ci-dessus. Par exemple, une clause HAVING doit être placée après toute clause GROUP BY et avant toute clause ORDER BY.

  • Une expression SELECT peut recevoir un alias en utilisant AS. L'alias est utilisé de la même fa¸on que le nom du champ et peut être employé avec des clauses ORDER BY ou HAVING. Par exemple :

    mysql> SELECT CONCAT(last_name,', ',first_name) AS full_name
        FROM mytable ORDER BY full_name;
    

    Le mot clé AS est optionnel lors de la création d'un alias pour une expression SELECT. L'exemple précédent aurait pu être écrit comme ceci :

    mysql> SELECT CONCAT(last_name,', ',first_name) full_name
        FROM mytable ORDER BY full_name;
    

    Comme AS est optionnel, un problème subtil peut survenir si vous oubliez une virgule entre deux expressions de SELECT : MySQL va interpréter la seconde comme un alias de la première. Par exemple, dans la commande suivante, columnb est traité comme un nom d'alias :

    mysql> SELECT columna columnb FROM mytable;
    
  • Il n'est pas possible d'utiliser un alias de champ dans une clause WHERE, car la valeur du champ peut ne pas être définie lorsque la clause WHERE est exécutée. See Section A.5.4, « Problèmes avec les alias ».

  • La clause FROM table_references indique les tables à partir desquelles nous allons obtenir les enregistrements. Si vous indiquez le nom de plusieurs tables, vous faites une jointure. Pour davantage d'informations sur la syntaxe des jointures, consultez Section 13.1.7.1, « Syntaxe de JOIN ». Pour chaque table spécifiée, vous pouvez éventuellement indiquer un alias.

    tbl_name [[AS] alias]
        [[USE INDEX (key_list)]
          | [IGNORE INDEX (key_list)]
          | [FORCE INDEX (key_list)]]
    

    L'utilisation de USE INDEX, IGNORE INDEX, FORCE INDEX pour donner des conseils d'optimisation à l'optimiseur d'index. Section 13.1.7.1, « Syntaxe de JOIN ».

    En MySQL 4.0.14, vous pouvez utiliser SET MAX_SEEKS_FOR_KEY=value comme une alternative pour forcer MySQL à choisir un scan d'index, plutôt qu'un scan de table.

  • Vous pouvez faire référence à une table avec nom_de_table (au sein de la base de données courante), ou avec dbname.nom_de_table pour expliciter le nom de la base de données. Vous pouvez vous référer à un champ avec nom_de_colonne, nom_de_table.nom_de_colonne, ou db_name.nom_de_table.nom_de_colonne. Vous n'êtes pas obligés d'indiquer de préfixe nom_de_table ou db_name.nom_de_table pour une référence à un champ dans un SELECT, à moins que la référence ne soit ambigue. Consultez Section 9.2, « Noms de bases, tables, index, colonnes et alias », pour des exemples d'ambiguîtés qui nécessitent des formes plus explicites de référence à des champs.

  • Depuis la version 4.1.0, vous êtes autorisés à spécifier DUAL comme nom de table, dans les situations ou aucune table n'est référencé. C'est une fonctionnalité pure de compatibilité, car certains autres serveurs requièrent cette syntaxe.

    mysql> SELECT 1 + 1 FROM DUAL;
             -> 2
    
  • Une référence à une table peut être aliasée en utilisant nom_de_table [AS] alias_name :

    mysql> SELECT t1.name, t2.salary FROM employee AS t1, info AS t2
        ->        WHERE t1.name = t2.name;
    mysql> SELECT t1.name, t2.salary FROM employee t1, info t2
        ->        WHERE t1.name = t2.name;
    
  • Dans la clause WHERE, vous pouvez utiliser toutes les fonctions que MySQL supporte, hormis les fonctions d'agrégation. See Chapitre 12, Fonctions à utiliser dans les clauses SELECT et WHERE.

  • Les colonnes sélectionnées dans le résultat peuvent être nommées dans les clauses ORDER BY et GROUP BY en utilisant leur nom de colonne, les alias ou leur position de colonne. Les positions commencent à 1 :

    mysql> SELECT college, region, seed FROM tournament
        ->        ORDER BY region, seed;
    mysql> SELECT college, region AS r, seed AS s FROM tournament
        ->        ORDER BY r, s;
    mysql> SELECT college, region, seed FROM tournament
        ->        ORDER BY 2, 3;
    

    Pour trier dans l'ordre inverse, ajoutez le mot-clé DESC (descendant) au nom du champ dans la clause ORDER BY qui vous permet de trier. Par défaut, l'ordre ascendant est utilisé; ceci peut être indiqué de fa¸on explicite en utilisant le mot-clé ASC.

    L'utilisation des positions de colonnes est obsolète, car la syntaxe a été supprimée du SQL standard.

  • Si vous utilisez GROUP BY, les lignes sont triées en fonction des colonnes GROUP BY comme si on avait ajouté la clause ORDER BY pour ces colonnes. MySQL a amélioré la clause GROUP BY depuis la version 3.23.34 pour que vous puissiez aussi spécifier ASC et DESC après le nom de la colonne :

    SELECT a, COUNT(b) FROM test_table GROUP BY a DESC
    
  • MySQL améliore l'utilisation de GROUP BY en vous autorisant à l'utiliser avec des champs qui ne sont pas mentionnés dans la clause GROUP BY. Si vous n'obtenez pas les résultats que vous attendiez, lisez la description de GROUP BY. See Section 12.9, « Fonctions et options à utiliser dans les clauses GROUP BY ».

  • Depuis MySQL 4.1.1, GROUP BY dispose de l'option WITH ROLLUP. See Section 12.9.2, « Options de GROUP BY ».

  • La clause HAVING peut faire référence à n'importe quel champs ou alias défini dans select_expr. C'est évalué en dernier lieu, juste avant que les éléments ne soient envoyés au client, sans aucune optimisation.

  • N'utilisez pas HAVING pour des éléments qui devraient être dans la clause WHERE. Par exemple, n'écrivez pas ceci :

    mysql> SELECT nom_de_colonne FROM nom_de_table HAVING nom_de_colonne > 0;
    

    Ecrivez plutôt cela :

    mysql> SELECT nom_de_colonne FROM nom_de_table WHERE nom_de_colonne > 0;
    

    Dans les versions 3.22.5 et supérieures de MySQL, vous pouvez aussi écrire des requêtes ainsi :

    mysql> SELECT user,MAX(salary) FROM users
        ->        GROUP BY user HAVING MAX(salary)>10;
    

    Dans des versions plus anciennes de MySQL, vous pouvez écrire à la place :

    mysql> SELECT user,MAX(salary) AS sum FROM users
        ->        group by user HAVING sum>10;
    
  • La clause HAVING peut utiliser des fonctions d'agrégation, alors que la clause WHERE ne le peut pas :

    mysql> SELECT user, MAX(salary) FROM users
        ->     GROUP BY user HAVING MAX(salary)>10;
    

    Cependant, cela ne fonctionne pas dans les anciennes versions du serveur MySQL, : avant la version 3.22.5. Au lieu de cela, ajoutez un alias de colonne dans la liste de colonnes, et faites référence à cet alias dans la colonne HAVING :

    mysql> SELECT user, MAX(salary) AS max_salary FROM users
        ->     GROUP BY user HAVING max_salary>10;
    
  • La clause LIMIT peut être utilisée pour limiter le nombre d'enregistrements retournés par la commande SELECT. LIMIT accepte un ou deux arguments numériques. Ces arguments doivent être des entiers constants.

    Avec un argument, la valeur spécifie le nombre de lignes à retourner depuis le début du jeu de résultat. Si deux arguments sont donnés, le premier indique le décalage du premier enregistrement à retourner, le second donne le nombre maximum d'enregistrement à retourner. Le décalage du premier enregistrement est 0 (pas 1) :

    Pour être compatible avec PostgreSQL, MySQL supporte aussi la syntaxe : LIMIT row_count OFFSET offset.

    mysql> SELECT * FROM table LIMIT 5,10;  # Retourne les enregistrements 6 à 15
    

    Pour obtenir tous les enregistrement d'un certain décalage jusqu'à la fin du résultat, vous pouvez utiliser de grands entier en tant que second paramètre :

    mysql> SELECT * FROM table LIMIT 95,18446744073709551615; # Retourne les enregistrements de 96 jusqu'au dernier.
    

    Si un seul argument est donné, il indique le nombre maximum d'enregistrements à retourner :

    mysql> SELECT * FROM table LIMIT 5;     # Retourne les 5 premiers enregistrements
    

    Autrement dit, LIMIT n est équivalent à LIMIT 0,n.

  • La forme SELECT ... INTO OUTFILE 'nom_fichier' de SELECT écrit les lignes sélectionnées dans un fichier. Le fichier est crée sur le serveur et ne peut y être déjà présent (cela permet entre autre d'éviter la destruction des tables et de fichiers tel que /etc/passwd). Vous devez avoir le droit FILE sur le serveur pour utiliser cette forme de SELECT.

    SELECT ... INTO OUTFILE à pour but principal de vous permettre de réaliser des dumps rapides des tables sur la machine serveur. Si vous voulez créer le fichier sur une autre machine, vous ne pouvez utiliser SELECT ... INTO OUTFILE. Dans ce cas là, vous pouvez utiliser à la place un programme client comme mysqldump --tab ou mysql -e "SELECT ..." > fichier pour générer le fichier.

    SELECT ... INTO OUTFILE est le complément de LOAD DATA INFILE; La syntaxe pour la partie export_options de la requête se compose des mêmes clauses FIELDS et LINES que celles utilisées avec la commande LOAD DATA INFILE. See Section 13.1.5, « Syntaxe de LOAD DATA INFILE ».

    Dans le fichier résultant, seul les caractères suivants sont protégés par le caractère ESCAPED BY :

    • Le caractère ESCAPED BY

    • Les premier caractère de FIELDS TERMINATED BY

    • Les premier caractère de LINES TERMINATED BY

    • ASCII 0 est convertit en ESCAPED BY suivi de 0 (ASCII 48).

    Si le caractère FIELDS ESCAPED BY est vide, aucun caractère n'est protégé, et NULL vaut NULL, et non \N. Il est probable que ce ne soit pas une bonne idée de spécifier un caractère de protection vide, en particulier sir les valeurs de vos champs peuvent être n'importe quoi.

    La raison de ce qui précède est que vous devez impérativement protéger chaque caractère FIELDS TERMINATED BY, ESCAPED BY, ou LINES TERMINATED BY pour assurer une relecture fiable du fichier. Le caractère ASCII 0 est échappé pour assurer la lisibilité sur certains clients.

    Comme le fichier résultant ne se doit pas d'être syntaxiquement conforme à SQL, vous n'avez besoin d'échapper rien d'autre.

    Voila un exemple de relecture de fichier au format utilisé par plusieurs anciens programmes.

    SELECT a,b,a+b INTO OUTFILE '/tmp/result.text'
    FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
    LINES TERMINATED BY '\n'
    FROM test_table;
    
  • Si vous utilisez INTO DUMPFILE au lieu de INTO OUTFILE, MySQL n'écrira qu'une seule ligne dans le fichier, sans aucun caractère de fin de ligne ou de colonne, ni d'échappement. Cela est utile lorsque vous voulez enregistrer un BLOB dans un fichier.

  • Note : notez que les fichiers crées par INTO OUTFILE et INTO DUMPFILE sera lisible par tout les utilisateurs ! La raison est que le serveur MySQL ne peut créer de fichier appartenant à autre que l'utilisateur qui l'a mis en route. (vous devez éviter d'exécuter mysqld en tant que root), le fichier doit se composer de mot lisible pour que les données puissent être récupérées.

  • Une clause PROCEDURE indique une procédure qui doit traiter les lignes du jeu de résultat. Pour un exemple, voyez Section 27.3.1, « La procédure Analyse ».

  • Si vous utilisez la clause FOR UPDATE avec un gestionnaire de tables qui gère les verrous de lignes ou de pages, les lignes seront verrouillées.

Après le mot SELECT, vous pouvez ajouter certaines options qui affectent le comportement de la commande.

Les options DISTINCT, DISTINCTROW et ALL indiquent quels enregistrements avec doublons doivent être retournés. Par défaut, c'est (ALL), retournant ainsi tous les enregistrements. DISTINCT et DISTINCTROW sont synonymes et indique que les doublons doivent être éliminés du résultat.

HIGH_PRIORITY, STRAIGHT_JOIN, et les options commen¸ants par SQL_ sont des extensions MySQL au standard SQL.

  • HIGH_PRIORITY donne à une commande SELECT une plus grande priorité qu'une commande qui modifie une table. Vous devez l'utiliser seulement pour les requêtes qui sont très rapides et qui doivent être effectuées en premier lieu. Une requête SELECT HIGH_PRIORITY s'exécutera sur une table verrouillée en lecture même si une commande de mise à jour attend que la table soit libérée.

    HIGH_PRIORITY ne peut être utilisée avec les commandes SELECT qui font partie d'une UNION.

  • STRAIGHT_JOIN force l'optimiseur à joindre les tables dans l'ordre dans lequel elles sont listées dans la clause FROM. Vous pouvez utiliser cela pour accélérer la requête, si les tables sont réordonnées sub-optimalement par l'optimiseur. See Section 7.2.1, « Syntaxe de EXPLAIN (Obtenir des informations sur les SELECT) ». STRAIGHT_JOIN peut aussi être utilisée dans la liste table_references. See Section 13.1.7.1, « Syntaxe de JOIN ».

  • SQL_BIG_RESULT peut être utilisé avec GROUP BY ou DISTINCT pour indiquer à l'optimiseur que le résultat comportera beaucoup d'enregistrements. Dans ce cas, MySQL utilisera si besoin directement les bases temporaires stockées sur le disque. MySQL préférera, dans ce cas, trier que d'obtenir une table temporaire avec une clé sur les éléments du GROUP BY.

  • SQL_BUFFER_RESULT forcera le résultat à être stocké dans une table temporaire. Ceci va aider MySQL à libérer plus tôt les verrous des tables et aidera aussi dans les cas ou l'envoi du résultat au client prend un temps assez conséquent.

  • SQL_SMALL_RESULT, une option spécifique à MySQL, peut être utilisée avec GROUP BY ou DISTINCT pour indiquer à l'optimiseur que le résultat sera petit. Dans ce cas, MySQL utilise des tables temporaires rapides pour stocker la table résultante plutôt que d'utiliser le tri. Dans MySQL 3.23, ceci n'est normalement pas nécessaire.

  • SQL_CALC_FOUND_ROWS (version 4.0.0 et supérieure) indique à MySQL de calculer combien d'enregistrements seront dans le jeu de résultats, indépendamment de n'importe quelle clause LIMIT. Le nombre d'enregistrements peut alors être obtenu avec SELECT FOUND_ROWS(). See Section 12.8.4, « Fonctions diverses ».

    Avant MySQL 4.1.0, cette option ne fonctionne pas avec LIMIT 0, qui est optimisée pour se terminer instantanément (le résultat ne contiendra pas de lignes). See Section 7.2.12, « Comment MySQL optimise LIMIT ».

  • SQL_CACHE demande à MySQL de ne pas stocker le résultat de la requête si vous utilisez query_cache_type avec la valeur 2 ou DEMAND. Pour une requête qui utilise UNION ou une sous-requête, cette option prend effet si elle est utilisée dans n'importe quelle partie de la requête SELECT. See Section 5.11, « Cache de requêtes MySQL ».

  • SQL_CACHE indique à MySQL de stocker le résultat de la requête dans le cache de requêtes si vous utilisez QUERY_CACHE_TYPE=2 (DEMAND). See Section 5.11, « Cache de requêtes MySQL ». Pour les requêtes qui utilisent UNION ou les sous-requêtes, cette option aura un effet sur toutes les parties de la requête SELECT.

13.1.7.1. Syntaxe de JOIN

MySQL supporte les syntaxes suivantes de JOIN pour une utilisation dans les SELECT :

reference_table, reference_table
reference_table [CROSS] JOIN reference_table
reference_table INNER JOIN reference_table condition_jointure
reference_table STRAIGHT_JOIN reference_table
reference_table LEFT [OUTER] JOIN reference_table condition_jointure
reference_table LEFT [OUTER] JOIN reference_table
reference_table NATURAL [LEFT [OUTER]] JOIN reference_table
{ OJ reference_table LEFT OUTER JOIN reference_table ON expr_conditionnelle }
reference_table RIGHT [OUTER] JOIN reference_table condition_jointure
reference_table RIGHT [OUTER] JOIN reference_table
reference_table NATURAL [RIGHT [OUTER]] JOIN reference_table

reference_table est définie de la manière suivante :

nom_de_table [[AS] alias] [USE INDEX (liste_de_clefs)] [IGNORE INDEX (liste_de_clefs)]

et condition_jointure est définie comme suit :

ON expr_conditionnelle |
USING (column_list)

Généralement, vous ne devez avoir aucune condition, dans la partie ON, qui soit utilisée pour spécifier les lignes que vous voulez obtenir en résultat. (il y a des exceptions à cette règle). Si vous voulez restreindre les lignes résultantes, vous devez le faire dans la clause WHERE.

Notez que dans les versions antérieures à la 3.23.17, INNER JOIN ne prenait pas en compte condition_jointure !

La dernière syntaxe de LEFT OUTER JOIN vue plus haut, n'existe que pour assurer la compatibilité avec ODBC :

  • On peut créer un alias sur une référence de table en utilisant nom_de_table AS alias_name ou nom_de_table alias_name :

    mysql> SELECT t1.name, t2.salary FROM employee AS t1, info AS t2
        ->        WHERE t1.name = t2.name;
    
  • La condition ON est de la même forme qu'une condition pouvant être utilisée dans la clause WHERE.

  • Si aucune ligne ne correspond dans la table de droite dans la partie ON ou USING du LEFT JOIN, une ligne avec toutes les colonnes mises à NULL est utilisé en remplacement. Vous pouvez utiliser ce fait pour trouver les enregistrements dans une table qui n'ont pas de correspondances dans une autre :

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

    Cet exemple retourne toutes les lignes trouvées dans table1 avec une valeur de id qui n'est pas présente dans table2 (autrement dit, toutes les lignes de table1 sans correspondances dans la table table2). Cela demande que table2.id soit déclaré NOT NULL, bien sur. See Section 7.2.9, « Comment MySQL optimise les clauses LEFT JOIN et RIGHT JOIN ».

  • La clause USING (column_list) recense la liste des colonnes qui doivent exister dans les deux tables. Les clauses USING suivantes sont identiques :

    a LEFT JOIN b USING (c1,c2,c3)
    a LEFT JOIN b ON a.c1=b.c1 AND a.c2=b.c2 AND a.c3=b.c3
    
  • La jointure de deux tables avec NATURAL [LEFT] JOIN est définie pour être sémantiquement équivalent à un INNER JOIN ou un LEFT JOIN avec une clause USING qui nomme toutes les colonnes qui existent dans les deux tables.

  • INNER JOIN et , (virgule) sont sémantiquement équivalents. Les deux opèrent une jointure totale sur les tables utilisées. Normalement, vous spécifiez les conditions de jointure dans la clause WHERE.

  • RIGHT JOIN fonctionne de fa¸on analogue à LEFT JOIN. Pour garder un code facilement portable, il est recommandé d'utiliser les LEFT JOIN à la place des RIGHT JOIN.

  • STRAIGHT_JOIN est identique à JOIN, sauf que la table de gauche est toujours lues avant celle de droite. Cela peut être utilisé dans les cas (rares) où l'optimiseur des jointures place les tables dans le mauvais ordre.

A partir de la version 3.23.12 de MySQL, vous pouvez donner des indications à propos de l'index à utiliser lors de la lecture d'informations d'une table. C'est utile si EXPLAIN montre que MySQL utilise un mauvais index de la liste de ceux disponibles. En spécifiant USE INDEX (liste_de_clefs), vous pouvez forcer MySQL à utiliser un index spécifique pour trouver les enregistrements dans la table. Une alternative réside dans l'utilisation de IGNORE INDEX (liste_de_clefs) pour dire à MySQL de ne pas utiliser certains index.

En MySQL 4.0.9, vous pouvez aussi utiliser la clause FORCE INDEX. Elle se comporte comme USE INDEX (key_list) mais en supposant que les scan de tables seront très coûteux. En d'autres termes, les scans de tables seront utilisés que s'il n'y a pas d'autres méthodes pour trouver les lignes.

USE/IGNORE KEY sont des synonymes de USE/IGNORE INDEX.

Note : USE INDEX, IGNORE INDEX et FORCE INDEX affectent uniquement les index qui sont utilisés lors du choix de la méthode de sélection des lignes dans la table, et comment faire une jointure. Elles n'affectent pas l'utilisation finale de l'index dans les clauses ORDER BY ou GROUP BY.

Quelques exemples :

mysql> SELECT * FROM table1,table2 WHERE table1.id=table2.id;
mysql> SELECT * FROM table1 LEFT JOIN table2 ON table1.id=table2.id;
mysql> SELECT * FROM table1 LEFT JOIN table2 USING (id);
mysql> SELECT * FROM table1 LEFT JOIN table2 ON table1.id=table2.id
    ->          LEFT JOIN table3 ON table2.id=table3.id;
mysql> SELECT * FROM table1 USE INDEX (key1,key2)
    ->          WHERE key1=1 AND key2=2 AND key3=3;
mysql> SELECT * FROM table1 IGNORE INDEX (key3)
    ->          WHERE key1=1 AND key2=2 AND key3=3;

See Section 7.2.9, « Comment MySQL optimise les clauses LEFT JOIN et RIGHT JOIN ».

13.1.7.2. Syntaxe de UNION

SELECT ...
UNION [ALL | DISTINCT]
SELECT ...
  [UNION [ALL | DISTINCT]
   SELECT ...]

UNION est implémentée en MySQL 4.0.0.

UNION est utilisé pour combiner le résultat de plusieurs requêtes SELECT en un seul résultat.

Les colonnes listées dans la partie select_expression du SELECT doivent être du même type. Les noms de colonnes utilisés dans le premier SELECT seront utilisé comme nom de champs pour les résultats retournés.

Les commandes SELECT sont des sélections normales, mais avec les restrictions suivantes :

  • Seule la dernière commande SELECT peut avoir une clause INTO OUTFILE.

  • HIGH_PRIORITY ne peut être utilisée avec les commandes SELECT qui ne font pas partie de l'UNION. Si vous la spécifiez pour la première commande SELECT, elle n'aura pas d'effet. Si vous la spécifiez pour toute autre commandes SELECT suivante, une erreur de syntaxe sera signalée.

Si vous n'utilisez pas le mot clef ALL pour l'UNION, toutes les lignes retournées seront uniques, comme si vous aviez fait un DISTINCT pour l'ensemble du résultat. Si vous spécifiez ALL, vous aurez alors tout les résultats retournés par toutes les commandes SELECT.

Si vous voulez utiliser un ORDER BY pour le résultat final de UNION, vous devez utiliser des parenthèses :

(SELECT a FROM nom_de_table WHERE a=10 AND B=1 ORDER BY a LIMIT 10)
UNION
(SELECT a FROM nom_de_table WHERE a=11 AND B=2 ORDER BY a LIMIT 10)
ORDER BY a;

Note : vous ne pouvez pas mélanger les clauses UNION ALL et UNION DISTINCT dans la même requête. Si vous utilisez ALL dans une des UNION, alors elle devra être utilisée partout.

Les types et longueurs des colonnes du jeu de résultat de UNION prend en compte les valeurs lues dans tous les SELECT. Avant MySQL 4.1.1, une limitation de UNION est que seules les valeurs du premier SELECT étaient utilisée pour déterminer le type de résultats, et leur taille. Cela peut conduire à un raccourcissement de la valeur si, par exemple, le second SELECT trouvait des valeurs plus grandes que le premier SELECT :

mysql> SELECT REPEAT('a',1) UNION SELECT REPEAT('b',10);
+---------------+
| REPEAT('a',1) |
+---------------+
| a             |
| b             |
+---------------+

Cette limitation a été supprimée en MySQL version 4.1.1 :

mysql> SELECT REPEAT('a',1) UNION SELECT REPEAT('b',10);
+---------------+
| REPEAT('a',1) |
+---------------+
| a             |
| bbbbbbbbbb    |
+---------------+

13.1.8. Sous-sélections (SubSELECT)

Une sous-requête est une commande SELECT dans une autre commande. Par exemple :

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

La requête externe (ou commande externe), et (SELECT column1 FROM t2) est la sous-requête. Nous disons que la sous-requête est imbriquée dans la requête externe, et en fait, il est possible d'imbriquer des requêtes dans des sous-requêtes, avec d'autres commandes. Une sous-requête doit toujours être entre parenthèses.

Depuis la version 4.1, MySQL supporte toutes les formes de sous-requêtes et opérations que le standard SQL requiert, ainsi que quelques fonctionnalités spécifiques. Les avantages des sous-requêtes sont :

  • Elles permettent aux requêtes d'être structuré pour que chaque partie puisse être isolée.

  • Elles fournissent une méthode pour réaliser des opérations qui seraient complexes, et impliqueraient des unions et jointures.

  • Elles sont, au dire de nombreuses personnes, lisibles. En fait, c'est les sous-requêtes qui ont donné aux inventeurs le nom original de SQL ``Structured Query Language''.

Dans les versions plus anciennes de MySQL, il fallait trouver des palliatifs, et contourner les sous-requêtes. Il est bien plus facile de se mettre aux sous-requêtes.

Voici un exemple de commande qui montre les principaux avantages des sous-requêtes et de leur syntaxe, aussi bien pour celle qui est proposée par le standard, que celle de MySQL.

DELETE FROM t1
WHERE s11 > ANY
 (SELECT COUNT(*) /* no hint */ FROM t2
 WHERE NOT EXISTS
  (SELECT * FROM t3
   WHERE ROW(5*t2.s1,77)=
    (SELECT 50,11*s1 FROM t4 UNION SELECT 50,77 FROM
     (SELECT * FROM t5) AS t5)));

Pour les versions de MySQL antérieure à la version 4.1, la plupart des sous requêtes peuvent être réécrites avec des jointures et d'autres méthodes. See Section 13.1.8.11, « Se passer des sous-requêtes avec les premières versions de MySQL ».

13.1.8.1. Les sous-requêtes comme opérateur scalaire

Dans dans forme la plus simple, une sous-requête scalaire, par opposition à une sous-requête de ligne ou de table qui seront présentées plus loin, est un simple opérande. Vous pouvez l'utilisez à chaque fois qu'une valeur de colonne ou qu'une valeur littérale est valide, et vous pouvez en attendre les mêmes caractéristiques : type de données, taille et indication de nullité, etc. Par exemple :

CREATE TABLE t1 (s1 INT, s2 CHAR(5) NOT NULL);
SELECT (SELECT s2 FROM t1);

La sous-requête de la commande SELECT ci-dessus est de type CHAR, de longueur 5. Son jeu de caractères et sa collation sont ceux fournis par défaut, et elle porte une marque de nullité. En fait, toutes les sous-requêtes peuvent prendre la valeur NULL, car si la table est vide, la valeur de la sous-requête sera alors NULL. Il y a quelques restrictions :

  • Une sous-requête peut être utilisée avec les commandes suivantes : SELECT, INSERT, UPDATE, DELETE, SET et DO.

  • Une sous-requête peut contenir les mots-clé et les clauses qu'une commande SELECT peut contenir : DISTINCT, GROUP BY, ORDER BY, LIMIT, jointures, UNION, commentaires, fonctions, etc.

Ainsi, lorsque vous lirez les exemples des sections suivantes qui utilisent la commande spartiate (SELECT column1 FROM t1), imaginez que votre code pourra contenir des commandes bien plus diverses et complexes.

Par exemple, supposons que nous avons ces deux tables :

CREATE TABLE t1 (s1 INT);
INSERT INTO t1 VALUES (1);
CREATE TABLE t2 (s1 INT);
INSERT INTO t2 VALUES (2);

Puis, que vous envoyons la commande suivante SELECT :

SELECT (SELECT s1 FROM t2) FROM t1;

Le résultat sera 2 car il y a une ligne dans t2, dont la colonne s1 a une valeur de 2.

La sous-requête peut faire partie d'une expression. Si c'est un opérande d'une fonction, n'oubliez pas les parenthèses.

Par exemple :

SELECT UPPER((SELECT s1 FROM t1)) FROM t2;

13.1.8.2. Comparaisons avec les sous-requêtes

L'utilisation la plus répandue des sous-requêtes est celle-ci :

<non-subquery operand> <comparison operator> (<subquery>)

<comparison operator> est l'un des opérateurs suivants :

= > < >= <= <>

Par exemple :

... 'a' = (SELECT column1 FROM t1)

Il fut un temps où la seule place possible pour une sous-requête était à la droite de l'opérateur de comparaison, mais vous pourrez rencontrer de vieilles bases qui insisteront sur ce point.

Voici un exemple de comparaison classiques, pour lequel vous ne pouvez pas utiliser de jointure : trouvez toutes les valeurs de la table t1 qui sont égales au maximum de la valeur dans la table t2.

SELECT column1 FROM t1
       WHERE column1 = (SELECT MAX(column2) FROM t2);

Voici un autre exemple, qui est aussi impossible à réaliser avec une jointure, car elle impose l'agrégation de plusieurs tables : trouver toutes les lignes de la table t1 qui contiennent une valeur qui apparaît deux fois.

SELECT * FROM t1
       WHERE 2 = (SELECT COUNT(column1) FROM t1);

13.1.8.3. Sous-requêtes avec les clauses ANY, IN et SOME

Syntaxe :

<operand> <comparison operator> ANY (<subquery>)
<operand> IN (<subquery>)
<operand> <comparison operator> SOME (<subquery>)

Le mot ANY, qui doit suivre immédiatement un opérateur de comparaison, signifie : ``retourne TRUE si la comparaison est TRUE pour UNE des lignes que la sous-requête retourne.'' Par exemple :

SELECT s1 FROM t1 WHERE s1 > ANY (SELECT s1 FROM t2);

Supposons qu'il y ait une ligne dans la table t1 qui contienne {10}. L'expression est TRUE si la table t2 contient {21,14,7} car il y a une valeur de t2, 7, qui est inférieure à 10. Cette expression est FALSE si la table t2 contient {20,10}, ou si la table t2 est vide. L'expression est UNKNOWN si la table t2 contient {NULL,NULL,NULL}.

Le mot IN est un alias de = ANY. Les deux commandes suivantes sont identiques :

SELECT s1 FROM t1 WHERE s1 = ANY (SELECT s1 FROM t2);
SELECT s1 FROM t1 WHERE s1 IN    (SELECT s1 FROM t2);

Le mot SOME est un alias de ANY. Les deux commandes suivantes sont identiques :

SELECT s1 FROM t1 WHERE s1 <> ANY  (SELECT s1 FROM t2);
SELECT s1 FROM t1 WHERE s1 <> SOME (SELECT s1 FROM t2);

L'utilisation du mot SOME est rare, mais les exemples ci-dessus montrent pourquoi il peut être utile. En langage parlé, ``a n'est pas égal à aucun b'' signifie pour la majorité des gens, ``il n'y a pas de b qui est égal à a'' : ce n'est pas la signification de la syntaxe SQL. En utilisant <> SOME, vous pouvez vous assurer que tout le monde comprend le véritable sens de la commande.

13.1.8.4. Sous-requêtes avec ALL

Syntaxe :

<operand> <comparison operator> ALL (<subquery>)

Le mot ALL, qui doit suivre immédiatement l'opérateur de comparaison, signifie ``retourne TRUE si la comparaison est TRUE pour TOUTES les lignes que la sous-requête retourne''.

Par exemple :

SELECT s1 FROM t1 WHERE s1 > ALL (SELECT s1 FROM t2);

Supposons qu'il y ait une ligne dans la table t1 contenant {10}. L'expression est TRUE si la table t2 contient {-5,0,+5} car les trois valeurs de t2 sont inférieures à 10. L'expression est FALSE si la table t2 contient {12,6,NULL,-100} car il y a une des valeurs de la table t2, ici 12, qui est plus grande que 10. L'expression est UNKNOWN si la table t2 contient {0,NULL,1}.

Finalement, si la table t2 est vide, le résultat est TRUE. Vous pouvez pensez que le résultat doit être indéterminé (UNKNOWN), mais c'est bien TRUE. Ce qui fait que, bizarrement,

SELECT * FROM t1 WHERE 1 > ALL (SELECT s1 FROM t2);

est TRUE si la table t2 est vide, mais

SELECT * FROM t1 WHERE 1 > (SELECT s1 FROM t2);

est UNKNOWN si la table t2 est vide. De plus,

SELECT * FROM t1 WHERE 1 > ALL (SELECT MAX(s1) FROM t2);

est UNKNOWN si la table t2 est vide. En général, les tables avec des NULL et les tables vides sont des cas particuliers : lorsque vous écrivez vos sous-requêtes, pensez bien à les prendre en compte.

13.1.8.5. Sous-requêtes de ligne

Jusqu'ici, nous avons étudié les sous-requêtes scalaires, ou de colonnes : des sous-requêtes qui retournent une seule valeur dans une ligne. Une sous-requête de ligne est une variante qui retourne une seule ligne : elle peut donc retourner plusieurs colonnes. Voici deux exemples :

SELECT * FROM t1 WHERE (1,2) = (SELECT column1, column2 FROM t2);
SELECT * FROM t1 WHERE ROW(1,2) = (SELECT column1, column2 FROM t2);

Les requêtes ci-dessus sont toutes les deux TRUE si la table t2 a une ligne où column1 = 1 et column2 = 2.

L'expression (1,2) est parfois appelée un constructeur de ligne et est valide dans d'autres contextes. Par exemple, les deux commandes suivantes sont sémantiquement équivalentes, même si la précédente peut être optimisée :

SELECT * FROM t1 WHERE (column1,column2) = (1,1);
SELECT * FROM t1 WHERE column1 = 1 AND column2 = 1;

L'utilisation traditionnelle des constructeurs de ligne est lors des comparaisons avec des sous-requêtes qui retournent plusieurs colonnes. Par exemple, cette requête répond à la question : ``trouve toutes les lignes de la table t1 qui sont dupliquées dans la table t2'':

SELECT column1,column2,column3
       FROM t1
       WHERE (column1,column2,column3) IN
             (SELECT column1,column2,column3 FROM t2);

13.1.8.6. EXISTS et NOT EXISTS

Si une sous-requête retourne absolument aucune valeur, alors la clause EXISTS <subquery> est TRUE, et la clause NOT EXISTS <subquery> est FALSE. Par exemple :

SELECT column1 FROM t1 WHERE EXISTS (SELECT * FROM t2);

Traditionnellement, une sous-requête qui EXISTS commence avec SELECT * mais elle peut commencer aussi bien avec SELECT 5 ou SELECT column1 ou n'importe quoi d'autre encore : MySQL ignore la liste de colonnes du SELECT de cette requête, ce qui fait que cela n'a pas d'importance.

Dans l'exemple ci-dessus, si la table t2 ne contient aucune ligne, même pas de ligne avec uniquement des valeurs NULL, alors la condition EXISTS est TRUE. C'est un exemple plutôt exceptionnel, car il y a presque toujours une sous-requête [NOT] EXISTS qui contiendra des corrélations. Voici des exemples plus concrets :

  • Quel type de magasin est le plus fréquent dans une ou plusieurs villes?

    SELECT DISTINCT store_type FROM Stores
      WHERE EXISTS (SELECT * FROM Cities_Stores
                    WHERE Cities_Stores.store_type = Stores.store_type);
    
  • Quel type de magasin n'est présent dans aucune villes?

    SELECT DISTINCT store_type FROM Stores
      WHERE NOT EXISTS (SELECT * FROM Cities_Stores
                        WHERE Cities_Stores.store_type = Stores.store_type);
    
  • Quel type de magasin est présent dans toutes les villes?

    SELECT DISTINCT store_type FROM Stores S1
      WHERE NOT EXISTS (
        SELECT * FROM Cities WHERE NOT EXISTS (
          SELECT * FROM Cities_Stores
           WHERE Cities_Stores.city = Cities.city
           AND Cities_Stores.store_type = Stores.store_type));
    

Le dernier exemple est une double imbrication de requête NOT EXISTS : elle possède une clause NOT EXISTS à l'intérieur de la clause NOT EXISTS. Formellement, elle répond à la question : ``Existe-t-il une ville avec un magasin qui n'est pas dans Stores?''. Mais il est plus facile de dire qu'une clause NOT EXISTS imbriquée répond à la question ``est-ce que x est vrai pour tous les y?''.

13.1.8.7. Sous-requêtes corrélées

Une sous-requête corrélée est une sous-requête qui contient une référence à une colonne qui est aussi dans une requête différente.

Par exemple :

SELECT * FROM t1 WHERE column1 = ANY
       (SELECT column1 FROM t2 WHERE t2.column2 = t1.column2);

Notez que dans notre exemple, la sous-requête contient une référence à une colonne de la table t1, même si la sous-requête de la clause FROM ne mentionne pas la table t1. MySQL recherche hors de la requête et trouve t1 dans la requête externe.

Supposez que la table t1 contienne une ligne où column1 = 5 et column2 = 6; alors que la table t2 continue une ligne où column1 = 5 et column2 = 7. l'expression ... WHERE column1 = ANY (SELECT column1 FROM t2) sera alors TRUE, mais dans cet exemple, la clause WHERE de la sous-requête est FALSE (car 7 <> 5), et donc, toute la sous-requête est FALSE.

Règles de contexte : MySQL fait les évaluations de l'intérieur vers l'extérieur. Par exemple :

SELECT column1 FROM t1 AS x
  WHERE x.column1 = (SELECT column1 FROM t2 AS x
    WHERE x.column1 = (SELECT column1 FROM t3 WHERE x.column2 = t3.column1));

Dans l'exemple ci-dessus, x.column2 doit être une colonne de la table t2 car SELECT column1 FROM t2 AS x ... prend le nom de t2. ce n'est pas une colonne de la table t1 car SELECT column1 FROM t1 ... est une requête externe, qui est à venir.

Pour les sous-requêtes placées dans des clauses HAVING ou ORDER BY, MySQL recherche aussi les noms de colonnes dans la liste des sélections externes.

Dans certains cas, les sous-requêtes corrélées sont optimisées. Par exemple :

val IN (SELECT key_val FROM tbl_name WHERE correlated_condition)

Sinon, elles sont inefficaces et plutôt lentes. Réécrire une requête sous forme de jointure peut améliorer les performances.

13.1.8.8. Sous-requêtes dans la clause FROM

Les sous-requêtes sont valides dans la clause FROM d'une commande SELECT. Voici une syntaxe que vous allez rencontrer :

SELECT ... FROM (<subquery>) AS <name> ...

La clause AS <name> est obligatoire, car les tables de la clause FROM doivent avoir un nom. Toutes les colonnes de la sous-requête <subquery> doivent avoir des noms distincts. Vous pourrez trouver cette syntaxe décrite ailleurs dans ce manuel, sous le nom de ``tables dérivées''.

Par exemple, supposons que vous avons cette table :

CREATE TABLE t1 (s1 INT, s2 CHAR(5), s3 FLOAT);

Comment utiliser la fonctionnalité de sous-requêtes dans la clause FROM, avec cette table d'exemple :

INSERT INTO t1 VALUES (1,'1',1.0);
INSERT INTO t1 VALUES (2,'2',2.0);
SELECT sb1,sb2,sb3
       FROM (SELECT s1 AS sb1, s2 AS sb2, s3*2 AS sb3 FROM t1) AS sb
       WHERE sb1 > 1;

Résultat : 2, '2', 4.0.

Voici un autre exemple : supposons que vous voulez connaître la moyenne de la somme pour un groupe de table. Ceci ne fonctionnera pas :

SELECT AVG(SUM(column1)) FROM t1 GROUP BY column1;

Mais cette requête-ci vous donnera les informations nécessaires :

SELECT AVG(sum_column1)
       FROM (SELECT SUM(column1) AS sum_column1
             FROM t1 GROUP BY column1) AS t1;

Notez que les colonnes sont nommées à partir de la sous-requête : (sum_column1) est reconnue dans la requête externe.

Actuellement, les sous-requêtes en clause FROM ne peuvent pas être corrélées.

13.1.8.9. Erreurs de sous-requêtes

Il y a de nouvelles erreurs qui ne s'appliquent qu'aux sous-requêtes. Cette section les rassemble, car elles vous aideront à garder en tête certains points importants.

  • ERROR 1235 (ER_NOT_SUPPORTED_YET)
    SQLSTATE = 42000
    Message = "This version of MySQL doesn't yet support
    'LIMIT & IN/ALL/ANY/SOME subquery'"
    

    Cela signifie que

    SELECT * FROM t1 WHERE s1 IN (SELECT s2 FROM t2 ORDER BY s1 LIMIT 1)
    

    ne fonctionnera pas, mais uniquement dans certaines versions d'origines, comme MySQL 4.1.1.

  • ERROR 1240 (ER_CARDINALITY_COL)
    SQLSTATE = 21000
    Message = "Operand should contain 1 column(s)"
    

    Cette erreur va survient dans des cas comme celui-ci :

    SELECT (SELECT column1, column2 FROM t2) FROM t1;
    

    Il est valide d'utiliser une sous-requête qui utilise plusieurs colonnes, dans le cadre d'une comparaison. See Section 13.1.8.5, « Sous-requêtes de ligne ». Mais dans d'autres contextes, la sous-requête doit être un opérande scalaire.

  • ERROR 1241 (ER_SUBSELECT_NO_1_ROW)
    SQLSTATE = 21000
    Message = "Subquery returns more than 1 row"
    

    Cette erreur survient dans des cas comme celui-ci :

    SELECT * FROM t1 WHERE column1 = (SELECT column1 FROM t2);
    

    mais uniquement lorsque plus d'une ligne sont extraites de t2. Cela signifie que cette erreur peut survenir dans du code qui fonctionne depuis longtemps : quelqu'un vient de modifier le nombre de ligne que la requête retourne. N'oubliez pas que si votre but est de trouver un nombre arbitraire de lignes, et non pas juste une seule, la commande correcte aurait été :

    SELECT * FROM t1 WHERE column1 = ANY (SELECT column1 FROM t2);
    
  • Error 1093 (ER_UPDATE_TABLE_USED)
    SQLSTATE = HY000
    Message = "You can't specify target table 'x' for update in FROM clause"
    

    Cette erreur survient dans des cas comme celui-ci :

    UPDATE t1 SET column2 = (SELECT MAX(column1) FROM t1);
    

Il est valide d'utiliser une sous-requête lors d'une affectation dans une commande UPDATE, car les sous-requêtes sont valides avec les commandes UPDATE et DELETE, tout comme dans les commandes SELECT. Cependant, vous ne pouvez pas les utiliser sur la même table, qui est ici t1, car cette table est alors la cible de la clause FROM et de la commande UPDATE.

Généralement, l'échec d'un sous-requête entraîne l'échec de toute la commande.

13.1.8.10. Optimisation des sous-requêtes

Le développement des sous-requêtes se poursuit, et aucun des conseils d'optimisation ne sera valable longtemps. Voici quelques astuces que vous voulez prendre en compte :

  • Utiliser une clause de sous-requête pour affecter le nombre ou l'ordre des lignes dans une sous-requête, par exemple :

    SELECT * FROM t1 WHERE t1.column1 IN
      (SELECT column1 FROM t2 ORDER BY column1);
    SELECT * FROM t1 WHERE t1.column1 IN
      (SELECT DISTINCT column1 FROM t2);
    SELECT * FROM t1 WHERE EXISTS
      (SELECT * FROM t2 LIMIT 1);
    
  • Remplacer une jointure par une sous-requête. Par exemple :

    SELECT DISTINCT column1 FROM t1 WHERE t1.column1 IN (
      SELECT column1 FROM t2);
    

    au lieu de

    SELECT DISTINCT t1.column1 FROM t1, t2
      WHERE t1.column1 = t2.column1;
    
  • Déplacer une clause FROM externe dans une sous-requête, comme ceci :

    SELECT * FROM t1
      WHERE s1 IN (SELECT s1 FROM t1 UNION ALL SELECT s1 FROM t2);
    

    au lieu de

    SELECT * FROM t1
      WHERE s1 IN (SELECT s1 FROM t1) OR s1 IN (SELECT s1 FROM t2);
    

    Un autre exemple :

    SELECT (SELECT column1 + 5 FROM t1) FROM t2;
    

    au lieu de

    SELECT (SELECT column1 FROM t1) + 5 FROM t2;
    
  • Utiliser une sous-requête de ligne plutôt qu'une corrélation. Par exemple :

    SELECT * FROM t1
      WHERE (column1,column2) IN (SELECT column1,column2 FROM t2);
    

    au lieu de

    SELECT * FROM t1
      WHERE EXISTS (SELECT * FROM t2 WHERE t2.column1=t1.column1
      AND t2.column2=t1.column2);
    
  • Utiliser NOT (a = ANY (...)) au lieu de a <> ALL (...).

  • Utiliser x = ANY (table containing {1,2}) plutôt que x=1 OR x=2.

  • Utiliser = ANY de préférence à EXISTS

Le truc ci-dessus peut accélérer certains programmes, et en ralentir d'autres. En utilisant la fonction utilitaire BENCHMARK(), vous pouvez obtenir une idée de votre cas. Ne vous attardez pas trop à transformer vos jointures, sauf si la compatibilité avec les anciennes versions est importante pour vous.

Quelques optimisation que MySQL va prendre en charge lui-même :

  • MySQL va exécuter les sous-requêtes non corrélées une seule fois (utilisez la commande EXPLAIN pour vous assurer que les requêtes ne sont pas correllées).

  • MySQL va transformer les sous-requêtes IN/ALL/ANY/SOME pour essayer de profiter de la possibilité que les colonnes sélectionnées dans la sous-requêtes sont indexées.

  • MySQL remplacera les sous-requêtes de la forme

    ... IN (SELECT indexed_column FROM single_table ...)
    

    par une recherche dans un index, que EXPLAIN décrira comme une jointure de type spécial.

  • MySQL va améliorer les expressions de la forme

    valeur {ALL|ANY|SOME} {> | < | >= | <=} (sous-requête non-correllée)
    

    avec une expression impliquant MIN ou MAX (à moins d'une valeur NULL ou d'ensembles SET vides). Par exemple,

    WHERE 5 > ALL (SELECT x FROM t)
    

    revient à

    WHERE 5 > (SELECT MAX(x) FROM t)
    

Il y a un chapitre intitulé ``Comment MySQL adapte les sous-requêtes'' dans les manuels internes de MySQL, que vous pouvez trouver en téléchargeant les sources de MySQL : il est dans un fichier appelé internals.texi, dans le dossier Docs.

13.1.8.11. Se passer des sous-requêtes avec les premières versions de MySQL

Jusqu'à la version 4.1, seules les requêtes imbriquées de la forme INSERT ... SELECT ... et REPLACE ... SELECT ... étaient supportées.

La clause IN() peut être utilisée dans certains contextes, pour tester la présence de valeur dans un ensemble de données.

Il est souvent possible de réécrire une requête sans sous-requête :

SELECT * FROM t1 WHERE id IN (SELECT id FROM t2);

Cela peut se réécrire :

SELECT t1.* FROM t1,t2 WHERE t1.id=t2.id;

Les requêtes :

SELECT * FROM t1 WHERE id NOT IN (SELECT id FROM t2);
SELECT * FROM t1 WHERE NOT EXISTS (SELECT id FROM t2 WHERE t1.id=t2.id);

peuvent être réécrites :

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

Une clause LEFT [OUTER] JOIN peut être plus rapide qu'une sous-requête équivalent, car le serveur va pouvoir l'optimiser bien mieux : c'est un fait qui n'est pas spécifique à MySQL. Avant SQL-92, les jointures externes n'existaient pas, et les sous-requêtes étaient la seule méthode pour résoudre certains problèmes. Aujourd'hui, le serveur MySQL et d'autres bases de données modernes offrent toute une gamme de jointures externes.

Pour les sous-requêtes plus complexes, vous pouvez simplement créer des tables temporaires pour contenir les résultats intermédiaires. Dans certains cas, cela ne sera pas possible. C'est notamment le cas des commandes de type DELETE, pour lesquelles le standard SQL ne supporte pas les jointures, sauf pour les sous-requêtes. Dans ces situations, trois solutions s'offrent à vous :

  • Passez en MySQL version 4.1.

  • Utilisez un langage de programmation procédural, comme Perl ou PHP, pour envoyer la requête SELECT, lire les clés primaires à effacer, et utiliser ces valeurs pour soumettre des requêtes de type DELETE (DELETE FROM ... WHERE ... IN (key1, key2, ...)).

  • La troisième option est d'utiliser le client interactif de mysql pour construire et exécuter une liste de commande DELETE automatiquement, avec la fonction CONCAT() au lieu de l'opérateur ||. 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 de script, et rediriger son résultat vers le client en ligne de commande mysql, pour que ce dernier lance une seconde instance de l'interprêteur :

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

MySQL 4.0 supporte les commandes DELETE multi-tables qui peuvent être utilisées pour effacer des lignes dans une table en fonction d'informations qui sont dans une autre table, ou même effacer des lignes simultanément dans plusieurs tables. Les commandes UPDATE multi-tables sont aussi supportés depuis la version 4.0.

13.1.9. Syntaxe de TRUNCATE

TRUNCATE TABLE nom_de_table

Dans la version 3.23, TRUNCATE TABLE est équivalent à COMMIT ; DELETE FROM nom_de_table. See Section 13.1.1, « Syntaxe de DELETE ».

TRUNCATE TABLE diffère de DELETE FROM ... des fa¸ons suivantes :

  • Implémentée comme une destruction/création de table, ce qui accélère la suppression des enregistrements.

  • Ne respecte pas les transactions. Vous aurez des erreurs si vous avez une transaction active ou une table protégée en écriture.

  • Ne retourne pas le nombre de lignes effacées.

  • Tant que le fichier de définition nom_de_table.frm est valide, la table peut être recréée, me si les données ou un index a été corrompu.

  • Le gestionnaire de table ne se souvient pas de la dernière valeur AUTO_INCREMENT utilisée, mais peut commencer à compter depuis le début. C'est vrai pour les tables MyISAM, ISAM et BDB.

TRUNCATE est une extension Oracle SQL. Cette commande a été ajoutée en MySQL 3.23.28, même si dans les versions 3.23.28 à 3.23.32, le mot clé TABLE devait être omis.

13.1.10. Syntaxe de UPDATE

UPDATE [LOW_PRIORITY] [IGNORE] tbl_name
    SET col_name1=expr1 [, col_name2=expr2 ...]
    [WHERE where_definition]
    [ORDER BY ...]
    [LIMIT row_count]

Syntaxe multi-tables :

UPDATE [LOW_PRIORITY] [IGNORE] tbl_name [, tbl_name ...]
    SET col_name1=expr1 [, col_name2=expr2 ...]
    [WHERE where_definition]

UPDATE met à jour des enregistrements dans une tables avec de nouvelles valeurs. La clause SET indique les colonnes à modifier et les valeurs à leur donner. La clause WHERE, si fournie, spécifie les enregistrements à mettre à jour. Sinon, tous les enregistrements sont mis à jour. Si la clause ORDER BY est fournie, les enregistrements seront mis à jour dans l'ordre spécifié.

La commande UPDATE accepte les options suivantes :

  • Si vous spécifiez le mot clef LOW_PRIORITY, l'exécution de l'UPDATE sera repoussé jusqu'à ce que aucun client ne lise plus de la table.

  • Si vous spécifiez le mot clef IGNORE, la mise à jour ne s'interrompra pas même si on rencontre des problèmes d'unicité de clefs durant l'opération. Les enregistrements posant problèmes ne seront pas mis à jour.

Si vous accédez à une colonne d'une table tbl_name dans une expression, UPDATE utilisera la valeur courante de la colonne. Par exemple, la requête suivante ajoute une année à l'âge actuel de tout le monde :

mysql> UPDATE persondata SET age=age+1;

Les requêtes UPDATE sont évaluées de gauche à droite. Par exemple, la requête suivante double la valeur de la colonnes âge, puis l'incrémente :

mysql> UPDATE persondata SET age=age*2, age=age+1;

Si vous changez la valeur d'une colonne en lui spécifiant sa valeur actuelle, MySQL s'en aper¸oit et ne fait pas la mise à jour.

UPDATE retourne le nombre d'enregistrements ayant changé. Depuis la version 3.22 de MySQL, la fonction mysql_info() de l'API C retourne le nombre de colonnes qui correspondaient, le nombre de colonnes mises à jour et le nombre d'erreurs générées pendant l'UPDATE.

Dans la version 3.23 de MySQL, vous pouvez utilisez le code LIMIT # pour vous assurer que seul un nombre d'enregistrements bien précis est changé.

  • Avant MySQL 4.0.13, LIMIT est une restrictions sur le nombre de lignes affectées. Cette clause stoppe dès que row_count ont été trouvées par la clause WHERE.

  • Depuis la version 4.0.13, LIMIT est une restriction sur le nombre de lignes trouvées. La commande s'arrête une fois que row_count lignes ont été trouvées par la clause WHERE, qu'elles ait été changées ou pas.

Ai une clause ORDER BY est utilisée (disponible depuis MySQL version 4.0.0), les lignes seront modifiées selon cet ordre. Ce n'est vraiment utile qu'en conjonction avec LIMIT.

Depuis MySQL version 4.0.4, vous pouvez aussi faire des opérations de UPDATE qui couvrent plusieurs tables :

UPDATE items,month SET items.price=month.price
WHERE items.id=month.id;

L'exemple ci-dessus montre une jointure interne, en utilisant la virgule comme séparateur, mais une commande UPDATE multi-table peut utiliser n'importe quel type de jointure autorisée dans une commande SELECT, tel qu'un LEFT JOIN.

Note : vous ne pouvez pas utiliser ORDER BY ou LIMIT avec les UPDATE multi-table.

13.2. Définition de données : CREATE, DROP, ALTER

13.2.1. Syntaxe de ALTER DATABASE

ALTER DATABASE db_name
    alter_specification [, alter_specification] ...

alter_specification:
    [DEFAULT] CHARACTER SET charset_name
  | [DEFAULT] COLLATE collation_name

ALTER DATABASE vous permet de modifier les caractéristiques générales d'une base de données. Ces caractéristiques sont stockées dans le fichier db.opt du dossier de base. Pour utiliser ALTER DATABASE, vous avez besoin des droits de ALTER sur la base.

La clause CHARACTER SET modifie le jeu de caractères par défaut de la base. La clause COLLATE modifie la collation par défaut de la base. Les noms de jeu de caractères et de collation sont présentés dans la section Chapitre 10, Jeux de caractères et Unicode.

ALTER DATABASE a été ajoutée MySQL 4.1.1.

13.2.2. Syntaxe de ALTER TABLE

ALTER [IGNORE] TABLE tbl_name
    alter_specification [, alter_specification] ...

alter_specification:
    ADD [COLUMN] column_definition [FIRST | AFTER col_name ]
  | ADD [COLUMN] (column_definition,...)
  | ADD INDEX [index_name] [index_type] (index_col_name,...)
  | ADD [CONSTRAINT [symbol]]
        PRIMARY KEY [index_type] (index_col_name,...)
  | ADD [CONSTRAINT [symbol]]
        UNIQUE [index_name] [index_type] (index_col_name,...)
  | ADD [FULLTEXT|SPATIAL] [index_name] (index_col_name,...)
  | ADD [CONSTRAINT [symbol]]
        FOREIGN KEY [index_name] (index_col_name,...)
        [reference_definition]
  | ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}
  | CHANGE [COLUMN] old_col_name column_definition
        [FIRST|AFTER col_name]
  | MODIFY [COLUMN] column_definition [FIRST | AFTER col_name]
  | DROP [COLUMN] col_name
  | DROP PRIMARY KEY
  | DROP INDEX index_name
  | DROP FOREIGN KEY fk_symbol
  | DISABLE KEYS
  | ENABLE KEYS
  | RENAME [TO] new_tbl_name
  | ORDER BY col_name
  | CONVERT TO CHARACTER SET charset_name [COLLATE collation_name]
  | [DEFAULT] CHARACTER SET charset_name [COLLATE collation_name]
  | DISCARD TABLESPACE
  | IMPORT TABLESPACE
  | table_options

ALTER TABLE vous permet de changer la structure d'une table existante. Par exemple, vous pouvez ajouter ou supprimer des colonnes, des index, changer le type des colonnes existantes, renommer ces colonnes, ou la table elle-même. Vous pouvez de même changer le commentaire sur la table, ou le type de celle-ci.

La syntaxe de nombreuses altérations est similaires aux clauses de la commande CREATE TABLE. See Section 13.2.5, « Syntaxe de CREATE TABLE ».

Si vous utilisez ALTER TABLE pour modifier les spécifications d'une colonne mais que DESCRIBE nom_de_table vous indique que cette colonne n'a pas été modifiée, il est possible que MySQL ait ignoré vos modifications pour une des raisons décrite dans Section 13.2.5.1, « Modification automatique du type de colonnes ». Par exemple, si vous essayez de changer une colonne de type VARCHAR en CHAR, MySQL continuera d'utiliser VARCHAR si la table contient d'autres colonnes de taille variable.

ALTER TABLE effectue une copie temporaire de la table originale. Les modifications sont faites sur cette copie, puis l'original est effacée, et enfin la copie est renommée pour remplacer l'originale. Cette méthode permet de rediriger toutes les commandes automatiquement vers la nouvelle table sans pertes. Durant l'exécution de ALTER TABLE, la table originale est lisible par d'autres clients. Les modifications et insertions sont reportées jusqu'à ce que la nouvelle table soit prête.

Notez que si vous utilisez une autre option que RENAME avec ALTER TABLE, MySQL créera toujours une table temporaire, même si les données n'ont pas besoin d'être copiées (comme quand vous changez le nom d'une colonne). Nous avons prévu de corriger cela dans les versions suivantes, mais comme la commande ALTER TABLE n'est pas utilisée très souvent, cette correction ne fait pas partie de nos priorités. Pour les tables MyISAM, vous pouvez accélérer la réindexation (qui est la partie la plus lente de la modification d'une table) en donnant à la variable système myisam_sort_buffer_size une valeur plus grande.

  • Pour utiliser ALTER TABLE, vous devez avoir les droits ALTER, INSERT, et CREATE sur la table.

  • IGNORE est une extension MySQL pour ANSI SQL92. Cette option contrôle la fa¸on dont ALTER TABLE fonctionne s'il y a des duplications sur une clef unique de la nouvelle table. Si IGNORE n'est pas spécifiée, la copie est annulée et la table originale est restaurée. Si IGNORE est spécifiée, les lignes contenant les éléments doublons de la table seront effacées, hormis la première, qui sera conservée.

  • Vous pouvez effectuer plusieurs opérations de ADD, ALTER, DROP, et CHANGE dans une même commande ALTER TABLE. C'est une extension de MySQL à la norme ANSI SQL92, qui n'autorise qu'une seule modification par commande ALTER TABLE.

  • CHANGE nom_colonne, DROP nom_colonne, et DROP INDEX sont des extensions de MySQL à la norme ANSI SQL92.

  • MODIFY est une extension Oracle à ALTER TABLE.

  • Le mot optionnel COLUMN est purement de la fioriture et peut être ignoré.

  • Si vous utilisez ALTER TABLE nom_de_table RENAME TO nouveau_nom sans autre option, MySQL va simplement renommer les fichiers qui correspondent à la table nom_de_table. Il n'y a pas de création de fichier temporaire. See Section 13.2.9, « Syntaxe de RENAME TABLE ».

  • La définition create_definition utilise la même syntaxe pour les clauses ADD et CHANGE que dans CREATE TABLE. Notez que cette syntaxe inclut le nom de la colonne, et pas seulement son type See Section 13.2.5, « Syntaxe de CREATE TABLE ».

  • Vous pouvez renommer une colonne avec la syntaxe CHANGE ancien_nom_de_colonne create_definition. Pour cela, indiquez l'ancien nom de la colonne, puis le nouveau nom et son type courant. Par exemple, pour renommer une colonne de type INTEGER, de a en b, vous pouvez faire ceci :

    mysql> ALTER TABLE t1 CHANGE a b INTEGER;
    

    Si vous ne voulez changer que le type de la colonne, avec la clause CHANGE vous devrez redonner le nom de la colonne. Par exemple :

    mysql> ALTER TABLE t1 CHANGE b b BIGINT NOT NULL;
    

    Cependant, à partir de la version 3.22.16a de MySQL, vous pouvez aussi utiliser la clause MODIFY pour changer le type d'une colonne sans la renommer :

    mysql> ALTER TABLE t1 MODIFY b BIGINT NOT NULL;
    
  • Si vous utilisez les clauses CHANGE ou MODIFY pour réduire la taille d'une colonne qui comportait un index sur une partie de la colonne (par exemple, si vous aviez un index sur 10 caractères d'une colonne de type VARCHAR), vous ne pouvez pas rendre la colonne plus petite que le nombre de caractères indexés.

  • Quand vous changez le type d'une colonne avec CHANGE ou MODIFY, MySQL essaye de convertir les données au niveau type dans la mesure du possible.

  • A partir de la version 3.22 de MySQL, vous pouvez utiliser FIRST ou ADD ... AFTER nom_colonne pour ajouter la colonne à un endroit spécifique dans la table. Par défaut, la colonne est ajoutée à la fin. A partir de la version 4.0.1, vous pouvez aussi utiliser les mots clés FIRST et AFTER avec CHANGE ou MODIFY.

  • ALTER COLUMN spécifie une nouvelle valeur par défaut pour une colonne ou enlève l'ancienne. si l'ancienne valeur est effacée et que la colonne peut être NULL, la nouvelle valeur par défaut sera NULL. Si la colonne ne peut être NULL, MySQL assigne une valeur par défaut, comme défini dans Section 13.2.5, « Syntaxe de CREATE TABLE ».

  • DROP INDEX supprime un index. C'est une extension MySQL à la norme ANSI SQL92. See Section 13.2.7, « Syntaxe de DROP INDEX ».

  • Si des colonnes sont effacées d'une table, ces colonnes sont aussi supprimés des index dont elles font partie. Si toutes les colonnes qui forment un index sont effacées, l'index lui même est supprimé.

  • Si une table ne comporte qu'une seule colonne, La colonne ne peut être supprimée. Si vous voulez effacer la table, utilisez la commande DROP TABLE.

  • DROP PRIMARY KEY supprime la clef primaire. Si cette clef n'existe pas, cette commande effacera le premier index UNIQUE de la table. (MySQL marque la première clef UNIQUE en tant que PRIMARY KEY si aucune PRIMARY KEY n'a été spécifiée explicitement.)

    Si vous ajoutez un UNIQUE INDEX ou PRIMARY KEY à une table, c'est enregistré avant les index non-UNIQUE pour que MySQL puisse détecter les valeurs dupliquées aussi vite que possible.

  • ORDER BY vous permet de créer une nouvelle table tout en ordonnant les lignes par défaut. Notez que cet ordre ne sera pas conservé après les prochaines insertions et modifications. Dans certains cas, cela aide MySQL si les colonnes sont dans l'ordre dans lequel vous allez trier les valeurs. Cette option n'est vraiment utile que si vous savez à l'avance dans quel ordre vous effectuerez les tris : vous y gagnerez alors en performances.

  • Si vous utilisez ALTER TABLE sur une table MyISAM, tous les index non-uniques sont créés par des opérations séparées. (comme dans REPAIR). Cela devrait rendre ALTER TABLE plus rapide quand vous avez beaucoup d'index.

    Depuis la version 4.0, la fonctionnalité ci-dessus peut être activée explicitement. ALTER TABLE ... DISABLE KEYS force MySQL à ne plus mettre à jour les index non-uniques pour les tables au format MyISAM. ALTER TABLE ... ENABLE KEYS doit alors être utilisé pour recréer les index manquants. Comme MySQL le fait avec un algorithme spécial qui est plus rapide que le fait d'insérer les clefs une par une, désactiver les clefs peut vous faire gagner en performances.

  • Les clauses FOREIGN KEY et REFERENCES sont supportées par le moteur de tables InnoDB, qui implémente les clauses ADD [CONSTRAINT [symbol]] FOREIGN KEY (...) REFERENCES ... (...). See Section 15.7.4, « Contraintes de clés étrangères FOREIGN KEY ». Pour les autres moteurs de stockages, ces clauses sont lues mais ignorées. La clause CHECK est analysée mais ignorée par tous les moteurs de stockage. See Section 13.2.5, « Syntaxe de CREATE TABLE ». La raison pour accepter mais ignorer ces clauses est que cela renforce la compatibilité avec le code des autres serveurs SQL, et qu'il est possible de créer des tables avec des références. See Section 1.5.5, « Différences entre MySQL et le standard SQL-92 ».

  • Depuis MySQL 4.0.13, InnoDB supporte l'utilisation de ALTER TABLE pour effacer des clés étrangères :

    ALTER TABLE yourtablename
        DROP FOREIGN KEY fk_symbol
    

    Pour plus d'informations, voyez Section 15.7.4, « Contraintes de clés étrangères FOREIGN KEY ».

  • ALTER TABLE ignore les options de tables DATA DIRECTORY et INDEX DIRECTORY.

  • Depuis MySQL 4.1.2, si vous voulez changer dans toutes les colonnes de texte (CHAR, VARCHAR, TEXT) le jeu de caractères, vous pouvez utiliser la commande suivante :

    ALTER TABLE tbl_name CONVERT TO CHARACTER SET charset_name;
    

    C'est pratique lorsque vous passez de MySQL 4.0.x en 4.1.x. See Section 10.10, « Préparer le passage de version 4.0 en 4.1 ».

    Attention : l'opération précédente va convertir les valeurs des colonnes entre les deux jeux de caractères. Ce n'est pas ce que vous souhaitez faire si une colonne est de type latin1 mais que les valeurs sont en fait dans un autre jeu de caractères (comme utf8). Dans ce cas, vous devez faire ceci avec une telle colonne :

    ALTER TABLE t1 CHANGE c1 c1 BLOB;
    ALTER TABLE t1 CHANGE c1 c1 TEXT CHARACTER SET utf8;
    

    La raison est que dans ce cas, il n'y aura pas de conversion lorsque vous passer en type BLOB.

    Pour ne changer que le type de caractères par défaut, utilisez cette commande :

    ALTER TABLE tbl_name DEFAULT CHARACTER SET charset_name;
    

    Le mot DEFAULT est optionnel. Le jeu de caractères par défaut est utilisé si vous ne spécifiez pas le jeu de caractères de la colonne explicitement, lorsque vous ajoutez une nouvelle colonne : par exemple, avec ALTER TABLE ... ADD column.

    Attention : depuis MySQL 4.1.2 et plus récent, ALTER TABLE ... DEFAULT CHARACTER SET et ALTER TABLE ... CHARACTER SET sont équivalent et ne changent que le jeu de caractères par défaut. Dans les versions antérieures à MySQL 4.1.2, ALTER TABLE ... DEFAULT CHARACTER SET changeait le jeu de caractères par défaut, mais ALTER TABLE ... CHARACTER SET (sans DEFAULT) changeait le jeu de caractères par défaut, et convertissaient les colonnes dans le nouveau jeu.

  • Pour une table InnoDB qui a été créée avec son propre espace de tables dans un fichier .ibd, ce fichier peut être supprimé et importé. Pour supprimer le fichier .ibd, utilisez la commande suivante :

    ALTER TABLE tbl_name DISCARD TABLESPACE;
    

    Elle efface le fichier .ibd courant, alors assurez vous que vous avez une copie de sauvegarde. Si vous tentez d'accéder à un espace de table sans ce fichier, vous obtiendrez une erreur.

    Pour importer un fichier de sauvegarde .ibd dans la table, copiez le nouveau fichier dans le dossier de la base, et utilisez cette commande :

    ALTER TABLE tbl_name IMPORT TABLESPACE;
    

    See Section 15.7.6, « Espaces de tables multiples : chaque table InnoDB a son fichier .ibd ».

  • Avec la fonction mysql_info() de l'API C, vous pouvez savoir combien d'enregistrements ont été copiés, et (quand IGNORE est spécifié) combien d'enregistrements ont été effacés à cause de la clef unique. See Section 24.2.3.31, « mysql_info() ».

Voilà un exemple qui montre quelques utilisations de ALTER TABLE. On commence par une table t1 créée comme suit :

mysql> CREATE TABLE t1 (a INTEGER,b CHAR(10));

Pour renommer la table de t1 à t2 :

mysql> ALTER TABLE t1 RENAME t2;

Pour changer une colonne a de INTEGER en TINYINT NOT NULL (en laissant le même nom), et pour changer une colonne b de CHAR(10) à CHAR(20) et la renommant de b en c :

mysql> ALTER TABLE t2 MODIFY a TINYINT NOT NULL, CHANGE b c CHAR(20);

Pour ajouter une nouvelle colonne TIMESTAMP nommée d :

mysql> ALTER TABLE t2 ADD d TIMESTAMP;

Pour ajouter un index sur une colonne d, et rendre la colonne a la clef primaire :

mysql> ALTER TABLE t2 ADD INDEX (d), ADD PRIMARY KEY (a);

Pour effacer la colonne c :

mysql> ALTER TABLE t2 DROP COLUMN c;

Pour ajouter une nouvelle colonne AUTO_INCREMENT nommée c :

mysql> ALTER TABLE t2 ADD c INT UNSIGNED NOT NULL AUTO_INCREMENT,
           ADD INDEX (c);

Notez que nous avons indexé c, car les colonnes AUTO_INCREMENT doivent être indexées, et que nous définissons aussi c en tant que NOT NULL, car les colonnes indexées ne peuvent être NULL.

Quand vous ajoutez une colonne AUTO_INCREMENT, les valeurs de la colonne sont remplies automatiquement pour vous. Vous pouvez choisir la valeur de départ pour l'indexation en utilisant SET INSERT_ID=# avant ALTER TABLE ou en utilisant l'option AUTO_INCREMENT = # de la table. See Section 13.5.2.8, « Syntaxe de SET ».

Avec les tables de type MyISAM, si vous ne changez pas la colonne AUTO_INCREMENT, l'indice d'auto-incrémentation ne sera pas affecté. Si vous effacez une colonne AUTO_INCREMENT puis en ajoutez une autre, l'indexation recommencera à partir de 1.

See Section A.7.1, « Problèmes avec ALTER TABLE. ».

13.2.3. Syntaxe de CREATE DATABASE

CREATE DATABASE [IF NOT EXISTS] db_name
    [create_specification [, create_specification] ...]

create_specification:
    [DEFAULT] CHARACTER SET charset_name
  | [DEFAULT] COLLATE collation_name

CREATE DATABASE crée une base de données avec le nom donné.

Les règles de nommage des bases de donnée sont présentées dans la section Section 9.2, « Noms de bases, tables, index, colonnes et alias ». Une erreur survient si une base de données de même nom existe déjà, si vous ne spécifiez pas l'option IF NOT EXISTS.

Depuis MySQL 4.1.1, les options create_specification peuvent être données pour spécifier des caractéristiques de la base. Les caractéristiques de la base sont stockées dans le fichier db.opt dans le dossier de la base. La clause CHARACTER SET spécifie le jeu de caractères par défaut pour les tables de cette base. La clause COLLATE spécifie la collation par défaut de la base de données. Les jeux de caractères et les collations sont présentées dans la section Chapitre 10, Jeux de caractères et Unicode.

Les bases de données MySQL sont implémentées comme des répertoires contenant des fichiers qui correspondent aux tables dans les bases de données. Puisqu'il n'y a pas de tables dans une base de données lors de sa création, la requête CREATE DATABASE créera seulement le dossier dans le répertoire de données de MySQL (et le fichier db.opt, depuis MySQL 4.1.1).

Vous pouvez aussi créer des bases de données avec mysqladmin. See Section 8.4, « mysqladmin, administration d'un serveur MySQL ».

13.2.4. Syntaxe de CREATE INDEX

CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name [index_type]
    ON tbl_name (index_col_name,...)

index_col_name:
    col_name [(length)] [ASC | DESC]

La requête CREATE INDEX n'effectue aucune action sur les versions de MySQL antérieures à la version 3.22. Dans les versions 3.22 et supérieures, CREATE INDEX est équivalent à une requête ALTER TABLE pour créer des index. See Section 13.2.2, « Syntaxe de ALTER TABLE ».

Normalement, tous les index sont créés en même temps que la table elle même avec CREATE TABLE. See Section 13.2.5, « Syntaxe de CREATE TABLE ». CREATE INDEX permet d'ajouter des index à une table existante.

Une liste de colonnes de la forme (col1,col2,...) crée un index multi-colonnes. Les valeurs de l'index sont créées en concaténant la valeur deux colonnes données.

Pour les colonnes CHAR et VARCHAR, les index peut être créés sur uniquement une partie de la colonne, avec la syntaxe col_name(length). Pour les colonnes BLOB et TEXT la longueur d'index est obligatoire. La requête suivante crée un index en utilisant les 10 premiers caractères de la colonne name :

mysql> CREATE INDEX part_of_name ON customer (name(10));

Comme la plupart des noms ont en général des différences dans les 10 premiers caractères, l'index ne devrait pas être plus lent qu'un index créé à partir de la colonne name en entier. Ainsi, en n'utilisant qu'une partie de la colonne pour les index, on peut réduire la taille du fichier d'index, ce qui peut permettre d'économiser beaucoup d'espace disque, et peut aussi accélérer les opérations INSERT!

Il est important de savoir qu'on peut indexer une colonne qui peut avoir la valeur NULL ou une colonne BLOB/TEXT que si on utilise une version 3.23.2 ou supérieure de MySQL et en utilisant le type MyISAM.

Pour plus d'informations à propos de l'utilisation des index dans MySQL, voir Section 7.4.5, « Comment MySQL utilise les index ».

Les index FULLTEXT ne peuvent indexer que des colonnes VARCHAR ou TEXT, et seulement dans les tables MyISAM. Les index FULLTEXT sont disponibles dans les versions 3.23.23 et supérieures de MySQL. Section 12.6, « Recherche en texte intégral (Full-text) dans MySQL ».

Les index SPATIAL peuvent indexer les colonnes spatiales, et uniquement avec les tables MyISAM. Les index SPATIAL sont disponibles en MySQL 4.1 et plus récent. Les colonnes spatiales sont présentées dans section Chapitre 18, Données spatiales avec MySQL.

13.2.5. Syntaxe de CREATE TABLE

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    [(create_definition,...)]
    [table_options] [select_statement]

ou :

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    [(] LIKE old_tbl_name [)];

create_definition:
    column_definition
  | [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (index_col_name,...)
  | KEY [index_name] [index_type] (index_col_name,...)
  | INDEX [index_name] [index_type] (index_col_name,...)
  | [CONSTRAINT [symbol]] UNIQUE [INDEX]
        [index_name] [index_type] (index_col_name,...)
  | [FULLTEXT|SPATIAL] [INDEX] [index_name] (index_col_name,...)
  | [CONSTRAINT [symbol]] FOREIGN KEY
        [index_name] (index_col_name,...) [reference_definition]
  | CHECK (expr)

column_definition:
    col_name type [NOT NULL | NULL] [DEFAULT default_value]
        [AUTO_INCREMENT] [[PRIMARY] KEY] [COMMENT 'string']
        [reference_definition]

type:
    TINYINT[(length)] [UNSIGNED] [ZEROFILL]
  | SMALLINT[(length)] [UNSIGNED] [ZEROFILL]
  | MEDIUMINT[(length)] [UNSIGNED] [ZEROFILL]
  | INT[(length)] [UNSIGNED] [ZEROFILL]
  | INTEGER[(length)] [UNSIGNED] [ZEROFILL]
  | BIGINT[(length)] [UNSIGNED] [ZEROFILL]
  | REAL[(length,decimals)] [UNSIGNED] [ZEROFILL]
  | DOUBLE[(length,decimals)] [UNSIGNED] [ZEROFILL]
  | FLOAT[(length,decimals)] [UNSIGNED] [ZEROFILL]
  | DECIMAL(length,decimals) [UNSIGNED] [ZEROFILL]
  | NUMERIC(length,decimals) [UNSIGNED] [ZEROFILL]
  | DATE
  | TIME
  | TIMESTAMP
  | DATETIME
  | CHAR(length) [BINARY | ASCII | UNICODE]
  | VARCHAR(length) [BINARY]
  | TINYBLOB
  | BLOB
  | MEDIUMBLOB
  | LONGBLOB
  | TINYTEXT
  | TEXT
  | MEDIUMTEXT
  | LONGTEXT
  | ENUM(value1,value2,value3,...)
  | SET(value1,value2,value3,...)
  | spatial_type

index_col_name:
    col_name [(length)] [ASC | DESC]

reference_definition:
    REFERENCES tbl_name [(index_col_name,...)]
               [MATCH FULL | MATCH PARTIAL]
               [ON DELETE reference_option]
               [ON UPDATE reference_option]

reference_option:
    RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT

table_options: table_option [table_option] ...

table_option:
    {ENGINE|TYPE} = {BDB|HEAP|ISAM|InnoDB|MERGE|MRG_MYISAM|MYISAM}
  | AUTO_INCREMENT = value
  | AVG_ROW_LENGTH = value
  | CHECKSUM = {0 | 1}
  | COMMENT = 'string'
  | MAX_ROWS = value
  | MIN_ROWS = value
  | PACK_KEYS = {0 | 1 | DEFAULT}
  | PASSWORD = 'string'
  | DELAY_KEY_WRITE = {0 | 1}
  | ROW_FORMAT = { DEFAULT | DYNAMIC | FIXED | COMPRESSED }
  | RAID_TYPE = { 1 | STRIPED | RAID0 }
        RAID_CHUNKS = value
        RAID_CHUNKSIZE = value
  | UNION = (tbl_name[,tbl_name]...)
  | INSERT_METHOD = { NO | FIRST | LAST }
  | DATA DIRECTORY = 'absolute path to directory'
  | INDEX DIRECTORY = 'absolute path to directory'
  | [DEFAULT] CHARACTER SET charset_name [COLLATE collation_name]

select_statement:
    [IGNORE | REPLACE] [AS] SELECT ...   (Some legal select statement)

CREATE TABLE Crée une table avec le nom donné, dans la base de données courante. Vous avez besoin des droits de CREATE pour créer une table.

Les règles de nommage des tables sont disponibles dans Section 9.2, « Noms de bases, tables, index, colonnes et alias ». Par défaut, une table est créée dans la base de données courante. Une erreur est affichée s'il n'y a pas de base courante, si la base de données n'existe pas ou si la table existe déjà.

En MySQL 3.22 et plus récent, le nom de la table peut être spécifié avec la syntaxe db_name.tbl_name, pour créer une table dans une base spécifique. Cela fonctionne même s'il n'y a pas de base courante. Si vous utilisez les identifiants protégez, et mettez le nom de la base et de lui de la table entre guillemets, séparément. Par exemple, `madb`.`matbl` est valide, mais `madb.matbl` ne l'est pas.

Depuis la version 3.22 de MySQL, vous pouvez utiliser le mot réservé TEMPORARY lorsque vous créez une table. Une table temporaire sera immédiatement effacée dès que la connexion se termine. Cela signifie que vous pouvez utiliser le même nom de table temporaire depuis deux connexions différentes sans risque de conflit entre les connexions. Vous pouvez aussi utiliser une table temporaire qui a le même nom qu'une table existante (la table existante est alors cachée tant que dure la table temporaire). En MySQL version 4.0.2 ou plus récent, vous avez juste à avoir le privilège CREATE TEMPORARY TABLES pour créer des tables temporaires.

Depuis la version 3.23 de MySQL, vous pouvez utiliser le mot réservé IF NOT EXISTS, de fa¸on à ce qu'aucune erreur ne soit affiché si la table que vous essayez de créer existe déjà. Notez qu'il n'y a pas de comparaisons entre les structures de table lors du test d'existence.

MySQL représente chaque table par un fichier de définition de table .frm, placé dans le dossier de la base de données. Le moteur de la table peut créer d'autres fichiers complémentaires. Dans le cas des tables MyISAM, le moteur de stockage utilise trois fichiers, avec le nom nom_de_table :

FichierRôle
nom_de_table.frmFichier de définition de la table
nom_de_table.MYDFichier de données
nom_de_table.MYIFichier d'index

Les fichiers créés par les moteurs de stockages pour représenter les tables sont décrits dans la section Chapitre 14, Moteurs de tables MySQL et types de table.

Pour des informations générales sur les propriétés des différentes colonnes, voyez Chapitre 11, Types de colonnes. Pour des informations sur les types de données spatiaux, voyez Chapitre 18, Données spatiales avec MySQL.

  • Si ni NULL, ni NOT NULL n'est spécifié, une colonne utilisera par défaut l'attribut NULL (elle acceptera les valeurs NULL).

  • Une colonne de nombre entier peut se voir attribuer l'attribut AUTO_INCREMENT. Lorsque vous insérez la valeur NULL (recommandée) ou 0 dans une colonne AUTO_INCREMENT, la colonne prendra automatiquement la valeur de value+1, où value est la plus grande valeur positive courante dans cette colonne. La série des valeurs AUTO_INCREMENT commence à 1. See Section 24.2.3.33, « mysql_insert_id() ».

    Depuis MySQL 4.1.1, en spécifiant l'option NO_AUTO_VALUE_ON_ZERO pour le mode --sql-mode ou la variable serveur sql_mode permet de stocker la valeur 0 dans les colonnes de type AUTO_INCREMENT, au lieu de voir 0 prendre le prochain numéro de séquence. See Section 5.2.1, « Options de ligne de commande de mysqld ».

    Note : Il ne peut y avoir qu'une seule colonne de type AUTO_INCREMENT dans une table, et elle doit être indexée. MySQL version 3.23 ne fonctionnera correctement que si cette colonne n'accueille que des valeurs positives. Insérer un nombre négatif sera considéré comme insérer un nombre de très grande taille, mais positif. Ceci est fait pour éviter les problèmes de précision lorsque les nombres passe de positif à négatif lorsqu'ils atteignent leur valeur maximale positive. C'est aussi pour éviter qu'une colonne de type AUTO_INCREMENT ne contienne de valeur 0.

    Si vous effacez la ligne contenant la valeur maximale dans la colonne AUTO_INCREMENT, cette valeur sera réutilisée dans les tables de type ISAM mais pas dans les tables de type MyISAM. Si vous effacez toutes les lignes dans la table avec la commande DELETE FROM nom_de_table (sans la clause WHERE) en mode AUTOCOMMIT, la série des valeurs AUTO_INCREMENT recommencera à 0.

    Avec les tables MyISAM et BDB, vous pouvez spécifier une colonne secondaire d'AUTO_INCREMENT dans une clef multi-colonnes. See Section 3.6.9, « Utiliser AUTO_INCREMENT ».

    Pour rendre MySQL avec certaines applications ODBC, vous pouvez retrouver la valeur de la dernière valeur automatiquement générée avec la requête suivante :

    SELECT * FROM nom_de_table WHERE auto_col IS NULL
    
  • Depuis MySQL 4.1, la définition des colonnes peut inclure un attribut CHARACTER SET pour spécifier le jeu de caractères, et éventuellement la collation de la colonne. Pour des détails, voyez Chapitre 10, Jeux de caractères et Unicode.

    CREATE TABLE t (c CHAR(20) CHARACTER SET utf8 COLLATE utf8_bin);
    

    Depuis la version 4.1 aussi, MySQL interprète les spécifications de longueur de colonne en caractères. Les anciennes versions l'interprète comme des octets.

  • La valeur NULL est traitée différemment dans les colonnes de type TIMESTAMP. Vous ne pouvez pas stocker de valeur NULL littérale dans une colonne TIMESTAMP; insérer une valeur NULL dans une telle colonne revient à insérer la date et l'heure courante. Car les colonnes TIMESTAMP ignorent les attributs NULL et NOT NULL.

    Cela facilite grandement l'utilisation des colonnes TIMESTAMP pour les clients MySQL : le serveur indique que ces colonnes peuvent se voir assigner une valeur NULL (ce qui est vrai), même si les colonnes TIMESTAMP ne contiendront jamais de valeur NULL. Vous pouvez le constater lorsque vous utiliser la commande DESCRIBE nom_de_table pour avoir une description de votre table.

    Notez qu'affecter la valeur 0 à une colonne TIMESTAMP n'est pas la même chose que lui affecter la valeur NULL, car 0 est une valeur TIMESTAMP valide.

  • Une valeur DEFAULT doit être une constante, ¸a ne peut être une fonction ou une expression. Cela signifie notamment que vous ne pouvez pas donner une valeur par défaut à une colonne de date, le résultat de la fonction NOW() ou CURRENT_DATE.

    Si aucune valeur par défaut (attribut DEFAULT) n'est spécifiée, MySQL en assigne une automatiquement

    Si la colonne accepte les valeur NULL, la valeur par défaut sera la valeur NULL.

    Si la colonne est déclarée comme NOT NULL (non-nulle), la valeur par défaut dépendra du type de colonne :

    • Pour les types numériques sans l'attribut AUTO_INCREMENT, la valeur sera 0. Pour une colonne AUTO_INCREMENT, la valeur par défaut sera la prochaine valeur de la série.

    • Pour les types dates et heures autres que TIMESTAMP, la valeur par défaut est la date zéro appropriée. Pour les colonnes TIMESTAMP, la valeur par défaut est la date et l'heure courante. See Section 11.3, « Les types date et heure ».

    • Pour les types de chaînes autres que ENUM, la valeur par défaut est la chaîne vide. Pour ENUM, la valeur par défaut est la première valeur de l'énumération.

    Les colonnes BLOB et TEXT ne peuvent pas recevoir de valeur par défaut.

  • Un commentaire pour une colonne peut être spécifiée avec COMMENT. Le commentaire est affiché par la commande SHOW CREATE TABLE, et par SHOW FULL COLUMNS. Cette option est disponible depuis MySQL 4.1. Il était autorisé, mais ignoré dans les anciennes versions.

  • Depuis MySQL version 4.1.0, l'attribut SERIAL peut être utilisé comme alias pour les colonnes BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE. C'est une fonctionnalité de compatibilité.

  • KEY est un synonyme de INDEX. Depuis la version 4.1, l'attribut de clé PRIMARY KEY peut aussi être spécifié avec la clause KEY. Il a été implémenté pour assurer la compatibilité avec les autres bases.

  • Avec MySQL, une clé UNIQUE peut avoir uniquement avoir deux valeurs distinctes. Une erreur surviendra si vous essayez d'ajouter une ligne dont la clé correspond à une ligne existante.

  • Une clé primaire (PRIMARY KEY) est un index UNIQUE avec la contrainte supplémentaire que les toutes les colonnes utilisées doit avoir l'attribut NOT NULL. En MySQL, cette clé est dite PRIMARY. Une table ne peut avoir qu'une seule clé primaire. Si vous n'avez pas de PRIMARY KEY et que des applications demandent la PRIMARY KEY dans vos tables, MySQL retournera la première clé UNIQUE, qui n'a aucune valeur NULL.

  • Dans une table créée, la clé primaire PRIMARY KEY est placée en première, suivie de tous les index UNIQUE, et enfin, les index non-unique. Cela permet à l'optimiseur MySQL d'utiliser en priorité les index, et de détecter rapidement les doublons pour les clés UNIQUE.

  • Une PRIMARY KEY peut être multi-colonnes. Cependant, vous ne pouvez pas créer d'index multi-colonnes avec l'attribut PRIMARY KEY dans une spécification de colonne. En faisant cela, le seul résultat sera que cette seule colonne sera marquée comme clé primaire. Vous devez absolument utiliser la syntaxe PRIMARY KEY (index_nom_de_colonne, ...).

  • Si une clé primaire (PRIMARY) ou unique (UNIQUE) est établit sur une seule colonne, et que cette colonne est de type entier, vous pouvez aussi faire référence à cette colonne sous le nom _rowid (nouveau en version 3.23.11).

  • Avec MySQL, le nom de la clé primaire PRIMARY KEY est PRIMARY. Si vous ne donnez pas de nom à un index, l'index prendra le nom de la première colonne qui le compose, avec éventuellement un suffixe (_2, _3, ...) pour le rendre unique. Vous pouvez voir les noms des index avec la commande SHOW INDEX FROM tbl_name. See Section 13.5.3.6, « Syntaxe de SHOW DATABASES ».

  • Depuis MySQL 4.1.0, certains moteurs de stockage vous permettent de spécifier un type d'index lors de la création d'un index. La syntaxe de index_type est USING type_name. Les valeurs possibles de type_name qui sont supportées par les différents moteurs de stockages sont listés ci-dessous. Lorsque des index multiples sont listés, le premier rencontré est celui par défaut, si aucun index_type n'est spécifié.

    Moteur de tableTypes d'index
    MyISAMBTREE
    InnoDBBTREE
    MEMORY/HEAPHASH, BTREE

    Exemple :

    CREATE TABLE lookup
        (id INT, INDEX USING BTREE (id))
        ENGINE = MEMORY;
    

    TYPE type_name peut être utilisé comme synonyme de USING type_name, pour spécifier un type d'index. Cependant, USING est la forme recommandée. De plus, le nom d'index qui précède le type d'index dans la syntaxe de spécification n'est pas optionnelle avec TYPE. Ceci est dû au fait que contrairement à USING, TYPE n'est pas un mot réservé, et donc, il pourrait être interprété comme un nom d'index.

    Si vous spécifiez un type d'index qui n'est pas légal pour le moteur de stockage, mais qu'il y a un autre type d'index que le moteur peut utiliser sans affecter les résultats de la requête, le moteur utilisera ce type en remplacement.

  • Seuls, les formats de table MyISAM, InnoDB, et BDB supportent des index sur des colonnes qui peuvent contenir des valeurs NULL. Dans les autres situations, vous devez déclarer ces colonnes NOT NULL ou une erreur sera générée.

  • Avec la syntaxe nom_de_colonne(longueur), vous pouvez spécifier un index qui n'utilise qu'une partie de la colonne CHAR ou VARCHAR. Cela peut réduire la taille des fichiers d'index. See Section 7.4.3, « Index de colonnes ».

    Le format de table MyISAM, et depuis la version MySQL 4.0.14, InnoDB, supportent l'indexation des colonnes BLOB et TEXT. Lorsque vous ajoutez un index à une colonne BLOB ou TEXT, vous devez absolument spécifier une longueur d'index :

    CREATE TABLE test (blob_col BLOB, INDEX(blob_col(10)));
    

    Le préfixe peut valoir jusqu'à 255 octets de long (où 1000 octets pour les tables MyISAM et InnoDB depuis MySQL 4.1.2). Notez que le préfixe est mesuré en octets, alors que la longueur du préfixe de CREATE TABLE est interprété comme un nombre de caractères. Prenez cela en compte lorsque vous spécifiez une longueur de préfixe pour une colonne dont le jeu de caractères est multi-octets.

  • Une spécification index_col_name peut se terminer avec ASC ou DESC. Ces mots clés sont prévus pour des extensions futures qui permettront un stockage dans un ordre donné. Actuellement, ils sont reconnus mais ignorés : les index sont stockés en ordre ascendant.

  • Lorsque vous utilisez une clause ORDER BY ou GROUP BY sur une colonne de type TEXT ou BLOB, seuls, les max_sort_longueur premiers octets seront lus. See Section 11.4.3, « Les types BLOB et TEXT ».

  • En MySQL version 3.23.23 ou plus récent, vous pouvez aussi créer des index spécial FULLTEXT. Ils sont utilisés pour faire des recherches en texte plein. Seul, le format de table MyISAM supporte les index FULLTEXT. Ils peuvent être créés uniquement pour les colonnes de type VARCHAR et TEXT. L'indexation est alors exécutée sur toute la colonne, et les indexations partielles ne sont pas supportées. Voir Section 12.6, « Recherche en texte intégral (Full-text) dans MySQL » pour les détails.

  • En MySQL version 4.1 ou plus récent, vous pouvez créer les index spéciaux SPATIAL pour les colonnes géographiques. Les types spatiaux sont supportés par les tables MyISAM, et les colonnes indexées doivent être déclarées comme NOT NULL. Voyez Chapitre 18, Données spatiales avec MySQL.

  • En MySQL version 3.23.44 et plus récent, les tables InnoDB supportent la vérification de clé étrangères. See Chapitre 15, Le moteur de tables InnoDB. Notez que la syntaxe des clés étrangères FOREIGN KEY de InnoDB est plus restrictive que la syntaxe présentée ci-dessus. InnoDB ne permet pas la spécification d'un index_name, et les colonnes de la table référencée doivent être explicitement nommées. Depuis la version 4.0.8, InnoDB supporte les clauses ON DELETE et ON UPDATE avec les clés étrangères. Voyez le manuel InnoDB pour la syntaxe précise. Section 15.7.4, « Contraintes de clés étrangères FOREIGN KEY ».

    Pour les autres types de tables, le serveur MySQL n'analyse pas les clauses FOREIGN KEY, CHECK et REFERENCES dans les commandes CREATE TABLE, et aucune action n'est réalisée. See Section 1.5.5.5, « Les clés étrangères ».

  • Pour les tables MyISAM et ISAM, chaque colonne NULL requiert un bit supplémentaire, arrondi à l'octet supérieur le plus proche.

    La taille maximale d'enregistrement peut être calculée comme ceci :

    row longueur = 1
                 + (somme des longueurs de colonnes)
                 + (nombre de colonnes NULL + 7)/8
                 + (nombre de colonnes à taille variable)
    

    delete_flag vaut 1 pour les tables avec un format fixe. Les tables à format fixe utilisent un bit dans les lignes pour un marqueur, qui indique si la ligne a été effacée. delete_flag vaut 0 pour les tables à ligne dynamique, car le marquer est stocké dans l'entête de la ligne.

    Ces calculs ne s'appliquent pas aux tables InnoDB, qui ne font pas la différente entre les colonnes NULL et les colonnes NOT NULL.

Les options options_de_table et SELECT ne sont implémentées que dans MySQL version 3.23 et plus récent.

Les options ENGINE et TYPE spécifie le type de moteur de table. ENGINE a été ajouté en MySQL 4.0.18, pour la série des 4.0 et 4.1.2, pour la série des 4.1. C'est le nom d'attribut recommandé pour ces versions et TYPE est maintenant abandonné. TYPE sera supporté dans les séries 4.x, mais abandonnée probablement en MySQL 5.1.

Les différents types de tables sont :

Table typeDescription
ARCHIVELe moteur d'archivage. See Section 14.7, « Le moteur de table ARCHIVE ».
BDBTables avec transactions. See Section 14.4, « Tables BDB ou BerkeleyDB ».
BerkeleyDBUn alias de BDB.
CSVTables qui stockent les lignes au format valeurs séparées par des virgules. See Section 14.8, « Le moteur CSV ».
EXAMPLEUn moteur d'exemple. See Section 14.5, « Le moteur de table EXAMPLE ».
FEDERATEDUn moteur qui accède à des tables distantes. See Section 14.6, « Le moteur de table FEDERATED ».
HEAPLes données de ces tables ne sont stockées qu'en mémoire. See Section 14.3, « Le moteur de table MEMORY (HEAP) ».
ISAMLe gestionnaire originel de tables. See Section 14.9, « Tables ISAM ».
InnoDBTables transactionnelles avec verrou de lignes. See Chapitre 15, Le moteur de tables InnoDB.
MEMORYUn alias de HEAP. (En fait, depuis MySQL 4.1, MEMORY est le terme recommandé.)
MERGEUn ensemble de tables MyISAM utilisées comme une seule et même table. See Section 14.2, « Tables assemblées MERGE ».
MRG_MyISAMUn synonyme pour les tables MERGE.
MyISAMLe nouveau gestionnaire de table binaire et portable. See Section 14.1, « Le moteur de tables MyISAM ».
NDBAlias de NDBCLUSTER.
NDBCLUSTERTables en grappe et en mémoire, tolérantes aux pannes. See Chapitre 16, Introduction à MySQL Cluster.

See Chapitre 14, Moteurs de tables MySQL et types de table.

Si un type de table est demandé, mais que ce type particulier n'est pas disponible, MySQL va choisir le type de table le plus proche de celui qui est spécifié. Par exemple, si TYPE=BDB est spécifié, et que la distribution de MySQL ne supporte pas les tables BDB, la table qui sera créée sera du type MyISAM.

Les autres options de tables sont utilisées pour optimiser le comportement de la table. Dans la plupart des cas, vous n'avez pas à les spécifier. Les options fonctionnent pour tous les types de tables (sauf contre-indication) :

  • AUTO_INCREMENT

    La prochaine valeur AUTO_INCREMENT de votre table (MyISAM). Ne fonctionne que pour les tables MyISAM. Pour donner la première valeur à une colonne AUTO_INCREMENT InnoDB, insérez une ligne bidon, avec la valeur désirée moins un, puis supprimez la ligne.

  • AVG_ROW_LENGTH

    La taille moyenne approchée des lignes de votre table. Vous ne devez fournir cette valeur que pour les tables à taille de ligne variable, de très grande taille.

    Lorsque vous créer une table MyISAM, MySQL utilise le produit des options MAX_ROWS et AVG_ROW_LENGTH pour décider de la taille du résultat. Si vous ne spécifiez aucune option, la taille maximale de la table sera de 4 Go (ou 2 Go si votre système d'exploitation ne supporte que les tables de 2 Go). Ceci sert à conserver la taille des pointeurs d'index petite, et rapide, si nous n'avez pas besoin de gros fichiers. Si vous voulez que vos tables dépassent 4 Go de taille, et que vous voulez garder les tables petites taille un peu plus lentes et grosses que nécessaire, vous pouvez augmenter la taille du pointeur d'index en modifiant la variable système globale myisam_data_pointer_size, qui a été ajoutée en MySQL 4.1.2. See Section 5.2.3, « Variables serveur système ».

  • CHECKSUM

    Passez 1 si vous voulez que MySQL génère une somme de vérification (ce qui facilite la recherche des lignes corrompues, mais ralentit les mises à jour). La commande CHECKSUM TABLE rapporte cette somme. MyISAM uniquement.

  • COMMENT

    Un commentaire pour votre table (60 caractères).

  • MAX_ROWS

    Nombre de lignes maximum que vous pensez stocker dans la table.

  • MIN_ROWS

    Nombre de minimum lignes que vous pensez stocker dans la table.

  • PACK_KEYS

    Spécifiez 1 si vous voulez un index plus compact. Généralement cela rend les mises à jour plus lentes, mais les lectures plus rapides.

    Spécifier la valeur de 0 désactive tout le compactage de clé. Spécifier la valeur DEFAULT (MySQL 4.0) indique au moteur de stockage de ne stocker que les colonnes CHAR/VARCHAR. (MyISAM et ISAM uniquement)

    Si vous n'utilisez pas PACK_KEYS, le comportement par défaut est de ne stocker que les chaînes, et non pas les nombres. Si vous utilisez PACK_KEYS=1, les nombres seront aussi compactés.

    Lors du compactage, MySQL utilise une compression de préfixe :

    • Chaque clé requiert un octet de plus pour indiquer combien d'octets sont identiques dans la clé précédente.

    • Le pointeur de ligne est stocké au format grand-octet-en-premier, directement après la clé, pour améliorer la compression.

    Cela signifie que si vous avez de nombreuses clés proches sur des lignes consécutives, les clés successives ``identiques'' ne prendront généralement que deux octets (incluant le pointeur de ligne). Comparez cela à la situation ordinaire, où les clés successives occupent taille_de_cle + taille_de_pointeur (où la taille du pointeur est généralement de 4). En conséquence, vous tirerez le meilleur parti de cette compression si vous avez plusieurs nombres identiques. Si toutes les clés sont totalement différentes, vous utiliserez un octet de plus par clé, si la clé n'accepte pas les valeurs NULL. Dans ce cas, la taille de la clé sera stockée dans le même octet que celui qui indique que la clé est NULL.)

  • PASSWORD

    Chiffre le fichier .frm avec un mot de passe. Cette option ne fait rien du tout pour la version standard de MySQL.

  • DELAY_KEY_WRITE

    Spécifiez 1 si vous voulez attendre la fermeture de la table pour mettre à jour les index. MyISAM uniquement.

  • ROW_FORMAT

    Définit la méthode de stockage des lignes (réservé pour le futur). Actuellement, cette option fonctionne uniquement avec des tables MyISAM qui supportent le DYNAMIC et FIXED en format de ligne. See Section 14.1.3, « Formats de table MyISAM ».

  • RAID_TYPE

    L'option RAID_TYPE vous permet de dépasser la limite de 2 Go/4 Go de votre fichier de données MyISAM (mais pas le fichier d'index), pour les systèmes d'exploitation qui ne supportent pas les grands fichiers. Cette option n'est pas recommandée pour les systèmes d'exploitation qui supportent les grands fichiers.

    Vous pouvez réduire les ralentissements d'E/S en pla¸ant les dossiers RAID sur différents disques physiques. Actuellement, le seul type RAID_TYPE est STRIPED. 1 et RAID0 sont des alias de STRIPED.

    Si vous spécifiez l'option RAID_TYPE pour une table MyISAM, spécifiez les options RAID_CHUNKS et RAID_CHUNKSIZE en même temps. La valeur maximale de RAID_CHUNKS est 255. MyISAM va créer RAID_CHUNKS sous-dossiers appelés 00, 01, 02, ... 09, 0a, 0b, ... dans le dossier de données. Dans chaque dossier, MyISAM va créer un fichier tbl_name.MYD. Lors de l'écriture dans le fichier de données, le gestionnaire RAID place les RAID_CHUNKSIZE*1024 premiers octets dans le premier fichier, les seconds RAID_CHUNKSIZE*1024 octets dans le fichier suivant, etc.

    RAID_TYPE fonctionne sur tous les systèmes d'exploitation, tant que vous avez compilé MySQL avec --with-raid, avec le script configure. Pour déterminer si votre serveur a le support des tables RAID, utilisez SHOW VARIABLES LIKE 'have_raid' pour voir si sa valeur vaut YES.

  • UNION

    UNION sert lorsque vous voulez que plusieurs tables identiques se comporte comme une seule table. Cela fonctionne avec les tables MERGE. See Section 14.2, « Tables assemblées MERGE ».

    Pour le moment, vous devez avoir les droits de SELECT, UPDATE et DELETE pour les tables intégrées dans la table MERGE. Originalement, toutes les tables utilisées devaient être dans la même base de données, que la table MERGE. Cette restriction a été levée depuis MySQL 4.1.1.

  • INSERT_METHOD

    Si vous voulez insérer des données dans une table MERGE, vous devez spécifier la table d'insertion avec l'attribut INSERT_METHOD. L'option INSERT_METHOD est utilisée uniquement avec les tables MERGE. Cette option a été introduite en MySQL 4.0.0. See Section 14.2, « Tables assemblées MERGE ».

  • DATA DIRECTORY, INDEX DIRECTORY

    En utilisant DATA DIRECTORY='directory' ou INDEX DIRECTORY='directory', vous pouvez spécifier où le moteur de stockage MyISAM doit placer les données de la table et le fichier d'index. Notez que vous devez donner un chemin absolu, et non un chemin relatif.

    Ces options ne fonctionnent que pour les tables MyISAM depuis MySQL 4.0, lorsque vous n'utilisez pas l'option --skip-symlink. Votre système d'exploitation doit aussi disposer d'une fonction realpath() compatible avec les threads. See Section 7.6.1.2, « Utiliser les liens symboliques avec les tables sous Unix ».

Depuis MySQL 3.23, vous pouvez créer une table à partir d'une autre, en ajoutant une commande SELECT après la commande CREATE TABLE :

CREATE TABLE new_tbl SELECT * FROM orig_tbl;

MySQL va créer une nouvelle colonne pour chaque colonne de résultat de la commande SELECT. Par exemple :

mysql> CREATE TABLE test (a INT NOT NULL AUTO_INCREMENT,
    ->        PRIMARY KEY (a), KEY(b))
    ->        TYPE=MyISAM SELECT b,c FROM test2;

Cela créer une table MyISAM avec trois colonnes a, b, et c. Notez que les colonnes de la commande SELECT sont ajoutées à droite de la table, et non dans la liste des colonnes. Par exemple :

mysql> SELECT * FROM foo;
+---+
| n |
+---+
| 1 |
+---+

mysql> CREATE TABLE bar (m INT) SELECT n FROM foo;
Query OK, 1 row affected (0.02 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM bar;
+------+---+
| m    | n |
+------+---+
| NULL | 1 |
+------+---+
1 row in set (0.00 sec)

Pour chaque ligne de la table foo, une ligne est insérée dans la colonne bar avec la valeur issue de foo et la valeur par défaut pour les nouvelles colonnes.

Si une erreur survient durant la copie de la table, la table est automatiquement effacée.

CREATE TABLE ... SELECT ne va pas créer automatiquement les index pour vous. Ceci est fait intentionnellement pour rendre la commande aussi souple que possible. Si vous voulez avoir les mêmes index, vous devez les spécifier dans la commande avant le SELECT :

mysql> CREATE TABLE bar (UNIQUE (n)) SELECT n FROM foo;

Certaines conversions de type pourraient avoir lieu. Par exemple, l'attribut AUTO_INCREMENT n'est pas préservé, et les colonnes VARCHAR peuvent devenir des colonnes CHAR.

Lors de la création de la table avec CREATE ... SELECT, assurez vous de mettre un nom d'alias à toutes les fonctions ou expression de la requête. Si vous ne le faîtes pas, la commande CREATE peut échouer ou donner des noms de colonnes inattendus.

CREATE TABLE artists_and_works
SELECT artist.name, COUNT(work.artist_id) AS number_of_works
FROM artist LEFT JOIN work ON artist.id = work.artist_id
GROUP BY artist.id;

Depuis MySQL 4.1, vous pouvez spécifier explicitement le type de colonne généré :

CREATE TABLE foo (a TINYINT NOT NULL) SELECT b+1 AS a FROM bar;

En MySQL 4.1, vous pouvez aussi utiliser la clause LIKE pour créer une table basée sur la définition d'une autre table, y compris les attributs de colonnes et les index originaux :

CREATE TABLE new_tbl LIKE orig_tbl;

CREATE TABLE ... LIKE ne copie pas les options de tables DATA DIRECTORY et INDEX DIRECTORY qui étaient spécifiées dans la table originale.

Vous pouvez faire précéder SELECT par IGNORE ou REPLACE pour indiquer comment gérer les clés doublons. Avec IGNORE, les nouvelles lignes qui sont en double seront ignorés. Avec REPLACE, les nouvelles lignes remplaceront les lignes précédentes, qui avaient la même valeur d'index. Si ni IGNORE, ni REPLACE ne sont spécifié, les doublons génèreront une erreur.

Pour s'assurer que le log binaire peut être réutilisé pour recréer la table originale, MySQL ne permettra pas les insertions concurrentes durant une commande CREATE TABLE ... SELECT.

13.2.5.1. Modification automatique du type de colonnes

Dans certains cas, MySQL change automatiquement la spécification d'une colonne fournie dans la commande CREATE TABLE. (Cela peut aussi arriver avec ALTER TABLE) :

  • Les colonnes VARCHAR avec une taille inférieure à quatre (4) sont changées en CHAR.

  • Si l'une des colonnes d'une table est de taille variable, toute la ligne est, par conséquent, de taille variable. Ainsi, si une ligne contient une colonne de taille variable (VARCHAR, TEXT ou BLOB) toutes les colonnes CHAR de plus de trois caractères sont transformées en VARCHAR. Cela ne change en rien la fa¸on dont vous utilisez les colonnes. Pour MySQL, VARCHAR est simplement une autre fa¸on de stocker les caractères. MySQL effectue cette conversion car cela économise de la place, et rend les calculs sur les tables plus rapides. See Chapitre 14, Moteurs de tables MySQL et types de table.

  • Depuis la version 4.1.0, si un champ CHAR ou VARCHAR est spécifié avec une taille supérieure à 255, il est converti en TEXT. C'est une fonctionnalité de compatibilité.

  • La taille d'affichage de TIMESTAMP doit être un nombre pair et être compris entre 2 et 14. (2, 4, 6, 8, 10, 12 ou 14). Si vous spécifiez une taille plus grande que 14, ou inférieure à 2, celle-ci sera transformée en 14. Les valeurs impaires sont ramenées à la valeur pair supérieure la plus proche.

  • Vous ne pouvez pas stocker de valeur littérale NULL dans une colonne de type TIMESTAMP. Cette valeur sera remplacée par la date et l'heure courante. De ce fait, les attributs NULL et NOT NULL n'ont pas de sens pour ces colonnes et sont ignorés. DESCRIBE nom_de_table indiquera toujours que la colonne TIMESTAMP accepte les valeurs NULL.

  • Les colonnes qui font partie d'une PRIMARY KEY ont l'attribut NOT NULL même si elles ne sont pas déclarées comme tel.

  • Depuis MySQL 3.23.51, les espaces terminaux sont automatiquement supprimés des valeurs ENUM et SET lors de la création de la table.

  • MySQL change certains type de colonnes utilisés par d'autres serveurs SQL en types MySQL. See Section 11.7, « Utilisation des types de données issues d'autres SGBDR ».

  • Si vous utilisez une clause USING pour spécifier un type d'index qui n'est pas légal pour un moteur de stockage, mais qu'un autre type d'index est disponible pour ce moteur sans affecter les résultats, le moteur utilisera le type disponible.

Si vous voulez voir si MySQL a utilisé un autre type que celui que vous avez spécifié, utilisez la commande DESCRIBE nom_de_table, après votre création ou modification de structure de table.

Certain types de colonnes peuvent être modifiés si vous compressez une table en utilisant l'utilitaire myisampack. See Section 14.1.3.3, « Caractéristiques des tables compressées ».

13.2.6. Syntaxe de DROP DATABASE

DROP DATABASE [IF EXISTS] db_name

DROP DATABASE détruit toutes les tables dans la base de données et l'efface elle même. Soyez très prudent avec cette commande! Pour utiliser la commande DROP DATABASE, vous avez besoin du droit de DROP sur cette base.

Depuis la version 3.22 de MySQL, vous pouvez utiliser le mot clef IF EXISTS pour éviter l'affichage d'erreur si la base n'existe pas.

Si vous utilisez la commande DROP DATABASE sur un lien symbolique pointant sur la base de données, le lien et la base seront effacés.

Depuis MySQL 4.1.2, DROP DATABASE retourne le nombre de tables qui ont été supprimées. Cela revient à compter le nombre de fichiers .frm qui ont été supprimées.

La commande DROP DATABASE efface tous les fichiers du dossier de la base de données, qui ont été créés par MySQL lui-même, durant ses opérations normales :

  • Tous les fichiers avec les extensions suivantes :

    .BAK.DAT.HSH.ISD
    .ISM.ISM.MRG.MYD
    .MYI.db.frm 
  • Tous les sous-dossiers qui consistent de 2 chiffres hexadécimaux 00-ff. Ce sont des dossiers RAID) qui sont aussi supprimés.

  • Le fichier db.opt, s'il existe.

Si d'autres fichiers ou dossiers restent dans le dossier de base après que MySQL ait supprimés ceux listés ci-dessus, le dossier de base ne pourra pas être supprimé. Dans ce cas, vous devez supprimer manuellement les fichiers restant, et lancer à nouveau la commande DROP DATABASE.

Vous pouvez aussi supprimer des bases de données avec mysqladmin. See Section 8.4, « mysqladmin, administration d'un serveur MySQL ».

13.2.7. Syntaxe de DROP INDEX

DROP INDEX nom_de_l_index ON nom_de_table

DROP INDEX supprime l'index nommé nom_de_l_index de la table nom_de_table. DROP INDEX ne fait rien avec la version 3.22 et les précédentes. Depuis cette version, DROP INDEX est un alias d'ALTER TABLE supprimant l'index.

See Section 13.2.2, « Syntaxe de ALTER TABLE ».

13.2.8. Syntaxe de DROP TABLE

DROP [TEMPORARY] TABLE [IF EXISTS]
    tbl_name [, tbl_name] ...
    [RESTRICT | CASCADE]

DROP TABLE supprime une ou plusieurs tables. Toutes les données et la structure de la tables sont perdues, alors soyez prudents avec cette commande !

Depuis la version 3.22 de MySQL, vous pouvez utiliser le mot réservé IF EXISTS pour éviter l'affichage des erreurs pour les tables qui n'existent pas. See Section 13.5.3.19, « SHOW WARNINGS | ERRORS ».

RESTRICT et CASCADE sont autorisés pour faciliter le port. Pour le moment, elles ne font rien.

Note : DROP TABLE va automatiquement valider les transactions actives (hormis si vous utilisez la version 4.1 et le mot clé TEMPORARY).

L'option TEMPORARY est ignorée en 4.0. En 4.1, cette option fonctionne comme suit :

  • Détruit uniquement les tables temporaires.

  • Ne termine pas les transactions en cours.

  • Aucun droits d'accès n'est vérifié.

TEMPORARY est pour le moment ignoré; Dans un futur proche, il servira à s'assurer qu'on efface vraiment une table temporaire.

13.2.9. Syntaxe de RENAME TABLE

RENAME TABLE nom_de_table TO nouveau_nom_de_table[, nom_de_table2 TO nouveau_nom_de_table2,...]

Le changement de nom se fait atomiquement ce qui signifie qu'aucun autre processus ne peut accéder la table tant que l'opération est en cours. Cela rend possible de remplacer une vielle table avec une table vide :

CREATE TABLE nouvelle_table (...);
RENAME TABLE ancienne_table TO backup_table, nouvelle_table TO ancienne_table;

L'opération s'effectue de gauche à droite ce qui signifie que si vous voulez échanger deux noms de tables, vous devez :

RENAME TABLE ancienne_table    TO backup_table,
             nouvelle_table    TO ancienne_table,
             backup_table TO nouvelle_table;

Si les deux bases de données sont sur le même disque, vous pouvez renommer à travers les bases :

RENAME TABLE bdd_courante.nom_de_table TO autre_bdd.nom_de_table;

Quand vous exécutez RENAME, vous ne pouvez avoir aucune transaction active ou une table protégée en mode écriture. Vous devez avoir les privilèges ALTER et DROP sur l'ancienne table, et les privilèges CREATE et INSERT sur la nouvelle.

Si MySQL rencontre des erreurs dans un renommage multiple, il remettra les noms changés à leurs valeurs d'origine pour revenir à l'état d'origine.

RENAME TABLE a été ajouté à la version 3.23.23 de MySQL.

13.3. Commandes de bases de l'utilisateur de MySQL

13.3.1. Syntaxe de DESCRIBE (obtenir des informations sur les colonnes)

{DESCRIBE | DESC} nom_de_table [nom_de_colonne | wild]

DESCRIBE fournit des informations à propos des colonnes de la table. DESCRIBE est un raccourci de SHOW COLUMNS FROM.

See Section 13.5.3.3, « Syntaxe de SHOW COLUMNS ».

nom_de_colonne peut être le nom d'une colonne ou une chaîne contenant les caractères spéciaux SQL ‘%’ et ‘_’. Il n'est pas nécessaire de placer la chaîne entre guillemets, hormis s'il y a des espaces ou d'autres caractères spéciaux.

mysql> DESCRIBE city;
+------------+----------+------+-----+---------+----------------+
| Field      | Type     | Null | Key | Default | Extra          |
+------------+----------+------+-----+---------+----------------+
| Id         | int(11)  |      | PRI | NULL    | auto_increment |
| Name       | char(35) |      |     |         |                |
| Country    | char(3)  |      | UNI |         |                |
| District   | char(20) | YES  | MUL |         |                |
| Population | int(11)  |      |     | 0       |                |
+------------+----------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

La colonne Null indique si la valeur NULL peut être stockée dans la colonne. YES indique que c'est le cas.

La colonne Key indique si un champ est indexé. La valeur PRI indique que le champ fait partie de la clé primaire de la table. UNI indique que le champ fait partie d'un index UNIQUE. La valeur MUL indique que plusieurs occurences d'une valeur sont autorisées dans le champ.

Un champ peut être désigné comme MUL même si l'index UNIQUE est utilisé, si les valeurs NULL sont autorisées, car une colonne UNIQUE peut contenir plusieurs valeurs NULL si la colonne est déclarée comme NOT NULL. Une autre cause pour MUL sur une colonne UNIQUE est lorsque deux colonnes forment un couple UNIQUE : même si la combinaison des deux colonnes est toujours unique, chaque colonne peut contenir des valeurs multiples. Notez que dans un index composé, seul le champ de gauche aura une entrée dans la colonne Key.

La colonne Default indique la valeur par défaut assignée à ce champ.

La colonne Extra indique des informations supplémentaires, disponibles sur le champ. Dans notre exemple, la colonne Extra indique que la colonne Id porte l'attribut AUTO_INCREMENT.

Si le type de colonne est différent de celui que vous pensiez avoir définit lors du CREATE TABLE, notez que MySQL change le type des colonnes de temps en temps. See Section 13.2.5.1, « Modification automatique du type de colonnes ».

Cette instruction est fournie pour une meilleure compatibilité avec Oracle.

L'instruction SHOW renvoie les mêmes informations. See Section 13.5.3, « Syntaxe de SHOW ».

13.3.2. Syntaxe de USE

USE db_name

La commande USE db_name spécifie à MySQL d'utiliser la base db_name comme base par défaut pour les requêtes ne les mentionnant pas. La base choisie reste la même jusqu'à la fermeture de la session ou un nouvel appel à USE :

mysql> USE db1;
mysql> SELECT COUNT(*) FROM ma_table;      # sélectionne à partir de db1.ma_table
mysql> USE db2;
mysql> SELECT COUNT(*) FROM ma_table;      # sélectionne à partir de db2.ma_table

Rendre une base de données la base courante (en utilisant USE) ne vous interdit pas l'accès à d'autres tables dans d'autres bases. L'exemple suivant accède à la table author de la base db1 et à la table editor de la base db2 :

mysql> USE db1;
mysql> SELECT author_name,editor_name FROM author,db2.editor
    ->        WHERE author.editor_id = db2.editor.editor_id;

La commande USE est fournie pour assurer la compatibilité Sybase.

13.4. Commandes relatives aux verrous et aux transactions

13.4.1. Syntaxes de START TRANSACTION, COMMIT et ROLLBACK

Par défaut, MySQL est lancé en mode autocommit. Cela signifie que chaque modification effectué est enregistré immédiatement sur le disque par MySQL.

Si vous utilisez des tables supportant les transactions (comme InnoDB, BDB), vous pouvez configurer MySQL en mode non-autocommit grâce à la commande:

SET AUTOCOMMIT=0

A partir de là, vous devez utiliser COMMIT pour enregistrer les modifications sur le disque ou ROLLBACK pour ignorer les modifications apportées depuis le début de la transaction.

Si vous souhaitez sortir du mode AUTOCOMMIT pour une série d'opérations, vous pouvez utiliser les commandes BEGIN ou BEGIN WORK :

START TRANSACTION;
SELECT @A:=SUM(salary) FROM table1 WHERE type=1;
UPDATE table2 SET summmary=@A WHERE type=1;
COMMIT;

BEGIN et BEGIN WORK peuvent être utilisé à la place de START TRANSACTION pour initialiser une transaction. START TRANSACTION a été ajouté en MySQL 4.0.11; C'est une syntaxe SQL-99, et il est recommandé de l'utiliser pour lancer une transaction. BEGIN et BEGIN WORK sont disponibles pour MySQL 3.23.17 et 3.23.19, respectivement.

Notez que si vous n'utilisez pas de table transactionnelles, les modifications seront validées automatiquement, indépendamment du mode de validation.

Si vous faites un ROLLBACK après avoir modifié une table non transactionnelle, vous obtiendrez (ER_WARNING_NOT_COMPLETE_ROLLBACK) comme message d'alerte. Toutes les tables supportant les transactions seront restaurées, mais aucune des autres tables ne changera.

Si vous utilisez START TRANSACTION ou SET AUTOCOMMIT=0, il est recommandé d'utiliser les "binary log" de MySQL à la place des anciens logs de modifications pour les sauvegardes. Les transactions sont stockées dans les logs binaires en un seul bloc, après COMMIT, pour être sûr que les transactions qui ont été annulées ne soient pas enregistrées. See Section 5.9.4, « Le log binaire ».

Vous pouvez changer le niveau d'isolation des transactions avec SET TRANSACTION ISOLATION LEVEL .... See Section 13.4.6, « Syntaxe de SET TRANSACTION  ».

13.4.2. Commandes qui ne peuvent pas être annulées

Certaines commandes ne peuvent pas être annulées. En général, elles incluent le langage de définition des données (DDL), comme les commandes qui créent ou effacent des bases de données, ou celles qui créent, modifient ou effacent des tables de données.

Il est recommandé de concevoir vos transactions pour éviter ces commandes. Si vous soumettez une commande qui ne peut pas être annulée, dès le début de votre transaction, et qu'une commande ultérieure échoue, vous pourrez pas annuler l'ensemble de la transaction avec ROLLBACK.

13.4.3. Commandes qui peuvent causer une validation implicite

Les commandes suivantes valident implicitement une transaction, comme si vous aviez émis une commande COMMIT après  :

ALTER TABLEBEGINCREATE INDEX
DROP DATABASEDROP INDEXDROP TABLE
LOAD MASTER DATALOCK TABLESRENAME TABLE
SET AUTOCOMMIT=1START TRANSACTIONTRUNCATE

UNLOCK TABLES termine aussi une transaction si toutes les tables courantes sont verrouillées. Avant MySQL version 4.0.13, CREATE TABLE terminait une transaction si le log binaire était activé.

Les transactions ne peuvent pas être imbriquées. C'est la conséquence de cette validation COMMIT implicite pour toutes les transactions en cours, lorsque vous émettez une commande START TRANSACTION ou équivalent.

13.4.4. Syntaxe de SAVEPOINT et ROLLBACK TO SAVEPOINT

Depuis MySQL 4.0.14 et 4.1.1, InnoDB supporte les commandes SQL SAVEPOINT et ROLLBACK TO SAVEPOINT.

SAVEPOINT identifier

Cette commande pose un jalon de transaction dont le nom est identifier. Si la transaction courante a déjà un jalon de ce nom, l'ancien jalon est effacé, et le nouveau est créé à la place.

Cette commande annule la transaction jusqu'au jalon. Les modifications que cette transaction a fait aux lignes depuis le jalon sont annulées, mais InnoDB ne libère pas les verrous posés en mémoire après le jalon. Notez que pour une nouvelle ligne insérée, l'information de verrou est conservée par l'identifiant de transaction de la ligne : le verrou n'est pas stocké en mémoire. Dansa ce cas, le verrou sera levé par l'annulation. Les jalons qui ont été posé après celui-ci sont aussi annulés.

Si la commande retourne l'erreur suivante, c'est qu'aucun jalon de ce nom n'a pu être trouvé.

ERROR 1181: Got error 153 during ROLLBACK

Tous les jalons de la transaction courante sont annulés si vous exécutez les commandes COMMIT ou ROLLBACK, sans préciser de nom de jalon.

13.4.5. Syntaxe de LOCK TABLES/UNLOCK TABLES

LOCK TABLES
    tbl_name [AS alias] {READ [LOCAL] | [LOW_PRIORITY] WRITE}
    [, tbl_name [AS alias] {READ [LOCAL] | [LOW_PRIORITY] WRITE}] ...
UNLOCK TABLES

LOCK TABLES verrouille une table pour le thread courant. UNLOCK TABLES déverrouillera automatiquement tous les verrous posés par le thread courant. Toutes les tables verrouillées par le thread courant sont automatiquement déverrouillées quand ce thread utilise à nouveau LOCK TABLES, ou quand la connexion au serveur est perdue.

Note : LOCK TABLES n'est pas compatible avec les transactions, et valide automatiquement toute transaction active avant de verrouiller une table.

L'utilisation de LOCK TABLES dans MySQL 4.0.2 nécessite le privilège LOCK TABLES global et un privilège de SELECT sur les tables impliquées. Dans MySQL 3.23, il faut les privilèges SELECT, INSERT, DELETE et UPDATE sur les tables.

Les principales raisons d'utiliser LOCK TABLES sont l'émulation de transactions ou l'accélération des processus de modification de tables. Cela sera détaillé plus loin.

Si un thread obtient un verrouillage READ sur une table, ce thread (et tous les autres threads) peuvent uniquement accéder a cette table en lecture. Si un thread obtient un verrouillage WRITE sur une table, alors seul le thread qui a posé le verrou peut lire ou écrire sur cette table. Tous les autres threads sont bloqués.

La différence entre READ LOCAL et READ est que READ LOCAL autorise des requêtes INSERT non-conflictuelles à être exécutées alors que le verrou est posé. Ceci ne peut cependant pas être utilisé si vous souhaitez modifier les fichiers de la base de données en dehors de MySQL pendant que le verrou est posé.

Quand vous utilisez LOCK TABLES, vous devez verrouiller toutes les tables que vous allez utiliser, et vous devez utiliser les mêmes alias sur ce que vous utiliserez dans vos requêtes ! Si vous utilisez une table a plusieurs reprises dans une requête (avec des alias), vous devez verrouiller chacun des alias !

Si vos requêtes utilisent un alias pour une table, alors vous devez verrouiller la table avec l'alias. Le verrouillage ne fonctionnera pas si vous verrouillez la table sans spécifier l'alias :

mysql> LOCK TABLE t READ;
mysql> SELECT * FROM t AS myalias;
ERROR 1100: Table 'myalias' was not locked with LOCK TABLES

De même, lorsque vous verrouillez une table avec un alias, vous devez utiliser le nom de l'alias dans vos requêtes :

mysql> LOCK TABLE t AS myalias READ;
mysql> SELECT * FROM t;
ERROR 1100: Table 't' was not locked with LOCK TABLES
mysql> SELECT * FROM t AS myalias;

Les verrous WRITE ont normalement des priorités supérieures aux verrous READ, afin de s'assurer que les modifications sont exécutées au plus vite. Cela signifie que si un thread demande un verrou READ et qu'un autre thread demande un verrou WRITE, la demande de verrou READ attendra que le thread WRITE ait abouti pour libérer le verrou. Vous pouvez utiliser le verrou LOW_PRIORITY WRITE pour permettre à d'autres threads d'obtenir des verrous READ pendant que le thread attend le verrou WRITE. Vous ne devriez utiliser les verrous LOW_PRIORITY WRITE que si vous êtes sûr qu'il y aura effectivement un moment où aucun thread ne posera de verrou READ.

LOCK TABLES fonctionne de la manière suivante :

  1. Trie toutes les tables à verrouiller dans un ordre défini par MySQL (l'utilisateur ne définit pas d'ordre).

  2. Si une table est verrouillée avec un verrou de lecture et un verrou d'écriture, il pose le verrou de lecture avant celui d'écriture.

  3. Verrouille une table à la fois jusqu'à ce que le thread ait tous ses verrous.

Cette politique garantit le bon verrouillage des tables. Il faut cependant connaître certaines choses sur ce schéma :

Si vous utilisez un verrou LOW_PRIORITY WRITE pour une table, cela signifie seulement que MySQL attendra, pour poser ce verrou, qu'aucun autre thread ne réclame de verrou READ. Quand le thread aura le verrou WRITE et qu'il attendra que les verrous soient posés sur les autres tables de la liste, tous les autres threads attendront que le verrou WRITE soit libéré. Si cela devient un problème grave pour votre application, il est conseillé de convertir des tables en tables supportant les transactions.

Vous pouvez terminer un thread attendant un verrouillage de table en toute sécurité avec KILL. See Section 13.5.4.3, « Syntaxe de KILL ».

Il est déconseillé de verrouiller des tables utilisées avec INSERT DELAYED, car, dans ce cas, la requête INSERT est exécutée dans un autre thread.

Normalement, vous n'avez pas besoin de verrouiller les tables puisque chaque requête UPDATE est atomique : aucun autre thread ne peut interférer avec une autre requête active. Il existe cependant quelques cas où vous aurez besoin de verrouiller les tables :

  • Si vous allez exécuter plusieurs requêtes sur plusieurs tables, il est préférable, d'un point de vue rapidité, de verrouiller les tables dont vous aurez besoin. L'inconvénient, bien sur, est que les autres threads ne pourront pas intervenir sur ces tables durant vos opérations, ni en extraire des informations si la table est verrouillée en WRITE.

    La raison pour laquelle les requêtes sont plus rapides avec LOCK TABLES est que MySQL ne rafraîchît pas l'index des clés des tables verrouillées tant que UNLOCK TABLES n'est pas invoqué (normalement, le cache des clés est rafraîchi après chaque requête SQL). Cela accélère les insertions, les modifications et les suppressions de données dans les tables MyISAM.

  • Si vous utilisez un type de table dans MySQL qui ne supporte pas les transactions, vous devez utiliser LOCK TABLES pour vous assurez qu'aucun autre thread ne s'intercale entre un SELECT et un UPDATE. L'exemple suivant nécessite LOCK TABLES pour s'exécuter en toute sécurité :

    mysql> LOCK TABLES trans READ, customer WRITE;
    mysql> SELECT SUM(value) FROM trans WHERE customer_id=some_id;
    mysql> UPDATE customer SET total_value=sum_from_previous_statement
        ->        WHERE customer_id=some_id;
    mysql> UNLOCK TABLES;
    

    Sans LOCK TABLES, Il est possible qu'un autre thread ait inséré une nouvelle ligne dans la table trans entre l'exécution du SELECT et l'exécution de la requête UPDATE.

L'utilisation de modifications incrémentales (UPDATE customer SET value=value+nouvelle_valeur) ou de la fonction LAST_INSERT_ID() permet de se passer de LOCK TABLES dans de nombreuses situations. See Section 1.5.5.3, « Transactions et opérations atomiques ».

Il est aussi possible de résoudre de nombreux cas en utilisant un verrou utilisateur, avec les fonctions GET_LOCK() et RELEASE_LOCK(). Ces verrous sont stockés dans une table de hashage dans le serveur et utilisent les fonctions pthread_mutex_lock() et pthread_mutex_unlock() pour plus de vitesse. See Section 12.8.4, « Fonctions diverses ».

Voir Section 7.3.1, « Méthodes de verrouillage » pour plus de détails.

Il est possible de verrouiller tous les tables de toutes les bases avec la commande FLUSH TABLES WITH READ LOCK.

See Section 13.5.4.2, « Syntaxe de FLUSH ». C'est une méthode très pratique pour effectuer des sauvegardes si vous utilisez un système de fichiers qui, comme Veritas, permet de créer des instantanés.

Note : LOCK TABLES ne fonctionne pas avec les transactions et validera automatiquement toutes les transactions actives avant de poser verrouiller la table. See Section A.7.1, « Problèmes avec ALTER TABLE. ».

13.4.6. Syntaxe de SET TRANSACTION

SET [GLOBAL | SESSION] TRANSACTION ISOLATION LEVEL
{ READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE }

Configuration du niveau d'isolation des transactions en général, pour la totalité de la session, ou pour la prochaine transaction.

Le comportement par défaut est de configurer le niveau d'isolation pour la transaction suivante (qui n'a pas encore été commencée) En utilisant le paramètre GLOBAL, on configure le niveau par défaut global pour toutes les nouvelles connections. Cette commande requiert les privilèges SUPER. En utilisant le paramètre SESSION, on configure le niveau par défaut pour toutes les prochaines transactions effectuées durant la session actuelle.

Pour une description de chaque niveau d'isolation de transaction InnoDB, voyez Section 15.11.2, « InnoDB et SET ... TRANSACTION ISOLATION LEVEL ... ». InnoDB supporte chacun des niveaux depuis MySQL 4.0.5. Le niveau par défaut est REPEATABLE READ.

On peut configurer le niveau d'isolation global des transactions pour mysqld avec --transaction-isolation=.... See Section 4.3.1, « Options de ligne de commande de mysqld ».

13.5. Référence de langage d'administration de la base de données

13.5.1. Commande de gestion des comptes utilisateurs

13.5.1.1. Syntaxe de CREATE USER

CREATE USER user [IDENTIFIED BY [PASSWORD] 'password']
    [, user [IDENTIFIED BY [PASSWORD] 'password']] ...

La commande CREATE USER crée un nouveau compte MySQL. Pour l'utiliser, vous devez avoir les droits de CREATE USER ou les droits d'INSERT dans la table de droits de la base mysql. Pour chaque compte, CREATE USER crée un nouvel enregistrement dans la table mysql.user, sans aucun droit. Une erreur survient si le compte existe déjà. Le compte peut recevoir un mot de passe avec la clause optionnelle IDENTIFIED BY. La valeur user et le mot de passe sont données de la même manière que dans la commande GRANT.

La commande CREATE USER a été ajoutée en MySQL 5.0.2.

13.5.1.2. Effacer des utilisateurs MySQL

DROP USER user_name

Cette commande a été ajoutée en MySQL 4.1.1.

Elle efface un utilisateur qui n'a aucun droits.

Pour effacer un utilisateur dans MySQL, vous devriez utiliser l'une des procédures suivantes, dans cet ordre :

  1. Vérifiez les droits de l'utilisateur avec la commande SHOW PRIVILEGES. See Section 13.5.3.13, « SHOW PRIVILEGES ».

  2. Effacez tous les droits de l'utilisateur avec la commande REVOKE. See Section 13.5.1.3, « Syntaxe de GRANT et REVOKE ».

  3. Effacez l'utilisateur avec DROP USER.

Si vous utilisez une vieille version de MySQL, vous devriez commencer par effacer les droits, puis l'utilisateur avec :

DELETE FROM mysql.user WHERE user='username' and host='hostname';
FLUSH PRIVILEGES;

13.5.1.3. Syntaxe de GRANT et REVOKE

GRANT priv_type [(column_list)] [, priv_type [(column_list)]] ...
    ON {tbl_name | * | *.* | db_name.*}
    TO user [IDENTIFIED BY [PASSWORD] 'password']
        [, user [IDENTIFIED BY [PASSWORD] 'password']] ...
    [REQUIRE
        NONE |
        [{SSL| X509}]
        [CIPHER cipher [AND]]
        [ISSUER issuer [AND]]
        [SUBJECT subject]]
    [WITH [GRANT OPTION | MAX_QUERIES_PER_HOUR count |
                          MAX_UPDATES_PER_HOUR count |
                          MAX_CONNECTIONS_PER_HOUR count]]
REVOKE priv_type [(column_list)] [, priv_type [(column_list)]] ...
    ON {tbl_name | * | *.* | db_name.*}
    FROM user [, user] ...

REVOKE ALL PRIVILEGES, GRANT OPTION FROM user [, user] ...

Les commandes GRANT et REVOKE permettent à l'administrateur système de créer et supprimer des comptes utilisateur et de leur donner ou retirer des droits. GRANT et REVOKE sont implémentées en MySQL 3.22.11 ou plus récent. Pour les anciennes versions de MySQL, ces commandes ne font rien.

Les informations sur les comptes MySQL sont stockés dans la base mysql. Cette base et son accès sont présentés en détails dans la section Chapitre 5, Administration du serveur.

Les droits sont donnés à 4 niveaux :

  • Niveau global

    Les droits globaux s'appliquent à toutes les bases de données d'un serveur. Ces droits sont stockés dans la table mysql.user. REVOKE ALL ON *.* retirera seulement les privilèges globaux.

  • Niveau base de données

    Les droits de niveau de base de données s'appliquent à toutes les tables d'une base de données. Ces droits sont stockés dans les tables mysql.db et mysql.host. REVOKE ALL ON db.* retirera seulement les privilèges de base de données.

  • Niveau table

    Les droits de table s'appliquent à toutes les colonnes d'une table. Ces droits sont stockés dans la table mysql.tables_priv. REVOKE ALL ON db.table retirera seulement les privilèges de table.

  • Niveau colonne

    Les droits de niveau de colonnes s'appliquent à des colonnes dans une table. Ces droits sont stockés dans la table mysql.columns_priv. Quand vous utilisez REVOKE vous devez spécifier les mêmes colonnes qui s'étaient vues accorder des privilèges.

Pour faciliter la suppression de tous les droits d'un utilisateur, MySQL 4.1.2 a ajouté la syntaxe suivante, qui efface tous les droits de base, table et colonne pour un utilisateur donné :

REVOKE ALL PRIVILEGES,GRANT FROM user_name [, user_name ...]

Avant MySQL 4.1.2, tous les droits ne peuvent pas être effacés d'un coup. Il faut deux commandes pour cela :

REVOKE ALL PRIVILEGES FROM user [, user] ...
REVOKE GRANT OPTION FROM user [, user] ...

Pour les commandes GRANT et REVOKE, la clause priv_type peut être spécifiée par les constantes suivantes :

DroitSignification
ALL [PRIVILEGES]Tous les droits sauf WITH GRANT OPTION.
ALTERAutorise l'utilisation de ALTER TABLE.
CREATEAutorise l'utilisation de CREATE TABLE.
CREATE TEMPORARY TABLESAutorise l'utilisation de CREATE TEMPORARY TABLE.
DELETEAutorise l'utilisation de DELETE.
DROPAutorise l'utilisation de DROP TABLE.
EXECUTEAutorise l'utilisateur à exécuter des procédures stockées (pour MySQL 5.0).
FILEAutorise l'utilisation de SELECT ... INTO OUTFILE et LOAD DATA INFILE.
INDEXAutorise l'utilisation de CREATE INDEX et DROP INDEX.
INSERTAutorise l'utilisation de INSERT.
LOCK TABLESAutorise l'utilisation de LOCK TABLES sur les tables pour lesquelles l'utilisateur a les droits de SELECT.
PROCESSAutorise l'utilisation de SHOW FULL PROCESSLIST.
REFERENCESRéservé pour le futur.
RELOADAutorise l'utilisation de FLUSH.
REPLICATION CLIENTDonne le droit à l'utilisateur de savoir où sont les maîtres et esclaves.
REPLICATION SLAVENécessaire pour les esclaves de réplication (pour lire les historiques binaires du maître).
SELECTAutorise l'utilisation de SELECT.
SHOW DATABASESSHOW DATABASES affiche toutes les bases de données.
SHUTDOWNAutorise l'utilisation de mysqladmin shutdown.
SUPERAutorise une connexion unique même si max_connections est atteint, et l'exécution des commandes CHANGE MASTER, KILL thread, mysqladmin debug, PURGE MASTER LOGS et SET GLOBAL.
UPDATEAutorise l'utilisation de UPDATE.
USAGESynonyme de ``pas de droits''.
GRANT OPTIONSynonyme pour WITH GRANT OPTION

USAGE peut être utilisé lorsque vous voulez créer un utilisateur sans aucun droit.

Les droits de CREATE TEMPORARY TABLES, EXECUTE, LOCK TABLES, REPLICATION ..., SHOW DATABASES et SUPER sont nouveaux en version 4.0.2. Pour utiliser ces droits après mise à jour en 4.0.2, vous devez exécuter le script mysql_fix_privilege_tables. See Section 2.6.7, « Mise à jour des tables de droits ».

Dans les anciennes versions de MySQL, le droit de PROCESS donnait les mêmes droits que le nouveau droit SUPER.

Vous pouvez donner des droits globaux en utilisant la syntaxe ON *.*. Vous pouvez donner des droits de base en utilisant la syntaxe ON nom_base.*. Si vous spécifiez ON * et que vous avez une base de données qui est déjà sélectionnée, vous allez donner des droits pour la base de données courante. Attention : si vous spécifiez ON * et que vous n'avez pas de base courante, vous allez affecter les droits au niveau du serveur !

Les droits EXECUTION, FILE, PROCESS, RELOAD, REPLICATION CLIENT, REPLICATION SLAVE, SHOW DATABASES, SHUTDOWN et SUPER sont des droits d'administration, qui ne peuvent être donnés que globalement (avec la syntaxe ON *.*).

Les autres droits peuvent être donnés globalement ou à des niveaux plus spécifiques.

Les seuls droits priv_type que vous pouvez donner au niveau d'une table sont SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, GRANT OPTION, INDEX et ALTER.

Les seuls droits priv_type que vous pouvez donner au niveau d'une colonne (avec la clause column_list) sont SELECT, INSERT et UPDATE.

GRANT ALL assigne des droits que vous possédez au niveau où vous le possédez. Par exemple, si vous utilisez GRANT ALL ON db_name.*, qui est un droit de niveau de base de données, aucun des droits globaux, comme FILE ne sera donné.

MySQL vous permet de donner des droits au niveau d'une base de données, même si la base de données n'existe pas, pour vous aider à préparer l'utilisation de la base de données. Actuellement, MySQL ne vous permet pas de créer des droits pour une table si la table n'existe pas.

MySQL ne supprime pas les droits lorsqu'un utilisateur efface une table ou une base.

Notez bien : les caractères joker ‘_’ et ‘%’ sont autorisés lors de la spécification de noms dans la commande GRANT. Cela signifie que si vous voulez utiliser par exemple le caractère littéral ‘_’ comme nom de base, vous devez le spécifier sous la forme ‘\_’ dans la commande GRANT, pour éviter à l'utilisateur d'accéder à d'autres bases, dont le nom pourrait correspondre au masque d'expression régulière ainsi créé. Utilisez plutôt GRANT ... ON `foo\_bar`.* TO ....

Afin de permettre l'identification des utilisateurs depuis des hôtes arbitraires, MySQL supporte la spécification du nom d'utilisateur nom_utilisateur sous la forme user@host. Si vous voulez spécifier un nom d'utilisateur user qui contient des caractères spéciaux tels que ‘-’, ou une chaîne d'hôte host qui contient des caractères joker (comme ‘%’), vous pouvez placer le nom de l'utilisateur ou de l'hôte entre guillemets (par exemple, 'test-utilisateur'@'test-nomdhote').

Vous pouvez spécifier des caractères jokers dans le nom d'hôte. Par exemple, user@'%.loc.gov' fait correspondre l'utilisateur user de n'importe quel hôte du domaine loc.gov, et user@'144.155.166.%' fait correspondre l'utilisateur user à n'importe quelle adresse de la classe C 144.155.166.

La forme simple de user est synonyme de user@"%".

MySQL ne supporte pas de caractères joker dans les noms d'utilisateur. Les utilisateurs anonymes sont définis par l'insertion de ligne avec User='' dans la table mysql.user, ou en créant un utilisateur avec un nom vide, grâce à la commande GRANT.

mysql> GRANT ALL ON test.* TO ''@'localhost' ...

Attention : si vous autorisez des utilisateurs anonymes à se connecter à votre serveur, vous devriez aussi donner ces droits à tous les utilisateurs locaux user@localhost car sinon, la ligne dans la table mysql.user sera utilisée lorsque l'utilisateur se connectera au serveur MySQL depuis la machine locale ! (Ce compte est créé durant l'installation de MySQL.)

Vous pouvez vérifier si cela s'applique à vous en exécutant la requête suivante :

mysql> SELECT Host,User FROM mysql.user WHERE User='';

Si vous voulez effacer les utilisateurs anonymes d'un serveur, utilisez ces commandes :

mysql> DELETE FROM mysql.user WHERE Host='localhost' AND User='';
mysql> FLUSH PRIVILEGES;

Actuellement, la commande GRANT supporte uniquement les noms d'hôte, colonne, table et bases de données d'au plus 60 caractères. Un nom d'utilisateur peut être d'au plus 16 caractères.

Les droits pour les tables et colonnes sont combinés par OU logique, avec les quatre niveaux de droits. Par exemple, si la table mysql.user spécifie qu'un utilisateur a un droit global de SELECT, ce droit ne pourra pas être annulé au niveau base, table ou colonne.

Les droits d'une colonne sont calculés comme ceci :

droit global
OR (droit de base de données ET droit d'hôte)
OR droit de table
OR droit de colonne

Dans la plupart des cas, vous donnez des droits à un utilisateur en utilisant un seul des niveaux de droits ci-dessus, ce qui fait que la vie n'est pas aussi compliquée. Le détails de la procédure de vérification des droits et disponible dans Section 5.5, « Règles de sécurité et droits d'accès au serveur MySQL ».

Si vous donnez des droits à une paire utilisateur/hôte qui n'existe pas dans la table mysql.user, une ligne sera créée et restera disponible jusqu'à son effacement avec la commande DELETE. En d'autre termes, GRANT crée une ligne dans la table user, mais REVOKE ne la supprime pas. Vous devez le faire explicitement avec la commande DELETE.

Avec MySQL version 3.22.12 ou plus récent, si un nouvel utilisateur est créé, ou si vous avez les droits de GRANT globaux, le mot de passe sera configuré avec le mot de passe spécifié avec la clause IDENTIFIED BY, si elle est fournie. Si l'utilisateur a déjà un mot de passe, il sera remplacé par ce nouveau.

Attention : si vous créez un nouvel utilisateur, mais ne spécifiez pas de clause IDENTIFIED BY, l'utilisateur n'aura pas de mot de passe. Ce n'est pas sécuritaire.

Les mots de passe peuvent aussi être modifiés avec la commande SET PASSWORD. See Section 13.5.1.5, « Syntaxe de SET PASSWORD ».

Si vous ne voulez pas transmettre le mot de passe en texte clair, vous pouvez immédiatement utiliser l'option PASSWORD suivi du mot de passe déjà chiffré avec la fonction PASSWORD() ou l'API C make_scrambled_password(char *to, const char *password).

Si vous donnez les droits de base, une ligne sera ajoutée dans la table mysql.db. Lorsque les droits sur cette base seront supprimés avec la commande REVOKE, cette ligne disparaîtra.

Si un utilisateur n'a pas de droit sur une table, elle ne sera pas affichée lorsqu'il demandera la liste des tables avec la commande SHOW TABLES. Si un utilisateur n'a pas de droit dans une base, le nom de la base ne sera pas affiché par SHOW DATABASES à moins que l'utilisateur n'ai un droit de SHOW DATABASES.

La clause WITH GRANT OPTION donne à l'utilisateur le droit de donner les droits qu'il possède à d'autres utilisateurs. La plus grande prudence est recommandée pour cette commande, car il permettra à terme à deux utilisateurs de combiner les droits dont ils disposent.

Vous ne pouvez pas donner un droit que vous ne possédez pas. le droit de GRANT OPTION ne vous donne le droit que de donner les droits que vous possédez.

Sachez que si vous donnez à quelqu'un le droit de GRANT OPTION, tous les droits que possède cet utilisateur seront distribuables. Supposez que vous donnez à un utilisateur le droit d'INSERT dans une base de données. Si vous donnez le droit de SELECT sur une base, et spécifiez l'option WITH GRANT OPTION, l'utilisateur peut distribuer non seulement son droit de SELECT, mais aussi son droit de INSERT. Si vous donnez ensuite le droit de UPDATE, il pourra alors distribuer INSERT, SELECT et UPDATE.

Il est recommandé de ne pas donner de droits de ALTER à un utilisateur normal. Si vous le faîtes, l'utilisateur pourra essayer de contourner le système de droits en renommant des tables.

MAX_QUERIES_PER_HOUR #, MAX_UPDATES_PER_HOUR # et MAX_CONNECTIONS_PER_HOUR # sont nouveaux en MySQL 4.0.2. Ces deux options limitent le nombre de requêtes et de modifications qu'un utilisateur peut réclamer dans une heure. Si # vaut 0 (valeur par défaut), alors cela signifie qu'il n'y a pas de limitations pour cet utilisateur. See Section 5.6.4, « Limiter les ressources utilisateurs ». Note: pour spécifier l'une de ces options pour un utilisateur existant sans ajouter d'autres privilèges additionnels, utilisez GRANT USAGE ... WITH MAX_....

MySQL peut vérifier les attributs X509 en plus des éléments d'identifications habituels, comme le nom d'utilisateur et le mot de passe. Pour spécifier des options SSL pour un compte MySQL, utilisez la clause REQUIRE de la commande GRANT. Pour des informations générales sur SSL et MySQL, voyez Section 5.6.7, « Utilisation des connexions sécurisées ».

Il y a différentes possibilités pour limiter le type de connexions d'un compte :

  • Si un compte ne doit pas utiliser SSL ou X509, les connexions sont autorisées si le mot de passe et le nom d'utilisateur sont valides. Cependant, les connexions non-chiffrées peuvent aussi être utilisées par le client, si le client dispose des bons certificats et clés.

  • L'option REQUIRE SSL limite le serveur aux connexions chiffrées avec SSL. Notez que cette option peut être omise s'il y a des lignes d'identifications qui autorisent les connexions non chiffrées.

    mysql> GRANT ALL PRIVILEGES ON test.* TO 'root'@'localhost'
        -> IDENTIFIED BY 'goodsecret' REQUIRE SSL;
    
  • REQUIRE X509 signifie que le client doit avoir un certificat valide, mais que le certificat exact, l'émetteur et le sujet n'ont pas d'importance. La seule obligation est qu'il faut pouvoir vérifier la signature auprès d'une des autorités de certification.

    mysql> GRANT ALL PRIVILEGES ON test.* TO 'root'@'localhost'
        -> IDENTIFIED BY 'goodsecret' REQUIRE X509;
    
  • REQUIRE ISSUER 'issuer' impose aux connexions l'utilisation d'un certificat X509 émis par l'autorité de certification 'issuer'. Si le client présente un certificat d'une autre autorité, le serveur rejette la connexion. L'utilisation des certificats X509 implique toujours un chiffrement, ce qui fait que l'option SSL est inutile.

    mysql> GRANT ALL PRIVILEGES ON test.* TO 'root'@'localhost'
        -> IDENTIFIED BY 'goodsecret'
        -> REQUIRE ISSUER '/C=FI/ST=Some-State/L=Helsinki/
           O=MySQL Finland AB/CN=Tonu Samuel/Email=tonu@example.com';
    

    Notez que la valeur de ISSUER doit être saisie comme une seule chaîne.

  • REQUIRE SUBJECT 'subject' impose à la connexion à la présentation d'un certificat X509 avec le sujet 'subject'. Si le client présente un certificat qui est valide, mais avec un autre sujet, le serveur rejette la connexion.

    mysql> GRANT ALL PRIVILEGES ON test.* TO 'root'@'localhost'
        -> IDENTIFIED BY 'goodsecret'
        -> REQUIRE SUBJECT '/C=EE/ST=Some-State/L=Tallinn/
           O=MySQL demo client certificate/
           CN=Tonu Samuel/Email=tonu@example.com';
    

    Notez que la valeur de SUBJECT doit être saisie comme une seule chaîne.

  • REQUIRE CIPHER 'cipher' est nécessaire pour s'assurer que des tailles de clé et chiffrements suffisantes sont utilisées. SSL peut être faible si de vieux algorithmes avec des clés courtes sont utilisées. En utilisant cette option, vous pouvez spécifier un chiffrement spécifique.

    mysql> GRANT ALL PRIVILEGES ON test.* TO 'root'@'localhost'
        -> IDENTIFIED BY 'goodsecret'
        -> REQUIRE CIPHER 'EDH-RSA-DES-CBC3-SHA';
    

Les clauses SUBJECT, ISSUER et CIPHER peuvent être combinées avec la clause REQUIRE comme ceci :

mysql> GRANT ALL PRIVILEGES ON test.* TO 'root'@'localhost'
    -> IDENTIFIED BY 'goodsecret'
    -> REQUIRE SUBJECT '/C=EE/ST=Some-State/L=Tallinn/
       O=MySQL demo client certificate/
       CN=Tonu Samuel/Email=tonu@example.com'
    -> AND ISSUER '/C=FI/ST=Some-State/L=Helsinki/
       O=MySQL Finland AB/CN=Tonu Samuel/Email=tonu@example.com'
    -> AND CIPHER 'EDH-RSA-DES-CBC3-SHA';

Notez que les valeurs de SUBJECT et ISSUER doivent être saisies comme une seule chaîne.

Depuis MySQL 4.0.4, le mot clé AND est optionnel entre les clauses de REQUIRE.

L'ordre des options est libre, mais les options doivent être spécifiées une seule fois.

Notez que si vous utilisez des droits de niveau table ou colonne même pour un utilisateur, le serveur vérifiera alors ces droits pour tous les utilisateurs, et cela ralentira MySQL un peu.

Lorsque mysqld démarre, tous les droits sont stockés en mémoire. Les droits de bases, tables et colonnes prennent aussitôt effet, et les droits des utilisateurs prendront effet dès leur prochaine configuration. Les modifications sur les tables de droits que vous effectuez avec les commandes GRANT et REVOKE sont prises en compte immédiatement par le serveur. Si vous modifiez manuellement les tables (avec INSERT, UPDATE, etc...), vous devez exécuter la commande FLUSH PRIVILEGES, ou la commande en ligne mysqladmin flush-privileges pour indiquer au serveur qu'il doit recharger les droits. See Section 5.5.7, « Quand les modifications de privilèges prennent-ils effets ? ».

Les différences notables entre l'ANSI SQL et MySQL pour la commande GRANT sont :

  • Les droits MySQL sont donnés pour une combinaison nom d'utilisateur + nom d'hôte, et non pas pour un nom d'hôte seulement.

  • L'ANSI SQL n'a pas de droits globaux ou de niveau base de données, et l'ANSI SQL ne supporte pas tous les types de droits que MySQL supporte. MySQL ne supporte pas le droit ANSI SQL de TRIGGER ou UNDER.

  • MySQL ne supporte pas les droits standard SQL TRIGGER et UNDER.

  • Les droits ANSI SQL sont structurés de manière hiérarchique. Si vous supprimez un utilisateur, tous les droits donnés à cet utilisateur seront supprimés. Avec MySQL, les droits ne sont pas automatiquement supprimés, et vous devez les supprimer manuellement, si besoin.

  • Avec MySQL, si vous avez le droit de INSERT sur uniquement quelques colonnes de la table, vous pourrez exécuter des insertions. Les colonnes pour lesquelles vous n'avez pas de droit prendront alors leur valeur par défaut. L'ANSI SQL vous impose d'avoir les droits d'INSERT sur toutes les colonnes.

  • Lorsque vous détruisez une table avec ANSI SQL, tous les droits liés à la table sont supprimés. Si vous supprimez un droit en ANSI SQL, tous les droits qui étaient basés sur ce droit sont supprimés. Avec MySQL, les droits peuvent être abandonnés explicitement avec la commande REVOKE, ou en manipulant les tables de droits de MySQL.

13.5.1.4. Syntaxe de RENAME USER

RENAME USER old_user TO new_user
    [, old_user TO new_user] ...

La commande RENAME USER renomme un compte MySQL existant. Pour l'utiliser, vous devez avoir les droits globaux de CREATE USER ou les droits de UPDATE dans la base mysql. Une erreur survient si l'ancien compte n'existe pas, ou que le nouveau compte existe déjà. Les valeurs old_user et new_user sont données de la même fa¸on que dans la commande GRANT.

La commande RENAME USER a été ajoutée en MySQL 5.0.2.

13.5.1.5. Syntaxe de SET PASSWORD

SET PASSWORD = PASSWORD('some password')
SET PASSWORD FOR user = PASSWORD('some password')

La commande SET PASSWORD assigne un mot de passe à un compte utilisateur existant.

La première syntaxe modifie le mot de passe de l'utilisateur courant. Tout client qui s'est connecté avec un compte non-anonyme peut changer le mot de passe pour ce compte.

La seconde syntaxe modifie le mot de passe pour un compte tiers, sur le serveur. Seuls les clients qui ont accès aux bases mysql peuvent faire cela. La valeur de user doit être donnée au format user_name@host_name, où user_name et host_name sont tels que listés dans les colonnes User et Host de la table mysql.user. Par exemple, si vous avez une ligne avec les champs User et Host qui valent 'bob' et '%.loc.gov', vous pouvez écrire la commande suivante :

mysql> SET PASSWORD FOR 'bob'@'%.loc.gov' = PASSWORD('newpass');

C'est l'équivalent de la commande suivante :

mysql> UPDATE mysql.user SET Password=PASSWORD('newpass')
    -> WHERE User='bob' AND Host='%.loc.gov';
mysql> FLUSH PRIVILEGES;

13.5.2. Commandes d'entretien des tables

13.5.2.1. Syntaxe de ANALYZE TABLE

ANALYZE TABLE nom_de_table[,nom_de_table...]

Cette commande analyse et stocke la clé de distribution de la table. Durant l'analyse, la table est verrouillée en lecture. Cette commande fonctionne avec les tables MyISAM et BDB.

C'est l'équivalent de la commande en ligne myisamchk -a.

MySQL utilise les clés de distribution pour décider dans quel ordre les tables doivent être rassemblées lors des jointures qui ne s'effectuent pas sur une constante.

La commande retourne une table avec les colonnes suivantes :

ColonneValeur
TableNom de la table
Op``analyze'' (toujours)
Msg_typeUn des status, error, info ou warning.
Msg_textLe message.

Vous pouvez vérifier la clé de distribution stockée avec la commande SHOW INDEX. See Section 13.5.3.6, « Syntaxe de SHOW DATABASES ».

Si la table n'a pas changé depuis la dernière commande ANALYZE TABLE, elle ne sera pas analysée à nouveau.

13.5.2.2. Syntaxe de BACKUP TABLE

BACKUP TABLE nom_de_table[,nom_de_table...] TO '/chemin/vers/le/dossier/de/sauvegardes'

Note : cette commande est obsolète. Nous travaillons à une solution de remplacement améliorée, qui proposera des sauvegardes à chaud. Durant ce temps, le script mysqlhotcopy peut être utilisé.

Cette commande copie le nombre minimal de fichiers de table dont en a besoin pour la restaurer vers le dossier de sauvegardes après avoir rafraîchit les changements dans le disque. Cela ne fonctionne actuellement que pour les tables au format MyISAM. Pour les tables MyISAM, elle ne copie que les fichiers .frm (définition) et .MYD (données), le fichier d'index .MYD pouvant, lui, être reconstruit à partir des deux autres.

Avant d'utiliser cette commande, merci de lire Section 5.7.1, « Sauvegardes de base de données ».

Pendant la sauvegarde, un verrou de lecture est posé sur chaque table, une par une, lors de leur copie. Si vous voulez sauvegarder une image instantanée de plusieurs table, vous devez d'abord exécuter un LOCK TABLES obtenant un verrou de lecture pour chaque table concernée.

La commande retourne une table avec les colonnes suivantes :

ColonneValeur
TableNom de la table
OpToujours ``backup''
Msg_typestatus, error, info ou encore warning.
Msg_textLe message.

Notez que BACKUP TABLE n'est disponible en MySQL que depuis la version 3.23.25.

13.5.2.3. Syntaxe de CHECK TABLE

CHECK TABLE tbl_name[,tbl_name...] [option [option...]]

option = QUICK | FAST | MEDIUM | EXTENDED | CHANGED

CHECK TABLE ne fonctionne qu'avec les tables MyISAM et InnoDB. Avec les tables MyISAM, c'est l'équivalent de la commande myisamchk -m table_name sur la table.

Par défaut, l'option MEDIUM est utilisée.

Cette commande vérifie l'intégrité des tables. Pour les tables MyISAM, des statistiques importantes sont mises à jour. La commande retourne les informations suivantes sur la table dans les colonnes suivantes :

ColonneValeur
TableNom de la table.
OpToujours ``check''.
Msg_typeUn des statut status, error, info ou warning.
Msg_textLe message.

Notez que vous pouvez obtenir de nombreuses lignes d'informations pour chaque table. La dernière ligne sera du type Msg_type status et doit être normalement OK. Si vous n'obtenez pas de statut OK ou Not checked, il vous faudra exécuter une réparation de la table. See Section 5.7.3, « Utilisation de myisamchk pour la maintenance des tables et leur recouvrement ». Not checked signifie que la table a indiqué qu'il n'y a pas de vérification à faire.

Les différents types de vérifications sont les suivants :

TypeSignification
QUICKN'analyse pas les lignes pour vérifier les liens erronés.
FASTNe vérifie que les tables qui n'ont pas été correctement fermées.
CHANGEDNe vérifie que les tables qui ont changées depuis la dernière vérification, ou bien qui n'ont pas été correctement fermées.
MEDIUMAnalyse les lignes pour s'assurer que les liens effacés sont corrects. Cette option calcule aussi la somme de contrôle des lignes, et la vérifie avec la somme de contrôle des clés.
EXTENDEDFait une vérification complète des liens pour chaque ligne. Cela vérifie que la table est totalement cohérente, mais cela peut prendre beaucoup de temps.

Pour les tables à format de dynamique de type MyISAM, une vérification de table sera toujours démarrée avec une option de niveau MEDIUM. Pour les tables à format de ligne statique, nous évitons les niveaux de QUICK et FAST car les lignes sont rarement corrompues.

Vous pouvez combiner les options de vérification comme ceci :

CHECK TABLE test_table FAST QUICK;

L'exemple ci-dessus va simplement faire une vérification de la table, pour s'assurer qu'elle a été correctement fermée.

Note : dans certains cas, CHECK TABLE va modifier la table! Cela arrive si la table a été marquée comme "corrupted" et "not closed properly" mais CHECK TABLE n'a trouvé aucun problème dans la table. Dans ce cas, CHECK TABLE va marquer la table comme correcte.

Si une table est corrompue, il est probable que les problèmes sont dans les fichiers d'index et non pas dans les données. Tous les types de vérifications présentés ci-dessus vérifient les index soigneusement, et ils devraient trouver la plupart des erreurs.

Si vous voulez simplement vérifier une table que vous supposez correcte, vous pouvez n'utiliser aucune option, ou l'option QUICK. Cette dernière peut aussi être utilisée si vous êtes pressé, et que vous pouvez prendre le risque minime que QUICK ne trouve pas d'erreur dans votre fichier. Dans la plupart des cas, MySQL doit trouver toutes les erreurs de données, pour un usage normal. Si cela arrive, alors la table est marquée comme 'corrupted', auquel cas, la table ne pourra pas être utilisée tant qu'elle n'a pas été réparée).

FAST et CHANGED sont surtout destinées à être utilisées depuis un script : par exemple, il peut être exécuté depuis une tâche cron, si vous voulez vérifier la table de temps en temps. Dans la plupart des cas, l'option FAST doit être préférée à CHANGED : le seul cas ou vous pourriez préférez CHANGED est lorsque vous soup¸onnez avoir trouvé un bogue dans les tables MyISAM.

EXTENDED ne doit être utilisé qu'après une vérification normale, et que vous obtenez toujours des erreurs étranges lorsque MySQL essaie de modifier une ligne ou trouve une ligne avec clé (ce qui est très rare, si une vérification a réussie).

Certains problèmes rapportés par la commande CHECK TABLE, ne peuvent être corrigés automatiquement :

  • Found row where the auto_increment column has the value 0.

    Cela signifie que vous avez dans votre table une ligne qui contient la valeur 0 alors qu'elle est de type AUTO_INCREMENT. (Il est possible de créer une ligne où la colonne AUTO_INCREMENT vaut 0 en spécifiant explicitement la valeur 0 dans la colonne avec la commande UPDATE.

    Ce n'est pas une erreur en soit, mais cela peut poser des problèmes si vous décidez de sauver cette table dans un fichier texte, et de la restaurer, ou encore d'appliquer la commande ALTER TABLE sur la table. Dans ce cas, la colonne AUTO_INCREMENT va changer automatiquement de valeur, en suivant les règles des colonnes de type AUTO_INCREMENT, qui vont causer un problème de clé doublon.

    Pour se débarasser de cette alerte, vous devez utiliser une commande UPDATE sur la table, pour mettre une valeur différente de 0 dans cette colonne.

13.5.2.4. Syntaxe de CHECKSUM TABLE

CHECKSUM TABLE tbl_name[,tbl_name ...] [ QUICK | EXTENDED ]

Calcule la somme de contrôle de la table.

Si QUICK est spécifié, la somme de contrôle instantanée est retournée, ou NULL si la table ne supporte pas les sommes de contrôle instantanées. See Section 13.2.5, « Syntaxe de CREATE TABLE ».

En mode EXTENDED, toute la table est lue, ligne par ligne, et la somme de contrôle est calculée. Cela peut être très lent pour les tables de grande taille.

Par défaut, sans QUICK ni EXTENDED, MySQL retourne la somme de contrôle si la table le supporte, et sinon, scanne la table.

Cette commande a été ajoutée en MySQL 4.1.1.

13.5.2.5. Syntaxe de OPTIMIZE TABLE

OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] ...

OPTIMIZE TABLE doit être utilisée si une grande partie de la base a été effacée, ou si vous avez fait de nombreuses modifications dans une table à format de ligne dynamique (des tables qui ont des colonnes de type VARCHAR, BLOB et TEXT). Les lignes effacées sont conservées dans une liste, et les prochaines opérations d'INSERT réutilisent les vieilles positions de lignes. Vous pouvez vous servir de la commande OPTIMIZE TABLE pour récupérer l'espace utilisé et défragmenter le fichier de données.

Dans la plupart des installations, vous n'avez pas à utiliser OPTIMIZE TABLE. Même si vous faites beaucoup de mises à jour sur des colonnes à taille dynamique, il n'est pas évident que vous ayez à passer cette commande plus d'une fois par semaine ou par mois, et uniquement sur quelques tables.

Pour le moment, OPTIMIZE TABLE fonctionne uniquement avec les tables de type MyISAM et BDB. Pour les tables BDB, OPTIMIZE TABLE est actuellement l'équivalent de ANALYZE TABLE. See Section 13.5.2.1, « Syntaxe de ANALYZE TABLE ».

Vous pouvez vous arranger pour que OPTIMIZE TABLE fonctionne sur d'autres types de tables, en démarrant mysqld avec --skip-new ou --safe-mode, mais dans ce cas, OPTIMIZE TABLE est simplement l'équivalent de ALTER TABLE.

OPTIMIZE TABLE fonctionne comme ceci :

  1. Si la table contient des lignes effacées ou des lignes fragmentées, la table est compactée.

  2. Si les pages d'index ne sont pas triées, OPTIMIZE TABLE les trie.

  3. Si les statistiques ne sont pas à jour (et que la table n'a pas pu effectuer de réparation en triant l'index), elles sont mises à jour.

Notez que la table est verrouillée durant la commande OPTIMIZE TABLE.

Avant MySQL 4.1.1, OPTIMIZE n'était pas reportée dans le log binaire. Depuis MySQL 4.1.1 elles le sont, à moins que l'attribut optionnel NO_WRITE_TO_BINLOG ou son alias LOCAL ne soit utilisé.

13.5.2.6. Syntaxe de REPAIR TABLE

REPAIR TABLE tbl_name[,tbl_name...] [QUICK] [EXTENDED] [USE_FRM]

REPAIR TABLE répare une table possiblement corrompue. Par défaut, elle a le même effet que myisamchk --recover tbl_name sur une table. REPAIR TABLE fonctionne unqiuement avec les tables MyISAM.

Normalement, vous n'avez pas à exécuter cette commande, mais si une catastrophe vous frappe, vous êtes presque assurés de retrouver vos données dans les tables MyISAM, avec la commande REPAIR TABLE. Si vos tables sont souvent corrompues, vous devrie toutefois rechercher la cause de ce problème! See Section A.4.2, « Que faire si MySQL plante constamment ? ». See Section 14.1.4, « Problèmes avec les tables MyISAM ».

REPAIR TABLE répare autant que possible les tables corrompues. La commande retourne la table suivante :

ColonneValeur
TableNom de la table
OpToujours repair
Msg_typeUn des statut status, error, info ou warning.
Msg_textLe message

La commande REPAIR TABLE pourrait afficher plusieurs messages pour chaque table. La dernière ligne doit être du format Msg_type status et doit être normalement OK. Si vous n'obtenez pas OK, vous devez essayer de réparer votre table avec la commande myisamchk -o, car REPAIR TABLE de supporte pas encore toutes les options de myisamchk. Dans un futur proche, nous allons rendre cette commande encore plus souple.

Si l'option QUICK est fournie, alors MySQL va essayer de ne réparer que le fichier d'index. Ce type de réparation est le même que myisamchk --recover --quick.

Si vous utilisez l'option EXTENDED, alors MySQL va essayer de créer l'index ligne par ligne, au lieu de créer un index à la fois, par tri. C'est une méthode qui peut s'avérer plus efficace que de trier sur des clés de taille fixe, si vous avez des clés CHAR longues qui se compressent bien. Ce type de réparation est l'équivalent de myisamchk --safe-recover.

Depuis MySQL 4.0.2, il existe le mode USE_FRM pour REPAIR. Utilisez-le si le fichier .MYI manque, ou si son entête est corrompu. Avec ce mode, MySQL va recréer le fichier .MYI, en utilisant les informations du fichier .frm. Ce type de réparation ne peut pas être fait avec myisamchk.

Attention : si le serveur s'arrête durant l'opération REPAIR TABLE, il est important d'exécuter à nouveau la commande REPAIR TABLE après le redémarrage (il est bon de faire une sauvegarde de toutes manières). Dans le pire scénario, vous pourriez vous retrouver avec un nouvel index sans relation avec les données, et la prochaine opération risque d'écraser le fichier de données. C'est peu probable, mais possible.

Avant MySQL 4.1.1, les commandes REPAIR TABLE n'étaient pas écrites dans le log binaire. Depuis MySQL 4.1.1, elles sont écrites dans le log binaire à moins que la clause NO_WRITE_TO_BINLOG ne soit utilisée (aussi connue sous le nom de LOCAL).

13.5.2.7. Syntaxe de RESTORE TABLE

RESTORE TABLE nom_de_table[,nom_de_table...] FROM '/chemin/vers/le/dossier/de/sauvegardes'

Restaure la ou les tables à partir d'une sauvegarde effectuée avec BACKUP TABLE. Les tables existantes ne seront pas écrasées et dans ce cas là, vous obtiendrez une erreur. La restauration prendra plus de temps que la sauvegarde à cause de la reconstruction du fichier d'index. Plus vous avez de clefs, plus la restauration sera longue. Tout comme BACKUP TABLE, RESTORE TABLE fonctionne seulement avec les tables MyISAM.

La sauvegarde de chaque table est constituée du fichier de format .frm et du fichier de données .MYD. L'opération de restauration restaure ces fichiers, puis les utilise pour reconstruire le fichier d'index .MYI. La restauration prend plus de temps que la sauvegarde, car il faut reconstituer l'index. Plus la table a d'index, plus cela prendra de temps.

Cette commande retourne un tableau avec les colonnes suivantes :

ColonneValeur
TableNom de la table
OpToujours ``restore''
Msg_typestatus, error, info ou encore warning.
Msg_textLe message.

13.5.2.8. Syntaxe de SET

SET variable_assignment [, variable_assignment] ...

variable_assignment:
      user_var_name = expr
    | [GLOBAL | SESSION] system_var_name = expr
    | @@[global. | session.]system_var_name = expr

SET permet de configurer plusieurs options qui affectent le comportement de votre serveur ou de votre client.

En MySQL 4.0.3, nous avons ajouté les options GLOBAL et SESSION et permis la modification des variables systèmes les plus importantes dynamiquement, durant l'exécution du serveur. Le système de variables que vous pouvez utiliser est décrit dans Section 5.2.3.1, « Variables système dynamiques ».

Dans les anciennes versions de MySQL, nous avions autorisé l'utilisation de la syntaxe SET OPTION, mais elle est maintenant abandonnée. Omettez simplement le mot OPTION.

Les exemples suivants montrent les différentes syntaxes qu'on peut utiliser pour configurer des variables.

Une variable utilisateur s'écrit sous la forme @var_name et peut être configurée comme ceci :

SET @var_name = expr;

Plus d'informations sur les variables utilisateurs sont données dans Section 9.3, « Variables utilisateur ».

Les variables système peuvent être identifiées dans une commande SET sous la forme var_name. Le nom peut être optionnellement précédé par GLOBAL ou @@global. pour indiquer que cette variable est globale, ou par SESSION, @@session., ou @@ pour indiquer que cette variable est une variable de session. LOCAL et @@local. son synonymes de SESSION et @@session.. Si aucune option n'est présente, SET spécifie une variable de session.

La syntaxe @@var_name pour les variables système est supportée pour rendre la syntaxe MySQL compatible avec les autres bases.

Si vous configurez plusieurs variables sur une seule ligne de commande, le dernier mode GLOBAL | SESSION utilisé est pris en compte.

SET sort_buffer_size=10000;
SET @@local.sort_buffer_size=10000;
SET GLOBAL sort_buffer_size=1000000, SESSION sort_buffer_size=1000000;
SET @@sort_buffer_size=1000000;
SET @@global.sort_buffer_size=1000000, @@local.sort_buffer_size=1000000;

Si vous utilisez SESSION (par défaut) l'option que vous configurez garde son effet jusqu'à ce que la session courante se termine, ou que vous modifiez à nouveau cette option. Si vous utilisez GLOBAL, qui requière le privilège SUPER, l'option est gardée en mémoire et utilisée pour les nouvelles connexion jusqu'au redémarrage du serveur. Si vous voulez qu'un changement reste permanent, vous devez l'effectuer dans l'un des fichiers d'options de MySQL. See Section 4.3.2, « Fichier d'options my.cnf ».

Pour éviter un mauvais usage, MySQL donnera une erreur si vous utilisez SET GLOBAL avec une variable qui ne peut être inutilisée que par SET SESSION ou si vous n'utilisez pas SET GLOBAL avec une variable globale.

Si vous voulez configurer une variable SESSION à une valeur GLOBAL ou une valeur GLOBAL à la valeur par défaut de MySQL, vous pouvez la configurer à DEFAULT.

SET max_join_size=DEFAULT;
SET @@session.max_join_size=@@global.max_join_size;

Vous pouvez obtenir une liste de la plupart des variables avec SHOW VARIABLES. See Section 13.5.3.18, « Syntaxe de SHOW VARIABLES ». Vous pouvez obtenir la valeur d'une variable spécifique avec la syntaxe @@[global.|local.]nom_variable :

SHOW VARIABLES LIKE 'max_join_size';
SHOW GLOBAL VARIABLES LIKE 'max_join_size';

Vous pouvez aussi obtenir une valeur spécifique d'une variable en utilisant la syntaxe @@[global.|local.]var_name avec SELECT :

SELECT @@max_join_size, @@global.max_join_size;

Lorsque vous lisez la valeur d'une variable avec la syntaxe SELECT @@var_name (c'est à dire, sans spécifier global., session. ou local.), MySQL retourne la valeur de SESSION si elle existe, et la valeur GLOBAL sinon.

Vous trouverez ici une description des variables qui utilisent une syntaxe non-standard de SET. Les définitions des autres variables peuvent être trouvées dans la section des Section 5.2.3, « Variables serveur système », avec les options de démarrage ou dans la description de SHOW VARIABLES.

Même si ces variables ne sont pas affichées par SHOW VARIABLES, vous pouvez obtenir leur valeur avec la commande SELECT (à l'exception de CHARACTER SET). Par exemple :

mysql> SELECT @@AUTOCOMMIT;
+--------------+
| @@autocommit |
+--------------+
|            1 |
+--------------+
  • AUTOCOMMIT= {0 | 1}

    Si définie à 1 tous les changements dans une table se feront en une seule fois. Pour démarrer une transaction multi-commandes, vous devez utiliser la commande BEGIN. See Section 13.4.1, « Syntaxes de START TRANSACTION, COMMIT et ROLLBACK ». Si définie à 0 vous devez utiliser COMMIT / ROLLBACK pour accepter/annuler cette transaction. Notez que quand vous passez du mode non AUTOCOMMIT vers le mode AUTOCOMMIT, MySQL fera un COMMIT automatique sur toutes les transactions en cours.

  • BIG_TABLES = {0 | 1}

    Si définie à 1, toutes les tables temporaires sont stockées sur le disque plutôt qu'en mémoire. Cela sera un peu plus lent, mais vous n'obtiendrez jamais l'erreur The table nom_de_table is full pour les grands SELECT qui requièrent une table temporaire. La valeur par défaut pour une nouvelle connexion est 0 (qui est d'utiliser la mémoire pour les tables temporaires). Cette option se nommait avant SQL_BIG_TABLES.

  • CHARACTER SET {charset_name | DEFAULT}

    Cela change le jeu de caractère dans toutes les chaînes du et vers le client avec le jeu donné. Jusqu'à maintenant, la seule option pour nom_jeu_de_caractères est cp1251_koi8, mais vous pouvez facilement ajouter d'autres possibilités en éditant le fichier sql/convert.cc dans la distribution des sources MySQL. Le jeu de caractères par défaut peut être restauré en utilisant la valeur DEFAULT de nom_jeu_de_caractères DEFAULT.

    Notez que la syntaxe pour configurer l'option CHARACTER SET diffère de la syntaxe pour configurer les autres options.

  • FOREIGN_KEY_CHECKS = {0 | 1}

    Si cette option vaut 1 (par défaut), les contraintes de clé étrangères des tables InnoDB sont vérifiées. Si cette option vaut 0, elles sont ignorées. Désactiver les clés étrangères peut être pratique pour recharger des tables InnoDB dans un ordre différent que celui qu'impose les relations de contraintes. Cette variable a été ajoutée en MySQL 3.23.52. See Section 15.7.4, « Contraintes de clés étrangères FOREIGN KEY ».

  • IDENTITY = valeur

    Cette variable est un synonyme de la variable LAST_INSERT_ID. Elle existe pour des raisons de compatibilité avec les autres bases. Depuis MySQL 3.23.25, vous pouvez lire sa valeur avec SELECT @@IDENTITY. Depuis MySQL 4.0.3, vous pouvez aussi modifier cette valeur avec SET IDENTITY.

  • INSERT_ID = valeur

    Configure la valeur à utiliser par l'appel suivant à la commande INSERT ou ALTER TABLE lors de l'insertion d'une valeur AUTO_INCREMENT. Cela est souvent utilisé par le log des modifications.

  • LAST_INSERT_ID = valeur

    Configure la valeur qui doit être retournée par LAST_INSERT_ID(). C'est enregistré dans le log de mises à jour quand vous utilisez LAST_INSERT_ID() dans une commande qui met à jour une table.

  • NAMES {'charset_name' | DEFAULT}

    SET NAMES spécifie les valeurs des trois variables systèmes de session character_set_client, character_set_connection et character_set_results avec le jeu de caractères donné.

    La valeur par défaut de ces variables peut être rappelée avec DEFAULT.

    Notez que la syntaxe de SET NAMES diffère en cela des autres options. Cette commande est disponible depuis MySQL 4.1.0.

  • SQL_AUTO_IS_NULL = {0 | 1}

    Si définie à 1 (par défaut) alors on peut trouver la dernière ligne insérée dans une table avec une colonne AUTO_INCREMENT avec la construction suivante :

    WHERE auto_increment_column IS NULL
    

    Ceci est utilisé par des programmes ODBC tel que Access. SQL_AUTO_IS_NULL a été ajouté en MySQL 3.23.52.

  • SQL_BIG_SELECTS = {0 | 1}

    Si configuré à 0, MySQL interrompra les requêtes SELECT qui prendront probablement trop de temps. C'est utile lorsqu'une clause WHERE déconseillée a été utilisée. Une grosse requête est définie comme étant un SELECT qui devra probablement étudier plus de max_join_size lignes. La valeur par défaut d'une nouvelle connexion est 1 (qui permet toutes les requêtes SELECT).

  • SQL_BUFFER_RESULT = {0 | 1}

    SQL_BUFFER_RESULT forcera les résultats des requêtes SELECT à être placés dans une table temporaire. Cela aidera MySQL à libérer les verrous sur table plus tôt et améliorera les cas où le jeu de résultats de la requête prend trop de temps à être envoyée au client.

  • SQL_LOG_BIN = {0 | 1}

    Si cette option vaut 0, aucun log n'est fait dans le log binaire du client, si le client a les droits de SUPER.

  • SQL_LOG_OFF = {0 | 1}

    Si cette option vaut 1, aucun log n'est fait dans le log standard du client, si le client a les droits de SUPER.

  • SQL_LOG_UPDATE = {0 | 1}

    Si définie à 0, aucune trace des requêtes ne sera gardée dans le log des mises à jour pour le client, si le client à le privilège SUPER. Cette variable est abandonnée depuis la version 5.0.0 et est remplacée par SQL_LOG_BIN (see Section C.1.7, « Changements de la version 5.0.0 (22 décembre 2003 : Alpha) »).

  • SQL_QUOTE_SHOW_CREATE = {0 | 1}

    Si vous le configurez à 1, SHOW CREATE TABLE protégera les noms de tables et de colonnes. Ceci est activé par défaut, pour que la réplication des tables avec des noms à risques fonctionne. Section 13.5.3.5, « Syntaxe de SHOW CREATE TABLE ».

  • SQL_SAFE_UPDATES = {0 | 1}

    Si définit à 1, MySQL annulera si un UPDATE ou un DELETE est exécuté alors qu'il n'utilise pas de clef ou de LIMIT dans la clause WHERE. Cela permet de bloquer les requêtes erronées crées à la main.

  • SQL_SELECT_LIMIT = valeur | DEFAULT

    Le nombre maximal des enregistrements que doivent retourner les requêtes SELECT. Si un SELECT possède une clause LIMIT, celle-ci est utilisée. La valeur par défaut pour une nouvelle connexion est ``illimitée.'' Si vous avez changé la limite, la valeur par défaut peut être retrouvée en utilisant la valeur DEFAULT avec SQL_SELECT_LIMIT.

  • SQL_WARNINGS = {0 | 1}

    Cette variable contrôle le fait que les insertion mono-ligne INSERT produisent une chaîne d'information si une alerte survient. La valeur par défaut est 0. Donnez la valeur de 1 pour avoir un message d'information. Cette variable a été ajoutée en MySQL 3.22.11.

  • TIMESTAMP = valeur_timestamp | DEFAULT

    Configure le temps pour ce client. C'est utilisé pour obtenir le timestamp d'origine si vous utilisez le log de mises à jour pour restaurer des lignes. valeur_timestamp doit être un timestamp Unix, et non un timestamp MySQL.

  • UNIQUE_CHECKS = {0 | 1}

    Si cette option vaut 1 (par défaut), les tests d'unicité sur les index secondaires des tables InnoDB sont effectués. Si l'option vaut 0, aucun test d'unicité n'est fait. Cette variable a été ajouté en MySQL 3.23.52. See Section 15.7.4, « Contraintes de clés étrangères FOREIGN KEY ».

13.5.3. Syntaxe de SHOW

SET vous permet de modifier des variables et options.

SHOW a de très nombreuses formes, pour donner des informations sur les bases, tables, colonnes ou le serveur. Cette section les décrit.

SHOW [FULL] COLUMNS FROM tbl_name [FROM db_name] [LIKE 'pattern']
SHOW CREATE DATABASE db_name
SHOW CREATE TABLE tbl_name
SHOW DATABASES [LIKE 'pattern']
SHOW [STORAGE] ENGINES
SHOW ERRORS [LIMIT [offset,] row_count]
SHOW GRANTS FOR user
SHOW INDEX FROM tbl_name [FROM db_name]
SHOW INNODB STATUS
SHOW [BDB] LOGS
SHOW PRIVILEGES
SHOW [FULL] PROCESSLIST
SHOW STATUS [LIKE 'pattern']
SHOW TABLE STATUS [FROM db_name] [LIKE 'pattern']
SHOW [OPEN] TABLES [FROM db_name] [LIKE 'pattern']
SHOW [GLOBAL | SESSION] VARIABLES [LIKE 'pattern']
SHOW WARNINGS [LIMIT [offset,] row_count]

Si la syntaxe d'une commande SHOW inclut la clause LIKE 'pattern', 'pattern' est une expression régulière qui peut contenir les jokers ‘%’ et ‘_’. Ce expression est utile pour restreindre la commande à une partie des valeurs normalement retournées.

Notez qu'il y a d'autres formes pour ces commandes, décrites à d'autres endroits du manuel :

13.5.3.1. Commande SHOW CHARACTER SET

La commande SHOW CHARACTER SET montre tous les jeux de caractères disponibles. Il faut une clause facultative LIKE pour limiter les jeux de caractères à afficher.

Par exemple :

mysql> SHOW CHARACTER SET LIKE 'latin%';
+---------+-----------------------------+-------------------+--------+
| Charset | Description                 | Default collation | Maxlen |
+---------+-----------------------------+-------------------+--------+
| latin1  | ISO 8859-1 West European    | latin1_swedish_ci |      1 |
| latin2  | ISO 8859-2 Central European | latin2_general_ci |      1 |
| latin5  | ISO 8859-9 Turkish          | latin5_turkish_ci |      1 |
| latin7  | ISO 8859-13 Baltic          | latin7_general_ci |      1 |
+---------+-----------------------------+-------------------+--------+
4 rows in set (0.00 sec)

Remarques sur la liste précédente :

  • La colonne Maxlen affiche le nombre maximum d'octets utilisés pour stocker un caractère.

13.5.3.2. Syntaxe de SHOW COLLATION

SHOW COLLATION [LIKE 'pattern']

Le résultat de SHOW COLLATION inclut tous les jeux de caractères disponibles. Vous pouvez utiliser optionnellement la clause LIKE pour limiter le nombre de réponses.

mysql> SHOW COLLATION LIKE 'latin1%';
+-------------------+---------+----+---------+----------+---------+
| Collation         | Charset | Id | Default | Compiled | Sortlen |
+-------------------+---------+----+---------+----------+---------+
| latin1_german1_ci | latin1  |  5 |         |          |       0 |
| latin1_swedish_ci | latin1  |  8 | Yes     | Yes      |       0 |
| latin1_danish_ci  | latin1  | 15 |         |          |       0 |
| latin1_german2_ci | latin1  | 31 |         | Yes      |       2 |
| latin1_bin        | latin1  | 47 |         | Yes      |       0 |
| latin1_general_ci | latin1  | 48 |         |          |       0 |
| latin1_general_cs | latin1  | 49 |         |          |       0 |
| latin1_spanish_ci | latin1  | 94 |         |          |       0 |
+-------------------+---------+----+---------+----------+---------+
7 rows in set (0.00 sec)

La colonne Default indique si une collation est la collation par défaut pour son jeu de caractères. Compiled indique si le jeu de caractères est compilé dans le serveur ou non. Sortlen est en relation avec la quantité de mémoire nécessaire pour trier des chaînes exprimées dans le jeu de caractéres.

13.5.3.3. Syntaxe de SHOW COLUMNS

SHOW [FULL] COLUMNS FROM tbl_name [FROM db_name] [LIKE 'pattern']

SHOW COLUMNS liste les colonnes de la table. Si les types de colonnes sont différents de ceux que vous avez utilisé avec la commande CREATE TABLE, c'est que MySQL a modifié silencieusement le type lors de la création. Les conditions de cette modification sont décrites dans Section 13.2.5.1, « Modification automatique du type de colonnes ».

Le mot clé FULL peut être utilisé depuis MySQL 3.23.32. Il fait afficher les droits dont vous disposez pour chaque colonne. Depuis MySQL 4.1, FULL affiche aussi les commentaires par colonne.

Vous pouvez utiliser db_name.tbl_name comme syntaxe alternative à tbl_name FROM db_name. Ces deux commandes sont équivalentes :

mysql> SHOW COLUMNS FROM mytable FROM mydb;
mysql> SHOW COLUMNS FROM mydb.mytable;

SHOW FIELDS est un synonyme de SHOW COLUMNS. Vous pouvez aussi lister les colonnes d'une table avec la commande mysqlshow db_name tbl_name.

La commande DESCRIBE fournit une information similaire à SHOW COLUMNS. See Section 13.3.1, « Syntaxe de DESCRIBE (obtenir des informations sur les colonnes) ».

13.5.3.4. Syntaxe de SHOW CREATE DATABASE

SHOW CREATE DATABASE db_name

La requête suivante montre une commande CREATE DATABASE qui va créer une base de donnée. Commande ajoutée en MySQL 4.1.

mysql> SHOW CREATE DATABASE test\G
*************************** 1. row ***************************
       Database: test
Create Database: CREATE DATABASE `test`
                 /*!40100 DEFAULT CHARACTER SET latin1 */

13.5.3.5. Syntaxe de SHOW CREATE TABLE

SHOW CREATE TABLE tbl_name

Affiche la commande CREATE TABLE nécessaire pour créer une table donnée.

mysql> SHOW CREATE TABLE t\G
*************************** 1. row ***************************
       Table: t
Create Table: CREATE TABLE t (
  id int(11) default NULL auto_increment,
  s char(60) default NULL,
  PRIMARY KEY (id)
) TYPE=MyISAM

SHOW CREATE TABLE va protéger le nom de la table et des colonnes selon l'option SQL_QUOTE_SHOW_CREATE. Section 13.5.2.8, « Syntaxe de SET ».

13.5.3.6. Syntaxe de SHOW DATABASES

SHOW {DATABASES | SCHEMAS} [LIKE 'pattern']

SHOW DATABASES liste les bases de données disponible sur le serveur MySQL. Vous pouvez aussi obtenir cette liste avec l'utilitaire mysqlshow. Depuis MySQL 4.0.2, vous ne verrez que les bases pour lesquelles vous avez des droits, à moins que vous n'ayez le droit de SHOW DATABASES.

Si le serveur a été lancé avec l'option --skip-show-database, vous ne pouvez pas utiliser cette commande à moins que vous n'ayez le droit de SHOW DATABASES.

SHOW SCHEMAS est disponible depuis MySQL 5.0.2

13.5.3.7. Syntaxe SHOW ENGINES

SHOW [STORAGE] ENGINES

SHOW ENGINES affiche les informations sur les moteurs de stockage du serveur. C'est particulièrement utile pour connaître les moteurs supportés par votre serveur, ou le moteur par défaut. Cette commande a été ajoutée en MySQL 4.1.2. SHOW TABLE TYPES est un synonyme, mais est abandonnée.

mysql> SHOW ENGINES\G
*************************** 1. row ***************************
   Type: MyISAM
Support: DEFAULT
Comment: Default type from 3.23 with great performance
*************************** 2. row ***************************
   Type: HEAP
Support: YES
Comment: Hash based, stored in memory, useful for temporary tables
*************************** 3. row ***************************
   Type: MEMORY
Support: YES
Comment: Alias for HEAP
*************************** 4. row ***************************
   Type: MERGE
Support: YES
Comment: Collection of identical MyISAM tables
*************************** 5. row ***************************
   Type: MRG_MYISAM
Support: YES
Comment: Alias for MERGE
*************************** 6. row ***************************
   Type: ISAM
Support: NO
Comment: Obsolete table type; Is replaced by MyISAM
*************************** 7. row ***************************
   Type: MRG_ISAM
Support: NO
Comment: Obsolete table type; Is replaced by MRG_MYISAM
*************************** 8. row ***************************
   Type: InnoDB
Support: YES
Comment: Supports transactions, row-level locking and foreign keys
*************************** 9. row ***************************
   Type: INNOBASE
Support: YES
Comment: Alias for INNODB
*************************** 10. row ***************************
   Type: BDB
Support: YES
Comment: Supports transactions and page-level locking
*************************** 11. row ***************************
   Type: BERKELEYDB
Support: YES
Comment: Alias for BDB

La valeur Support indique que le moteur est supporté, et si le moteur est le moteur par défaut. Par exemple, si le serveur est lancé avec l'option --default-table-type=InnoDB alors la valeur de la colonne Support de la ligne InnoDB contiendra DEFAULT.

13.5.3.8. Syntaxe de SHOW ERRORS

SHOW ERRORS [LIMIT [offset,] row_count]
SHOW COUNT(*) ERRORS

Cette commande est similaire à SHOW WARNINGS, hormis le fait qu'au lieu d'afficher les erreurs, alertes et notes, elle n'affiche que les erreurs. SHOW ERRORS est disponible depuis MySQL 4.1.0.

La clause LIMIT a la même syntaxe que celle de la commande SELECT. See Section 13.1.7, « Syntaxe de SELECT ».

La commande SHOW COUNT(*) ERRORS affiche le nombre d'erreurs. Vous pouvez aussi connaître ce nombre en lisant la variable error_count :

SHOW COUNT(*) ERRORS;
SELECT @@error_count;

Pour plus d'informations, voyez Section 13.5.3.19, « SHOW WARNINGS | ERRORS ».

13.5.3.9. SHOW GRANTS

SHOW GRANTS FOR user

SHOW GRANTS FOR user affiche la commande nécessaire pour donner les même droits qu'un utilisateur existant.

mysql> SHOW GRANTS FOR 'root'@'localhost';
+---------------------------------------------------------------------+
| Grants for root@localhost                                           |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
+---------------------------------------------------------------------+

Depuis MySQL 4.1.2, pour lister les droits de la session courante, vous pouvez connaître le nom d'utilisateur de la session avec ces commandes :

SHOW GRANTS;
SHOW GRANTS FOR CURRENT_USER;
SHOW GRANTS FOR CURRENT_USER();

Avant MySQL 4.1.2, vous pouviez connaître le nom d'identification de l'utilisation avec la fonction CURRENT_USER() (nouveau en MySQL 4.0.6). Puis, utilisez cette valeur avec la commande SHOW GRANTS. See Section 12.8.3, « Fonctions d'informations ».

SHOW GRANTS est disponible depuis MySQL 3.23.4.

13.5.3.10. Syntaxe de SHOW INDEX

SHOW INDEX FROM tbl_name [FROM db_name]

SHOW INDEX retourne les informations sur les index de la table, dans un format proche de celui de SQLStatistics en ODBC.

SHOW INDEX retourne les champs suivants :

  • Table

    Le nom de la table.

  • Non_unique

    0 si l'index ne peut pas contenir de doublons, et 1 s'il le peut.

  • Key_name

    Le nom de l'index.

  • Seq_in_index

    Le numéro de la colonne dans l'index, en commen¸ant à 1.

  • Column_name

    Le nom de la colonne.

  • Collation

    Comment la colonne est triée dans l'index. Avec MySQL, les valeurs peuvent être ‘A’ (Ascendant) ou NULL (non trié).

  • Cardinality

    Le nombre de valeurs uniques dans l'index. C'est une valeur qui est mise à jour avec la commande ANALYZE TABLE ou myisamchk -a. Cardinality est compté en se basant sur des statistiques entières : il n'est pas toujours exacte pour les petites tables.

  • Sub_part

    Le nombre de caractères indexé si la colonne n'est que partiellement indexée. NULL si la colonne entière est indexée.

  • Packed

    Indique comment la clé est compactée. NULL si elle ne l'est pas.

  • Null

    Contient YES si la colonne contient NULL, '' sinon.

  • Index_type

    La méthode d'indexation utilisée (BTREE, FULLTEXT, HASH, RTREE).

  • Comment

    Différentes remarques. Avant MySQL 4.0.2 lorsque la colonne Index_type a été ajoutée, Comment indiquait si un index était FULLTEXT.

Les colonnes Packed et Comment ont été ajoutée en MySQL 3.23.0. Les colonnes Null et Index_type ont été ajoutées en MySQL 4.0.2.

Vous pouvez utiliser la syntaxe db_name.tbl_name comme alternative à tbl_name FROM db_name. Ces deux commandes sont équivalentes :

mysql> SHOW INDEX FROM mytable FROM mydb;
mysql> SHOW INDEX FROM mydb.mytable;

SHOW KEYS est un synonyme SHOW INDEX. Vous pouvez aussi lister les index d'une table avec la commande en ligne mysqlshow -k db_name tbl_name.

13.5.3.11. Syntaxe de SHOW INNODB STATUS

SHOW INNODB STATUS

Cette commente donne des informations exhaustives sur le moteur de stockage InnoDB.

13.5.3.12. Syntaxe de SHOW LOGS

SHOW [BDB] LOGS

La commande SHOW LOGS affiche les informations d'état de vos fichiers de logs. Actuellement, elle n'affiche que les informations pour les fichiers de log des tables Berkeley DB.

  • File affiche le chemin complet jusqu'au fichier de log.

  • Type affiche le type de fichier de log (BDB pour les tables de types Berkeley DB)

  • Status affiche le status du fichier de log (FREE si le fichier peut être supprimé, ou IN USE si le fichier est utilisé par une transaction en cours)

13.5.3.13. SHOW PRIVILEGES

SHOW PRIVILEGES

Cette commande est implémentée en MySQL 4.1.0.

SHOW PRIVILEGES affiche la liste des droits que le serveur MySQL supporte.

mysql> show privileges;
+------------+--------------------------+-------------------------------------------------------+
| Privilege  | Context                  | Comment                                               |
+------------+--------------------------+-------------------------------------------------------+
| Select     | Tables                   | Lire des lignes d'une table                           |
| Insert     | Tables                   | Insérer des lignes dans une table                     |
| Update     | Tables                   | Modifier les lignes existantes                        |
| Delete     | Tables                   | Effacer des lignes existantes                         |
| Index      | Tables                   | Créer ou effacer des indexs                           |
| Alter      | Tables                   | Modifier la structure d'une table                     |
| Create     | Databases,Tables,Indexes | Créer une nouvelle base ou table                      |
| Drop       | Databases,Tables         | Effacer une base ou table                             |
| Grant      | Databases,Tables         | Donner à d'autres les droits courants                 |
| References | Databases,Tables         | Avoir des références sur les tables                   |
| Reload     | Server Admin             | Rafraîchir les droits, tables et logs                 |
| Shutdown   | Server Admin             | Eteindre le serveurver                                |
| Process    | Server Admin             | Voir la version texte des requêtes en cours           |
| File       | File access on server    | Lire et écrire des fichiers sur le serveur            |
+------------+--------------------------+-------------------------------------------------------+
14 rows in set (0.00 sec)

13.5.3.14. Syntaxe de SHOW PROCESSLIST

SHOW [FULL] PROCESSLIST

SHOW [FULL] PROCESSLIST affiche la liste de processus qui sont en cours d'exécution. Vous pouvez aussi obtenir ces informations avec la commande en ligne mysqladmin processlist. Si vous avez les droits de SUPER, vous pourrez aussi voir les autres threads. Sinon, vous ne pourrez voir que les votre. See Section 13.5.4.3, « Syntaxe de KILL ». Si vous n'utilisez pas l'option FULL, seuls les 100 premiers caractères de chaque requête seront affichés.

Cette commande est très pratique si vous obtenez trop d'erreurs ‘too many connections’ et que vous voulez savoir ce qui se passe. MySQL réserve une connexion supplémentaire pour un client ayant les droits de SUPER, de fa¸on à ce qu'il y ait toujours la possibilité de se connecter et de vérifier le système (en supposant que vous ne donnez pas ce droit à tous vos utilisateurs).

Certains états sont souvent disponible dans le résultat de mysqladmin processlist

  • Checking table

    Le thread fait une vérification (automatique) de la table.

  • Closing tables

    Le thread est en train d'écrire les données modifiées sur le disque, et il va fermer les tables. Cela doit être une opération très rapide. Si ce n'est pas le cas, vous devriez vérifier si vous n'avez pas un disque plein, ou que le disque est sous haute charge.

  • Connect Out

    Connexion d'un esclave sur le maître.

  • Copying to tmp table on disk

    Le résultat temporaire était plus grand que tmp_table_size et le thread passe d'une table en mémoire à une table sur disque.

  • Creating tmp table

    Le thread est en train de créer une table temporaire pour contenir le résultat d'une requête.

  • deleting from main table

    Lors de l'exécution de la première partie d'une requête d'effacement multi-table, et que MySQL n'a commencé à effacer que dans la première table.

  • deleting from reference tables

    Lors de l'exécution de la deuxième partie d'une requête d'effacement multi-table, et que MySQL a commencé à effacer dans les autres tables.

  • Flushing tables

    Le thread exécute la commande FLUSH TABLES et il attend que tous les threads ferme leur tables.

  • Killed

    Quelqu'un a envoyé une commande KILL et le thread s'annuler la prochaine fois qu'il vérifie l'option de kill. Cette option est vérifiée dans chaque boucle majeure de MySQL, mais dans certains cas, il peut lui prendre un court instant avant de s'arrêter. Si le thread est verrouillé par un autre thread, l'arrêt va prendre effet aussitôt que l'autre thread lève son verrou.

  • Sending data

    Le thread traite des lignes pour une commande SELECT et il envoie les données au client.

  • Sorting for group

    Le thread est en train de faire un tri pour satisfaire une clause GROUP BY.

  • Sorting for order

    Le thread est en train de faire un tri pour satisfaire une clause ORDER BY.

  • Opening tables

    Cela signifie simplement que le thread essaie d'ouvrir une table. Ce doit être une opération très rapide, à moins que quelque chose ne retarde l'ouverture. Par exemple, une commande ALTER TABLE ou LOCK TABLE peut empêcher l'ouverture de table, jusqu'à l'achèvement de la commande.

  • Removing duplicates

    La requête utilisait SELECT DISTINCT de telle manière que MySQL ne pouvait pas optimiser les lignes distinctes au début du traitement. A cause de cela, MySQL doit effectuer une opération de plus pour supprimer toutes les lignes en doubles, avant d'envoyer les lignes au client.

  • Reopen table

    Le thread a re¸u un verrou pour une table, mais a noté après l'avoir re¸u que la structure de la table a changé. Il a libéré le verrou, fermé la table, et maintenant il essaie de la rouvrir.

  • Repair by sorting

    Le thread répare la table en utilisant la méthode de tri pour créer l'index.

  • Repair with keycache

    Le thread répare la table en utilisant la méthode de création des clés à partir du cache de clé. C'est bien plus lent que la réparation par tri.

  • Searching rows for update

    Le thread effectue une première phase pour trouver toutes les lignes qui satisfont les critères avant de les modifier. Cela doit être fait si UPDATE modifie l'index qui sera utilisé pour trouver les lignes.

  • Sleeping

    Le thread attend que le client envoie une nouvelle commande.

  • System lock

    Le thread attend le verrou externe pour la table. Si vous n'utilisez pas de serveurs MySQL multiples qui exploitent les mêmes tables, vous pouvez désactiver les verrous systèmes avec l'option --skip-external-locking.

  • Upgrading lock

    Le gestionnaire de INSERT DELAYED essaie d'obtenir un verrou pour insérer des lignes.

  • Updating

    Le thread recherche des lignes pour les modifier.

  • User Lock

    Le thread attend un GET_LOCK().

  • Waiting for tables

    Le thread a re¸u l'annonce que la structure de table a été modifiée, et il doit réouvrir la table pour obtenir une nouvelle structure. Pour être capable de réouvrir la table, il doit attendre que les autres threads aient fermé la table en question.

    Cette annonce survient lorsqu'un autre autre thread a été utilisé avec la commande FLUSH TABLES ou une des commandes suivantes, appliquées à la table en question : FLUSH TABLES table_name, ALTER TABLE, RENAME TABLE, REPAIR TABLE, ANALYZE TABLE ou OPTIMIZE TABLE.

  • waiting for handler insert

    Le gestionnaire de INSERT DELAYED a traité toutes insertions, et en attend de nouvelles.

La plupart des états sont des opérations très rapides. Si le thread s'attarde dans un de ces états pour plusieurs secondes, il doit y avoir un problème qui mérite d'être étudié.

Il existe encore d'autres états qui ne sont pas mentionné ci-dessus, mais la majorité sont utilisés pour trouver des bogues dans mysqld.

13.5.3.15. Syntaxe de SHOW STATUS

SHOW STATUS [LIKE 'pattern']

SHOW STATUS affiche des informations sur le statut du serveur. Cette information est aussi accessible avec la commande la commande mysqladmin extended-status.

Un résultat partiel est présenté ci-dessous. La liste complète des variables dépend de votre serveur. Leur signification individuelle est présentée dans la section See Section 5.2.4, « Variables de statut du serveur  ».

mysql> SHOW STATUS;
+--------------------------+------------+
| Variable_name            | Value      |
+--------------------------+------------+
| Aborted_clients          | 0          |
| Aborted_connects         | 0          |
| Bytes_received           | 155372598  |
| Bytes_sent               | 1176560426 |
| Connections              | 30023      |
| Created_tmp_disk_tables  | 0          |
| Created_tmp_tables       | 8340       |
| Created_tmp_files        | 60         |
...
| Open_tables              | 1          |
| Open_files               | 2          |
| Open_streams             | 0          |
| Opened_tables            | 44600      |
| Questions                | 2026873    |
...
| Table_locks_immediate    | 1920382    |
| Table_locks_waited       | 0          |
| Threads_cached           | 0          |
| Threads_created          | 30022      |
| Threads_connected        | 1          |
| Threads_running          | 1          |
| Uptime                   | 80380      |
+--------------------------+------------+

Avec la clause LIKE, la commande peut limiter l'affichage des variables à celles qui vérifient un masque :

mysql> SHOW STATUS LIKE 'Key%';
+--------------------+----------+
| Variable_name      | Value    |
+--------------------+----------+
| Key_blocks_used    | 14955    |
| Key_read_requests  | 96854827 |
| Key_reads          | 162040   |
| Key_write_requests | 7589728  |
| Key_writes         | 3813196  |
+--------------------+----------+

13.5.3.16. SHOW TABLE STATUS

SHOW TABLE STATUS [FROM db_name] [LIKE wild]

SHOW TABLE STATUS (nouveau en version 3.23) fonctionne comme SHOW STATUS, mais fournit des informations sur les tables. Vous pouvez aussi obtenir ces informations en utilisant la commande en ligne mysqlshow --status db_name. Les données suivantes sont retournées :

  • Name

    Nom de la table.

  • Type

    Type de table. See Chapitre 14, Moteurs de tables MySQL et types de table.

  • Row_format

    Le format de stockage de ligne (Fixed, Dynamic ou Compressed).

  • Rows

    Nombre de lignes.

  • Avg_row_length

    Taille moyenne d'une ligne.

  • Data_length

    Taille du fichier de données.

  • Max_data_length

    Taille maximale du fichier de données. Pour les formats de lignes fixe, c'est le nombre maximal de lignes dans la table. Pour les formats de lignes dynamique, c'est le nombre total d'octets qui peuvent être stockés dans la table, avec le pointeur de données utilisé.

  • Index_length

    Taille du fichier d'index.

  • Data_free

    Nombre d'octets alloués mais non utilisés.

  • Auto_increment

    Prochaine valeur d'auto_increment.

  • Create_time

    Date de création de la table.

  • Update_time

    Date de dernière modification de la table.

  • Check_time

    Date de dernier entretien de la table.

  • Collation

    Le jeu de caractères et la collation de la table (nouveau en 4.1.1)

  • Checksum

    La somme de contrôle en direct (si elle existe). (nouveau en 4.1.1)

  • Create_options

    Options supplémentaires utilisées avec CREATE TABLE.

  • Comment

    Le commentaire utilisé lors de la création de la table (ou des informations sur pourquoi MySQL n'a pu accéder aux informations de la table).

Les tables InnoDB indiqueront l'espace disque libre dans le commentaire de table.

13.5.3.17. Syntaxe de SHOW TABLES

SHOW [OPEN] TABLES [FROM db_name] [LIKE 'pattern']

SHOW TABLES liste les tables permanentes (non TEMPORARY) dans une base de données. Vous pouvez obtenir cette liste avec la commande en ligne mysqlshow db_name.

Note : Si vous n'avez pas les droits sur une table, la table n'apparaîtra pas dans le résultat de SHOW TABLES et mysqlshow db_name.

SHOW OPEN TABLES liste les tables qui sont actuellement ouvertes dans le cache de table. See Section 7.4.8, « Quand MySQL ouvre et ferme les tables ». Le champ Comment du résultat indique le nombre d'ouverture de la table en cache (cached) et le nombre d'utilisation (in_use). OPEN est disponible depuis MySQL 3.23.33.

13.5.3.18. Syntaxe de SHOW VARIABLES

SHOW [GLOBAL | SESSION] VARIABLES [LIKE 'pattern']

SHOW VARIABLES affiche les valeurs des variables systèmes de MySQL. Vous pouvez aussi obtenir ces informations avec la commande mysqladmin variables.

Les options GLOBAL et SESSION ont été ajoutées en MySQL 4.0.3. Avec GLOBAL, vous obtiendrez les valeurs qui seront utilisées pour les nouvelles connexions au serveur MySQL. Avec SESSION, vous recevez les valeurs effectives pour la connexion en cours. Si vous ne précisez ni l'une ni l'autre, la valeur par défaut est SESSION. LOCAL est un synonyme de SESSION.

Si les valeurs par défaut ne vous conviennent pas, vous pouvez modifier la plupart de ces variables, en ligne de commande, lorsque mysqld est lancé. Voir Section 5.2.1, « Options de ligne de commande de mysqld » et Section 13.5.2.8, « Syntaxe de SET ».

Voici un extrait du résultat de la commande. La liste complète des variables et de leur valeur peut être différente pour votre serveur. La signification de chaque variable est présentée dans See Section 5.2.3, « Variables serveur système ». Des informations sur comment optimiser ces valeurs sont disponibles dans la section Section 7.5.2, « Réglage des paramètres du serveur ».

mysql> SHOW VARIABLES;
+---------------------------------+------------------------------+
| Variable_name                   | Value                        |
+---------------------------------+------------------------------|
| back_log                        | 50                           |
| basedir                         | /usr/local/mysql             |
| bdb_cache_size                  | 8388572                      |
| bdb_log_buffer_size             | 32768                        |
| bdb_home                        | /usr/local/mysql             |
...
| max_connections                 | 100                          |
| max_connect_errors              | 10                           |
| max_delayed_threads             | 20                           |
| max_error_count                 | 64                           |
| max_heap_table_size             | 16777216                     |
| max_join_size                   | 4294967295                   |
| max_relay_log_size              | 0                            |
| max_sort_length                 | 1024                         |
...
| timezone                        | EEST                         |
| tmp_table_size                  | 33554432                     |
| tmpdir                          | /tmp/:/mnt/hd2/tmp/          |
| version                         | 4.0.4-beta                   |
| wait_timeout                    | 28800                        |
+---------------------------------+------------------------------+

Avec la clause LIKE, la commande n'affichera que les variables qui vérifie le masque fourni :

mysql> SHOW VARIABLES LIKE 'have%';
+--------------------+----------+
| Variable_name      | Value    |
+--------------------+----------+
| have_bdb           | YES      |
| have_innodb        | YES      |
| have_isam          | YES      |
| have_raid          | NO       |
| have_symlink       | DISABLED |
| have_openssl       | YES      |
| have_query_cache   | YES      |
+--------------------+----------+

13.5.3.19. SHOW WARNINGS | ERRORS

SHOW WARNINGS [LIMIT [offset,] row_count]
SHOW COUNT(*) WARNINGS

Cette commande affiche les erreurs, alertes et notes qui ont été générées par la dernière commande. Les erreurs et alertes sont remises à zéro pour chaque nouvelle commande qui utilisent une table. Cette commande a été implémentée depuis MySQL 4.1.0. Une commande connexe, SHOW ERRORS, affiche uniquement les erreurs. See Section 13.5.3.8, « Syntaxe de SHOW ERRORS ».

La liste de messages est remise à zéro au début de chaque commande qui utilise la table.

La commande SHOW COUNT(*) WARNINGS affiche le nombre total d'erreurs, d'alertes et de notes. Vous pouvez aussi lire ce nombre avec la variable warning_count :

SHOW COUNT(*) WARNINGS;
SELECT @@warning_count;

La valeur de warning_count peut être plus grande que le nombre de messages affichés par SHOW WARNINGS si la variable système max_error_count est configurée assez bas pour que tous les messages ne soient pas stockés. Un exemple plus loin dans cette section montre ce qui arrive.

La clause LIMIT a la même syntaxe que la commande SELECT. See Section 13.1.7, « Syntaxe de SELECT ».

Le serveur MySQL retourne le nombre total d'alertes et d'erreurs que vous avez obtenu lors de la dernière commande. Ils sont disponibles avec la fonction mysql_warning_count(). See Section 24.2.3.61, « mysql_warning_count() ».

Jusqu'à max_error_count messages peuvent être stockés (variable globale et spécifique aux threads).

Vous pouvez lire le nombre d'erreurs dans @error_count et le nombre d'alertes dans @warning_count.

SHOW WARNINGS affiche aussi toutes les erreurs, alertes et notes de la dernière commande, alors que SHOW ERRORS ne montre que les erreurs.

mysql> DROP TABLE IF EXISTS no_such_table;
mysql> SHOW WARNINGS;

+-------+------+-------------------------------+
| Level | Code | Message                       |
+-------+------+-------------------------------+
| Note  | 1051 | Unknown table 'no_such_table' |
+-------+------+-------------------------------+

Notez que depuis MySQL 4.1.0, nous avons ajouté un nouveau système d'alertes, et peu de commandes MySQL génère des alertes. 4.1.1 supporte toutes sortes d'alertes pour LOAD DATA INFILE et les commandes DML telles que INSERT, UPDATE et ALTER.

Par exemple, voici une situation simple qui produit des alertes de conversions pour une commande d'insertion :

mysql> create table t1(a tinyint NOT NULL, b char(4));
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t1 values(10,'mysql'),(NULL,'test'),(300,'open source');
Query OK, 3 rows affected, 4 warnings (0.15 sec)
Records: 3  Duplicates: 0  Warnings: 4

mysql> show warnings;
+---------+------+---------------------------------------------------------------+
| Level   | Code | Message                                                       |
+---------+------+---------------------------------------------------------------+
| Warning | 1263 | Data truncated for column 'b' at row 1                        |
| Warning | 1261 | Data truncated, NULL supplied to NOT NULL column 'a' at row 2 |
| Warning | 1262 | Data truncated, out of range for column 'a' at row 3          |
| Warning | 1263 | Data truncated for column 'b' at row 3                        |
+---------+------+---------------------------------------------------------------+
4 rows in set (0.00 sec)

Le nombre maximal d'alertes peut être spécifié en utilisant la variable de serveur 'max_error_count', SET max_error_count=[count]; Par défaut, c'est 64. Pour désactiver les alertes, donnez simplement la valeur de 0 à la variable. Si max_error_count vaut 0, alors le nombre d'alertes représente toujours le nombre d'alertes qui ont eu lieu, mais aucun message d'erreur n'est accessible.

Par exemple, observez la commande ALTER suivante, pour l'exemple ci-dessus, qui retourne uniquement une alerte, même si le nombre total d'alertes est de 3 lorsque 'max_error_count'=1.

mysql> show variables like 'max_error_count';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_error_count | 64    |
+-----------------+-------+
1 row in set (0.00 sec)

mysql> set max_error_count=1;
Query OK, 0 rows affected (0.00 sec)

mysql> alter table t1 modify b char;
Query OK, 3 rows affected, 3 warnings (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 3

mysql> show warnings;
+---------+------+----------------------------------------+
| Level   | Code | Message                                |
+---------+------+----------------------------------------+
| Warning | 1263 | Data truncated for column 'b' at row 1 |
+---------+------+----------------------------------------+
1 row in set (0.00 sec)

mysql>

13.5.4. Autres commandes d'administration

13.5.4.1. Syntaxe de CACHE INDEX

CACHE INDEX
  table_index_list [, table_index_list] ...
  IN key_cache_name

table_index_list:
  tbl_name [[INDEX] (index_name[, index_name] ...)]

La commande CACHE INDEX assigne un index de table à un cache de clé spécifique. Cette commande est uniquement disponible pour les tables MyISAM.

La commande suivante assigne les index des tables t1, t2 et t3 au cache de clé appelé hot_cache :

mysql> CACHE INDEX t1, t2, t3 IN hot_cache;
+---------+--------------------+----------+----------+
| Table   | Op                 | Msg_type | Msg_text |
+---------+--------------------+----------+----------+
| test.t1 | assign_to_keycache | status   | OK       |
| test.t2 | assign_to_keycache | status   | OK       |
| test.t3 | assign_to_keycache | status   | OK       |
+---------+--------------------+----------+----------+

La syntaxe de CACHE INDEX vous permet de spécifier des index particuliers à un cache. Cependant, l'implémentation courante assigne tous les index de la table au cache, et il n'y a donc pas d'intérêt à spécifier autre chose que le nom de la table.

Le cache de clé utilisé dans une commande CACHE INDEX peut être créé en lui donnant une taille avec une commande de configuration, ou la configuration du serveur. Par exemple :

mysql> SET GLOBAL keycache1.key_buffer_size=128*1024;

Les paramètres du cache de clé sont accessibles depuis une variable système structurée. See Section 9.4.1, « Variables système structurées ».

Un cache de clé doit exister avant que vous ne l'utilisiez :

mysql> CACHE INDEX t1 in non_existent_cache;
ERROR 1283 (HY000): Unknown key cache 'non_existent_cache'

Par défaut, les index de table sont assignée au cache de clé par défaut, créé au moment du démarrage du serveur. Lorsqu'un cache de clé est détruit, tous les index qui lui étaient assigné sont transmis au cache par défaut.

Les assignations d'index affectent le serveur globalement : si un client assigne un index à un cache donné, ce cache sera utilisé pour tous les requêtes, quelque soit le client qui émet la requête.

CACHE INDEX a été ajouté en MySQL 4.1.1.

13.5.4.2. Syntaxe de FLUSH

FLUSH flush_option [,flush_option] ...

Vous devez utiliser la commande FLUSH si vous voulez effacer certains caches internes de MySQL. Pour exécuter FLUSH, vous devez avoir le droit RELOAD.

flush_option peut être l'une des suivantes :

  • HOSTS

    Vide le cache des hôtes. Vous devez vider ce cache si certaines des adresses IP de vos clients changent, ou si vous obtenez des erreurs du type Host ... is blocked. Lorsque plus de max_connect_errors erreurs successives surviennent pour un hôte, lors des connexions au serveur MySQL, MySQL suppose qu'il y a un problème, et interdit l'accès à l'hôte. See Section A.2.5, « Erreur Host '...' is blocked ». Vous pouvez démarrer mysqld avec -O max_connect_errors=999999999 pour éviter ce message.

  • DES_KEY_FILE

    Recharge les clés DES depuis le fichier de stockage spécifié par --des-key-file lors du démarrage du serveur.

  • LOGS

    Ferme et réouvre tous les fichiers de log. Si vous avez spécifié un fichier de log de mise à jour, ou un fichier de log binaire sans extension, le numéro d'extension du fichier de log sera incrémenté d'une unité. Si vous avez utilisé une extension dans le nom du fichier, MySQL va fermer et réouvrir le même fichier. See Section 5.9.3, « Le log de modification ». Ceci est la même chose que d'envoyer le signal SIGHUP au serveur mysqld.

  • PRIVILEGES

    Recharge les privilèges des tables de droits dans la base mysql.

  • QUERY CACHE

    Défragmente le cache des requêtes pour mieux en utiliser la mémoire. Cette commande n'effacera aucune requête du cache, à la différence de RESET QUERY CACHE.

  • TABLES

    Ferme toutes les tables ouvertes, et force les tables utilisées à se refermer. Cela vide aussi le cache de requêtes.

  • [TABLE | TABLES] nom_de_table [,nom_de_table...]

    Vide du cache uniquement les tables nommées.

  • TABLES WITH READ LOCK

    Ferme toutes les tables ouvertes, et verrouille en lecture toute les tables et bases, jusqu'à ce que vous exécutiez une commande UNLOCK TABLES. C'est très pratique pour générer des sauvegardes, si vous avez un système de fichiers comme Veritas, qui peut prendre des photos du système.

  • STATUS

    Remet la plupart des variables de statut à zéro. A n'utiliser que pour corriger une requête. See Section 1.4.1.3, « Comment rapporter un bogue ou un problème ».

  • USER_RESOURCES

    Remet toutes les ressources à zéro. Cela va autoriser de nouveau les utilisateurs qui ont été bloqués. See Section 5.6.4, « Limiter les ressources utilisateurs ».

Vous pouvez aussi accéder à toutes les commandes décrites plus haut en les donnant en arguments à mysqladmin (exemple : flush-hosts, flush-logs, reload, ou encore flush-tables).

Reportez-vous aussi à la commande RESET avec la réplication. See Section 13.5.4.5, « Syntaxe de la commande RESET ».

13.5.4.3. Syntaxe de KILL

KILL [CONNECTION | QUERY] thread_id

Chaque connexion à mysqld utilise un thread unique. Vous pouvez voir les threads en cours d'exécution en utilisant la commande SHOW PROCESSLIST et en terminer un avec la commande KILL thread_id.

Depuis MySQL 5.0.0, KILL autorise les options CONNECTION et QUERY :

  • KILL CONNECTION est similaire à KILL sans option : elle termine la connexion associée avec le thread thread_id.

  • KILL QUERY termine la requête que la connexion est actuellement en train de traiter, mais laisse la connexion ouverte.

Si vous avez le droit PROCESS, vous pouvez voir tous les threads. Si vous avez le droit SUPER, vous pouvez terminer tout les threads. Sinon, vous ne pouvez terminer que vos propres threads.

Vous pouvez aussi utiliser les commandes mysqladmin processlist et mysqladmin kill pour examiner et terminer les threads.

Note : vous ne pouvez actuellement pas utiliser KILL avec la bibliothèque du serveur embarqué, car celui-ci utilise les threads de l'application hôte, il ne crée pas ses propres threads.

Quand vous exécutez un KILL, un thread spécifique est crée pour ce thread.

Dans la plupart des cas, la terminaison du thread pourra prendre un certain temps vu que le thread de terminaison est invoqué à intervalles spécifiques.

  • Pour les boucles de SELECT, ORDER BY et GROUP BY, le thread de terminaison est vérifié après avoir lu un enregistrement. S'il est activé, la requête est abandonnée.

  • Lors d'un ALTER TABLE le thread de terminaison est vérifié avant la lecture de chacune des colonnes de la table d'origine. S'il est activé, la commande est abandonnée et la table temporaire effacée.

  • Lors d'un UPDATE ou d'un DELETE, le thread de terminaison est vérifié après chaque lecture de bloc et chaque mise à jour ou suppression de ligne. S'il est activé, la requête est abandonnée. Notez que si vous utilisez les transactions, les modifications ne seront pas perdues !

  • GET_LOCK() stoppera avec NULL.

  • Un thread INSERT DELAYED videra rapidement toutes les lignes en mémoire et se terminera.

  • Si le thread est dans le gestionnaire des verrous de tables (état : Locked), le verrou sur la table sera vite enlevé.

  • Si le thread est en attente de libération d'espace disque lors d'un appel à write, l'opération est avortée avec un message d'erreur indiquant que le disque est plein.

13.5.4.4. Syntaxe de LOAD INDEX INTO CACHE

LOAD INDEX INTO CACHE
  table_index_list [, table_index_list] ...

table_index_list:
  tbl_name
    [[INDEX] (index_name[, index_name] ...)]
    [IGNORE LEAVES]

La commande LOAD INDEX INTO CACHE précharge un index dans un cache de clé, qui est explicitement nommé dans dans la commande CACHE INDEX, ou dans le cache par défaut. LOAD INDEX INTO CACHE ne sert que pour les tables MyISAM.

L'option IGNORE LEAVES fait que les blocs terminaux de l'index ne sont pas lus.

La commande suivante précharge les noeuds des tables t1 et t2 :

mysql> LOAD INDEX INTO CACHE t1, t2 IGNORE LEAVES;
+---------+--------------+----------+----------+
| Table   | Op           | Msg_type | Msg_text |
+---------+--------------+----------+----------+
| test.t1 | preload_keys | status   | OK       |
| test.t2 | preload_keys | status   | OK       |
+---------+--------------+----------+----------+

Cette commande charge tous les index de t1. Elle ne charge que les index non-terminaux de t2.

La syntaxe de LOAD INDEX INTO CACHE vous permet de spécifier seulement des index particuliers à charger dans la table. Cependant, l'implémentation courante charge tous les index : il n' y a pas de raison pour utiliser autre chose que le nom de la table.

LOAD INDEX INTO CACHE a été ajouté en MySQL 4.1.1.

13.5.4.5. Syntaxe de la commande RESET

RESET reset_option [, reset_option] ...

La commande RESET sert à remettre à zéro des données. C'est aussi une version plus puissante de la commande FLUSH. See Section 13.5.4.2, « Syntaxe de FLUSH ».

Pour exécuter la commande RESET, vous devez avoir les droits RELOAD.

13.6. Commandes de réplication

Cette section décrit les commandes liées à la réplication. Un groupe de commande peut être utilisé pour contrôler le serveur. L'autre groupe sert avec les esclaves.

13.6.1. Requêtes SQL pour contrôler les maîtres de réplication

La réplication est contrôlable via l'interface SQL. Cette section présente les commandes qui contrôlent les maîtres de réplication. La section Section 13.6.2, « Commandes SQL de contrôle des esclaves de réplication » présente les commandes pour gérer les esclaves.

13.6.1.1. PURGE MASTER LOGS

PURGE {MASTER | BINARY} LOGS TO 'log_name'
PURGE {MASTER | BINARY} LOGS BEFORE 'date'

Efface tous les logs binaires listés dans l'index de logs, qui sont antérieurs à la date ou au log indiqué. Les logs sont aussi supprimés de cette liste : le log donné en paramètre devient alors le premier de la liste.

Exemple :

PURGE MASTER LOGS TO 'mysql-bin.010';
PURGE MASTER LOGS BEFORE '2003-04-02 22:46:26';

La variante BEFORE est disponible en MySQL 4.1; son argument de date peut être au format 'YYYY-MM-DD hh:mm:ss'. MASTER et BINARY sont synonymes, mais BINARY ne peut être utilisé que depuis MySQL 4.1.1.

Si vous avez un esclave actif qui est actuellement en train de lire l'un des logs que vous voulez effacer, la commande ne fera rien, et échouera avec une erreur. Cependant, si l'esclave est inactif, et que vous effacez un des logs qu'il utilisait, l'esclave sera incapable de reprendre la réplication. Cette commande peut être utilisée sans problème durant la réplication : vous n'avez pas besoin d'arrêter les esclaves.

Pour purger les logs, suivez cette procédure :

  1. Sur chaque esclave, utilisez la commande SHOW SLAVE STATUS pour vérifier quel log est lu.

  2. Faite une liste des logs sur le maître avec SHOW MASTER LOGS.

  3. Déterminez le plus ancien log parmi ceux utilisés par les esclaves. C'est votre limite. Si vous les esclaves sont à jour, alors ce sera le dernier log de la liste.

  4. Faites une sauvegarde de tous les logs que vous allez effacer. Cette étape est optionnelle, mais c'est une bonne idée.

  5. Purgez tous les logs jusqu'à celui qui précède votre limite.

13.6.1.2. RESET MASTER

RESET MASTER

Efface tous les fichiers de logs binaires dans le fichier d'index, et vide le fichier d'index des logs.

Cette commande s'appelait FLUSH MASTER avant MySQL 3.23.26.

13.6.1.3. SET SQL_LOG_BIN

SET SQL_LOG_BIN = {0|1}

Inactive ou active le log binaire de la connexion courante (SQL_LOG_BIN est une variable de session), si le client se connecte avec un compte qui a les droits de SUPER. La commande est ignorée si le client n'a pas de droits.

13.6.1.4. SHOW BINLOG EVENTS

SHOW BINLOG EVENTS [ IN 'log_name' ] [ FROM pos ] [ LIMIT [offset,] row_count ]

Affiche les événements du log binaire. Si vous ne spécifiez pas 'log_name', le premier log binaire sera affiché.

La clause LIMIT a la même syntaxe que celle de la commande SELECT. See Section 13.1.7, « Syntaxe de SELECT ».

Cette commande est disponible en MySQL 4.0

13.6.1.5. SHOW MASTER LOGS

SHOW MASTER LOGS

Liste les logs binaires disponibles sur le maître. Vous devriez utiliser cette commande avant PURGE MASTER LOGS pour savoir jusqu'où vous pouvez aller.

13.6.1.6. SHOW MASTER STATUS

SHOW MASTER STATUS

Affiche les informations d'état du log binaire du maître.

13.6.1.7. SHOW SLAVE HOSTS

SHOW SLAVE HOSTS

Affiche la liste des esclaves actuellement enregistrée sur le maître. Notez que les esclaves qui ne sont pas lancé avec l'option --report-host=nom_d_esclave ne seront pas visible dans cette liste.

13.6.2. Commandes SQL de contrôle des esclaves de réplication

La réplication est contrôlable via l'interface SQL. Cette section présente les commandes qui contrôlent les esclaves de réplication. La section Section 13.6.1, « Requêtes SQL pour contrôler les maîtres de réplication » présente les commandes pour gérer les maîtres.

13.6.2.1. CHANGE MASTER TO

CHANGE MASTER TO master_def [, master_def] ...

master_def =
      MASTER_HOST = 'host_name'
    | MASTER_USER = 'user_name'
    | MASTER_PASSWORD = 'password'
    | MASTER_PORT = port_num
    | MASTER_CONNECT_RETRY = count
    | MASTER_LOG_FILE = 'master_log_name'
    | MASTER_LOG_POS = master_log_pos
    | RELAY_LOG_FILE = 'relay_log_name'
    | RELAY_LOG_POS = relay_log_pos
    | MASTER_SSL = {0|1}
    | MASTER_SSL_CA = 'ca_file_name'
    | MASTER_SSL_CAPATH = 'ca_directory_name'
    | MASTER_SSL_CERT = 'cert_file_name'
    | MASTER_SSL_KEY = 'key_file_name'
    | MASTER_SSL_CIPHER = 'cipher_list'

Modifie les paramètres que l'esclave utilise pour se connecter et pour communiquer avec le serveur maître. Les valeurs possibles pour master_def sont présentées ci-dessus.

Les options de log de relais, RELAY_LOG_FILE et RELAY_LOG_POS, sont disponibles depuis MySQL 4.0.

Les options SSL, MASTER_SSL, MASTER_SSL_CA, MASTER_SSL_CAPATH, MASTER_SSL_CERT, MASTER_SSL_KEY et MASTER_SSL_CIPHER, sont disponibles depuis MySQL 4.1.1. Vous pouvez changer ces options même sur les esclaves qui sont compilé sans le support SSL. Elles seront sauvées dans le fichier master.info mais ignorées jusqu'à ce que le support SSL soit activé.

Par exemple :

mysql> CHANGE MASTER TO
    ->     MASTER_HOST='master2.mycompany.com',
    ->     MASTER_USER='replication',
    ->     MASTER_PASSWORD='bigs3cret',
    ->     MASTER_PORT=3306,
    ->     MASTER_LOG_FILE='master2-bin.001',
    ->     MASTER_LOG_POS=4,
    ->     MASTER_CONNECT_RETRY=10;
mysql> CHANGE MASTER TO
    ->     RELAY_LOG_FILE='slave-relay-bin.006',
    ->     RELAY_LOG_POS=4025;

MASTER_USER, MASTER_PASSWORD, MASTER_SSL, MASTER_SSL_CA, MASTER_SSL_CAPATH, MASTER_SSL_CERT, MASTER_SSL_KEY, et MASTER_SSL_CIPHER sont des informations qui permettent à l'esclave de se connecter au maître. Si vous omettez certains paramètres, les paramètres omis conserveront leur ancienne valeur. Par exemple, si le mot de passe sur le maître a changé, il suffit de faire :

mysql> STOP SLAVE; -- if replication was running
mysql> CHANGE MASTER TO MASTER_PASSWORD='new3cret';
mysql> START SLAVE; -- if you want to restart replication

pour indiquer à l'esclave le nouveau mot de passe : il n'y a pas besoin de spécifier les informations qui n'ont pas changé, comme l'hôte, le port, l'utilisateur, etc...

MASTER_HOST, MASTER_PORT sont le nom d'hôte ou l'adresse IP du maître, et son port TCP. Notez que si MASTER_HOST est égal à localhost, alors, comme généralement avec MySQL, le port sera ignoré si les sockets Unix sont utilisables.

Si vous spécifiez MASTER_HOST ou MASTER_PORT, l'esclave supposera que le serveur maître est différent du précédent, même si vous spécifier les mêmes valeurs d'hôte et de port que précédemment. Dans ce cas, les anciennes valeurs et position de l'historique binaire ne sont plus valides. Ainsi, si vous ne spécifiez pas MASTER_LOG_FILE et MASTER_LOG_POS dans la commande, MASTER_LOG_FILE='' et MASTER_LOG_POS=4 sont ajoutés silencieusement.

MASTER_LOG_FILE et MASTER_LOG_POS sont les coordonnées auxquelles le thread d'I/O doit commencer à lire chez le maître, lorsque le thread redémarrera. Si vous spécifiez l'un d'entre eux, vous ne pourrez pas spécifier RELAY_LOG_FILE ou RELAY_LOG_POS. Si MASTER_LOG_FILE, ni MASTER_LOG_POS n'ont été spécifiés, alors les dernières coordonnées du thread esclave d'avant la commande CHANGE MASTER seront utilisées. Cela assure que la réplication ne connaît pas de discontinuité, même si le thread esclave était en retard sur le thread d'I/O, alors que vous ne voulez changer que le mot de passe. Ce comportement sécuritaire a été introduit à partir de MySQL versions 4.0.17 et 4.1.1. Avant ces versions, les coordonnées utilisées celles du thread d'I/O, avant que la commande CHANGE MASTER soit émise, ce qui conduisait à des pertes d'événements au niveau du maître, et donc, la corruption de la réplication.

CHANGE MASTER efface tous les logs de relais (et en démarre de nouveaux), à moins que vous ne spécifiez l'option RELAY_LOG_FILE ou RELAY_LOG_POS (dans ce cas, les logs de relais seront conservés; depuis MySQL 4.1.1 la variable globale RELAY_LOG_PURGE sera automatiquement mise à 0). CHANGE MASTER TO modifie master.info et relay-log.info.

CHANGE MASTER sert à configurer un esclave lorsque vous avez une sauvegarde du maître, son log et la position qui correspond à la sauvegarde du maître. Vous pouvez utiliser la commande CHANGE MASTER TO MASTER_LOG_FILE='log_name_on_master', MASTER_LOG_POS=log_offset_on_master sur l'esclave après la restauration de la sauvegarde.

Le premier exemple ci-dessus (CHANGE MASTER TO MASTER_HOST='master2.mycompany.com' etc) modifie les coordonnées du maître et de son log binaire. Cela est utile lorsque vous voulez que l'esclave réplique le maître. Le second exemple, moins fréquent, sert lorsque l'esclave a des logs de relais que vous voulez utiliser à nouveau. Pour cela, le maître n'a pas besoin d'être rejoint : il suffit d'utiliser la commande CHANGE MASTER TO et de lancer le thread SQL START SLAVE SQL_THREAD. Vous pouvez même utiliser cela dans une configuration de réplication, sur un serveur indépendant, pour assurer la restauration après crash. Supposez que votre serveur soit planté, et que vous avez restauré la sauvegarde. Vous voulez que le serveur exécute à nouveau ses propres logs (non pas des logs de relais, mais ses logs binaires), qui sont par exemple, stockés sous le nom myhost-bin.*. Tout d'abord, faite une copie des fichiers de log dans un entrepôt, au cas où une erreur de manipulation surviendrait, et que le serveur vide ses logs. Si vous utilisez MySQL 4.1.1 ou plus récent, utilisez la commande suivante pour plus de sécurité : SET GLOBAL RELAY_LOG_PURGE=0.

Puis, lancez le serveur sans log-bin, et avec un nouvel identifiant (différent du précédent), avec l'option relay-log=myhost-bin (pour faire croire au serveur que ses propres logs sont des logs de relais), et skip-slave-start. Puis, envoyez cette commande :

mysql> CHANGE MASTER TO
    ->     RELAY_LOG_FILE='myhost-bin.153',
    ->     RELAY_LOG_POS=410,
    ->     MASTER_HOST='some_dummy_string';
mysql> START SLAVE SQL_THREAD;

Le serveur va alors lire et exécuter ses propres logs, et rattraper les données jusqu'au crash.

Une fois la restauration finie, faites STOP SLAVE, éteignez le serveur, supprimez master.info et relay-log.info, puis relancez le serveur avec ses options originales.

Pour le moment, spécifier MASTER_HOST (même avec une valeur insignifiante) est obligatoire pour que le serveur pense qu'il est un esclave. Donner au serveur un nouvel identifiant, différent du précédent, est aussi obligatoire, car sinon, le serveur va voir des événements avec son identifiant, et il va conclure que c'est une réplication circulaire, et il va les ignorer. Dans le futur, nous envisageons de nous débarasser de ces petites contraintes.

13.6.2.2. LOAD DATA FROM MASTER

LOAD DATA FROM MASTER

Fait une sauvegarde du maître et la copie vers l'esclave. Met à jour les valeurs de MASTER_LOG_FILE et MASTER_LOG_POS pour que la réplication reprennent à la bonne position. Respecte les interdictions de réplications de tables et de bases spécifiées par les options replicate-*.

L'utilisation de cette commande est sujette aux conditions suivantes :

  • Fonctionne avec les tables MyISAM.

  • Pose un verrou global en lecture sur le maître durant la sauvegarde, qui empêche les modifications sur le maître durant la phase de chargement.

Dans le futur, il est prévu de faire que cette commande fonctionne avec les tables InnoDB, et qu'elle se passe du verrou global en utilisant des fonctionnalités de sauvegarde à chaud non-bloquantes.

Si vous chargez de très grosses tables, pensez à augmenter les valeurs des options net_read_timeout et net_write_timeout sur vos maître et esclave. See Section 5.2.3, « Variables serveur système ».

Notez que LOAD DATA FROM MASTER ne copie pas les tables de droits de la base mysql. C'est fait pour simplifier l'utilisation de droits et utilisateurs différents sur le maître et les esclaves.

Cette commande requiert les droits de RELOAD et SUPER sur le maître, et le droit de SELECT sur toutes les tables du maître qui seront lues. Toutes les tables du maître sur lesquelles l'utilisateur n'a pas de droits de SELECT seront ignorées par LOAD DATA FROM MASTER; ceci est dû au fait que le maître va masquer ces tables à l'utilisateur : LOAD DATA FROM MASTER utilise SHOW DATABASES pour connaître les tables à charger, mais SHOW DATABASES ne retourne que les bases pour lesquelles l'utilisateur a des droits. Voyez Section 13.5.3.6, « Syntaxe de SHOW DATABASES ». Sur l'esclave, l'utilisateur qui envoie la commande LOAD DATA FROM MASTER doit avoir les droits de création et d'effacement des tables et bases impliquées.

13.6.2.3. Syntaxe de LOAD TABLE tbl_name FROM MASTER

LOAD TABLE tbl_name FROM MASTER

Télécharge une copie d'une table depuis le maître vers l'esclave. Cette commande est implémentée pour déboguer la commande LOAD DATA FROM MASTER. Elle requiert un compte pour se connecter au maître, avec les droits de RELOAD et SUPER, ainsi que les droits de SELECT sur la table a charger. Coté esclave, l'utilisateur qui émet la commande doit avoir les droits de LOAD TABLE FROM MASTER pour créer et effacer les tables. Lisez les informations sur les problèmes réseau dans LOAD DATA FROM MASTER; elles s'appliquent aussi ici. Notez aussi que les limitations de LOAD DATA FROM MASTER s'appliquent aussi (par exemple, LOAD TABLE FROM MASTER ne fonctionne que sur les tables de type MyISAM).

13.6.2.4. MASTER_POS_WAIT()

SELECT MASTER_POS_WAIT('master_log_file', master_log_pos)

C'est une fonction et non pas une commande. Elle sert à s'assurer que l'esclave a atteint (lu et exécuté) les événements du log binaire du maître jusqu'à une certaine position. Voyez la section See Section 12.8.4, « Fonctions diverses » pour une description complète.

13.6.2.5. RESET SLAVE

RESET SLAVE

Force l'esclave a oublier toute les positions de réplications dans les logs du maître. Cette commande permet de faire un démarrage propre : elle efface les fichiers master.info et relay-log.info, et les logs de relais, puis créer un nouveau log de relais.

Note : tous les logs de relais sont effacés, même si il n'ont pas été totalement exécutés par le thread SQL. (C'est un état qui est probable si l'esclave de réplication est fortement chargé, ou si vous avez lancé une commande STOP SLAVE.) Les informations de connexions stockées dans le fichier master.info reprennent immédiatement les valeurs spécifiées dans les options de démarrage, si elles étaient précisées. Ces informations incluent notamment le nom de l'hôte maître, le port, l'utilisateur et le mot de passe. Si le thread esclave était au milieu d'une réplication temporaire lorsqu'il a été arrêté, et que RESET SLAVE a été émise, ces tables temporaires sont aussi effacées.

Cette commande s'appelait FLUSH SLAVE avant MySQL 3.23.26.

13.6.2.6. SET GLOBAL SQL_SLAVE_SKIP_COUNTER

SET GLOBAL SQL_SLAVE_SKIP_COUNTER = n

Ignore les n prochains événements du maître. C'est une commande pratique pour rattraper les arrêts de réplications causés par une commande.

Cette commande n'est valide que lorsque le thread esclave ne fonctionne pas. Sinon, elle produit une erreur.

Avant MySQL 4.0, omettez le mot clé GLOBAL dans la commande.

13.6.2.7. SHOW SLAVE STATUS

SHOW SLAVE STATUS

Affiche des informations sur les paramètres essentiels des threads esclaves. Si vous utilisez cette commande avec le client mysql, vous pouvez utiliser le terminateur de commande \G plutôt que le point-virgule pour avoir un format plus lisible :

mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
       Slave_IO_State: Waiting for master to send event
          Master_Host: localhost
          Master_User: root
          Master_Port: 3306
        Connect_Retry: 3
      Master_Log_File: gbichot-bin.005
  Read_Master_Log_Pos: 79
       Relay_Log_File: gbichot-relay-bin.005
        Relay_Log_Pos: 548
Relay_Master_Log_File: gbichot-bin.005
     Slave_IO_Running: Yes
    Slave_SQL_Running: Yes
      Replicate_Do_DB:
  Replicate_Ignore_DB:
           Last_Errno: 0
           Last_Error:
         Skip_Counter: 0
  Exec_Master_Log_Pos: 79
      Relay_Log_Space: 552
      Until_Condition: None
       Until_Log_File:
        Until_Log_Pos: 0
   Master_SSL_Allowed: No
   Master_SSL_CA_File:
   Master_SSL_CA_Path:
      Master_SSL_Cert:
    Master_SSL_Cipher:
       Master_SSL_Key:
Seconds_Behind_Master: 8

Suivant votre version de MySQL, vous pourriez ne pas voir tous les champs qui sont dans cet exemple. Notamment, il y a plusieurs champs qui ne sont disponibles qu'avec MySQL 4.1.1.

Les champs affichés par SHOW SLAVE STATUS ont les définitions suivantes :

  • Slave_IO_State

    Une copie de la colonne State de la commande SHOW PROCESSLIST pour le thread d'I/O. Elle va vous indiquer si le thread essaye de se connecter au maître, attend des événements, se reconnecte, etc. Les différents états possibles sont listés dans la section Section 6.3, « Détails d'implémentation de la réplication ». Etudier cette colonne est nécessaire, par exemple, car le thread peut fonctionner mais ne pas réussir à se connecter au maître : seule cette colonne vous indiquera ce type de problèmes. D'un autre coté, l'état du thread SQL n'est pas indiqué, car les problèmes sont bien plus simples avec lui : soit il fonctionne, et il n'y a pas de problème; soit il ne fonctionne pas, et vous trouverez les messages d'erreur dans la colonne Last_Error, décrite plus bas.

    Ce champ a été ajouté en MySQL 4.1.1.

  • Master_Host

    L'hôte maître courant.

  • Master_User

    Le nom de l'utilisateur utilisé pour se connecter au maître.

  • Master_Port

    Le port courant sur le maître.

  • Connect_Retry

    La valeur courante de l'option master-connect-retry.

  • Master_Log_File

    Le nom du fichier de log binaire que le thread d'I/O utilise sur le maître.

  • Read_Master_Log_Pos

    La position que le thread d'I/O a atteint dans le fichier de log binaire du maître.

  • Relay_Log_File

    Le nom du fichier de log de relais dans lequel le thread SQL est actuellement en train de lire et de travailler.

  • Relay_Log_Pos

    La position à laquelle le thread SQL est en train de travailler.

  • Relay_Master_Log_File

    Le nom du fichier de log binaire du maître qui contient le dernier événement exécuté par le thread SQL.

  • Slave_IO_Running

    Indique si le thread d'I/O est lancé ou pas.

  • Slave_SQL_Running

    Indique si le thread SQL est lancé ou pas.

  • Replicate_Do_DB, Replicate_Ignore_DB

    La liste des bases de données qui ont été spécifiée dans l'option --replicate-do-db et --replicate-ignore-db, éventuellement.

  • Replicate_Do_Table, Replicate_Ignore_Table, Replicate_Wild_Do_Table, Replicate_Wild_Ignore_Table

    La liste des tables qui ont été spécifiées respectivement dans les options --replicate-do-table, --replicate-ignore-table, --replicate-wild-do-table et --replicate-wild-ignore_table, éventuellement.

    Ces champs ont été ajoutés en MySQL 4.1.1.

  • Last_Errno, Last_Error

    Last_Errno est le numéro d'erreur de la plus récent requête exécutée. La valeur de 0 signifie ``pas d'erreur''. Last_Error est le message d'erreur de la plus récent requête exécutée. Par exemple :

    Last_Errno: 1051
    Last_Error: error 'Unknown table 'z'' on query 'drop table z'
    

    Le message indique que la table z existait sur le maître et a été effacée, mais qu'elle n'existe pas sur l'esclave, et que DROP TABLE a échoué sur l'esclave. Cela peut arriver si vous avez oublié de copier une table dans l'esclave avant de lancer la réplication.

    Une chaîne vide signifie ``pas d'erreur''. Si Last_Error n'était pas vide, alors le même message apparaîtra dans le log d'erreur de l'esclave.

  • Skip_Counter

    La dernière valeur utilisée par SQL_SLAVE_SKIP_COUNTER.

  • Exec_Master_Log_Pos

    La position dans les logs binaires du maître (Relay_Master_Log_File) pour le dernier événement exécuté par le thread SQL. ((Relay_Master_Log_File,Exec_Master_Log_Pos) dans le log binaire du maître correspond à (Relay_Log_File,Relay_Log_Pos) dans le log de relais.

  • Relay_Log_Space

    La taille combinée de tous les logs de relais.

  • Until_Condition, Until_Log_File, Until_Log_Pos

    Les valeurs spécifiées dans la clause UNTIL de la commande START SLAVE.

    Until_Condition peut prendre ces valeurs :

    • None (aucune) si aucune clause UNTIL n'a été spécifiée

    • Master (maître), si l'esclave lit depuis une position donnée, dans le log binaire du maître

    • Relay (relais) si l'esclave lit dans une position donnée dans le log de relais.

    Until_Log_File et Until_Log_Pos indique le nom du fichier de log et la position qui définissent le point où le thread SQL va s'arrêter d'exécuter.

    Ces champs ont été ajoutés en MySQL 4.1.1.

  • Master_SSL_Allowed, Master_SSL_CA_File, Master_SSL_CA_Path, Master_SSL_Cert, Master_SSL_Cipher, Master_SSL_Key

    Ces champs indiquent les paramètres SSL utilisés par l'esclave pour se connecter au maître, s'ils sont fournis.

    Master_SSL_Allowed prend ses valeurs :

    • Yes (oui) si la connexion SSL au maître est autorisée

    • No (non) si la connexion SSL au maître est interdite

    • Ignored (ignoré) si la connexion SSL au maître est autorisée par l'esclave mais que le support de SSL n'est pas là.

    Les valeurs des autres champs correspondent aux valeurs des options --master-ca, --master-capath, --master-cert, --master-cipher et --master-key.

    Ces champs ont été ajoutés en MySQL 4.1.1.

  • Seconds_Behind_Master

    Le nombre de secondes qui se sont écoulées depuis le timestamp du dernier événement maître exécuté par le thread SQL. Ce sera NULL si aucun événement n'a été exécuté, ou après une commande CHANGE MASTER et RESET SLAVE. Cette colonne sert à mesurer le retard de l'esclave sur le maître. Cela fonctionne même si le maître et l'esclave on des horloges réglées différemment.

    Ce champ a été ajouté en MySQL 4.1.1.

13.6.2.8. START SLAVE

START SLAVE [thread_name [, thread_name] ... ]
START SLAVE [SQL_THREAD] UNTIL
    MASTER_LOG_FILE = 'log_name', MASTER_LOG_POS = log_pos
START SLAVE [SQL_THREAD] UNTIL
    RELAY_LOG_FILE = 'log_name', RELAY_LOG_POS = log_pos

thread_name = IO_THREAD | SQL_THREAD

START SLAVE, appelé sans option, démarre les deux threads esclaves. Le thread I/O lire les requêtes du maître et les stocke dans le log de relais. Le thread SQL lire le log de relais, et exécute les requêtes. Notez que si START SLAVE réussi à lancer le thread esclave, elle se terminera sans erreur. Mais même dans ce cas, il se peut que le thread esclave se lance, puis s'arrête (car il n'a pas pu se connecter au maître, ou lire le log binaire ou tout autre problème). START SLAVE ne vous préviendra pas de cet événement. Vous devez vérifier le log d'erreur de l'esclave pour voir si des messages ont été générés, ou encore vérifier que tout fonctionne avec la commande SHOW SLAVE STATUS.

Depuis MySQL 4.0.2, vous pouvez ajouter les options IO_THREAD ou SQL_THREAD à la commande, pour nommer les threads que vous lancez.

Depuis MySQL 4.1.1, une clause UNTIL peut être ajoutée pour spécifier que l'esclave doit commencer à un certain point dans le log binaire, ou dans le log de relais. Lorsque le thread SQL atteint ce point, il s'arrête. Si l'option SQL_THREAD est spécifiée dans la commande, seule le thread SQL est lancé. Sinon, les deux threads sont lancés. Si le thread SQL est déjà lancé, la clause UNTIL est ignorée, et une alerte est émise.

Avec la clause UNTIL, vous devez spécifier à la fois un fichier de log et une position. Ne confondez pas les options du maître et celles du log de relais.

Toute condition UNTIL est annulée par une commande STOP SLAVE, ou une commande START SLAVE qui n'inclut pas de condition UNTIL, ou encore un redémarrage serveur.

La clause UNTIL peut être utile pour déboguer la réplication, ou pour vous assurer que la réplication s'effectue jusqu'à un certain point. Par exemple, si une commande imprudente DROP TABLE a été exécutée sur le maître, vous pouvez utiliser la clause UNTIL pour dire à l'esclave de s'exécuter jusqu'à ce moment, puis de s'arrêter. Pour trouver cet événement, utilisez l'utilitaire mysqlbinlog sur le log du maître, ou sur le log de relais, ou encore utilisez la commande SHOW BINLOG EVENTS.

Si vous utilisez la clause UNTIL pour faire des réplications par portions, il est recommandé de lancer l'esclave avec l'option --skip-slave-start pour éviter que le thread SQL ne se lance lorsque l'esclave se lance. Il est probablement idéale d'utiliser cette option dans un fichier d'options plutôt qu'en ligne de commande, pour qu'un redémarrage intempestif ne l'oublie pas.

La commande SHOW SLAVE STATUS affiche un champ qui indique la valeur courante de la clause UNTIL.

13.6.2.9. STOP SLAVE

STOP SLAVE [thread_name [, thread_name] ... ]

thread_name = IO_THREAD | SQL_THREAD

Arrête l'esclave. Tout comme START SLAVE, cette commande peut être utilisée avec les options IO_THREAD et SQL_THREAD pour identifier le thread par son nom.

Cette commande s'appelait SLAVE STOP avant MySQL 4.0.5. Actuellement, SLAVE STOP est toujours disponible pour assurer la compatibilité ascendante, mais c'est une commande abandonnée.

13.7. Syntaxe SQL pour les commandes préparées

Le support des commandes préparées coté serveur a été ajouté en MySQL 4.1. Ce support tire profit du protocole client/serveur plus efficace, en supposant que vous utilisez la bonne interface client. Les interfaces correctes sont l'API C MySQL (pour les programmes en C), et MySQL Connector/J (pour les programmes Java). Par exemple, l'API C fournit un jeu de fonctions qui prépare les commandes. See Section 24.2.4, « Fonctions C de commandes préparées ». Les autres interfaces de langages peuvent fournir un support pour les commandes préparées, en utilisant le protocole binaire grâce à l'interface du client C. L'extension PHP 5 mysqli est un exemple.

Depuis MySQL 4.1.3, une interface alternative pour les commandes préparées est disponible : la suntaxe SQL pour les commandes préparées. Cette interface n'est pas aussi efficace que le protocole binaire, mais elle n'impose aucune programmation, car elle est disponible directement au niveau SQL.

  • Vous pouvez l'utiliser lorsqu'aucune interface de programmation n'est disponible.

  • Vous pouvez l'utiliser depuis n'importe quel programme qui vous permet d'envoyer des commandes au serveur, comme le client mysql.

  • Vous pouvez l'utiliser même si le client utilise une vieille version de la bibliothèque d'interface. La seule contrainte est que vous devez pouvoir vous connecter à un serveur suffisamment récent pour supporter cette syntaxe.

La syntaxe SQL pour les commandes préparées sert dans les situations suivantes :

  • Vous voulez tester les commandes préparées avec votre application sans faire de codage. Ou bien, votre application a des problèmes avec les commandes préparées, et vous voulez déterminer ce problème interactivement.

  • Vous voulez créer un cas de test qui décrit les problèmes que vous avez avec les commandes préparées, pour pouvoir envoyer un rapport de bogue.

  • Vous deve utiliser les commandes préparées, mais vous n'avez pas accès à une interface qui les supporte.

La syntaxe SQL pour les commandes préparées est basée sur 3 commandes SQL :

PREPARE stmt_name FROM preparable_stmt;

EXECUTE stmt_name [USING @var_name [, @var_name] ...];

DEALLOCATE PREPARE stmt_name;

La commande PREPARE prépare la commande, lui assigne le nom stmt_name, qui sera utilisé ultérieurement. preparable_stmt est soit une chaîne littérale, soit une variable utilisateur, qui contient le texte de la commande. Le texte doit représenter une seule commande SQL, et non pas plusieurs. Dans la commande, le caractère ‘?’ sert de variable de requête : ils indiquent que les valeurs seront fournies à l'application ultérieurement. Le caractère ‘?’ ne doit pas être placé entre guillemets, même si vous voulez leur donner des valeurs de chaînes de caractères.

Si une commande préparée existe déjà avec le même nom, elle sera détruite implicitement avant la préparation de la nouvelle commande. Cela signifie que si la nouvelle commande contient une erreur et ne peut pas être préparée, une erreur sera retournée, et la commande aura simplement été détruite.

Le contexte d'une commande préparée est celui de la session client dans laquelle elle est créée. Les autres clients ne peuvent y accéder.

Après avoir préparé une commande, vous l'exécutez avec la commande EXECUTE, qui fait référence au nom de la commande préparée. Si la commande préparée contient des variables, vous devez fournir leur valeur avec la clause USING qui liste les variables contenant les valeurs des paramètres. Les valeurs des paramètres doivent être aussi nombreuses que les paramètres de la commande.

Vous pouvez exécuter une commande préparée plusieurs fois, en lui passant différentes valeurs, ou différentes variables.

Pour détruire une commande préparée, utilisez la commande DEALLOCATE PREPARE. Tenter d'exécuter la commande préparée après destruction conduit à une erreur.

Si vous quittez la session client sans libérer les commandes préparées, le serveur le fera pour vous.

Les exemples suivants montre deux méthodes équivalentes pour préparer les commandes qui calculent l'hypothénuse d'un triangle à partir de la taille de deux de ses cotés.

Le premier exemple montre comment créer la commande préparée avec une chaîne littérale :

mysql> PREPARE stmt1 FROM 'SELECT SQRT(POW(?,2) + POW(?,2)) AS hypotenuse';
mysql> SET @a = 3;
mysql> SET @b = 4;
mysql> EXECUTE stmt1 USING @a, @b;
+------------+
| hypotenuse |
+------------+
|          5 |
+------------+
mysql> DEALLOCATE PREPARE stmt1;

Le second exexemple est similaire, mais fournit le texte de la commande dans une variable utilisateur :

mysql> SET @s = 'SELECT SQRT(POW(?,2) + POW(?,2)) AS hypotenuse';
mysql> PREPARE stmt2 FROM @s;
mysql> SET @a = 6;
mysql> SET @b = 8;
mysql> EXECUTE stmt2 USING @a, @b;
+------------+
| hypotenuse |
+------------+
|         10 |
+------------+
mysql> DEALLOCATE PREPARE stmt2;

La syntaxe SQL des commandes préparées syntax ne peut pas être utilisée par imbrication. C'est à dire, une commande passée à PREPARE ne peut pas exécuter les commandes PREPARE, EXECUTE ou DEALLOCATE PREPARE.

De plus, la syntaxe SQL pour les commandes préparées est distincte de l'API des commandes préparées. Par exemple, vous pouvez utiliser la fonction C mysql_stmt_prepare() pour préparer une commande PREPARE, EXECUTE, or DEALLOCATE PREPARE.


This is a translation of the MySQL Reference Manual that can be found at dev.mysql.com. The original Reference Manual is in English, and this translation is not necessarily as up to date as the English version.