ALTER TABLESPACE Statement 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; 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; 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: 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: DROP TABLE t1; ALTER TABLE t2 RENAME t1; Important 13.1.10 ALTER TABLESPACE Statement 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” . 2505