MySQL in a Nutshell (2008)
Part II. SQL Statements and Functions
Chapter 5. Database and Table Schema Statements
This chapter explains the SQL statements in MySQL related to database and table schema. These statements create, alter, and delete databases and tables, as well as display information related to databases, tables, and columns. The statements in this chapter pertain to information about these data structures, not the manipulation of data within them; statements that affect the data are covered in the next chapter. In essence, this chapter covers the SQL statements used when one is in the mode of creating database structures. This mode is a fairly distinct mindset and is sometimes the responsibility of different persons from those who manipulate the data itself.
This chapter covers the following SQL statements:
ALTER DATABASE, ALTER SCHEMA, ALTER SERVER, ALTER TABLE, ALTER VIEW, CREATE DATABASE, CREATE INDEX, CREATE SCHEMA, CREATE SERVER, CREATE TABLE, CREATE VIEW, DESCRIBE, DROP DATABASE, DROP INDEX, DROP SERVER,DROP TABLE, DROP VIEW, RENAME DATABASE, RENAME TABLE, SHOW CHARACTER SET, SHOW COLLATION, SHOW COLUMNS, SHOW CREATE DATABASE, SHOW CREATE TABLE, SHOW CREATE VIEW, SHOW DATABASES, SHOW INDEXES, SHOW SCHEMAS, SHOW TABLE STATUS, SHOW TABLES, SHOW VIEWS.
Statements and Clauses in Alphabetical Order
The following is a list of MySQL statements and clauses related to database and table schema, in alphabetical order. To understand how this book presents SQL syntax and describes SQL statements, as well as for information related to examples, please see the introduction to Part II. Many of the examples in this particular chapter involve the activities of the departments of a fictitious company: its human resources department and employee data, its sales department and client contact information, and its internal IT department with user work requests.
Name
ALTER DATABASE
Synopsis
ALTER {DATABASE|SCHEMA} database
[DEFAULT] CHARACTER SET character_set |
[DEFAULT] COLLATE collation
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 typicaldb.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.
Name
ALTER SCHEMA
Synopsis
ALTER {DATABASE|SCHEMA} database
[DEFAULT] CHARACTER SET character_set |
[DEFAULT] COLLATE collation
This statement is synonymous with ALTER DATABASE. See the description of that statement previously for more information and examples.
Name
ALTER SERVER
Synopsis
ALTER SERVER server
OPTIONS (
{ HOST host, |
DATABASE database, |
USER user, |
PASSWORD password, |
SOCKET socket, |
OWNER character, |
PORT port }
)
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.
Name
ALTER TABLE
Synopsis
ALTER [IGNORE] TABLE table changes[, ...]
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:
§ Add a new column (see the ALTER TABLE: ADD clauses for columns” subsection that follows)
§ Add an index (see the ALTER TABLE: ADD clause for standard indexes,” ALTER TABLE: ADD clause for FULLTEXT indexes,” and ALTER TABLE: ADD clause for SPATIAL indexes” subsections)
§ Add a foreign key constraint (see the ALTER TABLE: ADD clauses for foreign keys” subsection)
§ Change an existing column (see the ALTER TABLE: CHANGE clauses” subsection)
§ Delete a column or index (see the ALTER TABLE: DROP column clause” and ALTER TABLE: DROP index clauses” subsections)
§ Set other column and index factors (see the ALTER TABLE: Miscellaneous clauses” subsection)
§ Add and change table partitions (see the ALTER TABLE: Partition altering clauses” and ALTER TABLE: Partition administration clauses” subsections)
§ Set table-wide options (see the ALTER TABLE: Table options” subsection)
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. AnyINSERT 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 COLUMNkeyword 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). TheINDEX 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 STATUSstatement, 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 theemployees_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 underCREATE 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 AFTERkeywords. 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 TABLEstatement.
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.
Converting and setting character sets
The first two syntaxes shown may be used to change the character set and collation for a table. When a table is first created with the CREATE TABLE statement, unless a character set or collation is specified, defaults for these traits are used. To see the character set and collation for a particular table, use the SHOW TABLE STATUS statement. To convert the data, use the CONVERT TO CHARACTER SET clause. To set the table’s default without converting the data, use the DEFAULT CHARACTER SET clause with the ALTER TABLE statement. The following example shows how to convert a table’s character set:
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;
The first clause in this example converts the data in the clients table from its default of latin1_swedish_ci to latin2. The second clause sets the new default for the table to latin2, as well. Be aware that the CONVERT clause may cause problems with the data. So be sure to make a backup copy before using this clause and check the converted data before finishing. If you have a column with a character set in which data might be lost in the conversion, you could first convert the column to a Binary Large Object (BLOB) data type, and then to the data type and character set that you want. This usually works fine because BLOB data isn’t converted with a character set change.
Disabling and enabling keys
You can use the third clause (DISABLE and ENABLE) to disable or enable the updating of nonunique indexes on MyISAM tables. You will need ALTER, CREATE, INDEX, and INSERT privileges to execute this statement and clause. As of version 5.1.11 of MySQL, this clause will work on partitioned tables. When running a large number of row inserts, it can be useful to disable indexing until afterward:
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;
In this example, we’ve disabled the indexes of the catalog table in the sales_dept database so that we can more quickly import the new catalog data. If we had run the SHOW INDEXES statement at this point, we would have seen disabled in the Comment field of the results for all of the indexes except the PRIMARY key. In our example, we then reenabled the indexes for faster retrieval of data by users.
Discarding or importing tablespace
InnoDB tables use tablespaces instead of individual files for each table. A tablespace can involve multiple files and can allow a table to exceed the filesystem file limit as a result. You can use the TABLESPACE clauses in the ALTER TABLE statement to delete or import a tablespace:
ALTER TABLE workreq
IMPORT TABLESPACE;
This statement imports the .idb file if it’s in the database’s directory. Replacing the IMPORT keyword with DISCARD will delete the .idb file.
Reordering rows
You can use the next clause syntax structure, the ORDER BY clause, to permanently reorder the rows in a given table. Note that after an ALTER TABLE statement, any new rows inserted will be added to the end of the table and the table will not be reordered automatically. To enforce another order, you will need to run ALTER TABLE again with this clause. The only reason to use this clause is for tables that rarely change, because reordering sometimes improves performance. In most cases, instead of reordering the storage of the table, it’s recommended you include an ORDER BYclause in your SELECT statements.
Here’s an example with this clause:
ALTER TABLE clients
ORDER BY client_name;
It’s possible to give more than one column name in the ORDER BY clause, separated by commas. Expressions cannot be used. You can, however, specify ascending (ASC, the default) or descending (DESC) order for each column.
Renaming a table
You can use the RENAME clause to change the name of an existing table. Here is an example of this clause:
ALTER TABLE client RENAME TO clients;
This statement renames the client table to clients. The TO keyword is not required; it’s a matter of style preference and compatibility. A statement with this clause is equivalent to using the RENAME TABLE statement, except that the RENAME clause does not change user privileges from the old table name to refer to the new name of the table.
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.
Name
ALTER VIEW
Synopsis
ALTER
[ALGORITHM = {UNDEFINED|MERGE|TEMPTABLE}]
[DEFINER = {'user'@'host'|CURRENT_USER}]
[SQL SECURITY {DEFINER|INVOKER }]
VIEW view [(column, ...)]
AS SELECT...
[WITH [CASCADED|LOCAL] CHECK OPTION]
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.
Name
CREATE DATABASE
Synopsis
CREATE {DATABASE|SCHEMA} [IF NOT EXISTS] database [options]
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.
Name
CREATE INDEX
Synopsis
CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index
[USING type|TYPE type]
[USING type|TYPE type]
ON table (column [(length)] [ASC|DESC], ...)
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 theCREATE 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.
Name
CREATE SCHEMA
Synopsis
CREATE {DATABASE|SCHEMA} [IF NOT EXISTS] database [options]
This statement is synonymous with CREATE DATABASE. See the description of that statement earlier in this chapter for more information and examples.
Name
CREATE SERVER
Synopsis
CREATE SERVER server
FOREIGN DATA WRAPPER wrapper
OPTIONS (
{ DATABASE database, |
HOST host, |
USER user, |
PASSWORD password, |
SOCKET socket, |
OWNER character, |
PORT port }
)
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';
Name
CREATE TABLE
Synopsis
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] table
{[(definition)][options]|[[AS] SELECT...]|[LIKE table]}
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:
§ Set column properties regarding NULL and default values (see the CREATE TABLE: Column flags” subsection)
§ Create an index for a table based on one or more columns (see the CREATE TABLE: Index and key definitions” subsection)
§ Reference a foreign key constraint (see the CREATE TABLE: Foreign key references” subsection)
§ Specify various table options (see the CREATE TABLE: Table options” subsection)
§ Create a table exactly like another table (see the CREATE TABLE: Based on an existing table” subsection)
§ Create a table with filesystem partitions (see the three subsections on partitioning: CREATE TABLE: Partitioning,” CREATE TABLE: Partition definitions,” and CREATE TABLE: Subpartition definitions”)
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 DEFAULTkeyword. 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 oneAUTO_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 telephonecolumn. 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 theDESC 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 theprogrammers 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.
AUTO_INCREMENT
This parameter causes MySQL to assign a unique identification number automatically to the column in each row added to the table. By default, the starting number is 1. To set it to a different starting number when creating a table, you can use the AUTO_INCREMENT table option. Here’s an example using this option:
CREATE TABLE clients
(client_id INT AUTO_INCREMENT KEY,
client_name VARCHAR(75),
telephone CHAR(15))
AUTO_INCREMENT=1000;
This statement sets the initial value of the primary key column to 1000 so that the first row inserted will be 1001. There is usually no reason to set a starting number explicitly, because the key is used merely to distinguish different columns.
AVG_ROW_LENGTH
For large tables, you may want to set the average row length for better table optimization by using the AVG_ROW_LENGTH option:
CREATE TABLE clients
(client_id INT AUTO_INCREMENT KEY,
client_name VARCHAR(75),
telephone CHAR(15))
AVG_ROW_LENGTH = 12638;
CHARACTER SET
This option sets the character set used 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 sets and collation are explicitly specified. Use the SHOW CHARACTER SET and SHOW COLLATION statements to see the character sets and collations available:
CREATE TABLE clients
(client_id INT AUTO_INCREMENT KEY,
client_name VARCHAR(75),
telephone CHAR(15))
DEFAULT CHARACTER SET 'latin2'
COLLATE 'latin2_general_ci';
CHECKSUM
This option enables or disables a checksum for a table. Set the value to 0 to disable the checksum or to 1 to enable a checksum on a table. If you are upgrading 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 usingREPAIR TABLE to recalculate the checksum for the table:
CREATE TABLE clients
(client_id INT AUTO_INCREMENT KEY,
client_name VARCHAR(75),
telephone CHAR(15))
CHECKSUM = 0;
COLLATE
This option sets the collation (alphabetizing order) to use with character data in the table. 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:
CREATE TABLE clients
(client_id INT AUTO_INCREMENT KEY,
client_name VARCHAR(75),
telephone CHAR(15))
COLLATE 'latin2_general_ci'
DEFAULT CHARACTER SET 'latin2';
COMMENT
With this option, you can add notes for yourself or other table administrators regarding a table. Comments are shown only when the SHOW CREATE TABLE statement is executed:
CREATE TABLE clients
(client_id INT AUTO_INCREMENT KEY,
client_name VARCHAR(75),
telephone CHAR(15))
COMMENT = 'This table lists basic information on clients.';
CONNECTION
This 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 want 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:
CREATE TABLE clients
(client_id INT AUTO_INCREMENT KEY,
client_name VARCHAR(75),
telephone CHAR(15))
ENGINE = FEDERATED
CONNECTION='mysql://russell:rover123@santa_clara_svr:9306/federated/clients';
DATA DIRECTORY
This option is theoretically used to see the data directory path for the table. As of version 5.1.23 of MySQL, this option is ignored for table partitions. Filesystem privileges for the path given are required to specify the option:
CREATE TABLE clients
(client_id INT AUTO_INCREMENT KEY,
client_name VARCHAR(75),
telephone CHAR(15))
DATA DIRECTORY = '/data/mysql/clients';
DELAY_KEY_WRITE
This option delays index updates until the table is closed. It’s enabled with a value of 1 and disabled with a value of 0:
CREATE TABLE clients
(client_id INT AUTO_INCREMENT KEY,
client_name VARCHAR(75),
telephone CHAR(15))
DELAY_KEY_WRITE = 1;
ENGINE
Use this 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:
CREATE TABLE clients
(client_id INT AUTO_INCREMENT KEY,
client_name VARCHAR(75),
telephone CHAR(15))
ENGINE = MyISAM;
INDEX DIRECTORY
This option is theoretically used to see the directory path for the table indexes. As of version 5.1.23 of MySQL, this option is ignored for table partitions. Filesystem privileges for the path given are required to specify the option:
CREATE TABLE clients
(client_id INT AUTO_INCREMENT KEY,
client_name VARCHAR(75),
telephone CHAR(15))
INDEX DIRECTORY = '/data/mysql/clients_index';
INSERT_METHOD
To insert data into a MERGE table, you need to specify the insert method it will use. To set or change this method, use the INSERT_METHOD option with the CREATE TABLE or ALTER TABLE statements. A value of FIRST indicates that the first table should be used; LAST indicates the last table should be used; NO disables insertions:
CREATE TABLE sales_national
(order_id INT, sales_total INT)
ENGINE = MERGE
UNION = (sales_east, sales_west)
INSERT_METHOD = LAST;
This SQL statement creates the table sales_national based on two other tables while specifying that insertions use the last table in the list of tables given.
KEY_BLOCK_SIZE
This option gives the storage engine a hint of the size of index key blocks. Set the value to 0 to instruct the engine to use the default. This option was added in version 5.1.10 of MySQL:
CREATE TABLE clients
(client_id INT AUTO_INCREMENT KEY,
client_name VARCHAR(75),
telephone CHAR(15))
KEY_BLOCK_SIZE = 1024;
MAX_ROWS, MIN_ROWS
These options are used to set the maximum and minimum rows of a table, respectively. Use the SHOW CREATE TABLE statement to see the results:
CREATE TABLE clients
(client_id INT AUTO_INCREMENT KEY,
client_name VARCHAR(75),
telephone CHAR(15))
MIN_ROWS = 100,
MAX_ROWS = 1000;
PACK_KEYS
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 DEFAULTinstructs the storage engine to pack CHAR or VARCHAR data type columns only:
CREATE TABLE clients
(client_id INT AUTO_INCREMENT KEY,
client_name VARCHAR(75),
telephone CHAR(15))
PACK_KEYS = 0;
RAID_TYPE
This option specifies the type of RAID to be used. However, support for RAID has been removed from MySQL as of version 5.0. This SQL statement also used to permit the options RAID_CHUNKS and RAID_CHUNKSIZE, but they have been deprecated as well.
ROW_FORMAT
This option tells 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 run the myisampack utility on a MyISAM table, the format will be set to a value of COMPRESSED. You can uncompress a compressed MyISAM table by giving a value of REDUNDANT. This value 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:
CREATE TABLE clients
(client_id INT AUTO_INCREMENT KEY,
client_name VARCHAR(75),
telephone CHAR(15))
ROW_FORMAT = DYNAMIC;
UNION
To change the tables that make up a MERGE table, specify the full list of tables using this option:
CREATE TABLE sales_national
(order_id INT, sales_total INT)
ENGINE = MERGE
UNION = (sales_north, sales_south, sales_east, sales_west);
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 TABLEstatement 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 andSUBPARTITION clauses are explained in the next two subsections that cover this statement (CREATE TABLE: Partition definitions” and CREATE TABLE: Subpartition definitions”):
HASH
This subclause creates a key/value pair that controls which partition is used for saving rows of data and for indexing data. The value of the hash consists of the specified columns. If a table has a primary key, that column must be used by the hash. Functions that return a numerical value (not a string) may be used within a hash specification:
CREATE TABLE sales_figures
(emp_id INT,
sales_date DATE,
amount INT)
PARTITION BY HASH(MONTH(sales_date))
PARTITIONS 12;
This creates 12 partitions, one for each month extracted from the sales_data.
By default, the HASH method and the KEY method (described next) use the modulus of the hash function’s given value. The keyword LINEAR may be added in front of HASH or KEY to change the algorithm to a linear powers-of-two algorithm. For extremely large tables of data, the linear hash has higher performance results in processing data, but does not evenly spread data among partitions.
KEY
This subclause functions the same as HASH except that it accepts only a comma-separated list of columns for indexing and distributing data among partitions. The LINEAR flag may be given to change the algorithm method used. See the previous description for HASH:
CREATE TABLE clients
(client_id INT AUTO_INCREMENT KEY,
client_name VARCHAR(75),
telephone CHAR(15))
PARTITION BY KEY (client_id)
PARTITIONS 4;
LIST
This subclause can be used to give specific values for distributing data across partitions. The column and values must all be numeric, not strings:
CREATE TABLE sales_figures
(region_id INT, sales_date DATE, amount INT)
PARTITION BY LIST (region_id) (
PARTITION US_DATA VALUES IN(100,200,300),
PARTITION EU_DATA VALUES IN(400,500));
In this example, data is distributed between two partitions: one for the sales in the United States, which is composed of three regions, and a second partition for data for the two European regions. Notice that the names for the partitions given aren’t in the usual naming convention (e.g.,p0). Any name will do. It’s a matter of preference.
RANGE
To instruct MySQL to distribute data among the partitions based on a range of values, use the RANGE subclause. Use the VALUES LESS THAN subclause to set limits for each range. Use VALUES LESS THAN MAXVALUE to set the limit of the final partition:
CREATE TABLE clients
(client_id INT AUTO_INCREMENT KEY,
client_name VARCHAR(75),
telephone CHAR(15))
PARTITION BY RANGE (client_id) (
PARTITION p0 VALUES LESS THAN (500),
PARTITION p1 VALUES LESS THAN (1000),
PARTITION p3 VALUES LESS THAN MAXVALUE);
In this example, the data is distributed among the partitions based on the client_id values. The first partition will contain rows with a client identification number less than 500; the second will contain rows of values ranging from 501 to 1000; and the last partition will contain values of 1001 and higher. Values given for partitions must be in ascending order.
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:
COMMENT
Use this subclause if you want to add a comment to a partition. The text must be contained within single quotes. Comments can be viewed only with the SHOW CREATE TABLE statement:
CREATE TABLE sales_figures
(region_id INT, sales_date DATE, amount INT)
PARTITION BY LIST (region_id) (
PARTITION US_DATA VALUES IN(100,200,300)
COMMENT = 'U.S. Data',
PARTITION EU_DATA VALUES IN(400,500)
COMMENT = 'Europe Data');
DATA DIRECTORY, INDEX DIRECTORY
With these subclauses, you can specify file pathnames in order to fix the locations of partitions. The directories given must exist and you must have access privileges to the given directories:
CREATE TABLE clients
(client_id INT AUTO_INCREMENT KEY,
client_name VARCHAR(75),
telephone CHAR(15))
PARTITION BY RANGE (client_id) (
PARTITION p0 VALUES LESS THAN (500)
DATA DIRECTORY = '/data/mysql/old_clients/data'
INDEX DIRECTORY = '/data/mysql/old_clients/index',
PARTITION p1 VALUES LESS THAN MAXVALUE
DATA DIRECTORY = '/data/mysql/new_clients/data'
INDEX DIRECTORY = '/data/mysql/new_clients/index');
ENGINE
This subclause specifies an alternative storage engine to use for the partition. However, at this time all partitions must use the same storage engine:
CREATE TABLE clients
(client_id INT AUTO_INCREMENT KEY,
client_name VARCHAR(75),
telephone CHAR(15))
PARTITION BY RANGE (client_id) (
PARTITION p0 VALUES LESS THAN (500)
ENGINE = InnoDB,
PARTITION p1 VALUES LESS THAN MAXVALUE
ENGINE = InnoDB);
MAX_ROWS, MIN_ROWS
These subclauses suggest the maximum and minimum number of rows in a table partition, respectively. MySQL may deviate from these limits, though:
CREATE TABLE clients
(client_id INT AUTO_INCREMENT KEY,
client_name VARCHAR(75),
telephone CHAR(15))
PARTITION BY RANGE (client_id) (
PARTITION p0 VALUES LESS THAN (500)
MIN_ROWS = 10 MAX_ROWS = 1000,
PARTITION p3 VALUES LESS THAN MAXVALUE
MIN_ROWS = 10 MAX_ROWS = 500);
NODEGROUP
This subclause can be used only with MySQL Cluster, and places a partition in the given node group. (MySQL clusters are divided into different node groups in order to let certain nodes manage the data nodes.)
TABLESPACE
This subclause can be used only with MySQL Cluster, and specifies the tablespace to use with the partition.
VALUES
This subclause specifies a range of values or a list of specific values for indexing and determining the disbursal of data among partitions. These are described earlier in the CREATE TABLE: Partitioning” subsection.
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 existingemployees 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.
Name
CREATE VIEW
Synopsis
CREATE
[OR REPLACE]
[ALGORITHM = {MERGE|TEMPTABLE|UNDEFINED}]
[DEFINER = {'user'@'host'|CURRENT_USER}]
[SQL SECURITY {DEFINER|INVOKER}]
VIEW view [(column, . . . )]
AS SELECT...
[WITH [CASCADED|LOCAL] CHECK OPTION]
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';.
Name
DESCRIBE
Synopsis
{DESCRIBE|DESC} table [column]
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:
Field
Lists the name of each column in the table.
Type
Shows the data type of each column.
Null
Indicates whether the column in the table may contain a NULL value.
Default
Shows the default value of the column.
Key
Indicates what type of key the column is. If this field is empty, the column is not indexed. A value of PRI indicates a PRIMARY KEY column, UNI indicates a UNIQUE indexed column, and MUL means that multiple occurrences, or duplicate values, are permitted for the column. This is allowed because the column is only one of multiple columns making up an index.
Extra
Lists any extra information particular to the column.
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.
Name
DROP DATABASE
Synopsis
DROP {DATABASE|SCHEMA} [IF EXISTS] database
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.
Name
DROP INDEX
Synopsis
DROP INDEX index ON table
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 INDEXstatement:
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.
Name
DROP SERVER
Synopsis
DROP SERVER [IF EXISTS] server
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;
Name
DROP TABLE
Synopsis
DROP [TEMPORARY] TABLE [IF EXISTS] table[, ...]
[RESTRICT|CASCADE]
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 theSHOW WARNINGS statement, as shown in this example. Notice that the number of tables deleted is not returned, although the repairs table is deleted.
Name
DROP VIEW
Synopsis
DROP VIEW [IF EXISTS] view[, ...] [RESTRICT|CASCADE]
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.
Name
RENAME DATABASE
Synopsis
RENAME {DATABASE|SCHEMA} database TO database[,...]
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.
Name
RENAME TABLE
Synopsis
RENAME TABLE table TO table[,...]
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.
Name
SHOW CHARACTER SET
Synopsis
SHOW CHARACTER SET [LIKE 'pattern'| WHERE expression]
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.
Name
SHOW COLLATION
Synopsis
SHOW COLLATION [LIKE 'pattern'| WHERE expression]
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 |
+---------+------------------+-------------------+--------+
Name
SHOW COLUMNS
Synopsis
SHOW [FULL] COLUMNS FROM table [FROM database] [LIKE 'pattern'|WHERE expression]
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.
Name
SHOW CREATE DATABASE
Synopsis
SHOW CREATE {DATABASE|SCHEMA} database
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.
Name
SHOW CREATE TABLE
Synopsis
SHOW CREATE TABLE table
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.
Name
SHOW CREATE VIEW
Synopsis
SHOW CREATE VIEW view
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).
Name
SHOW DATABASES
Synopsis
SHOW {DATABASES|SCHEMAS} [LIKE 'pattern'| WHERE expression]
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.
Name
SHOW INDEXES
Synopsis
SHOW {INDEX|INDEXES|KEYS} FROM table [FROM database]
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 forcontact_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
Name
SHOW SCHEMAS
Synopsis
SHOW {DATABASES|SCHEMAS} [LIKE 'pattern']
This statement is synonymous with SHOW DATABASES. See the description of that statement earlier in this chapter for more information and examples.
Name
SHOW TABLE STATUS
Synopsis
SHOW TABLE STATUS [FROM database] [LIKE 'pattern']
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
Name
SHOW TABLES
Synopsis
SHOW [FULL|OPEN] TABLES [FROM database] [LIKE 'pattern'| WHERE expression]
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 theLIKE 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
Name
SHOW VIEWS
Synopsis
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';.