Introduction

You'll probably find it necessary on occasion to redesign some of your tables. A change in an application's specification may require that you record information not accounted for in the original definition of a table used by that application. Or you may find that an AUTO_INCREMENT column is running out of room to generate new sequence numbers and you need to change the column to use a larger integer type. MySQL offers many possibilities for modifying a table's structure. This chapter describes how to make the following types of changes:

• Dropping, adding, or repositioning a column.

  Columns that have become unnecessary or that you discover to be redundant may be removed to simplify a table and to save space. Or you may move columns from one table to another as part of a normalization procedure. Columns may be added when you need to record additional types of information.

• Changing a column definition or name.

  If a column as originally created does not serve your purposes, you may be able to correct the problem by redefining it. For example, you can convert a string column that is case sensitive to one that is not, or vice versa. Or you may have an AUTO_INCREMENT column that is a TINYINT and has room only for 127 sequence values. By changing the column to be unsigned or to use a larger integer type, you can extend the range of the sequence. Renaming a column can be useful if after an upgrade to a more recent version of MySQL you find that a column name is now a reserved word. Or maybe you just want to rename a column like num to something more descriptive like test_score to make the column's purpose more explicit.

• Changing a table's type.

  The various table types in MySQL have differing characteristics. If a table's type is less suitable for your applications than another type, you can convert it.

• Renaming a table.

  Like renaming a column, this can be done if you come up with a better name. Or you can rename for other purposes such as rotating the names of a set of tables used for logging.

• Modifying a table's index structure

  Dropping an index that is rarely used can improve performance of inserts and updates of table rows, because that index then need not be updated. Adding an index to a column that you reference frequently in queries can be useful for improving SELECT performance. Indexing can also be used to remove duplicate values from a table.

Most of these operations are handled by ALTER TABLE, one of MySQL's most powerful but least appreciated statements. By that I mean you can do a lot with ALTER TABLE, but many MySQL users do not fully exploit its capabilities. Perhaps this is because the ALTER TABLE statement has so many options that its syntax is somewhat daunting. I hope that this chapter will demystify the statement and make its utility more apparent.

Before you begin reading the rest of this chapter, create the following table for use in working through the examples:

CREATE TABLE mytbl
(
 i INT,
 c CHAR(1)
);

As you make each of the modifications shown in the following sections, you can see the resulting changes in this table's structure, either by issuing a SHOW COLUMNS FROM mytbl statement or a SHOW CREATE TABLE mytbl statement. For example, after you create mytbl, SHOW COLUMNS should display the following information:

mysql> SHOW COLUMNS FROM mytbl;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| i | int(11) | YES | | NULL | |
| c | char(1) | YES | | NULL | |
+-------+---------+------+-----+---------+-------+

Observe that MySQL automatically assigns default values and determines whether the columns can contain NULL values, even though the CREATE TABLE statement does not explicitly specify these attributes. The significance of this is discussed in Recipe 8.4.