MySQL in a Nutshell [59]
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[,...])
This subsection 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 statement in this chapter for more information about the options available.) You can give multiple options to ALTER TABLE in a comma-separated list.
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 1,000 so that the next row inserted will be 1,001. 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: sales
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 set and collation are explicitly specified. Use the SHOW CHARACTER SET and SHOW COLLATION statements 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 REPAIR TABLE to recalculate the checksum for the table:
ALTER TABLE clients
CHECKSUM = 0;
The COLLATE option sets the collation 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 SHOW CREATE TABLE statement to see the collation and character set for the 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 administrators 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