Table of Contents for

MySQL in a Nutshell, 2nd Edition

Use this statement to change an existing table’s structure and other properties. A table may be altered with this statement in the following ways:

The IGNORE flag applies to all clauses and instructs MySQL to ignore any error messages regarding duplicate rows that may occur as a result of a column change. It will keep the first unique row found and drop any duplicate rows. Otherwise, the statement will be terminated and changes will be rolled back.

This statement requires the ALTER, CREATE, and INSERT privileges for the table being altered, at a minimum. While an ALTER TABLE statement is being executed, users will be able to read the table, but usually they won’t be able to modify data or add data to a table being altered. Any INSERT statements using the DELAYED parameter that are not completed when a table is altered will be canceled and the data lost. Increasing the size of the myisam_sort_buffer_size system variable will sometimes make MyISAM table alterations go faster.

The syntax and explanation of each clause follows, with examples, grouped by type of clause. Multiple alterations may be combined in a single ALTER TABLE statement. They must be separated by commas and each clause must include the minimally required elements.

ALTER TABLE: ADD clauses for columns

ALTER [IGNORE] TABLE table
ADD [COLUMN] column definition [FIRST|AFTER column] |
ADD [COLUMN] (column definition,...)

These clauses add columns to a table. The same column definitions found in a CREATE TABLE statement are used in this statement. Basically, the statements list the name of the column followed by the column data type and the default value or other relevant components. The COLUMN keyword is optional and has no effect.

By default, an added column is appended to the end of the table. To insert a new column at the beginning of a table, use the FIRST keyword at the end of the ADD COLUMN clause. To insert it after a particular existing column, use the AFTER keyword followed by the name of the column after which the new column is to be inserted:

ALTER TABLE workreq
ADD COLUMN req_type CHAR(4) AFTER req_date,
ADD COLUMN priority CHAR(4) AFTER req_date;

In this example, two columns are added after the existing req_date column. The clauses are executed in the order that they are given. Therefore, req_type is placed after req_date. Then priority is added after req_date and before req_type. Notice that you can give more than one clause in one ALTER TABLE statement; just separate them with commas.

ALTER TABLE: ADD clause for standard indexes

ALTER [IGNORE] TABLE table
ADD {INDEX|KEY} [index] [USING index_type] (column,...)

Use the ADD INDEX clause to add an index to a table. If you omit the name of the index, MySQL will set it to the name of the first column on which the index is based. The type of index may be stated, but usually it’s not necessary. The names of one or more columns for indexing must be given within parentheses, separated by commas.

Here is an example of how you can add an index using the ALTER TABLE statement, followed by the SHOW INDEXES statement with the results:

ALTER TABLE clients
ADD INDEX client_index
(client_name(10), city(5)) USING BTREE;

SHOW INDEXES FROM clients \G

*************************** 1. row ***************************
       Table: clients
  Non_unique: 0
    Key_name: PRIMARY
Seq_in_index: 1
 Column_name: client_id
   Collation: A
 Cardinality: 0
    Sub_part: NULL
      Packed: NULL
        Null: 
  Index_type: BTREE
     Comment: 
*************************** 2. row ***************************
       Table: clients
  Non_unique: 1
    Key_name: client_index
Seq_in_index: 1
 Column_name: client_name
   Collation: A
 Cardinality: NULL
    Sub_part: 10
      Packed: NULL
        Null: YES
  Index_type: BTREE
     Comment: 
*************************** 3. row ***************************
       Table: clients
  Non_unique: 1
    Key_name: client_index
Seq_in_index: 2
 Column_name: city
   Collation: A
 Cardinality: NULL
    Sub_part: 5
      Packed: NULL
        Null: YES
  Index_type: BTREE
     Comment: 

As you can see in the results, there was already an index in the table clients (see row 1). The index we’ve added is called client_index. It’s based on two columns: the first 10 characters of the client_name column and the first 5 characters of the city column. Limiting the number of characters used in the index makes for a smaller index, which will be faster and probably just as accurate as using the complete column widths. The results of the SHOW INDEXES statement show a separate row for each column indexed, even though one of the indexes involves two rows.

The table in this example uses the MyISAM storage engine, which uses the BTREE index type by default, so it was unnecessary to specify a type. See Appendix A for more information about storage engines and available index types. Before MySQL version 5.1.10, the USING subclause could come either before or after the column list, but as of version 5.1.10, it must follow the column list.

ALTER TABLE: ADD clause for FULLTEXT indexes

ALTER [IGNORE] TABLE table
ADD FULLTEXT [INDEX|KEY] [index] (column,...) [WITH PARSER parser]

The ADD FULLTEXT clause adds an index to a TEXT column within an existing MyISAM table. A FULLTEXT index can also index CHAR and VARCHAR columns. This type of index is necessary to use the FULLTEXT functionality (the MATCH() AGAINST() function from Chapter 11). The INDEX and KEY keywords are optional as of MySQL version 5.

With this index, the whole column will be used for each column given. Although you can instruct it to use only the first few characters of a table, it will still use the full column for the index. The WITH PARSER clause may be used to give a parser plugin for a FULLTEXT index:

ALTER TABLE workreq
ADD FULLTEXT INDEX notes_index
(client_description, technician_notes);

SHOW INDEXES FROM workreq \G

*************************** 2. row ***************************
       Table: workreq
  Non_unique: 1
    Key_name: notes_index
Seq_in_index: 1
 Column_name: client_description
   Collation: NULL
 Cardinality: NULL
    Sub_part: NULL
      Packed: NULL
        Null: YES
  Index_type: FULLTEXT
     Comment: 
*************************** 3. row ***************************
       Table: workreq
  Non_unique: 1
    Key_name: notes_index
Seq_in_index: 2
 Column_name: technician_notes
   Collation: NULL
 Cardinality: NULL
    Sub_part: NULL
      Packed: NULL
        Null: YES
  Index_type: FULLTEXT
     Comment: 

I’ve eliminated the first row from these results because it relates to the primary index, not the one created here.

As of version 5.1 of MySQL, you can use the WITH PARSER clause to specify a parser plugin for a FULLTEXT index. This option requires that the plugin table be loaded in the mysql database. This table is part of the current installation of MySQL. If you’ve upgraded MySQL and the plugin table is not in your system’s mysql database, use the mysql_upgrade script to add it. Use the SHOW PLUGINS statement to see which plugins are installed.

ALTER TABLE: ADD clause for SPATIAL indexes

ALTER [IGNORE] TABLE table
ADD SPATIAL [INDEX|KEY] [index] (column,...)

This ADD clause is used to add a SPATIAL index. A SPATIAL index can index only spatial columns. A spatial index is used in a table that holds data based on the Open Geospatial Consortium (http://www.opengis.org) data for geographical and global positioning satellite (GPS) systems. For our purposes here, this clause is necessary to add an index for spatial extensions. For MyISAM tables, the RTREE index type is used. The BTREE is used by other storage engines that use nonspatial indexes of spatial columns. Here is an example:

ALTER TABLE squares
ADD SPATIAL INDEX square_index (square_points);

SHOW INDEXES FROM squares \G

*************************** 1. row ***************************
       Table: squares
  Non_unique: 1
    Key_name: square_index
Seq_in_index: 1
 Column_name: square_points
   Collation: A
 Cardinality: NULL
    Sub_part: 32
      Packed: NULL
        Null: 
  Index_type: SPATIAL
     Comment:

Notice that when we created the table, we specified that the column square_points is NOT NULL. This is required to be able to index the column. See the CREATE INDEX statement for SPATIAL indexes in this chapter for an explanation and more examples related to spatial indexes.

ALTER TABLE: ADD clauses for foreign keys

ALTER [IGNORE] TABLE table
ADD [CONSTRAINT [symbol]] PRIMARY KEY [USING index_type] (column,...) |
ADD [CONSTRAINT [symbol]] UNIQUE [INDEX|KEY] index [USING index_type] 
   (column,...) |
ADD [CONSTRAINT [symbol]] FOREIGN KEY [index] (column,...) 
   [REFERENCES table (column,...)
   [ON DELETE {RESTRICT|CASCADE|SET NULL|NO ACTION|SET DEFAULT}]
   [ON UPDATE {RESTRICT|CASCADE|SET NULL|NO ACTION|SET DEFAULT}]]

These ADD clauses add foreign keys and references to InnoDB tables. A foreign key is an index that refers to a key or an index in another table. See the explanation of the CREATE TABLE statement later in this chapter for more information and for an example of an SQL statement involving the creation of foreign keys in a table. The various flags shown are also explained in the CREATE TABLE statement.

Here is an example:

CREATE TABLE employees 
(emp_id INT AUTO_INCREMENT PRIMARY KEY, 
tax_id CHAR(12), 
emp_name VARCHAR(100))
ENGINE = INNODB;

CREATE TABLE employees_telephone 
(emp_id INT, 
tel_type ENUM('office','home','mobile'),
tel_number CHAR(25)) 
ENGINE = INNODB;

ALTER TABLE employees_telephone 
ADD FOREIGN KEY emp_tel (emp_id) 
REFERENCES employees (emp_id) 
ON DELETE RESTRICT;

The first two SQL statements create InnoDB tables: one for basic employee information and the other for employee telephone numbers. Using the ALTER TABLE statement afterward, we add a foreign key restriction between the two. Let’s look at the results using the SHOW TABLE STATUS statement, because the SHOW INDEXES statement won’t show foreign key restraints:

SHOW TABLE STATUS FROM human_resources 
LIKE 'employees_telephone' \G

*************************** 1. row ***************************
           Name: employees_telephone
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 0
 Avg_row_length: 0
    Data_length: 16384
Max_data_length: 0
   Index_length: 16384
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2007-04-03 04:01:39
    Update_time: NULL
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options: 
        Comment: InnoDB free: 4096 kB; ('emp_id') 
                 REFER 'human_resources'.'employees'('emp_id')

In the Comment field, we can see that we’ve created a restraint on the main table employees from employees_telephone. We’re telling MySQL not to allow a row for an employee to be removed from the employees table without first removing the rows of data for the employee in the employees_telephone table.

In the following example, we first insert an employee in the employees table, then add her home telephone number to the second table, and then attempt to delete her from the first table:

INSERT INTO employees 
VALUES(1000,'123-45-6789','Paola Caporale');

INSERT INTO employees_telephone 
VALUES(1000,2,'+39 343-12-34-5678');

DELETE FROM employees WHERE emp_id = 1000;

ERROR 1451 (23000): Cannot delete or update a parent row: 
a foreign key constraint fails 
('human_resources'.'employees_telephone', 
  CONSTRAINT 'employees_telephone_ibfk_1'
  FOREIGN KEY ('emp_id') REFERENCES 'employees' ('emp_id')
)

As you can see, we cannot delete the employee from the employees table and leave the stray row of data in the employees_telephone table. We have to delete the data in employees_telephone first, before deleting the related data from employees. See the explanation under CREATE TABLE in the CREATE TABLE: Foreign key references” section later in this chapter for examples of the other options with foreign keys. Incidentally, you can’t drop and add a foreign key in the same ALTER TABLE statement.

ALTER TABLE: CHANGE clauses

ALTER [IGNORE] TABLE table
ALTER [COLUMN] column {SET DEFAULT value|DROP DEFAULT} |
CHANGE [COLUMN] column column definition [FIRST|AFTER column] |
MODIFY [COLUMN] column definition [FIRST|AFTER column]

These three clauses are used to alter an existing column in a table. The first syntax structure is used either to set the default value of a column to a particular value or to reset it back to its default value for its column type (usually NULL or 0). The other two syntax structures are used primarily to change the column definitions. The COLUMN keyword is optional and has no effect.

To change the column’s character set, add CHARACTER SET to the end of the column definition for the CHANGE or MODIFY clauses, followed by the character set name to use. Here’s an example of the first clause:

ALTER TABLE clients
ALTER COLUMN city SET DEFAULT 'New Orleans';

This statement sets the default value of the city column in the clients table to a value of New Orleans, because that’s where most of the clients are located.

The clauses that change column definitions are roughly synonymous; they follow the standards of different SQL systems for the sake of compatibility (e.g., MODIFY is used with Oracle). They can also be used to relocate the column within the table schema with the FIRST or the AFTER keywords. If a column’s data type is changed, MySQL attempts to adjust the data to suit the new data type. If a column width is shortened, MySQL truncates the data and generates warning messages for the affected rows. Indexes related to changed columns will be adjusted automatically for the new lengths.

In the CHANGE clause, the current column name must be specified first, followed by either the same column name if the name is to remain the same, or a new column name if the name is to be changed. The full column definition for the column must be given as well, even if it’s not to be changed.

The MODIFY clause cannot be used to change a column’s name, so the column name appears only once with it.

The following SQL statement shows the columns in the clients table, where the column name begins with a c and contains an i to list the columns that begin with either client or city. After viewing these limited results, we change one column using each of the clauses for changing column definitions:

SHOW COLUMNS FROM clients LIKE 'c%i%';

+--------------+--------------+------+-----+---------+----------------+
| Field        | Type         | Null | Key | Default | Extra          |
+--------------+--------------+------+-----+---------+----------------+
| client_id    | int(11)      | NO   | PRI | NULL    | auto_increment | 
| client_name  | varchar(255) | YES  | MUL | NULL    |                | 
| city         | varchar(255) | YES  |     | NULL    |                | 
| client_zip   | char(10)     | YES  |     | NULL    |                | 
| client_state | char(2)      | YES  |     | NULL    |                | 
+--------------+--------------+------+-----+---------+----------------+

ALTER TABLE clients
CHANGE COLUMN city client_city VARCHAR(100) CHARACTER SET 'latin2',
MODIFY COLUMN client_state CHAR(4) AFTER client_city;

After looking at the current columns, we’ve decided to change the name of the city column to client_city to match the other related columns, and to enlarge the client_state column and move it before the column for the postal ZIP code. To do this, the CHANGE clause is used to change the name of the city column, but not its column type and size. The second clause changes the column type and size and relocates the client_state column to a position after the client_city column.

When a column is changed, MySQL will attempt to preserve the data. If a column size is reduced, the data won’t be completely deleted, but it may be truncated, in which case the results will show a number of warnings. Use the SHOW WARNINGS statement to view them.

ALTER TABLE: DROP column clause

ALTER [IGNORE] TABLE table
DROP [COLUMN] column

The DROP clause of the ALTER TABLE statement removes a given column from a table and deletes the column’s data. A table must have at least one column, so this statement will fail if used on the only column in a table. Use the DROP TABLE statement to delete a table. If a dropped column is part of an index, the column will be removed automatically from the index definition. If all of the columns of an index are dropped, the index will automatically be dropped.

Here is an example including this clause:

ALTER TABLE clients
DROP COLUMN miscellaneous,
DROP COLUMN comments;

This statement drops two columns and deletes the data they contain without warning. Notice that multiple columns may be dropped by separating each clause by a comma. It’s not possible to combine clauses. That is to say, ...DROP COLUMN (miscellaneous, comments) is not permitted. Once a column has been deleted, you won’t be able to recover its data from MySQL. Instead, you’ll have to restore the table from a backup of your data if you made one.

ALTER TABLE: DROP index clauses

ALTER [IGNORE] TABLE table
DROP INDEX index |
DROP PRIMARY KEY |
DROP FOREIGN KEY foreign_key_symbol

These clauses are used to delete indexes. A standard index is fairly easy to eliminate with the first syntax shown. Here’s an example of its use:

ALTER TABLE clients 
DROP INDEX client_index;

The second syntax deletes the primary key index of a table. However, if the primary key is based on a column with an AUTO_INCREMENT type, you may need to change the column definition in the same statement so it is no longer AUTO_INCREMENT before you can drop the primary key. Here is an example in which we fail to change the indexed column first:

ALTER TABLE clients
DROP PRIMARY KEY;

ERROR 1075 (42000): Incorrect table definition; 
there can be only one auto column and it must be defined as a key

ALTER TABLE clients
CHANGE client_id client_id INT,
DROP PRIMARY KEY;

The first SQL statement here causes an error in which MySQL complains that if we are going to have a column with AUTO_INCREMENT, it must be a key column. So using the CHANGE clause in the second SQL statement, we change the client_id column from INT AUTO_INCREMENT to just INT. After the AUTO_INCREMENT is removed, the PRIMARY KEY may be dropped. Before version 5.1 of MySQL, if a primary key doesn’t exist, the first UNIQUE key is dropped instead. After version 5.1, an error is returned and no key is dropped.

To delete a foreign key, the third syntax is used. Here is an example that deletes a foreign index:

ALTER TABLE client
DROP FOREIGN KEY '0_34531';

In this example, the name of the index is not the name of any of the columns, but an index that was created by combining two columns and was given its own name. The name was changed by InnoDB automatically. To get a list of indexes for a table, use the SHOW CREATE TABLE statement.

ALTER TABLE: Miscellaneous clauses

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

You can use these miscellaneous clauses with the ALTER TABLE statement to change a variety of table properties. They are described here in the order that they are listed in the syntax.

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;

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;

ALTER TABLE workreq
IMPORT TABLESPACE;

ALTER TABLE clients
ORDER BY client_name;

ALTER TABLE client RENAME TO clients;

ALTER TABLE: Partition altering clauses

ALTER [IGNORE] TABLE table
PARTITION BY options | 
ADD PARTITION (definition) | 
COALESCE PARTITION number | 
DROP PARTITION partition | 
REORGANIZE PARTITION partition INTO (definition) | 
REMOVE PARTITIONING

These table partition clauses for ALTER TABLE may be used to add or remove partitions in a table. They were added as of version 5.1.6 of MySQL. For partition clauses that analyze, check, optimize, rebuild, and repair partitions in a table, see the next subsection (ALTER TABLE: Partition administration clauses”). Also, see the CREATE TABLE statement explanation for more information on table partitioning.

It should be noted that the execution of the partition clauses for ALTER TABLE is very slow. You may not want to use them with data that is in use if you can avoid it. Instead, you might deploy a method of locking the table to be partitioned for read-only activities, making a copy of the table, partitioning the new table, and switching the new table with the old one, but keeping the old table as a backup copy in case there are problems.

This section includes several examples of partitioning a MyISAM table. The partition clauses are explained as they are used in each example. Partitioning is visible at the filesystem level, so to start, let’s look at a table’s files:

ls -1 clients*

clients.frm
clients.MYD
clients.MYI

We used the ls command (because this server is running Linux) at the command line to get a directory listing of the files for the clients table, in the sales_dept database subdirectory, in the data directory for MySQL. You can see the usual three file types for a MyISAM table.

The PARTITION BY clause can be used to initially partition a table with the ALTER TABLE statement. Any partition options used with the same clause in the CREATE TABLE statement may be used in ALTER TABLE. See the definition of the CREATE TABLE statement later in this chapter for more options.

In the following example, we alter the table clients using this clause to create partitions:

ALTER TABLE clients
PARTITION BY KEY(client_id)
PARTITIONS 2;

In this statement, we are instructing MySQL to partition the given table by the KEY method using the client_id column. We further tell it to split the table into two partitions. Now, let’s run the ls command again to see the results at the filesystem level:

ls -1 clients*

clients.frm
clients.par
clients#P#p0.MYD
clients#P#p0.MYI
clients#P#p1.MYD
clients#P#p1.MYI

As you can see, we now have a pair of index and datafiles for each partition, along with another file related to the partition schema (i.e., the .par file). The table schema file (i.e., the .frm file) remains unchanged.

The ADD PARTITION clause adds a new partition to a table in which partitions are determined based on a range of values. To demonstrate this, let’s partition the clients table again, but this time we’ll base the partitioning on a range of values for the client_id column, the primary key. If a table has a primary key, that key must be included in the basis of the partitions:

ALTER TABLE clients
ADD PARTITION (PARTITION p2);

The REMOVE PARTITIONING clause removes partitioning from a table. It shifts data back to one datafile and one index file. Here is an example of its use:

ALTER TABLE clients
REMOVE PARTITIONING;

For some situations, the ADD PARTITION clause discussed previously won’t work. In particular, it won’t work with a table in which the last partition was given the range of MAXVALUE:

ALTER TABLE clients
PARTITION BY RANGE (client_id) (
PARTITION p0 VALUES LESS THAN (400),
PARTITION p1 VALUES LESS THAN MAXVALUE);

ALTER TABLE clients
ADD PARTITION (PARTITION p2 VALUES LESS THAN (800));

ERROR 1481 (HY000): 
VALUES LESS THAN value must be strictly increasing for each partition

Instead of ADD PARTITION, the REORGANIZE PARTITION clause can be used to split the data contained in the last partition into two separate partitions. This clause can be used to separate the data in an existing partition into multiple partitions based on their given partition definitions.

Here is an example of this clause using the partitions previously described:

ALTER TABLE clients
REORGANIZE PARTITION p1 INTO
(PARTITION p1 VALUES LESS THAN (800),
PARTITION p2 VALUES LESS THAN MAXVALUE);

When experimenting with an empty table, this SQL statement takes my server 10 seconds to execute. Consider this when using this clause or any partitioning clauses with ALTER TABLE.

The DROP PARTITION clause may be used to eliminate named partitions in an existing table and to delete the data contained in the dropped partitions. To reduce the number of partitions without loss of data, see the COALESCE PARTITION clause for this same SQL statement. For an example of the DROP PARTITION clause, if you have a table that has six partitions and you want to delete two of them, you could execute an SQL statement like the second one here:

CREATE TABLE clients
(client_id INT,
name VARCHAR(255))
PARTITION BY RANGE (client_id) (
PARTITION p0 VALUES LESS THAN (400),
PARTITION p1 VALUES LESS THAN (800),
PARTITION p2 VALUES LESS THAN (1000),
PARTITION p3 VALUES LESS THAN MAXVALUE);

ALTER TABLE clients
DROP PARTITION p1, p2;

Notice that the ALTER TABLE statement is dropping two middle partitions and not the last one. The data contained in the two dropped would be lost if they had any. Because of the MAXVALUE parameter of the last partition, any new rows of data that have a client_id of 400 or greater will be stored in the p3 partition. Partitions need to be in order, but not sequentially named.

The COALESCE PARTITION clause may be used to reduce the number of partitions in an existing table by the number given. For example, if you have a table that has four partitions and you want to reduce it to three, you could execute a statement like the ALTER TABLE one here:

CREATE TABLE clients
(client_id INT,
name VARCHAR(255))
PARTITION BY HASH( client_id )
PARTITIONS 4;

ALTER TABLE clients
COALESCE PARTITION 1;

Notice that the PARTITION keyword in this last SQL statement is not plural. Also notice that you give the number of partitions by which you want to reduce the partitions, not the total you want. If you give a value equal to or greater than the number of partitions in the table, you’ll receive an error instructing you that you must use DROP TABLE instead.

See the CREATE TABLE statement explanation for more information about table partitioning.

ALTER TABLE: Partition administration clauses

ALTER [IGNORE] TABLE table
ANALYZE PARTITION partition | 
CHECK PARTITION partition | 
OPTIMIZE PARTITION partition |
REBUILD PARTITION partition | 
REPAIR PARTITION partition

Because the ANALYZE TABLE, CHECK TABLE, OPTIMIZE TABLE, and REPAIR TABLE statements do not work with partitioned tables, you will have to use the clauses of ALTER TABLE in this subsection instead. They all follow the same syntax format: the clause is followed by a comma-separated list of partitions to be administered.

The ANALYZE PARTITION clause may be used to read and store the indexes of a partition:

ALTER TABLE clients
ANALYZE PARTITION p0, p1, p2;

To check a partition for corrupted data and indexes, use the CHECK PARTITION clause:

ALTER TABLE clients
CHECK PARTITION p0, p1, p2;

Use the OPTIMIZE PARTITION clause to compact a partition in which the data has changed significantly:

ALTER TABLE clients
OPTIMIZE PARTITION p0, p1, p2;

The REBUILD PARTITION clause defragments the given partitions:

ALTER TABLE clients
REBUILD PARTITION p0, p1, p2;

The REPAIR PARTITION clause attempts to repair corrupted partitions, similar to the REPAIR TABLE statement for tables:

ALTER TABLE clients
REPAIR PARTITION p0, p1, p2;

See the CREATE TABLE statement explanation for more information about table partitioning.

ALTER TABLE: Table options

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[,...])

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 BTREE
) 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. 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 uncompressed 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 Redundant Arrays of Independent Disks (RAID) to be used. However, support for RAID has been removed from MySQL as of version 5.0. This SQL statement is 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 that 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 later in this chapter for more information and examples regarding many of the options for the ALTER TABLE statement.