Rechercher dans le manuel MySQL

13.1.9.3 ALTER TABLE Examples

Inhoudsopgave

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;

Zoek in de MySQL-handleiding

Nederlandse vertaling

U hebt gevraagd om deze site in het Nederlands te bezoeken. Voor nu wordt alleen de interface vertaald, maar nog niet alle inhoud.

Als je me wilt helpen met vertalingen, is je bijdrage welkom. Het enige dat u hoeft te doen, is u op de site registreren en mij een bericht sturen waarin u wordt gevraagd om u toe te voegen aan de groep vertalers, zodat u de gewenste pagina's kunt vertalen. Een link onderaan elke vertaalde pagina geeft aan dat u de vertaler bent en heeft een link naar uw profiel.

Bij voorbaat dank.

Document heeft de 26/06/2006 gemaakt, de laatste keer de 26/10/2018 gewijzigd
Bron van het afgedrukte document:https://www.gaudry.be/nl/mysql-rf-alter-table-examples.html

De infobrol is een persoonlijke site waarvan de inhoud uitsluitend mijn verantwoordelijkheid is. De tekst is beschikbaar onder CreativeCommons-licentie (BY-NC-SA). Meer info op de gebruiksvoorwaarden en de auteur.

Referenties

  1. Bekijk - html-document Taal van het document:en Manuel MySQL : https://dev.mysql.com/

Deze verwijzingen en links verwijzen naar documenten die geraadpleegd zijn tijdens het schrijven van deze pagina, of die aanvullende informatie kunnen geven, maar de auteurs van deze bronnen kunnen niet verantwoordelijk worden gehouden voor de inhoud van deze pagina.
De auteur Deze site is als enige verantwoordelijk voor de manier waarop de verschillende concepten, en de vrijheden die met de referentiewerken worden genomen, hier worden gepresenteerd. Vergeet niet dat u meerdere broninformatie moet doorgeven om het risico op fouten te verkleinen.

Inhoudsopgave Haut