content_image/847730.jpg
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 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 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: 

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

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

This set of statements also produces a new table t2  identical to t1 , with the addition of an AUTO_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; 

<img src='content_image/847730.jpg'>

## Important 

To guarantee the same ordering on both source and replica, 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: 

## 13.1.10 ALTER TABLESPACE Statement 

DROP TABLE t1; ALTER TABLE t2 RENAME t1; 

ALTER [UNDO] TABLESPACE tablespace_name NDB only: {ADD | DROP} DATAFILE ’ file_name ’ [INITIAL_SIZE [=] size] [WAIT] InnoDB and NDB: [RENAME TO tablespace_name ] InnoDB only: [AUTOEXTEND_SIZE [=] ’ value ’] [SET {ACTIVE | INACTIVE}] [ENCRYPTION [=] {’Y’ | ’N’}] InnoDB and NDB: [ENGINE [=] engine_name ] Reserved for future use: [ENGINE_ATTRIBUTE [=] ’ string ’] 

This statement is used with NDB  and InnoDB  tablespaces. It can be used to add a new data file to, or to drop a data file from an NDB  tablespace. It can also be used to rename an NDB Cluster Disk Data tablespace, rename an InnoDB  general tablespace, encrypt an InnoDB  general tablespace, or mark an InnoDB  undo tablespace as active or inactive. 

The UNDO  keyword, introduced in MySQL 8.0.14, is used with the SET {ACTIVE | INACTIVE}  clause to mark an InnoDB  undo tablespace as active or inactive. For more information, see Section 15.6.3.4, “Undo Tablespaces” . 

