7.5.4 Créer des tables InnoDB

Supposons que vous avez démarré le client MySQL avec la commande mysql test . Pour créer une table au format InnoDB vous devez spécifier le type TYPE = InnoDB lors de la création de table, dans la commande SQL :

CREATE TABLE CUSTOMER (A INT, B CHAR (20), INDEX (A)) TYPE = InnoDB;

Cette commande SQL va créer une table et un index sur la colonne A dans la base InnoDB constituée par les fichiers de données que vous avez spécifié dans le fichier de configuration my.cnf . De plus, MySQL va créer un fichier CUSTOMER.frm dans le dossier de données de MySQL test . En interne, InnoDB va ajouter une entrée dans son propre dictionnaire de données une entrée pour la table 'test/CUSTOMER' . De cette façon, vous pouvez créer plusieurs table avec le même nom de CUSTOMER , mais dans d'autres bases MySQL, et les noms de seront pas en conflit avec InnoDB.Vous pouvez demander la quantité d'espace disponible dans l'espace de tables InnoDB avec la commande de statut de MySQL pour toutes les tables de type TYPE = InnoDB . La quantité d'espace disponible apparaît dans la section de commentaire de la commande SHOW . Par exemple :

SHOW TABLE STATUS FROM test LIKE 'CUSTOMER'

Notez que les statistiques que SHOW vous donne sur les tables InnoDB ne sont que des approximations : elles sont utilisées pour les optimisations SQL par MySQL. Les tailles réservées d'index et de table, exprimées en octets, sont précises. Convertir une table MyISAM en InnoDB

InnoDB n'a pas d'optimisation particulière pour la création d'un fichier externe d'index. Donc, exporter pour réimporter les données n'apporte pas de gain de performances. La méthode la plus rapide pour donner à une table le format InnoDB est de faire les insertions directement dans la table InnoDB, avec la commande ALTER TABLE ... TYPE=INNODB , ou de créer une table vide InnoDB avec les mêmes caractéristiques, et d'insérer les lignes avec la commande INSERT INTO ... SELECT * FROM ... .

INSERT INTO newtable SELECT * FROM oldtable
   WHERE votre_cle > borne1 AND votre_cle <= borne2;

Assurez vous que vous avez toujours de l'espace pour les données : les tables InnoDB prennent bien plus de place que les tables MyISAM. Si une commande ALTER TABLE rencontre un problème d'espace disque, elle va s'annuler, et cela va prendre des heures si le disque est plein. Lors des insertions, InnoDB utilise un buffer d'insertion pour fusionner les index par groupe. Cela économise beaucoup d'accès disques. Lors d'une annulation, aucun mécanisme de ce type n'est utilisé, et l'annulation peut prendre jusqu'à 30 fois la durée de l'insertion.

Contraintes de clé étrangères

[CONSTRAINT symbol] FOREIGN KEY (index_col_name, ...)
                  REFERENCES table_name (index_col_name, ...)
                  [ON DELETE CASCADE | ON DELETE SET NULL]

Depuis la version 3.23.50, vous pouvez aussi associer la clause ON DELETE CASCADE ou ON DELETE SET NULL avec les contraintes de clé étrangère.

Depuis la version 3.23.50, InnoDB ne vérifie pas les contraintes de clé étrangère pour ces clés étrangères, ou les clés qui font référence à la valeur NULL.

CREATE TABLE parent(id INT NOT NULL, PRIMARY KEY (id)) TYPE=INNODB;
CREATE TABLE child(id INT, parent_id INT, INDEX par_ind (parent_id),
             FOREIGN KEY (parent_id) REFERENCES parent(id)
             ON DELETE SET NULL
) TYPE=INNODB;

ALTER TABLE yourtablename
ADD [CONSTRAINT symbol] FOREIGN KEY (...) REFERENCES anothertablename(...)

Lorsqu'il fait des vérifications de clé étrangères, InnoDB pose des verrous de lignes sur les lignes filles et mères qu'il doit utiliser. InnoDB vérifie les contraintes immédiatement : la vérification n'est pas reportée à la validation de la transaction.

Si vous recréez une table qui a été effacée, elle doit avoir une définition qui satisfont les contraintes de clé étrangères. Elle doit avoir les bons noms et types de colonnes, ainsi que les index nécessaires. Si ces conditions ne sont pas remplies, MySQL retourne une erreur numéro 1005, et fait référence au numéro de message 150.

SHOW CREATE TABLE yourtablename

SHOW TABLE STATUS FROM yourdatabasename LIKE 'T'