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;

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 apparait dans la section de commentaire de la commande SHOW . Par exemple :

SHOW TABLE STATUS FROM test LIKE 'CUSTOMER'

Convertir une table MyISAM en InnoDB

Pour avoir un meilleur contrôle sur le processus d'insertion, c'est une bonne idée de faire les insertions par portions de table :

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

Durant la conversion des grandes tables, vous devriez utiliser le buffer InnoDB en lui donnant une grande taille pour réduire les accès disques. Ne lui donnez pas une taille supérieure à 80% de votre mémoire physique. Vous devriez aussi avoir de grand fichiers de log et un buffer de log important.

Dans le cas d'une annulation, si vous n'avez pas de données importante dans votre base, il est mieux de terminer le processus et de détruire les données InnoDB et les fichiers de log, ainsi que le fichier de table .frm , et de recommencer, plutôt que d'attendre les millions d'accès disques.

Depuis la version 3.23.43b, InnoDB supporte les contraîntes de clé étrangères. InnoDB est le premier géstionnaire de tables MySQL qui permet de définir des contraîntes de clé étrangères pour assurer la cohérence de vos données.

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

Les colonnes correspondantes dans la clé étrangère doivent être de même type interne à InnDB, de façon à ce qu'elle puissent être comparées sans conversion. La taille et le signe des types entiers doivent être les mêmes . La taille des chaînes n'a pas besoin d'être les mêmes.

Si ON DELETE CASCADE est spécifié, et qu'une ligne de la table parente est effacée, alors InnoDB va automatiquement effacer la lgine dans la table fille, dont la clé étrangère est égale à la clé référencée dans la table parente. Si ON DELETE SET NULL est spécifié, les lignes de la table fille sont automatiquement modifiée de façon à ce que les colonnes dans la clé étrangère prennent automatiquement la valeur SQL de NULL.

Depuis la version 3.23.50, InnoDB vous permet d'utiliser les guillemets américains (`) autour des noms de tables et colonnes dans la clause FOREIGN KEY ... REFERENCES ... mais InnoDB ne prend pas encore en compte l'option lower_case_table_names que vous pouvez spécifier dans le fichier my.cnf .

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(...)

InnoDB vous permet d'effacer n'importe quelle table, même si cela casse la cohérence d'une clé étrangère, qui fait référence à la table. Lorsque vous effacez une table, les contraintes qui en faisaient parties sont aussi effacées.

SHOW CREATE TABLE yourtablename

SHOW TABLE STATUS FROM yourdatabasename LIKE 'T'