ALTER TABLE - Table options

This page lists all of the table options that can be set with the ALTER TABLE statement for altering a MySQL table.

Syntax

Below is the syntax for this command. Text within square brackets (i.e, [ and ] ) are optional. Choices are separated by bars (i.e, | ). Ellipses preceded by a comma indicates a repeating pattern. Ellipses before or after syntax are used to highlight a relevant except from the larger syntax. Text displayed in italic letters represent text that should be replaced with the specific names related to the database (e.g., column with the name of the column).

ALTER TABLE table 
[TABLESPACE tablespace_name STORAGE DISK] 
   {ENGINE|TYPE} [=] {BDB|HEAP|ISAM|INNODB|MERGE|MRG_MYISAM|MYISAM} | 
AUTO_INCREMENT [=] value | 
AVG_ROW_LENGTH [=] value | 
[DEFAULT] CHARACTER SET character_set |
CHECKSUM [=] {0|1} | 
CONNECTION [=] 'string' | 
COLLATE collation |
COMMENT [=] 'string' | 
DATA DIRECTORY [=] '/path' |
ENGINE [=] engine |
INDEX DIRECTORY [=] '/path' |
INSERT_METHOD [=] {NO|FIRST|LAST } | 
KEY_BLOCK_SIZE [=] value | 
MAX_ROWS [=] value | 
MIN_ROWS [=] value | 
PACK_KEYS [=] {0|1|DEFAULT} |  
DELAY_KEY_WRITE [=] {0|1} | 
ROW_FORMAT [=] {DEFAULT|DYNAMIC|FIXED|COMPRESSED|REDUNDANT|COMPACT} | 
RAID_TYPE = {1|STRIPED|RAID0} | 
UNION [=] (table[,...])

Explanation

This page lists all of the table options that can be set with the ALTER TABLE statement. The options are the same as those that can be specified for CREATE TABLE when a table is first created. See the description of that MySQL statement in our MySQL documentation for more information about the options available. You can give multiple options to CREATE TABLE in a comma-separated list.

Examples

To change the starting point for an AUTO_INCREMENT column, enter the following statement:

ALTER TABLE clients
AUTO_INCREMENT = 1000;

This statement sets the value of the primary key column to 1000 so that the next row inserted will be 1001. You cannot set it to a value less than the highest data value that already exists for the column.

For large tables, you may want to set the average row length for better table optimization by using the AVG_ROW_LENGTH option. The following example uses the SHOW TABLE STATUS statement to see the average row length for a table similar to the one we want to alter, to get an idea of what the average row length should be:

SHOW TABLE STATUS LIKE 'sales' G

*************************** 1. row ***************************
           Name: articles
         Engine: MyISAM
        Version: 10
     Row_format: Dynamic
           Rows: 93
 Avg_row_length: 12638
    Data_length: 1175412
Max_data_length: 281474976710655
   Index_length: 706560
      Data_free: 0
 Auto_increment: 113
    Create_time: 2007-05-02 14:27:59
    Update_time: 2007-05-03 13:57:05
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options: 
        Comment: 
        
ALTER TABLE clients 
AVG_ROW_LENGTH = 12638;

In the second SQL statement we've set the average row length value of the clients table.

The CHARACTER SET option sets the character set to use for character data in the table. The DEFAULT flag is not required. This option is typically used along with the COLLATE option. These options do not affect columns for which the character sets and collation are explicitly specified. Use the MySQL statements, SHOW CHARACTER SET and SHOW COLLATION to see the character sets and collations available.

ALTER TABLE clients
DEFAULT CHARACTER SET 'latin2'
COLLATE 'latin2_general_ci';

The CHECKSUM option enables or disables a checksum for a table. Set the value to 0 to disable a checksum or 1 to enable checksum. If you upgrade a table that uses a checksum and was created prior to version 4.1 of MySQL, the table may be corrupted in the process. Try using the MySQL statement, REPAIR TABLE to recalculate the checksum for the table.

ALTER TABLE clients
CHECKSUM = 0;

The COLLATE option sets the collaction to use with the data in the table (that is, how the character data is alphabetized). This option is typically used along with the CHARACTER SET option. These options do not affect columns for which the collation and character sets are explicitly specified. Use the MySQL statement, SHOW CREATE TABLE to see the collation and character set for the MySQL table and its columns.

ALTER TABLE clients
COLLATE 'latin2_general_ci'
DEFAULT CHARACTER SET 'latin2';

With the COMMENT option you can add notes for yourself or other table adminstrators regarding a table.

ALTER TABLE clients
MAX_ROWS = 1000,
COMMENT = 'This table lists basic information on clients.';

SHOW CREATE TABLE clients G

*************************** 1. row ***************************
       Table: clients
Create Table: CREATE TABLE `clients` (
  `client_id` int(11) NOT NULL AUTO_INCREMENT,
  `client_name` varchar(255) DEFAULT NULL, ...
  PRIMARY KEY (`client_id`),
  KEY `client_index` (`client_name`(10),`city`(5)) USING BTREE
) ENGINE=MyISAM 
AUTO_INCREMENT=1001 
DEFAULT CHARSET=latin1 MAX_ROWS=1000 
COMMENT='This table lists basic information on clients.'

We've shortened the results by deleting the list of columns shown here to save space and to focus on the options. The SHOW CREATE TABLE is the only method for viewing the table options in MySQL. They will not be shown with DESCRIBE.

The CONNECTION option is provided for tables that use the FEDERATED storage engine. Previously you would use the COMMENT option to specify this option. The syntax for this option is:

CONNECTION='mysql://username:password@hostname:port/database/tablename'

The password and port are optional.

If you wish to federate an existing table with a remote table, you can alter the table on your system to specify the connection to the remote table like this:

ALTER TABLE clients
CONNECTION='mysql://ulfthewolf:rover123@santa_clara_svr:9306/federated/clients';

The DATA DIRECTORY option is theoretically used to see the data directory path for the table. However, MySQL currently ignores the option.

ALTER TABLE clients
DATA DIRECTORY = '/data/mysql/clients';

Use the ENGINE option to change the storage engine (formerly known as the table type) for the table given. Be careful with using this option as it may cause problems with data. Make a backup of your table and data before using it. As of version 5.1.11 of MySQL, this option cannot be used to change a table to the BLACKHOLE or MERGE storage engines.

ALTER TABLE clients 
ENGINE = INNODB;

This statement changes the storage engine used for the given table to InnoDb. If a table has special requirements that the new engine cannot provide, you'll receive an error when trying to make this change and the statement will fail. For instance, a MyISAM table that has FULLTEXT indexes could not be changed to InnoDB since it doesn't support that kind of indexing. Instead, create a new table using the desired storage engine, migrate the data to the new table, and then drop the old table after verifying the integrity of the data.

The INDEX DIRECTORY option is theoretically used to see the directory path for the table indexes. However, MySQL currently ignores the option.

ALTER TABLE clients
INDEX DIRECTORY = '/data/mysql/clients_index';

To insert data into a MERGE table, you will need to specify the insert method it is to use. To specify or change this method, use the INSERT_METHOD option with the ALTER TABLE statement. A value of FIRST indicates that the first table should be used; LAST indicates the last table should be used; NO disables inserting.

CREATE TABLE sales_national
(order_id INT, sales_total INT) 
ENGINE = MERGE
UNION = (sales_east, sales_west) 
INSERT_METHOD = LAST;

ALTER TABLE sales_national
INSERT_METHOD = FIRST;

In the first SQL statement here, we create the table sales_national based on two other tables and specify that inserts use the last table in the list of tables given. In the second SQL statement, we change the insert method.

To give the storage engine a hint of the size of index key blocks, use the KEY_BLOCK_SIZE option. Set the value to 0 to instruct the engine to

To give the storage engine a hint of the size of.1.10 of MySQL.

ALTER TABLE clients
KEY_BLOCK_SIZE = 1024;

The MAX_ROWS and MIN_ROWS option are used to set the maximum and minimum rows of a table, respectively. Use the SHOW CREATE TABLE statement to see the results of this option.

ALTER TABLE clients
MIN_ROWS = 100, 
MAX_ROWS = 1000;

For small MyISAM tables in which users primarily read the data and rarely update it, you can use the PACK_KEYS option to pack the indexes. This will make reads faster but updates slower. Set the value of this option to 1 to enable packing and 0 to disable it. A value of DEFAULT instructs the storage engine to pack CHAR or VARCHAR data type columns only.

ALTER TABLE clients
PACK_KEYS = 1;

The DELAY_KEY_WRITE option delays updates of indexes until the table is closed. It's enabled with a value of 1, disabled with 0.

ALTER TABLE clients
DELAY_KEY_WRITE = 1;

The ROW_FORMAT option instructs the storage engine how to store rows of data. With MyISAM, a value of DYNAMIC (i.e., variable length) or FIXED may be given. If you use the utility myisampack on a MyISAM table, the format will be set to a value of COMPRESSED. You can change a compressed MyISAM to un-compressed by giving a value of REDUNDANT. This is deprecated, though. InnoDB tables use the COMPACT method, but offer a REDUNDANT method to be compatible with a more wasteful format used in older versions of InnoDB.

ALTER TABLE clients
ROW_FORMAT = FIXED;

The RAID_TYPE option is used to specify the type of RAID to be used. However, support for RAID has been removed from MySQL as of version 5.0. This SQL statement also used to permit the options RAID_CHUNKS and RAID_CHUNKSIZE. They have been deprecated, as well.

For MERGE tables in which you want to change the tables which make up the merged table, use the UNION option.

ALTER TABLE sales_national
UNION = (sales_north, sales_south, sales_east, sales_west);

See the CREATE TABLE statement in our MySQL documentation for more information and examples regarding many of the options for the ALTER TABLE statement.