Rechercher dans le manuel MySQL

13.1.9.3 ALTER TABLE Examples

Table des matières

Begin with a table t1 created as shown here:

  1. CREATE TABLE t1 (a INTEGER, b CHAR(10));

To rename the table from t1 to t2:

  1. ALTER TABLE t1 RENAME t2;

To change column a from INTEGER to TINYINT NOT NULL (leaving the name the same), and to change column b from CHAR(10) to CHAR(20) as well as renaming it from b to c:

  1. ALTER TABLE t2 MODIFY a TINYINT NOT NULL, CHANGE b c CHAR(20);

To add a new TIMESTAMP column named d:

  1. ALTER TABLE t2 ADD d TIMESTAMP;

To add an index on column d and a UNIQUE index on column a:

  1. ALTER TABLE t2 ADD INDEX (d), ADD UNIQUE (a);

To remove column c:

  1. ALTER TABLE t2 DROP COLUMN c;

To add a new AUTO_INCREMENT integer column named c:

  1. ALTER TABLE t2 ADD c INT UNSIGNED NOT NULL AUTO_INCREMENT,
  2.   ADD PRIMARY KEY (c);

We indexed c (as a PRIMARY KEY) because AUTO_INCREMENT columns must be indexed, and we declare c as NOT NULL because primary key columns cannot be NULL.

For NDB tables, it is also possible to change the storage type used for a table or column. For example, consider an NDB table created as shown here:

  1. mysql> CREATE TABLE t1 (c1 INT) TABLESPACE ts_1 ENGINE NDB;
  2. Query OK, 0 rows affected (1.27 sec)

To convert this table to disk-based storage, you can use the following ALTER TABLE statement:

  1. mysql> ALTER TABLE t1 TABLESPACE ts_1 STORAGE DISK;
  2. Query OK, 0 rows affected (2.99 sec)
  3. Records: 0  Duplicates: 0  Warnings: 0
  4.  
  5. mysql> SHOW CREATE TABLE t1\G
  6. *************************** 1. row ***************************
  7.        Table: t1
  8. Create Table: CREATE TABLE `t1` (
  9.   `c1` int(11) DEFAULT NULL
  10. ) /*!50100 TABLESPACE ts_1 STORAGE DISK */
  11. ENGINE=ndbcluster DEFAULT CHARSET=latin1
  12. 1 row in set (0.01 sec)

It is not necessary that the tablespace was referenced when the table was originally created; however, the tablespace must be referenced by the ALTER TABLE:

  1. mysql> CREATE TABLE t2 (c1 INT) ts_1 ENGINE NDB;
  2. Query OK, 0 rows affected (1.00 sec)
  3.  
  4. mysql> ALTER TABLE t2 STORAGE DISK;
  5. ERROR 1005 (HY000): Can't create table 'c.#sql-1750_3' (errno: 140)
  6. mysql> ALTER TABLE t2 TABLESPACE ts_1 STORAGE DISK;
  7. Query OK, 0 rows affected (3.42 sec)
  8. Records: 0  Duplicates: 0  Warnings: 0
  9. mysql> SHOW CREATE TABLE t2\G
  10. *************************** 1. row ***************************
  11.        Table: t1
  12. Create Table: CREATE TABLE `t2` (
  13.   `c1` int(11) DEFAULT NULL
  14. ) /*!50100 TABLESPACE ts_1 STORAGE DISK */
  15. ENGINE=ndbcluster DEFAULT CHARSET=latin1
  16. 1 row in set (0.01 sec)

To change the storage type of an individual column, you can use ALTER TABLE ... MODIFY [COLUMN]. For example, suppose you create an NDB Cluster Disk Data table with two columns, using this CREATE TABLE statement:

  1. mysql> CREATE TABLE t3 (c1 INT, c2 INT)
  2.     ->     TABLESPACE ts_1 STORAGE DISK ENGINE NDB;
  3. Query OK, 0 rows affected (1.34 sec)

To change column c2 from disk-based to in-memory storage, include a STORAGE MEMORY clause in the column definition used by the ALTER TABLE statement, as shown here:

  1. mysql> ALTER TABLE t3 MODIFY c2 INT STORAGE MEMORY;
  2. Query OK, 0 rows affected (3.14 sec)
  3. Records: 0  Duplicates: 0  Warnings: 0

You can make an in-memory column into a disk-based column by using STORAGE DISK in a similar fashion.

Column c1 uses disk-based storage, since this is the default for the table (determined by the table-level STORAGE DISK clause in the CREATE TABLE statement). However, column c2 uses in-memory storage, as can be seen here in the output of SHOW CREATE TABLE:

  1. mysql> SHOW CREATE TABLE t3\G
  2. *************************** 1. row ***************************
  3.        Table: t3
  4. Create Table: CREATE TABLE `t3` (
  5.   `c1` int(11) DEFAULT NULL,
  6.   `c2` int(11) /*!50120 STORAGE MEMORY */ DEFAULT NULL
  7. ) /*!50100 TABLESPACE ts_1 STORAGE DISK */ ENGINE=ndbcluster DEFAULT CHARSET=latin1
  8. 1 row in set (0.02 sec)

When you add an AUTO_INCREMENT column, column values are filled in with sequence numbers automatically. For MyISAM tables, you can set the first sequence number by executing SET INSERT_ID=value before ALTER TABLE or by using the AUTO_INCREMENT=value table option.

With MyISAM tables, if you do not change the AUTO_INCREMENT column, the sequence number is not affected. If you drop an AUTO_INCREMENT column and then add another AUTO_INCREMENT column, the numbers are resequenced beginning with 1.

When replication is used, adding an AUTO_INCREMENT column to a table might not produce the same ordering of the rows on the slave and the master. This occurs because the order in which the rows are numbered depends on the specific storage engine used for the table and the order in which the rows were inserted. If it is important to have the same order on the master and slave, the rows must be ordered before assigning an AUTO_INCREMENT number. Assuming that you want to add an AUTO_INCREMENT column to the table t1, the following statements produce a new table t2 identical to t1 but with an AUTO_INCREMENT column:

  1. CREATE TABLE t2 (id INT AUTO_INCREMENT PRIMARY KEY)
  2. SELECT * FROM t1 ORDER BY col1, col2;

This assumes that the table t1 has columns col1 and col2.

This set of statements will also produce a new table t2 identical to t1, with the addition of an AUTO_INCREMENT column:

  1. CREATE TABLE t2 LIKE t1;
  2. ALTER TABLE t2 ADD id INT AUTO_INCREMENT PRIMARY KEY;
  3. INSERT INTO t2 SELECT * FROM t1 ORDER BY col1, col2;
Important

To guarantee the same ordering on both master and slave, all columns of t1 must be referenced in the ORDER BY clause.

Regardless of the method used to create and populate the copy having the AUTO_INCREMENT column, the final step is to drop the original table and then rename the copy:

  1. DROP TABLE t1;
  2. ALTER TABLE t2 RENAME t1;

Rechercher dans le manuel MySQL

Traduction non disponible

Le manuel MySQL n'est pas encore traduit en français sur l'infobrol. Seule la version anglaise est disponible pour l'instant.

Document créé le 26/06/2006, dernière modification le 26/10/2018
Source du document imprimé : https://www.gaudry.be/mysql-rf-alter-table-examples.html

L'infobrol est un site personnel dont le contenu n'engage que moi. Le texte est mis à disposition sous licence CreativeCommons(BY-NC-SA). Plus d'info sur les conditions d'utilisation et sur l'auteur.

Références

  1. Consulter le document html Langue du document :en Manuel MySQL : https://dev.mysql.com/

Ces références et liens indiquent des documents consultés lors de la rédaction de cette page, ou qui peuvent apporter un complément d'information, mais les auteurs de ces sources ne peuvent être tenus responsables du contenu de cette page.
L'auteur de ce site est seul responsable de la manière dont sont présentés ici les différents concepts, et des libertés qui sont prises avec les ouvrages de référence. N'oubliez pas que vous devez croiser les informations de sources multiples afin de diminuer les risques d'erreurs.

Table des matières Haut