This statement creates new user accounts on the MySQL server. The username is given within quotes, followed by the at sign (@) and a host IP address or hostname within quotes. For accessing MySQL locally, use the host of localhost. The IP address is 127.0.0.1. Use the percent sign (%) wildcard as the host to allow a client with the specified username to connect from any host. If no host or @ is given, the percent sign is assumed.

The user password is given in plain text within quotes, preceded by the IDENTIFIED BY clause. You don’t need to use the PASSWORD() function to encrypt the password; this is done automatically. However, if you wish to provide the hash value of the password, precede the password with IDENTIFIED BY PASSWORD. If the password clause is not given, a blank password is assumed and will be accepted. This is a potential security problem and should never be done. If you do this by mistake, use the SET PASSWORD statement to set the password.

Multiple user accounts may be specified in a comma-separated list.

The CREATE USER statement was introduced in version 5.0.2 of MySQL. For previous versions, use the GRANT statement. This new statement operates similarly to the GRANT statement, except that you cannot specify user privileges with the CREATE USER statement. As a result, the process is to create a user with the CREATE USER statement and then to grant the user privileges with the GRANT statement. This two-step process is a more logical process, especially to a newcomer to MySQL. However, you can still use just the GRANT statement to create and set privileges for a new user.

This statement requires CREATE USER privilege or INSERT privilege for the mysql database, which contains user account information and privileges. To remove a user, use the DROP USER statement and possibly also the REVOKE statement:

CREATE USER 'paola'@'localhost'
IDENTIFIED BY 'her_password',
'paola'@'caporale.com'
IDENTIFIED BY 'her_password';

In this example, two user accounts are created along with their passwords, but both are for the same person. The difference is that one allows the user to log into the server hosting the database and to run the mysql client or some other client on the server, the localhost. The other account allows the user to connect from a host named caporale.com using a client from that host. No other host will be allowed for this user.

Use this statement to delete a user account for the MySQL server. As of version 5.0.2 of MySQL, this statement will delete the user account and its privileges from all grant tables. The username is given within quotes, followed by the at sign (@) and the host IP address or hostname within quotes. This statement requires a CREATE USER privilege or DELETE privilege for the mysql database, which contains user account information and privileges. Dropping a user account does not affect current sessions for the user account. It will take effect when any sessions opened by the user terminate. Use the KILL statement (explained in Chapter 7) to terminate an open client session for a user that has been dropped.

Some users may have more than one user account (i.e., user and host combinations). You should check the server’s mysql.user table to be sure:

SELECT User,Host 
FROM mysql.user 
WHERE User LIKE 'paola';

+-------+--------------+
| User  | Host         |
+-------+--------------+
| paola | localhost    | 
| paola | caporale.com | 
+-------+--------------+

DROP USER 'paola'@'localhost',
'paola'@'caporale.com';

Prior to version 5.0.2 of MySQL, the DROP USER statement won’t delete a user that has any privileges set to 'Y'. To eliminate the user account’s privileges, issue the REVOKE statement before using DROP USER:

REVOKE ALL ON *.* FROM 'paola'@'localhost';

DROP USER 'paola'@'localhost';

The ALL option is used to ensure revocation of all privileges. The *.* covers all tables in all databases. Prior to version 4.1.1 of MySQL, you would have to issue the following instead of a DROP USER statement:

DELETE FROM mysql.user
WHERE User='paola' AND Host='localhost';

FLUSH PRIVILEGES;

Notice that the FLUSH PRIVILEGES statement is necessary for the preceding DELETE statement to take effect immediately. It’s not necessary after the DROP USER statement, though.

Options:

DES_KEY_FILE, HOSTS, LOGS, MASTER, PRIVILEGES, QUERY_CACHE,
STATUS, TABLE, TABLES, TABLES WITHOUT READ LOCK, USER_RESOURCES

Use this statement to clear and reload temporary caches in MySQL. It requires RELOAD privileges. To prevent this statement from writing to the binary log file, the NO_WRITE_TO_BINLOG flag or its LOCAL alias may be given. A particular cache to flush may be given as an option. Multiple options (see Table 4-1) may be given in a comma-separated list.

As of version 5.1 of MySQL, FLUSH cannot be used in stored functions and triggers, but can be used in stored procedures. As an alternative to the FLUSH statement, you can use the mysqladmin command (see Chapter 16).

This statement may be used to create new MySQL users, but its primary use is for granting user privileges. Privileges can be global (apply to all databases on the server), database-specific, table-specific, or column-specific. Users can now also be limited by functions and procedures. Additionally, users can be limited by number of connections or by a maximum of resources per hour.

The privileges to grant to a user are listed immediately after the GRANT keyword in a comma-separated list. To restrict a user to specific columns in a table, list those columns in a comma-separated list within parentheses. This is then followed by the ON clause in which the privileges granted may be limited to a database, table, function, or procedure. To limit the privileges to a function, use the FUNCTION keyword; to limit them to a procedure, use the PROCEDURE keyword.

For tables, the keyword TABLE is optional and the default. You can then specify the database to which the privileges relate in quotes, followed by a period (.) and the name of the table, function, or procedure in quotes. You may also use the asterisk wildcard (*) to specify all databases or all tables, functions, or procedures offered by the database.

In the TO clause, give the username (in quotes) and the IP address or host (also in quotes) for which the user account privileges are permitted, separated by an at sign (@). To provide the password for the user account, add the IDENTIFIED BY clause, followed by the user’s password in plain text and enclosed in quotes. To provide the password in encrypted hash form, add the keyword PASSWORD just before the password given. You can use the WITH clause to grant the GRANT OPTION privilege to a user so that that user may execute this statement. The GRANT statement with the IDENTIFIED BY clause can be used to change a password for an existing user.

For an explanation of how to restrict user accounts based on types of connections, see the next section of this statement (GRANT: Type of connection restrictions”). For information on how to restrict user accounts based on the amount of activity for a period of time or the number of connections permitted, see the last section of this statement (GRANT: Time and number of connection limits”). To see the privileges for a given user, use the SHOW GRANTS statement described later in this chapter.

A large variety of privileges may be granted to a user, so a common set of privileges has been combined in the ALL keyword. Here is an example:

GRANT ALL PRIVILEGES ON *.*
TO 'evagelia'@'localhost'
IDENTIFIED BY 'papadimitrou1234'
WITH GRANT OPTION;

In this example, the user evagelia is created and granted all basic privileges because of the ALL keyword. This does not include the GRANT privilege, the ability to use the GRANT statement. To do that, the WITH GRANT OPTION clause is given, as shown here, explicitly to give that privilege to the user. It’s not a good idea to give users this privilege unless they are MySQL server administrators. Table 4-2 later in this chapter lists and describes each privilege.

As mentioned before, a user’s privileges can be refined to specific SQL statements and specific databases. A GRANT statement can also restrict a user to only certain tables and columns. Here is an example that leaves the user fairly limited:

GRANT SELECT ON workrequests.*
TO 'jerry'@'localhost' IDENTIFIED BY 'neumeyer3186';

GRANT SELECT,INSERT,UPDATE ON workrequests.workreq
TO 'jerry'@'localhost' IDENTIFIED BY 'neumeyer3186';

Assuming the user jerry does not already exist, the first statement here creates the user and gives him SELECT privileges only for the workrequests database for all of its tables. This will allow him to read from the various tables but not edit the data. The second SQL statement grants jerry the right to add and change data in the workreq table of the workrequests database. This will allow him to enter work requests and make changes to them. The first statement causes an entry to be made to the db table in the mysql database. The second affects the tables_priv table. An entry is also made to the user table showing the user jerry, but he has no global privileges. This is the equivalent of granting just the USAGE privilege.

GRANT: Type of connection restrictions

GRANT privilege[,...] [(column[,...])][, ...] 
ON [TABLE|FUNCTION|PROCEDURE] {[{database|*}.{table|*}] | *}
TO 'user'@'host' [IDENTIFIED BY [PASSWORD] 'password'][, ...]

[REQUIRE NONE |
[{SSL|X509} [AND]] 
[CIPHER 'cipher' [AND]] 
[ISSUER 'issue' [AND]] 
[SUBJECT 'subject']]

[time and number of connection limits] ...]

A user can also be restricted to certain types of connections with the REQUIRE clause. There are several options that may be given together with the keyword AND. Each option can be used only once in a statement. REQUIRE NONE is the default and indicates that no such restrictions are required. Encrypted and unencrypted connections from clients are permitted from the user that has been properly authenticated.

The REQUIRE SSL option restricts the user account to only SSL-encrypted connections. The mysql client of the user account would start the client with the --ssl-ca option, and also the --ssl-key and --ssl-cert options if necessary:

GRANT ALL PRIVILEGES ON workrequests.* TO 'rusty'@'localhost'
IDENTIFIED BY 'her_password'
REQUIRE SSL;

Use the REQUIRE X509 option to require the user account to have a valid CA certificate. This does not require any specific certificate, though. The mysql client would need to be started with the --ssl-ca, --ssl-key, and --ssl-cert options. To simplify handling of these options, the user can put them in a options file in her home directory on the server (e.g., ~/.my.cnf). The following is a sample of what that options file would contain to conform to the user account restrictions:

[client]
ssl-ca=/data/mysql/cacert.pem
ssl-key=/data/mysql/rusty-key.pem
ssl-cert=/data/mysql/rusty-cert.pem

Use the REQUIRE CIPHER option to require that the user account use a given cipher method:

GRANT ALL PRIVILEGES ON workrequests.* TO 'rusty'@'localhost'
IDENTIFIED BY 'her_password'
REQUIRE CIPHER 'EDH-RSA-DES-CBC3-SHA';

REQUIRE ISSUER is used to require the user to supply a valid X.509 certificate issued by the given CA. Although the string given for an issuer may be lengthy, it must be written as one string without an embedded line break:

GRANT ALL PRIVILEGES ON workrequests.* TO 'rusty'@'localhost'
IDENTIFIED BY 'her_password'
REQUIRE ISSUER '/C=US/ST=Louisiana/L=New+20Orleans/O=WorkRequesters/CN=
   cacert.workrequests.com/emailAddress=admin@workrequests.com';

The REQUIRE SUBJECT option requires that the X.509 certificate used by the user account have the given subject:

GRANT ALL PRIVILEGES ON workrequests.* TO 'rusty'@'localhost'
IDENTIFIED BY 'her_password'
REQUIRE SUBJECT '/C=US/ST=Louisiana/L=New+20Orleans/O=WorkRequesters/CN=
   Rusty Osborne/emailAddress=rusty@workrequests.com';

GRANT: Time and number of connection limits

GRANT privilege[,...] [(column[,...])][, ...] 
ON [TABLE|FUNCTION|PROCEDURE] {[{database|*}.{table|*}] | *}
TO 'user'@'host' [IDENTIFIED BY [PASSWORD] 'password'][, ...]

[type of connection restrictions]

[WITH [MAX_QUERIES_PER_HOUR count |
       MAX_UPDATES_PER_HOUR count |
       MAX_CONNECTIONS_PER_HOUR count |
       MAX_USER_CONNECTIONS count] ...]

You can use the WITH clause along with the MAX_QUERIES_PER_HOUR option to specify the maximum number of queries that a user account may execute per hour. The MAX_UPDATES_PER_HOUR option is used to give the maximum number of UPDATE statements that may be issued per hour by the user account. The maximum number of connections by a user account to the server per hour can be set with the MAX_CONNECTIONS_PER_HOUR option. The default values for these three options are all 0. This value indicates that there is no limit or restrictions for these resources. The MAX_USER_CONNECTIONS option is used to set the maximum number of simultaneous connections the given user account may have. If this value is not set or is set to 0, the value of the system variable max_user_connections is used instead. Here is an example of how a user might be limited in such a way:

GRANT SELECT ON catalogs.*
TO 'webuser'@'%'
WITH MAX_QUERIES_PER_HOUR 1000
MAX_CONNECTIONS_PER_HOUR 100;

This account is designed for large numbers of users running queries through a web server. The statement creates the webuser user and allows it to read tables from the catalogs database. The user may not run more than 1,000 queries in an hour and may establish only 100 connections in an hour.

To change an existing user account’s resources without changing the account’s existing privileges, you can use the USAGE keyword. Simply enter a statement like this:

GRANT USAGE ON catalogs.*
TO 'webuser'@'%'
WITH MAX_QUERIES_PER_HOUR 10000
MAX_CONNECTIONS_PER_HOUR 100;

In this example, the existing user account has been limited in resources without changing the user account’s privileges. See Table 4-2 for a list of privileges.

Use this statement to change the username or the host of an existing user account. It does not change the user privileges or necessarily migrate any privileges to specific databases, events, stored routines, tables, triggers, or views. Here is an example:

RENAME USER 'michaelzabalaoui'@'localhost' TO 'zabb'@'%',
'richardstringer'@'localhost' TO 'littlerichard'@'localhost';

The first user’s name and host have been changed here, whereas the second user’s name only was changed.

Use this statement to reset certain server settings and log files. The RELOAD privilege is required to use this statement. The QUERY CACHE option clears the cache containing SQL query results.

Use the MASTER option to reset a master used for replication. This statement must be executed from the master itself. It will start a new binary log file, as well as delete the binary log file names from the index file and delete the contents of the binary log index file. The SLAVE option is used to reset a slave used for replication and must be executed from the slave itself. It will start a new relay log file and delete any existing ones, as well as delete its notation of its position in the master’s binary log. See Chapter 8 on replication for more information on these two options.

Use this statement to revoke some or all privileges that were granted to a user with the GRANT statement. The first syntax is used to revoke all privileges from a user. Multiple users may be given in a comma-separated list. A list of users and their privileges are stored in the mysql database, in the user table in particular:

REVOKE ALL PRIVILEGES
ON *.*
FROM 'paola'@localhost';

To revoke only some privileges, use the second syntax structure, giving the specific privileges to be removed in a comma-separated list after the keyword REVOKE. For a list of privileges and their descriptions, see Table 4-2 under the description of the GRANT statement earlier in this chapter.

To revoke privileges for specific columns, list the columns within parentheses in a comma-separated list. Privileges that are granted based on columns are stored in the columns_priv table of the mysql database. Privileges may be revoked on a specific table for a specific database. To revoke privileges on all tables of a database, specify an asterisk as a wildcard for the table name. You can do the same for the database name to apply the statement to all databases. Table-specific privileges are stored in the tables_priv table, and database-specific privileges are stored in the db table.

Use this statement to change the password for a user account. The username and host must be given. The change of password will apply only to the given combination of username and host. It won’t apply to other hosts for the same user in the grant tables.

To get a list of user accounts on your server, enter the following SQL statement:

SELECT User, Host FROM mysql.user;

If the FOR clause is not given with the SET PASSWORD statement, the current user account is assumed. The PASSWORD() function will encrypt the password given.

This statement does not need to be followed by a FLUSH PRIVILEGES statement. It will automatically update the privileges cache for the new password. If you updated your server from a version before 4.1 to a new version, you may have problems changing a user account’s password and cause the user account’s password to become invalid. You may need to run the mysql_fix_privilege_tables utility to change the Password column in the user table in the mysql database. See Chapter 16 for more information on this utility.

Here is an example of changing a user account’s password:

SET PASSWORD FOR 'kenneth'@'localhost' = PASSWORD('his_password');

This SQL statement displays the GRANT statement for a given user. If the FOR clause is not given, the current user account is assumed. If the username is given without reference to a particular host, the wildcard % is assumed. Otherwise, the username should be followed by the host as shown here:

SHOW GRANTS FOR 'russell'@'localhost'\G

*************************** 1. row ***************************
Grants for russell@localhost: 

GRANT ALL PRIVILEGES ON *.* 
TO 'russell'@'localhost' 
IDENTIFIED BY PASSWORD '57fa103a3c5c9f30' 
WITH GRANT OPTION

The resulting statement is what would be entered to create the user russell for the host localhost, with the given privileges including the WITH GRANT OPTION flag.

This statement provides a list of privileges available, along with the context of each one (e.g., server administration) and a description. The output is not based on the user. Instead, it’s a complete listing of the privileges that may be assigned to a user. This statement is available as of version 4.1 of MySQL.

This function decrypts text that was encrypted using the Advanced Encryption Standard (AES) algorithm with a 128-bit key length, reversing the AES_ENCRYPT() function. The function unlocks the encrypted string with the password given as the second argument. It returns NULL if one of the given parameters is NULL. This is available as of version 4.0.2 of MySQL. Here is an example:

SELECT AES_DECRYPT(personal, 'my_password') AS Personal
FROM teachers
WHERE teacher_id='730522';

+----------+
| Personal |
+----------+
| text     |
+----------+

In this example, the value for the personal column is decrypted using the password given. The result is just the plain text of the column.

This function encrypts a given string using the AES algorithm with a 128-bit key length. It locks the encrypted string with the password given as the second argument. The function returns NULL if one of the given parameters is NULL. It’s available as of version 4.0.2 of MySQL. The results of this function can be reversed with AES_DECRYPT(). Here is an example:

UPDATE teachers
SET personal = AES_ENCRYPT('text', 'my_password')
WHERE teacher_id = '730522';

This function returns the username and the host that were given by the user for the current MySQL connection. There are no arguments for the function. It may not always return the same results as USER(). Here is an example:

SELECT CURRENT_USER( ), USER( );

+-----------------+------------------+
| CURRENT_USER( ) | USER( )          |
+-----------------+------------------+
| ''@localhost    | russel@localhost |
+-----------------+------------------+

In this example, the user logged in to the mysql client with the username russel (missing one “l” in the name), but because there isn’t an account for that user, the client logged in with the anonymous (i.e., '') account.

This function decrypts a given string that was encrypted with a given password. See the ENCODE() function later in this chapter:

SELECT ENCODE(pwd, 'oreilly')
FROM teachers
WHERE teacher_id = '730522';

This function decrypts the contents of the pwd column and unlocks it using the oreilly password, which was used to encrypt it originally using ENCODE().

This function decrypts text that was encrypted using the triple Data Encryption Standard (DES) algorithm with a 128-bit key length, reversing the DES_ENCRYPT() function. It returns NULL if an error occurs. The function will work only if MySQL has been configured for Secure Sockets Layer (SSL) support. It is available as of version 4.0.1 of MySQL. Here is an example:

SELECT DES_DECRYPT(credit_card_nbr, 0)
FROM orders
WHERE order_nbr = '8347';

In this example, the value for the credit_card_nbr column is decrypted using the first key string in the key file. See the description of DES_ENCRYPT() next for more information on key files.

This function returns encrypted text using the triple DES algorithm with a 128-bit key length. It returns NULL if an error occurs. The function is available as of version 4.0.1 of MySQL.

This function requires MySQL to be configured for SSL support. In addition, a key file must be created and the mysqld daemon must be started with the --des-key-file option. The key file should be set up with a separate key string on each line. Each line should begin with a single-digit number (0–9) as an index, followed by a space before the key string (e.g., key_number des_string).

The key given as the second argument to the function can either be the actual key to use for encryption or a number that refers to a key in the key file. If the second argument is omitted, the function uses the first key in the key file:

UPDATE orders
SET credit_card_nbr = DES_ENCRYPT('4011-7839-1234-4321')
WHERE order_nbr = '8347';

The results of this function can be reversed with DES_DECRYPT().

This function encrypts a given string in binary format and locks it with the password. You should not use this function for the password column in the user table of the mysql database. Use PASSWORD() instead. Here is an example:

UPDATE teachers
SET pwd = ENCODE('test', 'oreilly')
WHERE teacher_id = '730522';

The function here encrypts the word test and locks it with the oreilly password. The results are stored in the pwd column for the chosen teacher. To unlock the results, use the DECODE() function with the same password.

This function returns encrypted text using the C-language crypt function. A two-character string may be given in the second argument to increase the randomness of encryption. The resulting string cannot be decrypted. You should not use this function for the password column in the user table of the mysql database. Use PASSWORD() instead. Here is an example:

UPDATE teachers
SET pwd = ENCRYPT('test', 'JT')
WHERE teacher_id = '730522';

This function uses a Message-Digest algorithm 5 (MD5) 128-bit checksum to return a 32-character hash value of string from the Request for Comments (RFC) 1321 standard. Here is an example:

SELECT MD5('Test') AS 'MD5( ) Test';
+----------------------------------+
| MD5( ) Test                      |
+----------------------------------+
| 0cbc6611f5540bd0809a388dc95a615b |
+----------------------------------+

This function encrypts a given string based on the password encryption method used prior to version 4.1 of MySQL. The result cannot be decrypted. Here is an example:

UPDATE teachers
SET pwd = OLD_PASSWORD('test')
WHERE teacher_id = '730522';

This function encrypts a password given as an argument. The result cannot be decrypted. This function is used for encrypting data in the password column of the user table in the mysql database. Here is an example:

UPDATE teachers
SET pwd = PASSWORD('test')
WHERE teacher_id = '730522';

This function returns the username and the hostname for the current MySQL connection. The function takes no arguments. It’s synonymous with SYSTEM_USER() and USER().

This function returns the Secure Hash Algorithm (SHA) 160-bit checksum for the given string. The result is a string composed of 40 hexadecimal digits. NULL is returned if the given string is NULL. This function is synonymous with SHA1(). Here is an example:

 SELECT SHA('test');

+------------------------------------------+
| SHA('test')                              |
+------------------------------------------+
| a94a8fe5ccb19ba61c4c0873d391e987982fbbd3 |
+------------------------------------------+

This function returns the SHA 160-bit checksum for the given string. The result is a string composed of 40 hexadecimal digits. NULL is returned if the given string is NULL. This function is synonymous with SHA().

This function returns the username and the hostname for the current MySQL connection. The function takes no arguments. It’s synonymous with SESSION_USER() and USER().

This function returns the username and the hostname for the current MySQL connection. The function takes no arguments. It’s synonymous with SESSION_USER() and with SYSTEM_USER(). Here is an example:

SELECT USER( );

+-------------------+
| USER( )           |
+-------------------+
| russell@localhost |
+-------------------+

Use this statement to alter settings for a database. Version 4.1.1 of MySQL introduced this function and added a file named db.opt containing the database settings to the database directory. Currently, two options are available: CHARACTER SET and COLLATE. Here are the contents of a typical db.opt file:

default-character-set=latin1
default-collation=latin1_swedish_ci

Although an administrator can edit the file manually, it may be more robust to use the ALTER DATABASE statement to change the file. It’s synonymous with ALTER SCHEMA as of version 5.0.2 of MySQL. The ALTER privilege is necessary for this statement.

The CHARACTER SET option can set the first line shown, which specifies the default database character set that will be used. The COLLATE option can set the second line, which specifies the default database collation (how the character data is alphabetized). Here’s an example of the use of this statement:

ALTER DATABASE human_resources
CHARACTER SET latin2_bin
COLLATE latin2_bin;

Notice that both options may be given in one SQL statement. The DEFAULT keyword is unnecessary, but it is offered for compatibility with other database systems. Beginning with version 4.1.8 of MySQL, if the name of the database is omitted from this SQL statement, the current database will be assumed. To determine the current database, use the DATABASE() function:

SELECT DATABASE();

+--------------+
| DATABASE()   |
+--------------+
| workrequests |
+--------------+

See the explanations for the SHOW CHARACTER SET and SHOW COLLATION SQL statements later in this chapter for more information on character sets and collations.

This statement is synonymous with ALTER DATABASE. See the description of that statement previously for more information and examples.

Use this SQL statement with the FEDERATED storage engine to change the connection parameters of a server created with CREATE SERVER. The values given are stored in the server table of the mysql database. Options are given in a comma-separated list. Option values must be specified as character or numeric literals (UTF-8; maximum length of 64 characters). This statement was introduced in version 5.1.15 of MySQL and requires SUPER privileges:

ALTER SERVER server1
OPTIONS (USER 'test_user', PASSWORD 'testing123', PORT 3307);

This example changes the values of an existing server, the username, the password, and the port to be used for connecting to the server.

Use this statement to change an existing table’s structure and other properties. A table may be altered with this statement in the following ways:

The IGNORE flag applies to all clauses and instructs MySQL to ignore any error messages regarding duplicate rows that may occur as a result of a column change. It will keep the first unique row found and drop any duplicate rows. Otherwise, the statement will be terminated and changes will be rolled back.

This statement requires the ALTER, CREATE, and INSERT privileges for the table being altered, at a minimum. While an ALTER TABLE statement is being executed, users will be able to read the table, but usually they won’t be able to modify data or add data to a table being altered. Any INSERT statements using the DELAYED parameter that are not completed when a table is altered will be canceled and the data lost. Increasing the size of the myisam_sort_buffer_size system variable will sometimes make MyISAM table alterations go faster.

The syntax and explanation of each clause follows, with examples, grouped by type of clause. Multiple alterations may be combined in a single ALTER TABLE statement. They must be separated by commas and each clause must include the minimally required elements.

ALTER TABLE: ADD clauses for columns

ALTER [IGNORE] TABLE table
ADD [COLUMN] column definition [FIRST|AFTER column] |
ADD [COLUMN] (column definition,...)

These clauses add columns to a table. The same column definitions found in a CREATE TABLE statement are used in this statement. Basically, the statements list the name of the column followed by the column data type and the default value or other relevant components. The COLUMN keyword is optional and has no effect.

By default, an added column is appended to the end of the table. To insert a new column at the beginning of a table, use the FIRST keyword at the end of the ADD COLUMN clause. To insert it after a particular existing column, use the AFTER keyword followed by the name of the column after which the new column is to be inserted:

ALTER TABLE workreq
ADD COLUMN req_type CHAR(4) AFTER req_date,
ADD COLUMN priority CHAR(4) AFTER req_date;

In this example, two columns are added after the existing req_date column. The clauses are executed in the order that they are given. Therefore, req_type is placed after req_date. Then priority is added after req_date and before req_type. Notice that you can give more than one clause in one ALTER TABLE statement; just separate them with commas.

ALTER TABLE: ADD clause for standard indexes

ALTER [IGNORE] TABLE table
ADD {INDEX|KEY} [index] [USING index_type] (column,...)

Use the ADD INDEX clause to add an index to a table. If you omit the name of the index, MySQL will set it to the name of the first column on which the index is based. The type of index may be stated, but usually it’s not necessary. The names of one or more columns for indexing must be given within parentheses, separated by commas.

Here is an example of how you can add an index using the ALTER TABLE statement, followed by the SHOW INDEXES statement with the results:

ALTER TABLE clients
ADD INDEX client_index
(client_name(10), city(5)) USING BTREE;

SHOW INDEXES FROM clients \G

*************************** 1. row ***************************
       Table: clients
  Non_unique: 0
    Key_name: PRIMARY
Seq_in_index: 1
 Column_name: client_id
   Collation: A
 Cardinality: 0
    Sub_part: NULL
      Packed: NULL
        Null: 
  Index_type: BTREE
     Comment: 
*************************** 2. row ***************************
       Table: clients
  Non_unique: 1
    Key_name: client_index
Seq_in_index: 1
 Column_name: client_name
   Collation: A
 Cardinality: NULL
    Sub_part: 10
      Packed: NULL
        Null: YES
  Index_type: BTREE
     Comment: 
*************************** 3. row ***************************
       Table: clients
  Non_unique: 1
    Key_name: client_index
Seq_in_index: 2
 Column_name: city
   Collation: A
 Cardinality: NULL
    Sub_part: 5
      Packed: NULL
        Null: YES
  Index_type: BTREE
     Comment: 

As you can see in the results, there was already an index in the table clients (see row 1). The index we’ve added is called client_index. It’s based on two columns: the first 10 characters of the client_name column and the first 5 characters of the city column. Limiting the number of characters used in the index makes for a smaller index, which will be faster and probably just as accurate as using the complete column widths. The results of the SHOW INDEXES statement show a separate row for each column indexed, even though one of the indexes involves two rows.

The table in this example uses the MyISAM storage engine, which uses the BTREE index type by default, so it was unnecessary to specify a type. See Appendix A for more information about storage engines and available index types. Before MySQL version 5.1.10, the USING subclause could come either before or after the column list, but as of version 5.1.10, it must follow the column list.

ALTER TABLE: ADD clause for FULLTEXT indexes

ALTER [IGNORE] TABLE table
ADD FULLTEXT [INDEX|KEY] [index] (column,...) [WITH PARSER parser]

The ADD FULLTEXT clause adds an index to a TEXT column within an existing MyISAM table. A FULLTEXT index can also index CHAR and VARCHAR columns. This type of index is necessary to use the FULLTEXT functionality (the MATCH() AGAINST() function from Chapter 11). The INDEX and KEY keywords are optional as of MySQL version 5.

With this index, the whole column will be used for each column given. Although you can instruct it to use only the first few characters of a table, it will still use the full column for the index. The WITH PARSER clause may be used to give a parser plugin for a FULLTEXT index:

ALTER TABLE workreq
ADD FULLTEXT INDEX notes_index
(client_description, technician_notes);

SHOW INDEXES FROM workreq \G

*************************** 2. row ***************************
       Table: workreq
  Non_unique: 1
    Key_name: notes_index
Seq_in_index: 1
 Column_name: client_description
   Collation: NULL
 Cardinality: NULL
    Sub_part: NULL
      Packed: NULL
        Null: YES
  Index_type: FULLTEXT
     Comment: 
*************************** 3. row ***************************
       Table: workreq
  Non_unique: 1
    Key_name: notes_index
Seq_in_index: 2
 Column_name: technician_notes
   Collation: NULL
 Cardinality: NULL
    Sub_part: NULL
      Packed: NULL
        Null: YES
  Index_type: FULLTEXT
     Comment: 

I’ve eliminated the first row from these results because it relates to the primary index, not the one created here.

As of version 5.1 of MySQL, you can use the WITH PARSER clause to specify a parser plugin for a FULLTEXT index. This option requires that the plugin table be loaded in the mysql database. This table is part of the current installation of MySQL. If you’ve upgraded MySQL and the plugin table is not in your system’s mysql database, use the mysql_upgrade script to add it. Use the SHOW PLUGINS statement to see which plugins are installed.

ALTER TABLE: ADD clause for SPATIAL indexes

ALTER [IGNORE] TABLE table
ADD SPATIAL [INDEX|KEY] [index] (column,...)

This ADD clause is used to add a SPATIAL index. A SPATIAL index can index only spatial columns. A spatial index is used in a table that holds data based on the Open Geospatial Consortium (http://www.opengis.org) data for geographical and global positioning satellite (GPS) systems. For our purposes here, this clause is necessary to add an index for spatial extensions. For MyISAM tables, the RTREE index type is used. The BTREE is used by other storage engines that use nonspatial indexes of spatial columns. Here is an example:

ALTER TABLE squares
ADD SPATIAL INDEX square_index (square_points);

SHOW INDEXES FROM squares \G

*************************** 1. row ***************************
       Table: squares
  Non_unique: 1
    Key_name: square_index
Seq_in_index: 1
 Column_name: square_points
   Collation: A
 Cardinality: NULL
    Sub_part: 32
      Packed: NULL
        Null: 
  Index_type: SPATIAL
     Comment:

Notice that when we created the table, we specified that the column square_points is NOT NULL. This is required to be able to index the column. See the CREATE INDEX statement for SPATIAL indexes in this chapter for an explanation and more examples related to spatial indexes.

ALTER TABLE: ADD clauses for foreign keys

ALTER [IGNORE] TABLE table
ADD [CONSTRAINT [symbol]] PRIMARY KEY [USING index_type] (column,...) |
ADD [CONSTRAINT [symbol]] UNIQUE [INDEX|KEY] index [USING index_type] 
   (column,...) |
ADD [CONSTRAINT [symbol]] FOREIGN KEY [index] (column,...) 
   [REFERENCES table (column,...)
   [ON DELETE {RESTRICT|CASCADE|SET NULL|NO ACTION|SET DEFAULT}]
   [ON UPDATE {RESTRICT|CASCADE|SET NULL|NO ACTION|SET DEFAULT}]]

These ADD clauses add foreign keys and references to InnoDB tables. A foreign key is an index that refers to a key or an index in another table. See the explanation of the CREATE TABLE statement later in this chapter for more information and for an example of an SQL statement involving the creation of foreign keys in a table. The various flags shown are also explained in the CREATE TABLE statement.

Here is an example:

CREATE TABLE employees 
(emp_id INT AUTO_INCREMENT PRIMARY KEY, 
tax_id CHAR(12), 
emp_name VARCHAR(100))
ENGINE = INNODB;

CREATE TABLE employees_telephone 
(emp_id INT, 
tel_type ENUM('office','home','mobile'),
tel_number CHAR(25)) 
ENGINE = INNODB;

ALTER TABLE employees_telephone 
ADD FOREIGN KEY emp_tel (emp_id) 
REFERENCES employees (emp_id) 
ON DELETE RESTRICT;

The first two SQL statements create InnoDB tables: one for basic employee information and the other for employee telephone numbers. Using the ALTER TABLE statement afterward, we add a foreign key restriction between the two. Let’s look at the results using the SHOW TABLE STATUS statement, because the SHOW INDEXES statement won’t show foreign key restraints:

SHOW TABLE STATUS FROM human_resources 
LIKE 'employees_telephone' \G

*************************** 1. row ***************************
           Name: employees_telephone
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 0
 Avg_row_length: 0
    Data_length: 16384
Max_data_length: 0
   Index_length: 16384
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2007-04-03 04:01:39
    Update_time: NULL
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options: 
        Comment: InnoDB free: 4096 kB; ('emp_id') 
                 REFER 'human_resources'.'employees'('emp_id')

In the Comment field, we can see that we’ve created a restraint on the main table employees from employees_telephone. We’re telling MySQL not to allow a row for an employee to be removed from the employees table without first removing the rows of data for the employee in the employees_telephone table.

In the following example, we first insert an employee in the employees table, then add her home telephone number to the second table, and then attempt to delete her from the first table:

INSERT INTO employees 
VALUES(1000,'123-45-6789','Paola Caporale');

INSERT INTO employees_telephone 
VALUES(1000,2,'+39 343-12-34-5678');

DELETE FROM employees WHERE emp_id = 1000;

ERROR 1451 (23000): Cannot delete or update a parent row: 
a foreign key constraint fails 
('human_resources'.'employees_telephone', 
  CONSTRAINT 'employees_telephone_ibfk_1'
  FOREIGN KEY ('emp_id') REFERENCES 'employees' ('emp_id')
)

As you can see, we cannot delete the employee from the employees table and leave the stray row of data in the employees_telephone table. We have to delete the data in employees_telephone first, before deleting the related data from employees. See the explanation under CREATE TABLE in the CREATE TABLE: Foreign key references” section later in this chapter for examples of the other options with foreign keys. Incidentally, you can’t drop and add a foreign key in the same ALTER TABLE statement.

ALTER TABLE: CHANGE clauses

ALTER [IGNORE] TABLE table
ALTER [COLUMN] column {SET DEFAULT value|DROP DEFAULT} |
CHANGE [COLUMN] column column definition [FIRST|AFTER column] |
MODIFY [COLUMN] column definition [FIRST|AFTER column]

These three clauses are used to alter an existing column in a table. The first syntax structure is used either to set the default value of a column to a particular value or to reset it back to its default value for its column type (usually NULL or 0). The other two syntax structures are used primarily to change the column definitions. The COLUMN keyword is optional and has no effect.

To change the column’s character set, add CHARACTER SET to the end of the column definition for the CHANGE or MODIFY clauses, followed by the character set name to use. Here’s an example of the first clause:

ALTER TABLE clients
ALTER COLUMN city SET DEFAULT 'New Orleans';

This statement sets the default value of the city column in the clients table to a value of New Orleans, because that’s where most of the clients are located.

The clauses that change column definitions are roughly synonymous; they follow the standards of different SQL systems for the sake of compatibility (e.g., MODIFY is used with Oracle). They can also be used to relocate the column within the table schema with the FIRST or the AFTER keywords. If a column’s data type is changed, MySQL attempts to adjust the data to suit the new data type. If a column width is shortened, MySQL truncates the data and generates warning messages for the affected rows. Indexes related to changed columns will be adjusted automatically for the new lengths.

In the CHANGE clause, the current column name must be specified first, followed by either the same column name if the name is to remain the same, or a new column name if the name is to be changed. The full column definition for the column must be given as well, even if it’s not to be changed.

The MODIFY clause cannot be used to change a column’s name, so the column name appears only once with it.

The following SQL statement shows the columns in the clients table, where the column name begins with a c and contains an i to list the columns that begin with either client or city. After viewing these limited results, we change one column using each of the clauses for changing column definitions:

SHOW COLUMNS FROM clients LIKE 'c%i%';

+--------------+--------------+------+-----+---------+----------------+
| Field        | Type         | Null | Key | Default | Extra          |
+--------------+--------------+------+-----+---------+----------------+
| client_id    | int(11)      | NO   | PRI | NULL    | auto_increment | 
| client_name  | varchar(255) | YES  | MUL | NULL    |                | 
| city         | varchar(255) | YES  |     | NULL    |                | 
| client_zip   | char(10)     | YES  |     | NULL    |                | 
| client_state | char(2)      | YES  |     | NULL    |                | 
+--------------+--------------+------+-----+---------+----------------+

ALTER TABLE clients
CHANGE COLUMN city client_city VARCHAR(100) CHARACTER SET 'latin2',
MODIFY COLUMN client_state CHAR(4) AFTER client_city;

After looking at the current columns, we’ve decided to change the name of the city column to client_city to match the other related columns, and to enlarge the client_state column and move it before the column for the postal ZIP code. To do this, the CHANGE clause is used to change the name of the city column, but not its column type and size. The second clause changes the column type and size and relocates the client_state column to a position after the client_city column.

When a column is changed, MySQL will attempt to preserve the data. If a column size is reduced, the data won’t be completely deleted, but it may be truncated, in which case the results will show a number of warnings. Use the SHOW WARNINGS statement to view them.

ALTER TABLE: DROP column clause

ALTER [IGNORE] TABLE table
DROP [COLUMN] column

The DROP clause of the ALTER TABLE statement removes a given column from a table and deletes the column’s data. A table must have at least one column, so this statement will fail if used on the only column in a table. Use the DROP TABLE statement to delete a table. If a dropped column is part of an index, the column will be removed automatically from the index definition. If all of the columns of an index are dropped, the index will automatically be dropped.

Here is an example including this clause:

ALTER TABLE clients
DROP COLUMN miscellaneous,
DROP COLUMN comments;

This statement drops two columns and deletes the data they contain without warning. Notice that multiple columns may be dropped by separating each clause by a comma. It’s not possible to combine clauses. That is to say, ...DROP COLUMN (miscellaneous, comments) is not permitted. Once a column has been deleted, you won’t be able to recover its data from MySQL. Instead, you’ll have to restore the table from a backup of your data if you made one.

ALTER TABLE: DROP index clauses

ALTER [IGNORE] TABLE table
DROP INDEX index |
DROP PRIMARY KEY |
DROP FOREIGN KEY foreign_key_symbol

These clauses are used to delete indexes. A standard index is fairly easy to eliminate with the first syntax shown. Here’s an example of its use:

ALTER TABLE clients 
DROP INDEX client_index;

The second syntax deletes the primary key index of a table. However, if the primary key is based on a column with an AUTO_INCREMENT type, you may need to change the column definition in the same statement so it is no longer AUTO_INCREMENT before you can drop the primary key. Here is an example in which we fail to change the indexed column first:

ALTER TABLE clients
DROP PRIMARY KEY;

ERROR 1075 (42000): Incorrect table definition; 
there can be only one auto column and it must be defined as a key

ALTER TABLE clients
CHANGE client_id client_id INT,
DROP PRIMARY KEY;

The first SQL statement here causes an error in which MySQL complains that if we are going to have a column with AUTO_INCREMENT, it must be a key column. So using the CHANGE clause in the second SQL statement, we change the client_id column from INT AUTO_INCREMENT to just INT. After the AUTO_INCREMENT is removed, the PRIMARY KEY may be dropped. Before version 5.1 of MySQL, if a primary key doesn’t exist, the first UNIQUE key is dropped instead. After version 5.1, an error is returned and no key is dropped.

To delete a foreign key, the third syntax is used. Here is an example that deletes a foreign index:

ALTER TABLE client
DROP FOREIGN KEY '0_34531';

In this example, the name of the index is not the name of any of the columns, but an index that was created by combining two columns and was given its own name. The name was changed by InnoDB automatically. To get a list of indexes for a table, use the SHOW CREATE TABLE statement.

ALTER TABLE: Miscellaneous clauses

ALTER [IGNORE] TABLE table
CONVERT TO CHARACTER SET charset [COLLATE collation] | 
[DEFAULT] CHARACTER SET charset [COLLATE collation] | 
DISABLE|ENABLE KEYS | 
DISCARD|IMPORT TABLESPACE | 
ORDER BY column [ASC|DESC][,...] | 
RENAME [TO] table

You can use these miscellaneous clauses with the ALTER TABLE statement to change a variety of table properties. They are described here in the order that they are listed in the syntax.

SHOW TABLE STATUS LIKE 'clients' \G

*************************** 1. row ***************************
           Name: clients
         Engine: MyISAM
        Version: 10
     Row_format: Dynamic
           Rows: 632
 Avg_row_length: 12732
    Data_length: 1024512
Max_data_length: 281474976710655
   Index_length: 3072
      Data_free: 0
 Auto_increment: 1678
    Create_time: 2006-02-01 14:12:31
    Update_time: 2007-04-03 05:25:41
     Check_time: 2006-08-14 21:31:36
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options: max_rows=1000
        Comment: This table lists basic information on clients.

ALTER TABLE clients
CONVERT TO CHARACTER SET latin2 COLLATE latin2_bin,
DEFAULT CHARACTER SET latin2 COLLATE latin2_bin;

ALTER TABLE sales_dept.catalog
DISABLE KEYS;

LOAD DATA INFILE '/tmp/catalog.txt'
INTO TABLE sales_dept.catalog
FIELDS TERMINATED BY '|'
LINES TERMINATED BY '\n';

ALTER TABLE sales_dept.catalog
ENABLE KEYS;

ALTER TABLE workreq
IMPORT TABLESPACE;

ALTER TABLE clients
ORDER BY client_name;

ALTER TABLE client RENAME TO clients;

ALTER TABLE: Partition altering clauses

ALTER [IGNORE] TABLE table
PARTITION BY options | 
ADD PARTITION (definition) | 
COALESCE PARTITION number | 
DROP PARTITION partition | 
REORGANIZE PARTITION partition INTO (definition) | 
REMOVE PARTITIONING

These table partition clauses for ALTER TABLE may be used to add or remove partitions in a table. They were added as of version 5.1.6 of MySQL. For partition clauses that analyze, check, optimize, rebuild, and repair partitions in a table, see the next subsection (ALTER TABLE: Partition administration clauses”). Also, see the CREATE TABLE statement explanation for more information on table partitioning.

It should be noted that the execution of the partition clauses for ALTER TABLE is very slow. You may not want to use them with data that is in use if you can avoid it. Instead, you might deploy a method of locking the table to be partitioned for read-only activities, making a copy of the table, partitioning the new table, and switching the new table with the old one, but keeping the old table as a backup copy in case there are problems.

This section includes several examples of partitioning a MyISAM table. The partition clauses are explained as they are used in each example. Partitioning is visible at the filesystem level, so to start, let’s look at a table’s files:

ls -1 clients*

clients.frm
clients.MYD
clients.MYI

We used the ls command (because this server is running Linux) at the command line to get a directory listing of the files for the clients table, in the sales_dept database subdirectory, in the data directory for MySQL. You can see the usual three file types for a MyISAM table.

The PARTITION BY clause can be used to initially partition a table with the ALTER TABLE statement. Any partition options used with the same clause in the CREATE TABLE statement may be used in ALTER TABLE. See the definition of the CREATE TABLE statement later in this chapter for more options.

In the following example, we alter the table clients using this clause to create partitions:

ALTER TABLE clients
PARTITION BY KEY(client_id)
PARTITIONS 2;

In this statement, we are instructing MySQL to partition the given table by the KEY method using the client_id column. We further tell it to split the table into two partitions. Now, let’s run the ls command again to see the results at the filesystem level:

ls -1 clients*

clients.frm
clients.par
clients#P#p0.MYD
clients#P#p0.MYI
clients#P#p1.MYD
clients#P#p1.MYI

As you can see, we now have a pair of index and datafiles for each partition, along with another file related to the partition schema (i.e., the .par file). The table schema file (i.e., the .frm file) remains unchanged.

The ADD PARTITION clause adds a new partition to a table in which partitions are determined based on a range of values. To demonstrate this, let’s partition the clients table again, but this time we’ll base the partitioning on a range of values for the client_id column, the primary key. If a table has a primary key, that key must be included in the basis of the partitions:

ALTER TABLE clients
ADD PARTITION (PARTITION p2);

The REMOVE PARTITIONING clause removes partitioning from a table. It shifts data back to one datafile and one index file. Here is an example of its use:

ALTER TABLE clients
REMOVE PARTITIONING;

For some situations, the ADD PARTITION clause discussed previously won’t work. In particular, it won’t work with a table in which the last partition was given the range of MAXVALUE:

ALTER TABLE clients
PARTITION BY RANGE (client_id) (
PARTITION p0 VALUES LESS THAN (400),
PARTITION p1 VALUES LESS THAN MAXVALUE);

ALTER TABLE clients
ADD PARTITION (PARTITION p2 VALUES LESS THAN (800));

ERROR 1481 (HY000): 
VALUES LESS THAN value must be strictly increasing for each partition

Instead of ADD PARTITION, the REORGANIZE PARTITION clause can be used to split the data contained in the last partition into two separate partitions. This clause can be used to separate the data in an existing partition into multiple partitions based on their given partition definitions.

Here is an example of this clause using the partitions previously described:

ALTER TABLE clients
REORGANIZE PARTITION p1 INTO
(PARTITION p1 VALUES LESS THAN (800),
PARTITION p2 VALUES LESS THAN MAXVALUE);

When experimenting with an empty table, this SQL statement takes my server 10 seconds to execute. Consider this when using this clause or any partitioning clauses with ALTER TABLE.

The DROP PARTITION clause may be used to eliminate named partitions in an existing table and to delete the data contained in the dropped partitions. To reduce the number of partitions without loss of data, see the COALESCE PARTITION clause for this same SQL statement. For an example of the DROP PARTITION clause, if you have a table that has six partitions and you want to delete two of them, you could execute an SQL statement like the second one here:

CREATE TABLE clients
(client_id INT,
name VARCHAR(255))
PARTITION BY RANGE (client_id) (
PARTITION p0 VALUES LESS THAN (400),
PARTITION p1 VALUES LESS THAN (800),
PARTITION p2 VALUES LESS THAN (1000),
PARTITION p3 VALUES LESS THAN MAXVALUE);

ALTER TABLE clients
DROP PARTITION p1, p2;

Notice that the ALTER TABLE statement is dropping two middle partitions and not the last one. The data contained in the two dropped would be lost if they had any. Because of the MAXVALUE parameter of the last partition, any new rows of data that have a client_id of 400 or greater will be stored in the p3 partition. Partitions need to be in order, but not sequentially named.

The COALESCE PARTITION clause may be used to reduce the number of partitions in an existing table by the number given. For example, if you have a table that has four partitions and you want to reduce it to three, you could execute a statement like the ALTER TABLE one here:

CREATE TABLE clients
(client_id INT,
name VARCHAR(255))
PARTITION BY HASH( client_id )
PARTITIONS 4;

ALTER TABLE clients
COALESCE PARTITION 1;

Notice that the PARTITION keyword in this last SQL statement is not plural. Also notice that you give the number of partitions by which you want to reduce the partitions, not the total you want. If you give a value equal to or greater than the number of partitions in the table, you’ll receive an error instructing you that you must use DROP TABLE instead.

See the CREATE TABLE statement explanation for more information about table partitioning.

ALTER TABLE: Partition administration clauses

ALTER [IGNORE] TABLE table
ANALYZE PARTITION partition | 
CHECK PARTITION partition | 
OPTIMIZE PARTITION partition |
REBUILD PARTITION partition | 
REPAIR PARTITION partition

Because the ANALYZE TABLE, CHECK TABLE, OPTIMIZE TABLE, and REPAIR TABLE statements do not work with partitioned tables, you will have to use the clauses of ALTER TABLE in this subsection instead. They all follow the same syntax format: the clause is followed by a comma-separated list of partitions to be administered.

The ANALYZE PARTITION clause may be used to read and store the indexes of a partition:

ALTER TABLE clients
ANALYZE PARTITION p0, p1, p2;

To check a partition for corrupted data and indexes, use the CHECK PARTITION clause:

ALTER TABLE clients
CHECK PARTITION p0, p1, p2;

Use the OPTIMIZE PARTITION clause to compact a partition in which the data has changed significantly:

ALTER TABLE clients
OPTIMIZE PARTITION p0, p1, p2;

The REBUILD PARTITION clause defragments the given partitions:

ALTER TABLE clients
REBUILD PARTITION p0, p1, p2;

The REPAIR PARTITION clause attempts to repair corrupted partitions, similar to the REPAIR TABLE statement for tables:

ALTER TABLE clients
REPAIR PARTITION p0, p1, p2;

See the CREATE TABLE statement explanation for more information about table partitioning.

ALTER TABLE: Table options

ALTER TABLE table 
[TABLESPACE tablespace_name STORAGE DISK] 
   {ENGINE|TYPE} [=] {BDB|HEAP|ISAM|INNODB|MERGE|MRG_MYISAM|MYISAM} | 
AUTO_INCREMENT [=] value | 
AVG_ROW_LENGTH [=] value | 
[DEFAULT] CHARACTER SET character_set |
CHECKSUM [=] {0|1} | 
CONNECTION [=] 'string' | 
COLLATE collation |
COMMENT [=] 'string' | 
DATA DIRECTORY [=] '/path' |
ENGINE [=] engine |
INDEX DIRECTORY [=] '/path' |
INSERT_METHOD [=] {NO|FIRST|LAST } | 
KEY_BLOCK_SIZE [=] value | 
MAX_ROWS [=] value | 
MIN_ROWS [=] value | 
PACK_KEYS [=] {0|1|DEFAULT} |  
DELAY_KEY_WRITE [=] {0|1} | 
ROW_FORMAT [=] {DEFAULT|DYNAMIC|FIXED|COMPRESSED|REDUNDANT|COMPACT} | 
RAID_TYPE = {1|STRIPED|RAID0} | 
UNION [=] (table[,...])

This subsection lists all of the table options that can be set with the ALTER TABLE statement. The options are the same as those that can be specified for CREATE TABLE when a table is first created. (See the description of that statement in this chapter for more information about the options available.) You can give multiple options to ALTER TABLE in a comma-separated list.

To change the starting point for an AUTO_INCREMENT column, enter the following statement:

ALTER TABLE clients
AUTO_INCREMENT = 1000;

This statement sets the value of the primary key column to 1,000 so that the next row inserted will be 1,001. You cannot set it to a value less than the highest data value that already exists for the column.

For large tables, you may want to set the average row length for better table optimization by using the AVG_ROW_LENGTH option. The following example uses the SHOW TABLE STATUS statement to see the average row length for a table similar to the one we want to alter, to get an idea of what the average row length should be:

SHOW TABLE STATUS LIKE 'sales' \G

*************************** 1. row ***************************
           Name: sales
         Engine: MyISAM
        Version: 10
     Row_format: Dynamic
           Rows: 93
 Avg_row_length: 12638
    Data_length: 1175412
Max_data_length: 281474976710655
   Index_length: 706560
      Data_free: 0
 Auto_increment: 113
    Create_time: 2007-05-02 14:27:59
    Update_time: 2007-05-03 13:57:05
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options: 
        Comment: 
        
ALTER TABLE clients 
AVG_ROW_LENGTH = 12638;

In the second SQL statement we’ve set the average row length value of the clients table.

The CHARACTER SET option sets the character set to use for character data in the table. The DEFAULT flag is not required. This option is typically used along with the COLLATE option. These options do not affect columns for which the character set and collation are explicitly specified. Use the SHOW CHARACTER SET and SHOW COLLATION statements to see the character sets and collations available:

ALTER TABLE clients
DEFAULT CHARACTER SET 'latin2'
COLLATE 'latin2_general_ci';

The CHECKSUM option enables or disables a checksum for a table. Set the value to 0 to disable a checksum or 1 to enable checksum. If you upgrade a table that uses a checksum and was created prior to version 4.1 of MySQL, the table may be corrupted in the process. Try using REPAIR TABLE to recalculate the checksum for the table:

ALTER TABLE clients
CHECKSUM = 0;

The COLLATE option sets the collation to use with the data in the table (that is, how the character data is alphabetized). This option is typically used along with the CHARACTER SET option. These options do not affect columns for which the collation and character sets are explicitly specified. Use the SHOW CREATE TABLE statement to see the collation and character set for the table and its columns:

ALTER TABLE clients
COLLATE 'latin2_general_ci'
DEFAULT CHARACTER SET 'latin2';

With the COMMENT option, you can add notes for yourself or other table administrators regarding a table:

ALTER TABLE clients
MAX_ROWS = 1000,
COMMENT = 'This table lists basic information on clients.';

SHOW CREATE TABLE clients \G

*************************** 1. row ***************************
       Table: clients
Create Table: CREATE TABLE 'clients' (
  'client_id' int(11) NOT NULL AUTO_INCREMENT,
  'client_name' varchar(255) DEFAULT NULL, ...
  PRIMARY KEY ('client_id'),
  KEY 'client_index' ('client_name'(10),'city'(5)) USING BTREE
) ENGINE=MyISAM 
AUTO_INCREMENT=1001 
DEFAULT CHARSET=latin1 MAX_ROWS=1000 
COMMENT='This table lists basic information on clients.'

I’ve shortened the results shown here to save space and to focus on the options. SHOW CREATE TABLE is the only method for viewing the table options in MySQL. They will not be shown with DESCRIBE.

The CONNECTION option is provided for tables that use the FEDERATED storage engine. Previously, you would use the COMMENT option to specify this option. The syntax for this option is:

CONNECTION='mysql://username:password@hostname:port/database/tablename'

The password and port are optional.

If you wish to federate an existing table with a remote table, you can alter the table on your system to specify the connection to the remote table like this:

ALTER TABLE clients
CONNECTION='mysql://russell:rover123@santa_clara_svr:9306/federated/clients';

The DATA DIRECTORY option is theoretically used to see the data directory path for the table. However, MySQL currently ignores the option:

ALTER TABLE clients
DATA DIRECTORY = '/data/mysql/clients';

Use the ENGINE option to change the storage engine (formerly known as the table type) for the table given. Be careful using this option as it may cause problems with data. Make a backup of your table and data before using it. As of version 5.1.11 of MySQL, this option cannot be used to change a table to the BLACKHOLE or MERGE storage engines:

ALTER TABLE clients 
ENGINE = INNODB;

This statement changes the storage engine used for the given table to InnoDB. If a table has special requirements that the new engine cannot provide, you’ll receive an error when trying to make this change and the statement will fail. For instance, a MyISAM table that has FULLTEXT indexes could not be changed to InnoDB since it doesn’t support that kind of indexing. Instead, create a new table using the desired storage engine, migrate the data to the new table, and then drop the old table after verifying the integrity of the data.

The INDEX DIRECTORY option is theoretically used to see the directory path for the table indexes. However, MySQL currently ignores the option:

ALTER TABLE clients
INDEX DIRECTORY = '/data/mysql/clients_index';

To insert data into a MERGE table, you will need to specify the insert method it will use. To specify or change this method, use the INSERT_METHOD option with the ALTER TABLE statement. A value of FIRST indicates that the first table should be used; LAST indicates the last table should be used; NO disables inserts:

CREATE TABLE sales_national
(order_id INT, sales_total INT) 
ENGINE = MERGE
UNION = (sales_east, sales_west) 
INSERT_METHOD = LAST;

ALTER TABLE sales_national
INSERT_METHOD = FIRST;

In the first SQL statement here, we create the table sales_national based on two other tables and specify that inserts use the last table in the list of tables given. In the second SQL statement, we change the insert method.

To give the storage engine a hint of the size of index key blocks, use the KEY_BLOCK_SIZE option. Set the value to 0 to instruct the engine to use the default. This option was added in version 5.1.10 of MySQL:

ALTER TABLE clients
KEY_BLOCK_SIZE = 1024;

The MAX_ROWS and MIN_ROWS options are used to set the maximum and minimum rows of a table, respectively. Use the SHOW CREATE TABLE statement to see the results of these options:

ALTER TABLE clients
MIN_ROWS = 100, 
MAX_ROWS = 1000;

For small MyISAM tables in which users primarily read the data and rarely update it, you can use the PACK_KEYS option to pack the indexes. This will make reads faster but updates slower. Set the value of this option to 1 to enable packing and 0 to disable it. A value of DEFAULT instructs the storage engine to pack CHAR or VARCHAR data type columns only:

ALTER TABLE clients
PACK_KEYS = 1;

The DELAY_KEY_WRITE option delays updates of indexes until the table is closed. It’s enabled with a value of 1, disabled with 0:

ALTER TABLE clients
DELAY_KEY_WRITE = 1;

The ROW_FORMAT option instructs the storage engine how to store rows of data. With MyISAM, a value of DYNAMIC (i.e., variable length) or FIXED may be given. If you use the utility myisampack on a MyISAM table, the format will be set to a value of COMPRESSED. You can change a compressed MyISAM to uncompressed by giving a value of REDUNDANT. This is deprecated, though. InnoDB tables use the COMPACT method, but offer a REDUNDANT method to be compatible with a more wasteful format used in older versions of InnoDB:

ALTER TABLE clients
ROW_FORMAT = FIXED;

The RAID_TYPE option is used to specify the type of Redundant Arrays of Independent Disks (RAID) to be used. However, support for RAID has been removed from MySQL as of version 5.0. This SQL statement is also used to permit the options RAID_CHUNKS and RAID_CHUNKSIZE. They have been deprecated, as well.

For MERGE tables in which you want to change the tables that make up the merged table, use the UNION option:

ALTER TABLE sales_national
UNION = (sales_north, sales_south, sales_east, sales_west);

See the CREATE TABLE statement later in this chapter for more information and examples regarding many of the options for the ALTER TABLE statement.

Use this statement to change a view. Views are available as of version 5.0.1 of MySQL.

The statement is used primarily to change the SELECT statement that determines the view, which you can do simply by placing the new SELECT statement for the view after the AS keyword.

Change the column names provided by the view queries by providing the new column names in a comma-separated list within the parentheses following the view’s name. Don’t include either the old SELECT statement or the old column names in the statement.

The ALGORITHM parameter changes algorithmic methods to use for processing a view: the choices are MERGE or TEMPTABLE. TEMPTABLE prevents a view from being updatable.

The DEFINER clause can change the user account considered to be the view’s creator. This clause is available as of version 5.1.2 of MySQL. The same version introduced the related SQL SECURITY clause. It instructs MySQL to authorize access to the view based on the privileges of either the user account of the view’s creator (DEFINER) or the user account of the user who is querying the view (INVOKER). This can help prevent some users from accessing restricted views.

The WITH CHECK OPTION clause can change the restrictions on the updating of a view to only rows in which the WHERE clause of the underlying SELECT statement returns true. For a view that is based on another view, if you include the LOCAL keyword, this restriction will be limited to the view in which it’s given and not the underlying view. If you specify CASCADED instead, underlying views will be considered as well.

Here is an example of this statement’s use:

ALTER VIEW student_directory(ID, Name, Cell_Telephone, Home_Telephone) 
AS SELECT student_id, 
CONCAT(name_first, SPACE(1), name_last), 
phone_dorm, phone_home
FROM students;

If you look at the example for CREATE VIEW later in this chapter, you’ll see that we’re adding an extra column to the view created in that example. The other settings remain unchanged.

You cannot change the name of an existing view. Instead, use the DROP VIEW statement and then create a new view with the CREATE VIEW statement.

This statement creates a new database with the name given. As of version 5.0.2 of MySQL, the keyword DATABASE is synonymous with SCHEMA wherever used in any SQL statement. You can use the IF NOT EXISTS flag to suppress an error message when the statement fails if a database with the same name already exists.

A database name cannot be longer than 64 bytes (not characters) in size. The system uses Unicode (UTF-8), so any character that is part of the UTF-8 character set may be used. The name cannot be the ASCII value of 0 (0x00) or 255 (0xff)—these are reserved. Database names should not include single or double quotation marks or end with a space. If you want a database name to include quotes, though, you will have to enable the SQL mode of ANSI_QUOTES. This can be done with the --sql-mode server option. As of version 5.1.6 of MySQL, database names can contain backslashes, forward slashes, periods, and other characters that may not be permitted in a directory name at the filesystem level. If you use a name that is a reserved word, you must always enclose it in quotes when referring to it.

Special characters in the name are encoded in the filesystem names. If you upgrade your system to a new version of MySQL and you have a database that has special characters in its name, the database will be displayed with a prefix of #mysql50#. For instance, a database named human-resources will be displayed as #mysql50#human-resources. You won’t be able to access this database. Don’t try to change the name from within MySQL, as you may destroy data. Instead, there are a couple of methods you can use. One is to shut down MySQL, go to the MySQL data directory, and rename the subdirectory that contains the database to a name without the unacceptable character (e.g., from human-resources to human_resources) and then restart MySQL. Another method would be to use the mysqlcheck utility, like so:

mysqlcheck --check-upgrade --fix-db-names

The --fix-db-names option was added in version 5.1.7 of MySQL. For more options with this utility, see Chapter 16.

As of version 4.1.1, a db.opt file is added to the filesystem subdirectory created for the database in the MySQL server’s data directory. This file contains a couple of settings for the database. You can specify these settings as options to this SQL statement in a comma-separated list.

Currently, two options are available: CHARACTER SET and COLLATE. Here is an example of how you can create a database with both of these options:

CREATE DATABASE sales_prospects
CHARACTER SET latin1
COLLATE latin1_bin;

There is no equals sign before the value given for each option and no comma between the first and second option. Here are the contents of the db.opt file created for this statement:

default-character-set=latin1
default-collation=latin1_bin

For a list of character sets available on your system, use the SHOW CHARACTER SET statement. For a list of collation possibilities, use the SHOW COLLATION statement. MySQL occasionally adds new character sets and collations to new versions of MySQL. If you need one of the new ones, you’ll have to upgrade your server to the new version.

Use this statement to add an index to a table after it has been created. This is an alias of the clause of the ALTER TABLE statement that adds an index. You can add indexes only to MyISAM, InnoDB, and BDB types of tables. You can also create these tables with indexes, as shown in the CREATE TABLE statement later in this chapter.

To prevent duplicates, add the UNIQUE flag between the CREATE keyword and INDEX. Only columns with CHAR, TEXT, and VARCHAR data types of MyISAM tables can be indexed with FULLTEXT indexes.

Creating UNIQUE indexes

CREATE UNIQUE INDEX index
ON table (column, ...)

After the INDEX keyword, the name of the index or key is given. This name can be the same as one of the columns indexed, or a totally new name.

You can specify the type of index with the USING keyword. For MyISAM and InnoDB tables, BTREE is the default, but RTREE is also available as of version 5.0 of MySQL. The TYPE keyword is an alias for USING.

For wide columns, it may be advantageous to specify a maximum number of characters to use from a column for indexing. This can speed up indexing and reduce the size of index files on the filesystem.

Although there is an ASC option for sorting indexes in ascending order and a DESC option for sorting in descending order, these are for a future release of MySQL. All indexes are currently sorted in ascending order. Additional columns for indexing may be given within the parentheses:

CREATE UNIQUE INDEX client_name
ON clients (client_lastname, client_firstname(4), rec_date);

In this example, an index is created called client_name. It is based on the last names of clients, the first four letters of their first names, and the dates that the records were created. This index is based on it being unlikely that a record would be created on the same day for two people with the same last name and a first name starting with the same four letters.

To see the indexes that have been created for a table, use the SHOW INDEXES statement. To remove an index, use the DROP INDEX statement.

Creating FULLTEXT indexes

CREATE FULLTEXT INDEX index
ON table (column, ...)

After the INDEX keyword, the name of the index or key is given. This name can be the same as one of the columns indexed or a totally new name.

You can specify the type of index with the USING keyword. For MyISAM and InnoDB tables, BTREE is the default, but RTREE is also available as of version 5.0 of MySQL. The TYPE keyword is an alias for USING.

For wide columns, it may be advantageous to specify a maximum number of characters to use from a column for indexing. This can speed up indexing and reduce the size of index files on the filesystem.

Although there is an ASC option for sorting indexes in ascending order and a DESC option for sorting in descending order, these are for a future release of MySQL. All indexes are currently sorted in ascending order. Additional columns for indexing may be given within the parentheses:

CREATE FULLTEXT INDEX client_notes
ON clients (business_description, comments);

In this example, an index is created called client_notes. It is based on two columns, both of which are TEXT columns.

To see the indexes that have been created for a table, use the SHOW INDEXES statement. To remove an index, use the DROP INDEX statement.

Creating SPATIAL indexes

CREATE SPATIAL INDEX index
ON table (column, ...)

SPATIAL indexes can index spatial columns only in MyISAM tables. This is available starting with version 4.1 of MySQL. Here is an example in which first a table and then a spatial index is created:

CREATE TABLE squares 
(square_id INT, square_name VARCHAR(100), 
square_points POLYGON NOT NULL);

CREATE SPATIAL INDEX square_index 
ON squares (square_points);

Notice that when we create the table, we specify that the column square_points is NOT NULL. This is required to be able to index the column. Let’s insert two rows of data:

INSERT INTO squares 
VALUES(1000, 'Red Square', 
(GeomFromText('MULTIPOLYGON(((0 0, 0 3, 3 3, 3 0, 0 0)))')) ),
(1000, 'Green Square', 
(GeomFromText('MULTIPOLYGON(((3 3, 3 5, 5 5, 4 3, 3 3)))')) );

Here we added two squares by giving the five points of the polygon: the starting point (e.g., for the first row, x=0, y=0), the left top point (x=0, y=3), the right top point (x=3, y=3), the right bottom point (x=3, y=0), and the ending point (x=0, y=0) for good measure, which is the same as the starting point. So, the first row contains a square that is 3×3 in size, and the second contains a square that is 2×2 in size. Using the AREA() function we can find the area of each:

SELECT square_name AS 'Square',
AREA(square_points) AS 'Area of Square'
FROM squares;

+--------------+----------------+
| Square       | Area of Square |
+--------------+----------------+
| Red Square   |              9 | 
| Green Square |              3 | 
+--------------+----------------+

If we want to find which square contains a given point on a Cartesian plane (e.g., x=1, y=2), we can use the MBRContains() function like so:

SELECT square_name
FROM squares
WHERE 
MBRContains(square_points, GeomFromText('POINT(1 2)'));

+-------------+
| square_name |
+-------------+
| Red Square  | 
+-------------+

To see how the index we added is involved, we would run an EXPLAIN statement using the same SELECT statement:

EXPLAIN SELECT square_name
FROM squares
WHERE 
MBRContains(square_points, GeomFromText('POINT(1 2)')) \G

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: squares
         type: range
possible_keys: square_index
          key: square_index
      key_len: 32
          ref: NULL
         rows: 1
        Extra: Using where

Notice that the SQL statement is using the square_index spatial index that we created.

This statement is synonymous with CREATE DATABASE. See the description of that statement earlier in this chapter for more information and examples.

Use this SQL statement with the FEDERATED storage engine to set the connection parameters. The values given are stored in the mysql database, in the server table, in a new row. The server name given cannot exceed 63 characters, and it’s not case-sensitive. The only wrapper permitted at this time is mysql. Options are given in a comma-separated list. You’re not required to specify all options listed in the example syntax. If an option is not given, the default will be an empty string. To change options after a server has been created, use the ALTER SERVER statement, described earlier in this chapter. For option values, character or numeric literals (UTF-8; maximum length of 64 characters) must be given. This statement was introduced in version 5.1.15 of MySQL and requires SUPER privileges.

The host may be a hostname or an IP address. The username and password given are those that are required for accessing the server. Provide either the name of the socket or the port to use for connecting to the server. The owner is the filesystem username to use for accessing the server:

CREATE SERVER server1
FOREIGN DATA WRAPPER mysql
OPTIONS (USER 'russell', HOST 'dyerhouse.com', DATABASE 'db1', PORT 3306, 
   OWNER 'root');

CREATE TABLE table1 (col1 INT) 
ENGINE = FEDERATED CONNECTION='server1';

Use this statement to create a new table within a database. This statement has many clauses and options; however, when creating a basic table, you can omit most of them. The TEMPORARY keyword is used to create a temporary table that can be accessed only by the current connection thread and is not accessible by other users. The IF NOT EXISTS flag is used to suppress error messages caused by attempting to create a table by the same name as an existing one. After the table name is given, either the table definition is given (i.e., a list of columns and their data types) along with table options or properties, or a table can be created based on another table. The subsections that follow describe how to:

Here is a simple example of how you can use the CREATE TABLE statement:

CREATE TABLE clients
(client_id INT AUTO_INCREMENT PRIMARY KEY,
client_name VARCHAR(75), 
telephone CHAR(15));

This creates a table with three columns. The first column is called client_id and may contain integers. It will be incremented automatically as records are created. It will also be the primary key field for records, which means that no duplicates are allowed and the rows will be indexed based on this column. The second column, client_name, is a variable-width, character-type column with a maximum width of 75 characters. The third column is called telephone and is a fixed-width, character-type column with a minimum and maximum width of 15 characters. To see the results of this statement, you can use the DESCRIBE statement. There are many column data types. They’re all listed and described in Appendix A.

CREATE TABLE: Column flags

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] table 
(column type[(width)] [ASC|DESC] [NOT NULL|NULL] [DEFAULT value]
   [AUTO_INCREMENT] [[PRIMARY] KEY]|[[UNIQUE] KEY] 
   [COMMENT 'string']
   [REFERENCES table [(column,...)]
      [MATCH FULL|MATCH PARTIAL|MATCH SIMPLE]
      [ON DELETE [RESTRICT|CASCADE|SET NULL|NO ACTION]]
      [ON UPDATE [RESTRICT|CASCADE|SET NULL|NO ACTION]] [,...]
   ]
[,...]) [options]

This is the syntax for the CREATE TABLE statement again, but detailing the column flags portion of the column definition. For some column types, you may need to specify the size of the column within parentheses after the column name and column type.

If a column is indexed, the keyword ASC or DESC may be given next to indicate whether indexes should be stored in ascending or descending order, respectively. By default, they are stored in ascending order. For older versions of MySQL, these flags are ignored. Adding the NOT NULL flag indicates the column may not be NULL. The NULL flag may be given to state that a NULL value is allowed. Some data types are NULL by default. For some, you don’t have a choice whether a column may be NULL or not. To set a default value for a column, you can use the DEFAULT keyword. For some data types (e.g., TIMESTAMP), a default value is not allowed. The AUTO_INCREMENT option tells MySQL to assign a unique identification number automatically to a column. It must be designated as a PRIMARY or UNIQUE key column, and you cannot have more than one AUTO_INCREMENT column in a table. If a column is to be the basis of an index, either PRIMARY KEY, UNIQUE KEY, UNIQUE, or just KEY can be given. Just KEY indicates the column is a primary key.

To document what you’re doing for an administrator or a developer, a comment regarding a column may be given. The results of a SELECT statement won’t show it, but a SHOW FULL COLUMNS statement will reveal it. To add a comment, use the COMMENT keyword followed by a string within quotes. Here is an example using some of the flags and clauses mentioned here:

CREATE TABLE clients
(client_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
client_name VARCHAR(75),
client_city VARCHAR(50) DEFAULT 'New Orleans',
telephone CHAR(15) COMMENT 'Format: ###-###-####');

In this example, the client_id column is a primary key. The NOT NULL option is included for completeness, even though it’s not necessary, because a primary key must be unique and non-NULL. For the client_city column, the DEFAULT clause is used to provide the default value of the column. The default will be used during inserts when no value is given, although you can override the default by specifying an explicit blank value for the column. This statement also includes a comment regarding the typical format for entering telephone numbers in the telephone column. Again, this will be displayed only with the SHOW FULL COLUMNS statement.

For information on the REFERENCES column flag, see the CREATE TABLE: Foreign key references” subsection later in this section.

CREATE TABLE: Index and key definitions

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] table 
(column, ..., index type[(width)] [ASC|DESC] | 
[CONSTRAINT [symbol]] PRIMARY KEY [type] (column,...) 
   [KEY_BLOCK_SIZE value|type|WITH PARSER parser] | 
INDEX|[PRIMARY] KEY [index] [type] (column,...) 
   [KEY_BLOCK_SIZE value|type|WITH PARSER parser] |
[CONSTRAINT [symbol]] UNIQUE [INDEX] [index] [type] (column,...) 
   [KEY_BLOCK_SIZE value|type|WITH PARSER parser] |
[FULLTEXT|SPATIAL] [INDEX] [index] (column,...) 
   [KEY_BLOCK_SIZE value|type|WITH PARSER parser] |
[CONSTRAINT [symbol]] FOREIGN KEY [index] (column,...)
    [reference_definition] | 
CHECK (expression)]
[,...]) [options]

You can use one or more columns for an index, and a table can contain multiple indexes. Indexes can greatly increase the speed of data retrieval from a table. You can define an index involving multiple columns with this statement, or later with the ALTER TABLE statement or the CREATE INDEX statement. With the CREATE TABLE statement, though, indexes can be given after the definition of the columns they index.

A KEY (also called a PRIMARY KEY) is a particular kind of index obeying certain constraints. It must be unique, for instance. It is often combined in MySQL with the AUTO_INCREMENT keyword, and used for identifiers that appear as columns in tables. The general format is to specify the type of index, such as KEY, INDEX, or UNIQUE. This is followed by the index name. Optionally, the index type may be specified with the USING keyword. For most tables, there is only one type of index, so this is unnecessary.

Before version 5 of MySQL, BTREE is the only type for MyISAM tables. Beginning with version 5, the RTREE index type is also available, so you may want to specify the index type. After the index type, one or more columns on which the index is based are listed within parentheses, separated by commas. Before explaining the various possibilities, let’s look at an example:

CREATE TABLE clients
(client_id INT AUTO_INCREMENT KEY,
name_last VARCHAR(50), name_first VARCHAR(50),
telephone CHAR(15),
INDEX names USING BTREE (name_last(5), name_first(5) DESC));

The client_id column here is a PRIMARY KEY, although that clause has been abbreviated to just KEY. This abbreviation is available as of version 4.1 of MySQL. There can be only one PRIMARY KEY but any number of other indexes. The table contains a second index using the first five characters of the two name columns. To specify a combination, the index definition is generally given at the end of the table’s column definitions with the INDEX keyword. The index is named names in the example.

After the index name, the USING clause specifies the type of index to be used. Currently, this is unnecessary because BTREE is the default type for a MyISAM table.

Next, the two columns to index appear within parentheses. The name columns are variable-width columns and 50 characters in length, so to speed up indexing, only the first five characters of each column are used. The name_first column is supposed to be used in descending order per the DESC flag. However, this will be ignored for the current version of MySQL.

The syntax structures for the index clauses listed here vary depending on the type of table index to be created: PRIMARY KEY, INDEX, UNIQUE, FULLTEXT (or BLOB column types), or SPATIAL.

To create constraints on tables based on columns in another table, use the FOREIGN KEY index syntax structures. Foreign keys are used only to link columns in InnoDB tables. The CHECK clause is not used in MySQL but is available for porting to other database systems. Here is an example of how you can use foreign keys to create a table:

CREATE TABLE employees
(emp_id INT NOT NULL PRIMARY KEY,
name_last VARCHAR(25), name_first VARCHAR(25))
TYPE = INNODB;

CREATE TABLE programmers
(prog_id INT, emp_id INT,
INDEX (emp_id),
FOREIGN KEY (emp_id) REFERENCES employees(emp_id)
ON DELETE CASCADE)
TYPE=INNODB;

The first CREATE TABLE statement creates a table of basic employee information. The second CREATE TABLE statement creates a simple table of programmers. In the employees table, the key column emp_id will be used to identify employees and will be the foreign key for the programmers table. The programmers table sets up an index based on emp_id, which will be tied to the emp_id column in the employees table. The FOREIGN KEY clause establishes this connection using the REFERENCES keyword to indicate the employees table and the key column to use in that table. Additionally, the ON DELETE CASCADE clause instructs MySQL to delete the row in the programmers table whenever an employee record for a programmer is deleted from the employees table.

The next subsection, CREATE TABLE: Foreign key references,” gives the syntax for references to foreign keys and the meaning of each component.

At the end of both of these SQL statements, the storage engine is set to InnoDB with the TYPE clause. The ENGINE keyword could be used instead and would have the same effect.

To give the storage engine a hint of the size of index key blocks, use the KEY_BLOCK_SIZE option. Set the value to 0 to instruct the engine to use the default. This option was added in version 5.1.10 of MySQL.

The WITH PARSER clause may be used to give a parser plugin for an index. This is used only with FULLTEXT indexes.

CREATE TABLE: Foreign key references

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] table 
(column, ..., index type[(width)] [ASC|DESC]
[CONSTRAINT [symbol]] FOREIGN KEY [index] (column,...)
REFERENCES table [(column,...)]
   [MATCH FULL|MATCH PARTIAL|MATCH SIMPLE]
   [ON DELETE [RESTRICT|CASCADE|SET NULL|NO ACTION]]
   [ON UPDATE [RESTRICT|CASCADE|SET NULL|NO ACTION]]
[,...]) [options]

This subsection describes the REFERENCES options to the FOREIGN KEY clause, which creates a relationship between an index and another table. This information also applies to the REFERENCES column flag (see the earlier subsection CREATE TABLE: Column flags”).

The MATCH FULL clause requires that the reference match on the full width of each column indexed. In contrast, MATCH PARTIAL allows the use of partial columns. Partial columns can accelerate indexing when the first few characters of a column determine that a row is unique.

The ON DELETE clause instructs MySQL to react to deletions of matching rows from the foreign table according to the option that follows. The ON UPDATE clause causes MySQL to respond to updates made to the referenced table according to the options that follow it. You can use both clauses in the same CREATE TABLE statement.

The RESTRICT keyword option instructs MySQL not to allow the deletion or update (depending on the clause in which it’s used) of the rows in the foreign table if rows in the current table are linked to them. The CASCADE keyword says that when deleting or updating the rows that are referenced in the parent table, delete or update the related rows in the child table accordingly (as in the last example of the previous subsection).

SET NULL causes MySQL to change the data contained in the related columns to a NULL value. For this to work, the column in the child table must allow NULL values. The NO ACTION setting has MySQL not react to deletions or updates with regard to the referencing table.

CREATE TABLE: Table options

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] table 
(column, ..., index type[(width)] [ASC|DESC]
[TABLESPACE tablespace_name STORAGE DISK] 
   {ENGINE|TYPE} [=] {BDB|HEAP|ISAM|INNODB|MERGE|MRG_MYISAM|MYISAM} | 
AUTO_INCREMENT [=] value | 
AVG_ROW_LENGTH [=] value | 
[DEFAULT] CHARACTER SET character_set |
CHECKSUM [=] {0|1} | 
CONNECTION [=] 'string' | 
COLLATE collation |
COMMENT [=] 'string' | 
DATA DIRECTORY [=] '/path' |
DELAY_KEY_WRITE [=] {0|1} | 
ENGINE [=] engine |
INDEX DIRECTORY [=] '/path' |
INSERT_METHOD [=] {NO|FIRST|LAST } | 
KEY_BLOCK_SIZE [=] value | 
MAX_ROWS [=] value | 
MIN_ROWS [=] value | 
PACK_KEYS [=] {0|1|DEFAULT} |  
ROW_FORMAT [=] {DEFAULT|DYNAMIC|FIXED|COMPRESSED|REDUNDANT|COMPACT} | 
RAID_TYPE = {1|STRIPED|RAID0} | 
UNION [=] (table[,...])

This subsection lists all of the table options that can be set with the CREATE TABLE statement. The options are given after the closing parenthesis for the column definitions. To see the values for an existing table, use the SHOW TABLE STATUS statement. To change the values of any options after a table has been created, use the ALTER TABLE statement. Each option is explained in the following paragraphs in alphabetical order, as shown in the preceding syntax. Examples of each are also given.

CREATE TABLE: Partitioning

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] table
PARTITION BY
  [LINEAR] HASH(expression) | 
  [LINEAR] KEY(columns) | 
  RANGE(expression) | 
  LIST(expression)
  [PARTITIONS number]
    [SUBPARTITION BY
      [LINEAR] HASH(expression) | 
      [LINEAR] KEY(columns)
      [SUBPARTITIONS number]
    ]
  [PARTITION partition
        [VALUES {LESS THAN (expression)|MAXVALUE|IN (values)}]
        [[STORAGE] ENGINE [=] engine]
        [COMMENT [=] 'text' ]
        [DATA DIRECTORY [=] '/path']
        [INDEX DIRECTORY [=] '/path']
        [MAX_ROWS [=] number]
        [MIN_ROWS [=] number]
        [TABLESPACE [=] (tablespace)]
        [NODEGROUP [=] value]

        [(SUBPARTITION logical_name
           [[STORAGE] ENGINE [=] engine]
           [COMMENT [=] 'text' ]
           [DATA DIRECTORY [=] '/path']
           [INDEX DIRECTORY [=] '/path']
           [MAX_ROWS [=] number]
           [MIN_ROWS [=] number]
           [TABLESPACE [=] (tablespace)]
           [NODEGROUP [=] value]
        [, SUBPARTITION...])]
  [, PARTITION...]]
  ]

These table partition clauses may be used in CREATE TABLE to create a table using partitions—that is, to organize data into separate files on the filesystem. This capability was added as of version 5.1.6 of MySQL. To add or alter partitions on an existing table, see the ALTER TABLE statement explanation earlier in this chapter. See that section also for comments on partitions in general. This subsection includes several examples of creating a MyISAM table with partitions.

The PARTITION BY clause is required when partitioning in order to explain how data is split and distributed among partitions. A table cannot have more than 1,024 partitions and subpartitions. The subclauses of PARTITION BY are explained in this subsection, whereas the PARTITION and SUBPARTITION clauses are explained in the next two subsections that cover this statement (CREATE TABLE: Partition definitions” and CREATE TABLE: Subpartition definitions”):

See the ALTER TABLE explanation for more information on table partitioning, especially modifying or removing partitioning.

CREATE TABLE: Partition definitions

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] table
    PARTITION partition
        [VALUES {LESS THAN (expression) | MAXVALUE | IN (value_list)}]
        [[STORAGE] ENGINE [=] engine]
        [COMMENT [=] 'string' ]
        [DATA DIRECTORY [=] '/path']
        [INDEX DIRECTORY [=] '/path']
        [MAX_ROWS [=] number]
        [MIN_ROWS [=] number]
        [TABLESPACE [=] (tablespace)]
        [NODEGROUP [=] number]
        [(subpartition_definition[, subpartition_definition] ...)]

The subclauses described in this subsection define general parameters of partitions, such as their sizes and locations in the filesystems:

CREATE TABLE: Subpartition definitions

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] table
    SUBPARTITION partition
        [[STORAGE] ENGINE [=] engine]
        [COMMENT [=] 'string' ]
        [DATA DIRECTORY [=] '/path']
        [INDEX DIRECTORY [=] '/path']
        [MAX_ROWS [=] number]
        [MIN_ROWS [=] number]
        [TABLESPACE [=] (tablespace)]
        [NODEGROUP [=] number]

Only partitions distributed by the RANGE or LIST methods can be subpartitioned. The subpartitions can use only the HASH or KEY methods. The definitions for subpartitions are the same as for partitions, described earlier in the CREATE TABLE: Partitioning” subsection. Here are some examples of subpartitioning:

CREATE TABLE ts (id INT, purchased DATE)
    PARTITION BY RANGE( YEAR(purchased) )
    SUBPARTITION BY HASH( TO_DAYS(purchased) )
    SUBPARTITIONS 2 (
        PARTITION p0 VALUES LESS THAN (1990),
        PARTITION p1 VALUES LESS THAN (2000),
        PARTITION p2 VALUES LESS THAN MAXVALUE
    );

CREATE TABLE sales_figures
(emp_id INT, sales_date DATE, amount INT)
PARTITION BY RANGE(YEAR(sales_date))
SUBPARTITION BY HASH(MONTH(sales_date))
SUBPARTITIONS 4 (
   PARTITION QTR1 VALUES LESS THAN (4),
   PARTITION QTR2 VALUES LESS THAN (7),
   PARTITION QTR3 VALUES LESS THAN (10),
   PARTITION QTR4 VALUES LESS THAN MAXVALUE);

Notice that although the subpartition uses HASH, the subpartitions are specified in ranges of values because it’s a subpartition of a partition that uses the RANGE method.

CREATE TABLE: Based on an existing table

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] table
LIKE table |
[IGNORE|REPLACE] [AS] SELECT...

These two syntaxes for the CREATE TABLE statement allow a new table to be created based on an existing table. With the LIKE clause, a table is created based on the structure of the existing table given. For example, suppose a database has a table called employees that contains information on full-time and part-time employees. Suppose further that it has been decided that information on part-time employees should be stored in a separate table. You could execute the following statement to create a new table for part-time employees with the same structure as the existing employees table:

CREATE TABLE part_time_employees
LIKE employees;

This statement results in a new table with the same structure but without any data. If the table that was copied has a primary key or any indexes, they won’t be copied. You can use the CREATE INDEX statement to create an index. You would first have to do the following to copy the data over:

INSERT INTO part_time_employees 
SELECT * FROM employees 
WHERE part_time = 'Y';

To create a new table based on the structure of an existing table, and to copy some data from the old table to the new one, you can enter something like the following statement:

CREATE TABLE part_time_employees
SELECT *
FROM employees
WHERE part_time = 'Y';

CREATE INDEX emp_id ON part_time_employees(emp_id);

In this example, the table structure is copied and the data is copied for rows where the part_time column has a value of Y, meaning yes. You could follow this statement with a DELETE statement to delete the rows for part-time employees from the employees table. The second SQL statement in this example restores the index on emp_id. However, it doesn’t make the column a primary key or an AUTO_INCREMENT one. For that, you would need to use ALTER TABLE instead.

You can use the IGNORE keyword before the SELECT statement to instruct MySQL to ignore any error messages regarding duplicate rows, to not insert them, or to proceed with the remaining rows of the SELECT statement. Use the REPLACE keyword instead if duplicate rows are to be replaced in the new table.

Use this statement to create a view, which is a preset query stored in a database. In certain situations, a view can be useful for improved security. Views are available as of version 5.0.2 of MySQL.

The contents of a view are based on the SELECT statement given in the AS clause. Users can subsequently issue queries and updates to the view in place of a table; updates ultimately change the data in the tables that underlie the views.

The name of the view cannot be the same as a table in the database, because they share the same tablespace. A view can be based on other views, rather than directly based on a table. To label the column headings for the view’s results set, column names may be given in a comma-separated list in parentheses after the view name. This SQL statement is available as of version 5.0.1 of MySQL.

A few parameters may appear between the CREATE and VIEW keywords. By default, attempts to create a view with the name of an existing view will fail, but the OR REPLACE parameter will overwrite a view with the same name if it exists and will create a new view otherwise. Also by default, the view’s definer (used to determine access rights to the columns of the view) is the user who creates it, but another user can be specified with the DEFINER clause. This clause is available as of version 5.1.2 of MySQL. This version also introduced the related SQL SECURITY clause, which instructs MySQL to authorize access to the view based on the privileges of either the user account of the view’s creator (DEFINER, the default) or the user account of the user who is querying the view (INVOKER). This can help prevent some users from accessing restricted views.

The ALGORITHM parameter selects one of the two types of algorithmic methods to use for processing a view: MERGE or TEMPTABLE. TEMPTABLE prevents a view from being updatable. The default of UNDEFINED leaves the choice to MySQL.

The WITH CHECK OPTION clause restricts updates to rows in which the WHERE clause of the underlying SELECT statement returns true. For a view that is based on another view, if you include the LOCAL keyword, this restriction will be limited to the view in which it’s given and not the underlying view. Conversely, if you use the default choice of CASCADED, the WHERE clauses of underlying views will be considered as well.

If the mysqld server is started with the --updatable_views_with_limit option, updates that contain a LIMIT clause can update views only if the views contain all of the columns that are part of the primary keys of the underlying tables. If set to the default value of 1, only a warning is returned and updates are not restricted.

Here is an example of how you can use this statement:

CREATE DEFINER = 'russell'@'localhost'
SQL SECURITY INVOKER
VIEW student_directory(ID, Name, Telephone) 
AS SELECT student_id, 
CONCAT(name_first, SPACE(1), name_last), phone_home
FROM students;

This SQL statement creates a view that contains each student’s identification number, the student’s first and last name concatenated together with a space between, and the student’s home telephone number. To retrieve this data, enter the following SQL statement:

SELECT * FROM student_directory 
WHERE Name LIKE '%Tears';
+-----------+-------------------+-----------+
| ID        | Name              | Telephone |
+-----------+-------------------+-----------+
| 433342000 | Christina Tears   | 4883831   |  
+-----------+-------------------+-----------+

To save space in the output, the query includes a WHERE clause to retrieve a student with the last name of Tears. Notice that the column names are the ones named by the CREATE VIEW statement, not the underlying tables on which the view is based. This view will be available for all users who have SELECT privileges for the database in which it was created.

By default, a view is created in the default database at the time that the CREATE VIEW statement is entered. To create a view in a different database, simply add the database name and a dot as a separator in front of the view name in the CREATE VIEW statement.

To delete a view from a database, use the DROP VIEW statement. To see a list of existing views for the current database, run SHOW FULL TABLES WHERE Table_type='VIEW';.

This statement displays information about the columns of a given table. The DESCRIBE keyword can be abbreviated to DESC:

DESCRIBE workreq;

+--------------------+---------+------+-----+---------+----------------+
| Field              | Type    | Null | Key | Default | Extra          |
+--------------------+---------+------+-----+---------+----------------+
| req_id             | int(11) | NO   | PRI | NULL    | auto_increment | 
| client_id          | int(11) | YES  |     | NULL    |                | 
| client_description | text    | YES  | MUL | NULL    |                | 
| technician_notes   | text    | YES  |     | NULL    |                | 
+--------------------+---------+------+-----+---------+----------------+

For information on a specific column, supply only the column name. For information on multiple columns but not all columns, you can supply a name pattern within quotes and use the wildcard characters % and _. Quotes around the string aren’t necessary unless the string contains spaces.

To list the columns in the workreq table that have names beginning with the characters client, enter the following:

DESCRIBE workreq 'client%';

+--------------------+---------+------+-----+---------+-------+
| Field              | Type    | Null | Key | Default | Extra |
+--------------------+---------+------+-----+---------+-------+
| client_id          | int(11) | YES  |     | NULL    |       | 
| client_description | text    | YES  | MUL | NULL    |       | 
+--------------------+---------+------+-----+---------+-------+

Notice that the keyword LIKE is not used. The fields in the results have the following meanings:

To understand how the options you use when creating or altering a table affect the output of DESCRIBE, let’s look at the schema of the table shown in an earlier example:

SHOW CREATE TABLE workreq \G

*************************** 1. row ***************************
       Table: workreq
Create Table: CREATE TABLE 'workreq' (
  'req_id' int(11) NOT NULL AUTO_INCREMENT,
  'client_id' int(11) DEFAULT NULL,
  'client_description' text,
  'technician_notes' text,
  PRIMARY KEY ('req_id'),
  FULLTEXT KEY 'notes_index' ('client_description','technician_notes')
) ENGINE=MyISAM DEFAULT CHARSET=latin1

The results of this SHOW CREATE TABLE statement indicate that client_description is part of the index called notes_index. The other column that is part of that index is technician_notes. Notice in the results of the earlier DESCRIBE statement that only the first column of the index is marked MUL.

Use this statement to delete a given database along with all of its tables and data. The addition of the IF EXISTS flag suppresses an error message if the database does not already exist. You must have the DROP privilege for the database to be able to delete it. Here is an example of this statement’s use:

DROP DATABASE IF EXISTS test;
Query OK, 6 rows affected (0.42 sec)

The number of tables deleted is returned in the rows affected count. If the database doesn’t exist or if there are other files in the database’s filesystem directory, an error message will be displayed. The tables will be deleted if other files exist, but the foreign file and the directory for the database won’t be removed. They will have to be deleted manually at the command line using a filesystem command such as rm in Unix or del in Windows. Here’s an example in which a foreign file is found in the database directory when dropping a database:

DROP DATABASE IF EXISTS test;

ERROR 1010 (HY000): 
Error dropping database (can't rmdir './test/', errno: 17)

SHOW TABLES FROM test;
Empty set (0.00 sec)

SHOW DATABASES LIKE 'test';

+-----------------+
| Database (test) |
+-----------------+
| test            | 
+-----------------+

In this example, we attempt to drop the database, but we are unsuccessful because of a foreign file located in the database’s directory at the filesystem level. The tables are all dropped as indicated from the results of the SHOW TABLES statement, but the database remains. After manually deleting the foreign file, we run the DROP DATABASE statement again:

DROP DATABASE IF EXISTS test;
Query OK, 0 rows affected (0.43 sec)

DROP DATABASE test;

ERROR 1008 (HY000): Can't drop database 'test'; 
database doesn't exist

This time the statement is successful, as indicated by our extra attempt without the IF EXISTS flag. No tables are dropped by the second attempt because they are all deleted on the first attempt, so the number of rows affected is 0.

If a database is dropped, any user privileges specific to the database (e.g., privileges listed in the db table of the mysql database) are not automatically deleted. Therefore, if a database is later created with the same name, those user privileges will apply to the new database, which is a potential security risk.

This statement deletes a given index from a table. It’s synonymous with ALTER TABLE...DROP INDEX.... See the section on ALTER TABLE: DROP index clauses” under ALTER TABLE earlier in this chapter for more details and options for dropping indexes from a table.

To determine the name of a particular index, we’ll use the SHOW INDEXES statement:

SHOW INDEXES FROM clients \G

*************************** 1. row ***************************
       Table: clients
  Non_unique: 0
    Key_name: PRIMARY
Seq_in_index: 1
 Column_name: client_id
   Collation: A
 Cardinality: 0
    Sub_part: NULL
      Packed: NULL
        Null: 
  Index_type: BTREE
     Comment: 
*************************** 2. row ***************************
       Table: clients
  Non_unique: 1
    Key_name: client_index
Seq_in_index: 1
 Column_name: client_name
   Collation: A
 Cardinality: NULL
    Sub_part: 10
      Packed: NULL
        Null: YES
  Index_type: BTREE
     Comment: 
*************************** 3. row ***************************
       Table: clients
  Non_unique: 1
    Key_name: client_index
Seq_in_index: 2
 Column_name: client_city
   Collation: A
 Cardinality: NULL
    Sub_part: 5
      Packed: NULL
        Null: YES
  Index_type: BTREE
     Comment:

The preceding results show three rows, but there are really only two indexes: the primary key based on the client_id column and the client_index, which is based on the client_name and the client_city columns combined. To delete client_index, use the DROP INDEX statement:

DROP INDEX client_index ON clients;

Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

The client_index index is successfully dropped from the clients table. If you run SHOW INDEXES again, the results will list only the primary key.

This statement can be used with the FEDERATED storage engine to delete a given server that is created with CREATE SERVER. The IF EXISTS flag may be given to prevent an error from being generated if the server does not exist. Any tables created with a CONNECTION to a FEDERATED server will not be dropped or altered as a result of this statement. See the CREATE SERVER statement explanation earlier in this chapter for more information on this topic. This statement was introduced in version 5.1.15 of MySQL and requires SUPER privileges:

DROP SERVER server1;

Use this statement to delete a table from a database, including its data. You can delete multiple tables in the same statement by naming them in a comma-separated list. If some tables given exist and other don’t, the ones that exist will be deleted and an error message will be generated for the nonexistent ones. The addition of the IF EXISTS flag prevents the error message from being displayed if a table doesn’t exist. Instead, a NOTE is generated and not displayed, but can be retrieved with the SHOW WARNINGS statement. If the TEMPORARY flag is given, only temporary tables matching the table names given will be deleted. This statement will cause a commit of the current transaction, except when the TEMPORARY flag is used.

The DROP privilege is required for this statement. This privilege isn’t checked when the TEMPORARY flag is used because the statement will apply only to temporary tables, and they are visible and usable only by the user of the current session who created them.

The RESTRICT and CASCADE flags are for future versions and are related to compatibility with other systems.

If a table is dropped, any specific user privileges for the table (e.g., privileges listed in the tables_priv table of the mysql database) are not automatically deleted. Therefore, if a table is later created with the same name, those user privileges will apply to the new table, a potential security risk:

DROP TABLE IF EXISTS repairs, clientss_old;
Query OK, 0 rows affected (0.00 sec)

SHOW WARNINGS;

+-------+------+------------------------------+
| Level | Code | Message                      |
+-------+------+------------------------------+
| Note  | 1051 | Unknown table 'clientss_old' |
+-------+------+------------------------------+

In this example, we try to delete both the repairs and the clients_old tables, but we misspell clients_old. Because the IF EXISTS flag is included, the statement doesn’t return an error message. Starting with version 4.1 of MySQL, a note is created that can be retrieved using the SHOW WARNINGS statement, as shown in this example. Notice that the number of tables deleted is not returned, although the repairs table is deleted.

This statement deletes a view. Multiple views may be given in a comma-separated list. The IF EXISTS flag prevents error messages if one of the specified views doesn’t exist. Instead, a note will be generated, which can be displayed afterward by executing the SHOW WARNINGS statement. Any other views given with the statements that do exist will be dropped.

The RESTRICT or CASCADE options are reserved for a future release of MySQL. This statement is available as of version 5.0.1 of MySQL and requires DROP privilege for the view being deleted.

Use this statement to rename a given database to a new name, given after the TO keyword. While a database is being renamed, no other client can interact with the database involved. Tables that are currently locked or tables that are part of a transaction in progress cannot be renamed. Additional databases may be renamed in the same statement, given in a comma-separated list. This statement was added in version 5.1.7 of MySQL. As of version 5.0.2 of MySQL, the keyword DATABASE is synonymous with SCHEMA:

RENAME DATABASE personnel TO human_resources,
applicants TO human_resources_applicants;

In this example, the name of the database called personnel is changed to human_resources, and applicants is changed to human_resources_applicants, to coincide with a renaming of the department to which they relate. All of the tables and data are the same and continue to exist in the directories with the new database names.

Use this statement to rename a table to a new name, given after the TO keyword. Multiple tables may be specified in a comma-separated list, following the format old_name TO new_name. Multiple renames are performed left to right, and if any errors are encountered, all of the table name changes are reversed from right to left. While tables are being renamed, no other client can interact with the tables involved. Tables that are currently locked or tables that are part of a transaction in progress cannot be renamed.

Tables can be renamed and moved to databases on the same filesystem. If a trigger is associated with a table that is renamed and moved to a new database, the trigger will fail when used. You won’t be warned of this possibility when renaming the table.

You can use this statement to rename a view, but you cannot use it to move the view to a different database.

This statement requires ALTER and DROP privileges for the table being renamed. CREATE and INSERT privileges are needed for the new table and database if the table is being moved.

As an example, suppose that users add data to a particular table during the course of the day, and each day the contents of the table are to be preserved. Suppose further that you want to reset the table to contain no data. Here’s one way you might do that:

CREATE TABLE survey_new LIKE survey;

RENAME TABLE survey TO survey_bak,
survey_new TO survey;

In this example, a new table called survey_new is created based on the table structure of the old table called survey, but without the data. In the second SQL statement, the old table is renamed to survey_bak and the blank table, survey_new, is renamed to survey. If issued from an API program, the name of the backup copy could be generated based on the date (e.g., survey_2008dec07) so that each day’s data could be preserved. As mentioned earlier, you can also change the database of a table in the process:

CREATE TABLE survey_new LIKE survey;

RENAME TABLE survey TO backup.survey_2008dec07,
survey_new TO survey;

In this example, the old table is renamed and moved into a database called backup.

This statement will show all of the character sets installed on the server. To be more selective, use a pattern with the LIKE clause and the wildcard characters (i.e., % and _). Or you may use the WHERE clause to refine the results set. For instance, to list all of the character sets beginning with the name latin, enter the following:

SHOW CHARACTER SET LIKE 'latin%'\G
*************************** 1. row ***************************
          Charset: latin1
      Description: ISO 8859-1 West European
Default collation: latin1_swedish_ci
           Maxlen: 1
*************************** 2. row ***************************
          Charset: latin2
      Description: ISO 8859-2 Central European
Default collation: latin2_general_ci
           Maxlen: 1
*************************** 3. row ***************************
          Charset: latin5
      Description: ISO 8859-9 Turkish
Default collation: latin5_turkish_ci
           Maxlen: 1
*************************** 4. row ***************************
          Charset: latin7
      Description: ISO 8859-13 Baltic
Default collation: latin7_general_ci
           Maxlen: 1

To see the default character set, use the SHOW VARIABLES statement. To change the client’s character set, use the SET CHARACTER SET statement. The Default collation field in the results indicates the related collation for the character set. The Maxlen field gives the maximum number of bytes for storing one character of the character set. For European character sets, this value is usually 1; for Asian character sets, it’s usually more than 1.

Use this statement to list all of the collation character sets. You can use the LIKE clause and the wildcard characters (% and _) to list character sets based on a naming pattern. Or you may use the WHERE clause to refine the results set. This statement is available as of version 4.1 of MySQL. Here is an example:

SHOW COLLATION LIKE '%greek%';

+------------------+---------+----+---------+----------+---------+
| Collation        | Charset | Id | Default | Compiled | Sortlen |
+------------------+---------+----+---------+----------+---------+
| greek_general_ci | greek   | 25 | Yes     | Yes      |       1 | 
| greek_bin        | greek   | 70 |         | Yes      |       1 | 
+------------------+---------+----+---------+----------+---------+

In this example, character sets that contain the letters greek in their name are listed. These are Greek character sets. Under the Charset column is shown the character set for which the collation relates. Both are for the greek character set. Using SHOW CHARACTER SET, we can see information on this character set. Looking at the Default just shown (and the Default collation shown next), we can see that greek_general_ci is the default collation for the character set greek. This is indicated with the Yes value. The field Compiled in the results shown previously indicates that the character set was compiled in the MySQL server. The field Sortlen indicates the bytes needed when collating data:

SHOW CHARACTER SET LIKE 'greek';

+---------+------------------+-------------------+--------+
| Charset | Description      | Default collation | Maxlen |
+---------+------------------+-------------------+--------+
| greek   | ISO 8859-7 Greek | greek_general_ci  |      1 | 
+---------+------------------+-------------------+--------+

Use this statement to display the columns for a given table. If the table is not in the current default database, the FROM database clause may be given to specify another database. You can use the LIKE clause to list only columns that match a naming pattern given in quotes. Or you may use the WHERE clause to refine the results set. The FULL flag will return the name of the character set used for collating and the user privileges of the current session for the columns returned:

SHOW COLUMNS FROM clients FROM workrequests LIKE 'client%';

+-------------+-------------+------+-----+---------+-------+
| Field       | Type        | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| client_id   | varchar(4)  |      | PRI |         |       |
| client_name | varchar(50) | YES  |     | NULL    |       |
+-------------+-------------+------+-----+---------+-------+

In this example, only information for columns beginning with the name client is retrieved. The following example is just for the client_id column and uses the FULL flag along with the alternate display method (\G):

SHOW FULL COLUMNS FROM clients FROM workrequests
LIKE 'client_id'\G
*************************** 1. row ***************************
     Field: client_id
      Type: varchar(4)
 Collation: latin1_swedish_ci
      Null:
       Key: PRI
   Default:
     Extra:
Privileges: select,insert,update,references
   Comment:

Notice that the name of the collation used for the column (latin1_swedish_ci) and the user’s privileges (SELECT, INSERT, UPDATE, and REFERENCES) with regard to the column are provided.

This statement displays an SQL statement that can be used to create a database like the one given. This statement is mostly useful for determining the default character set. It’s available as of version 4.1 of MySQL. As of version 5.0.2, the keyword SCHEMA may be used instead of DATABASE:

SHOW CREATE DATABASE human_resources \G

*************************** 1. row ***************************
       Database: human_resources
Create Database: CREATE DATABASE `human_resources`
                 /*!40100 DEFAULT CHARACTER SET latin1 */

If you don’t want the database name in the results to be quoted with backticks as shown here, you can set the server variable SQL_QUOTE_SHOW_CREATE to 0 instead of its default value of 1.

This statement displays an SQL statement that can be used to create a table like the one named. The results may be copied and used with another database. You can also copy the results and modify the name of the table in order to use the CREATE statement on the same database. If you want a table exactly like an existing one, you might do better to use CREATE TABLE...LIKE... instead:

SHOW CREATE TABLE programmers \G

*************************** 1. row ***************************
Table: programmers
Create Table: CREATE TABLE 'programmers' (
              'prog_id' varchar(4) NOT NULL default '',
              'prog_name' varchar(50) NOT NULL default '',
              PRIMARY KEY ('prog_id')
              ) ENGINE=MyISAM DEFAULT CHARSET=latin1

Notice that the results include the table type and other default options.

As with the SHOW CREATE DATABASE statement, if you don’t want the table name in the results to be quoted with backticks as shown here, you can set the server variable SQL_QUOTE_SHOW_CREATE to 0 instead of its default value of 1.

Use this statement to display an SQL statement that can be used to create a view like the one named. The results may be copied and used with another database. You can also copy the results and modify the name of the view so that the statement may be used to create a similar or identical view on the same database. This statement is available as of version 5.0.1 of MySQL:

SHOW CREATE VIEW student_directory \G

*************************** 1. row ***************************
       View: student_directory
Create View: CREATE ALGORITHM=UNDEFINED 
DEFINER='russell'@'localhost' SQL SECURITY INVOKER 
VIEW 'student_directory'
AS SELECT 'students'.'student_id' AS 'ID',
CONCAT('students'.'name_first',
convert(repeat(_utf8' ',1) using latin1),
'students'.'name_last') AS 'Name',
'students'.'phone_home' AS 'Telephone' 
FROM 'students'

This view is the same one created in the example given for the CREATE VIEW statement earlier. Notice that the database name (personnel) has been added to the end of the view name (employee_directory).

This statement displays the list of databases on the server. The keyword DATABASE is synonymous with SCHEMA as of version 5.0.2 of MySQL. Using the LIKE clause, a naming pattern may be given. Or you may use the WHERE clause to refine the results set.

For example, suppose that a server has a separate database for each customer of the organization and that the pattern for the names of the databases is cust_number, where the number is the customer account number. You could enter the following SQL statement to obtain a list of databases based on this pattern:

SHOW DATABASES LIKE 'cust%' LIMIT 1;

+------------------+
| Database (cust%) |
+------------------+
| cust_37881       | 
+------------------+

The SHOW DATABASES privilege is necessary to see all databases. Otherwise, the user will see only the databases for which he has privileges. The --skip-show-database server option will disable this limitation.

The mysqlshow utility can be used at the command line to view the same information:

mysqlshow --user=user --password

The results from this utility are also limited by the user’s privileges.

This SQL statement displays information about the indexes for a given table. A table from a different database can be specified either by preceding the table name with the database name and a dot (e.g., database.table) or by adding the FROM clause. The INDEXES keyword may be replaced with INDEX or KEYS—all three are synonymous:

SHOW INDEXES FROM contacts FROM sales_dept \G

*************************** 1. row ***************************
       Table: contacts
  Non_unique: 0
    Key_name: PRIMARY
Seq_in_index: 1
 Column_name: contact_id
   Collation: A
 Cardinality: 265
    Sub_part: NULL
      Packed: NULL
        Null: 
  Index_type: BTREE
     Comment: 
*************************** 2. row ***************************
       Table: contacts
  Non_unique: 0
    Key_name: contact_name
Seq_in_index: 1
 Column_name: name_last
   Collation: A
 Cardinality: NULL
    Sub_part: 10
      Packed: NULL
        Null: YES
  Index_type: BTREE
     Comment: 
*************************** 3. row ***************************
       Table: contacts
  Non_unique: 0
    Key_name: contact_name
Seq_in_index: 2
 Column_name: name_first
   Collation: A
 Cardinality: NULL
    Sub_part: 10
      Packed: NULL
        Null: YES
  Index_type: BTREE
     Comment: 

Looking at these results, we can see that for each index the table name is given. This is followed by a field indicating whether the index is nonunique. A unique index is indicated by 0, a nonunique index by 1. The name of the index or key (i.e., PRIMARY or contact_name in the example) is shown next. For indexes that use only one column, the key name and the column name are often the same. For indexes that use more than one column, a row will be listed for each column, each row having the same table name and the same key name (i.e., name_last and name_first for contact_name).

The output gives the sequence of the columns in the index, where 1 is the first column. The name of the column (or columns) indexed is next, followed by the collation (how the column is sorted in the index). A value of A means ascending and D means descending. If the index is not sorted, the Collation field value is NULL.

The Cardinality field is based on the number of unique indexes contained in the column. The server consults this information to determine whether to use an index in a join. The higher the cardinality, the more likely it will be used.

The Sub_part field indicates the number of characters of the column that are indexed for partially indexed columns. This field is NULL if the NULL column is indexed.

The Packed field indicates how the key is packed. If the key is not packed, the field has a value of NULL. See the earlier subsection ALTER TABLE: Table options” for a description of packed keys.

If the column may contain a NULL value, the Null field reads Yes; otherwise, it’s empty. Index_type is the structure of the index, which can be BTREE, HASH, FULLTEXT, RTREE (as of version 5.0.1 of MySQL), or SPATIAL. The Comments field contains any comments associated with the index.

From the command line, the mysqlshow utility with the --keys option can be used to show the same information:

mysqlshow --user=user --password --keys database table

This statement is synonymous with SHOW DATABASES. See the description of that statement earlier in this chapter for more information and examples.

This statement displays status information on a set of tables from a database. To obtain the status of tables from a database other than the current default one, use the FROM clause. The results include information on all of the tables of the database, unless the LIKE clause is used to limit the tables displayed by a naming pattern:

SHOW TABLE STATUS FROM human_resources 
LIKE 'employees' \G

*************************** 1. row ***************************
           Name: employees
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 122
 Avg_row_length: 16384
    Data_length: 1094812
Max_data_length: 281474976710655
   Index_length: 2048
      Data_free: 0
 Auto_increment: 1145
    Create_time: 2006-08-14 21:31:36
    Update_time: 2007-03-30 07:02:17
     Check_time: 2006-08-14 21:31:36
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options: max_rows=1000
        Comment: InnoDB free: 4096 kB

In this example, the number of tables is limited to one because a specific table name is given in the LIKE clause without the % wildcard. You can change some of these variables or table options using the ALTER TABLE statement; see the ALTER TABLE: Table options” subsection earlier in this chapter.

In the results of this statement, the name of the table is shown first, followed by a description of the table. The Engine field lists the type of storage engine used. The Version field gives the version number from the table’s .frm file. Row_format can be Compact, Compressed, Dynamic, Fixed, or Redundant, unless it’s an InnoDB table, in which case the possibilities are Compact or Redundant. The Rows field shows the number of rows of data contained in the table. Except for MyISAM tables, this number usually isn’t accurate. The Avg_row_length field gives the average length of the rows in bytes. The Data_length field gives the size of the datafile in bytes. This is the same size shown at the filesystem level for the .MYD file. Max_data_length gives the maximum size allowed for the datafile of the table. Index_length is the size of the index file, the .MYI file. Data_free is the space that has been allocated for the datafile that is not in use at the moment; this is typically 0. The value of the Auto_increment field is the value of the column that uses AUTO_INCREMENT for the next row to be created. Create_time is the date and time the table was created; Update_time shows the time the table was last updated; and Check_time is the last date and time that the table was checked. This isn’t always accurate. Collation names the collation used for sorting the table’s data. Checksum provides the checksum value if there is one, NULL if not. The Create_options field lists any options, and the Comment field shows any comments that were given when the table was created or altered. For InnoDB tables, the free space is given under Comment.

From the command line, the utility mysqlshow with the --keys option can be used to show the indexes of a table:

mysqlshow --user=user --password --status database table

This statement displays a list of tables and views (as of version 5.0.1 of MySQL). To distinguish between tables and views, add the FULL keyword. In the results, an extra column called Table_type will be displayed. A value of BASE TABLE indicates a table, and VIEW indicates a view. The tables shown will not include temporary tables and will be from the current database by default. To list tables from another database, add the FROM clause along with the name of the database. You can reduce the list of tables to those with a name meeting a given naming pattern with either the LIKE or the WHERE clause. For a list of all tables for all databases that are currently being used by queries, add the OPEN flag instead:

SHOW TABLES FROM workrequests LIKE 'work%';

This statement will list all of the tables and views with names that begins with the word “work” for the database workrequests. By default, only tables for which the user has privileges will be listed.

From the command line, the utility mysqlshow with the --keys option can be used to show the tables contained in a database:

mysqlshow --user=user --password database

There is no SHOW VIEWS statement at this time. To see a list of existing views for the current database, run SHOW FULL TABLES WHERE Table_type='VIEW';.

Use this statement to start a transaction. Transaction statements are currently supported by the InnoDB, NDB Cluster, and BDB storage engines and are ignored if used with MyISAM tables. The WORK keyword is optional. Don’t confuse the BEGIN statement with the BEGIN...END compound statement used in stored procedures and triggers (see Chapter 9). To eliminate confusion on this point, it’s recommended you use the alias START TRANSACTION instead of BEGIN.

A transaction is permanently recorded when the session issues a COMMIT statement, starts another transaction, or terminates the connection. You can reverse a transaction by issuing a ROLLBACK statement if the transaction has not yet been committed. See the explanations of COMMIT and ROLLBACK later in this chapter for more information on transactions. The SAVEPOINT and ROLLBACK TO SAVEPOINT statements may also be useful.

Here is an example of the BEGIN statement’s use in context:

BEGIN;

INSERT DATA INFILE '/tmp/customer_orders.sql'
INTO TABLE orders;

COMMIT;

In this example, if there is a problem after the batch of orders is inserted into the orders table, the ROLLBACK statement could be issued instead of the COMMIT statement shown here. ROLLBACK would remove the data imported by the INSERT DATA INFILE statement.

Use this statement to commit transactions, which are SQL statements that have changed data and have been entered into MySQL but are not yet saved. Transaction statements are currently supported by the InnoDB, NDB Cluster, and BDB storage engines and are ignored if used with MyISAM tables.

If AUTOCOMMIT is enabled, it must be disabled for this statement to be meaningful. You can disable it explicitly with the statement:

SET AUTOCOMMIT = 0;

Normally, AUTOCOMMIT is disabled by a START TRANSACTION statement and reinstated with the COMMIT statement.

The WORK keyword is optional and has no effect on the results. It’s available for compatibility with its counterpart, BEGIN WORK. Use the AND CHAIN clause to complete one transaction and start another, thus making it unnecessary to use START TRANSACTION again. Use the AND RELEASE clause to end the current client session after completing the transaction.

Add the keyword NO to indicate explicitly that a new transaction is not to begin (when used with CHAIN) or that the client session is not to end (when used with RELEASE). This is necessary only when the system variable completion_type is set so that the server assumes that a COMMIT statement indicates the start of another transaction or releases a session.

Here is a basic example of this statement:

START TRANSACTION;

LOCK TABLES orders WRITE;

INSERT DATA INFILE '/tmp/customer_orders.sql'
INTO TABLE orders;

SELECT ...;

COMMIT;

UNLOCK TABLES;

In this example, after inserting a batch of orders into the orders table, an administrator enters a series of SELECT statements to check the integrity of the data. They are omitted here to save space. If there is a problem, the ROLLBACK statement could be issued rather than the COMMIT statement shown here. ROLLBACK would remove the data imported by the INSERT DATA INFILE statement.

The following statements also cause a transaction to be committed: ALTER EVENT, ALTER FUNCTION, ALTER PROCEDURE, ALTER TABLE, BEGIN, CREATE DATABASE, CREATE EVENT, CREATE FUNCTION, CREATE INDEX, CREATE PROCEDURE, CREATE TABLE, DROP DATABASE, DROP EVENT, DROP FUNCTION, DROP INDEX, DROP PROCEDURE, DROP TABLE, LOAD DATA INFILE, LOCK TABLES, RENAME TABLE, SET AUTOCOMMIT=1, START TRANSACTION, TRUNCATE, and UNLOCK TABLES.

Use this statement to delete rows of data from a given table. Three basic syntax structures are allowed. The first one shown here is restricted to a single table, whereas the other two can handle multiple tables. For all three, the LOW_PRIORITY keyword instructs the server to wait until there are no queries on the table named before deleting rows. This keyword works only with storage engines that allow table-level locking (i.e., MyISAM, MEMORY, MERGE). The QUICK keyword can be used with MyISAM tables to make deletions faster by not merging leaves in the index’s tree. The IGNORE keyword instructs MySQL to continue even if it encounters errors. You can retrieve error messages afterward with the SHOW WARNINGS statement.

Use the WHERE clause to specify which rows are to be deleted based on a given condition. You can use the DELETE statement in conjunction with the JOIN clause, which is explained later in this chapter.

Here is a simple example of this statement:

DELETE LOW_PRIORITY FROM workreq
WHERE client_id = '1076'
AND status <> 'DONE';

In this example, the client 1076 has closed its account, and management has decided just to delete all of its incomplete work requests. If a WHERE clause is not given, all of the rows for the table would be deleted permanently.

If you want to delete all of the data in a table, you can use this statement without the WHERE clause, but it’s slow because deletions are performed one row at a time. The same result can be obtained faster with the TRUNCATE statement. However, the TRUNCATE statement doesn’t return the number of rows deleted, so use DELETE if that’s important to you.

To delete only a certain number of rows in a table, use the LIMIT clause to specify the number of rows to delete. To delete a specific number of rows for a particular range of column values, use the ORDER BY clause along with the LIMIT clause. For example, suppose an account executive informs the database administrator that the last four work requests she entered for a particular client (1023) need to be deleted. The database administrator could enter the following to delete those rows:

DELETE FROM workreq
WHERE client_id = '1023'
ORDER BY request_date DESC
LIMIT 4;

In this example, the rows are first ordered by the date of the work request, in descending order (latest date first). Additional columns may be given in a comma-separated list for the ordering. The LIMIT clause is used to limit the number of deletions to the first four rows of the results of the WHERE clause and the ORDER BY clause.

The second syntax for this statement allows other tables to be referenced. In the first example shown here, the database administrator wants to delete rows representing a particular client from the work request table, but she doesn’t know the client account number. However, she knows the client’s name begins with Cole, so she could enter the following to delete the records:

DELETE workreq FROM workreq, clients
WHERE workreq.client_id = clients.client_id
AND client_name LIKE 'Cole%';

In this example, the table in which rows will be deleted is given after the DELETE keyword. It’s also given in the list of tables in the FROM clause, which specifies the table from which information will be obtained to determine the rows to delete. The two tables are joined in the WHERE clause on the client identification number column in each. Using the LIKE keyword, the selection of rows is limited to clients with a name beginning with Cole. Incidentally, if more than one client has a name beginning with Cole, the rows for all will be deleted from the work request table. You can delete rows in more than one table with a single statement by listing the tables in a comma-separated list after the DELETE keyword. For example, suppose that we decide to delete not only the work requests for the client, but also the row for the client in the clients table:

DELETE workreq, clients FROM workreq, clients
WHERE workreq.clientid = clients.clientid
AND client_name LIKE 'Cole%';

Notice that the only syntactical difference between this statement and the one in the previous example is that this statement lists both tables for which rows are to be deleted after the DELETE keyword and before the FROM clause. Deletions are permanent, so take care which tables you list for deletion.

The third syntax operates in the same way as the second one, but it offers a couple of keywords that may be preferred for clarity. If the previous example were entered with this third syntax, it would look like this:

DELETE FROM workreq USING workreq, clients
WHERE workreq.clientid = clients.clientid
AND client_name LIKE 'Cole%';

Notice that the table from which rows will be deleted is listed in the FROM clause. The tables that the statement will search for information to determine which rows to delete are listed in the USING clause. The results of statements using this syntax structure and those using the previous one are the same. It’s just a matter of style preference and compatibility with other database systems.

Although MySQL will eventually reuse space allocated for deleted rows, you can compact a table that has had many rows deleted by using the OPTIMIZE TABLE statement or the myisamchk utility.

This statement suppresses the display of an expression’s results. Multiple expressions may be given in a comma-separated list. As of version 4.1 of MySQL, subqueries may be given. Here is an example:

DO  (SET @company = 'Van de Lay Industries' );

This statement creates the @company variable with the value given, but without displaying any results.

Use this statement to display information about the columns of a given table or the handling of a SELECT statement. For the first usage, the statement is synonymous with the DESCRIBE and SHOW COLUMNS statements. For the latter usage, EXPLAIN shows which index the statement will use and, when multiple tables are queried, the order in which the tables are used. This can be helpful in determining the cause of a slow query. Here is an example involving a simple subquery in which we are retrieving a list of our top clients and counting the number of work request tickets they’ve generated, and then querying those results to order them by the number of tickets:

EXPLAIN
SELECT * FROM
   (SELECT client_name, COUNT(*) AS tickets
    FROM work_req 
    JOIN clients USING(client_id) 
    WHERE client_type = 1 
    AND DATEDIFF(NOW(), request_date) < 91
    GROUP BY client_id) AS derived1
ORDER BY tickets DESC;

*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: <derived2>
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 8
        Extra: Using filesort
*************************** 2. row ***************************
           id: 2
  select_type: DERIVED
        table: clients
         type: ALL
possible_keys: PRIMARY
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 94
        Extra: Using where; Using temporary; Using filesort
*************************** 3. row ***************************
           id: 2
  select_type: DERIVED
        table: work_req
         type: ref
possible_keys: client_id,workreq_date_key
          key: workreq_date_key
      key_len: 5
          ref: company_database.clients.client_id
         rows: 1
        Extra: Using where; Using index

We can discern plenty from these results, such as which indexes were used, if any. For example, the possible_keys field in the third row lists the indexes that might have been used to find the data, whereas the key field indicates that the index workreq_date_key was actually used. (That index covers the client_id and request_date columns.) If the possible_keys field showed a value of NULL, then no index was used or could have been used. This would indicate that you should consider adding an index to the table.

Basically, this statement tells you what MySQL does when it executes the given SQL statement. It doesn’t tell you what to do differently to improve performance. For that, you will need to use your judgment. See Table 6-1 for a list of possible select_types.

A handle provides direct access to a table, as opposed to working from a results set. Handles can be faster than SELECT statements when reading large numbers of rows from a table. MyISAM and InnoDB tables currently support handlers.

A handle is usable only by the session (connection thread) that established it. The table is still accessible by other sessions, though, and is not locked by this statement. Because of this, and because the method provides direct table access, the data in the table can change and even be incomplete as the handler performs successive reads.

Create a handler by issuing a HANDLER statement with the OPEN clause to establish a handle for the table, much like a file handle in a programming language such as Perl. The AS clause and handle name are optional. If an alias is not given, the table name is used as the handler name for subsequent HANDLER statements.

You can then use HANDLER statement formats with READ clauses to read data from a table. Finish by issuing HANDLER with a CLOSE clause.

Here are a couple of basic examples of the HANDLER statement:

HANDLER clients OPEN AS clients_handle;
HANDLER clients_handle READ FIRST;

The first line creates the table handle called clients_handle, based on the clients table. The next SQL statement retrieves the first row of data from the table. The result of this statement is the same as running a SELECT to retrieve all columns of the table and then picking off the first row in the results set. To continue retrieving results in the same way as a results set from a SELECT, issue the following:

HANDLER clients_handle READ NEXT;

Every time the statement is run with the NEXT keyword, the pointer is advanced and the next row in the table is displayed until the end of the table is reached. To retrieve more than one row, you can use the LIMIT clause like this:

HANDLER clients_handle READ NEXT LIMIT 3;

This statement displays the next three rows from the table.

The WHERE clause may be used with a HANDLER...READ statement in the same way as with the SELECT statement. Here is an example:

HANDLER clients_handle READ FIRST
WHERE state = 'MA' LIMIT 5;

This statement displays the first five rows in which the client is located in the state of Massachusetts. Note that no ORDER BY clause is available for HANDLER...READ statements. Therefore, the first five rows are based on the order in which they are stored in the table.

To extract data based on an index, use one of the READ clauses that specify indexes. Here is an example like the previous one, but with the addition of an index:

HANDLER clients_handle READ cid PREV
WHERE state = 'MA' LIMIT 2;

This example retrieves two rows matching the condition of the WHERE clause; the rows come from the previous batch of rows displayed thanks to the PREV keyword. Performance could benefit from the use of the cid index, if it was based on the state column. To retrieve the next set of rows using this syntax, replace PREV with NEXT.

The LAST keyword searches for and retrieves rows starting from the last row of the table. Here is another example using an index:

HANDLER clients_handle READ name = ('NeumeyerGera');

The name index is a combination of the name_last and the name_first column, but only the first four characters of the first name are used by the index. Given the sample database used for this book, this statement displays the row for the client Gerard Neumeyer. The values for each column may be separated with commas (e.g., 'Neumeyer', 'Gera'), or spliced together as shown. This feature, a condition for a multicolumn index, would be a difficult contortion with a SELECT statement.

You can use this statement to access built-in documentation. Enter HELP alone to display a list of MySQL commands for which you may display documentation. Typing HELP contents displays a table of contents for this internal documentation. For quick reference, you can also give an SQL statement or clause:

HELP SELECT;

This displays the syntax for the SELECT statement along with a brief description of some of the clauses. Similarly, entering HELP SHOW gives you a list of SQL statements beginning with SHOW.

Use this statement to add rows of data to a table. The first format shown can insert only one row of data per statement. The second format can handle one or more rows in a single statement. The columns and their order are specified once, but values for multiple rows may be given. Each row of values is to be contained in its own set of parentheses, separated by commas. The third format inserts columns copied from rows in other tables. Explanations of the specifics of each type of statement, their various clauses and keywords, and examples of their uses follow in the next three subsections of this SQL statement.

A few parameters are common to two formats, and a few are common to all formats.

You can use the LOW_PRIORITY keyword to instruct the server to wait until all other queries related to the table in which data is to be added are finished before running the INSERT statement. When the table is free, it is locked for the INSERT statement and will prevent concurrent inserts.

The DELAYED keyword is available for the first two syntaxes and indicates the same priority status, but it releases the client so that other queries may be run and so that the connection may be terminated. A DELAYED query that returns without an error message does not guarantee that the inserts will take place; it confirms only that the query is received by the server to be processed. If the server crashes, the data additions may not be executed when the server restarts and the user won’t be informed of the failure. To confirm a DELAYED insert, the user must check the table later for the inserted content with a SELECT statement. The DELAYED option works only with MyISAM and InnoDB tables. It’s also not applicable when the ON DUPLICATE KEY UPDATE clause is used.

Use the HIGH_PRIORITY keyword to override a --low-priority-updates server option and to disable concurrent inserts.

The IGNORE keyword instructs the server to ignore any errors encountered and suppress the error messages. In addition, for multiple row insertions, the statement continues to insert rows after encountering errors on previous rows. Warnings are generated that the user can display with the SHOW WARNINGS statement.

The INTO keyword is optional and only for compatibility with other database engines.

The DEFAULT keyword can be given for a column for the first two syntax formats to instruct the server to use the default value for the column. You can set the default value either with the CREATE TABLE statement when the table is created or with the ALTER TABLE statement for existing tables.

The ON DUPLICATE KEY UPDATE clause tells an INSERT statement how to handle an insert when an index in the table already contains a specified value in a column. With this clause, the statement updates the data in the existing row to reflect the new values in the given columns. Without this clause, the statement generates an error. An example appears in the next section.

Single-row insertion with the SET clause

INSERT [LOW_PRIORITY|DELAYED|HIGH_PRIORITY] [IGNORE]
  [INTO] table
  SET column={expression|DEFAULT}, ...
  [ON DUPLICATE KEY UPDATE column=expression, ...]

This variant of the INSERT statement allows only one row of data to be inserted into a table per SQL statement. The SET clause lists one or more column names, each followed by the value to which it is to be set. The value given can be a static value or an expression. Here is an example:

INSERT INTO clients
SET client_name =  'Geoffrey & Company',
city = 'Boston', state = 'MA';

This example lists three columns along with the values to be set in a row entry in the clients table. Other columns in the newly inserted row will be handled in a default manner. For instance, an AUTO_INCREMENT column will be set to the next number in sequence.

As mentioned earlier, the ON DUPLICATE KEY UPDATE clause allows an INSERT statement to handle rows that already contain specified values. Here is an example:

CREATE UNIQUE INDEX client_phone
ON clients(client_name,telephone);

ALTER TABLE clients 
ADD COLUMN new_telephone TINYINT(1) 
AFTER telephone;

INSERT INTO clients
SET client_name = 'Marie & Associates',
new_telephone = 0
telephone = '504-486-1234'
ON DUPLICATE KEY UPDATE
new_client = 1;

This example starts by creating an index on the client_phone column in the clients table. The index type is UNIQUE, which means that duplicate values for the combination of client_name and telephone columns are not allowed. With the second SQL statement, we add a column to flag new telephone numbers for existing clients. The INSERT statement tries to insert the specified client name and telephone number. But it indicates that if there is already a row in the table for the client, a new row is not to be added. Instead, the existing row is to be updated per the UPDATE clause, setting the original entry’s telephone column to the value given in the SET clause. The assumption is that the new data being inserted either is for a new client or is an update to the existing client’s telephone number. Instead of using a column value after the equals sign, a literal value or an expression may be given.

Multiple-row insertions

INSERT [LOW_PRIORITY|DELAYED|HIGH_PRIORITY] [IGNORE]
  [INTO] table [(column,...)]
  VALUES ({expression|DEFAULT},...), (...)
  [ON DUPLICATE KEY UPDATE column=expression,...]

This format of the INSERT statement allows one SQL statement to insert multiple rows. The columns in which data is to be inserted may be given in parentheses in a comma-separated list. If no columns are specified, the statement must include a value for each column in each row, in the order that they appear in the table. In the place reserved for an AUTO_INCREMENT column, specify NULL and the server will insert the correct next value in the column. To specify default values for other columns, use the DEFAULT keyword. NULL may also be given for any other column that permits NULL and that you wish to leave NULL. The VALUES clause lists the values of each row to be inserted into the table. The values for each row are enclosed in parentheses; each row is separated by a comma. Here is an example:

INSERT INTO clients (client_name, telephone)
VALUES('Marie & Associates', '504-486-1234'),
('Geoffrey & Company', '617-522-1234'),
('Kenneth & Partners', '617-523-1234');

In this example, three rows are inserted into the clients table with one SQL statement. Although the table has several columns, only two columns are inserted for each row here. The other columns are set to their default value or to NULL. The order of the values for each row corresponds to the order that the columns are listed.

Normally, if a multiple INSERT statement is entered and one of the rows to be inserted is a duplicate, an error is triggered and an error message is displayed. The statement is terminated and no rows are inserted. The IGNORE keyword, however, instructs the server to ignore any errors encountered, suppress the error messages, and insert only the non-duplicate rows. The results of this statement display like so:

Query OK, 120 rows affected (4.20 sec)
Records: 125  Duplicates: 5  Warnings: 0

These results indicate that 125 records were to be inserted, but only 120 rows were affected or successfully inserted. There were five duplicates in the SQL statement, but there were no warnings because of the IGNORE keyword. Entering the SHOW WARNINGS statement will display the suppressed warning messages.

Inserting rows based on a SELECT

INSERT [LOW_PRIORITY|HIGH_PRIORITY] [IGNORE]
  [INTO] table [(column,...)]
  SELECT...
  [ON DUPLICATE KEY UPDATE column=expression,...]

This method of the INSERT statement allows for multiple rows to be inserted in one SQL statement, based on data retrieved from another table by way of a SELECT statement. If no columns are listed (i.e., an asterisk is given instead), the SELECT will return the values of all columns in the order in which they are in the selected table and will be inserted (if possible without error) in the same order in the table designated for inserting data into. If you don’t want to retrieve all of the columns of the selected table, or if the columns in both tables are not the same, then you must list the columns to retrieve in the SELECT statement and provide a matching ordered list of the columns of the table that data is to be inserted into.

For the following example, suppose that the employees table contains a column called softball to indicate whether an employee is a member of the company’s softball team. Suppose further that it is decided that a new table should be created to store information about members of the softball team and that the team’s captain will have privileges to this new table (softball_team), but no other tables. The employee names and telephone numbers need to be copied into the new table because the team’s captain will not be allowed to do a query on the employees table to extract that information. Here are the SQL statements to set up the new table with its initial data:

CREATE TABLE softball_team
(player_id INT KEY, player_name VARCHAR(50),  
 position VARCHAR(20), telephone CHAR(8));

INSERT INTO softball_team
(player_id, player_name, telephone)
  SELECT emp_id, CONCAT(name_first, ' ', name_last),
  RIGHT(telephone_home, 8)
  FROM employees
  WHERE softball = 'Y';

The first SQL statement creates the new table. The columns are very simple: one column as a row identifier, one column for both the first and last names of the player, another for the player’s home telephone number, and yet another for the player’s position, to be filled in later by the team’s captain. Normally, we wouldn’t include a column like the one for the player’s name because that would be duplicating data in two tables. However, the team captain intends to change many of the player’s names to softball nicknames (e.g., Slugger Johnson).

In the second SQL statement, the INSERT statement uses an embedded SELECT statement to retrieve data from the employees table where the softball column for the row is set to 'Y'. The CONCAT() function is used to put together the first and last names, separated by a space. This will go into the name column in the new table. The RIGHT() function is used to extract only the last eight characters of the telephone_home column because all of the employees on the softball team are from the same telephone dialing area. See Chapter 11 for more information on these functions. Notice that we’ve listed the three columns that data is to go into, although there are four in the table. Also notice that the SELECT statement has three columns of the same data types but with different names.

The JOIN clause is common to several SQL statements (SELECT, UPDATE, DELETE) and is complex; therefore, it is listed here as its own entry in the chapter. Use JOIN to link tables together based on columns with common data for purposes of selecting, updating, or deleting data. The JOIN clause is entered at the place in the relevant statement that specifies the tables to be referenced. This precludes the need to join the tables based on key columns in the WHERE clause.

The ON keyword is used to indicate the pair of columns by which the tables are to be joined (indicated with the equals sign operator). As an alternative method, the USING keyword may be given along with a comma-separated list of columns both tables have in common, contained within parentheses. The columns must exist in each table that is joined. To improve performance, you can also provide index hints to MySQL (see the last subsection of this clause definition, Index hints”).

Here is an example of a JOIN:

SELECT CONCAT(name_first, SPACE(1), name_last) AS Name
FROM employees
JOIN branches ON employees.branch_id = branches.branch_id
WHERE location = 'New Orleans';

This statement displays a list of employees from the employees table who are located in the New Orleans branch office. The problem solved by the JOIN is that the employees table doesn’t indicate New Orleans by name as the branch; that table just has a numeric identifier. The branches table is used to retrieve the branch name for the WHERE clause. The location column is a column in the branches table. Nothing is actually displayed from the branches table here. Since the record identification column for branches is branch_id in both tables, the USING keyword can be used instead of ON to create the same join:

SELECT CONCAT(name_first, SPACE(1), name_last) AS Name
FROM employees
JOIN branches USING (branch_id)
WHERE location = 'New Orleans';

This will join the two tables on the branch_id column in each table. Since these tables have only one column in common, it’s not necessary to specify that row; instead, you can use the NATURAL keyword. Here is the same statement with this change:

SELECT CONCAT(name_first, SPACE(1), name_last) AS Name
FROM employees
NATURAL JOIN branches
WHERE location = 'New Orleans';

Notice that the USING keyword and the column for linking are omitted. MySQL will assume that branch_id in both columns are the same and will naturally join the tables on them. The results of this SQL statement will be the same as those of the previous two.

When joining two tables in a simple join, as shown in the previous examples, if no rows in the second table match rows from the first table, no row will be displayed for the unmatched data. For example, if the branches table lists a branch office for which there are no employees listed in the employees table belonging to that branch, the results set would not show a row for that supposedly empty branch office. Sometimes, though, it can be useful to display a record regardless. In our example, this would tell us that something’s wrong with the data: either one or more employees are marked with the wrong branch_id, or some employee records are missing from the employees table. Conversely, if an employee has a branch_id value that does not exist in the branches table, we would want to see it in the results so that we can correct the data.

To list a row for each employee including stray ones, the LEFT keyword may be given in front of the JOIN keyword to indicate that records from the first table listed on the left are to be displayed regardless of whether there is a matching row in the table on the right:

SELECT CONCAT(name_first, SPACE(1), name_last) AS Name,
location AS Branch
FROM employees
LEFT JOIN branches USING (branch_id)
ORDER BY location;

This SQL statement lists a row for each employee along with the employee’s location. If a row for an employee has either a NULL value for the branch_id, or a branch number that is not in the branches table, the employee name will still be displayed but with the branch name reading as NULL. Again, this can be useful for spotting errors or inconsistencies in the data between related tables.

In contrast to LEFT JOIN, the RIGHT JOIN clause includes all matching entries from the table on the right even if there are no matches from the table on the left. Here is an example using a RIGHT JOIN:

SELECT CONCAT(name_first, SPACE(1), name_last) AS Name,
location AS Branch
FROM employees
RIGHT JOIN branches USING (branch_id)
ORDER BY location;

This example displays branches for which there are no matching employee records. For both the LEFT and RIGHT JOIN methods, the OUTER keyword is optional and has no effect on the results. It’s just a matter of preference and compatibility with other database engines.

The JOIN clause has a few other options. The STRAIGHT_JOIN keyword explicitly instructs MySQL to read the tables as listed, from left to right. The keywords INNER and CROSS have no effect on the results, as of recent versions of MySQL. They cannot be used in conjunction with the keywords LEFT, RIGHT, or NATURAL. The syntax starting with the OJ keyword is provided for compatibility with Open Database Connectivity (ODBC).

You can use the AS keyword to introduce aliases for tables. Several examples of aliasing are provided earlier in the explanation of this clause.

Index hints

SELECT...|UPDATE...|DELETE...    
table...JOIN table
USE {INDEX|KEY} [{FOR {JOIN|ORDER BY|GROUP BY}] ([index[,...]]) |
FORCE {INDEX|KEY} [{FOR {JOIN|ORDER BY|GROUP BY}] (index[,...]) |
IGNORE {INDEX|KEY} [{FOR {JOIN|ORDER BY|GROUP BY}] (index[,...])

When MySQL joins and searches tables, indexes can be used to increase the speed of the SQL statements. Use the EXPLAIN statement to analyze a joined SQL statement to see which indexes are being used and in which order, as well as whether there are other indexes available that aren’t being used in the join. MySQL may not always choose the best index available. To hint to MySQL which index it should check first, and perhaps which index to ignore, or even to force it to use a particular index, you can employ index hints.

To tell MySQL to use a particular index, add the USE INDEX clause to the JOIN along with the names of the indexes in a comma-separated list, within parentheses. To present an example of this method, let’s start with a JOIN statement that may execute in a suboptimal manner:

SELECT client_name, COUNT(*) AS tickets
FROM work_req 
JOIN clients USING(client_id) 
WHERE client_type = 1 
AND DATEDIFF(NOW(), request_date) < 91
GROUP BY client_id

This statement retrieves a list of support clients and a count of the number of support tickets that they have created in the last 90 days. It gets the count of tickets from work_req and the client name from the clients table. To tweak the performance of the statement, let’s examine the indexes for the work_req table:

SHOW INDEXES FROM work_req \G

*************************** 1. row ***************************
       Table: work_req
  Non_unique: 0
    Key_name: PRIMARY
Seq_in_index: 1
 Column_name: wr_id
   Collation: A
 Cardinality: 115
    Sub_part: NULL
      Packed: NULL
        Null: 
  Index_type: BTREE
     Comment: 
*************************** 2. row ***************************
       Table: work_req
  Non_unique: 1
    Key_name: workreq_date_key
Seq_in_index: 1
 Column_name: wr_id
   Collation: A
 Cardinality: 217337
    Sub_part: NULL
      Packed: NULL
        Null: YES
  Index_type: BTREE
     Comment: 
*************************** 3. row ***************************
       Table: work_req
  Non_unique: 1
    Key_name: workreq_date_key
Seq_in_index: 2
 Column_name: request_date
   Collation: A
 Cardinality: 217337
    Sub_part: NULL
      Packed: NULL
        Null: 
  Index_type: BTREE
     Comment:

The results show us that the table work_req has two indexes: a primary one based on the wr_id (see row 1) and a second one called workreq_date_key (see the Key_name field in rows 2 and 3) based on wr_id and request_date together. To suggest to MySQL in our JOIN statement that this second index should be used, enter the statement like so:

SELECT client_name, COUNT(*) AS tickets
FROM work_req 
JOIN clients 
USE INDEX FOR JOIN (workreq_date_key) 
USING(client_id) 
WHERE client_type = 1 
AND DATEDIFF(NOW(), request_date) < 91
GROUP BY client_id;

The FORCE INDEX option instructs MySQL to attempt to limit its search to the specified index; others, however, will be used if the requested columns make it necessary:

SELECT client_name, COUNT(*) AS tickets
FROM work_req 
JOIN clients 
FORCE INDEX FOR JOIN (workreq_date_key)
USING(client_id) 
WHERE client_type = 1 
AND DATEDIFF(NOW(), request_date) < 91
GROUP BY client_id;

To instruct MySQL not to use certain indexes, list them with the IGNORE INDEX option in the same manner:

SELECT client_name, COUNT(*) AS tickets
FROM work_req 
JOIN clients 
IGNORE INDEX FOR JOIN (workreq_date_key)
USING(client_id) 
WHERE client_type = 1 
AND DATEDIFF(NOW(), request_date) < 91
GROUP BY client_id;

It’s also permitted to use combinations of these three index hint clauses, separated only by a space.

Use the LIMIT clause to limit the number of rows the server will process to satisfy the given SQL statement. For the SELECT statement, it limits the number of rows returned in the results set. In an UPDATE statement, it limits the number of rows changed. With the DELETE statement, it limits the number of rows deleted. The DELETE statement permits only the first syntax shown, whereas the other statements allow all three.

The LIMIT clause accepts only literal values, not expressions or variables. Nor will it accept a negative value. The most straightforward method of limiting the number of rows is to specify the maximum row count to be displayed, like this:

SELECT * FROM employees
LIMIT 5;

To begin listing rows after a specific number of records, an offset may be given, where the offset for the first row is 0. Two syntaxes accomplish this. One gives the amount of the offset, followed by a comma and then the maximum count of rows to display. The other specifies the count followed by the OFFSET keyword, followed by the amount of the offset. Here is an example of the first structure, which is preferred:

SELECT * FROM employees
LIMIT 10, 5;

In this example, after the 10th record is reached, the next 5 records will be returned—in other words, results 11 through 15 are returned. The offset and count for the LIMIT clause are based on the rows in the results set, not necessarily on the rows in the tables. So the amount of the offset is related to the order of the rows retrieved from the tables based on clauses, such as the WHERE clause and the ORDER BY clause.

You can use this statement to import organized data from a text file into a table in MySQL. The file can be either on the server or on the client.

For a file on the server, if you use a bare filename (such as input.txt) or a relative path (such as ../), the file is found relative to the directory of the database into which the data is to be imported. If the file is not located in the directory’s database, the file permissions must be set so it can be read for all filesystem users.

For a file on the client, the LOCAL keyword must be given. This feature must be enabled on both the client and the server by using the startup option of --local-infile=1. See Chapter 15 for more information on server and client settings.

If a data text file contains rows of data duplicating some of the rows in the table into which it’s being imported, an error will occur and the import may end without importing the remaining data. Duplicate rows are those that have the same values for key columns or other unique columns. To instruct the server to ignore any errors encountered and to continue loading other rows, use the IGNORE keyword. Use the SHOW WARNINGS statement to retrieve the error messages that would have been displayed. To instruct the server to replace any duplicate rows with the ones being imported, use the REPLACE keyword. This will completely replace the values of all columns in the row, even when the new record contains no data for a column and the existing one does.

Here is a basic example of LOAD DATA INFILE:

LOAD DATA INFILE '/tmp/catalog.txt'
INTO TABLE catalog
FIELDS TERMINATED BY '|'
LINES TERMINATED BY '\n';

In this example, the file to be loaded is in the /tmp directory and is called catalog.txt. The data contained in the file is to be inserted into the catalog table in the current database in use. Each field in the text file is terminated with a vertical bar character. The rows of data in the text file are on separate lines. They are separated by a newline character (\n). This is the default for a Unix text file. For DOS or Windows systems, lines are usually terminated with \n\r, signifying a newline and a Return character. If the rows start with a special character, you can identify that character with the LINES STARTED BY clause.

This statement also offers the ENCLOSED BY clause to specify a character that can start and terminate a field, such as a quotation mark. You can use the OPTIONALLY keyword to indicate that the character is used for enclosing columns containing string data, but optional for numeric data. Numeric fields may then include or omit the given character. For example, if the optional character is an apostrophe (single quote), a numeric value for a field may be given as '1234' or 1234, so MySQL should expect and accept both.

The ESCAPED BY clause indicates the character used in the input file to escape special characters. The backslash (\) is the default value.

Some data text files contain one or more lines of column headings that should not be imported. To omit these initial lines from the import, use the IGNORE count LINES clause, where count is the number of lines to ignore.

For some data text files, the fields of data are not in the same order as the columns of the receiving table. Sometimes there are fewer fields in the text file than in the table. For both of these situations, to change the order and number of columns, add a list of columns and their order in the text file to the end of the statement within parentheses. Here is an example of such a scenario:

LOAD DATA LOW_PRIORITY INFILE '/tmp/catalog.txt' IGNORE
INTO TABLE catalog
FIELDS TERMINATED BY '|'
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(cat_id, description, price);

The first line of the text file contains column headings describing the data, but that line will not be imported because of the IGNORE 1 LINES clause here. The catalog table has several more columns than the three that are being imported, and they are in a different order. Finally, because this import is not critical, the LOW_PRIORITY keyword near the beginning of the statement instructs the server to handle other queries on the catalog table before running this statement. If this was replaced with CONCURRENT, the import would be performed even if other clients were querying the same table.

As of version 5.0.3 of MySQL, the list of fields can contain column names and user variables. Also, SET may be added to set or change the value to be imported. Here is an example:

LOAD DATA LOW_PRIORITY INFILE '/tmp/catalog.txt' IGNORE
INTO TABLE catalog
FIELDS TERMINATED BY '|'
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(cat_id, @discarded, description, @mfg_price)
SET price = @mfg_price * .9;

In this example, the table receiving the data has five columns. The second one is to be ignored and stored in a discarded user variable. The third column is the price. Since the company sells the manufacturer’s products at ten percent less than the manufacturer’s suggested retail price, the statement receives the raw value in the user variable @mfg_price and then we use SET to adjust that value for the column when loaded.

This statement instructs the server to release a savepoint named earlier with the SAVEPOINT statement for the current transaction. The statement does not commit the transaction, nor does it roll back the transaction to the savepoint. Instead, it merely eliminates the savepoint as a possible rollback point. See the SAVEPOINT statement for more information. Here is an example of RELEASE SAVEPOINT:

START TRANSACTION;

LOCK TABLES orders WRITE;

INSERT DATA INFILE '/tmp/customer_info.sql'
INTO TABLE orders;

SAVEPOINT orders_import;

INSERT DATA INFILE '/tmp/customer_orders.sql'
INTO TABLE orders;

SAVEPOINT orders_import1;

INSERT DATA INFILE '/tmp/customer_orders1.sql'
INTO TABLE orders;

SELECT...

RELEASE SAVEPOINT orders_import1;

In this example, the database administrator imports a customer information file and two files containing customer orders and sets up two savepoints. After running a few SELECT statements (not fully shown here), he decides that the results of the second batch of orders look all right and he releases the savepoint for that batch. He hasn’t yet decided if the first batch was imported properly. If he decides that there was a problem, he can still roll back all of the orders imported, but he can no longer roll back just the second batch.

Use this statement to insert new rows of data and to replace existing rows where the PRIMARY KEY or UNIQUE index key is the same as the new record being inserted. This statement requires INSERT and DELETE privileges because it is potentially a combination of both.

The LOW_PRIORITY keyword instructs the server to wait until there are no queries on the table named, including reads, and then to lock the table for exclusive use by the thread so that data may be inserted and replaced. When the statement is finished, the lock is released, automatically. For busy servers, a client may be waiting for quite a while. The DELAYED keyword will free the client by storing the statement in a buffer for processing when the table is not busy. The client won’t be given notice of the success of the statement, just that it’s buffered. If the server crashes before the changes to the data are processed, the client will not be informed and the buffer contents will be lost. The INTO keyword is optional and is a matter of style preference and compatibility with other database engines.

The REPLACE statement has three basic formats. The first contains the values for each row in parentheses after the VALUES keyword. If the order and number of values do not match the columns of the table named, the columns have to be listed in parentheses after the table name in the order in which the values are arranged. Here is an example of the REPLACE statement using this syntax:

REPLACE INTO workreq (wr_id, client_id, description)
VALUES(5768,1000,'Network Access Problem'),
(5770,1000,'Network Access Problem');

Notice that this statement is able to insert two rows without the column names being listed twice. In this example, the first row already exists before this statement is to be executed. Once it’s run, the row represented by the work request identifier 5768 is completely replaced with this data. Columns that are not included in the list of columns here are reset to their default values or to NULL, depending on the column.

The second syntax does not allow multiple rows. Instead of grouping the column names in one part of the statement and the values in another part, column names and values are given in a column=value pair. To enter the REPLACE statement from the preceding example in this format, you would have to enter the following two statements:

REPLACE INTO workreq
SET wr_id = 5768, client_id = 1000,
description = 'Network Access Problem';

REPLACE INTO workreq
SET wr_id = 5770, client_id = 1000,
description = 'Network Access Problem';

The third syntax involves a subquery, which is available as of version 4.1 of MySQL. With a subquery, data can be retrieved from another table and inserted into the table referenced in the main query for the statement. Here is an example:

REPLACE INTO workreq (wr_id, client_id, status)
SELECT wr_id, client_id, 'HOLD'
FROM wk_schedule
WHERE programmer_id = 1000;

Work requests assigned to a particular programmer are being changed to a temporarily on-hold status. The values for two of the columns are taken from the work schedule table, and the fixed string of HOLD is inserted as the value of the third column. Currently, the table for which replacement data is being inserted cannot be used in the subquery.

Use this statement with transactional tables to reverse transactions that have not yet been committed. Transaction statements are currently supported by the InnoDB, NDB Cluster, and BDB storage engines and are ignored if used with MyISAM tables.

If AUTOCOMMIT is enabled, it must be disabled for this statement to be meaningful, which can be done as follows:

SET AUTOCOMMIT = 0;

AUTOCOMMIT is also disabled when a transaction is started with the START TRANSACTION statement. It is reinstated with the execution of the COMMIT statement, the ending of the current session, and several other statements that imply that a commit is desired. See the explanation of COMMIT earlier in this chapter for a list of statements that imply a commit.

The WORK keyword is optional and has no effect on the results. It’s available for compatibility with its counterparts, BEGIN WORK and COMMIT WORK. Use the AND CHAIN clause to indicate that the transaction is to be rolled back and another is starting, thus making it unnecessary to execute the START TRANSACTION statement again. Use the AND RELEASE clause to end the current client session after rolling back the transaction. Add the keyword NO to indicate explicitly that a new transaction is not to begin (when used with CHAIN) or the client session is not to end (when used with RELEASE)—these are the default settings, though. It’s necessary to specify NO only when the system variable completion_type is set to something other than the default setting.

Here is an example of this statement’s use in context:

START TRANSACTION;

LOCK TABLES orders WRITE;

INSERT DATA INFILE '/tmp/customer_orders.sql'
INTO TABLE orders;

SELECT ...;

ROLLBACK;

UNLOCK TABLES;

In this example, after the batch of orders is inserted into the orders table, the administrator manually enters a series of SELECT statements (not shown) to check the integrity of the data. If everything seems all right, the COMMIT statement would be issued to commit the transactions, instead of the ROLLBACK statement shown here. In this case, a problem leads the administrator to issue ROLLBACK to remove the data imported by the INSERT DATA INFILE statement.

A rollback will not undo the creation or deletion of databases. It also cannot be performed on changes to table schema (e.g., ALTER TABLE, CREATE TABLE, or DROP TABLE statements). Transactions cannot be reversed with the ROLLBACK statement if they have been committed. Commits are caused by the COMMIT statement as well as several other implicit commit statements. See the explanation of COMMIT for a list of statements that imply a commit.

This statement instructs the server to reverse SQL statements for the current transaction back to a point marked in the transaction by the SAVEPOINT statement. Any transactions for the session made after the savepoint are undone. This is in contrast to ROLLBACK by itself, which undoes all changes since the start of the transaction. Transaction statements are currently supported by the InnoDB, NDB Cluster, and BDB storage engines and are ignored if used with MyISAM tables. Multiple savepoints may be set up during a transaction. Here is an example:

START TRANSACTION;

LOCK TABLES orders WRITE;

INSERT DATA INFILE '/tmp/customer_info.sql'
INTO TABLE orders;

SAVEPOINT orders_import;

INSERT DATA INFILE '/tmp/customer_orders.sql'
INTO TABLE orders;

SELECT...

SAVEPOINT orders_import1;

INSERT DATA INFILE '/tmp/customer_orders1.sql'
INTO TABLE orders;

SELECT...

ROLLBACK TO SAVEPOINT orders_import1;

In this example, the database administrator has imported a customer information file and two files containing customer orders and has set up two savepoints. After running a few SELECT statements (not fully shown here), he decides that there was a problem loading the second batch of orders, so he rolls back the transaction to the savepoint, eliminating the data that was imported from the customer_orders1.sql file. If he wants, he can still roll back all of the orders imported, as well as the whole transaction. When he’s finished, he can commit the transactions by executing the COMMIT statement. See that statement earlier in this chapter for more information on committing transactions explicitly and implicitly.

Use this statement to identify a point in a transaction to which SQL statements may potentially be undone later. It’s used in conjunction with the ROLLBACK TO SAVEPOINT statement. It may be released with the RELEASE SAVEPOINT statement. You can use any unreserved word to identify a savepoint and can create several savepoints during a transaction. If an additional SAVEPOINT statement is issued with the same name, the previous point will be replaced with the new point for the name given. Here is an example:

START TRANSACTION;
LOCK TABLES orders WRITE;
INSERT DATA INFILE '/tmp/customer_info.sql'
INTO TABLE orders;
SAVEPOINT orders_import;
INSERT DATA INFILE '/tmp/customer_orders.sql'
INTO TABLE orders;

At this point in this example, the administrator can check the results of the orders imported before committing the transactions. If the administrator decides that the orders imported have problems (the /tmp/customer_orders.sql file), but not the client information that was first imported (the /tmp/customer_info.sql file), the following statement could be entered:

ROLLBACK TO SAVEPOINT orders_import;

If the administrator decides that the customer information that was imported also has problems, the ROLLBACK statement can be issued to undo the entire transaction.

As of version 5.0.17 of MySQL, if a stored function or trigger is used, a new savepoint level is set up and the previous savepoints are suspended. When the stored function or trigger is finished, any savepoints it created are released and the original savepoint level resumes.

Use this statement to retrieve and display data from tables within a database. It has many clauses and options, but for simple data retrieval many of them can be omitted. The basic syntax for the statement is shown. After the SELECT keyword, some keywords to control the whole operation may be given. Next comes an asterisk to retrieve all columns, a list of columns to retrieve, or expressions returning values to display, separated by commas.

Data can be retrieved from one or more tables, given in a comma-separated list. If multiple tables are specified, other clauses must define how the tables are joined. The remaining clauses may be called on to refine the data to be retrieved, to order it, and so forth. These various keywords, options, and clauses are detailed in subsections of this statement explanation. To start, here is a simple example of how you can use the SELECT statement:

SELECT name_first, name_last, telephone_home,
DATEDIFF(now( ), last_review)
AS 'Days Since Last Review'
FROM employees;

In this example, three columns and the results of an expression based on a fourth column are to be displayed. The first and last name of each employee, each employee’s home telephone number, and the difference between the date of the employee’s last employment review and the date now are listed. This last field has the addition of the AS keyword to set the column heading of the results set, and to name an alias for the field. An alias may be referenced in subsequent clauses of the same statement (e.g., the ORDER BY clause). To select all columns in the table, the wildcard * can be given instead of the column names.

SELECT statement keywords

SELECT
[ALL|DISTINCT|DISTINCTROW]
[HIGH_PRIORITY] [STRAIGHT_JOIN]
[SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
[SQL_CACHE|SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
{*|column|expression}[, ...]
FROM table[, ...]
[WHERE condition] [other clauses] [options]

Between the initial SELECT keyword and list of columns and expressions, several keywords may be given. They are shown in the preceding syntax, with the other components of the statement abbreviated.

When a WHERE clause is used with the SELECT statement, rows in the results may contain duplicate data. If you want all rows that meet the selection conditions to be displayed, you may include the ALL keyword. This is the default, so it’s not necessary to give this keyword. If you want to display only the first occurrence of a row, include the DISTINCT keyword or its synonym DISTINCTROW. Here is an example:

SELECT DISTINCT dept 
FROM employees;

This statement will list the names of all departments for which we have employees listed in the employees table. Even though there are several employees in the same department, it will list only one row for each department.

By default, any UPDATE statements that are issued have priority over SELECT statements submitted by other client sessions at the same time; the updates are run first. To give a particular SELECT statement higher priority than any UPDATE statements, use the HIGH_PRIORITY keyword.

Multiple tables may be selected with the SELECT statement. The column on which they should be joined is given with the WHERE clause or the JOIN clause. The JOIN clause is described earlier in this chapter. For the purposes of this section, you just need to know that in order to optimize retrieval, MySQL might not join tables in the order that they are listed in the SQL statement. To insist on joining in the order given, you must use the STRAIGHT_JOIN keyword.

When you know that the results of a SELECT statement using the DISTINCT keyword or the GROUP BY clause (discussed later) will be small, you can use the SQL_SMALL_RESULT keyword. This will cause MySQL to use temporary tables, with a key based on the GROUP BY clause elements, to sort the results and possibly make for faster data retrieval. If you expect the results to be large, you can use the SQL_BIG_RESULT keyword. This will cause MySQL to use temporary tables on the filesystem. Regardless of whether you use DISTINCT or GROUP BY, the SQL_BUFFER_RESULT keyword may be given for any SELECT statement to have MySQL use a temporary table to buffer the results. You can use only one of the SQL_*_RESULT keywords in each statement.

If the MySQL server is not using the query cache by default, you can force its use by including the SQL_CACHE keyword. If the server does use the query cache by default, you can use the SQL_NO_CACHE to instruct MySQL not to use the cache for this particular SELECT statement. To determine whether the server uses query cache by default, enter SHOW VARIABLES LIKE 'query_cache_type';. A value of ON indicates that it is in use.

The last keyword available is SQL_CALC_FOUND_ROWS, which counts the number of rows that meet the conditions of the statement. This is not affected by a LIMIT clause. The results of this count must be retrieved in a separate SELECT statement with the FOUND_ROWS() function. See the end of this chapter for information on this function:

SELECT SQL_CALC_FOUND_ROWS 
name_first, name_last, telephone_home,
DATEDIFF(now( ), last_review)
AS 'Days Since Last Review'
FROM employees 
WHERE dept = 'sales'
ORDER BY last_review DESC
LIMIT 10;

SELECT FOUND_ROWS();

The first statement retrieves a list of sales people to review, limited to the 10 who have gone the longest without a performance review. The second gets a count of how many employees there are to review in the sales department.

Exporting SELECT results

SELECT [flags] {*|columns|expression}[, ...]
[INTO OUTFILE '/path/filename'
  [FIELDS TERMINATED BY 'character']
  [FIELDS ENCLOSED BY 'character']
  [ESCAPED BY 'character' ]
  [LINES [STARTING BY 'character'] [TERMINATED BY 'character']]
|INTO DUMPFILE '/path/filename'
|INTO 'variable'[, ...]
[FOR UPDATE|LOCK IN SHARE MODE]]
FROM table[, ...]
[WHERE condition]
[other clauses] [options]

The INTO clause is used to export data from a SELECT statement to an external text file or a variable. Only the results will be exported, not the column names or other information.

Various clauses set delimiter and control characters in the output:

FILE privilege is necessary to use the INTO clause of the SELECT statement. This statement and clause combination is essentially the counterpart of the LOAD DATA INFILE statement. See the explanation of that statement earlier in this chapter for more details on the options for this clause. Here is an example of this clause and these options:

SELECT * FROM employees
INTO OUTFILE '/tmp/employees.txt'
FIELDS TERMINATED BY '|'
LINES TERMINATED BY '\n'
ESCAPED BY '\\';

The text file created by this SQL statement will contain a separate line for each row selected. Each field will end with a vertical bar. Any special characters (e.g., an apostrophe) will be preceded by a backslash. Because a backslash is an escape character within an SQL statement, two backslashes are needed in the ESCAPE BY clause because the first escapes the second. To import the resulting data text file, use the FOUND_ROWS() statement.

The second syntax uses the clause INTO DUMPFILE and exports only one row into an external text file. It does not allow any field or line terminators like the INTO OUTFILE clause. Here is an example of its use:

SELECT photograph
INTO DUMPFILE '/tmp/bobs_picture.jpeg'
FROM employees
WHERE emp_id = '1827';

This statement exports the contents of the photograph column for an employee’s record. It’s a BLOB type column and contains an image file. The result of the exported file is a complete and usable image file.

You can also use the INTO clause to store a value in a user variable or a system variable for reuse. Here’s an example:

SET @sales = 0;

SELECT SUM(total_order) AS Sales
INTO @sales
FROM orders
WHERE YEAR(order_date) = YEAR(CURDATE());

This example creates the user variable @sales. Then we calculate the total sales for the current year and store it into that variable for reuse in subsequent statements in the session.

Grouping SELECT results

SELECT [flags] {*|column|expression}[, ...]
FROM table[, ...]
[WHERE condition]
[GROUP BY {column|expression|position} [ASC|DESC], ...
  [WITH ROLLUP]]
[other clauses] [options]

A SELECT statement sometimes produces more meaningful results if you group together rows containing the same value for a particular column. The GROUP BY clause specifies one or more columns by which MySQL is to group the data retrieved. This is used with aggregate functions so that the values of numeric columns for the rows grouped will be aggregated.

For instance, suppose that a SELECT statement is to list the sales representatives for a business and their orders for the month. Without a GROUP BY clause, one line would be displayed for each sales representative for each order. Here’s an example of how this might be resolved: