Using SQL to Manage Data

This chapter is from the book 

2.6. Creating, Dropping, Indexing, and Altering Tables

MySQL enables you to create tables, drop (remove) them, and change their structure with the CREATE TABLE, DROP TABLE, and ALTER TABLE statements. The CREATE INDEX and DROP INDEX statements enable you to add or remove indexes on existing tables. The following sections provide the details for these statements, but first it’s necessary to discuss the storage engines that MySQL supports for managing different types of tables.

2.6.1. Storage Engine Characteristics

MySQL supports multiple storage engines (or “table handlers” as they used to be known). Each storage engine implements tables that have a specific set of properties or characteristics. Table 2.1 briefly describes these storage engines, and later discussion provides more detail about some of them (primarily InnoDB and MyISAM). Others are either less commonly used or, in the case of NDB, require extensive discussion beyond what can be given here. Consequently, the remainder of this book says little about them.

Table 2.1. MySQL Storage Engines

Storage Engine	Description
ARCHIVE	Archival storage (no modification of rows after insertion)
BLACKHOLE	Engine that discards writes and returns empty reads
CSV	Storage in comma-separated values format
FEDERATED	Engine for accessing remote tables
InnoDB	Transactional engine with foreign keys
MEMORY	In-memory tables
MERGE	Manages collections of MyISAM tables
MyISAM	The main nontransactional storage engine
NDB	The engine for MySQL Cluster

Some of the engine names have synonyms. MRG_MyISAM and NDBCLUSTER are synonyms for MERGE and NDB, respectively. The MEMORY and InnoDB storage engines originally were known as HEAP and Innobase, respectively. The latter names are still recognized but deprecated.

Originally, the MySQL server was built such that all storage engines to be made available were compiled in. Now the server uses a “pluggable” architecture that enables plugins to be loaded selectively, and many storage engines are built as plugins. This permits the DBA to treat those engines as optional and load only those needed. The plugin interface also permits storage engines from third-party developers to be integrated into the server. For information about this interface, see Section 12.4, “The Plugin Interface.”

2.6.1.1. Checking Which Storage Engines Are Available

The engines actually available for a given server depend on your version of MySQL, how the server was configured at build time, and the startup options you use. For information about selecting storage engines, see Section 12.5, “Storage Engine Configuration.”

To see which storage engines the server knows about, use the SHOW ENGINES statement:

mysql> SHOW ENGINES\G
*************************** 1. row ***************************
      Engine: InnoDB
     Support: DEFAULT
     Comment: Supports transactions, row-level locking, and foreign keys
Transactions: YES
          XA: YES
  Savepoints: YES
...
*************************** 8. row ***************************
      Engine: MyISAM
     Support: YES
     Comment: MyISAM storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
...

The Support column value is YES or NO to indicate that the engine is or is not available, DISABLED if the engine is present but turned off, or DEFAULT for the storage engine that the server uses by default. The engine designated as DEFAULT should be considered available. The Transactions column indicates whether an engine supports transactions. XA and Savepoints indicate whether an engine supports distributed transactions (not covered in this book) and partial transaction rollback.

The ENGINES table in the INFORMATION_SCHEMA database provides the same information as SHOW ENGINES, but since you access it with SELECT, you can apply query conditions to select only the information in which you’re interested. For example, this query uses the ENGINES table to check for available engines that support transactions:

mysql> SELECT ENGINE FROM INFORMATION_SCHEMA.ENGINES
    -> WHERE TRANSACTIONS = 'YES';
+--------+
| ENGINE |
+--------+
| InnoDB |
+--------+

2.6.1.2. Table Representation on Disk

Each time you create a table, MySQL creates a disk file that contains the table’s format (that is, its definition). The format file has a basename that is the same as the table name and an .frm extension. For a table named t, the format file is named t.frm. The server creates the file in the database directory for the database that the table belongs to. The .frm file is an invariant because there is one for every table, no matter which storage engine manages the table. The name of a table as used in SQL statements might differ from the table-name part of the associated .frm file if the name contains characters that are problematic in filenames. See Section 11.2.6, “Operating System Constraints on Database Object Names,” for a description of the rules for mapping from SQL names to filenames.

Individual storage engines may also create other files that are unique to the table, to be used for storing the table’s content. For a given table, any files specific to it are located in the database directory for the database that contains the table. Table 2.2 shows the filename extensions for table-specific files created by certain storage engines.

Table 2.2. Table Files Created by Storage Engines

Storage Engine	Files on Disk
InnoDB	.ibd (data and indexes)
MyISAM	.MYD (data), .MYI (indexes)
CSV	.CSV (data), .CSM (metadata)

For some storage engines, the format file is the only file specifically associated with a particular table. Other engines may store table content elsewhere than on disk, or may use one or more tablespaces (storage areas shared by multiple tables):

• The MEMORY storage engine stores table contents in memory, not on disk.
• By default, InnoDB stores table data and indexes in its system tablespace. That is, all InnoDB table contents are managed within a shared storage area, not within files specific to a particular table. Alternatively, InnoDB creates .ibd files if you configure it to use individual per-table tablespaces.

The following sections characterize the features and behavior of selected MySQL storage engines. For additional information about how engines represent tables physically, see Section 11.2.3, “Representation of Tables in the Filesystem.”

2.6.1.3. The InnoDB Storage Engine

The InnoDB storage engine is the default engine in MySQL, unless you have configured your server otherwise. The following list describes some of its features:

• Transaction-safe tables with commit and rollback. Savepoints can be created to enable partial rollback.
• Automatic recovery after a crash.
• Foreign key and referential integrity support, including cascaded delete and update.
• Row-level locking and multi-versioning for good concurrency performance under query mix conditions that include both retrievals and updates.
• As of MySQL 5.6, InnoDB supports full-text searches and FULLTEXT indexes.

By default, InnoDB manages tables within a single system tablespace, rather than by using table-specific files like most other storage engines. The tablespace consists of one or more files and can include raw partitions. The InnoDB storage engine, in effect, treats the tablespace as a virtual filesystem within which it manages the contents of all InnoDB tables. Tables thus can exceed the size permitted by the filesystem for individual files. You can also configure InnoDB to use a separate tablespace file for each table. In this case, each table has an .ibd file in its database directory.

To configure individual tablespaces, enable the innodb_file_per_table system variable, either at server startup or at runtime. Enabling this variable also enables other InnoDB features, such as fast table truncation and row storage formats that offer more efficient table processing for some kinds of data. For more information, see Section 12.5.3.1.4, “Using Individual (Per-Table) InnoDB Tablespaces.”

2.6.1.4. The MyISAM Storage Engine

The MyISAM storage engine offers these features:

• Key compression when storing runs of successive similar string index values. 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 MyISAM table.
• More features for AUTO_INCREMENT columns than provided by other storage engines. For more information, see Section 3.4, “Working with Sequences.”
• Each MyISAM table has a flag that is set when a table-check operation is performed. MyISAM tables also have a flag indicating whether a table was closed properly when last used. If the server shuts down abnormally or the machine crashes, the flags can be used to detect tables that need to be checked. To do this automatically, start the server with the myisam_recover_options system variable set to a value that includes the FORCE option. This causes the server to check the table flags whenever it opens a MyISAM table and perform a table repair if necessary. See Section 14.3.1, “Using the Server’s Auto-Recovery Capabilities.”
• Full-text searches and FULLTEXT indexes.
• Spatial data types and SPATIAL indexes.

2.6.1.5. The MEMORY Storage Engine

The MEMORY storage engine uses tables that are stored in memory and that have fixed-length rows, two properties that make them very fast.

MEMORY tables are temporary in the sense that their contents disappear when the server terminates. That is, a MEMORY table still exists when the server restarts, but will be empty. However, in contrast to temporary tables created with CREATE TEMPORARY TABLE, MEMORY tables are visible to other clients.

MEMORY tables have characteristics that enable them to be handled more simply, and thus more quickly:

• By default, MEMORY tables use hashed indexes, which are very fast for equality comparisons but slow for range comparisons. Consequently, hashed indexes are used only for comparisons performed with the = and <=> equality operators, but not for comparison operators such as < or >. Hashed indexes also are not used in ORDER BY clauses for this reason.
• Rows are stored in MEMORY tables using fixed-length format for easier processing. A consequence is that you cannot use the BLOB and TEXT variable-length data types. VARCHAR is a variable-length type, but is permitted because it is treated internally as CHAR, a fixed-length type.

To use a MEMORY table for comparisons that look for a range of values using operators such as <, >, or BETWEEN, you can use BTREE indexes instead of hashed indexes. See Section 2.6.4.2, “Creating Indexes,” and Section 5.1.3, “Choosing Indexes.”

2.6.1.6. The NDB Storage Engine

NDB is MySQL’s cluster storage engine. For this storage engine, the MySQL server actually acts as a client to a cluster of other processes that provide access to the NDB tables. Cluster node processes communicate with each other to manage tables in memory. The tables are replicated among cluster processes for redundancy. Memory storage provides high performance, and the cluster provides high availability because it survives failure of any given node.

NDB configuration and use is beyond the scope of this book and is not covered further here. See the MySQL Reference Manual for details.

2.6.1.7. Other Storage Engines

MySQL has several other storage engines that I group here under the “miscellaneous” category:

• The ARCHIVE engine provides archival storage. It’s intended for storage of large numbers of rows that are written once and never modified thereafter. For this reason, it supports only a limited number of statements. INSERT and SELECT work, but REPLACE always acts like INSERT, and you cannot use DELETE or UPDATE. Rows are compressed during storage and decompressed during retrieval to save space. An ARCHIVE table can include an indexed AUTO_INCREMENT column; other columns cannot be indexed.
• The BLACKHOLE engine creates tables for which writes are ignored and reads return nothing. It is the database equivalent of the Unix /dev/null device.
• The CSV engine stores data in comma-separated values format. For each table, it creates a .CSV file in the database directory. This is a plain text file in which each table row appears as a single line. The CSV engine does not support indexing.
• The FEDERATED engine provides access to tables that are managed by other MySQL servers. In other words, the contents of a FEDERATED table really are located remotely. For a FEDERATED table, you specify the host where the other server is running and provide the username and password of an account on that server. When you access the FEDERATED table, the local server connects to the remote server using this account.
• The MERGE engine provides a means of grouping a set of MyISAM tables into a single logical unit. Querying a MERGE table in effect queries all the constituent tables. One advantage of this is that you can exceed the maximum table size permitted by the filesystem for individual MyISAM tables. Partitioned tables provide an alternative to MERGE tables and are not limited to MyISAM tables. See Section 2.6.2.5, “Using Partitioned Tables.”

2.6.2. Creating Tables

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

A CREATE TABLE statement specifies, at a minimum, the table name and a list of the columns in it. For example:

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

In addition to the column definitions, 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 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 than loading the data into an unindexed MyISAM table and creating the indexes afterward.

We have already covered the basic syntax for CREATE TABLE in Chapter 1, “Getting Started with MySQL.” Details on how to write column definitions are given in Chapter 3, “Data Types.” Here, we deal more generally with some important extensions to CREATE TABLE that give you a lot of flexibility in how you construct tables:

• Table options that modify storage characteristics
• Creating a table only if it doesn’t already exist
• Temporary tables that the server drops automatically when the client session ends
• Creating a table from another table or from the result of a SELECT query
• Using partitioned tables

2.6.2.1. Table Options

To modify a table’s storage characteristics, add one or more table options following the closing parenthesis in the CREATE TABLE statement. For a complete list of options, see the description for CREATE TABLE in Appendix E, “SQL Syntax Reference.”

One table option is ENGINE = engine_name, which specifies the storage engine to use for the table. For example, to create a MEMORY or MyISAM table, write the statement like this:

CREATE TABLE mytbl ( ... ) ENGINE=MEMORY;
CREATE TABLE mytbl ( ... ) ENGINE=MyISAM;

The engine name is not case sensitive. With no ENGINE option, the server creates the table using the default storage engine. The built-in default is InnoDB, but you can tell the server to use a different default using the instructions in Section 12.5.2, “Selecting a Default Storage Engine.”

If you name a storage engine that is not enabled, two warnings occur:

mysql> CREATE TABLE t (i INT) ENGINE=ARCHIVE;
Query OK, 0 rows affected, 2 warnings (0.01 sec)
mysql> SHOW WARNINGS;
+---------+------+-------------------------------------------+
| Level   | Code | Message                                   |
+---------+------+-------------------------------------------+
| Warning | 1286 | Unknown storage engine 'ARCHIVE'          |
| Warning | 1266 | Using storage engine InnoDB for table 't' |
+---------+------+-------------------------------------------+

To make sure that a table uses a particular storage engine, be sure to include the ENGINE table option. Because the default engine can be changed, you might not get the default you expect if you omit ENGINE. In addition, verify that the CREATE TABLE statement produces no warnings, which often indicate that the specified engine was not available and that the default engine was used instead.

To tell MySQL to issue an error if the engine you specify is not available, (instead of substituting the default storage engine), enable the NO_ENGINE_SUBSTITUTION SQL mode.

To determine which storage engine a table uses, issue a SHOW CREATE TABLE statement and look for the ENGINE option in the output:

mysql> SHOW CREATE TABLE t\G
*************************** 1. row ***************************
       Table: t
Create Table: CREATE TABLE `t` (
  `i` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1

The storage engine is also available in the output from the SHOW TABLE STATUS statement or the INFORMATION_SCHEMA.TABLES table.

The MAX_ROWS and AVG_ROW_LENGTH options can help you size a MyISAM table. By default, MyISAM creates tables with an internal row pointer size that permits table files to grow up to 256TB. If you specify the MAX_ROWS and AVG_ROW_LENGTH options, that gives MyISAM information that it should use a pointer size for a table that can hold at least MAX_ROWS rows.

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

ALTER TABLE mytbl ENGINE=InnoDB;

For more information about changing storage engines, see Section 2.6.5, “Altering Table Structure.”

2.6.2.2. Provisional Table Creation

To create a table only if it doesn’t already exist, use CREATE TABLE IF NOT EXISTS. You can use this statement 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 second and subsequent times, table creation attempts are silently ignored without error. This enables the job to continue processing as if the attempt had succeeded.

If you use IF NOT EXISTS, be aware that MySQL does not compare the table structure in the CREATE TABLE statement with the existing table. If a table exists with the given name but has a different structure, the statement does not fail. If that is a risk you wish not to take, it is better instead to precede your CREATE TABLE statement by DROP TABLE IF EXISTS.

2.6.2.3. TEMPORARY Tables

Adding the TEMPORARY keyword to a table-creation statement causes the server to create a temporary table that disappears automatically when your session with the server terminates:

CREATE TEMPORARY TABLE tbl_name ... ;

This is handy because you need not issue a DROP TABLE statement to get rid of the table, and the table doesn’t persist if your session terminates abnormally. For example, if you have a complex query stored in a batch file that you run with mysql and you decide not to wait for it to finish, you can kill the script with impunity and the server will remove any TEMPORARY tables created by the script.

To create a temporary table using a particular storage engine, add an ENGINE table option to the CREATE TEMPORARY TABLE statement.

Although the server drops a TEMPORARY table automatically when your client session ends, you can drop it explicitly as soon as you’re done with it to enable the server to free any resources associated with it. This is a good idea if your session with the server will not end for a while, particularly for temporary MEMORY tables.

A TEMPORARY table is visible only to the client that creates the table. Different clients can each create a TEMPORARY table with the same name and without conflict because each client sees only the table that it created.

The name of a TEMPORARY table can be the same as an existing permanent table. This is not an error, nor does the existing permanent table get clobbered. Instead, the permanent table becomes hidden (inaccessible) to the client that creates the TEMPORARY table while the TEMPORARY table exists. Suppose that you create a TEMPORARY table named member in the sampdb database. 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 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.)

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

Keep in mind the following caveats when considering whether to use a TEMPORARY table:

• If your client program automatically reconnects to the server if the connection is lost, any TEMPORARY tables will be gone when you reconnect. If you were using the TEMPORARY table to “hide” a permanent table with the same name, the permanent table now becomes the table that you use. For example, a DROP TABLE after an undetected reconnect will drop the permanent table. To avoid this problem, use DROP TEMPORARY TABLE instead.
• Because TEMPORARY tables are visible only within the session that created them, they are not useful with connection pooling mechanisms that do not guarantee the same connection for each statement that you issue.
• With connection pooling or persistent connections, your connection to the MySQL server will not necessarily close when your application terminates. Those mechanisms might hold the connection open for use by other clients, which means that you cannot assume that TEMPORARY tables will disappear automatically when your application terminates.

2.6.2.4. Creating Tables from Other Tables or Query Results

It’s sometimes useful to create a copy of a table. For example, you might have a data file that you want to load into a table using LOAD DATA, but you’re not quite sure about the options for specifying the data format. You can end up with malformed rows in the original table if you don’t get the options right the first time. Using an empty copy of the original table enables you to experiment with the LOAD DATA options for specifying column and line delimiters until you’re satisfied your input rows are being interpreted properly. Then you can load the file into the original table by rerunning the LOAD DATA statement with the original table name.

It’s also sometimes desirable to save the result of a query into a table rather than displaying it on your screen. By saving the result, you can refer to it later without rerunning the original query, perhaps to perform further analysis on it.

MySQL provides two statements for creating new tables from other tables or from query results. These statements have differing advantages and disadvantages:

• CREATE TABLE ... LIKE creates a new table as an empty copy of the original one. It copies the original table structure exactly, so that each column is preserved with all of its attributes. The index structure is copied as well. However, the new table is empty, so to populate it a second statement is needed (such as INSERT INTO ... SELECT). Also, CREATE TABLE ... LIKE cannot create a new table from a subset of the original table’s columns, and it cannot use columns from any other table but the original one.
• CREATE TABLE ... SELECT creates a new table from the result of an arbitrary SELECT statement. By default, this statement does not copy all column attributes such as AUTO_INCREMENT. Nor does creating a table by selecting data into it automatically copy any indexes from the original table, because result sets are not themselves indexed. On the other hand, CREATE TABLE ... SELECT can both create and populate the new table in a single statement. It also can create a new table using a subset of the original table and include columns from other tables or columns created as the result of expressions.

To use CREATE TABLE ... LIKE for creating an empty copy of an existing table, write a statement like this:

CREATE TABLE new_tbl_name LIKE tbl_name;

To create an empty copy of a table and then populate it from the original table, use CREATE TABLE ... LIKE followed by INSERT INTO ... SELECT:

CREATE TABLE new_tbl_name LIKE tbl_name;
INSERT INTO new_tbl_name SELECT * FROM tbl_name;

To create a table as a temporary copy of itself, include the TEMPORARY keyword:

CREATE TEMPORARY TABLE tbl_name LIKE tbl_name;
INSERT INTO tbl_name SELECT * FROM tbl_name;

Using a TEMPORARY table with the same name as the original can be useful when you want to try some statements that modify the contents of a table, without changing the original table. To use prewritten scripts that use the original table name, you need not edit them to refer to a different table. Just add the CREATE TEMPORARY TABLE and INSERT statements to the beginning of the script. The script will create a temporary copy and operate on the copy, which the server deletes when the script finishes. (However, bear in mind the auto-reconnect caveat noted in Section 2.6.2.3, “TEMPORARY Tables.”)

To insert into the new table only some of the rows from the original table, add a WHERE clause that identifies which rows to select. The following statements create a new table named student_f that contains only the rows for female students from the student table:

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

If you don’t care about retaining the exact column definitions from the original table, CREATE TABLE ... SELECT sometimes is easier to use than CREATE TABLE ... LIKE because it can create and populate the new table in a single statement:

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

CREATE TABLE ... SELECT also can create new tables that don’t contain exactly the same set of columns in an existing table. You can use it to cause a new table to spring into existence on the fly to hold the result of an arbitrary SELECT query. This makes it exceptionally easy to create a table fully populated with the data in which you’re interested, ready to be used in further statements. However, the new table can contain strange column names if you’re not careful. 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, which creates a table with an unusual column name:

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

That’s unfortunate, because the column name can be referred to directly only as a quoted identifier:

mysql> SELECT `PI() * 2` FROM mytbl;
+----------+
| PI() * 2 |
+----------+
| 6.283185 |
+----------+

To avoid this problem, use a column alias to provide a name that is easier to work with:

mysql> DROP TABLE mytbl;
mysql> CREATE TABLE mytbl SELECT PI() * 2 AS mycol;
mysql> SELECT mycol FROM mytbl;
+----------+
| mycol    |
+----------+
| 6.283185 |
+----------+

A related difficulty occurs if you select from different tables columns that have the same name. Suppose that 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 fails because it attempts to create a table with two columns named c:

mysql> CREATE TABLE t3 SELECT * FROM t1 INNER JOIN t2;
ERROR 1060 (42S21): Duplicate column name 'c'

To solve this problem, provide aliases as necessary to give each column a unique name in the new table:

mysql> CREATE TABLE t3 SELECT t1.c, t2.c AS c2
    -> FROM t1 INNER JOIN t2;

As mentioned previously, a shortcoming of CREATE TABLE ... SELECT is that it does not incorporate all characteristics of the original data into the structure of the new table. For example, creating a table by selecting data into it does not copy indexes from the original table, and it can lose column attributes. The retained attributes include whether the column is NULL or NOT NULL, the character set and collation, the default value, and the column comment.

In some cases, you can force specific attributes to be used in the new table by invoking the CAST() function in the SELECT part of the statement. The following CREATE TABLE ... SELECT statement forces the columns produced by the SELECT to be treated as INT UNSIGNED, TIME, and DECIMAL(10,5), as you can verify with DESCRIBE:

mysql> CREATE TABLE mytbl SELECT
    -> CAST(1 AS UNSIGNED) AS i,
    -> CAST(CURTIME() AS TIME) AS t,
    -> CAST(PI() AS DECIMAL(10,5)) AS d;
mysql> DESCRIBE mytbl;
+-------+-----------------+------+-----+---------+-------+
| Field | Type            | Null | Key | Default | Extra |
+-------+-----------------+------+-----+---------+-------+
| i     | int(1) unsigned | NO   |     | 0       |       |
| t     | time            | YES  |     | NULL    |       |
| d     | decimal(10,5)   | NO   |     | 0.00000 |       |
+-------+-----------------+------+-----+---------+-------+

The permitted cast types are BINARY (binary string), CHAR, DATE, DATETIME, TIME, SIGNED, SIGNED INTEGER, UNSIGNED, UNSIGNED INTEGER, and DECIMAL.

It is also possible to provide explicit column definitions in the CREATE TABLE part, to be used for the columns retrieved by the SELECT part. Columns in the two parts are matched by name (not position), so provide aliases in the SELECT part as necessary to cause them to match properly:

mysql> CREATE TABLE mytbl (i INT UNSIGNED, t TIME, d DECIMAL(10,5))
    -> SELECT
    -> 1 AS i,
    -> CAST(CURTIME() AS TIME) AS t,
    -> CAST(PI() AS DECIMAL(10,5)) AS d;

mysql> DESCRIBE mytbl;
+-------+------------------+------+-----+---------+-------+
| Field | Type             | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| i     | int(10) unsigned | YES  |     | NULL    |       |
| t     | time             | YES  |     | NULL    |       |
| d     | decimal(10,5)    | YES  |     | NULL    |       |
+-------+------------------+------+-----+---------+-------+

The technique of providing explicit definitions enables you to create numeric columns with specified precision and scale, character columns that have a different width than the longest value in the result set, and so forth. Also note that the Null and Default attributes for some of the columns differ in this example from those in the previous one. You can provide explicit definitions for those attributes in the CREATE TABLE part if necessary.

2.6.2.5. Using Partitioned Tables

MySQL supports table partitioning, which enables division of table contents into different physical storage locations. By sectioning table storage, partitioned tables offer benefits such as these:

• Table storage can be distributed over multiple devices, which may improve access time by virtue of I/O parallelism.
• The optimizer may be able to localize searches to specific partitions, or to search partitions in parallel.

To create a partitioned table, supply the list of columns and indexes in the CREATE TABLE statement, as usual. In addition, specify a PARTITION BY clause that defines a partitioning function to be used to assign rows to partitions, and possibly other partition-related options. A partitioning function assigns rows based on ranges or lists of values or hash values:

• Use range partitioning when rows contain a domain of values such as dates, income level, or weight that can be divided into discrete ranges.
• Use list partitioning when it makes sense to specify an explicit list of values for each partition, such as sets of postal codes, phone number prefixes, or IDs for entities that you group by geographical region.
• Use hash partitioning to distribute the rows among partitions according to hash values computed from row keys. You can either supply the hash function yourself or tell MySQL which columns to use and it computes values based on those columns using a built-in hash function.

The partitioning function must be deterministic so that the same input values consistently result in row assignment to the same partition. This rules out functions such as RAND() or NOW().

Suppose that you want to create a table for storing simple log entries consisting of a date and a descriptive string, and that you already have several years’ worth of entries to be loaded into the table. For data entries that each contain a date, range partitioning is most natural. To assign rows for each year to a given partition, use the year part of the date value:

CREATE TABLE log_partition
(
  dt    DATETIME NOT NULL,
  info  VARCHAR(100) NOT NULL,
  INDEX (dt)
)
PARTITION BY RANGE(YEAR(dt))
(
  PARTITION p0 VALUES LESS THAN (2010),
  PARTITION p1 VALUES LESS THAN (2011),
  PARTITION p2 VALUES LESS THAN (2012),
  PARTITION p3 VALUES LESS THAN (2013),
  PARTITION pmax VALUES LESS THAN MAXVALUE
);

The MAXVALUE partition is assigned all rows that have dates from the year 2014 or later. When the year 2014 arrives, you can split that partition so that all year 2014 rows get their own partition and rows for 2015 and later go into the MAXVALUE partition:

ALTER TABLE log_partition REORGANIZE PARTITION pmax
INTO (
  PARTITION p4 VALUES LESS THAN (2014),
  PARTITION pmax VALUES LESS THAN MAXVALUE
);

By default, MySQL stores partitions under the directory for the database to which the partitioned table belongs. To distribute storage to other locations (for example, to place them on different physical devices), use the DATA_DIRECTORY and INDEX_DIRECTORY partition options. For more information about the syntax for these and other partitioning options, see the description for CREATE TABLE in Appendix E, “SQL Syntax Reference.”

2.6.3. Dropping Tables

Dropping a table is much easier than creating it because you need not specify anything about the format of its contents. You just have to name it, assuming that you have the DROP privilege for it:

DROP TABLE tbl_name;

In MySQL, the DROP TABLE statement has several useful extensions. To drop multiple tables, specify them all in the same statement:

DROP TABLE tbl_name1, tbl_name2, ... ;

By default, an error occurs if you try to drop a table that does not exist. To suppress this error and generate a warning instead for nonexistent tables, include IF EXISTS in the statement:

DROP TABLE IF EXISTS tbl_name;

If the statement generates warnings, you can view them with SHOW WARNINGS.

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 used 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 fails the first time because the tables have never been created. Using IF EXISTS makes the problem go away. If the tables exist, they are dropped. If they do not exist, no error occurs and the script continues to execute.

To drop a table only if it is a temporary table, include the TEMPORARY keyword:

DROP TEMPORARY TABLE tbl_name;

2.6.4. 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 most of an entire chapter discusses why you use indexes, how they work, and how best to take advantage of them to optimize your queries (see Chapter 5, “Query Optimization”). This section covers the characteristics of indexes for the various table types and the syntax for creating and dropping indexes.

2.6.4.1. Storage Engine Index Characteristics

MySQL provides quite a bit of flexibility for index construction:

• You can index single columns or multiple columns. Multiple-column indexes are also known as composite indexes.
• An index can be constrained to contain only unique values or permitted to contain duplicate values.
• You can have more than one index on a table to help optimize different types of queries on the table.
• For string data types other than ENUM or SET, you can elect to index a prefix of a column; that is, only the leftmost n characters, or n bytes for binary string types. (For BLOB and TEXT columns, you can set up an index only if you specify a prefix length.) If the column is mostly unique within the prefix length, 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 storage engines offer all indexing features. Table 2.3 summarizes the index properties for some of MySQL’s storage engines. The table does not include the MERGE storage engine, because MERGE tables are created from MyISAM tables and have similar index characteristics. Nor does it include the ARCHIVE, BLACKHOLE, or CSV engines, which support indexing either not at all or only in limited fashion.

Table 2.3. Storage Engine Index Characteristics

Index Characteristic	InnoDB	MyISAM	MEMORY
NULL values permitted	Yes	Yes	Yes
Columns per index	16	16	16
Indexes per table	64	64	64
Maximum index row size (bytes)	3072	1000	3072
Index column prefixes	Yes	Yes	Yes
Maximum prefix size (bytes)	767	1000	3072
BLOB/TEXT indexes	Yes	Yes	No
FULLTEXT indexes	As of 5.6.4	Yes	No
SPATIAL indexes	No	Yes	No
HASH indexes	No	No	Yes

One implication of the variations in index characteristics for different storage engines is that if you require an index to have certain properties, you may not be able to use certain types of tables. For example, to use a HASH index, you must use a MEMORY table. To index a TEXT column, you must use InnoDB or MyISAM.

To convert an existing table to use a different storage engine that has more suitable index characteristics, use ALTER TABLE. Suppose that you have an InnoDB table in MySQL 5.5 but need to perform searches using a FULLTEXT index. In MySQL 5.5, this is supported only by MyISAM. Convert the table using this statement:

ALTER TABLE tbl_name ENGINE=MyISAM;

2.6.4.2. Creating Indexes

MySQL can create several types of indexes:

• A unique index. This prohibits duplicate values for a single-column index, and duplicate combinations of values for a multiple-column (composite) index.
• A regular (nonunique) index. This gives you indexing benefits but permits duplicates.
• A FULLTEXT index, used for performing full-text searches. This index type is supported only for MyISAM tables (or, as of MySQL 5.6.4, InnoDB). For more information, see Section 2.14, “Using FULLTEXT Searches.”
• A SPATIAL index. These can be used only with MyISAM tables containing spatial values, which are described briefly in Section 3.1.4, “Spatial Values.”
• A HASH index. This is the default index type for MEMORY tables, although you can override the default to create BTREE indexes instead.

You can include index definitions for a new table when you use CREATE TABLE. For examples, see Section 1.4.6, “Creating Tables.” To add indexes to existing tables, use ALTER TABLE or CREATE INDEX. (MySQL maps CREATE INDEX statements onto ALTER TABLE operations internally.)

ALTER TABLE is the more versatile than CREATE INDEX because it can create any kind of index supported by MySQL. For example:

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_name (index_columns);
ALTER TABLE tbl_name ADD SPATIAL index_name (index_columns);

tbl_name is the name of the table to which the index should be added, and index_columns names the column or columns to index, separated by commas. The index name index_name is optional. If you leave it out, MySQL picks a name based on the name of the first indexed column.

An indexed column must be NOT NULL if indexed using a PRIMARY KEY or SPATIAL index. Other indexes permit indexed columns to contain NULL values.

A single ALTER TABLE statement can include multiple table alterations if you separate them by commas. This enables you to create several indexes at the same time, which is faster than adding them one at a time with individual ALTER TABLE statements.

To constrain an index to contain only unique values, create the index as a PRIMARY KEY or as a UNIQUE index. The two types of index are very similar, but have two differences:

• A table can contain only one PRIMARY KEY. This is because the name of a PRIMARY KEY is always PRIMARY and a table cannot have two indexes with the same name. You can place multiple UNIQUE indexes on a table.
• A PRIMARY KEY cannot contain NULL values. A UNIQUE index can. If a UNIQUE index can contain NULL values, it can contain multiple NULL values. (A NULL is not considered equal to any other value, even another NULL.)

CREATE INDEX can add most types of indexes, with the exception of 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);
CREATE SPATIAL 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 with a CREATE TABLE statement, the syntax is similar to that used for ALTER TABLE, but you specify the index-creation clauses in addition to the column definitions:

CREATE TABLE tbl_name
(
  ... column definitions ...
  INDEX index_name (index_columns),
  UNIQUE index_name (index_columns),
  PRIMARY KEY (index_columns),
  FULLTEXT index_name (index_columns),
  SPATIAL index_name (index_columns),
  ...
);

As with ALTER TABLE, index_name is optional. MySQL picks an index name if you leave it out.

As a special case, you can create a single-column PRIMARY KEY or UNIQUE index by adding a PRIMARY KEY or UNIQUE clause to the end of a column definition. For example, the following CREATE TABLE statements are equivalent:

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

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

The default index type for a MEMORY table is HASH. A hashed index is very fast for exact-value lookups, which is the typical way MEMORY tables are used. However, if you plan to use a MEMORY table for comparisons that can match a range of values (for example, id < 100), hashed indexes do not work well. You’ll be better off creating a BTREE index instead, by adding a USING BTREE clause to the index definition:

CREATE TABLE namelist
(
  id   INT NOT NULL,
  name CHAR(100),
  INDEX (id) USING BTREE
) ENGINE=MEMORY;

To index a prefix of a string column, the syntax for naming the column in the index definition is col_name(n) rather than simply col_name. The prefix value, n, indicates that the index should include the first n bytes of column values for binary string types, or the first n characters for nonbinary string types. For example, the following statement creates a table with a CHAR column and a BINARY column. It indexes the first 10 characters of the CHAR column and the first 15 bytes of the BINARY column:

CREATE TABLE addresslist
(
  name    CHAR(30) NOT NULL,
  address BINARY(60) NOT NULL,
  INDEX (name(10)),
  INDEX (address(15))
);

When you index a prefix of a string column, the prefix length, just like the column length, is specified in the same units as the column data type—that is, bytes for binary strings and characters for nonbinary strings. However, the maximum size of index entries are measured internally in bytes. The two measures are the same for single-byte character sets, but not for multi-byte character sets. For nonbinary strings that have multi-byte character sets, MySQL stores into index values as many complete characters as fit within the maximum permitted byte length.

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

• A prefix is required to index a BLOB or TEXT column.
• 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 maximum permitted number of bytes in index rows, you can make the index “narrower” by indexing a column prefix. Suppose that a MyISAM table that uses the latin1 single-byte character set contains four CHAR(255) columns named c1 through c4. An index value for each full column value takes 255 bytes, so an index on all four columns would require 1,020 bytes. However, the maximum length of a MyISAM index row is 1,000 bytes, so you cannot create a composite index that includes the entire contents of all four columns. However, you can create the index by indexing a shorter part of some or all of them. For example, you could index the first 250 characters from each column.

Columns in FULLTEXT indexes are indexed in full and do not have prefixes. If you specify a prefix length for a column in a FULLTEXT index, MySQL ignores it.

2.6.4.3. Dropping Indexes

To drop an index, use either a DROP INDEX or an ALTER TABLE statement. To use DROP INDEX, you must name the index to be dropped:

DROP INDEX index_name ON tbl_name;

To drop a PRIMARY KEY with DROP INDEX, specify the name PRIMARY as a quoted identifier:

DROP INDEX `PRIMARY` ON tbl_name;

That statement is unambiguous because a table is permitted only one PRIMARY KEY and its name is always PRIMARY.

Like the CREATE INDEX statement, DROP INDEX is handled internally as an ALTER TABLE statement. The preceding DROP INDEX statements correspond to the following ALTER TABLE statements:

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

If you don’t know the names of a table’s indexes, use SHOW CREATE TABLE or SHOW INDEX to find out.

When you drop columns from a table, indexes may be affected implicitly. Dropping a column that is a part of an index removes the column from the index as well. If you drop all columns in an index, MySQL drops the entire index.

2.6.5. Altering Table Structure

ALTER TABLE is a versatile statement and has many uses. We’ve already seen a few of its capabilities earlier in this chapter (for changing storage engines and for creating and dropping indexes). ALTER TABLE can also rename tables, add or drop columns, change column data types, and more. This section covers some of its features. For its complete syntax, see Appendix E, “SQL Syntax Reference.”

ALTER TABLE is useful when you find that the structure of a table no longer reflects its intended use. Perhaps you want to record additional information, or the table contains information that has become superfluous. Maybe existing columns are too small, or it turns out that you’ve defined columns larger than you need and you’d like to make them smaller to save space and improve query performance. Here are some situations for which ALTER TABLE is valuable:

• You assign case numbers to records for a research project 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 data 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 might generate another 50,000 records. You need a bigger type 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 or convert it to VARCHAR(255) to save space.
• You want to convert a table to use a different storage engine to take advantage of features offered by that engine. For example, MyISAM tables are not transaction-safe, but you have an application that needs transactional capabilities. You can convert the affected tables to use InnoDB, which supports transactions. Or you might be using MyISAM in MySQL 5.5 because it supports FULLTEXT capabilities, but now you have upgraded to MySQL 5.6, which expands FULLTEXT support to InnoDB.

The syntax for ALTER TABLE looks like this:

ALTER TABLE tbl_name action [, action] ... ;

Each action specifies a modification to make to the table. Some database systems permit only a single action in an ALTER TABLE statement, but MySQL supports multiple actions, separated by commas.

The following examples discuss some of the capabilities of ALTER TABLE.

Changing a column’s data type. To change a data type, use either a CHANGE or MODIFY clause. Suppose that the column i in a table mytbl is SMALLINT UNSIGNED. To change it to MEDIUMINT UNSIGNED, use either of the following statements:

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

Why is the column named twice in the statement 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 k at the same time you changed the type, you’d do so like this:

ALTER TABLE mytbl CHANGE i k MEDIUMINT UNSIGNED;

Remember that with CHANGE, you name the column you want to change and then specify its new name and definition. To retain the same column name, you must specify the name twice.

To rename a column without changing its data type, use CHANGE old_name new_name followed by the column’s current definition.

To change a column’s character set, use the CHARACTER SET attribute in the column definition:

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

An important reason for changing data types is to improve query efficiency for joins that compare columns from two tables. Indexes often can be used for comparisons in joins between similar column types, but comparisons are quicker when both columns are exactly the same type. Suppose that you’re running a query like this:

SELECT ... FROM t1 INNER JOIN 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 these statements:

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

Converting a table to a different storage engine. To convert a table from one storage engine to another, use an ENGINE clause that specifies the new engine name:

ALTER TABLE tbl_name ENGINE=engine_name;

engine_name is a name such as InnoDB, MyISAM, or MEMORY. Lettercase does not matter.

One reason to change a storage engine is to make it transaction-safe. Suppose that 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 use InnoDB:

ALTER TABLE tbl_name ENGINE=InnoDB;

When you convert a table to a different engine, the permitted or sensible conversions may depend on the feature compatibility of the old and new engines. For example, if you have a table that includes a BLOB column, you cannot convert the table to use the MEMORY engine because MEMORY tables do not support BLOB columns.

There are circumstances under which you should not use ALTER TABLE to convert a table to use a different storage engine. For example:

• An InnoDB table can be converted to use another storage engine. However, if the table has foreign key constraints, they will be lost because only InnoDB supports foreign keys.
• MEMORY tables are held in memory and disappear when the server exits. If you require a table’s contents to persist across server restarts, do not convert it to use the MEMORY engine.

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. The syntax looks like this:

RENAME TABLE tbl_name TO new_tbl_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 t2;

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 a name that already exists.