Manuel de référence MySQL 5.0

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

Syntaxe multitable :

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. Voir Section 13.1.9, « Syntaxe de TRUNCATE »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émarre à zéro pour tous les types de tables 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 15.7.3, « Comment les colonnes AUTO_INCREMENT fonctionnent avec InnoDB »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é multicolonne. Dans ce cas, la réutilisation des clés à partir de la fin de la séquence se fait aussi pour les tables MyISAM. Voir Section 3.6.9, « Utiliser AUTO_INCREMENT »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 option 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 suppression peut être affectée par les facteurs présentés dans la Section 7.2.16, « Rapidité des requêtes DELETE »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 »13.5.2.5. Syntaxe de OPTIMIZE TABLE et Section 5.7.3.10, « Optimisation de table »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 lignes à 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érieur à la valeur de LIMIT.

Si la commande DELETE inclut la clause ORDER BY, les lignes sont effacées dans l'ordre spécifié par cette clause. Elle n'est vraiment utile 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 multitable.

La première syntaxe de DELETE multitable est supportée depuis MySQL 4.0.0. La deuxième syntaxe de DELETE multitable 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 13.1.7.1, « Syntaxe de JOIN »13.1.7.1. Syntaxe de JOIN.

L'idée est que seules les lignes concordantes dans les tables énumérées avant le FROM ou avant la clause USING sont effacées. 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ésentent des jointures internes, en utilisant l'opérateur virgule, mais les DELETE multitables 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 multitable 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és à ne pas faire ce changement en version 4.0, est la compatibilité ascendante avec les vieilles applications 4.0, qui utilisent la vieille syntaxe.

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.

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. Cet 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. Voir Section 13.1.7, « Syntaxe de SELECT »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 les suivantes.

• 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 perte 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 traverser 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.

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ée à 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étail : Voir Section 13.1.4.1, « Syntaxe de INSERT ... SELECT »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 où la clause SET indique les colonnes qui seront affectées.

• La liste des noms de colonnes où 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.

• À 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 table, 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 »13.2.5. Syntaxe de CREATE TABLE.

  MySQL a toujours une valeur par défaut pour chaque champ. 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 par 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. Voir Section 2.4.2, « Options habituelles de configure »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 est vide, INSERT crée une ligne pour chaque colonne avec sa valeur par défaut :

   

  Sélectionnez

  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 :

   

  Sélectionnez

  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 :

   

  Sélectionnez

  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. Voir Section 13.1.4.2, « Syntaxe de INSERT DELAYED »13.1.4.2. Syntaxe de INSERT DELAYED.

• Si on spécifie le mot LOW_PRIORITY, l'exécution de INSERT sera retardée 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. Voir Section 13.1.4.2, « Syntaxe de INSERT DELAYED »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. Voir Section 14.1, « Le moteur de tables MyISAM »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 ferait doublon avec une clé PRIMARY ou UNIQUE existant dans la table sera ignorée 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 même façon dans toutes les circonstances. Le comportement des commandes INSERT avec les colonnes AUTO_INCREMENT sont décrites dans la Section 12.8.3, « Fonctions d'informations »12.8.3. Fonctions d'informations et Section 24.2.3.33, « mysql_insert_id() »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 lignes qui ont été traitées par cette commande. Ce n'est pas forcément le nombre de lignes 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 colonne. 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(). Voir Section 24.2.3.31, « mysql_info() »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 ...

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

13-1-4-2. Syntaxe de INSERT DELAYED▲

INSERT DELAYED ...

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 »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 fait que mysqlimport lit les fichiers de données chez le client. Vous pouvez spécifier l'option --compress pour avoir de meilleures performances avec les connexions lentes si le client et le serveur supportent le protocole compressé. Voir Section 8.10, « mysqlimport, importer des données depuis des fichiers texte »8.10. mysqlimport, importer des données depuis des fichiers texte.

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

Si vous spécifiez le mot-clé CONCURRENT avec une 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écents.

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 lisibles par tous. Pour utiliser la commande LOAD DATA INFILE sur des fichiers du serveur, vous devez avoir le droit de FILE sur le serveur. Voir Section 5.5.3, « Droits fournis par MySQL »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 côté, 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és pour. Par exemple, si mysqld a été lancé avec --local-infile=0, LOCAL ne fonctionnera pas. Voir Section 5.4.4, « Problèmes de sécurité avec LOAD DATA LOCAL »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. Voir Section 13.1.6, « Syntaxe de REPLACE »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. Voir Section 5.7.3, « Utilisation de myisamchk pour la maintenance des tables et leur recouvrement »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. Voir Section 13.1.7, « Syntaxe de SELECT »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 pas de clause FIELDS, les valeurs par défaut sont :

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

Si vous ne spécifiez pas 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 champ.

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

• Écriture des tabulations entre les champs ;

• Les champs ne sont pas entourés de guillemets ;

• Utilisation de '\' pour échapper les occurrences de tabulation, nouvelle ligne, et '\' trouvées dans les valeurs ;

• Insertion d'une nouvelle ligne entre les lignes.

Notez que pour utiliser FIELDS ESCAPED BY '\\', vous devez spécifier deux antislashes pour que cette valeur soit interprétée comme un antislash 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 un 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 a é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 ne 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 champ sont échappées 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. 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és, 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 :

• le 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 »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 multioctets.

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 colonnes 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 :

   

  Sélectionnez

  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 »13.2.5. Syntaxe de CREATE TABLE.

Une valeur de champ 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. Voir Section 11.3, « Les types date et heure »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ées 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 (voir Section 13.1.4, « Syntaxe de INSERT »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. Voir Section 13.5.3.19, « SHOW WARNINGS | ERRORS »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 : voir Section 7.2.14, « Vitesse des requêtes INSERT »7.2.14. Vitesse des requêtes INSERT.

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. Voir Section 13.1.4, « Syntaxe de INSERT »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'était 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 une 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ée 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.

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 disponible depuis MySQL 4.0 et 4.1, respectivement. Voir Section 13.1.7.2, « Syntaxe de UNION »13.1.7.2. Syntaxe de UNION et Section 13.1.8, « Sous-sélections (SubSELECT) »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 »13.1.7.1. Syntaxe de JOIN.

• where_definition indique les conditions auxquelles 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 :

   

  Sélectionnez

  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 :

   

  Sélectionnez

  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 :

   

  Sélectionnez

  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. Voir Section A.5.4, « Problèmes avec les alias »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 »13.1.7.1. Syntaxe de JOIN. Pour chaque table spécifiée, vous pouvez éventuellement indiquer un alias.

   

  Sélectionnez

  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 »13.1.7.1. Syntaxe de JOIN.

  En MySQL 4.0.14, vous pouvez utiliser SET MAX_SEEKS_FOR_KEY=value comme une autre solution 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é 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 ambigüe. Consultez Section 9.2, « Noms de bases, tables, index, colonnes et alias »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é à spécifier DUAL comme nom de table, dans les situations où aucune table n'est référencée. C'est une fonctionnalité pure de compatibilité, car certains autres serveurs requièrent cette syntaxe.

   

  Sélectionnez

  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 :

   

  Sélectionnez

  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. Voir Chapitre 12, Fonctions à utiliser dans les clauses SELECT et WHEREChapitre 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 :

   

  Sélectionnez

  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 :

   

  Sélectionnez

  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. Voir Section 12.9, « Fonctions et options à utiliser dans les clauses GROUP BY »12.9. Fonctions et options à utiliser dans les clauses GROUP BY.

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

• La clause HAVING peut faire référence à n'importe quel champ ou alias définis 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 :

   

  Sélectionnez

  mysql> SELECT nom_de_colonne FROM nom_de_table HAVING nom_de_colonne > 0;

  Écrivez plutôt cela :

   

  Sélectionnez

  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 :

   

  Sélectionnez

  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 :

   

  Sélectionnez

  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 :

   

  Sélectionnez

  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 :

   

  Sélectionnez

  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'enregistrements à 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.

   

  Sélectionnez

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

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

   

  Sélectionnez

  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 :

   

  Sélectionnez

  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éé sur le serveur et ne peut y être déjà présent (cela permet entre autres d'éviter la destruction des tables et de fichiers tels que /etc/passwd). Vous devez avoir le droit FILE sur le serveur pour utiliser cette forme de SELECT.

  SELECT ... INTO OUTFILE a 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. Voir Section 13.1.5, « Syntaxe de LOAD DATA INFILE »13.1.5. Syntaxe de LOAD DATA INFILE.

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

  • le caractère ESCAPED BY ;

  • les premiers caractères de FIELDS TERMINATED BY ;

  • les premiers caractères de LINES TERMINATED BY ;

  • ASCII 0 est converti 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 si 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 pas besoin d'échapper autre chose.

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

   

  Sélectionnez

  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éés par INTO OUTFILE et INTO DUMPFILE seront lisibles par tous 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 mots lisibles 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 »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 ligne ou de page, 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 indiquent que les doublons doivent être éliminés du résultat.

HIGH_PRIORITY, STRAIGHT_JOIN, et les options commençant 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. Voir Section 7.2.1, « Syntaxe de EXPLAIN (Obtenir des informations sur les SELECT) »7.2.1. Syntaxe de EXPLAIN (Obtenir des informations sur les SELECT). STRAIGHT_JOIN peut aussi être utilisée dans la liste table_references. Voir Section 13.1.7.1, « Syntaxe de JOIN »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 important.

• 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érieures) 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(). Voir Section 12.8.4, « Fonctions diverses »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 ligne). Voir Section 7.2.12, « Comment MySQL optimise LIMIT »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. Voir Section 5.11, « Cache de requêtes MySQL »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). Voir Section 5.11, « Cache de requêtes MySQL »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▲

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

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

ON expr_conditionnelle |
USING (column_list)

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;

13-1-7-2. Syntaxe de UNION▲

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

(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;

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

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

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 les suivantes.

• Elles permettent aux requêtes d'être structurées 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. Voir Section 13.1.8.11, « Se passer des sous-requêtes avec les premières versions de MySQL »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▲

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

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

SELECT (SELECT s1 FROM t2) FROM t1;

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

13-1-8-2. Comparaisons avec les sous-requêtes▲

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

= > < >= <= <>

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

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

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

13-1-8-3. Sous-requêtes avec les clauses ANY, IN et SOME▲

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

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

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

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

13-1-8-4. Sous-requêtes avec ALL▲

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

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

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

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

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

13-1-8-5. Sous-requêtes de ligne▲

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

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

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▲

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

13-1-8-7. Sous-requêtes corrélées▲

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

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));

val IN (SELECT key_val FROM tbl_name WHERE correlated_condition)

13-1-8-8. Sous-requêtes dans la clause FROM▲

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

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

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;

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

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

13-1-8-9. Erreurs de sous-requêtes▲

13-1-8-10. Optimisation des sous-requêtes▲

13-1-8-11. Se passer des sous-requêtes avec les premières versions de MySQL▲

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

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

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);

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

TRUNCATE TABLE nom_de_table

Dans la version 3.23, TRUNCATE TABLE est équivalent à COMMIT ; DELETE FROM nom_de_table. Voir Section 13.1.1, « Syntaxe de DELETE »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éfinitions nom_de_table.frm est valide, la table peut être recréée, même si les données ou un index ont été corrompus.

• Le gestionnaire de tables 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.

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

Syntaxe multitable :

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 table 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-clé LOW_PRIORITY, l'exécution de l'UPDATE sera repoussé jusqu'à ce qu'aucun client ne lise plus de la table.

• Si vous spécifiez le mot-clé IGNORE, la mise à jour ne s'interrompra pas même si on rencontre des problèmes d'unicité de clés durant l'opération. Les enregistrements posant problème 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 colonne â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 utiliser le code LIMIT # pour vous assurer que seul un nombre d'enregistrements bien précis sont changés.

• Avant MySQL 4.0.13, LIMIT est une restriction 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 aient été changées ou pas.

Si 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 multitable 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 multitables.

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 UnicodeChapitre 10. Jeux de caractères et Unicode.

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

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 similaire aux clauses de la commande CREATE TABLE. Voir Section 13.2.5, « Syntaxe de CREATE TABLE »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écrites dans Section 13.2.5.1, « Modification automatique du type de colonnes »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é, 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 clé 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. Voir Section 13.2.9, « Syntaxe de RENAME TABLE »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 Voir Section 13.2.5, « Syntaxe de CREATE TABLE »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 :

   

  Sélectionnez

  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 :

   

  Sélectionnez

  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 :

   

  Sélectionnez

  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.

• À 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. À 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 »13.2.5. Syntaxe de CREATE TABLE.

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

• Si des colonnes sont effacées d'une table, ces colonnes sont aussi supprimées 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 clé primaire. Si cette clé n'existe pas, cette commande effacera le premier index UNIQUE de la table. (MySQL marque la première clé 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 clés une par une, désactiver les clés 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 ... (...). Voir Section 15.7.4, « Contraintes de clés étrangères FOREIGN KEY »15.7.4. Contraintes de clés étrangères FOREIGN KEY. Pour les autres moteurs de stockage, ces clauses sont lues, mais ignorées. La clause CHECK est analysée, mais ignorée par tous les moteurs de stockage. Voir Section 13.2.5, « Syntaxe de CREATE TABLE »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. Voir Section 1.5.5, « Différences entre MySQL et le standard SQL-92 »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 :

   

  Sélectionnez

  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 »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 :

   

  Sélectionnez

  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. Voir Section 10.10, « Préparer le passage de version 4.0 en 4.1 »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 :

   

  Sélectionnez

  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 :

   

  Sélectionnez

  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écents, 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 :

   

  Sélectionnez

  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 :

   

  Sélectionnez

  ALTER TABLE tbl_name IMPORT TABLESPACE;

  Voir Section 15.7.6, « Espaces de tables multiples : chaque table InnoDB a son fichier .ibd »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 clé unique. Voir Section 24.2.3.31, « mysql_info() »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 clé 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. Voir Section 13.5.2.8, « Syntaxe de SET »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'autoincrémentation ne sera pas affecté. Si vous effacez une colonne AUTO_INCREMENT puis en ajoutez une autre, l'indexation recommencera à partir de 1.

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

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ées sont présentées dans la Section 9.2, « Noms de bases, tables, index, colonnes et alias »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és dans le Chapitre 10, Jeux de caractères et UnicodeChapitre 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. Voir Section 8.4, « mysqladmin, administration d'un serveur MySQL »8.4. mysqladmin, administration d'un serveur MySQL.

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. Voir Section 13.2.2, « Syntaxe de ALTER TABLE »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. Voir Section 13.2.5, « Syntaxe de CREATE TABLE »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 multicolonne. 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 peuvent ê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érieures 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 »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 »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écents. Les colonnes spatiales sont présentées dans le Chapitre 18, Données spatiales avec MySQLChapitre 18. Données spatiales avec MySQL.

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 »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écents, 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 celui 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écents, 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ée si la table que vous essayez de créer existe déjà. Notez qu'il n'y a pas de comparaison 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 :

Les fichiers créés par les moteurs de stockage pour représenter les tables sont décrits dans le Chapitre 14, Moteurs de tables MySQL et types de tableChapitre 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 colonnesChapitre 11. Types de colonnes. Pour des informations sur les types de données spatiaux, voyez Chapitre 18, Données spatiales avec MySQLChapitre 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 nombres entiers 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. Voir Section 24.2.3.33, « mysql_insert_id() »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. Voir Section 5.2.1, « Options de ligne de commande de mysqld »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 passent 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 à 1.

  Avec les tables MyISAM et BDB, vous pouvez spécifier une colonne secondaire d'AUTO_INCREMENT dans une clé multicolonne. Voir Section 3.6.9, « Utiliser AUTO_INCREMENT »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 :

   

  Sélectionnez

  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 UnicodeChapitre 10. Jeux de caractères et Unicode.

   

  Sélectionnez

  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ètent 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 utilisez 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 la 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. Voir Section 11.3, « Les types date et heure »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é 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. Elle était autorisée, mais ignorée 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 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 toutes les colonnes utilisées doivent 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 uniques. 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 multicolonne. Cependant, vous ne pouvez pas créer d'index multicolonne 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 établie 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. Voir Section 13.5.3.6, « Syntaxe de SHOW DATABASES »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 stockage sont listées 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 table	Types d'index
  MyISAM	BTREE
  InnoDB	BTREE
  MEMORY/HEAP	HASH, BTREE

  Exemple :

   

  Sélectionnez

  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 optionnel 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. Voir Section 7.4.3, « Index de colonnes »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 :

   

  Sélectionnez

  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ée 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 multioctet.

• 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. Voir Section 11.4.3, « Les types BLOB et TEXT »11.4.3. Les types BLOB et TEXT.

• En MySQL version 3.23.23 ou plus récentes, vous pouvez aussi créer des index spéciaux 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 »12.6. Recherche en texte intégral (Full-text) dans MySQL pour les détails.

• En MySQL version 4.1 ou plus récentes, 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 MySQLChapitre 18. Données spatiales avec MySQL.

• En MySQL version 3.23.44 et plus récentes, les tables InnoDB supportent la vérification de clés étrangères. Voir Chapitre 15, Le moteur de tables InnoDBChapitre 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 »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. Voir Section 1.5.5.5, « Les clés étrangères »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 :

   

  Sélectionnez

  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érence 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écents.

Les options ENGINE et TYPE spécifient 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 :

Voir Chapitre 14, Moteurs de tables MySQL et types de tableChapitre 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éez 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 de petite 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. Voir Section 5.2.3, « Variables serveur système »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. Voir Section 14.1.3, « Formats de table MyISAM »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 comportent comme une seule table. Cela fonctionne avec les tables MERGE. Voir Section 14.2, « Tables assemblées MERGE »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. Voir Section 14.2, « Tables assemblées MERGE »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. Voir Section 7.6.1.2, « Utiliser les liens symboliques avec les tables sous Unix »7.6.1.2. Utiliser les liens symboliques avec les tables sous Unix.