ALTER TABLE - Miscellaneous clauses

These clauses of the MySQL statement, ALTER TABLE can be used to change a variety of MySQL table properties.

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 [IGNORE] TABLE table
CONVERT TO CHARACTER SET charset [COLLATE collation] | 
[DEFAULT] CHARACTER SET charset [COLLATE collation] | 
DISABLE|ENABLE KEYS | 
DISCARD|IMPORT TABLESPACE | 
ORDER BY column [ASC|DESC][,...] | 
RENAME [TO] table

Explanation

You can use these miscellaneous clauses with the MySQL statement, ALTER TABLE to change a variety of table properties. They are described in the Examples section below.

Examples

The various MySQL table properties that may be altered with this MySQL statement are described here in the order that they are listed in the syntax above.

  • Converting & Setting Character Sets
  • The first two syntaxes shown may be used to change the character set and collation for a table. When a table is first created with the CREATE TABLE statement, unless a character set or collation is specified, defaults for these traits are used. To see the character set and collation for a particular table, use the SHOW TABLE STATUS statement. To convert the data, use the CONVERT TO CHARACTER SET clause. To set the table's default without converting the data, use the DEFAULT CHARACTER SET clause with the ALTER TABLE statement. The following example shows how to convert a table's character set:

    SHOW TABLE STATUS LIKE 'clients' \G
    
    *************************** 1. row ***************************
               Name: clients
             Engine: MyISAM
            Version: 10
         Row_format: Dynamic
               Rows: 632
     Avg_row_length: 12732
        Data_length: 1024512
    Max_data_length: 281474976710655
       Index_length: 3072
          Data_free: 0
     Auto_increment: 1678
        Create_time: 2006-02-01 14:12:31
        Update_time: 2007-04-03 05:25:41
         Check_time: 2006-08-14 21:31:36
          Collation: latin1_swedish_ci
           Checksum: NULL
     Create_options: max_rows=1000
            Comment: This table lists basic information on clients.
    
    ALTER TABLE clients
    CONVERT TO CHARACTER SET latin2 COLLATE latin2_bin,
    DEFAULT CHARACTER SET latin2 COLLATE latin2_bin;

    The first clause in this example converts the data in the clients table from its default of latin1_swedish_ci to latin2. The second clause sets the new default for the table to latin2, as well. Be aware that the CONVERT clause may cause problems with the data. So be sure to make a backup copy before using this clause and check the converted data before finishing. If you have a column with a character set in which data might be lost in the conversion, you could first convert the column to a BLOB data type, and then to the data type and character set that you want. This usually works fine because BLOB data isn't converted with a character set change.

  • Disabling & Enabling Keys
  • You can use the third clause (DISABLE and ENABLE) to disable or enable the updating of non-unique indexes on MyISAM tables. You will need ALTER, CREATE, INDEX, and INSERT privileges to execute this statement and clause. As of version 5.1.11 of MySQL, this clause will work on partitioned tables. When running a large number of row inserts, it can be useful to disable indexing until afterwards. You will need ALTER, CREATE, INDEX, and INSERT privileges to execute a statement with this clause. As of version 5.1.11 of MySQL, it will work on partitioned tables.

    ALTER TABLE sales_dept.catalog
    DISABLE KEYS;
    
    LOAD DATA INFILE '/tmp/catalog.txt'
    INTO TABLE sales_dept.catalog
    FIELDS TERMINATED BY '|'
    LINES TERMINATED BY '\n';
    
    ALTER TABLE sales_dept.catalog
    ENABLE KEYS;

    In this example, we've disabled the indexes of the catalog table in the sales_dept database so that we can more quickly import the new catalog data. If we had run the SHOW INDEXES statement at this point, we would have seen disabled in the Comment field of the results for all of the indexes except the PRIMARY key. In our example, we then reenabled the indexes for faster retrieval of data by users.

  • Discarding or Importing Tablespace
  • InnoDB tables use table-spaces instead of individual files for each table. A table-space can involve multiple files and can allow a table to exceed the filesystem file limit as a result. You can use the TABLESPACE clauses in the ALTER TABLE statement to delete or import a table-space.

    ALTER TABLE workreq
    IMPORT TABLESPACE;

    This statement imports the .idb file if it's in the database's directory. Replacing the IMPORT keyword with DISCARD will delete the .idb file.

    Reording Rows: You can use the next clause syntax structure, the ORDER BY clause, to permanently reorder the rows in a given table. Note that after an ALTER TABLE statement, any new rows insertedwill be added to the end of the table and the table will not be reordered automatically. To enforce another order, you will need to run ALTER TABLE again with this clause. The only reason to use this clause is for tables that rarely change, because reordering sometimes improves performance.

    In most cases, instead of reordering the storage of the table, it's recommended you include an ORDER BY clause in your SELECT statements.

    Here's an example with this clause:

    ALTER TABLE clients
    ORDER BY client_name;

    It's possible to give more than one column name in the order, separated by commas. Expressions cannot be used, though. You can, however, specify ascending (ASC, the default) or descending (DESC) order for each column.

  • Renaming a Table:
  • You can use the RENAME clause shown to change the name of an existing table. Here is an example of this clause:

    ALTER TABLE client RENAME TO clients;

    This statement renames the client table to clients. The TO keyword is not required; it's a matter of style preference and compatibility. A statement with this clause is equivalent to using the RENAME TABLE statement, except that the RENAME clause does not change user privileges related to the table to refer to the new name of the table.