Changing a Table Type

8.6.1 Problem

A table has one type, and now you realize that another table type has properties that are more desirable for the way you use the table.

8.6.2 Solution

Use ALTER TABLE to change its type with a TYPE clause.

8.6.3 Discussion

MySQL supports several tables types, each of which have differing characteristics. Sometimes it's necessary or desirable to convert a table from one type to another. Some situations where a change of table type can be useful are as follows:

Changing a table type is easy; use ALTER TABLE with a TYPE specifier. For example, to convert a table to the MyISAM type, use this statement:

ALTER TABLE tbl_name TYPE = MYISAM;

To find out the current type of a table, use the SHOW TABLE STATUS statement (introduced in MySQL 3.23.0) or SHOW CREATE TABLE (introduced in MySQL 3.23.20):

mysql> SHOW TABLE STATUS LIKE 'mytbl'G *************************** 1. row *************************** Name: mytbl Type: MyISAM Row_format: Fixed Rows: 0 Avg_row_length: 0 Data_length: 0 Max_data_length: 85899345919 Index_length: 1024 Data_free: 0 Auto_increment: NULL Create_time: 2002-07-15 21:28:34 Update_time: 2002-07-15 21:28:34 Check_time: NULL Create_options: Comment: mysql> SHOW CREATE TABLE mytblG *************************** 1. row *************************** Table: mytbl Create Table: CREATE TABLE `mytbl` ( `c` char(10) default NULL, `j` bigint(20) default NULL, `e2` enum('hardware','software','books','office supplies', 'telecommunications','furniture','utilities','shipping','tax') default NULL ) TYPE=MyISAM

Alternatively, use the mysqldump command-line utility:

% mysqldump --no-data cookbook mytbl CREATE TABLE mytbl ( c char(10) default NULL, j bigint(20) default NULL, e2 enum('hardware','software','books','office supplies', 'telecommunications','furniture','utilities','shipping','tax') default NULL ) TYPE=MyISAM;

Категории