CREATE [TEMPORARY] TABLE [IF NOT EXISTS] nom_de_table [(definition_de_create,...)]
[options_de_table] [select_statement]
definition_de_create:
nom_de_colonne type [NOT NULL | NULL] [DEFAULT valeur_par_defaut]
[AUTO_INCREMENT]
[PRIMARY KEY] [definition_de_reference]
ou PRIMARY KEY (index_col_name,...) ou KEY [nom_index] (index_col_name,...) ou INDEX [nom_index] (index_col_name,...)
ou UNIQUE [INDEX] [nom_index] (index_col_name,...) ou FULLTEXT [INDEX] [nom_index] (index_col_name,...)
ou [CONSTRAINT symbol] FOREIGN KEY [nom_index] (index_col_name,...) [reference_definition]
ou CHECK (expr) type:
TINYINT[(longueur)] [UNSIGNED] [ZEROFILL]
ou SMALLINT[(longueur)] [UNSIGNED] [ZEROFILL]
ou MEDIUMINT[(longueur)] [UNSIGNED] [ZEROFILL]
ou INT[(longueur)] [UNSIGNED] [ZEROFILL]
ou INTEGER[(longueur)] [UNSIGNED] [ZEROFILL]
ou BIGINT[(longueur)] [UNSIGNED] [ZEROFILL]
ou REAL[(longueur,décimales)] [UNSIGNED] [ZEROFILL]
ou DOUBLE[(longueur,décimales)] [UNSIGNED] [ZEROFILL]
ou FLOAT[(longueur,décimales)] [UNSIGNED] [ZEROFILL]
ou DECIMAL(longueur,décimales) [UNSIGNED] [ZEROFILL]
ou NUMERIC(longueur,décimales) [UNSIGNED] [ZEROFILL]
ou CHAR(longueur) [BINARY]
ou VARCHAR(longueur) [BINARY]
ou DATE ou TIME ou TIMESTAMP ou DATETIME ou TINYBLOB ou BLOB
ou MEDIUMBLOB ou LONGBLOB ou TINYTEXT ou TEXT
ou MEDIUMTEXT ou LONGTEXT
ou ENUM(valeur1,valeur2,valeur3,...) ou SET(valeur1,valeur2,valeur3,...) index_nom_de_colonne:
nom_de_colonne [(longueur)]
definition_de_reference:
REFERENCES nom_de_table [(index_nom_de_colonne,...)]
[MATCH FULL | MATCH PARTIAL]
[ON DELETE option_de_reference]
[ON UPDATE option_de_reference]
option_de_reference:
RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT options_de_table:
TYPE = {BDB | HEAP | ISAM | InnoDB | MERGE | MRG_MYISAM | MYISAM } ou AUTO_INCREMENT = #
ou AVG_ROW_LENGTH = # ou CHECKSUM = {0 | 1}
ou COMMENT = "phrase"
ou MAX_ROWS = # ou MIN_ROWS = #
ou PACK_KEYS = {0 | 1 | DEFAULT}
ou PASSWORD = "mot"
ou DELAY_KEY_WRITE = {0 | 1}
ou ROW_FORMAT= { default | dynamic | fixed | compressed }
ou RAID_TYPE= {1 | STRIPED | RAID0 } RAID_CHUNKS=# RAID_CHUNKSIZE=#
ou UNION = (nom_de_table,[nom_de_table...]) ou INSERT_METHOD= {NO | FIRST | LAST }
ou DATA DIRECTORY="chemin absolu vers dossier"
ou INDEX DIRECTORY="chemin absolu vers dossier"
select_statement:
[IGNORE | REPLACE] SELECT ... (une clause de séléction valide) CREATE TABLE Crée une table avec le nom donné, dans la base de données courante. Une erreur est affichée s'il n'y a pas de base courante, ou si la table existe déjà. Depuis la version 3.22 de mySQL, vous pouvez utiliser le mot réservé TEMPORARY lorsque vous créez une table. Une table temporaire sera immédiatement effacée dès que la connexion se termine. Cela signifie que vous pouvez utiliser le même nom de table temporaire depuis deux connexions différentes sans risque de conflit entre les connexions. Vous pouvez aussi utiliser une table temporaire qui a le même nom qu'une table existante (la table existante est alors cachée tant que dure la table temporaire). En mySQL version 4.0.2 ou plus récent, vous avez juste à avoir le privilège CREATE TEMPORARY TABLES pour créer des tables temporaires. Depuis la version 3.23 de mySQL, vous pouvez utiliser le mot réservé IF NOT EXISTS, de façon à ce qu'aucune erreur ne soit affiché si la table que vous essayez de créer existe déjà. Notez qu'il n'y a pas de comparaisons entre les structures de table lors du test d'existence. Chaque table nom_de_table est representée par des fichiers dans le dossier de la base de données. Dans le cas des tables de type MyISAM, ce sont les fichiers suivants :
Fichier Rôle
nom_de_table.frm Fichier de définition de la table nom_de_table.MYD Fichier de données
nom_de_table.MYI Fichier d'index
• Si ni NULL, ni NOT NULL n'est spécifié, une colonne utilisera par défaut l'attribut NULL (elle acceptera les valeurs NULL).
• Une colonne de nombre entier peut se voir attribuer l'attribut AUTO_INCREMENT. Lorsque vous insérez la valeur NULL (recommandée) ou 0 dans une colonne AUTO_INCREMENT, la colonne prendra automatiquement la valeur de value+1, où value est la plus grande valeur positive courante dans cette colonne. La série des valeurs AUTO_INCREMENT commence à 1. 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 à zéro. 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 inserer un nombre de très grande taille, mais
positif. Ceci est fait pour éviter les problèmes de précision lorsque les nombres passe de positif à négatif lorsqu'ils atteignent leur valeur maximale positive. C'est aussi pour éviter qu'une colonne de type AUTO_INCREMENT ne contienne de valeur 0. En MyISAM et tables BDB, vous pouvez spécifier une colonne secondaire d'AUTO_INCREMENT dans une clef multi-colonne. Pour rendre mySQL avec certaines applications ODBC, vous pouvez retrouver la valeur de la dernière valeur automatiquement générée avec la requête suivante : SELECT * FROM nom_de_table WHERE auto_col IS NULL
• CREATE TABLE effectue automatiquement la transaction courante d'InnoDB si le mySQL binlogging est employé.
• La valeur NULL est traitée différemment dans les colonnes de type TIMESTAMP. Vous ne pouvez pas stocker de valeur NULL littérale dans une colonne TIMESTAMP; insérer une valeur NULL dans une telle colonne revient à insérer la date et l'heure courante. Car les colonnes TIMESTAMP ignorent les attributs NULL et NOT NULL. Cela facilite grandement l'utilisation des colonnes TIMESTAMP pour les clients mySQL : le serveur indique que ces colonnes peuvent se voir assigner une valeur NULL (ce qui est vrai), même si les colonnes TIMESTAMP ne contiendront jamais de valeur NULL. Vous pouvez le constater lorsque vous utiliser la commande DESCRIBE nom_de_table pôur 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.
Si aucun valeur par défaut (attribut DEFAULT) n'est spécifié, mySQL en assigne une automatiquement Si la colonne accepte les valeur NULL, la valeur par défaut sera la valeur NULL. Si la colonne est déclarée comme NOT NULL (non-nulle), la valeur par défaut dépendra du type de colonne :
• Pour les types numériques sans l'attribut AUTO_INCREMENT, la valeur sera 0. Pour une colonne AUTO_INCREMENT, la valeur par défaut sera la prochaine valeur de la série.
• Pour les types dates et heures autres que TIMESTAMP, la valeur par défaut est la date zéro appropriée. Pour les colonnes TIMESTAMP, la valeur par défaut est la date et l'heure courante.
• DEFAULT directive). Pour les chaînes autres que ENUM, la valeur par défaut est la chaîne vide. Pour les valeurs de type ENUM, la valeur par défaut est le premier élément de l'énumération.
• KEY est un synonyme de INDEX.
• Les valeurs par défaut doivent être des constantes. Cela signifie, par exemple, que vous ne pouvez pas donner de valeur par défaut en fonction de NOW() ou CURRENT_DATE.
• Une clé primaire (PRIMARY KEY) est un index UNIQUE avec la contrainte supplémentaire que les toutes les colonnes utilisées doit avoir l'attribut NOT NULL. En mySQL, cette clé est dite PRIMARY. Une table ne peut avoir qu'une seule clé primaire. Si vous n'avez pas de PRIMARY KEY et que des applications demandent la PRIMARY KEY dans vos tables, mySQL retournera la première clé UNIQUE, qui n'a aucune valeur NULL.
• Une PRIMARY KEY peut être multi-colonne. Cependant, vous ne pouvez pas créer d'index multi-colonne avec l'attribut PRIMARY KEY dans une spécification de colonne. En faisant cela, le seul résultat sera que cette seule colonne sera marquée comme clé primaire. Vous devez absolument utiliser la syntaxe PRIMARY KEY (index_nom_de_colonne, ...).
• Si une clé primaire (PRIMARY) ou unique (UNIQUE) est établit sur une seule colonne, et que cette colonne est de type entier, vous pouvez aussi faire référence à cette colonne sous le nom _rowid (nouveau en version 3.23.11).
• 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 nom_de_table.
• Seul, 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.
• Seul le format de table MyISAM supporte l'indexation des colonnes BLOB et TEXT. Colonnes TEXT. Lorsque vous ajoutez un index à une colonne BLOB ou TEXT, vous devez ABSOLUMENT spécifier une longueur d'index :
CREATE TABLE test (blob_col BLOB, INDEX(blob_col(10)));
• 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.
• En mySQL version 3.23.23 ou plus récent, vous pouvez aussi créer des index spécial FULLTEXT. Ils sont utilisés pour faire des recherches en texte plein. Seul, le format de table MyISAM supporte les index FULLTEXT. Ils peuvent être créés uniquement pour les colonnes de type VARCHAR et TEXT. L'indexation est alors exécutée sur toute la colonne, et les indexations partielles ne sont pas supportées.
• Chaque colonne NULL requiert un bit supplémentaire, arrondi à l'octet supérieur le plus proche.
• La taille maximale d'enregistrement peut être calculée comme ceci : row longueur = 1
+ (somme des longueurs de colonnes) + (nombre de colonnes NULL + 7)/8
+ (nombre de colonnes à taille variable)
• Les options options_de_table et SELECT ne sont implémentées que dans mySQL version 3.23 et plus récent. Les différents types de tables sont :
Table type Description
BDB or BerkeleyDB Tables avec transactions.
HEAP Les données de ces tables ne sont stockées qu'en mémoire.
ISAM Le gestionnaire originel de tables.
InnoDB Transaction-safe tables with row locking.
MERGE Un emsemble de tables MyISAM utilisées comme une seule et même table.
MRG_MyISAM Un synonyme pour MERGE les tables.
MyISAM Le nouveau gestionnaire de table binaire et portable.
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) :
Option Description
AUTO_INCREMENT La prochaine valeur auto_increment de votre table (MyISAM)..
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.
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) (MyISAM).
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 Passez 1 si vous voulez un index plus compact. Cela rend les mises à jour plus lente, mais les lectures plus rapides (MyISAM, ISAM).
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 Passez 1 si vous voulez attendre la fermeture de la table pour mettre à jour les index.
ROW_FORMAT
Definit la méthode de stockage des lignes (réservé pour le futur).
Actuellement, cette option fonctionne uniquement avec des tables MySAM qui supportent le DYNAMIC et FIXED en format de ligne.
Lorsque vous utilisez une table MyISAM, mySQL utilise le produit max_rows * avg_row_longueur pour décider de la taille de la table. Si vous ne spécifiez pas ces options, la taille maximum sera 4Go (ou 2Go si votre système d'exploitation ne supporte que les tables de 2 Go). La raison de cette option est le choix des tailles de pointeurs d'index : plus la table sera petite, plus les index seront petits, et rapides à lire. Si vous n'utilisez pas l'option PACK_KEYS, l'option par défaut est de ne compacter que les chaînes, et pas les nombres. Si vous passez PACK_KEYS=1, les nombres seront aussi compactés. Lorsque vous compactez des clés binaires numériques, mySQL utilisera la compression par préfixe. Cela signifie que vous n'y aurez vraiment intérêt, que si beaucoup de nombres sont identiques. La compression par préfixe utilise un octet de plus pour chaque clé, pour indiquer le nombre d'octets de la clé courante, identique à la clé précédente (notez que le pointeur de ligne est stocké au format bigendian (les premiers bits ont le plus de poids), pour améliorer la taux de compression). Cela signifie que si vous avez plusieurs clés de la même valeurs sur des lignes consécutives, les clés ne prendront que 2 octets (y compris le pointeur de ligne).
Faites vous-même la comparaison avec la méthode standard, où la clé suivante occupe storage_size_for_key + pointer_size (généralement 4). D'un autre coté, si toutes vos clés sont totalement différente, vous perdre un autre octet par clé (si la clé ne peut avoir de valeur NULL : dans ce cas, la taille de la clé compactée sera stockée dans le même octet qui indique si la clé est NULL.)
• Si vous spécifiez une clause SELECT dans une commande CREATE STATEMENT, mySQL créera de nouveaux champs pour tous les éléments du SELECT. Par exemple:
mysql> CREATE TABLE test (a INT NOT NULL AUTO_INCREMENT, -> PRIMARY KEY (a), KEY(b))
-> TYPE=MyISAM SELECT b,c FROM test2;
Cette ligne va créer une table MySAM de 3 colonnes. Notez que cette table sera automatiquement supprimée si une erreur survient durant la copie des données dans la table.
Voyez cet exemple:
mysql> SELECT * FROM foo;
+---+
| n | +---+
| 1 | +---+
mysql> CREATE TABLE bar (m INT) SELECT n FROM foo;
Query OK, 1 row affected (0.02 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> SELECT * FROM bar;
+---+---+
| m | n | +---+---+
| NULL | 1 | +---+---+
1 row in set (0.00 sec)
Pour chaque ligne dans la table foo, une ligne est insérée dans bar avec la valeur de foo et les valeurs par défaut pour les nouvelles colonnes. CREATE TABLE ... SELECT ne créera pas d'index automatiquement pour vous. Cela est intentionnel, pour rendre la commande aussi flexible que possible. Si vous voulez avoir des index dans la table créée, vous devez le spécifier avant la commande SELECT :
mysql> CREATE TABLE bar (UNIQUE (n)) SELECT n FROM foo;
Si une erreur survient durant la copie des données dans la table, elle sera automatiquement effacée. Pour d'assurer que les journaux des modifications ou les journaux binaires puissent être utilisés pour re-créer les tables originales, mySQL n'autorise pas les insertions concurrents pendant CREATE TABLE ... SELECT.
• L'option RAID_TYPE vous aidera à passer outre la limite de 2Go/4Go pour les fichiers MyISAM (mais pas le fichier d'index), sur les systèmes d'exploitation qui ne supportent pas les grands fichiers. Il est possible d'accélérer le goulet d'étranglement des I/O en disposant les répertoires RAID sur différents disques physiques. RAID_TYPE fonctionne sur tous les OS, à condition d'avoir configuré mySQL avec --with-raid. Actuellement, le seul RAID_TYPE autorisé est STRIPED (1 et RAID0 sont des redirections vers celui ci). Si vous spécifiez RAID_TYPE=STRIPED pour une table MyISAM, MyISAM va créer des sous-dossiers RAID_CHUNKS nommés 00, 01, 02 dans le dossier de la base de données. Dans chacun de ces dossiers, MyISAM va créer un fichier nom_de_table.MYD. Lorsqu'il écrira dans le fichier, le gestionnaire RAID placera les RAID_CHUNKSIZE *1024 premiers octets dans le premier fichier, et les RAID_CHUNKSIZE *1024 octets suivants dans le fichier suivant.
• UNION sert lorsque vous voulez utiliser un ensemble de tables comme une seule table. Cela ne fonctionne qu'avec les tables MERGE. Actuellement, vous devez avoir les droits de SELECT, UPDATE, et DELETE sur ces tables pour les consolider en une seule table MERGE. Toutes les tables doivent être de la même base que la table consolidée.
• Si vous voulez insérer des données dans une table MERGE, vous devez spécifier avec INSERT_METHOD. Cette option a été introduite dans mySQL 4.0.0.
• Dans la table ainsi créée, la clé primaire PRIMARY sera placée en premier, suivie des clés uniques et des clés standard. Cela aide l'optimiseur mySQL à utiliser les clés dans l'ordre de priorité, et à détecter les clés doubles.
• En utilisant DATA DIRECTORY="directory" ou INDEX DIRECTORY="directory"
vous pouvez spécifier ou le gestionnaire de la table doit mettre la table et son index. Notez que le chemin doit être complet. Pas de chemin relatif. Cela fonctionne uniquement dans les
tables MyISAM en mySQL 4.0, quand vous avez pas utilisé l'option --skip-symlink. (1) Modification automatique du type de colonnes
Dans certains cas, mySQL change automatiquement la spécification d'une colonne fournie dans la commande CREATE TABLE. (Cela peut aussi arriver avec ALTER TABLE) :
• Les colonnes VARCHAR avec un taille inférieure à quatre (4) sont changées en CHAR.
• Si l'une des colonnes d'une table est de taille variable, toute la ligne est, par conséquent, de taille variable. Ansi, si une ligne contient une colonne de taille variable (VARCHAR, TEXT ou BLOB) toutes les colonnes CHAR de plus de trois caractères sont transoformées en VARCHAR. Cela ne change en rien la façon dont vous utilisez les colonnes. Pour mySQL, VARCHAR est simplement une autre façon de stocker les caractères. mySQL effectue cette conversion car cela économise de la place, et rend les calculs sur les tables plus rapides.
• La taille d'affichage de TIMESTAMP doit être un nombre pair et être compris entre 2 et 14.
(2, 4, 6, 8, 10, 12 ou 14). Si vous spécifiez une taille plus grande que 14, ou inférieure à 2, celle-ci sera tranformée en 14. Les valeurs impaires sont rammenées à la valeur pair supérieure la plus proche.
• # Vous ne pouvez pas stocker de valeur littérale NULL dans une colonne de type TIMESTAMP. Cette valeur sera remplacée par la date et l'heure courante. De ce fait, les attributs NULL et NOT NULL n'ont pas de sens pour ces colonnes et sont ignorés. DESCRIBE nom_de_table indiquera toujours que la colonne TIMESTAMP accepte les valeurs NULL.
• mySQL change certains types de colonnes utilisés par d'autres serveurs SQL en types mySQL.
Si vous voulez voir si mySQL a utilisé un autre type que celui que vous avez spécifié, utilisez la commande DESCRIBE nom_de_table, après votre création ou modification de structure de table.
Certains types de colonnes peuvent être modifiés si vous compressez une table en utilisant l'utilitaire myisampack.