Movatterモバイル変換

Begin with a tablet1 created as shown here:

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

To rename the table fromt1 tot2:

ALTER TABLE t1 RENAME t2;

To change columna fromINTEGER toTINYINT NOT NULL (leaving the name the same), and to change columnb fromCHAR(10) toCHAR(20) as well as renaming it fromb toc:

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

To add a newTIMESTAMP column namedd:

ALTER TABLE t2 ADD d TIMESTAMP;

To add an index on columnd and aUNIQUE index on columna:

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

To remove columnc:

ALTER TABLE t2 DROP COLUMN c;

To add a newAUTO_INCREMENT integer column namedc:

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

We indexedc (as aPRIMARY KEY) becauseAUTO_INCREMENT columns must be indexed, and we declarec asNOT NULL because primary key columns cannot beNULL.

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

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

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

mysql> ALTER TABLE t1 TABLESPACE ts_1 STORAGE DISK;Query OK, 0 rows affected (2.99 sec)Records: 0  Duplicates: 0  Warnings: 0mysql> SHOW CREATE TABLE t1\G*************************** 1. row ***************************       Table: t1Create Table: CREATE TABLE `t1` (  `c1` int(11) DEFAULT NULL) /*!50100 TABLESPACE ts_1 STORAGE DISK */ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci1 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 theALTER TABLE:

mysql> CREATE TABLE t2 (c1 INT) ts_1 ENGINE NDB;Query OK, 0 rows affected (1.00 sec)mysql> ALTER TABLE t2 STORAGE DISK;ERROR 1005 (HY000): Can't create table 'c.#sql-1750_3' (errno: 140)mysql> ALTER TABLE t2 TABLESPACE ts_1 STORAGE DISK;Query OK, 0 rows affected (3.42 sec)Records: 0  Duplicates: 0  Warnings: 0mysql> SHOW CREATE TABLE t2\G*************************** 1. row ***************************       Table: t1Create Table: CREATE TABLE `t2` (  `c1` int(11) DEFAULT NULL) /*!50100 TABLESPACE ts_1 STORAGE DISK */ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci1 row in set (0.01 sec)

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

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

To change columnc2 from disk-based to in-memory storage, include aSTORAGE MEMORY clause in the column definition used by theALTER TABLE statement, as shown here:

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

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

Columnc1 uses disk-based storage, since this is the default for the table (determined by the table-levelSTORAGE DISK clause in theCREATE TABLE statement). However, columnc2 uses in-memory storage, as can be seen here in the output ofSHOW CREATE TABLE:

mysql> SHOW CREATE TABLE t3\G*************************** 1. row ***************************       Table: t3Create Table: CREATE TABLE `t3` (  `c1` int(11) DEFAULT NULL,  `c2` int(11) /*!50120 STORAGE MEMORY */ DEFAULT NULL) /*!50100 TABLESPACE ts_1 STORAGE DISK */ ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci1 row in set (0.02 sec)

When you add anAUTO_INCREMENT column, column values are filled in with sequence numbers automatically. ForMyISAM tables, you can set the first sequence number by executingSET INSERT_ID=value beforeALTER TABLE or by using theAUTO_INCREMENT=value table option.

WithMyISAM tables, if you do not change theAUTO_INCREMENT column, the sequence number is not affected. If you drop anAUTO_INCREMENT column and then add anotherAUTO_INCREMENT column, the numbers are resequenced beginning with 1.

When replication is used, adding anAUTO_INCREMENT column to a table might not produce the same ordering of the rows on the replica and the source. 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 source and replica, the rows must be ordered before assigning anAUTO_INCREMENT number. Assuming that you want to add anAUTO_INCREMENT column to the tablet1, the following statements produce a new tablet2 identical tot1 but with anAUTO_INCREMENT column:

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

This assumes that the tablet1 has columnscol1 andcol2.

This set of statements also produces a new tablet2 identical tot1, with the addition of anAUTO_INCREMENT column:

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

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

DROP TABLE t1;ALTER TABLE t2 RENAME t1;