7.6 CREATE TABLE

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] Nom_table (create_definition,...)
[options_de_table] [commande_de_selection]

create_definition:
  Nom_col type [NOT NULL | NULL] [DEFAULT valeur_par_defaut] [AUTO_INCREMENT]
            [PRIMARY KEY] [reference_definition]
  ou    PRIMARY KEY (index_Nom_col,...)
  ou    KEY [Nom_index] KEY(index_Nom_col,...)
  ou    INDEX [Nom_index] (index_Nom_col,...)
  ou    UNIQUE [INDEX] [Nom_index] (index_Nom_col,...)
  ou    [CONSTRAINT symbole] FOREIGN KEY Nom_index(index_Nom_col,...)
            [reference_definition]
  ou    CHECK (expression )

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(value1,value2,value3,...)
  ou    SET(value1,value2,value3,...)

index_Nom_col:
        Nom_col [(longueur)]

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

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

options_de_table:
type = [ISAM | MYISAM | HEAP]

ouauto_increment = #
ouavg_row_longueur = #
ouchecksum = [0 | 1]
oucomment = "string"
oulignes_max= #
oulignes_min = #
oupack_keys = [0 | 1]
oupassword= "string"
commande_de_selection:
[ | IGNORE | REPLACE] SELECT ...  (Un select valide)

CREATE TABLE crée une table, de nom Nom_table, dans la base de donnés courante. Les règles qui régissent les nom de table sont détaillées dans 7.1.5 Noms de base de données, table, index, column et alias. MySQL retourne une erreur si il n'y a pas de base de données courante, ou la table existe déjà.

Avec MySQL 3.22 ou plus récent, on peut se référer à la table en utilisant la structure Nom_bdd.Nom_table. Ceci fonctionne, qu'il y ait une base de données courante ou pas.

MySQL 3.23 autorise l'utilisation du mot clé TEMPORARY lors de la création de table. Une table temporaire sera automatiquement effacée lorsque la connexion sera terminée. Cela permet à deux connexions différents d'utiliser le même nom de table temporaire, sans conflit l'un avec l'autre, ou avec une table existante (la table permanente est cachée jusqu'à ce que la table temporaire soit effacée).

MySQL 3.22 ou plus récent autorise l'utilisation du mot clé IF NOT EXISTS, qui ne retourne pas d'erreur si la table à créer existe déjà.

Chaque table est representée par un ou plusieurs fichiers dans le dossier de la bas e de données. Dans le cas ou la table est de type ISAM, il y aura :

Pour plus d'information sur les propriétés des différents type de colonne, 7.2 Types de colonnes.

  • Si ni NULL ni NOT NULL n'a été précisé, alors la colonne est considéré comme de type NULL.
  • Une colonne de type entier peut avoir l'attribut AUTO_INCREMENT. A chaque insertion de la valeur ou de 0 dans une colonne de type AUTO_INCREMENT, la valeur de la colonne est mise à value+1, avec value qui est la plus grande valeur dans la colonne de la table courante. AUTO_INCREMENT commence à 1. Si la ligne contenant la plus grande valeur de la colonne est effacée, cette valeur sera réutilisée. Si toutes les lignes de la table sont effacée, AUTO_INCREMENT recommence à 1. Il ne peux y avoir qu'une seule colonne de type AUTO_INCREMENT par table, et cette colonne doit être indexée. Par souci de compatibilité avec ODBC, il est possible d'accéder à la dernière ligne insérée avec la requête suivante : 
SELECT * FROM Nom_table WHERE auto_col IS NULL
  • Les valeurs NULL sont gérées de manière différentes pour les colonnes de type TIMESTAMP. Il n'est pas possible de stocker la valeur NULL dans une colonne de type TIMESTAMP, alors cette affectation conduit à affecter la date et l'heure courante à la place. A cause de ce comportement, les attributs NULL et NOT NULL ne s'appliquent pas, et sont ignorées. D'un autre coté, afin de rendre MySQL plus simple à utiliser, le serveur autorise l'affectation de NULL aux colonnes de type TIMESTAMP (ce qui est vrai), même si les colonnes de type TIMESTAMP ne contiendront jamais réellement la valeur NULL. Cette information est accessible en utilisant la commande DESCRIBE Nom_table qui détaille les colonnes de la table. Il est bon de rappeler que la valeur 0 est une valeur valide de TIMESTAMP.
  • Si aucune valeur par défaut n'est précisé avec l'attribut DEFAULT, MySQL en assignera automatiquement une. Si la colonne peut prendre la valeur NULL, alors MySQL utilisera cette valeur par défaut. Sinon, son comportement dépendra du type de colonne :
    • Pour les types numériques autres que ceux ayant l'attribut AUTO_INCREMENT , la valeur par défaut est 0 . Pour les colonnes ayant l'attribut AUTO_INCREMENT, la valeur par défaut est la prochaine valeur de la séquence.
    • Pour les types date et heures, excepté TIMESTAMP, la valeur par défaut est le ``zéro'' . Pour la première colonne de type TIMESTAMP, la valeur par défaut est la date et l'heure courante. 7.2.6 Types date et heure.
    • Pour les types chaînes, autre que ENUM, la valeur par défaut est la chaîne vide. Pour le type ENUM, la valeur par défaut est la première valeur de l'énumération.
  • KEY est un synonyme pour INDEX.
  • Avec MySQL, l'attribut UNIQUE force la valeur à toujours prendre une valeur distincte. Une erreur surviendra lors de l'insertion d'une ligne qui doublera une ligne déjà existante.
  • Avec MySQL, l'attribut PRIMARY KEY est identique à l'attribut KEY qui porterait le nom de PRIMARY Une table ne peut avoir qu'une seule colonne avec l'attribut table PRIMARY KEY. Si aucune colonne n'a de PRIMARY KEY et qu'une application requiert la colonne de PRIMARY KEY, MySQL retournera la première colonne ayant l'attribut UNIQUE.
  • Une PRIMARY KEY peut être un index multi colonne. Mais il n'est pas possible de créer un index multi colonne qui aurait l'attribut PRIMARY KEY. Pour cela, il faut utiliser la fonction PRIMARY KEY(index_Nom_col, ...) , sous peine de ne voir que la première colonne porter l'attribut PRIMARY KEY.
  • Si aucun nom n'est affecté à un index, un nom sera automatiquement généré et assigné. Ce nom sera constitué du nom de la première colonne, avec un suffixe optionnel,(_2, _3, ...), pour l rendre unique. La liste des noms d'index est accessible avec la requête : SHOW INDEX FROM Nom_table.
  • Seule les tables de type MyISAM acceptent les index sur les colonnes qui contiennent la valeur NULL. Dans les autres cas, il faut absolument déclarer la colonne de type NOT NULL pour ne pas générer une erreur.
  • Avec la syntaxe Nom_col(longueur), il est possible de spécifier un index qui utilise seulement une partie de la colonne de type CHAR or VARCHAR. Cela rend l'index nettement plus petit et plus efficace. Indexes.
  • Seules les tables de type MyISAM acceptent d'indexer les colonnes de type BLOB et TEXT . mettre un index sur une telle colonne impose de préciser la longueur de la colonne dans l'index. 
CREATE TABLE test (blob_col BLOB, index(blob_col(10)));
  • Lors de l'utilisation des clauses ORDER BY ou GROUP BY avec de colonnes de type TEXT ou BLOB, seuls les max_sort_longueur premiers octets sont pris en compte. BLOB.
  • Les attributs FOREIGN KEY, CHECK et REFERENCES ne font en réalité rien de spécial. Ils sont uniquement fournis pour assurer la compatibilité et la portabilité de programme SQL et de les faire tourner avec MySQL.
  • Les colonnes de type prennent un bit de plus, arrondi à l'octet le plus proche.
  • La taille maximale d'un enregistrement peut être obtenue avec la requête suivante : 
row longueur = 1
             + (sum of column longueurs)
             + (number of NULL columns + 7)/8
             + (number of variable-longueur columns)
  • Les options options_de_table et SELECT ne sont disponibles qu'à partir de MySQL 3.23. Les différents types de tables sont :

Les options de table sont utilisées pour optimiser la gestion de la table. Dans la plus part des cas, ces options peuvent être ignorées. Ces options fonctionnent pour toutes les types de table, sauf indication contraire.

Lors de l'utilisation d'une tape de type MyISAM table, MySQL utilise le produit max* avg_row_longueur pour connaître la taille maximale de la table. Par défaut, la taille maximale de la table est 4Go (4 Giga octets) (ou 2Go, si le système ne les supporte pas.

  • Si un CREATE STATEMENT est spécifié après SELECT, MySQL va créer de nouveaux champs pour chacun des éléments dans le SELECT . Par exemple 
mysql> CREATE TABLE test (a int not null auto_increment, primary key (a), key(b))
TYPE=HEAP SELECT b,c from test2;

Cette requête va créer une table de type HEAP table avec 3 colonnes. Cette table sera automatiquement effacée si une erreur survient lors de la copie des données dans ma table.

7.6.1 Modifications automatiques de type de colonne

Dans certains cas, MySQL spontanément les spécifications d'une colonne lors d'une commande de CREATE TABLE (Cela peut aussi intervenir lors d'une commande with ALTER TABLE.)

  • Les colonnes de type VARCHAR d'une longueur de moins de 4 caractères sont changées en CHAR.
  • Si l'une des colonne de la table a une longueur variable, alors la ligne complète a aussi une longueur variable. Par conséquent, si la table contient une colonne de longueur variable(VARCHAR, TEXT or BLOB), toutes les colonnes de type CHAR et de longueur supérieur à 3 caractères sont changées en colonne de type VARCHAR .Cela ne change en rien l'utilisation de ces colonnes, cela modifie simplement la façon dont les colonnes sont stockées. MySQL effectue cette conversion car cela économise de la place, et rend les opérations sur la table plus rapides.
  • La taille d'affichage des colonnes de type TIMESTAMP doit être pair et compris dans l'intervalle de 2 à 14. En dehors de l'intervalle 2 à 14, la taille de l'affichage est ramenée à 14. En cas de nombre impair, le nombre est arrondi au nombre pair directement supérieur.
  • Il est impossible d'enregistrer une valeur NULL dans une colonne de type TIMESTAMP, car une telle affectation revient à assigner la date et l'heure courante à la colonne. De ce fait, les attributs NULL et NOT NULL ne s'appliquent pas aux colonnes de type TIMESTAMP, et sont ignorés. Lors d'une requête DESCRIBE Nom_table MySQL répondra toujours que la colonne accepte les valeurs de type NULL.
  • MySQL effectue la correspondance entre certains types utilisé par d'autres bases de données SQL vers des types MySQL. 7.2.11 Utiliser des types de colonnes d'autres bases de données.

Pour savoir si MySQL a modifié spontanément le type d'une colonne, il faut utiliser la requête DESCRIBE Nom_table après la création ou la modification de la table.

Certaines autres modifications de type peuvent intervenir lors de la compression de table avec la commande pack_isam. 10.17 Quel sont les différents formats de lignes? Quand utiliser VARCHAR/CHAR?.