MySQL in a Nutshell [60]
) ENGINE=MyISAM
AUTO_INCREMENT=1001
DEFAULT CHARSET=latin1 MAX_ROWS=1000
COMMENT='This table lists basic information on clients.'
I’ve shortened the results shown here to save space and to focus on the options. 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://russell: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 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 will 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 inserts:
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 use the default. This option was added in version 5.1.10 of MySQL:
ALTER TABLE clients
KEY_BLOCK_SIZE = 1024;
The MAX_ROWS and MIN_ROWS options are used to set the maximum and minimum rows of a table, respectively. Use the SHOW CREATE TABLE statement to see the results of these options:
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.