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