MySQL in a Nutshell [56]
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 Binary Large Object (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 and enabling keys
You can use the third clause (DISABLE and ENABLE) to disable or enable the updating of nonunique 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 afterward:
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 tablespaces instead of individual files for each table. A tablespace 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 tablespace:
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.
Reordering 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 inserted will 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 BY clause, separated by commas. Expressions cannot be used. You can, however, specify ascending (ASC, the default) or descending (DESC) order for each column.
Renaming a table
You can use the RENAME clause 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