Working with Tables

Altering Table Structure

ALTER TABLE is a versatile statement in MySQL, and you can use it to do many things. We've already seen some of its capabilities. You can also use ALTER TABLE to rename tables, add or drop columns, change column types, and more. In this section, we'll cover some of the other features it offers.

ALTER TABLE is useful when you find that the structure of a table no longer reflects what you want to do with it. You may want to use the table to record additional information, or perhaps it contains information that has become superfluous.

Maybe existing columns are too small, or perhaps you've declared them larger than it turns out you need and you'd like to make them smaller to save space and improve query performance. Or maybe you just typed in the table's name incorrectly when you issued the CREATE TABLE statement. The following are some examples:

. You're running a research project. You assign case numbers to research records using an AUTO_INCREMENT column. You didn't expect your funding to last long enough to generate more than about 50,000 records, so you made the column type SMALLINT UNSIGNED, which holds a maximum of 65,535 unique values.

However, the funding for the project was renewed, and it looks like you may generate another 50,000 records. You need to make the type bigger to accommodate more case numbers.

. Size changes can go the other way, too. Maybe you created a CHAR(255) column but now recognize that no value in the table is more than 100 characters long. You can shorten the column to save space.

. You want to convert a table to another type to take advantage of features offered by that type. For example, an ISAM table won't allow NULL values in indexed columns. If you really need to index a column that contains NULL, you can convert it to be a MyISAM table.

The syntax for ALTER TABLE is as follows:

ALTER TABLE tbl_name action, ... ;

Each action specifies a modification you want to make to the table. Some database engines allow only a single action in an ALTER TABLE statement, but MySQL allows multiple actions; just separate the actions by commas.

This extension to ALTER TABLE is useful because some types of table modifications cannot be performed with single-action statements.

For example, it's impossible to change all the VARCHAR columns to CHAR columns by changing them one at a time. You must change them all at once.

The following examples show some of the capabilities of ALTER TABLE:

. Renaming a table. Use a RENAME clause that specifies the new table name:

ALTER TABLE tbl_name RENAME TO new_tbl_name;

Another way to rename tables is with RENAME TABLE, available as of MySQL 3.23.23. The syntax looks like this:

RENAME TABLE old_name TO new_name;

One thing that RENAME TABLE can do that ALTER TABLE cannot is rename multiple tables in the same statement. For example, you can swap the names of two tables like this:

RENAME TABLE t1 TO tmp, t2 TO t1, tmp TO t1;

If you qualify a table name with a database name, you can move a table from one database to another by renaming it. Either of the following statements move the table t from the sampdb database to the test database:

ALTER TABLE sampdb.t RENAME TO test.t; RENAME TABLE sampdb.t TO test.t;

You cannot rename a table to use a name that already exists, however.

. Changing a column type. To change a column type, you can use either a CHANGE or MODIFY clause. Suppose the column in a table mytbl is SMALLINT UNSIGNED and you want to change it to MEDIUMINT UNSIGNED. Do so using either of the following commands:

. ALTER TABLE mytbl MODIFY i MEDIUMINT UNSIGNED; . ALTER TABLE mytbl CHANGE i i MEDIUMINT UNSIGNED;

Why is the column named twice in the command that uses CHANGE? Because one thing that CHANGE can do that MODIFY cannot is to rename the column in addition to changing the type.

If you had wanted to rename i to j at the same time you changed the type, you'd do so like this:

ALTER TABLE mytbl CHANGE i j MEDIUMINT UNSIGNED;

The important thing with CHANGE is that you name the column you want to change and then specify a complete column declaration, which includes the column name. You must include the name in the declaration, even if it's the same as the old name.

As of MySQL 4.1, you can assign character sets to individual columns, so it's possible to use the CHARACTER SET attribute in a column's definition to change its character set:

ALTER TABLE t MODIFY c CHAR(20) CHARACTER SET ucs2;

An important reason for changing column types is to improve query efficiency for joins that compare columns from two tables. A comparison is quicker when the columns are both the same type. Suppose you're running a query like the following:

SELECT ... FROM t1, t2 WHERE t1.name = t2.name;

If t1.name is CHAR(10) and t2.name is CHAR(15), the query won't run as quickly as if they were both CHAR(15). You can make them the same by changing t1.name using either of the following commands:

ALTER TABLE t1 MODIFY name CHAR(15); ALTER TABLE t1 CHANGE name name CHAR(15);

Prior to MySQL 3.23, it's essential that joined columns be of the same type, or indexes will not be used for the comparison and the join will run more slowly.

But even from 3.23 and later, when indexes can be used in joins between dissimilar column types, a query will still be faster if the types are identical.

. Converting a table from variable-length rows to fixed-length rows. Suppose you have a table chartbl that was created like this:

CREATE TABLE chartbl (name VARCHAR(40), address VARCHAR(80));

The table contains VARCHAR columns and you want to convert them to CHAR columns to see what kind of performance improvements you get. (If the table uses ISAM or MyISAM storage format, fixed-length rows generally can be processed more quickly than variable-length rows.)

The problem here is that you need to change the columns all at once in the same ALTER TABLE statement. You can't do them one at a time or the attempt will be ineffective.

(Try changing just one of them and then run DESCRIBE chartbl; you'll find that the columns are still defined as VARCHAR!) The reason for this is that if you change a single column at a time, MySQL notices that the table still contains variable-length columns and reconverts the changed column back to VARCHAR to save space. To deal with this, change all the VARCHAR columns at the same time:

ALTER TABLE chartbl MODIFY name CHAR(40), MODIFY address CHAR(80);

Now DESCRIBE will show that the table contains CHAR columns. It's exactly this type of operation that makes it important that ALTER TABLE support multiple actions in the same statement.

There is a caveat to be aware of when you want to convert a table as just shown: BLOB and TEXT types are variable-length types with no fixed-length equivalent.

The presence of any BLOB or TEXT columns in a table will defeat any attempt to convert it to fixed-length row format because even one variable-length column in a table causes it to have variable-length rows.

. Converting a table from fixed-length rows to variable-length rows. Suppose you discover that chartbl is indeed faster with fixed-length rows. On the other hand, it takes more storage than you'd like, so you decide to convert it back to its original form to save space.

Converting a table in this direction is much easier. You only need to change one CHAR column to VARCHAR and MySQL will convert the other CHAR columns automatically. To convert the chartbl table, either of the following statements will do:

. ALTER TABLE chartbl MODIFY name VARCHAR(40); . ALTER TABLE chartbl MODIFY address VARCHAR(80); . Converting a table type. To convert a table from one storage format to another, use a TYPE clause to change the table's type: . ALTER TABLE tbl_name TYPE = tbl_type;

tbl_type is a type specifier such as ISAM, MYISAM, HEAP, BDB, or INNODB (lettercase does not matter).

Changing table types can be useful when you upgrade your MySQL installation to a newer version that provides additional table-handling features. For example, if you upgrade from a pre-3.23 version of MySQL to 3.23 or later, your older tables will be in ISAM format. To change them to MyISAM format, use the following statement for each one:

ALTER TABLE tbl_name TYPE = MYISAM;

Doing this allows you to take advantages of the capabilities that MyISAM offers than ISAM does not. For example, MyISAM tables are machine independent, so you can move them to other machines by copying table files directly, even if the machines have different hardware architectures.

Another reason to change a table type is to make it transaction-safe. Suppose you have a MyISAM table and discover that an application that uses it needs to perform transactional operations, including rollback in case failures occur.

MyISAM tables do not support transactions, but you can make the table transaction-safe by converting it to a BDB or InnoDB table:

ALTER TABLE tbl_name TYPE = BDB; ALTER TABLE tbl_name TYPE = INNODB;

ALTER TABLE is useful in many ways, but there are circumstances under which you should not use it. The following are two examples:

. HEAP tables are held in memory and disappear when the server exits. It is not a good idea to convert a table to type HEAP if you require the table to last beyond server shutdown.

. If you use a MERGE table to group a collection of MyISAM tables together, you should avoid using ALTER TABLE to modify any of the MyISAM tables unless you make the same change to all of them, and to the MERGE table as well.

The proper functioning of a MERGE table depends on its having the same structure as all of its constituent MyISAM tables.