sql_alter_table

NAME

sql_alter_table: Generate ALTER TABLE SQL statements per table, with engine and create options

TYPE

View

DESCRIPTION

sql_alter_table provides with SQL statements to alter a table to its current form.

This view analyzes table structure and provides with the syntax required to rebuild the table by various aspects. It can be useful in generating a "resurrection" script to restore table to its current engine or indexes state. For example, it may provide with the rollback script for a database migration from MyISAM to InnoDB, or from InnoDB Antelope to Barracuda format.

STRUCTURE

mysql> DESC common_schema.sql_alter_table;
+-----------------+--------------+------+-----+---------+-------+
| Field           | Type         | Null | Key | Default | Extra |
+-----------------+--------------+------+-----+---------+-------+
| TABLE_SCHEMA    | varchar(64)  | NO   |     |         |       |
| TABLE_NAME      | varchar(64)  | NO   |     |         |       |
| ENGINE          | varchar(64)  | YES  |     | NULL    |       |
| sql_drop_keys   | longtext     | YES  |     | NULL    |       |
| sql_add_keys    | longblob     | YES  |     | NULL    |       |
| table_options   | varchar(327) | YES  |     | NULL    |       |
| alter_statement | varchar(511) | YES  |     | NULL    |       |
+-----------------+--------------+------+-----+---------+-------+

SYNOPSIS

Columns of this view:

  • TABLE_SCHEMA: schema of current table
  • TABLE_NAME: current table name
  • ENGINE: current engine name
  • sql_drop_keys: A SQL statement to drop all keys on table.
  • sql_add_keys: A SQL statement to create all keys on table
  • table_options: All table options for this table
  • alter_statement: A SQL statement which ALTERs current table to its current engine with create options.
    Use eval() to apply SQL columns.

The SQL statements are not terminated by ';'.

EXAMPLES

Generate ALTER TABLE statements for `sakila` tables:

mysql> SELECT TABLE_SCHEMA, TABLE_NAME, ENGINE, alter_statement FROM common_schema.sql_alter_table WHERE TABLE_SCHEMA='sakila';
+--------------+---------------+--------+-----------------------------------------------------+
| TABLE_SCHEMA | TABLE_NAME    | ENGINE | alter_statement                                     |
+--------------+---------------+--------+-----------------------------------------------------+
| sakila       | actor         | InnoDB | ALTER TABLE `sakila`.`actor` ENGINE=InnoDB          |
| sakila       | address       | InnoDB | ALTER TABLE `sakila`.`address` ENGINE=InnoDB        |
| sakila       | category      | InnoDB | ALTER TABLE `sakila`.`category` ENGINE=InnoDB       |
| sakila       | city          | InnoDB | ALTER TABLE `sakila`.`city` ENGINE=InnoDB           |
| sakila       | country       | InnoDB | ALTER TABLE `sakila`.`country` ENGINE=InnoDB        |
| sakila       | customer      | InnoDB | ALTER TABLE `sakila`.`customer` ENGINE=InnoDB       |
| sakila       | film          | InnoDB | ALTER TABLE `sakila`.`film` ENGINE=InnoDB           |
| sakila       | film_actor    | InnoDB | ALTER TABLE `sakila`.`film_actor` ENGINE=InnoDB     |
| sakila       | film_category | InnoDB | ALTER TABLE `sakila`.`film_category` ENGINE=InnoDB  |
| sakila       | film_text     | MyISAM | ALTER TABLE `sakila`.`film_text` ENGINE=MyISAM      |
| sakila       | inventory     | InnoDB | ALTER TABLE `sakila`.`inventory` ENGINE=InnoDB      |
| sakila       | language      | InnoDB | ALTER TABLE `sakila`.`language` ENGINE=InnoDB       |
| sakila       | payment       | InnoDB | ALTER TABLE `sakila`.`payment` ENGINE=InnoDB        |
| sakila       | rental        | InnoDB | ALTER TABLE `sakila`.`rental` ENGINE=InnoDB         |
| sakila       | staff         | InnoDB | ALTER TABLE `sakila`.`staff` ENGINE=InnoDB          |
| sakila       | store         | InnoDB | ALTER TABLE `sakila`.`store` ENGINE=InnoDB          |
+--------------+---------------+--------+-----------------------------------------------------+

Modify tables; again generate ALTER TABLE statements for `sakila` tables:

mysql> ALTER TABLE sakila.film_text ENGINE=MyISAM ROW_FORMAT=FIXED;
mysql> ALTER TABLE sakila.film_category ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;

mysql> SELECT TABLE_SCHEMA, TABLE_NAME, ENGINE, alter_statement FROM common_schema.sql_alter_table WHERE TABLE_SCHEMA='sakila';
+--------------+---------------+--------+-------------------------------------------------------------------------------------------+
| TABLE_SCHEMA | TABLE_NAME    | ENGINE | alter_statement                                                                           |
+--------------+---------------+--------+-------------------------------------------------------------------------------------------+
| sakila       | actor         | InnoDB | ALTER TABLE `sakila`.`actor` ENGINE=InnoDB                                                |
| sakila       | address       | InnoDB | ALTER TABLE `sakila`.`address` ENGINE=InnoDB                                              |
| sakila       | category      | InnoDB | ALTER TABLE `sakila`.`category` ENGINE=InnoDB                                             |
| sakila       | city          | InnoDB | ALTER TABLE `sakila`.`city` ENGINE=InnoDB                                                 |
| sakila       | country       | InnoDB | ALTER TABLE `sakila`.`country` ENGINE=InnoDB                                              |
| sakila       | customer      | InnoDB | ALTER TABLE `sakila`.`customer` ENGINE=InnoDB                                             |
| sakila       | film          | InnoDB | ALTER TABLE `sakila`.`film` ENGINE=InnoDB                                                 |
| sakila       | film_actor    | InnoDB | ALTER TABLE `sakila`.`film_actor` ENGINE=InnoDB                                           |
| sakila       | film_category | InnoDB | ALTER TABLE `sakila`.`film_category` ENGINE=InnoDB row_format=COMPRESSED KEY_BLOCK_SIZE=8 |
| sakila       | film_text     | MyISAM | ALTER TABLE `sakila`.`film_text` ENGINE=MyISAM row_format=FIXED                           |
| sakila       | inventory     | InnoDB | ALTER TABLE `sakila`.`inventory` ENGINE=InnoDB                                            |
| sakila       | language      | InnoDB | ALTER TABLE `sakila`.`language` ENGINE=InnoDB                                             |
| sakila       | payment       | InnoDB | ALTER TABLE `sakila`.`payment` ENGINE=InnoDB                                              |
| sakila       | rental        | InnoDB | ALTER TABLE `sakila`.`rental` ENGINE=InnoDB                                               |
| sakila       | staff         | InnoDB | ALTER TABLE `sakila`.`staff` ENGINE=InnoDB                                                |
| sakila       | store         | InnoDB | ALTER TABLE `sakila`.`store` ENGINE=InnoDB                                                |
+--------------+---------------+--------+-------------------------------------------------------------------------------------------+

Note again that the SQL statements are not terminated by ';'. Either CONCAT() these beforehand, or use sed/awk afterwards.

Show indexes DROP and ADD statements for a given table:

mysql> SELECT TABLE_SCHEMA, TABLE_NAME, sql_drop_keys, sql_add_keys FROM common_schema.sql_alter_table WHERE TABLE_SCHEMA='sakila' and TABLE_NAME='rental' \G

 TABLE_SCHEMA: sakila
   TABLE_NAME: rental
sql_drop_keys: DROP KEY `idx_fk_customer_id`, DROP KEY `idx_fk_inventory_id`, DROP KEY `idx_fk_staff_id`, DROP PRIMARY KEY, DROP KEY `rental_date`
 sql_add_keys: ADD KEY `idx_fk_customer_id`(`customer_id`), ADD KEY `idx_fk_inventory_id`(`inventory_id`), ADD KEY `idx_fk_staff_id`(`staff_id`), ADD PRIMARY KEY (`rental_id`), ADD UNIQUE KEY `rental_date`(`rental_date`,`inventory_id`,`customer_id`)

ENVIRONMENT

MySQL 5.1 or newer

SEE ALSO

eval(), sql_foreign_keys, sql_range_partitions

AUTHOR

Shlomi Noach
 
common_schema documentation