MySQL SQL Syntax and Use

This chapter is from the book 

Creating, Dropping, Indexing, and Altering Tables

MySQL allows you to create tables, drop (remove) them, and change their structure using the CREATE TABLE, DROP TABLE, and ALTER TABLE statements. The CREATE INDEX and DROP INDEX statements allow you to add or remove indexes on existing tables. But before diving into the details for these statements, it's helpful to understand something about the different types of tables that MySQL supports.

Table Types

MySQL supports multiple table handlers, each of which implements a table type that has a specific set of properties or characteristics. The table types actually available to you will depend on your version of MySQL, how it was configured at build time, and the options with which it was started. The current table type handlers and the versions in which they are first available are listed in the following table:

Table Type	MySQL Version First Available
ISAM	All versions
MyISAM	3.23.0
MERGE	3.23.25
HEAP	3.23.0
BDB	3.23.17/3.23.34a
InnoDB	3.23.29/3.23.34a

Two version numbers are listed for BDB and InnoDB. The first number indicates when the table type appeared in binary distributions, the second when it became available in source distributions. MRG_MyISAM and BerkeleyDB are synonyms for MERGE and BDB. (From 3.23.29 through 3.23.36, the InnoDB table type was known as Innobase; thereafter, InnoDB is the preferred name, though Innobase is recognized as a synonym.)

Because MySQL can be configured in different ways, it's quite possible that a server for a given version of MySQL will not support all table types available in that version. See the "Getting Information about Databases and Tables" section later in this chapter to find out how to tell which types a given server actually supports. See the "Selecting Table Handlers" section in Chapter 11 for details on configuring the server.

The general characteristics of MySQL's table types are described in the following sections.

ISAM Tables

The ISAM handler manages tables that use the indexed sequential access method. The ISAM storage format is the original MySQL table type and is the only one available prior to Version 3.23. The ISAM handler has since been superceded by the MyISAM handler; MyISAM tables are the preferred general replacement because they have fewer limitations. The ISAM type is still available but is considered pretty much obsolete. Support for it will fade over time. (ISAM table support has been omitted from the embedded server now, for example, and probably will disappear entirely in MySQL 5.)

MyISAM Tables

The MyISAM storage format is the default table type in MySQL as of version 3.23, unless the server has been configured otherwise.

• Tables can be larger than for the ISAM storage method if your operating system itself allows large file sizes.

• Table contents are stored in machine-independent format. This means you can copy tables directly from one machine to another, even if they have different architectures.

• Relative to ISAM tables, MyISAM relaxes several indexing constraints. For details, see the "Indexing Tables" section later in this section.

• MyISAM format provides better key compression than does ISAM format. Both formats use compression when storing runs of successive similar string index values, but MyISAM also can compress runs of similar numeric index values because numeric values are stored with the high byte first. (Index values tend to vary faster in the low-order bytes, so high-order bytes are more subject to compression.) To enable numeric compression, use the PACK_KEYS=1 option when creating a table.

• MyISAM has more capable AUTO_INCREMENT handling than is available for other table types. The details of this are discussed in the "Working with Sequences" section of Chapter 2.

• For improved table integrity checking, each MyISAM table has a flag that is set when the table is checked by the server or by myisamchk. MyISAM tables also have a flag indicating whether a table was closed properly. If the server shuts down abnormally or the machine crashes, the flag can be used to detect tables that need to be checked. This can be done automatically at server startup time by specifying the --myisam-recover option.

• The MyISAM handler supports full text searching through the use of FULLTEXT indexes.

MERGE Tables

MERGE tables are a means for grouping multiple MyISAM tables into a single logical unit. By querying a MERGE table, you are in effect querying all the constituent tables. One advantage of this is that you can in effect exceed the maximum table size allowed by the file system for individual MyISAM tables.

The tables that make up a MERGE table must all have the same structure. This means the columns in each table must be defined with the same names and types in the same order, and the indexes must be defined in the same way and in the same order. It is allowable to mix compressed and uncompressed tables. (Compressed tables are produced with myisampack; see Appendix E, "MySQL Program Reference.")

A MERGE table cannot refer to tables in a different database.

HEAP Tables

The HEAP storage format uses tables that are stored in memory and that have fixed-length rows, two characteristics that make them very fast. HEAP tables are temporary in the sense that they disappear when the server terminates. However, in contrast to temporary tables created with CREATE TEMPORARY TABLE, HEAP tables are visible to other clients. Several constraints apply to HEAP tables that allow them to be handled more simply and thus more quickly:

• Indexes are used only for comparisons performed with the = and <=> operators. This is due to the use of hashed indexes, which are very fast for equality comparisons but not for range searches with comparison operators such as < or >. Indexes also are not used in ORDER BY clauses for this reason.

• You cannot have NULL values in indexed columns prior to MySQL 4.0.2.

• AUTO_INCREMENT columns cannot be used prior to MySQL 4.1.

• BLOB and TEXT columns cannot be used. Because rows are stored using fixed-length format, you cannot use variable length column types such as BLOB and TEXT. VARCHAR is allowed but is treated internally as the corresponding CHAR type.

BDB Tables

BDB tables are managed by the Berkeley DB handler developed by Sleepycat. The BDB handler offers these features:

• Transaction-safe tables with commit and rollback

• Automatic recovery after a crash

• Page-level locking for good concurrency performance under query mix conditions that include both retrievals and updates

InnoDB Tables

InnoDB tables are the most recent table type added to MySQL. They are managed by the InnoDB handler developed by Innobase Oy. The InnoDB handler offers the following features:

• Transaction-safe tables with commit and rollback.

• Automatic recovery after a crash.

• Foreign key support, including cascaded delete.

• Row-level locking for good concurrency performance under query mix conditions that include both retrievals and updates.

• InnoDB tables are managed within a separate tablespace rather than by using table-specific files like the other table types. The tablespace can consist of multiple files and can include raw partitions. The InnoDB handler, in effect, treats the tablespace as a virtual file system within which it manages the contents of all InnoDB tables.

• Tables can exceed the size allowed by the file system for individual files through use of multiple files or raw partitions in the tablespace.

Table Representation on Disk

Every table, no matter its format, is represented on disk by a file that contains the table's format (that is, its definition). This file has a basename that is the same as the table name and a .frm extension. For most table types, a table's contents are stored on disk using other files that are unique to the table. The exceptions are for HEAP and InnoDB tables, for which the .frm file is the only one that is uniquely associated with a given table. (HEAP table contents are stored in memory. InnoDB table contents are managed within the InnoDB tablespace in common with other InnoDB tables, not within files specific to a particular table.) The various table types use files with the following extensions:

Table Type	Files on Disk
ISAM	.frm (definition), .ISD (data), .ISM (indexes)
MyISAM	.frm (definition), .MYD (data), .MYI (indexes)
MERGE	.frm (definition), .MRG (list of constituent MyISAM table names)
HEAP	.frm (definition)
BDB	.frm (definition), .db (data and indexes)
InnoDB	.frm (definition)

For any given table, the files specific to it are located in the directory that represents the database to which the table belongs.

Table Type Portability Characteristics

Any table is portable to another server in the sense that you can dump it into a text file with mysqldump, move the file to the machine where the other server runs, and load the file to recreate the table. Portability as described in this section means that you can directly copy the files that represent the table on disk to another machine, install them into a database directory, and expect the MySQL server there to be able to use the table. Of course, HEAP tables do not satisfy this definition because their contents are stored in memory, not on disk. Of the other table types, some are portable and some are not:

• ISAM tables are stored in a machine-dependent format, so they are portable only between machines that have identical hardware characteristics.

• BDB tables are not portable because the location of the table is encoded into the table's .db file. This makes a BDB table location-specific within the file system of the machine on which the table was created. (That's the conservative view of BDB portability. I have experimented with BDB files in various ways, such as by moving them between database directories, renaming the files to use a different basename, and so on. I have not observed ill effects. But presumably it's better to play it safe and move BDB tables by dumping them with mysqldump and re-creating them on the destination machine by reloading the dump file.)

• MyISAM and InnoDB tables are stored in machine-independent format and are portable, assuming that your processor uses two's-complement integer arithmetic and IEEE floating-point format. Unless you have some kind of oddball machine, neither of these conditions should present any real issues. In practice, you're probably most likely to see portability- compromising variation in hardware if you're using an embedded server built for a special-purpose device, as these sometimes will use processors that have non-standard operating characteristics.

• MERGE tables are portable as long as their constituent MyISAM files are portable.

In essence, the portability requirements for MyISAM and InnoDB tables are that they either contain no floating-point columns or that both machines use the same floating-point storage format. "Floating-point" means FLOAT and DOUBLE here. DECIMAL columns are stored as strings, which are portable.

Note that for InnoDB, portability must be assessed at the tablespace level, not at the table level. The InnoDB handler stores the contents of all InnoDB tables within the tablespace rather than within table-specific files. Consequently, it's the InnoDB tablespace files that are or are not portable, not individual InnoDB tables. This means that the floating-point portability constraint applies if any InnoDB table uses floating-point columns.

Regardless of a table type's general portability characteristics, you should not attempt to copy table or tablespace files to another machine unless the server has been shut down cleanly. You cannot assume the integrity of your tables if you perform a copy after an unclean shutdown; they may be in need of repair or there may be transaction information still stored in a table handler's log files that needs to be applied or rolled back to bring tables up to date.

Similarly, if the server is running and actively updating tables, the table contents on disk will be in flux and the associated files will not yield usable table copies. In the case of a running server, you may be able to tell it to leave the tables alone while you copy them. For details, see Chapter 13, "Database Backups, Maintenance, and Repair."

Creating Tables

To create a table, use a CREATE TABLE statement. The full syntax for this statement is complex because there are so many optional clauses, but in practice, it's usually fairly simple to use. For example, all of the CREATE TABLE statements that we used in Chapter 1 are reasonably uncomplicated. If you start with the more basic forms and work up, you shouldn't have much trouble.

The CREATE TABLE specifies, at a minimum, the table name and a list of the columns in it—for example:

CREATE TABLE mytbl
(
  name  CHAR(20),
  age  INT NOT NULL,
  weight INT,
  sex  ENUM('F','M')
);

In addition to the columns that make up a table, you can specify how the table should be indexed when you create it. Another option is to leave the table unindexed when you create it and add the indexes later. (For MyISAM and ISAM tables, that's a good strategy if you plan to populate the table with a lot of data before you begin using it for queries. Updating indexes as you insert each row is much slower for those table types than loading the data into an unindexed table and creating the indexes afterward.)

We have already covered the basic syntax for the CREATE TABLE statement in Chapter 1 and discussed how to write column definitions in Chapter 2. I assume you've read those chapters and won't repeat that material here. Instead, the remainder of this section deals with some important extensions to the CREATE TABLE statement that were introduced beginning with MySQL 3.23 and that give you a lot of flexibility in how you construct tables:

• Table options that modify storage characteristics

• Creation of a table only if it doesn't already exist

• Temporary tables that are dropped automatically when the client session ends

• The capability of creating a table from the result of a SELECT query

• Using MERGE tables

Table Options

As of MySQL 3.23, you can add table options after the closing parenthesis in the CREATE TABLE statement to modify the table's storage characteristics. For example, prior to MySQL 3.23, any table created will be of type ISAM, because that is the only type available. From 3.23 on, you can add a TYPE = tbl_type option to specify the type explicitly. For example, to create a HEAP or InnoDB table, write the statement like this (the table type name is not case sensitive):

CREATE TABLE mytbl ( ... ) TYPE = HEAP;
CREATE TABLE mytbl ( ... ) TYPE = INNODB;

With no TYPE specifier, the server creates the table using its default type. This will be MyISAM unless you reconfigure the server to use a different default, either when you build the server or by giving a --default-table-type option at server startup time. If you specify a table type name that is syntactically legal but for which the handler is unavailable, MySQL creates the table using the default type. If you give an illegal table type, an error results.

Other table options can be given as well. Many of them apply only to particular table types. For example, a MIN_ROWS = n option can be used with HEAP tables to allow the HEAP handler to optimize memory usage:

CREATE TABLE mytbl ( ... ) TYPE = HEAP MIN_ROWS = 10000;

If the handler considers the value of MIN_ROWS to be large, it may allocate memory in larger hunks to avoid the overhead of making many allocation calls.

A complete list of table options is given in the entry for CREATE TABLE in Appendix D.

For an existing table, table options can be used with an ALTER TABLE statement to modify the table's current characteristics. For example, to change mytbl from its current table type to InnoDB, do this:

ALTER TABLE mytbl TYPE = INNODB;

The types allowed when you convert a table's type may depend on the feature compatibility of the old and new types. Suppose you have a MyISAM table that includes a BLOB column. You will not be able to convert the table to HEAP format because HEAP tables do not support BLOB columns.

Provisional Table Creation

To create a table only if it doesn't already exist, use CREATE TABLE IF NOT EXISTS. This feature is available as of MySQL 3.23.0. You can use it for an application that makes no assumptions about whether a table that it needs has been set up in advance. The application can go ahead and attempt to create the table as a matter of course. The IF NOT EXISTS modifier is particularly useful for scripts that you run as batch jobs with mysql. In this context, a regular CREATE TABLE statement doesn't work very well. The first time the job runs, it creates the table, but the second time an error occurs because the table already exists. If you use IF NOT EXISTS, there is no problem. The first time the job runs, it creates the table, as before. For the second and subsequent times, table creation attempts are silently ignored without error. This allows the job to continue processing as if the attempt had succeeded.

Temporary Tables

You can use CREATE TEMPORARY TABLE to create temporary tables that disappear automatically when your session ends. This is handy because you don't have to bother issuing a DROP TABLE statement explicitly to get rid of the table, and the table doesn't hang around if your session terminates abnormally. For example, if you have a canned query in a batch file that you run with mysql and decide not to wait for it to finish, you can kill the script in the middle with impunity and the server will remove any temporary tables that the script creates.

A temporary table is visible only to the client that creates the table. The name can be the same as that of an existing permanent table. This is not an error, nor does the existing permanent table get clobbered. Instead, the permanent table becomes hidden (inaccessible) while the temporary table exists. Suppose you create a temporary table in the sampdb database named member. The original member table becomes hidden, and references to member refer to the temporary table. If you issue a DROP TABLE member statement, the temporary table is removed and the original member table "reappears." If you simply disconnect from the server without dropping the temporary table, the server automatically drops it for you. The next time you connect, the original member table is visible again. (The original table also reappears if you rename a temporary table that hides it to have a different name. If the temporary table's new name happens to be that of another permanent table, that table becomes hidden while the temporary table has its name.)

The name-hiding mechanism works only to one level. That is, you cannot create two temporary tables with the same name.

A TEMPORARY table can be created with a particular storage format by using a TYPE option. (Prior to MySQL 3.23.54, a MERGE table cannot be TEMPORARY.)

Prior to MySQL 3.23.2, TEMPORARY is unavailable, so there are no true temporary tables except in the sense that you consider them temporary in your own mind. You must remember to drop such a table yourself. If you forget, the table hangs around until you notice and remove it. Table persistence also occurs if an application creates a table but exits early due to an error before it can drop the table.

Creating Tables from SELECT Query Results

One of the key concepts of relational databases is that everything is represented as a table of rows and columns, and the result of every SELECT is also a table of rows and columns. In many cases, the "table" that results from a SELECT is just an image of rows and columns that scroll off the top of your display as you continue working. But sometimes it is desirable to save a query result in another table so that you can refer to it later.

As of MySQL 3.23.0, you can do that easily. Use a CREATE TABLE ... SELECT statement to cause a new table to spring into existence on-the-fly to hold the result of an arbitrary SELECT query. You can do this in a single step without having to know or specify the data types of the columns you're retrieving. This makes it exceptionally easy to create a table fully populated with the data you're interested in, ready to be used in further queries. For example, the following statement creates a new table named student_f that consists of information for all female students in the student table:

CREATE TABLE student_f SELECT * FROM student WHERE sex = 'f';

To copy an entire table, omit the WHERE clause:

CREATE TABLE new_tbl_name SELECT * FROM tbl_name;

Or, to create an empty copy, use a WHERE clause that always evaluates to false:

CREATE TABLE new_tbl_name SELECT * FROM tbl_name WHERE 0;

Creating an empty copy of a table is useful if you want to load a data file into the original table using LOAD DATA, but you're not sure if you have the options for specifying the data format quite right. You don't want to end up with malformed records in the original table if you don't get the options right the first time! Using an empty copy of the original table allows you to experiment with the LOAD DATA options for specifying column and line delimiters until you're satisfied your input records are being interpreted properly. After you're satisfied, you can load the file into the original table. Do that either by rerunning the LOAD DATA statement with the original table name or by copying the data into it from the copy:

INSERT INTO orig_tbl SELECT * FROM copy_tbl;

You can combine CREATE TEMPORARY TABLE with SELECT to retrieve a table's contents into a temporary copy of itself:

CREATE TEMPORARY TABLE mytbl SELECT * FROM mytbl;

That allows you to modify the contents of mytbl without affecting the original, which can be useful when you want to try out some queries that modify the contents of the table, but you don't want to change the original table. To use pre-written scripts that use the original table name, you don't need to edit them to refer to a different table; just add the CREATE TEMPORARY TABLE statement to the beginning of the script. The script will create a temporary copy and operate on the copy, and the server will delete the copy when the script finishes. (One caution to observe here is that some clients, such as mysql, attempt to reconnect to the server automatically if the connection drops. Should this happen when you're working with the temporary table, it will be dropped and the queries executed subsequent to reconnecting will use the original table. Keep this in mind if you have an unreliable network.)

To create a table as an empty copy of itself, use a WHERE clause that is never true in conjunction with CREATE TEMPORARY TABLE ... SELECT:

CREATE TEMPORARY TABLE mytbl SELECT * FROM mytbl WHERE 0;

Creating a table on-the-fly from the results of a SELECT statement is a powerful capability, but there are several issues to consider when doing this.

With CREATE TABLE ... SELECT, you should use aliases as necessary to provide reasonable column names. When you create a table by selecting data into it, the column names are taken from the columns that you are selecting. If a column is calculated as the result of an expression, the "name" of the column is the text of the expression. Prior to MySQL 3.23.6, the following statement will fail outright, because expressions aren't legal as column names:

mysql> CREATE TABLE mytbl SELECT PI();
ERROR 1166: Incorrect column name 'PI()'

From 3.23.6 on, column naming rules are relaxed, so the statement will succeed but create a table with an unusual column name:

mysql> CREATE TABLE mytbl SELECT PI();
mysql> SELECT * FROM mytbl;
+----------+
| PI()     |
+----------+
| 3.141593 |
+----------+

That's unfortunate, because the column name can be referred to directly only by enclosing it within backticks:

mysql> SELECT ´PI()´ FROM mytbl;
+----------+
| PI()     |
+----------+
| 3.141593 |
+----------+

To provide a column name that is easier to work with when selecting an expression, use an alias:

mysql> CREATE TABLE mytbl SELECT PI() AS mycol;
mysql> SELECT mycol FROM mytbl;
+----------+
| mycol    |
+----------+
| 3.141593 |
+----------+

A related snag occurs if you select columns from different tables that have the same name. Suppose tables t1 and t2 both have a column c and you want to create a table from all combinations of rows in both tables. The following statement will fail because it attempts to create a table with two columns named c:

mysql> CREATE TABLE t3 SELECT * FROM t1, t2;
ERROR 1060: Duplicate column name 'c'

You can provide aliases to specify unique column names in the new table:

mysql> CREATE TABLE t3 SELECT t1.c AS c1, t2.c AS c2 FROM t1, t2;

Another thing to watch out for is that characteristics of the original table that are not reflected in the selected data will not be incorporated into the structure of the new table. For example, creating a table by selecting data into it does not automatically copy any indexes from the original table, because result sets are not themselves indexed. Similarly, column attributes such as AUTO_INCREMENT or the default value may not be carried into the new table. (Newer versions do better than older ones.) In some cases, you can force specific attributes to be used in the new table by invoking the CAST() function, which is available as of MySQL 4.0.2. The following CREATE TABLE ... SELECT statement forces the columns produced by the SELECT to be treated as INT UNSIGNED, DATE, and CHAR BINARY, which you can verify with DESCRIBE:

mysql> CREATE TABLE mytbl SELECT
  -> CAST(1 AS UNSIGNED) AS i,
  -> CAST(CURDATE() AS DATE) AS d,
  -> CAST('Hello, world' AS BINARY) AS c;
mysql> DESCRIBE mytbl;
+-------+-----------------+------+-----+------------+-------+
| Field | Type            | Null | Key | Default    | Extra |
+-------+-----------------+------+-----+------------+-------+
| i     | int(1) unsigned |      |     | 0          |       |
| d     | date            |      |     | 0000-00-00 |       |
| c     | char(12) binary |      |     |            |       |
+-------+-----------------+------+-----+------------+-------+

You can apply CAST() to column values retrieved from other tables as well. The allowable cast types are BINARY (binary string), DATE, DATETIME, TIME, SIGNED, SIGNED INTEGER, UNSIGNED, and UNSIGNED INTEGER.

As of MySQL 4.1, it's possible to provide even more information about the types that you want the columns in the new table to have by giving explicit definitions for them. Columns in the table are matched with the selected columns by name, so provide aliases for the selected columns if necessary to cause them to match up properly:

mysql> CREATE TABLE mytbl (i INT UNSIGNED, d DATE, c CHAR(20) BINARY)
  -> SELECT
  -> 1 AS i,
  -> CURDATE() AS d,
  -> 'Hello, world' AS c;
mysql> DESCRIBE mytbl;
+-------+------------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| i   | int(10) unsigned | YES |   | NULL  |    |
| d   | date       | YES |   | NULL  |    |
| c   | char(20) binary | YES |   | NULL  |    |
+-------+------------------+------+-----+---------+-------+

Note that this allows you to create character columns that have a different width than that of the longest value in the result set. Also note that the Null and Default attributes of the columns are different for this example than for the previous one. You could provide explicit declarations for those attributes as well if necessary.

Prior to MySQL 3.23, CREATE TABLE ... SELECT is unavailable. If you want to save the results of a SELECT in a table for use in further queries, you must make special arrangements in advance:

1. Run a DESCRIBE or SHOW COLUMNS query to determine the types of the columns in the tables from which you want to capture information.

2. Issue an explicit CREATE TABLE statement to create the table into which you want to save the SELECT results. The statement should specify the names and types of the columns that the SELECT will retrieve.

3. After creating the table, issue an INSERT INTO ... SELECT query to retrieve the results and insert them into the table.

Clearly, compared to CREATE TABLE ... SELECT, this involves a lot of ugly messing around.

Using MERGE Tables

The MERGE table type, available in MySQL 3.23.25 and up, provides a way to perform queries on a set of tables simultaneously by treating them all as a single logical unit. As described earlier in the "Table Types" section, MERGE can be applied to a collection of MyISAM tables that all have identical structure. Suppose you have a set of individual log tables that contain log entries on a year-by-year basis and that each are defined like this, where CCYY represents the century and year:

CREATE TABLE log_CCYY
(
  dt  DATETIME NOT NULL,
  info VARCHAR(100) NOT NULL,
  INDEX (dt)
) TYPE = MYISAM;

If the current set of log tables includes log_1999, log_2000, log_2001, log_2002, and log_2003, you can set up a MERGE table that maps onto them like this:

CREATE TABLE log_all
(
  dt  DATETIME NOT NULL,
  info VARCHAR(100) NOT NULL,
  INDEX (dt)
) TYPE = MERGE UNION = (log_1999, log_2000, log_2001, log_2002, log_2003);

The TYPE option must be MERGE, and the UNION option lists the tables to be included in the MERGE table. After the table has been set up, you query it just like any other table, but the queries will refer to all the constituent tables at once. The following query determines the total number of rows in all the log tables:

SELECT COUNT(*) FROM log_all;

This query determines how many log entries there are per year:

SELECT YEAR(dt) AS y, COUNT(*) AS entries FROM log_all GROUP BY y;

Besides the convenience of being able to refer to multiple tables without issuing multiple queries, MERGE tables offer some other nice features:

• A MERGE table can be used to create a logical entity that exceeds the allowable size of individual MyISAM tables.

• You can include compressed tables in the collection. For example, after a given year comes to an end, you wouldn't be adding any more entries to the corresponding log file, so you could compress it with myisampack to save space. The MERGE table will continue to function as before.

• Operations on MERGE tables are similar to UNION operations. UNION is unavailable prior to MySQL 4, but MERGE tables can be used in some cases as a workaround.

MERGE tables also support DELETE and UPDATE operations. INSERT is trickier, because MySQL needs to know which table to insert new records into. As of MySQL 4.0.0, MERGE table definitions can include an INSERT_METHOD option with a value of NO, FIRST, or LAST to indicate that INSERT is forbidden or that records should be inserted into the first or last table named in the UNION option. For example, the following definition would cause an INSERT into log_all to be treated as an INSERT into log_2003, the last table named in the UNION option:

CREATE TABLE log_all
(
  dt  DATETIME NOT NULL,
  info VARCHAR(100) NOT NULL,
  INDEX (dt)
) TYPE = MERGE UNION = (log_1999, log_2000, log_2001, log_2002, log_2003)
INSERT_METHOD = LAST;

Dropping Tables

Dropping a table is much easier than creating it because you don't have to specify anything about its contents. You just have to name it:

DROP TABLE tbl_name;

MySQL extends the DROP TABLE statement in some useful ways. First, you can drop several tables by specifying them all on the same statement:

DROP TABLE tbl_name1, tbl_name2, ... ;

Second, if you're not sure whether or not a table exists, but you want to drop it if it does, you can add IF EXISTS to the statement. This causes MySQL not to complain or issue an error if the table or tables named in the statement don't exist:

DROP TABLE IF EXISTS tbl_name;

IF EXISTS is particularly useful in scripts that you use with the mysql client. By default, mysql exits when an error occurs, and it is an error to try to remove a table that doesn't exist. For example, you might have a setup script that creates tables that you use as the basis for further processing in other scripts. In this situation, you want to make sure the setup script has a clean slate when it begins. If you use a regular DROP TABLE at the beginning of the script, it would fail the first time because the tables have never been created. If you use IF EXISTS, there is no problem. If the tables are there, they are dropped; if not, the script continues anyway.

Indexing Tables

Indexes are the primary means of speeding up access to the contents of your tables, particularly for queries that involve joins on multiple tables. This is an important enough topic that Chapter 4, "Query Optimization," discusses why you use indexes, how they work, and how best to take advantage of them to optimize your queries. This section covers the characteristics of indexes for the various table types and the syntax you use for creating and dropping them.

Table Type Indexing Characteristics

MySQL provides quite a bit of flexibility in the way you can construct indexes:

• You can index single columns or construct composite indexes from combinations of columns.

• An index can be allowed to contain duplicate values or required to contain only unique values.

• You can have more than one index on a table if you want to be able to look up a values quickly from different columns of a table.

• For string column types other than ENUM or SET, you may elect to index a prefix of a column, that is, only the leftmost n bytes. (In fact, for BLOB and TEXT columns, you cannot set up an index unless you do specify a prefix length.) Prefixes can be up to 255 bytes. If the column is mostly unique within the first n bytes, you usually won't sacrifice performance, and may well improve it. Indexing a column prefix rather than the entire column can make an index much smaller and faster to access.

Not all table types offer all indexing features. The following table summarizes the indexing properties of the various table types. (The table does not include the MERGE type because MERGE tables are created from MyISAM tables and have similar indexing characteristics.)

Index Characteristic	ISAM	MyISAM	HEAP	BDB	InnoDB
NULL values allowed	No	Yes	As of 4.0.2	Yes	Yes
Columns per index	16	16	16	16	16
Indexes per table	16	32	32	31	32
Maximum index row size (bytes)	256	500	500	500/1024	500/1024
Index column prefixes allowed	Yes	Yes	Yes	Yes	No
BLOB/TEXT indexes allowed	No	Yes (255 bytes max)	No	Yes (255 bytes max)	No

Two numbers are shown for the BDB and InnoDB index row sizes. For these table types, the size is 500 bytes up through 4.0.3 and 1024 bytes thereafter.

The table illustrates some of the reasons why MyISAM storage format generally is to be preferred to the ISAM format that it succeeds. MyISAM relaxes several of the indexing constraints that apply to ISAM tables. For example, with MyISAM tables, you can index columns that contain NULL values, you can index BLOB and TEXT columns, and you can have a larger number of indexes per table.

One implication of the differences in indexing characteristics for the various table types is that, depending on your version of MySQL, you may simply not be able to index certain columns. For example, you can use only ISAM tables if your MySQL is older than 3.23, which means you can't index a column if you want it to be able to contain NULL values. Conversely, if you require an index to have certain properties, you may not be able to use certain types of tables. If you need to index a BLOB column, for example, you must use a MyISAM or BDB table.

If you have an existing table of one type but would like to convert it to another type that has more suitable indexing characteristics, use ALTER TABLE to change the type. Suppose you have MySQL 3.23 or later but have older tables that were originally created as ISAM tables. You can easily convert them to MyISAM storage format using ALTER TABLE, which allows you to take advantage of MyISAM's superior indexing features:

ALTER TABLE tbl_name TYPE = MYISAM;

Creating Indexes

MySQL can create several types of index:

• A regular (non-unique) index. This gives you indexing benefits but allows duplicates.

• A unique index. This disallows duplicate values. For a single-column index, this ensures that the column contains no duplicate values. For a multiple-column (composite) index, it ensures that no combination of values in the columns is duplicated among the rows of the table.

• A FULLTEXT index, used when you want to perform full text searches. This index type is supported only for MyISAM tables. (For more information, see the "Using FULLTEXT Searches" section later in this chapter.)

You can create indexes for a new table when you use CREATE TABLE, or add indexes to existing tables with CREATE INDEX or ALTER TABLE. CREATE INDEX was introduced in MySQL 3.22, but you can use ALTER TABLE if your version of MySQL is older than that. (MySQL maps CREATE INDEX statements onto ALTER TABLE operations internally.)

ALTER TABLE is the more versatile than CREATE INDEX. You can use it to create a regular index, a UNIQUE index, a PRIMARY KEY, or a FULLTEXT index :

ALTER TABLE tbl_name ADD INDEX index_name (index_columns);
ALTER TABLE tbl_name ADD UNIQUE index_name (index_columns);
ALTER TABLE tbl_name ADD PRIMARY KEY (index_columns);
ALTER TABLE tbl_name ADD FULLTEXT (index_columns);

tbl_name is the name of the table to add the index to, and index_columns indicates which column or columns should be indexed. If the index consists of more than one column, separate the names by commas. The index name index_name is optional, so you can leave it out and MySQL will pick a name based on the name of the first indexed column. ALTER TABLE allows you to specify multiple table alterations in a single statement, so you can create several indexes at the same time. (This is faster than adding them one at a time with individual statements.)

To require that an index contain only unique values, create the index as a PRIMARY KEY or a UNIQUE index. The two types of index are very similar. In fact, a PRIMARY KEY is just a UNIQUE index that has the name PRIMARY. Two differences between the types of index are:

• A table can contain only one PRIMARY KEY because you can't have two indexes with the name PRIMARY. You can place multiple UNIQUE indexes on a table, although it's somewhat unusual to do so.

• A PRIMARY KEY cannot contain NULL values, whereas a UNIQUE index can. If a UNIQUE can contain NULL values, it usually can contain multiple NULL values. The reason for this is that it is not possible to know whether one NULL represents the same value as another, so they cannot be considered equal. (BDB tables are an exception—a BDB table allows only one NULL value within a UNIQUE index.)

CREATE INDEX can add a regular, UNIQUE, or FULLTEXT index to a table, but not a PRIMARY KEY:

CREATE INDEX index_name ON tbl_name (index_columns);
CREATE UNIQUE INDEX index_name ON tbl_name (index_columns);
CREATE FULLTEXT INDEX index_name ON tbl_name (index_columns);

tbl_name, index_name, and index_columns have the same meaning as for ALTER TABLE. Unlike ALTER TABLE, the index name is not optional with CREATE INDEX, and you cannot create multiple indexes with a single statement.

To create indexes for a new table when you issue a CREATE TABLE statement, the syntax is similar to that used for ALTER TABLE, but you specify the index-creation clauses as part of the column specification list:

CREATE TABLE tbl_name
(
  ... column declarations ...
  INDEX index_name (index_columns),
  UNIQUE index_name (index_columns),
  PRIMARY KEY (index_columns),
  FULLTEXT index_name (index_columns),

...

);

As with ALTER TABLE, the index name is optional in CREATE TABLE statements for each INDEX, UNIQUE, and FULLTEXT clause; MySQL will pick an index name if you leave it out.

As a special case, you can create a single-column PRIMARY KEY by adding PRIMARY KEY to the end of a column declaration. As of MySQL 3.23, you can do the same for a UNIQUE index. For example, this statement:

CREATE TABLE mytbl
(
  i INT NOT NULL PRIMARY KEY,
  j CHAR(10) NOT NULL UNIQUE
);

is equivalent to the following one:

CREATE TABLE mytbl
(
  i INT NOT NULL,
  j CHAR(10) NOT NULL,
  PRIMARY KEY (i),
  UNIQUE (j)
);

Each of the preceding table-creation examples have specified NOT NULL for the indexed columns. For ISAM tables (and for HEAP tables prior to MySQL 4.0.2), that's a requirement because you cannot index columns that may contain NULL values. For other table types, indexed columns can be NULL as long as the index is not a PRIMARY KEY.

To index a prefix of a string column (the leftmost n bytes of column values), the syntax for naming the column in the index definition is col_name(n) rather than simply col_name. For example, the following statement creates a table with two CHAR columns but uses only the first 10 bytes from each in the index created from those columns.

CREATE TABLE mytbl
(
  name  CHAR(30) NOT NULL,
  address CHAR(60) NOT NULL,
  INDEX (name(10),address(10))
);

Index prefixes are supported for ISAM, MyISAM, HEAP, and BDB tables, but not for InnoDB tables.

Prefix lengths, just like column lengths, refer to bytes rather than characters. The two are the same for single-byte character sets, but not for multi-byte character sets. MySQL will store into an index value as many complete characters as will fit. For example, if an index prefix is 5 bytes long and a column value consists of 2-byte characters, the index value will contain 2 characters, not 2.5 characters.

In some circumstances, you may find it not only desirable but necessary to index a column prefix rather than the entire column:

• Prefixes are necessary for BLOB or TEXT columns in any table type that allows those column types to be indexed. The prefix may be up to 255 bytes long.

• The length of index rows is equal to the sum of the length of the index parts of the columns that make up the index. If this length exceeds the allowable length of index rows, you can make the index "narrower" by indexing a column prefix. Suppose a MyISAM table contains two CHAR(255) columns named c1 and c2, and you want to create an index based on both of them. The length of an index row in this case would be 255+255, which exceeds the MyISAM limit of 500 bytes per index row. However, you can create the index by indexing a shorter part of one or both columns.

Indexing a prefix of a column constrains that changes that you can make to the column later. You cannot shorten the column to a length less than the prefix length without dropping the index and re-creating it using a shorter length for the indexed part of a column. If you index the first 30 bytes of a 40-byte CHAR column but then discover that you never store more than 20 bytes in the column, you might decide to save space in the table by changing the column to be only 20 bytes wide. In this case, you must drop the index first before making the column narrower. Then you can add the index again, indexing 20 or fewer bytes.

Columns in FULLTEXT indexes do not have prefixes. If you specify a prefix length for a FULLTEXT index, it will be ignored.

Dropping Indexes

To drop an index, use either a DROP INDEX or an ALTER TABLE statement. Like the CREATE INDEX statement, DROP INDEX was introduced in MySQL 3.22, is handled internally as an ALTER TABLE statement and cannot be used to affect a PRIMARY KEY. The syntax for index-dropping statements looks like this:

DROP INDEX index_name ON tbl_name;
ALTER TABLE tbl_name DROP INDEX index_name;
ALTER TABLE tbl_name DROP PRIMARY KEY;

The first two statements are equivalent. The third is used only for dropping a PRIMARY index; it is unambiguous because a table can have only one such key. If no index was created explicitly as a PRIMARY KEY but the table has one or more UNIQUE indexes, MySQL drops the first of them.

Indexes can be affected if you drop columns from a table. If you drop a column that is a part of an index, the column is removed from the index as well. If all columns that make up an index are dropped, the entire index is dropped.

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 (for changing table types and for creating and dropping indexes in this chapter, and for renumbering sequences in Chapter 2). 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. The full syntax for ALTER TABLE is described in Appendix D.

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. These are discussed earlier in the "Table Types" and "Indexing Tables" sections earlier in this chapter. 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. In addition, with MyISAM tables, you can index BLOB and TEXT columns, and indexed columns can contain NULL values.

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.