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:

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.

Категории