Par défaut, une table est créée dans la base de données courante. Une erreur est affichée s'il n'y a pas de base courante, si la base de données n'existe pas ou si la table existe déjà.

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

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

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

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

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.

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.

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

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

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

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

SELECT * FROM nom_de_table WHERE auto_col IS NULL

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

MyISAM BTREE
InnoDB BTREE
MEMORY/HEAP HASH, BTREE

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

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

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

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

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

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

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

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

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

Lorsque vous utilisez une clause ORDER BY ou GROUP BY sur une colonne de type TEXT ou BLOB, seuls, les max_sort_longueur premiers octets seront lus.

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.

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

En MySQL version 3.23.44 et plus récent, les tables InnoDB supportent la vérification de clé étrangères. 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.

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.

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

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

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

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

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

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

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

Les différents types de tables sont :
ARCHIVE : Le moteur d'archivage.
BDB : Tables avec transactions.
BerkeleyDB : Un alias de BDB.
CSV : Tables qui stockent les lignes au format valeurs séparées par des virgules.
EXAMPLE : Un moteur d'exemple.
FEDERATED : Un moteur qui accède à des tables distantes.
HEAP : Les données de ces tables ne sont stockées qu'en mémoire.
ISAM : Le gestionnaire originel de tables.
InnoDB : Tables transactionnelles avec verrou de lignes.
MEMORY : Un alias de HEAP. (En fait, depuis MySQL 4.1, MEMORY est le terme recommandé.)
MERGE : Un ensemble de tables MyISAM utilisées comme une seule et même table.
MRG_MyISAM : Un synonyme pour les tables MERGE.
MyISAM : Le nouveau gestionnaire de table binaire et portable.
NDB : Alias de NDBCLUSTER.
NDBCLUSTER : Tables en grappe et en mémoire, tolérantes aux pannes.

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

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

AUTO_INCREMENT

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

AVG_ROW_LENGTH

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

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

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.

RAID_TYPE

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

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

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

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

UNION

UNION sert lorsque vous voulez que plusieurs tables identiques se comporte comme une seule table. Cela fonctionne avec les tables MERGE.

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.

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.

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

CREATE TABLE new_tbl SELECT * FROM orig_tbl;

MySQL va créer une nouvelle colonne pour chaque colonne de résultat de la commande SELECT.
Par exemple :
mysql> CREATE TABLE test (a INT NOT NULL AUTO_INCREMENT,
-> PRIMARY KEY (a), KEY(b))
-> TYPE=MyISAM SELECT b,c FROM test2;

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

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

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

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

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

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

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

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

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