Services webmasters
Partenaires
Jeux concours gratuits
 
Syntaxe de DROP DATABASE
<<<
Syntaxe de CREATE TABLE Syntaxe de ALTER TABLE
>>>

6.5 Définition de données : CREATE , DROP , ALTER
6 Référence du langage MySQL
 Manuel de Référence MySQL 4.1 : Version Française

Syntaxe de CREATE DATABASE
Syntaxe de DROP DATABASE
->Syntaxe de CREATE TABLE
Syntaxe de ALTER TABLE
Syntaxe de RENAME TABLE
Syntaxe de DROP TABLE
Syntaxe de CREATE INDEX
Syntaxe de DROP INDEX

6.5.3 Syntaxe de CREATE TABLE


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& />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. Les règles de nommage des tables sont disponibles dans Noms de base de données, tables, index, colonnes et alias . 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
Pour plus d'informations sur les propriétés des différentes colonnes et de leurs types, voyez Types de colonnes :
  • 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 . mysql_insert_id() .

    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. Utiliser AUTO_INCREMENT .

    Pour rendre MySQL avec certaines applications ODBC, vous pouvez retrouver la valeur de la dernière valeur automatiquement générée avec la requête suivante :
    
    SELECT * FROM nom_de_table WHERE auto_col IS NULL
    
  • 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. Types dates et heures .
    • 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. Syntaxe de SHOW .
  • 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. Index de colonne .
  • 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. Les types BLOB et TEXT .
  • En MySQL version 3.23.23 ou plus récent, vous pouvez aussi créer des index spécial FULLTEXT . Ils sont utilisés pour faire des recherches en texte plein. Seul, le format de table MyISAM supporte les index FULLTEXT . Ils peuvent être créés uniquement pour les colonnes de type VARCHAR et TEXT . L'indexation est alors exécutée sur toute la colonne, et les indexations partielles ne sont pas supportées. supported. Voir Recherche full-text avec MySQL pour les détails.
  • 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. Tables BDB ou BerkeleyDB .
    HEAP Les données de ces tables ne sont stockées qu'en mémoire. Tables HEAP .
    ISAM Le gestionnaire originel de tables. Tables ISAM .
    InnoDB Transaction-safe tables with row locking. Tables InnoDB .
    MERGE Un emsemble de tables MyISAM utilisées comme une seule et même table. Tables MERGE .
    MRG_MyISAM Un synonyme pour MERGE les tables.
    MyISAM Le nouveau gestionnaire de table binaire et portable. Tables MyISAM .
    Types de tables MySQL .

    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. Format de table MyISAM .
    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 . 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 . Tables MERGE . 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 a 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 . Utiliser les liens symboliques pour les tables .

Sommaire :

<< Syntaxe de CREATE TABLE >>
Syntaxe de DROP DATABASE Définition de données : CREATE , DROP , ALTER Syntaxe de ALTER TABLE
Services webmasters
Les manuels
 
CoursPHP.com - Reproduction interdite -