| Chapter 13. SQL Statement Syntax This chapter describes the syntax for the SQL statements supported by MySQL. 13.1. Data Definition Statements13.1.1. ALTER DATABASE SyntaxALTER {DATABASE | SCHEMA} [db_name] alter_specification ...ALTER {DATABASE | SCHEMA} db_name UPGRADE DATA DIRECTORY NAMEalter_specification: [DEFAULT] CHARACTER SET [=] charset_name | [DEFAULT] COLLATE [=] collation_name ALTER DATABASE enables you to change the overall characteristics of a database. These characteristics are stored in the db.opt file in the database directory. To use ALTER DATABASE, you need the ALTER privilege on the database. ALTER SCHEMA is a synonym for ALTER DATABASE. The database name can be omitted from the first syntax, in which case the statement applies to the default database. National Language Characteristics The CHARACTER SET clause changes the default database character set. The COLLATE clause changes the default database collation. Section 10.1, "Character Set Support", discusses character set and collation names. You can see what character sets and collations are available using, respectively, the SHOW CHARACTER SET and SHOW COLLATION statements. See Section 13.7.5.4, "SHOW CHARACTER SET Syntax", and Section 13.7.5.5, "SHOW COLLATION Syntax", for more information. If you change the default character set or collation for a database, stored routines that use the database defaults must be dropped and recreated so that they use the new defaults. (In a stored routine, variables with character data types use the database defaults if the character set or collation are not specified explicitly. See Section 13.1.15, "CREATE PROCEDURE and CREATE FUNCTION Syntax".) Upgrading from Versions Older than MySQL 5.1 The syntax that includes the UPGRADE DATA DIRECTORY NAME clause updates the name of the directory associated with the database to use the encoding implemented in MySQL 5.1 for mapping database names to database directory names (see Section 9.2.3, "Mapping of Identifiers to File Names"). This clause is for use under these conditions: It is intended when upgrading MySQL to 5.1 or later from older versions. It is intended to update a database directory name to the current encoding format if the name contains special characters that need encoding. The statement is used by mysqlcheck (as invoked by mysql_upgrade).
For example, if a database in MySQL 5.0 has the name a-b-c, the name contains instances of the - (dash) character. In MySQL 5.0, the database directory is also named a-b-c, which is not necessarily safe for all file systems. In MySQL 5.1 and later, the same database name is encoded as a@002db@002dc to produce a file system-neutral directory name. When a MySQL installation is upgraded to MySQL 5.1 or later from an older version,the server displays a name such as a-b-c (which is in the old format) as #mysql50#a-b-c, and you must refer to the name using the #mysql50# prefix. Use UPGRADE DATA DIRECTORY NAME in this case to explicitly tell the server to re-encode the database directory name to the current encoding format: ALTER DATABASE `#mysql50#a-b-c` UPGRADE DATA DIRECTORY NAME; After executing this statement, you can refer to the database as a-b-c without the special #mysql50# prefix. 13.1.2. ALTER EVENT SyntaxALTER [DEFINER = { user | CURRENT_USER }] EVENT event_name [ON SCHEDULE schedule] [ON COMPLETION [NOT] PRESERVE] [RENAME TO new_event_name] [ENABLE | DISABLE | DISABLE ON SLAVE] [COMMENT 'comment'] [DO event_body] The ALTER EVENT statement changes one or more of the characteristics of an existing event without the need to drop and recreate it. The syntax for each of the DEFINER, ON SCHEDULE, ON COMPLETION, COMMENT, ENABLE / DISABLE, and DO clauses is exactly the same as when used with CREATE EVENT. (See Section 13.1.11, "CREATE EVENT Syntax".) Any user can alter an event defined on a database for which that user has the EVENT privilege. When a user executes a successful ALTER EVENT statement, that user becomes the definer for the affected event. ALTER EVENT works only with an existing event: mysql> ALTER EVENT no_such_event > ON SCHEDULE > EVERY '2:3' DAY_HOUR;ERROR 1517 (HY000): Unknown event 'no_such_event' In each of the following examples, assume that the event named myevent is defined as shown here: CREATE EVENT myevent ON SCHEDULE EVERY 6 HOUR COMMENT 'A sample comment.' DO UPDATE myschema.mytable SET mycol = mycol + 1; The following statement changes the schedule for myevent from once every six hours starting immediately to once every twelve hours, starting four hours from the time the statement is run: ALTER EVENT myevent ON SCHEDULE EVERY 12 HOUR STARTS CURRENT_TIMESTAMP + INTERVAL 4 HOUR; It is possible to change multiple characteristics of an event in a single statement. This example changes the SQL statement executed by myevent to one that deletes all records from mytable; it also changes the schedule for the event such that it executes once, one day after this ALTER EVENT statement is run. ALTER EVENT myevent ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 DAY DO TRUNCATE TABLE myschema.mytable; Specify the options in an ALTER EVENT statement only for those characteristics that you want to change; omitted options keep their existing values. This includes any default values for CREATE EVENT such as ENABLE. To disable myevent, use this ALTER EVENT statement: ALTER EVENT myevent DISABLE; The ON SCHEDULE clause may use expressions involving built-in MySQL functions and user variables to obtain any of the timestamp or interval values which it contains. You cannot use stored routines or user-defined functions in such expressions, and you cannot use any table references; however, you can use SELECT FROM DUAL. This is true for both ALTER EVENT and CREATE EVENT statements. References to stored routines, user-defined functions, and tables in such cases are specifically not permitted, and fail with an error (see Bug #22830). Although an ALTER EVENT statement that contains another ALTER EVENT statement in its DO clause appears to succeed, when the server attempts to execute the resulting scheduled event, the execution fails with an error. To rename an event, use the ALTER EVENT statement's RENAME TO clause. This statement renames the event myevent to yourevent: ALTER EVENT myevent RENAME TO yourevent; You can also move an event to a different database using ALTER EVENT ... RENAME TO ... and db_name.event_name notation, as shown here: ALTER EVENT olddb.myevent RENAME TO newdb.myevent; To execute the previous statement, the user executing it must have the EVENT privilege on both the olddb and newdb databases. Note There is no RENAME EVENT statement. The value DISABLE ON SLAVE is used on a replication slave instead of ENABLED or DISABLED to indicate an event that was created on the master and replicated to the slave, but that is not executed on the slave. Normally, DISABLE ON SLAVE is set automatically as required; however, there are some circumstances under which you may want or need to change it manually. See Section 16.4.1.10, "Replication of Invoked Features", for more information. 13.1.3. ALTER LOGFILE GROUP SyntaxALTER LOGFILE GROUP logfile_group ADD UNDOFILE 'file_name' [INITIAL_SIZE [=] size] [WAIT] ENGINE [=] engine_name This statement adds an UNDO file named 'file_name' to an existing log file group logfile_group. An ALTER LOGFILE GROUP statement has one and only one ADD UNDOFILE clause. No DROP UNDOFILE clause is currently supported. Note All MySQL Cluster Disk Data objects share the same namespace. This means that each Disk Data object must be uniquely named (and not merely each Disk Data object of a given type). For example, you cannot have a tablespace and an undo log file with the same name, or an undo log file and a data file with the same name. The optional INITIAL_SIZE parameter sets the UNDO file's initial size in bytes; if not specified, the initial size default to 128M (128 megabytes). You may optionally follow size with a one-letter abbreviation for an order of magnitude, similar to those used in my.cnf. Generally, this is one of the letters M (for megabytes) or G (for gigabytes). On 32-bit systems, the maximum supported value for INITIAL_SIZE is 4G. (Bug #29186) The minimum permitted value for INITIAL_SIZE is 1M. (Bug #29574) Note WAIT is parsed but otherwise ignored. This keyword currently has no effect, and is intended for future expansion. The ENGINE parameter (required) determines the storage engine which is used by this log file group, with engine_name being the name of the storage engine. Currently, the only accepted values for engine_name are "NDBCLUSTER" and "NDB". The two values are equivalent. Here is an example, which assumes that the log file group lg_3 has already been created using CREATE LOGFILE GROUP (see Section 13.1.14, "CREATE LOGFILE GROUP Syntax"): ALTER LOGFILE GROUP lg_3 ADD UNDOFILE 'undo_10.dat' INITIAL_SIZE=32M ENGINE=NDBCLUSTER; When ALTER LOGFILE GROUP is used with ENGINE = NDBCLUSTER (alternatively, ENGINE = NDB), an UNDO log file is created on each MySQL Cluster data node. You can verify that the UNDO files were created and obtain information about them by querying the INFORMATION_SCHEMA.FILES table. For example: mysql> SELECT FILE_NAME, LOGFILE_GROUP_NUMBER, EXTRA -> FROM INFORMATION_SCHEMA.FILES -> WHERE LOGFILE_GROUP_NAME = 'lg_3';+-------------+----------------------+----------------+| FILE_NAME | LOGFILE_GROUP_NUMBER | EXTRA |+-------------+----------------------+----------------+| newdata.dat | 0 | CLUSTER_NODE=3 || newdata.dat | 0 | CLUSTER_NODE=4 || undo_10.dat | 11 | CLUSTER_NODE=3 || undo_10.dat | 11 | CLUSTER_NODE=4 |+-------------+----------------------+----------------+4 rows in set (0.01 sec) (See Section 20.29.1, "The INFORMATION_SCHEMA FILES Table".) Memory used for UNDO_BUFFER_SIZE comes from the global pool whose size is determined by the value of the SharedGlobalMemory data node configuration parameter. This includes any default value implied for this option by the setting of the InitialLogFileGroup data node configuration parameter. ALTER LOGFILE GROUP is useful only with Disk Data storage for MySQL Cluster. For more information, see Section 17.5.12, "MySQL Cluster Disk Data Tables". 13.1.4. ALTER FUNCTION SyntaxALTER FUNCTION func_name [characteristic ...]characteristic: COMMENT 'string' | LANGUAGE SQL | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA } | SQL SECURITY { DEFINER | INVOKER } This statement can be used to change the characteristics of a stored function. More than one change may be specified in an ALTER FUNCTION statement. However, you cannot change the parameters or body of a stored function using this statement; to make such changes, you must drop and re-create the function using DROP FUNCTION and CREATE FUNCTION. You must have the ALTER ROUTINE privilege for the function. (That privilege is granted automatically to the function creator.) If binary logging is enabled, the ALTER FUNCTION statement might also require the SUPER privilege, as described in Section 19.7, "Binary Logging of Stored Programs". 13.1.5. ALTER PROCEDURE SyntaxALTER PROCEDURE proc_name [characteristic ...]characteristic: COMMENT 'string' | LANGUAGE SQL | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA } | SQL SECURITY { DEFINER | INVOKER } This statement can be used to change the characteristics of a stored procedure. More than one change may be specified in an ALTER PROCEDURE statement. However, you cannot change the parameters or body of a stored procedure using this statement; to make such changes, you must drop and re-create the procedure using DROP PROCEDURE and CREATE PROCEDURE. You must have the ALTER ROUTINE privilege for the procedure. By default, that privilege is granted automatically to the procedure creator. This behavior can be changed by disabling the automatic_sp_privileges system variable. See Section 19.2.2, "Stored Routines and MySQL Privileges". 13.1.6. ALTER SERVER SyntaxALTER SERVER server_name OPTIONS (option [, option] ...) Alters the server information for server_name, adjusting any of the options permitted in the CREATE SERVER statement. The corresponding fields in the mysql.servers table are updated accordingly. This statement requires the SUPER privilege. For example, to update the USER option: ALTER SERVER s OPTIONS (USER 'sally'); ALTER SERVER does not cause an automatic commit. In MySQL 5.5, ALTER SERVER is not written to the binary log, regardless of the logging format that is in use. 13.1.7. ALTER TABLE SyntaxALTER [ONLINE | OFFLINE] [IGNORE] TABLE tbl_name [alter_specification [, alter_specification] ...] [partition_options]alter_specification: table_options | ADD [COLUMN] col_name column_definition [FIRST | AFTER col_name ] | ADD [COLUMN] (col_name column_definition,...) | ADD {INDEX|KEY} [index_name] [index_type] (index_col_name,...) [index_option] ... | ADD [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (index_col_name,...) [index_option] ... | ADD [CONSTRAINT [symbol]] UNIQUE [INDEX|KEY] [index_name] [index_type] (index_col_name,...) [index_option] ... | ADD FULLTEXT [INDEX|KEY] [index_name] (index_col_name,...) [index_option] ... | ADD SPATIAL [INDEX|KEY] [index_name] (index_col_name,...) [index_option] ... | ADD [CONSTRAINT [symbol]] FOREIGN KEY [index_name] (index_col_name,...) reference_definition | ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT} | CHANGE [COLUMN] old_col_name new_col_name column_definition [FIRST|AFTER col_name] | MODIFY [COLUMN] col_name column_definition [FIRST | AFTER col_name] | DROP [COLUMN] col_name | DROP PRIMARY KEY | DROP {INDEX|KEY} index_name | DROP FOREIGN KEY fk_symbol | MAX_ROWS = rows | DISABLE KEYS | ENABLE KEYS | RENAME [TO|AS] new_tbl_name | ORDER BY col_name [, col_name] ... | CONVERT TO CHARACTER SET charset_name [COLLATE collation_name] | [DEFAULT] CHARACTER SET [=] charset_name [COLLATE [=] collation_name] | DISCARD TABLESPACE | IMPORT TABLESPACE | FORCE | ADD PARTITION (partition_definition) | DROP PARTITION partition_names | TRUNCATE PARTITION {partition_names | ALL} | COALESCE PARTITION number | REORGANIZE PARTITION [partition_names INTO (partition_definitions)] | ANALYZE PARTITION {partition_names | ALL} | CHECK PARTITION {partition_names | ALL} | OPTIMIZE PARTITION {partition_names | ALL} | REBUILD PARTITION {partition_names | ALL} | REPAIR PARTITION {partition_names | ALL} | PARTITION BY partitioning_expression | REMOVE PARTITIONINGindex_col_name: col_name [(length)] [ASC | DESC]index_type: USING {BTREE | HASH}index_option: KEY_BLOCK_SIZE [=] value | index_type | WITH PARSER parser_name | COMMENT 'string'table_options: table_option [[,] table_option] ... (see CREATE TABLE options)partition_options: (see CREATE TABLE options) ALTER TABLE changes the structure of a table. For example, you can add or delete columns, create or destroy indexes, change the type of existing columns, or rename columns or the table itself. You can also change characteristics such as the storage engine used for the table or the table comment. Partitioning-related clauses for ALTER TABLE can be used with partitioned tables for repartitioning, for adding, dropping, merging, and splitting partitions, and for performing partitioning maintenance. For more information, see Section 13.1.7.1, "ALTER TABLE Partition Operations". If an ALTER TABLE statement contains a partitioning operation, that must be the only operation specified in the statement. Following the table name, specify the alterations to be made. If none are given, ALTER TABLE does nothing. The syntax for many of the permissible alterations is similar to clauses of the CREATE TABLE statement. See Section 13.1.17, "CREATE TABLE Syntax", for more information. Some operations may result in warnings if attempted on a table for which the storage engine does not support the operation. These warnings can be displayed with SHOW WARNINGS. See Section 13.7.5.41, "SHOW WARNINGS Syntax". For information on troubleshooting ALTER TABLE, see Section C.5.7.1, "Problems with ALTER TABLE". Storage, Performance, and Concurrency Considerations In most cases, ALTER TABLE makes a temporary copy of the original table. MySQL waits for other operations that are modifying the table, then proceeds. It incorporates the alteration into the copy, deletes the original table, and renames the new one. While ALTER TABLE is executing, the original table is readable by other sessions. Updates and writes to the table that begin after the ALTER TABLE operation begins are stalled until the new table is ready, then are automatically redirected to the new table without any failed updates. The temporary table is created in the database directory of the new table. This can differ from the database directory of the original table for ALTER TABLE operations that rename the table to a different database. For MyISAM tables, you can speed up index re-creation (the slowest part of the alteration process) by setting the myisam_sort_buffer_size system variable to a high value. For some operations, an in-place ALTER TABLE is possible that does not require a temporary table: For ALTER TABLE tbl_name RENAME TO new_tbl_name without any other options, MySQL simply renames any files that correspond to the table tbl_name without making a copy. (You can also use the RENAME TABLE statement to rename tables. See Section 13.1.32, "RENAME TABLE Syntax".) Any privileges granted specifically for the renamed table are not migrated to the new name. They must be changed manually. Alterations that modify only table metadata and not table data can be made immediately by altering the table's .frm file and not touching table contents. The following changes are fast alterations that can be made this way: Renaming a column, except for the InnoDB storage engine. Changing the default value of a column (except for NDB tables; see Limitations of NDBCLUSTER online operations). Changing the definition of an ENUM or SET column by adding new enumeration or set members to the end of the list of valid member values, as long as the storage side of the data type does not change. For example, adding a member to a SET column that has 8 members changes the required storage per value from 1 byte to 2 bytes; this will require a table copy. Adding members in the middle of the list causes renumbering of existing members, which requires a table copy.
ALTER TABLE with ADD PARTITION, DROP PARTITION, COALESCE PARTITION, REBUILD PARTITION, or REORGANIZE PARTITION does not create any temporary tables (except when used with NDB tables); however, these operations can and do create temporary partition files. ADD or DROP operations for RANGE or LIST partitions are immediate operations or nearly so. ADD or COALESCE operations for HASH or KEY partitions copy data between all partitions, unless LINEAR HASH or LINEAR KEY was used; this is effectively the same as creating a new table, although the ADD or COALESCE operation is performed partition by partition. REORGANIZE operations copy only changed partitions and do not touch unchanged ones. Renaming an index, except for InnoDB. Adding or dropping an index, for InnoDB and NDB.
You can force an ALTER TABLE operation that would otherwise not require a table copy to use the temporary table method (as supported in MySQL 5.0) by setting the old_alter_table system variable to ON. As of MySQL 5.5.11, you can also use ALTER TABLE tbl_name FORCE to perform a "null" alter operation that rebuilds the table. Previously the FORCE option was recognized but ignored. For NDBCLUSTER tables, operations that add and drop indexes on variable-width columns occur online, without any table copying and without blocking concurrent DML actions for most of their duration. See Section 13.1.7.2, "ALTER TABLE Online Operations". Usage Notes To use ALTER TABLE, you need ALTER, CREATE, and INSERT privileges for the table. Renaming a table requires ALTER and DROP on the old table, ALTER, CREATE, and INSERT on the new table. IGNORE is a MySQL extension to standard SQL. It controls how ALTER TABLE works if there are duplicates on unique keys in the new table or if warnings occur when strict mode is enabled. If IGNORE is not specified, the copy is aborted and rolled back if duplicate-key errors occur. If IGNORE is specified, only the first row is used of rows with duplicates on a unique key. The other conflicting rows are deleted. Incorrect values are truncated to the closest matching acceptable value. Pending INSERT DELAYED statements are lost if a table is write locked and ALTER TABLE is used to modify the table structure. table_option signifies a table option of the kind that can be used in the CREATE TABLE statement, such as ENGINE, AUTO_INCREMENT, or AVG_ROW_LENGTH. (Section 13.1.17, "CREATE TABLE Syntax", lists all table options.) However, ALTER TABLE ignores the DATA DIRECTORY and INDEX DIRECTORY table options. For example, to convert a table to be an InnoDB table, use this statement: ALTER TABLE t1 ENGINE = InnoDB; See Section 14.3.5.2, "Converting Tables from MyISAM to InnoDB" for considerations when switching tables to the InnoDB storage engine. When you specify an ENGINE clause, ALTER TABLE rebuilds the table. This is true even if the table already has the specified storage engine. The outcome of attempting to change a table's storage engine is affected by whether the desired storage engine is available and the setting of the NO_ENGINE_SUBSTITUTION SQL mode, as described in Section 5.1.7, "Server SQL Modes". To prevent inadvertent loss of data, ALTER TABLE cannot be used to change the storage engine of a table to MERGE or BLACKHOLE. To change the value of the AUTO_INCREMENT counter to be used for new rows, do this: ALTER TABLE t2 AUTO_INCREMENT = value; You cannot reset the counter to a value less than or equal to any that have already been used. For MyISAM, if the value is less than or equal to the maximum value currently in the AUTO_INCREMENT column, the value is reset to the current maximum plus one. For InnoDB, if the value is less than the current maximum value in the column, no error occurs and the current sequence value is not changed. You can issue multiple ADD, ALTER, DROP, and CHANGE clauses in a single ALTER TABLE statement, separated by commas. This is a MySQL extension to standard SQL, which permits only one of each clause per ALTER TABLE statement. For example, to drop multiple columns in a single statement, do this: ALTER TABLE t2 DROP COLUMN c, DROP COLUMN d; CHANGE col_name, DROP col_name, and DROP INDEX are MySQL extensions to standard SQL. The word COLUMN is optional and can be omitted. column_definition clauses use the same syntax for ADD and CHANGE as for CREATE TABLE. See Section 13.1.17, "CREATE TABLE Syntax". You can rename a column using a CHANGE old_col_name new_col_name column_definition clause. To do so, specify the old and new column names and the definition that the column currently has. For example, to rename an INTEGER column from a to b, you can do this: ALTER TABLE t1 CHANGE a b INTEGER; To change a column's type but not the name, CHANGE syntax still requires an old and new column name, even if they are the same. For example: ALTER TABLE t1 CHANGE b b BIGINT NOT NULL; You can also use MODIFY to change a column's type without renaming it: ALTER TABLE t1 MODIFY b BIGINT NOT NULL; MODIFY is an extension to ALTER TABLE for Oracle compatibility. When you use CHANGE or MODIFY, column_definition must include the data type and all attributes that should apply to the new column, other than index attributes such as PRIMARY KEY or UNIQUE. Attributes present in the original definition but not specified for the new definition are not carried forward. Suppose that a column col1 is defined as INT UNSIGNED DEFAULT 1 COMMENT 'my column' and you modify the column as follows: ALTER TABLE t1 MODIFY col1 BIGINT; The resulting column will be defined as BIGINT, but will not include the attributes UNSIGNED DEFAULT 1 COMMENT 'my column'. To retain them, the statement should be: ALTER TABLE t1 MODIFY col1 BIGINT UNSIGNED DEFAULT 1 COMMENT 'my column'; When you change a data type using CHANGE or MODIFY, MySQL tries to convert existing column values to the new type as well as possible. Warning This conversion may result in alteration of data. For example, if you shorten a string column, values may be truncated. To prevent the operation from succeeding if conversions to the new data type would result in loss of data, enable strict SQL mode before using ALTER TABLE (see Section 5.1.7, "Server SQL Modes"). To add a column at a specific position within a table row, use FIRST or AFTER col_name. The default is to add the column last. You can also use FIRST and AFTER in CHANGE or MODIFY operations to reorder columns within a table. ALTER ... SET DEFAULT or ALTER ... DROP DEFAULT specify a new default value for a column or remove the old default value, respectively. If the old default is removed and the column can be NULL, the new default is NULL. If the column cannot be NULL, MySQL assigns a default value as described in Section 11.5, "Data Type Default Values". DROP INDEX removes an index. This is a MySQL extension to standard SQL. See Section 13.1.24, "DROP INDEX Syntax". If you are unsure of the index name, use SHOW INDEX FROM tbl_name. If columns are dropped from a table, the columns are also removed from any index of which they are a part. If all columns that make up an index are dropped, the index is dropped as well. If you use CHANGE or MODIFY to shorten a column for which an index exists on the column, and the resulting column length is less than the index length, MySQL shortens the index automatically. If a table contains only one column, the column cannot be dropped. If what you intend is to remove the table, use DROP TABLE instead. DROP PRIMARY KEY drops the primary key. If there is no primary key, an error occurs. For information about the performance characteristics of primary keys, especially for InnoDB tables, see Section 8.3.2, "Using Primary Keys". If you add a UNIQUE INDEX or PRIMARY KEY to a table, MySQL stores it before any nonunique index to permit detection of duplicate keys as early as possible. Some storage engines permit you to specify an index type when creating an index. The syntax for the index_type specifier is USING type_name. For details about USING, see Section 13.1.13, "CREATE INDEX Syntax". The preferred position is after the column list. Support for use of the option before the column list will be removed in a future MySQL release. index_option values specify additional options for an index. USING is one such option. For details about permissible index_option values, see Section 13.1.13, "CREATE INDEX Syntax". After an ALTER TABLE statement, it may be necessary to run ANALYZE TABLE to update index cardinality information. See Section 13.7.5.23, "SHOW INDEX Syntax". ORDER BY enables you to create the new table with the rows in a specific order. Note that the table does not remain in this order after inserts and deletes. This option is useful primarily when you know that you are mostly to query the rows in a certain order most of the time. By using this option after major changes to the table, you might be able to get higher performance. In some cases, it might make sorting easier for MySQL if the table is in order by the column that you want to order it by later. ORDER BY syntax permits one or more column names to be specified for sorting, each of which optionally can be followed by ASC or DESC to indicate ascending or descending sort order, respectively. The default is ascending order. Only column names are permitted as sort criteria; arbitrary expressions are not permitted. This clause should be given last after any other clauses. ORDER BY does not make sense for InnoDB tables that contain a user-defined clustered index (PRIMARY KEY or NOT NULL UNIQUE index). InnoDB always orders table rows according to such an index if one is present. Note When used on a partitioned table, ALTER TABLE ... ORDER BY orders rows within each partition only. If you use ALTER TABLE on a MyISAM table, all nonunique indexes are created in a separate batch (as for REPAIR TABLE). This should make ALTER TABLE much faster when you have many indexes. This feature can be activated explicitly for a MyISAM table. ALTER TABLE ... DISABLE KEYS tells MySQL to stop updating nonunique indexes. ALTER TABLE ... ENABLE KEYS then should be used to re-create missing indexes. MySQL does this with a special algorithm that is much faster than inserting keys one by one, so disabling keys before performing bulk insert operations should give a considerable speedup. Using ALTER TABLE ... DISABLE KEYS requires the INDEX privilege in addition to the privileges mentioned earlier. While the nonunique indexes are disabled, they are ignored for statements such as SELECT and EXPLAIN that otherwise would use them. If ALTER TABLE for an InnoDB table results in changes to column values (for example, because a column is truncated), InnoDB's FOREIGN KEY constraint checks do not notice possible violations caused by changing the values. The FOREIGN KEY and REFERENCES clauses are supported by the InnoDB storage engine, which implements ADD [CONSTRAINT [symbol]] FOREIGN KEY (...) REFERENCES ... (...). See Section 14.3.5.4, "FOREIGN KEY Constraints". For other storage engines, the clauses are parsed but ignored. The CHECK clause is parsed but ignored by all storage engines. See Section 13.1.17, "CREATE TABLE Syntax". The reason for accepting but ignoring syntax clauses is for compatibility, to make it easier to port code from other SQL servers, and to run applications that create tables with references. See Section 1.8.5, "MySQL Differences from Standard SQL". Important The inline REFERENCES specifications where the references are defined as part of the column specification are silently ignored by InnoDB. InnoDB only accepts REFERENCES clauses defined as part of a separate FOREIGN KEY specification. InnoDB supports the use of ALTER TABLE to drop foreign keys: ALTER TABLE tbl_name DROP FOREIGN KEY fk_symbol; For more information, see Section 14.3.5.4, "FOREIGN KEY Constraints". You cannot add a foreign key and drop a foreign key in separate clauses of a single ALTER TABLE statement. You must use separate statements. For an InnoDB table that is created with its own tablespace in an .ibd file, that file can be discarded and imported. To discard the .ibd file, use this statement: ALTER TABLE tbl_name DISCARD TABLESPACE; This deletes the current .ibd file, so be sure that you have a backup first. Attempting to access the table while the tablespace file is discarded results in an error. To import the backup .ibd file back into the table, copy it into the database directory, and then issue this statement: ALTER TABLE tbl_name IMPORT TABLESPACE; The tablespace file must have been created on the server into which it is imported later. See Section 14.3.3, "Using Per-Table Tablespaces". To change the table default character set and all character columns (CHAR, VARCHAR, TEXT) to a new character set, use a statement like this: ALTER TABLE tbl_name CONVERT TO CHARACTER SET charset_name; For a column that has a data type of VARCHAR or one of the TEXT types, CONVERT TO CHARACTER SET will change the data type as necessary to ensure that the new column is long enough to store as many characters as the original column. For example, a TEXT column has two length bytes, which store the byte-length of values in the column, up to a maximum of 65,535. For a latin1 TEXT column, each character requires a single byte, so the column can store up to 65,535 characters. If the column is converted to utf8, each character might require up to three bytes, for a maximum possible length of 3 � 65,535 = 196,605 bytes. That length will not fit in a TEXT column's length bytes, so MySQL will convert the data type to MEDIUMTEXT, which is the smallest string type for which the length bytes can record a value of 196,605. Similarly, a VARCHAR column might be converted to MEDIUMTEXT. To avoid data type changes of the type just described, do not use CONVERT TO CHARACTER SET. Instead, use MODIFY to change individual columns. For example: ALTER TABLE t MODIFY latin1_text_col TEXT CHARACTER SET utf8;ALTER TABLE t MODIFY latin1_varchar_col VARCHAR(M) CHARACTER SET utf8; If you specify CONVERT TO CHARACTER SET binary, the CHAR, VARCHAR, and TEXT columns are converted to their corresponding binary string types (BINARY, VARBINARY, BLOB). This means that the columns no longer will have a character set and a subsequent CONVERT TO operation will not apply to them. If charset_name is DEFAULT, the database character set is used. Warning The CONVERT TO operation converts column values between the character sets. This is not what you want if you have a column in one character set (like latin1) but the stored values actually use some other, incompatible character set (like utf8). In this case, you have to do the following for each such column: ALTER TABLE t1 CHANGE c1 c1 BLOB;ALTER TABLE t1 CHANGE c1 c1 TEXT CHARACTER SET utf8; The reason this works is that there is no conversion when you convert to or from BLOB columns. To change only the default character set for a table, use this statement: ALTER TABLE tbl_name DEFAULT CHARACTER SET charset_name; The word DEFAULT is optional. The default character set is the character set that is used if you do not specify the character set for columns that you add to a table later (for example, with ALTER TABLE ... ADD column).
With the mysql_info() C API function, you can find out how many rows were copied by ALTER TABLE, and (when IGNORE is used) how many rows were deleted due to duplication of unique key values. See Section 22.8.3.35, "mysql_info()". 13.1.7.1. ALTER TABLE Partition Operations Partitioning-related clauses for ALTER TABLE can be used with partitioned tables for repartitioning, for adding, dropping, merging, and splitting partitions, and for performing partitioning maintenance. If an ALTER TABLE statement contains a partitioning operation, that must be the only operation specified in the statement. Simply using a partition_options clause with ALTER TABLE on a partitioned table repartitions the table according to the partitioning scheme defined by the partition_options. This clause always begins with PARTITION BY, and follows the same syntax and other rules as apply to the partition_options clause for CREATE TABLE (see Section 13.1.17, "CREATE TABLE Syntax", for more detailed information), and can also be used to partition an existing table that is not already partitioned. For example, consider a (nonpartitioned) table defined as shown here: CREATE TABLE t1 ( id INT, year_col INT); This table can be partitioned by HASH, using the id column as the partitioning key, into 8 partitions by means of this statement: ALTER TABLE t1 PARTITION BY HASH(id) PARTITIONS 8; The table that results from using an ALTER TABLE ... PARTITION BY statement must follow the same rules as one created using CREATE TABLE ... PARTITION BY. This includes the rules governing the relationship between any unique keys (including any primary key) that the table might have, and the column or columns used in the partitioning expression, as discussed in Section 18.5.1, "Partitioning Keys, Primary Keys, and Unique Keys". The CREATE TABLE ... PARTITION BY rules for specifying the number of partitions also apply to ALTER TABLE ... PARTITION BY. The partition_definition clause for ALTER TABLE ADD PARTITION supports the same options as the clause of the same name for the CREATE TABLE statement. (See Section 13.1.17, "CREATE TABLE Syntax", for the syntax and description.) Suppose that you have the partitioned table created as shown here: CREATE TABLE t1 ( id INT, year_col INT)PARTITION BY RANGE (year_col) ( PARTITION p0 VALUES LESS THAN (1991), PARTITION p1 VALUES LESS THAN (1995), PARTITION p2 VALUES LESS THAN (1999)); You can add a new partition p3 to this table for storing values less than 2002 as follows: ALTER TABLE t1 ADD PARTITION (PARTITION p3 VALUES LESS THAN (2002)); DROP PARTITION can be used to drop one or more RANGE or LIST partitions. This statement cannot be used with HASH or KEY partitions; instead, use COALESCE PARTITION (see below). Any data that was stored in the dropped partitions named in the partition_names list is discarded. For example, given the table t1 defined previously, you can drop the partitions named p0 and p1 as shown here: ALTER TABLE t1 DROP PARTITION p0, p1; ADD PARTITION and DROP PARTITION do not currently support IF [NOT] EXISTS. Renames of partitioned table are supported. You can rename individual partitions indirectly using ALTER TABLE ... REORGANIZE PARTITION; however, this operation makes a copy of the partition's data.. Beginning with MySQL 5.5.0, it is possible to delete rows from selected partitions using the TRUNCATE PARTITION option. This option takes a comma-separated list of one or more partition names. For example, consider the table t1 as defined here: CREATE TABLE t1 ( id INT, year_col INT)PARTITION BY RANGE (year_col) ( PARTITION p0 VALUES LESS THAN (1991), PARTITION p1 VALUES LESS THAN (1995), PARTITION p2 VALUES LESS THAN (1999), PARTITION p3 VALUES LESS THAN (2003), PARTITION p4 VALUES LESS THAN (2007)); To delete all rows from partition p0, you can use the following statement: ALTER TABLE t1 TRUNCATE PARTITION p0; The statement just shown has the same effect as the following DELETE statement: DELETE FROM t1 WHERE year_col < 1991; When truncating multiple partitions, the partitions do not have to be contiguous: This can greatly simplify delete operations on partitioned tables that would otherwise require very complex WHERE conditions if done with DELETE statements. For example, this statement deletes all rows from partitions p1 and p3: ALTER TABLE t1 TRUNCATE PARTITION p1, p3; An equivalent DELETE statement is shown here: DELETE FROM t1 WHERE (year_col >= 1991 AND year_col < 1995) OR (year_col >= 2003 AND year_col < 2007); You can also use the ALL keyword in place of the list of partition names; in this case, the statement acts on all partitions in the table. TRUNCATE PARTITION merely deletes rows; it does not alter the definition of the table itself, or of any of its partitions. Note TRUNCATE PARTITION does not work with subpartitions. You can verify that the rows were dropped by checking the INFORMATION_SCHEMA.PARTITIONS table, using a query such as this one: SELECT PARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 't1'; TRUNCATE PARTITION is supported only for partitioned tables that use the MyISAM, InnoDB, or MEMORY storage engine. It also works on BLACKHOLE tables (but has no effect). It is not supported for ARCHIVE tables. COALESCE PARTITION can be used with a table that is partitioned by HASH or KEY to reduce the number of partitions by number. Suppose that you have created table t2 using the following definition: CREATE TABLE t2 ( name VARCHAR (30), started DATE)PARTITION BY HASH( YEAR(started) )PARTITIONS 6; You can reduce the number of partitions used by t2 from 6 to 4 using the following statement: ALTER TABLE t2 COALESCE PARTITION 2; The data contained in the last number partitions will be merged into the remaining partitions. In this case, partitions 4 and 5 will be merged into the first 4 partitions (the partitions numbered 0, 1, 2, and 3). To change some but not all the partitions used by a partitioned table, you can use REORGANIZE PARTITION. This statement can be used in several ways: To merge a set of partitions into a single partition. This can be done by naming several partitions in the partition_names list and supplying a single definition for partition_definition. To split an existing partition into several partitions. You can accomplish this by naming a single partition for partition_names and providing multiple partition_definitions. To change the ranges for a subset of partitions defined using VALUES LESS THAN or the value lists for a subset of partitions defined using VALUES IN. This statement may also be used without the partition_names INTO (partition_definitions) option on tables that are automatically partitioned using HASH partitioning to force redistribution of data. (Currently, only NDBCLUSTER tables are automatically partitioned in this way.) This is useful in MySQL Cluster where, after you have added new MySQL Cluster data nodes online to an existing MySQL Cluster, you wish to redistribute existing MySQL Cluster table data to the new data nodes. In such cases, you should invoke the statement with the ONLINE option; in other words, as shown here: ALTER ONLINE TABLE table REORGANIZE PARTITION; You cannot perform other DDL concurrently with online table reorganization-that is, no other DDL statements can be issued while an ALTER ONLINE TABLE ... REORGANIZE PARTITION statement is executing. For more information about adding MySQL Cluster data nodes online, see Section 17.5.13, "Adding MySQL Cluster Data Nodes Online". ALTER ONLINE TABLE ... REORGANIZE PARTITION does not work with tables which were created using the MAX_ROWS option, because it uses the constant MAX_ROWS value specified in the original CREATE TABLE statement to determine the number of partitions required, so no new partitions are created. Beginning with MySQL Cluster NDB 7.2.6, you can use ALTER ONLINE TABLE ... MAX_ROWS=rows to increase the maxmimum number of rows for the table; after this, ALTER ONLINE TABLE ... REORGANIZE PARTITION can use this new, larger value to increase the number of partitions. The value of rows must be greater than the value specified for MAX_ROWS in the original CREATE TABLE statement for this to work. Attempting to use REORGANIZE PARTITION without the partition_names INTO (partition_definitions) option on explicitly partitioned tables results in the error REORGANIZE PARTITION without parameters can only be used on auto-partitioned tables using HASH partitioning.
Note For partitions that have not been explicitly named, MySQL automatically provides the default names p0, p1, p2, and so on. The same is true with regard to subpartitions. For more detailed information about and examples of ALTER TABLE ... REORGANIZE PARTITION statements, see Section 18.3.1, "Management of RANGE and LIST Partitions". Several additional options provide partition maintenance and repair functionality analogous to that implemented for nonpartitioned tables by statements such as CHECK TABLE and REPAIR TABLE (which are also supported for partitioned tables; see Section 13.7.2, "Table Maintenance Statements" for more information). These include ANALYZE PARTITION, CHECK PARTITION, OPTIMIZE PARTITION, REBUILD PARTITION, and REPAIR PARTITION. Each of these options takes a partition_names clause consisting of one or more names of partitions, separated by commas. The partitions must already exist in the table to be altered. You can also use the ALL keyword in place of partition_names, in which case the statement acts on all partitions in the table. For more information and examples, see Section 18.3.3, "Maintenance of Partitions". Prior to MySQL 5.5.30, it was not safe to execute multiple concurrent REBUILD TABLE operations on partitioned tables, whether on the same or different tables. (Bug #14589559, Bug #66645) Some MySQL storage engines, such as InnoDB, do not support per-partition optimization. For a partitioned table using such a storage engine, ALTER TABLE ... OPTIMIZE PARTITION rebuilds the entire table. This is a known issue. (Bug #42822) Beginning with MySQL 5.5.30, running this statement on such a table causes the entire table to rebuilt and analyzed, and an appropriate warning to be issued. (Bug #11751825) To work around this problem, use the statements ALTER TABLE ... REBUILD PARTITION and ALTER TABLE ... ANALYZE PARTITION instead. The ANALYZE PARTITION, CHECK PARTITION, OPTIMIZE PARTITION, and REPAIR PARTITION options are not permitted for tables which are not partitioned. REMOVE PARTITIONING enables you to remove a table's partitioning without otherwise affecting the table or its data. This option can be combined with other ALTER TABLE options such as those used to add, drop, or rename drop columns or indexes. Using the ENGINE option with ALTER TABLE changes the storage engine used by the table without affecting the partitioning.
Only a single instance of any one of the following options can be used in a given ALTER TABLE statement: PARTITION BY, ADD PARTITION, DROP PARTITION, TRUNCATE PARTITION, REORGANIZE PARTITION, or COALESCE PARTITION, ANALYZE PARTITION, CHECK PARTITION, OPTIMIZE PARTITION, REBUILD PARTITION, REMOVE PARTITIONING. For example, the following two statements are invalid: ALTER TABLE t1 ANALYZE PARTITION p1, ANALYZE PARTITION p2;ALTER TABLE t1 ANALYZE PARTITION p1, CHECK PARTITION p2; In the first case, you can analyze partitions p1 and p2 of table t1 concurrently using a single statement with a single ANALYZE PARTITION option that lists both of the partitions to be analyzed, like this: ALTER TABLE t1 ANALYZE PARTITION p1, p2; In the second case, it is not possible to perform ANALYZE and CHECK operations on different partitions of the same table concurrently. Instead, you must issue two separate statements, like this: ALTER TABLE t1 ANALYZE PARTITION p1;ALTER TABLE t1 CHECK PARTITION p2; 13.1.7.2. ALTER TABLE Online Operations Operations that add and drop indexes on variable-width columns of NDBCLUSTER tables occur online. Online operations are noncopying; that is, they do not require that indexes be re-created. They do not lock the table being altered from access my other API nodes in a MySQL Cluster (but see Limitations later in this section). Such operations do not require single user mode for NDBCLUSTER table alterations made in a cluster with multiple API nodes; transactions can continue uninterrupted during online DDL operations. The ONLINE keyword can be used to perform online ADD COLUMN, ADD INDEX (including CREATE INDEX statements), and DROP INDEX operations on NDBCLUSTER tables. Online renaming of NDBCLUSTER tables is also supported. The ONLINE and OFFLINE keywords are supported only in MySQL Cluster. For standard MySQL Server 5.5 releases: The server determines automatically whether an ADD INDEX or DROP INDEX operation can be (and is) performed online or offline; if the column is of a variable-width data type, the operation is performed online. It is not possible to override the server behavior in this regard. Attempting to use the ONLINE or OFFLINE keyword in an ALTER TABLE, CREATE INDEX, or DROP INDEX statement results in an error.
Currently you cannot add disk-based columns to NDBCLUSTER tables online. This means that, if you wish to add an in-memory column to an NDBCLUSTER table that uses a table-level STORAGE DISK option, you must declare the new column as using memory-based storage explicitly. For example-assuming that you have already created tablespace ts1-suppose that you create table t1 as follows: mysql> CREATE TABLE t1 ( > c1 INT NOT NULL PRIMARY KEY, > c2 VARCHAR(30) > ) > TABLESPACE ts1 STORAGE DISK > ENGINE NDBCLUSTER;Query OK, 0 rows affected (1.73 sec)Records: 0 Duplicates: 0 Warnings: 0 You can add a new in-memory column to this table online as shown here: mysql> ALTER ONLINE TABLE t1 ADD COLUMN c3 INT COLUMN_FORMAT DYNAMIC STORAGE MEMORY;Query OK, 0 rows affected (1.25 sec)Records: 0 Duplicates: 0 Warnings: 0 This statement fails if the STORAGE MEMORY option is omitted: mysql> ALTER ONLINE TABLE t1 ADD COLUMN c3 INT COLUMN_FORMAT DYNAMIC;ERROR 1235 (42000): This version of MySQL doesn't yet support'ALTER ONLINE TABLE t1 ADD COLUMN c3 INT COLUMN_FORMAT DYNAMIC' If you omit the COLUMN_FORMAT DYNAMIC option, the dynamic column format is employed automatically, but a warning is issued, as shown here: mysql> ALTER ONLINE TABLE t1 ADD COLUMN c3 INT STORAGE MEMORY;Query OK, 0 rows affected, 1 warning (1.17 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> SHOW WARNINGS;+---------+------+---------------------------------------------------------------+| Level | Code | Message |+---------+------+---------------------------------------------------------------+| Warning | 1478 | Converted FIXED field to DYNAMIC to enable on-line ADD COLUMN |+---------+------+---------------------------------------------------------------+1 row in set (0.00 sec)mysql> SHOW CREATE TABLE t1\G*************************** 1. row *************************** Table: t1Create Table: CREATE TABLE `t1` ( `c1` int(11) NOT NULL, `c2` varchar(30) DEFAULT NULL, `c3` int(11) /*!50120 STORAGE MEMORY */ /*!50120 COLUMN_FORMAT DYNAMIC */ DEFAULT NULL, `t4` int(11) /*!50120 STORAGE MEMORY */ DEFAULT NULL, PRIMARY KEY (`c1`)) /*!50100 TABLESPACE ts_1 STORAGE DISK */ ENGINE=ndbcluster DEFAULT CHARSET=latin11 row in set (0.03 sec) Note The STORAGE and COLUMN_FORMAT keywords are supported only in MySQL Cluster; in any other version of MySQL, attempting to use either of these keywords in a CREATE TABLE or ALTER TABLE statement results in an error. It is also possible to use the statement ALTER ONLINE TABLE ... REORGANIZE PARTITION with no partition_names INTO (partition_definitions) option on NDBCLUSTER tables. This can be used to redistribute MySQL Cluster data among new data nodes that have been added to the cluster online. For more information about this statement, see Section 13.1.7.1, "ALTER TABLE Partition Operations" For more information about adding data nodes online to a MySQL Cluster, see Section 17.5.13, "Adding MySQL Cluster Data Nodes Online". Limitations of NDBCLUSTER online operations Online DROP COLUMN operations are not supported. Online ALTER TABLE, CREATE INDEX, or DROP INDEX statements that add columns or add or drop indexes are subject to the following limitations: A given online ALTER TABLE can use only one of ADD COLUMN, ADD INDEX, or DROP INDEX. One or more columns can be added online in a single statement; only one index may be created or dropped online in a single statement. The table being altered is not locked with respect to API nodes other than the one on which an online ALTER TABLE ADD COLUMN, ADD INDEX, or DROP INDEX operation (or CREATE INDEX or DROP INDEX statement) is run. However, the table is locked against any other operations originating on the same API node while the online operation is being executed. The table to be altered must have an explicit primary key; the hidden primary key created by the NDBCLUSTER storage engine is not sufficient for this purpose. The storage engine used by the table cannot be changed online. When used with MySQL Cluster Disk Data tables, it is not possible to change the storage type (DISK or MEMORY) of a column online. This means, that when you add or drop an index in such a way that the operation would be performed online, and you want the storage type of the column or columns to be changed, you must use the OFFLINE keyword in the statement that adds or drops the index.
Columns to be added online must meet the following criteria: The columns must be dynamic; that is, it must be possible to create them using COLUMN_FORMAT DYNAMIC. If you omit the COLUMN_FORMAT DYNAMIC option, the dynamic column format is employed automatically. The columns must permit NULL values and not have any explicit default value other than NULL. Columns added online are automatically created as DEFAULT NULL, as can be seen here: mysql> CREATE TABLE t1 ( > c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY > ) ENGINE=NDBCLUSTER;Query OK, 0 rows affected (1.44 sec)mysql> ALTER ONLINE TABLE t1 > ADD COLUMN c2 INT, > ADD COLUMN c3 INT;Query OK, 0 rows affected, 2 warnings (0.93 sec)mysql> SHOW CREATE TABLE t1\G*************************** 1. row *************************** Table: t1Create Table: CREATE TABLE `t1` ( `c1` int(11) NOT NULL AUTO_INCREMENT, `c2` int(11) DEFAULT NULL, `c3` int(11) DEFAULT NULL, PRIMARY KEY (`c1`)) ENGINE=ndbcluster DEFAULT CHARSET=latin11 row in set (0.00 sec) The columns must be added following any existing columns. If you attempt to add a column online before any existing columns or using the FIRST keyword, the statement fails with an error. Existing table columns cannot be reordered online.
The preceding limitations do not apply to operations that merely rename tables or columns. For online ALTER TABLE operations on NDBCLUSTER tables, fixed-format columns are converted to dynamic when they are added online, or when indexes are created or dropped online, as shown here: mysql> CREATE TABLE t1 ( > c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY > ) ENGINE=NDBCLUSTER;Query OK, 0 rows affected (1.44 sec)mysql> ALTER ONLINE TABLE t1 ADD COLUMN c2 INT, ADD COLUMN c3 INT;Query OK, 0 rows affected, 2 warnings (0.93 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> SHOW WARNINGS;+---------+------+---------------------------------------------------------------+| Level | Code | Message |+---------+------+---------------------------------------------------------------+| Warning | 1475 | Converted FIXED field to DYNAMIC to enable on-line ADD COLUMN || Warning | 1475 | Converted FIXED field to DYNAMIC to enable on-line ADD COLUMN |+---------+------+---------------------------------------------------------------+2 rows in set (0.00 sec) Note Existing columns, including the table's primary key, need not be dynamic; only the column or columns to be added online must be dynamic. mysql> CREATE TABLE t2 ( > c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY COLUMN_FORMAT FIXED > ) ENGINE=NDBCLUSTER;Query OK, 0 rows affected (2.10 sec)mysql> ALTER ONLINE TABLE t2 ADD COLUMN c2 INT;Query OK, 0 rows affected, 1 warning (0.78 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> SHOW WARNINGS;+---------+------+---------------------------------------------------------------+| Level | Code | Message |+---------+------+---------------------------------------------------------------+| Warning | 1475 | Converted FIXED field to DYNAMIC to enable on-line ADD COLUMN |+---------+------+---------------------------------------------------------------+1 row in set (0.00 sec) Columns are not converted from FIXED to DYNAMIC column format by renaming operations. For more information about COLUMN_FORMAT, see Section 13.1.17, "CREATE TABLE Syntax". The KEY, CONSTRAINT, and IGNORE keywords are supported in ALTER TABLE statements using the ONLINE keyword. 13.1.7.3. ALTER TABLE Examples Begin with a table t1 that is created as shown here: CREATE TABLE t1 (a INTEGER,b CHAR(10)); To rename the table from t1 to t2: ALTER TABLE t1 RENAME t2; To change column a from INTEGER to TINYINT NOT NULL (leaving the name the same), and to change column b from CHAR(10) to CHAR(20) as well as renaming it from b to c: ALTER TABLE t2 MODIFY a TINYINT NOT NULL, CHANGE b c CHAR(20); To add a new TIMESTAMP column named d: ALTER TABLE t2 ADD d TIMESTAMP; To add an index on column d and a UNIQUE index on column a: ALTER TABLE t2 ADD INDEX (d), ADD UNIQUE (a); To remove column c: ALTER TABLE t2 DROP COLUMN c; To add a new AUTO_INCREMENT integer column named c: ALTER TABLE t2 ADD c INT UNSIGNED NOT NULL AUTO_INCREMENT, ADD PRIMARY KEY (c); We indexed c (as a PRIMARY KEY) because AUTO_INCREMENT columns must be indexed, and we declare c as NOT NULL because primary key columns cannot be NULL. For NDB tables, it is also possible to change the storage type used for a table or column. For example, consider an NDB table created as shown here: mysql> CREATE TABLE t1 (c1 INT) TABLESPACE ts_1 ENGINE NDB;Query OK, 0 rows affected (1.27 sec) To convert this table to disk-based storage, you can use the following ALTER TABLE statement: mysql> ALTER TABLE t1 TABLESPACE ts_1 STORAGE DISK;Query OK, 0 rows affected (2.99 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> SHOW CREATE TABLE t1\G*************************** 1. row *************************** Table: t1Create Table: CREATE TABLE `t1` ( `c1` int(11) DEFAULT NULL) /*!50100 TABLESPACE ts_1 STORAGE DISK */ENGINE=ndbcluster DEFAULT CHARSET=latin11 row in set (0.01 sec) It is not necessary that the tablespace was referenced when the table was originally created; however, the tablespace must be referenced by the ALTER TABLE: mysql> CREATE TABLE t2 (c1 INT) ts_1 ENGINE NDB;Query OK, 0 rows affected (1.00 sec)mysql> ALTER TABLE t2 STORAGE DISK;ERROR 1005 (HY000): Can't create table 'c.#sql-1750_3' (errno: 140)mysql> ALTER TABLE t2 TABLESPACE ts_1 STORAGE DISK;Query OK, 0 rows affected (3.42 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> SHOW CREATE TABLE t2\G*************************** 1. row *************************** Table: t1Create Table: CREATE TABLE `t2` ( `c1` int(11) DEFAULT NULL) /*!50100 TABLESPACE ts_1 STORAGE DISK */ENGINE=ndbcluster DEFAULT CHARSET=latin11 row in set (0.01 sec) To change the storage type of an individual column, you can use ALTER TABLE ... MODIFY [COLUMN]. For example, suppose you create a MySQL Cluster Disk Data table with two columns, using this CREATE TABLE statement: mysql> CREATE TABLE t3 (c1 INT, c2 INT) -> TABLESPACE ts_1 STORAGE DISK ENGINE NDB;Query OK, 0 rows affected (1.34 sec) To change column c2 from disk-based to in-memory storage, include a STORAGE MEMORY clause in the column definition used by the ALTER TABLE statement, as shown here: mysql> ALTER TABLE t3 MODIFY c2 INT STORAGE MEMORY;Query OK, 0 rows affected (3.14 sec)Records: 0 Duplicates: 0 Warnings: 0 You can make an in-memory column into a disk-based column by using STORAGE DISK in a similar fashion. Column c1 uses disk-based storage, since this is the default for the table (determined by the table-level STORAGE DISK clause in the CREATE TABLE statement). However, column c2 uses in-memory storage, as can be seen here in the output of SHOW CREATE TABLE: mysql> SHOW CREATE TABLE t3\G*************************** 1. row *************************** Table: t3Create Table: CREATE TABLE `t3` ( `c1` int(11) DEFAULT NULL, `c2` int(11) /*!50120 STORAGE MEMORY */ DEFAULT NULL) /*!50100 TABLESPACE ts_1 STORAGE DISK */ ENGINE=ndbcluster DEFAULT CHARSET=latin11 row in set (0.02 sec) When you add an AUTO_INCREMENT column, column values are filled in with sequence numbers automatically. For MyISAM tables, you can set the first sequence number by executing SET INSERT_ID=value before ALTER TABLE or by using the AUTO_INCREMENT=value table option. See Section 5.1.4, "Server System Variables". With MyISAM tables, if you do not change the AUTO_INCREMENT column, the sequence number is not affected. If you drop an AUTO_INCREMENT column and then add another AUTO_INCREMENT column, the numbers are resequenced beginning with 1. When replication is used, adding an AUTO_INCREMENT column to a table might not produce the same ordering of the rows on the slave and the master. This occurs because the order in which the rows are numbered depends on the specific storage engine used for the table and the order in which the rows were inserted. If it is important to have the same order on the master and slave, the rows must be ordered before assigning an AUTO_INCREMENT number. Assuming that you want to add an AUTO_INCREMENT column to the table t1, the following statements produce a new table t2 identical to t1 but with an AUTO_INCREMENT column: CREATE TABLE t2 (id INT AUTO_INCREMENT PRIMARY KEY)SELECT * FROM t1 ORDER BY col1, col2; This assumes that the table t1 has columns col1 and col2. This set of statements will also produce a new table t2 identical to t1, with the addition of an AUTO_INCREMENT column: CREATE TABLE t2 LIKE t1;ALTER TABLE t2 ADD id INT AUTO_INCREMENT PRIMARY KEY;INSERT INTO t2 SELECT * FROM t1 ORDER BY col1, col2; Important To guarantee the same ordering on both master and slave, all columns of t1 must be referenced in the ORDER BY clause. Regardless of the method used to create and populate the copy having the AUTO_INCREMENT column, the final step is to drop the original table and then rename the copy: DROP t1;ALTER TABLE t2 RENAME t1; 13.1.8. ALTER TABLESPACE SyntaxALTER TABLESPACE tablespace_name {ADD|DROP} DATAFILE 'file_name' [INITIAL_SIZE [=] size] [WAIT] ENGINE [=] engine_name This statement can be used either to add a new data file, or to drop a data file from a tablespace. The ADD DATAFILE variant enables you to specify an initial size using an INITIAL_SIZE clause, where size is measured in bytes; the default value is 128M (128 megabytes). You may optionally follow this integer value with a one-letter abbreviation for an order of magnitude, similar to those used in my.cnf. Generally, this is one of the letters M (for megabytes) or G (for gigabytes). Note All MySQL Cluster Disk Data objects share the same namespace. This means that each Disk Data object must be uniquely named (and not merely each Disk Data object of a given type). For example, you cannot have a tablespace and an data file with the same name, or an undo log file and a tablespace with the same name. On 32-bit systems, the maximum supported value for INITIAL_SIZE is 4G. (Bug #29186) INITIAL_SIZE is rounded, explicitly, as for CREATE TABLESPACE. Once a data file has been created, its size cannot be changed; however, you can add more data files to the tablespace using additional ALTER TABLESPACE ... ADD DATAFILE statements. Using DROP DATAFILE with ALTER TABLESPACE drops the data file 'file_name' from the tablespace. You cannot drop a data file from a tablespace which is in use by any table; in other words, the data file must be empty (no extents used). See Section 17.5.12.1, "MySQL Cluster Disk Data Objects". In addition, any data file to be dropped must previously have been added to the tablespace with CREATE TABLESPACE or ALTER TABLESPACE. Both ALTER TABLESPACE ... ADD DATAFILE and ALTER TABLESPACE ... DROP DATAFILE require an ENGINE clause which specifies the storage engine used by the tablespace. Currently, the only accepted values for engine_name are NDB and NDBCLUSTER. WAIT is parsed but otherwise ignored, and so has no effect in MySQL 5.5. It is intended for future expansion. When ALTER TABLESPACE ... ADD DATAFILE is used with ENGINE = NDB, a data file is created on each Cluster data node. You can verify that the data files were created and obtain information about them by querying the INFORMATION_SCHEMA.FILES table. For example, the following query shows all data files belonging to the tablespace named newts: mysql> SELECT LOGFILE_GROUP_NAME, FILE_NAME, EXTRA -> FROM INFORMATION_SCHEMA.FILES -> WHERE TABLESPACE_NAME = 'newts' AND FILE_TYPE = 'DATAFILE';+--------------------+--------------+----------------+| LOGFILE_GROUP_NAME | FILE_NAME | EXTRA |+--------------------+--------------+----------------+| lg_3 | newdata.dat | CLUSTER_NODE=3 || lg_3 | newdata.dat | CLUSTER_NODE=4 || lg_3 | newdata2.dat | CLUSTER_NODE=3 || lg_3 | newdata2.dat | CLUSTER_NODE=4 |+--------------------+--------------+----------------+2 rows in set (0.03 sec) See Section 20.29.1, "The INFORMATION_SCHEMA FILES Table". ALTER TABLESPACE is useful only with Disk Data storage for MySQL Cluster. See Section 17.5.12, "MySQL Cluster Disk Data Tables". 13.1.9. ALTER VIEW SyntaxALTER [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}] [DEFINER = { user | CURRENT_USER }] [SQL SECURITY { DEFINER | INVOKER }] VIEW view_name [(column_list)] AS select_statement [WITH [CASCADED | LOCAL] CHECK OPTION] This statement changes the definition of a view, which must exist. The syntax is similar to that for CREATE VIEW and the effect is the same as for CREATE OR REPLACE VIEW. See Section 13.1.20, "CREATE VIEW Syntax". This statement requires the CREATE VIEW and DROP privileges for the view, and some privilege for each column referred to in the SELECT statement. ALTER VIEW is permitted only to the definer or users with the SUPER privilege. 13.1.10. CREATE DATABASE SyntaxCREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name [create_specification] ...create_specification: [DEFAULT] CHARACTER SET [=] charset_name | [DEFAULT] COLLATE [=] collation_name CREATE DATABASE creates a database with the given name. To use this statement, you need the CREATE privilege for the database. CREATE SCHEMA is a synonym for CREATE DATABASE. An error occurs if the database exists and you did not specify IF NOT EXISTS. As of MySQL 5.5.3, CREATE DATABASE is not permitted within a session that has an active LOCK TABLES statement. create_specification options specify database characteristics. Database characteristics are stored in the db.opt file in the database directory. The CHARACTER SET clause specifies the default database character set. The COLLATE clause specifies the default database collation. Section 10.1, "Character Set Support", discusses character set and collation names. A database in MySQL is implemented as a directory containing files that correspond to tables in the database. Because there are no tables in a database when it is initially created, the CREATE DATABASE statement creates only a directory under the MySQL data directory and the db.opt file. Rules for permissible database names are given in Section 9.2, "Schema Object Names". If a database name contains special characters, the name for the database directory contains encoded versions of those characters as described in Section 9.2.3, "Mapping of Identifiers to File Names". If you manually create a directory under the data directory (for example, with mkdir), the server considers it a database directory and it shows up in the output of SHOW DATABASES. You can also use the mysqladmin program to create databases. See Section 4.5.2, "mysqladmin - Client for Administering a MySQL Server". 13.1.11. CREATE EVENT SyntaxCREATE [DEFINER = { user | CURRENT_USER }] EVENT [IF NOT EXISTS] event_name ON SCHEDULE schedule [ON COMPLETION [NOT] PRESERVE] [ENABLE | DISABLE | DISABLE ON SLAVE] [COMMENT 'comment'] DO event_body;schedule: AT timestamp [+ INTERVAL interval] ... | EVERY interval [STARTS timestamp [+ INTERVAL interval] ...] [ENDS timestamp [+ INTERVAL interval] ...]interval: quantity {YEAR | QUARTER | MONTH | DAY | HOUR | MINUTE | WEEK | SECOND | YEAR_MONTH | DAY_HOUR | DAY_MINUTE | DAY_SECOND | HOUR_MINUTE | HOUR_SECOND | MINUTE_SECOND} This statement creates and schedules a new event. The event will not run unless the Event Scheduler is enabled. For information about checking Event Scheduler status and enabling it if necessary, see Section 19.4.2, "Event Scheduler Configuration". CREATE EVENT requires the EVENT privilege for the schema in which the event is to be created. It might also require the SUPER privilege, depending on the DEFINER value, as described later in this section. The minimum requirements for a valid CREATE EVENT statement are as follows: The keywords CREATE EVENT plus an event name, which uniquely identifies the event in a database schema. An ON SCHEDULE clause, which determines when and how often the event executes. A DO clause, which contains the SQL statement to be executed by an event.
This is an example of a minimal CREATE EVENT statement: CREATE EVENT myevent ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 HOUR DO UPDATE myschema.mytable SET mycol = mycol + 1; The previous statement creates an event named myevent. This event executes once-one hour following its creation-by running an SQL statement that increments the value of the myschema.mytable table's mycol column by 1. The event_name must be a valid MySQL identifier with a maximum length of 64 characters. Event names are not case sensitive, so you cannot have two events named myevent and MyEvent in the same schema. In general, the rules governing event names are the same as those for names of stored routines. See Section 9.2, "Schema Object Names". An event is associated with a schema. If no schema is indicated as part of event_name, the default (current) schema is assumed. To create an event in a specific schema, qualify the event name with a schema using schema_name.event_name syntax. The DEFINER clause specifies the MySQL account to be used when checking access privileges at event execution time. If a user value is given, it should be a MySQL account specified as 'user_name'@'host_name' (the same format used in the GRANT statement), CURRENT_USER, or CURRENT_USER(). The default DEFINER value is the user who executes the CREATE EVENT statement. This is the same as specifying DEFINER = CURRENT_USER explicitly. If you specify the DEFINER clause, these rules determine the valid DEFINER user values: If you do not have the SUPER privilege, the only permitted user value is your own account, either specified literally or by using CURRENT_USER. You cannot set the definer to some other account. If you have the SUPER privilege, you can specify any syntactically valid account name. If the account does not actually exist, a warning is generated. Although it is possible to create an event with a nonexistent DEFINER account, an error occurs at event execution time if the account does not exist.
For more information about event security, see Section 19.6, "Access Control for Stored Programs and Views". Within an event, the CURRENT_USER() function returns the account used to check privileges at event execution time, which is the DEFINER user. For information about user auditing within events, see Section 6.3.11, "SQL-Based MySQL Account Activity Auditing". IF NOT EXISTS has the same meaning for CREATE EVENT as for CREATE TABLE: If an event named event_name already exists in the same schema, no action is taken, and no error results. (However, a warning is generated in such cases.) The ON SCHEDULE clause determines when, how often, and for how long the event_body defined for the event repeats. This clause takes one of two forms: AT timestamp is used for a one-time event. It specifies that the event executes one time only at the date and time given by timestamp, which must include both the date and time, or must be an expression that resolves to a datetime value. You may use a value of either the DATETIME or TIMESTAMP type for this purpose. If the date is in the past, a warning occurs, as shown here: mysql> SELECT NOW();+---------------------+| NOW() |+---------------------+| 2006-02-10 23:59:01 |+---------------------+1 row in set (0.04 sec)mysql> CREATE EVENT e_totals -> ON SCHEDULE AT '2006-02-10 23:59:00' -> DO INSERT INTO test.totals VALUES (NOW());Query OK, 0 rows affected, 1 warning (0.00 sec)mysql> SHOW WARNINGS\G*************************** 1. row *************************** Level: Note Code: 1588Message: Event execution time is in the past and ON COMPLETION NOT PRESERVE is set. The event was dropped immediately after creation. CREATE EVENT statements which are themselves invalid-for whatever reason-fail with an error. You may use CURRENT_TIMESTAMP to specify the current date and time. In such a case, the event acts as soon as it is created. To create an event which occurs at some point in the future relative to the current date and time-such as that expressed by the phrase "three weeks from now"-you can use the optional clause + INTERVAL interval. The interval portion consists of two parts, a quantity and a unit of time, and follows the same syntax rules that govern intervals used in the DATE_ADD() function (see Section 12.7, "Date and Time Functions". The units keywords are also the same, except that you cannot use any units involving microseconds when defining an event. With some interval types, complex time units may be used. For example, "two minutes and ten seconds" can be expressed as + INTERVAL '2:10' MINUTE_SECOND. You can also combine intervals. For example, AT CURRENT_TIMESTAMP + INTERVAL 3 WEEK + INTERVAL 2 DAY is equivalent to "three weeks and two days from now". Each portion of such a clause must begin with + INTERVAL. To repeat actions at a regular interval, use an EVERY clause. The EVERY keyword is followed by an interval as described in the previous discussion of the AT keyword. (+ INTERVAL is not used with EVERY.) For example, EVERY 6 WEEK means "every six weeks". Although + INTERVAL clauses are not permitted in an EVERY clause, you can use the same complex time units permitted in a + INTERVAL. An EVERY clause may contain an optional STARTS clause. STARTS is followed by a timestamp value that indicates when the action should begin repeating, and may also use + INTERVAL interval to specify an amount of time "from now". For example, EVERY 3 MONTH STARTS CURRENT_TIMESTAMP + INTERVAL 1 WEEK means "every three months, beginning one week from now". Similarly, you can express "every two weeks, beginning six hours and fifteen minutes from now" as EVERY 2 WEEK STARTS CURRENT_TIMESTAMP + INTERVAL '6:15' HOUR_MINUTE. Not specifying STARTS is the same as using STARTS CURRENT_TIMESTAMP-that is, the action specified for the event begins repeating immediately upon creation of the event. An EVERY clause may contain an optional ENDS clause. The ENDS keyword is followed by a timestamp value that tells MySQL when the event should stop repeating. You may also use + INTERVAL interval with ENDS; for instance, EVERY 12 HOUR STARTS CURRENT_TIMESTAMP + INTERVAL 30 MINUTE ENDS CURRENT_TIMESTAMP + INTERVAL 4 WEEK is equivalent to "every twelve hours, beginning thirty minutes from now, and ending four weeks from now". Not using ENDS means that the event continues executing indefinitely. ENDS supports the same syntax for complex time units as STARTS does. You may use STARTS, ENDS, both, or neither in an EVERY clause. If a repeating event does not terminate within its scheduling interval, the result may be multiple instances of the event executing simultaneously. If this is undesirable, you should institute a mechanism to prevent simultaneous instances. For example, you could use the GET_LOCK() function, or row or table locking.
The ON SCHEDULE clause may use expressions involving built-in MySQL functions and user variables to obtain any of the timestamp or interval values which it contains. You may not use stored functions or user-defined functions in such expressions, nor may you use any table references; however, you may use SELECT FROM DUAL. This is true for both CREATE EVENT and ALTER EVENT statements. References to stored functions, user-defined functions, and tables in such cases are specifically not permitted, and fail with an error (see Bug #22830). Times in the ON SCHEDULE clause are interpreted using the current session time_zone value. This becomes the event time zone; that is, the time zone that is used for event scheduling and is in effect within the event as it executes. These times are converted to UTC and stored along with the event time zone in the mysql.event table. This enables event execution to proceed as defined regardless of any subsequent changes to the server time zone or daylight saving time effects. For additional information about representation of event times, see Section 19.4.4, "Event Metadata". See also Section 13.7.5.19, "SHOW EVENTS Syntax", and Section 20.7, "The INFORMATION_SCHEMA EVENTS Table". Normally, once an event has expired, it is immediately dropped. You can override this behavior by specifying ON COMPLETION PRESERVE. Using ON COMPLETION NOT PRESERVE merely makes the default nonpersistent behavior explicit. You can create an event but prevent it from being active using the DISABLE keyword. Alternatively, you can use ENABLE to make explicit the default status, which is active. This is most useful in conjunction with ALTER EVENT (see Section 13.1.2, "ALTER EVENT Syntax"). A third value may also appear in place of ENABLED or DISABLED; DISABLE ON SLAVE is set for the status of an event on a replication slave to indicate that the event was created on the master and replicated to the slave, but is not executed on the slave. See Section 16.4.1.10, "Replication of Invoked Features". You may supply a comment for an event using a COMMENT clause. comment may be any string of up to 64 characters that you wish to use for describing the event. The comment text, being a string literal, must be surrounded by quotation marks. The DO clause specifies an action carried by the event, and consists of an SQL statement. Nearly any valid MySQL statement that can be used in a stored routine can also be used as the action statement for a scheduled event. (See Section E.1, "Restrictions on Stored Programs".) For example, the following event e_hourly deletes all rows from the sessions table once per hour, where this table is part of the site_activity schema: CREATE EVENT e_hourly ON SCHEDULE EVERY 1 HOUR COMMENT 'Clears out sessions table each hour.' DO DELETE FROM site_activity.sessions; MySQL stores the sql_mode system variable setting that is in effect at the time an event is created, and always executes the event with this setting in force, regardless of the current server SQL mode. A CREATE EVENT statement that contains an ALTER EVENT statement in its DO clause appears to succeed; however, when the server attempts to execute the resulting scheduled event, the execution fails with an error. Note Statements such as SELECT or SHOW that merely return a result set have no effect when used in an event; the output from these is not sent to the MySQL Monitor, nor is it stored anywhere. However, you can use statements such as SELECT ... INTO and INSERT INTO ... SELECT that store a result. (See the next example in this section for an instance of the latter.) The schema to which an event belongs is the default schema for table references in the DO clause. Any references to tables in other schemas must be qualified with the proper schema name. As with stored routines, you can use compound-statement syntax in the DO clause by using the BEGIN and END keywords, as shown here: delimiter |CREATE EVENT e_daily ON SCHEDULE EVERY 1 DAY COMMENT 'Saves total number of sessions then clears the table each day' DO BEGIN INSERT INTO site_activity.totals (time, total) SELECT CURRENT_TIMESTAMP, COUNT(*) FROM site_activity.sessions; DELETE FROM site_activity.sessions; END |delimiter ; This example uses the delimiter command to change the statement delimiter. See Section 19.1, "Defining Stored Programs". More complex compound statements, such as those used in stored routines, are possible in an event. This example uses local variables, an error handler, and a flow control construct: delimiter |CREATE EVENT e ON SCHEDULE EVERY 5 SECOND DO BEGIN DECLARE v INTEGER; DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN END; SET v = 0; WHILE v < 5 DO INSERT INTO t1 VALUES (0); UPDATE t2 SET s1 = s1 + 1; SET v = v + 1; END WHILE; END |delimiter ; There is no way to pass parameters directly to or from events; however, it is possible to invoke a stored routine with parameters within an event: CREATE EVENT e_call_myproc ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 DAY DO CALL myproc(5, 27); If an event's definer has the SUPER privilege, the event can read and write global variables. As granting this privilege entails a potential for abuse, extreme care must be taken in doing so. Generally, any statements that are valid in stored routines may be used for action statements executed by events. For more information about statements permissible within stored routines, see Section 19.2.1, "Stored Routine Syntax". You can create an event as part of a stored routine, but an event cannot be created by another event. 13.1.12. CREATE FUNCTION Syntax The CREATE FUNCTION statement is used to create stored functions and user-defined functions (UDFs): 13.1.13. CREATE INDEX SyntaxCREATE [ONLINE|OFFLINE] [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name [index_type] ON tbl_name (index_col_name,...) [index_option] ...index_col_name: col_name [(length)] [ASC | DESC]index_type: USING {BTREE | HASH}index_option: KEY_BLOCK_SIZE [=] value | index_type | WITH PARSER parser_name | COMMENT 'string' CREATE INDEX is mapped to an ALTER TABLE statement to create indexes. See Section 13.1.7, "ALTER TABLE Syntax". CREATE INDEX cannot be used to create a PRIMARY KEY; use ALTER TABLE instead. For more information about indexes, see Section 8.3.1, "How MySQL Uses Indexes". Normally, you create all indexes on a table at the time the table itself is created with CREATE TABLE. See Section 13.1.17, "CREATE TABLE Syntax". This guideline is especially important for InnoDB tables, where the primary key determines the physical layout of rows in the data file. CREATE INDEX enables you to add indexes to existing tables. A column list of the form (col1,col2,...) creates a multiple-column index. Index key values are formed by concatenating the values of the given columns. Indexes can be created that use only the leading part of column values, using col_name(length) syntax to specify an index prefix length: Prefixes can be specified for CHAR, VARCHAR, BINARY, and VARBINARY columns. BLOB and TEXT columns also can be indexed, but a prefix length must be given. Prefix lengths are given in characters for nonbinary string types and in bytes for binary string types. That is, index entries consist of the first length characters of each column value for CHAR, VARCHAR, and TEXT columns, and the first length bytes of each column value for BINARY, VARBINARY, and BLOB columns. For spatial columns, prefix values cannot be given, as described later in this section.
The statement shown here creates an index using the first 10 characters of the name column: CREATE INDEX part_of_name ON customer (name(10)); If names in the column usually differ in the first 10 characters, this index should not be much slower than an index created from the entire name column. Also, using column prefixes for indexes can make the index file much smaller, which could save a lot of disk space and might also speed up INSERT operations. Prefix support and lengths of prefixes (where supported) are storage engine dependent. For example, a prefix can be up to 1000 bytes long for MyISAM tables, and 767 bytes for InnoDB tables. The NDBCLUSTER storage engine does not support prefixes (see Section 17.1.6.6, "Unsupported or Missing Features in MySQL Cluster"). Note Prefix limits are measured in bytes, whereas the prefix length in CREATE INDEX statements is interpreted as number of characters for nonbinary data types (CHAR, VARCHAR, TEXT). Take this into account when specifying a prefix length for a column that uses a multi-byte character set. Indexes on variable-width columns of NDBCLUSTER tables are created online; that is, without any table copying. The table is not locked against access from other MySQL Cluster API nodes, although it is locked against other operations on the same API node for the duration of the operation. This is done automatically by the server whenever it determines that it is possible to do so; you do not have to use any special SQL syntax or server options to cause it to happen. In standard MySQL 5.5 releases, it is not possible to override the server when it determines that an index is to be created without table copying. In MySQL Cluster, you can create indexes offline (which causes the table to be locked to all API nodes in the cluster) using the OFFLINE keyword. The rules and limitations governing CREATE OFFLINE INDEX and CREATE ONLINE INDEX are the same as for ALTER OFFLINE TABLE ... ADD INDEX and ALTER ONLINE TABLE ... ADD INDEX. You cannot cause the noncopying creation of an index that would normally be created offline by using the ONLINE keyword: If it is not possible to perform the CREATE INDEX operation without table copying, the server ignores the ONLINE keyword. For more information, see Section 13.1.7.2, "ALTER TABLE Online Operations". Note The ONLINE and OFFLINE keywords are available only in MySQL Cluster; attempting to use these keywords in standard MySQL Server 5.5 releases results in a syntax error. A UNIQUE index creates a constraint such that all values in the index must be distinct. An error occurs if you try to add a new row with a key value that matches an existing row. For all engines, a UNIQUE index permits multiple NULL values for columns that can contain NULL. If you specify a prefix value for a column in a UNIQUE index, the column values must be unique within the prefix. FULLTEXT indexes are supported only for MyISAM tables and can include only CHAR, VARCHAR, and TEXT columns. Indexing always happens over the entire column; column prefix indexing is not supported and any prefix length is ignored if specified. See Section 12.9, "Full-Text Search Functions", for details of operation. The MyISAM, InnoDB, NDB, and ARCHIVE storage engines support spatial columns such as (POINT and GEOMETRY. (Section 12.17, "Spatial Extensions", describes the spatial data types.) However, support for spatial column indexing varies among engines. Spatial and nonspatial indexes are available according to the following rules. Spatial indexes (created using SPATIAL INDEX) have these characteristics: Available only for MyISAM tables. Specifying SPATIAL INDEX for other storage engines results in an error. Indexed columns must be NOT NULL. In MySQL 5.5, column prefix lengths are prohibited. The full width of each column is indexed.
Characteristics of nonspatial indexes (created with INDEX, UNIQUE, or PRIMARY KEY): Permitted for any storage engine that supports spatial columns except ARCHIVE. Columns can be NULL unless the index is a primary key. For each spatial column in a non-SPATIAL index except POINT columns, a column prefix length must be specified. (This is the same requirement as for indexed BLOB columns.) The prefix length is given in bytes. The index type for a non-SPATIAL index depends on the storage engine. Currently, B-tree is used.
In MySQL 5.5: You can add an index on a column that can have NULL values only if you are using the MyISAM, InnoDB, or MEMORY storage engine. You can add an index on a BLOB or TEXT column only if you are using the MyISAM, or InnoDB storage engine.
An index_col_name specification can end with ASC or DESC. These keywords are permitted for future extensions for specifying ascending or descending index value storage. Currently, they are parsed but ignored; index values are always stored in ascending order. Following the index column list, index options can be given. An index_option value can be any of the following: KEY_BLOCK_SIZE [=] value For compressed InnoDB tables, optionally specifies the size in bytes to use for pages. The value is treated as a hint; a different size could be used if necessary. A value of 0 represents the default compressed page size. See Section 14.4.3, "InnoDB Data Compression" for usage details. index_type Some storage engines permit you to specify an index type when creating an index. The permissible index type values supported by different storage engines are shown in the following table. Where multiple index types are listed, the first one is the default when no index type specifier is given. Example: CREATE TABLE lookup (id INT) ENGINE = MEMORY;CREATE INDEX id_index ON lookup (id) USING BTREE; BTREE indexes are implemented by the NDBCLUSTER storage engine as T-tree indexes. Note For indexes on NDB table columns, the USING option can be specified only for a unique index or primary key. USING HASH prevents the creation of an implicit ordered index; otherwise, creating a unique index or primary key on an NDB table automatically results in the creation of both an ordered index and a hash index, each of which indexes the same set of columns. This means that a query using a unique index or primary key on a NULL column is always handled by NDB with a full scan of the table. In particular, if you plan to use an IS NULL or IS NOT NULL condition involving a unique index or primary key column of an NDB table, you should create any such index without USING HASH. The index_type clause cannot be used together with SPATIAL INDEX. If you specify an index type that is not valid for a given storage engine, but there is another index type available that the engine can use without affecting query results, the engine uses the available type. The parser recognizes RTREE as a type name, but currently this cannot be specified for any storage engine. Use of this option before the ON tbl_name clause is deprecated; support for use of the option in this position will be removed in a future MySQL release. If an index_type option is given in both the earlier and later positions, the final option applies. TYPE type_name is recognized as a synonym for USING type_name. However, USING is the preferred form. WITH PARSER parser_name This option can be used only with FULLTEXT indexes. It associates a parser plugin with the index if full-text indexing and searching operations need special handling. See Section 23.2, "The MySQL Plugin API", for details on creating plugins. COMMENT 'string' As of MySQL 5.5.3, index definitions can include an optional comment of up to 1024 characters.
13.1.14. CREATE LOGFILE GROUP SyntaxCREATE LOGFILE GROUP logfile_group ADD UNDOFILE 'undo_file' [INITIAL_SIZE [=] initial_size] [UNDO_BUFFER_SIZE [=] undo_buffer_size] [REDO_BUFFER_SIZE [=] redo_buffer_size] [NODEGROUP [=] nodegroup_id] [WAIT] [COMMENT [=] comment_text] ENGINE [=] engine_name This statement creates a new log file group named logfile_group having a single UNDO file named 'undo_file'. A CREATE LOGFILE GROUP statement has one and only one ADD UNDOFILE clause. For rules covering the naming of log file groups, see Section 9.2, "Schema Object Names". Note All MySQL Cluster Disk Data objects share the same namespace. This means that each Disk Data object must be uniquely named (and not merely each Disk Data object of a given type). For example, you cannot have a tablespace and a log file group with the same name, or a tablespace and a data file with the same name. In MySQL Cluster NDB 7.2, you can have only one log file group per Cluster at any given time. (See Bug #16386) The optional INITIAL_SIZE parameter sets the UNDO file's initial size; if not specified, it defaults to 128M (128 megabytes). The optional UNDO_BUFFER_SIZE parameter sets the size used by the UNDO buffer for the log file group; The default value for UNDO_BUFFER_SIZE is 8M (eight megabytes); this value cannot exceed the amount of system memory available. Both of these parameters are specified in bytes. You may optionally follow either or both of these with a one-letter abbreviation for an order of magnitude, similar to those used in my.cnf. Generally, this is one of the letters M (for megabytes) or G (for gigabytes). The memory used for both INITIAL_SIZE and UNDO_BUFFER_SIZE comes from the global pool whose size is determined by the value of the SharedGlobalMemory data node configuration parameter. This includes any default value implied for these options by the setting of the InitialLogFileGroup data node configuration parameter. The maximum permitted for UNDO_BUFFER_SIZE is 600M. On 32-bit systems, the maximum supported value for INITIAL_SIZE is 4G. The minimum permitted value for INITIAL_SIZE is 1M. The ENGINE option determines the storage engine to be used by this log file group, with engine_name being the name of the storage engine. In MySQL 5.5, this must be NDB (or NDBCLUSTER). If ENGINE is not set, MySQL tries to use the engine specified by the default_storage_engine server system variable (formerly storage_engine). In any case, if the engine is not specified as NDB or NDBCLUSTER, the CREATE LOGFILE GROUP statement appears to succeed but actually fails to create the log file group, as shown here: mysql> CREATE LOGFILE GROUP lg1 -> ADD UNDOFILE 'undo.dat' INITIAL_SIZE = 10M;Query OK, 0 rows affected, 1 warning (0.00 sec)mysql> SHOW WARNINGS;+-------+------+-----------------------------------------------------------+| Level | Code | Message |+-------+------+-----------------------------------------------------------+| Error | 1478 | Table storage engine 'InnoDB' does not support the create || | | option 'TABLESPACE or LOGFILE GROUP' |+-------+------+-----------------------------------------------------------+1 row in set (0.00 sec)mysql> DROP LOGFILE GROUP lg1 ENGINE = NDB; ERROR 1529 (HY000): Failed to drop LOGFILE GROUPmysql> CREATE LOGFILE GROUP lg1 -> ADD UNDOFILE 'undo.dat' INITIAL_SIZE = 10M -> ENGINE = NDB;Query OK, 0 rows affected (2.97 sec) The fact that the CREATE LOGFILE GROUP statement does not actually return an error when a non-NDB storage engine is named, but rather appears to succeed, is a known issue which we hope to address in a future release of MySQL Cluster. REDO_BUFFER_SIZE, NODEGROUP, WAIT, and COMMENT are parsed but ignored, and so have no effect in MySQL 5.5. These options are intended for future expansion. When used with ENGINE [=] NDB, a log file group and associated UNDO log file are created on each Cluster data node. You can verify that the UNDO files were created and obtain information about them by querying the INFORMATION_SCHEMA.FILES table. For example: mysql> SELECT LOGFILE_GROUP_NAME, LOGFILE_GROUP_NUMBER, EXTRA -> FROM INFORMATION_SCHEMA.FILES -> WHERE FILE_NAME = 'undo_10.dat';+--------------------+----------------------+----------------+| LOGFILE_GROUP_NAME | LOGFILE_GROUP_NUMBER | EXTRA |+--------------------+----------------------+----------------+| lg_3 | 11 | CLUSTER_NODE=3 || lg_3 | 11 | CLUSTER_NODE=4 |+--------------------+----------------------+----------------+2 rows in set (0.06 sec) CREATE LOGFILE GROUP is useful only with Disk Data storage for MySQL Cluster. See Section 17.5.12, "MySQL Cluster Disk Data Tables". 13.1.15. CREATE PROCEDURE andCREATE FUNCTION SyntaxCREATE [DEFINER = { user | CURRENT_USER }] PROCEDURE sp_name ([proc_parameter[,...]]) [characteristic ...] routine_bodyCREATE [DEFINER = { user | CURRENT_USER }] FUNCTION sp_name ([func_parameter[,...]]) RETURNS type [characteristic ...] routine_bodyproc_parameter: [ IN | OUT | INOUT ] param_name typefunc_parameter: param_name typetype: Any valid MySQL data typecharacteristic: COMMENT 'string' | LANGUAGE SQL | [NOT] DETERMINISTIC | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA } | SQL SECURITY { DEFINER | INVOKER }routine_body: Valid SQL routine statement These statements create stored routines. By default, a routine is associated with the default database. To associate the routine explicitly with a given database, specify the name as db_name.sp_name when you create it. The CREATE FUNCTION statement is also used in MySQL to support UDFs (user-defined functions). See Section 23.3, "Adding New Functions to MySQL". A UDF can be regarded as an external stored function. Stored functions share their namespace with UDFs. See Section 9.2.4, "Function Name Parsing and Resolution", for the rules describing how the server interprets references to different kinds of functions. To invoke a stored procedure, use the CALL statement (see Section 13.2.1, "CALL Syntax"). To invoke a stored function, refer to it in an expression. The function returns a value during expression evaluation. CREATE PROCEDURE and CREATE FUNCTION require the CREATE ROUTINE privilege. They might also require the SUPER privilege, depending on the DEFINER value, as described later in this section. If binary logging is enabled, CREATE FUNCTION might require the SUPER privilege, as described in Section 19.7, "Binary Logging of Stored Programs". By default, MySQL automatically grants the ALTER ROUTINE and EXECUTE privileges to the routine creator. This behavior can be changed by disabling the automatic_sp_privileges system variable. See Section 19.2.2, "Stored Routines and MySQL Privileges". The DEFINER and SQL SECURITY clauses specify the security context to be used when checking access privileges at routine execution time, as described later in this section. If the routine name is the same as the name of a built-in SQL function, a syntax error occurs unless you use a space between the name and the following parenthesis when defining the routine or invoking it later. For this reason, avoid using the names of existing SQL functions for your own stored routines. The IGNORE_SPACE SQL mode applies to built-in functions, not to stored routines. It is always permissible to have spaces after a stored routine name, regardless of whether IGNORE_SPACE is enabled. The parameter list enclosed within parentheses must always be present. If there are no parameters, an empty parameter list of () should be used. Parameter names are not case sensitive. Each parameter is an IN parameter by default. To specify otherwise for a parameter, use the keyword OUT or INOUT before the parameter name. Note Specifying a parameter as IN, OUT, or INOUT is valid only for a PROCEDURE. For a FUNCTION, parameters are always regarded as IN parameters. An IN parameter passes a value into a procedure. The procedure might modify the value, but the modification is not visible to the caller when the procedure returns. An OUT parameter passes a value from the procedure back to the caller. Its initial value is NULL within the procedure, and its value is visible to the caller when the procedure returns. An INOUT parameter is initialized by the caller, can be modified by the procedure, and any change made by the procedure is visible to the caller when the procedure returns. For each OUT or INOUT parameter, pass a user-defined variable in the CALL statement that invokes the procedure so that you can obtain its value when the procedure returns. If you are calling the procedure from within another stored procedure or function, you can also pass a routine parameter or local routine variable as an IN or INOUT parameter. The following example shows a simple stored procedure that uses an OUT parameter: mysql> delimiter //mysql> CREATE PROCEDURE simpleproc (OUT param1 INT) -> BEGIN -> SELECT COUNT(*) INTO param1 FROM t; -> END//Query OK, 0 rows affected (0.00 sec)mysql> delimiter ;mysql> CALL simpleproc(@a);Query OK, 0 rows affected (0.00 sec)mysql> SELECT @a;+------+| @a |+------+| 3 |+------+1 row in set (0.00 sec) The example uses the mysql client delimiter command to change the statement delimiter from ; to // while the procedure is being defined. This enables the ; delimiter used in the procedure body to be passed through to the server rather than being interpreted by mysql itself. See Section 19.1, "Defining Stored Programs". The RETURNS clause may be specified only for a FUNCTION, for which it is mandatory. It indicates the return type of the function, and the function body must contain a RETURN value statement. If the RETURN statement returns a value of a different type, the value is coerced to the proper type. For example, if a function specifies an ENUM or SET value in the RETURNS clause, but the RETURN statement returns an integer, the value returned from the function is the string for the corresponding ENUM member of set of SET members. The following example function takes a parameter, performs an operation using an SQL function, and returns the result. In this case, it is unnecessary to use delimiter because the function definition contains no internal ; statement delimiters: mysql> CREATE FUNCTION hello (s CHAR(20))mysql> RETURNS CHAR(50) DETERMINISTIC -> RETURN CONCAT('Hello, ',s,'!');Query OK, 0 rows affected (0.00 sec)mysql> SELECT hello('world');+----------------+| hello('world') |+----------------+| Hello, world! |+----------------+1 row in set (0.00 sec) Parameter types and function return types can be declared to use any valid data type, except that the COLLATE attribute cannot be used prior to MySQL 5.5.3. As of 5.5.3, COLLATE can be used if preceded by the CHARACTER SET attribute. The routine_body consists of a valid SQL routine statement. This can be a simple statement such as SELECT or INSERT, or a compound statement written using BEGIN and END. Compound statements can contain declarations, loops, and other control structure statements. The syntax for these statements is described in Section 13.6, "MySQL Compound-Statement Syntax". MySQL permits routines to contain DDL statements, such as CREATE and DROP. MySQL also permits stored procedures (but not stored functions) to contain SQL transaction statements such as COMMIT. Stored functions may not contain statements that perform explicit or implicit commit or rollback. Support for these statements is not required by the SQL standard, which states that each DBMS vendor may decide whether to permit them. Statements that return a result set can be used within a stored procedure but not within a stored function. This prohibition includes SELECT statements that do not have an INTO var_list clause and other statements such as SHOW, EXPLAIN, and CHECK TABLE. For statements that can be determined at function definition time to return a result set, a Not allowed to return a result set from a function error occurs (ER_SP_NO_RETSET). For statements that can be determined only at runtime to return a result set, a PROCEDURE %s can't return a result set in the given context error occurs (ER_SP_BADSELECT). USE statements within stored routines are not permitted. When a routine is invoked, an implicit USE db_name is performed (and undone when the routine terminates). The causes the routine to have the given default database while it executes. References to objects in databases other than the routine default database should be qualified with the appropriate database name. For additional information about statements that are not permitted in stored routines, see Section E.1, "Restrictions on Stored Programs". For information about invoking stored procedures from within programs written in a language that has a MySQL interface, see Section 13.2.1, "CALL Syntax". MySQL stores the sql_mode system variable setting that is in effect at the time a routine is created, and always executes the routine with this setting in force, regardless of the server SQL mode in effect when the routine is invoked. The switch from the SQL mode of the invoker to that of the routine occurs after evaluation of arguments and assignment of the resulting values to routine parameters. If you define a routine in strict SQL mode but invoke it in nonstrict mode, assignment of arguments to routine parameters does not take place in strict mode. If you require that expressions passed to a routine be assigned in strict SQL mode, you should invoke the routine with strict mode in effect. The COMMENT characteristic is a MySQL extension, and may be used to describe the stored routine. This information is displayed by the SHOW CREATE PROCEDURE and SHOW CREATE FUNCTION statements. The LANGUAGE characteristic indicates the language in which the routine is written. The server ignores this characteristic; only SQL routines are supported. A routine is considered "deterministic" if it always produces the same result for the same input parameters, and "not deterministic" otherwise. If neither DETERMINISTIC nor NOT DETERMINISTIC is given in the routine definition, the default is NOT DETERMINISTIC. To declare that a function is deterministic, you must specify DETERMINISTIC explicitly. Assessment of the nature of a routine is based on the "honesty" of the creator: MySQL does not check that a routine declared DETERMINISTIC is free of statements that produce nondeterministic results. However, misdeclaring a routine might affect results or affect performance. Declaring a nondeterministic routine as DETERMINISTIC might lead to unexpected results by causing the optimizer to make incorrect execution plan choices. Declaring a deterministic routine as NONDETERMINISTIC might diminish performance by causing available optimizations not to be used. If binary logging is enabled, the DETERMINISTIC characteristic affects which routine definitions MySQL accepts. See Section 19.7, "Binary Logging of Stored Programs". A routine that contains the NOW() function (or its synonyms) or RAND() is nondeterministic, but it might still be replication-safe. For NOW(), the binary log includes the timestamp and replicates correctly. RAND() also replicates correctly as long as it is called only a single time during the execution of a routine. (You can consider the routine execution timestamp and random number seed as implicit inputs that are identical on the master and slave.) Several characteristics provide information about the nature of data use by the routine. In MySQL, these characteristics are advisory only. The server does not use them to constrain what kinds of statements a routine will be permitted to execute. CONTAINS SQL indicates that the routine does not contain statements that read or write data. This is the default if none of these characteristics is given explicitly. Examples of such statements are SET @x = 1 or DO RELEASE_LOCK('abc'), which execute but neither read nor write data. NO SQL indicates that the routine contains no SQL statements. READS SQL DATA indicates that the routine contains statements that read data (for example, SELECT), but not statements that write data. MODIFIES SQL DATA indicates that the routine contains statements that may write data (for example, INSERT or DELETE).
The SQL SECURITY characteristic can be DEFINER or INVOKER to specify the security context; that is, whether the routine executes using the privileges of the account named in the routine DEFINER clause or the user who invokes it. This account must have permission to access the database with which the routine is associated. The default value is DEFINER. The user who invokes the routine must have the EXECUTE privilege for it, as must the DEFINER account if the routine executes in definer security context. The DEFINER clause specifies the MySQL account to be used when checking access privileges at routine execution time for routines that have the SQL SECURITY DEFINER characteristic. If a user value is given for the DEFINER clause, it should be a MySQL account specified as 'user_name'@'host_name' (the same format used in the GRANT statement), CURRENT_USER, or CURRENT_USER(). The default DEFINER value is the user who executes the CREATE PROCEDURE or CREATE FUNCTION or statement. This is the same as specifying DEFINER = CURRENT_USER explicitly. If you specify the DEFINER clause, these rules determine the valid DEFINER user values: If you do not have the SUPER privilege, the only permitted user value is your own account, either specified literally or by using CURRENT_USER. You cannot set the definer to some other account. If you have the SUPER privilege, you can specify any syntactically valid account name. If the account does not actually exist, a warning is generated. Although it is possible to create a routine with a nonexistent DEFINER account, an error occurs at routine execution time if the SQL SECURITY value is DEFINER but the definer account does not exist.
For more information about stored routine security, see Section 19.6, "Access Control for Stored Programs and Views". Within a stored routine that is defined with the SQL SECURITY DEFINER characteristic, CURRENT_USER returns the routine's DEFINER value. For information about user auditing within stored routines, see Section 6.3.11, "SQL-Based MySQL Account Activity Auditing". Consider the following procedure, which displays a count of the number of MySQL accounts listed in the mysql.user table: CREATE DEFINER = 'admin'@'localhost' PROCEDURE account_count()BEGIN SELECT 'Number of accounts:', COUNT(*) FROM mysql.user;END; The procedure is assigned a DEFINER account of 'admin'@'localhost' no matter which user defines it. It executes with the privileges of that account no matter which user invokes it (because the default security characteristic is DEFINER). The procedure succeeds or fails depending on whether invoker has the EXECUTE privilege for it and 'admin'@'localhost' has the SELECT privilege for the mysql.user table. Now suppose that the procedure is defined with the SQL SECURITY INVOKER characteristic: CREATE DEFINER = 'admin'@'localhost' PROCEDURE account_count()SQL SECURITY INVOKERBEGIN SELECT 'Number of accounts:', COUNT(*) FROM mysql.user;END; The procedure still has a DEFINER of 'admin'@'localhost', but in this case, it executes with the privileges of the invoking user. Thus, the procedure succeeds or fails depending on whether the invoker has the EXECUTE privilege for it and the SELECT privilege for the mysql.user table. The server handles the data type of a routine parameter, local routine variable created with DECLARE, or function return value as follows: Assignments are checked for data type mismatches and overflow. Conversion and overflow problems result in warnings, or errors in strict SQL mode. Only scalar values can be assigned. For example, a statement such as SET x = (SELECT 1, 2) is invalid. For character data types, if there is a CHARACTER SET attribute in the declaration, the specified character set and its default collation is used. If the COLLATE attribute is also present, that collation is used rather than the default collation. If there is no CHARACTER SET attribute, the database character set and collation in effect at routine creation time are used. (The database character set and collation are given by the value of the character_set_database and collation_database system variables.) Prior to MySQL 5.5.3, if there is a CHARACTER SET attribute in the declaration, the COLLATE attribute is not supported, and the character set's default collation is used. (This includes use of BINARY, which in this context specifies the binary collation of the character set.) If there is no CHARACTER SET attribute, the database character set and its default collation (rather than the database collation) are used. If you change the database default character set or collation, stored routines that use the database defaults must be dropped and recreated so that they use the new defaults.
13.1.16. CREATE SERVER SyntaxCREATE SERVER server_name FOREIGN DATA WRAPPER wrapper_name OPTIONS (option [, option] ...)option: { HOST character-literal | DATABASE character-literal | USER character-literal | PASSWORD character-literal | SOCKET character-literal | OWNER character-literal | PORT numeric-literal } This statement creates the definition of a server for use with the FEDERATED storage engine. The CREATE SERVER statement creates a new row in the servers table in the mysql database. This statement requires the SUPER privilege. The server_name should be a unique reference to the server. Server definitions are global within the scope of the server, it is not possible to qualify the server definition to a specific database. server_name has a maximum length of 64 characters (names longer than 64 characters are silently truncated), and is case insensitive. You may specify the name as a quoted string. The wrapper_name should be mysql, and may be quoted with single quotation marks. Other values for wrapper_name are not currently supported. For each option you must specify either a character literal or numeric literal. Character literals are UTF-8, support a maximum length of 64 characters and default to a blank (empty) string. String literals are silently truncated to 64 characters. Numeric literals must be a number between 0 and 9999, default value is 0. Note The OWNER option is currently not applied, and has no effect on the ownership or operation of the server connection that is created. The CREATE SERVER statement creates an entry in the mysql.servers table that can later be used with the CREATE TABLE statement when creating a FEDERATED table. The options that you specify will be used to populate the columns in the mysql.servers table. The table columns are Server_name, Host, Db, Username, Password, Port and Socket. For example: CREATE SERVER sFOREIGN DATA WRAPPER mysqlOPTIONS (USER 'Remote', HOST '192.168.1.106', DATABASE 'test'); Be sure to specify all options necessary to establish a connection to the server. The user name, host name, and database name are mandatory. Other options might be required as well, such as password. The data stored in the table can be used when creating a connection to a FEDERATED table: CREATE TABLE t (s1 INT) ENGINE=FEDERATED CONNECTION='s'; For more information, see Section 14.11, "The FEDERATED Storage Engine". CREATE SERVER causes an implicit commit. In MySQL 5.5, CREATE SERVER is not written to the binary log, regardless of the logging format that is in use. 13.1.17. CREATE TABLE SyntaxCREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name (create_definition,...) [table_options] [partition_options] Or: CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name [(create_definition,...)] [table_options] [partition_options] select_statement Or: CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name { LIKE old_tbl_name | (LIKE old_tbl_name) }create_definition: col_name column_definition | [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (index_col_name,...) [index_option] ... | {INDEX|KEY} [index_name] [index_type] (index_col_name,...) [index_option] ... | [CONSTRAINT [symbol]] UNIQUE [INDEX|KEY] [index_name] [index_type] (index_col_name,...) [index_option] ... | {FULLTEXT|SPATIAL} [INDEX|KEY] [index_name] (index_col_name,...) [index_option] ... | [CONSTRAINT [symbol]] FOREIGN KEY [index_name] (index_col_name,...) reference_definition | CHECK (expr)column_definition: data_type [NOT NULL | NULL] [DEFAULT default_value] [AUTO_INCREMENT] [UNIQUE [KEY] | [PRIMARY] KEY] [COMMENT 'string'] [COLUMN_FORMAT {FIXED|DYNAMIC|DEFAULT}] [STORAGE {DISK|MEMORY|DEFAULT}] [reference_definition]data_type: BIT[(length)] | TINYINT[(length)] [UNSIGNED] [ZEROFILL] | SMALLINT[(length)] [UNSIGNED] [ZEROFILL] | MEDIUMINT[(length)] [UNSIGNED] [ZEROFILL] | INT[(length)] [UNSIGNED] [ZEROFILL] | INTEGER[(length)] [UNSIGNED] [ZEROFILL] | BIGINT[(length)] [UNSIGNED] [ZEROFILL] | REAL[(length,decimals)] [UNSIGNED] [ZEROFILL] | DOUBLE[(length,decimals)] [UNSIGNED] [ZEROFILL] | FLOAT[(length,decimals)] [UNSIGNED] [ZEROFILL] | DECIMAL[(length[,decimals])] [UNSIGNED] [ZEROFILL] | NUMERIC[(length[,decimals])] [UNSIGNED] [ZEROFILL] | DATE | TIME | TIMESTAMP | DATETIME | YEAR | CHAR[(length)] [CHARACTER SET charset_name] [COLLATE collation_name] | VARCHAR(length) [CHARACTER SET charset_name] [COLLATE collation_name] | BINARY[(length)] | VARBINARY(length) | TINYBLOB | BLOB | MEDIUMBLOB | LONGBLOB | TINYTEXT [BINARY] [CHARACTER SET charset_name] [COLLATE collation_name] | TEXT [BINARY] [CHARACTER SET charset_name] [COLLATE collation_name] | MEDIUMTEXT [BINARY] [CHARACTER SET charset_name] [COLLATE collation_name] | LONGTEXT [BINARY] [CHARACTER SET charset_name] [COLLATE collation_name] | ENUM(value1,value2,value3,...) [CHARACTER SET charset_name] [COLLATE collation_name] | SET(value1,value2,value3,...) [CHARACTER SET charset_name] [COLLATE collation_name] | spatial_typeindex_col_name: col_name [(length)] [ASC | DESC]index_type: USING {BTREE | HASH}index_option: KEY_BLOCK_SIZE [=] value | index_type | WITH PARSER parser_name | COMMENT 'string'reference_definition: REFERENCES tbl_name (index_col_name,...) [MATCH FULL | MATCH PARTIAL | MATCH SIMPLE] [ON DELETE reference_option] [ON UPDATE reference_option]reference_option: RESTRICT | CASCADE | SET NULL | NO ACTIONtable_options: table_option [[,] table_option] ...table_option: ENGINE [=] engine_name | AUTO_INCREMENT [=] value | AVG_ROW_LENGTH [=] value | [DEFAULT] CHARACTER SET [=] charset_name | CHECKSUM [=] {0 | 1} | [DEFAULT] COLLATE [=] collation_name | COMMENT [=] 'string' | CONNECTION [=] 'connect_string' | DATA DIRECTORY [=] 'absolute path to directory' | DELAY_KEY_WRITE [=] {0 | 1} | INDEX DIRECTORY [=] 'absolute path to directory' | INSERT_METHOD [=] { NO | FIRST | LAST } | KEY_BLOCK_SIZE [=] value | MAX_ROWS [=] value | MIN_ROWS [=] value | PACK_KEYS [=] {0 | 1 | DEFAULT} | PASSWORD [=] 'string' | ROW_FORMAT [=] {DEFAULT|DYNAMIC|FIXED|COMPRESSED|REDUNDANT|COMPACT} | TABLESPACE tablespace_name [STORAGE {DISK|MEMORY|DEFAULT}] | UNION [=] (tbl_name[,tbl_name]...)partition_options: PARTITION BY { [LINEAR] HASH(expr) | [LINEAR] KEY(column_list) | RANGE{(expr) | COLUMNS(column_list)} | LIST{(expr) | COLUMNS(column_list)} } [PARTITIONS num] [SUBPARTITION BY { [LINEAR] HASH(expr) | [LINEAR] KEY(column_list) } [SUBPARTITIONS num] ] [(partition_definition [, partition_definition] ...)]partition_definition: PARTITION partition_name [VALUES {LESS THAN {(expr | value_list) | MAXVALUE} | IN (value_list)}] [[STORAGE] ENGINE [=] engine_name] [COMMENT [=] 'comment_text' ] [DATA DIRECTORY [=] 'data_dir'] [INDEX DIRECTORY [=] 'index_dir'] [MAX_ROWS [=] max_number_of_rows] [MIN_ROWS [=] min_number_of_rows] [TABLESPACE [=] tablespace_name] [NODEGROUP [=] node_group_id] [(subpartition_definition [, subpartition_definition] ...)]subpartition_definition: SUBPARTITION logical_name [[STORAGE] ENGINE [=] engine_name] [COMMENT [=] 'comment_text' ] [DATA DIRECTORY [=] 'data_dir'] [INDEX DIRECTORY [=] 'index_dir'] [MAX_ROWS [=] max_number_of_rows] [MIN_ROWS [=] min_number_of_rows] [TABLESPACE [=] tablespace_name] [NODEGROUP [=] node_group_id]select_statement: [IGNORE | REPLACE] [AS] SELECT ... (Some valid select statement) CREATE TABLE creates a table with the given name. You must have the CREATE privilege for the table. Rules for permissible table names are given in Section 9.2, "Schema Object Names". By default, the table is created in the default database, using the InnoDB storage engine. An error occurs if the table exists, if there is no default database, or if the database does not exist. The table name can be specified as db_name.tbl_name to create the table in a specific database. This works regardless of whether there is a default database, assuming that the database exists. If you use quoted identifiers, quote the database and table names separately. For example, write `mydb`.`mytbl`, not `mydb.mytbl`. You can use the TEMPORARY keyword when creating a table. A TEMPORARY table is visible only to the current connection, and is dropped automatically when the connection is closed. This means that two different connections can use the same temporary table name without conflicting with each other or with an existing non-TEMPORARY table of the same name. (The existing table is hidden until the temporary table is dropped.) To create temporary tables, you must have the CREATE TEMPORARY TABLES privilege. Note CREATE TABLE does not automatically commit the current active transaction if you use the TEMPORARY keyword. The keywords IF NOT EXISTS prevent an error from occurring if the table exists. However, there is no verification that the existing table has a structure identical to that indicated by the CREATE TABLE statement. MySQL represents each table by an .frm table format (definition) file in the database directory. The storage engine for the table might create other files as well. For InnoDB tables, the file storage is controlled by the innodb_file_per_table configuration option. When this option is turned off, all InnoDB tables and indexes are stored in the system tablespace, represented by one or more .ibd files. For each InnoDB table created when this option is turned on, the table data and all associated indexes are stored in a .ibd file located inside the database directory. For MyISAM tables, the storage engine creates data and index files. Thus, for each MyISAM table tbl_name, there are three disk files. Chapter 14, Storage Engines, describes what files each storage engine creates to represent tables. If a table name contains special characters, the names for the table files contain encoded versions of those characters as described in Section 9.2.3, "Mapping of Identifiers to File Names". data_type represents the data type in a column definition. spatial_type represents a spatial data type. The data type syntax shown is representative only. For a full description of the syntax available for specifying column data types, as well as information about the properties of each type, see Chapter 11, Data Types, and Section 12.17, "Spatial Extensions". Some attributes do not apply to all data types. AUTO_INCREMENT applies only to integer and floating-point types. DEFAULT does not apply to the BLOB or TEXT types. If neither NULL nor NOT NULL is specified, the column is treated as though NULL had been specified. An integer or floating-point column can have the additional attribute AUTO_INCREMENT. When you insert a value of NULL (recommended) or 0 into an indexed AUTO_INCREMENT column, the column is set to the next sequence value. Typically this is value+1, where value is the largest value for the column currently in the table. AUTO_INCREMENT sequences begin with 1. To retrieve an AUTO_INCREMENT value after inserting a row, use the LAST_INSERT_ID() SQL function or the mysql_insert_id() C API function. See Section 12.14, "Information Functions", and Section 22.8.3.37, "mysql_insert_id()". If the NO_AUTO_VALUE_ON_ZERO SQL mode is enabled, you can store 0 in AUTO_INCREMENT columns as 0 without generating a new sequence value. See Section 5.1.7, "Server SQL Modes". Note There can be only one AUTO_INCREMENT column per table, it must be indexed, and it cannot have a DEFAULT value. An AUTO_INCREMENT column works properly only if it contains only positive values. Inserting a negative number is regarded as inserting a very large positive number. This is done to avoid precision problems when numbers "wrap" over from positive to negative and also to ensure that you do not accidentally get an AUTO_INCREMENT column that contains 0. For MyISAM tables, you can specify an AUTO_INCREMENT secondary column in a multiple-column key. See Section 3.6.9, "Using AUTO_INCREMENT". To make MySQL compatible with some ODBC applications, you can find the AUTO_INCREMENT value for the last inserted row with the following query: SELECT * FROM tbl_name WHERE auto_col IS NULL For information about InnoDB and AUTO_INCREMENT, see Section 14.3.5.3, "AUTO_INCREMENT Handling in InnoDB". For information about AUTO_INCREMENT and MySQL Replication, see Section 16.4.1.1, "Replication and AUTO_INCREMENT". Character data types (CHAR, VARCHAR, TEXT) can include CHARACTER SET and COLLATE attributes to specify the character set and collation for the column. For details, see Section 10.1, "Character Set Support". CHARSET is a synonym for CHARACTER SET. Example: CREATE TABLE t (c CHAR(20) CHARACTER SET utf8 COLLATE utf8_bin); MySQL 5.5 interprets length specifications in character column definitions in characters. (Versions before MySQL 4.1 interpreted them in bytes.) Lengths for BINARY and VARBINARY are in bytes. The DEFAULT clause specifies a default value for a column. With one exception, the default value must be a constant; it cannot be a function or an expression. This means, for example, that you cannot set the default for a date column to be the value of a function such as NOW() or CURRENT_DATE. The exception is that you can specify CURRENT_TIMESTAMP as the default for a TIMESTAMP column. See Section 11.3.5, "Automatic Initialization and Updating for TIMESTAMP". If a column definition includes no explicit DEFAULT value, MySQL determines the default value as described in Section 11.5, "Data Type Default Values". BLOB and TEXT columns cannot be assigned a default value. CREATE TABLE fails if a date-valued default is not correct according to the NO_ZERO_IN_DATE SQL mode, even if strict SQL mode is not enabled. For example, c1 DATE DEFAULT '2010-00-00' causes CREATE TABLE to fail with Invalid default value for 'c1'. A comment for a column can be specified with the COMMENT option, up to 1024 characters long (255 characters before MySQL 5.5.3). The comment is displayed by the SHOW CREATE TABLE and SHOW FULL COLUMNS statements. In MySQL Cluster, it is also possible to specify a data storage format for individual columns of NDB tables using COLUMN_FORMAT. Permissible column formats are FIXED, DYNAMIC, and DEFAULT. FIXED is used to specify fixed-width storage, DYNAMIC permits the column to be variable-width, and DEFAULT causes the column to use fixed-width or variable-width storage as determined by the column's data type (possibly overridden by a ROW_FORMAT specifier). For NDB tables, the default value for COLUMN_FORMAT is DEFAULT. COLUMN_FORMAT currently has no effect on columns of tables using storage engines other than NDB. The COLUMN_FORMAT keyword is supported only in the build of mysqld that is supplied with MySQL Cluster; it is not recognized in any other version of MySQL, where attempting to use COLUMN_FORMAT causes a syntax error. For NDB tables, it is also possible to specify whether the column is stored on disk or in memory by using a STORAGE clause. STORAGE DISK causes the column to be stored on disk, and STORAGE MEMORY causes in-memory storage to be used. The CREATE TABLE statement used must still include a TABLESPACE clause: mysql> CREATE TABLE t1 ( -> c1 INT STORAGE DISK, -> c2 INT STORAGE MEMORY -> ) ENGINE NDB;ERROR 1005 (HY000): Can't create table 'c.t1' (errno: 140)mysql> CREATE TABLE t1 ( -> c1 INT STORAGE DISK, -> c2 INT STORAGE MEMORY -> ) TABLESPACE ts_1 ENGINE NDB;Query OK, 0 rows affected (1.06 sec) For NDB tables, STORAGE DEFAULT is equivalent to STORAGE MEMORY. The STORAGE clause has no effect on tables using storage engines other than NDB. The STORAGE keyword is supported only in the build of mysqld that is supplied with MySQL Cluster; it is not recognized in any other version of MySQL, where any attempt to use the STORAGE keyword causes a syntax error. KEY is normally a synonym for INDEX. The key attribute PRIMARY KEY can also be specified as just KEY when given in a column definition. This was implemented for compatibility with other database systems. A UNIQUE index creates a constraint such that all values in the index must be distinct. An error occurs if you try to add a new row with a key value that matches an existing row. For all engines, a UNIQUE index permits multiple NULL values for columns that can contain NULL. A PRIMARY KEY is a unique index where all key columns must be defined as NOT NULL. If they are not explicitly declared as NOT NULL, MySQL declares them so implicitly (and silently). A table can have only one PRIMARY KEY. The name of a PRIMARY KEY is always PRIMARY, which thus cannot be used as the name for any other kind of index. If you do not have a PRIMARY KEY and an application asks for the PRIMARY KEY in your tables, MySQL returns the first UNIQUE index that has no NULL columns as the PRIMARY KEY. In InnoDB tables, keep the PRIMARY KEY short to minimize storage overhead for secondary indexes. Each secondary index entry contains a copy of the primary key columns for the corresponding row. (See Section 14.3.11, "InnoDB Table and Index Structures".) In the created table, a PRIMARY KEY is placed first, followed by all UNIQUE indexes, and then the nonunique indexes. This helps the MySQL optimizer to prioritize which index to use and also more quickly to detect duplicated UNIQUE keys. A PRIMARY KEY can be a multiple-column index. However, you cannot create a multiple-column index using the PRIMARY KEY key attribute in a column specification. Doing so only marks that single column as primary. You must use a separate PRIMARY KEY(index_col_name, ...) clause. If a PRIMARY KEY or UNIQUE index consists of only one column that has an integer type, you can also refer to the column as _rowid in SELECT statements. In MySQL, the name of a PRIMARY KEY is PRIMARY. For other indexes, if you do not assign a name, the index is assigned the same name as the first indexed column, with an optional suffix (_2, _3, ...) to make it unique. You can see index names for a table using SHOW INDEX FROM tbl_name. See Section 13.7.5.23, "SHOW INDEX Syntax". Some storage engines permit you to specify an index type when creating an index. The syntax for the index_type specifier is USING type_name. Example: CREATE TABLE lookup (id INT, INDEX USING BTREE (id)) ENGINE = MEMORY; The preferred position for USING is after the index column list. It can be given before the column list, but support for use of the option in that position is deprecated and will be removed in a future MySQL release. index_option values specify additional options for an index. USING is one such option. For details about permissible index_option values, see Section 13.1.13, "CREATE INDEX Syntax". For more information about indexes, see Section 8.3.1, "How MySQL Uses Indexes". In MySQL 5.5, only the MyISAM, InnoDB, and MEMORY storage engines support indexes on columns that can have NULL values. In other cases, you must declare indexed columns as NOT NULL or an error results. For CHAR, VARCHAR, BINARY, and VARBINARY columns, indexes can be created that use only the leading part of column values, using col_name(length) syntax to specify an index prefix length. BLOB and TEXT columns also can be indexed, but a prefix length must be given. Prefix lengths are given in characters for nonbinary string types and in bytes for binary string types. That is, index entries consist of the first length characters of each column value for CHAR, VARCHAR, and TEXT columns, and the first length bytes of each column value for BINARY, VARBINARY, and BLOB columns. Indexing only a prefix of column values like this can make the index file much smaller. See Section 8.3.4, "Column Indexes". Only the MyISAM and InnoDB storage engines support indexing on BLOB and TEXT columns. For example: CREATE TABLE test (blob_col BLOB, INDEX(blob_col(10))); Prefixes can be up to 1000 bytes long (767 bytes for InnoDB tables). Note that prefix limits are measured in bytes, whereas the prefix length in CREATE TABLE statements is interpreted as number of characters for nonbinary data types (CHAR, VARCHAR, TEXT). Take this into account when specifying a prefix length for a column that uses a multi-byte character set. An index_col_name specification can end with ASC or DESC. These keywords are permitted for future extensions for specifying ascending or descending index value storage. Currently, they are parsed but ignored; index values are always stored in ascending order. When you use ORDER BY or GROUP BY on a column in a SELECT, the server sorts values using only the initial number of bytes indicated by the max_sort_length system variable. You can create special FULLTEXT indexes, which are used for full-text searches. Only the MyISAM storage engine supports FULLTEXT indexes. They can be created only from CHAR, VARCHAR, and TEXT columns. Indexing always happens over the entire column; column prefix indexing is not supported and any prefix length is ignored if specified. See Section 12.9, "Full-Text Search Functions", for details of operation. A WITH PARSER clause can be specified as an index_option value to associate a parser plugin with the index if full-text indexing and searching operations need special handling. This clause is valid only for FULLTEXT indexes. See Section 23.2, "The MySQL Plugin API", for details on creating plugins. You can create SPATIAL indexes on spatial data types. Spatial types are supported only for MyISAM tables and indexed columns must be declared as NOT NULL. See Section 12.17, "Spatial Extensions". As of MySQL 5.5.3, index definitions can include an optional comment of up to 1024 characters. InnoDB tables support checking of foreign key constraints. See Section 14.3, "The InnoDB Storage Engine". Note that the FOREIGN KEY syntax in InnoDB is more restrictive than the syntax presented for the CREATE TABLE statement at the beginning of this section: The columns of the referenced table must always be explicitly named. InnoDB supports both ON DELETE and ON UPDATE actions on foreign keys. For the precise syntax, see Section 14.3.5.4, "FOREIGN KEY Constraints". For other storage engines, MySQL Server parses and ignores the FOREIGN KEY and REFERENCES syntax in CREATE TABLE statements. The CHECK clause is parsed but ignored by all storage engines. See Section 1.8.5.4, "Foreign Key Differences". Important For users familiar with the ANSI/ISO SQL Standard, please note that no storage engine, including InnoDB, recognizes or enforces the MATCH clause used in referential integrity constraint definitions. Use of an explicit MATCH clause will not have the specified effect, and also causes ON DELETE and ON UPDATE clauses to be ignored. For these reasons, specifying MATCH should be avoided. The MATCH clause in the SQL standard controls how NULL values in a composite (multiple-column) foreign key are handled when comparing to a primary key. InnoDB essentially implements the semantics defined by MATCH SIMPLE, which permit a foreign key to be all or partially NULL. In that case, the (child table) row containing such a foreign key is permitted to be inserted, and does not match any row in the referenced (parent) table. It is possible to implement other semantics using triggers. Additionally, MySQL and InnoDB require that the referenced columns be indexed for performance. However, the system does not enforce a requirement that the referenced columns be UNIQUE or be declared NOT NULL. The handling of foreign key references to nonunique keys or keys that contain NULL values is not well defined for operations such as UPDATE or DELETE CASCADE. You are advised to use foreign keys that reference only UNIQUE and NOT NULL keys. Furthermore, InnoDB does not recognize or support "inline REFERENCES specifications" (as defined in the SQL standard) where the references are defined as part of the column specification. InnoDB accepts REFERENCES clauses only when specified as part of a separate FOREIGN KEY specification. For other storage engines, MySQL Server parses and ignores foreign key specifications. There is a hard limit of 4096 columns per table, but the effective maximum may be less for a given table and depends on the factors discussed in Section E.10.4, "Table Column-Count and Row-Size Limits".
The TABLESPACE and STORAGE table options are employed only with NDBCLUSTER tables. The tablespace named tablespace_name must already have been created using CREATE TABLESPACE. STORAGE determines the type of storage used (disk or memory), and can be one of DISK, MEMORY, or DEFAULT. TABLESPACE ... STORAGE DISK assigns a table to a MySQL Cluster Disk Data tablespace. See Section 17.5.12, "MySQL Cluster Disk Data Tables", for more information. Important A STORAGE clause cannot be used in a CREATE TABLE statement without a TABLESPACE clause. The ENGINE table option specifies the storage engine for the table. The ENGINE table option takes the storage engine names shown in the following table. If a storage engine is specified that is not available, MySQL uses the default engine instead. Normally, this is MyISAM. For example, if a table definition includes the ENGINE=INNODB option but the MySQL server does not support INNODB tables, the table is created as a MyISAM table. This makes it possible to have a replication setup where you have transactional tables on the master but tables created on the slave are nontransactional (to get more speed). In MySQL 5.5, a warning occurs if the storage engine specification is not honored. Engine substitution can be controlled by the setting of the NO_ENGINE_SUBSTITUTION SQL mode, as described in Section 5.1.7, "Server SQL Modes". Note The older TYPE option was synonymous with ENGINE. TYPE was deprecated in MySQL 4.0 and removed in MySQL 5.5. When upgrading to MySQL 5.5 or later, you must convert existing applications that rely on TYPE to use ENGINE instead. The other table options are used to optimize the behavior of the table. In most cases, you do not have to specify any of them. These options apply to all storage engines unless otherwise indicated. Options that do not apply to a given storage engine may be accepted and remembered as part of the table definition. Such options then apply if you later use ALTER TABLE to convert the table to use a different storage engine. AUTO_INCREMENT The initial AUTO_INCREMENT value for the table. In MySQL 5.5, this works for MyISAM, MEMORY, InnoDB, and ARCHIVE tables. To set the first auto-increment value for engines that do not support the AUTO_INCREMENT table option, insert a "dummy" row with a value one less than the desired value after creating the table, and then delete the dummy row. For engines that support the AUTO_INCREMENT table option in CREATE TABLE statements, you can also use ALTER TABLE tbl_name AUTO_INCREMENT = N to reset the AUTO_INCREMENT value. The value cannot be set lower than the maximum value currently in the column. AVG_ROW_LENGTH An approximation of the average row length for your table. You need to set this only for large tables with variable-size rows. When you create a MyISAM table, MySQL uses the product of the MAX_ROWS and AVG_ROW_LENGTH options to decide how big the resulting table is. If you don't specify either option, the maximum size for MyISAM data and index files is 256TB by default. (If your operating system does not support files that large, table sizes are constrained by the file size limit.) If you want to keep down the pointer sizes to make the index smaller and faster and you don't really need big files, you can decrease the default pointer size by setting the myisam_data_pointer_size system variable. (See Section 5.1.4, "Server System Variables".) If you want all your tables to be able to grow above the default limit and are willing to have your tables slightly slower and larger than necessary, you can increase the default pointer size by setting this variable. Setting the value to 7 permits table sizes up to 65,536TB. [DEFAULT] CHARACTER SET Specify a default character set for the table. CHARSET is a synonym for CHARACTER SET. If the character set name is DEFAULT, the database character set is used. CHECKSUM Set this to 1 if you want MySQL to maintain a live checksum for all rows (that is, a checksum that MySQL updates automatically as the table changes). This makes the table a little slower to update, but also makes it easier to find corrupted tables. The CHECKSUM TABLE statement reports the checksum. (MyISAM only.) [DEFAULT] COLLATE Specify a default collation for the table. COMMENT A comment for the table, up to 2048 characters long (60 characters before MySQL 5.5.3). CONNECTION The connection string for a FEDERATED table. Note Older versions of MySQL used a COMMENT option for the connection string. DATA DIRECTORY, INDEX DIRECTORY By using DATA DIRECTORY='directory' or INDEX DIRECTORY='directory' you can specify where the MyISAM storage engine should put a table's data file and index file. The directory must be the full path name to the directory, not a relative path. Important Table-level DATA DIRECTORY and INDEX DIRECTORY options are ignored for partitioned tables. (Bug #32091) These options work only when you are not using the --skip-symbolic-links option. Your operating system must also have a working, thread-safe realpath() call. See Section 8.11.3.1.2, "Using Symbolic Links for MyISAM Tables on Unix", for more complete information. If a MyISAM table is created with no DATA DIRECTORY option, the .MYD file is created in the database directory. By default, if MyISAM finds an existing .MYD file in this case, it overwrites it. The same applies to .MYI files for tables created with no INDEX DIRECTORY option. To suppress this behavior, start the server with the --keep_files_on_create option, in which case MyISAM will not overwrite existing files and returns an error instead. If a MyISAM table is created with a DATA DIRECTORY or INDEX DIRECTORY option and an existing .MYD or .MYI file is found, MyISAM always returns an error. It will not overwrite a file in the specified directory. Important You cannot use path names that contain the MySQL data directory with DATA DIRECTORY or INDEX DIRECTORY. This includes partitioned tables and individual table partitions. (See Bug #32167.) DELAY_KEY_WRITE Set this to 1 if you want to delay key updates for the table until the table is closed. See the description of the delay_key_write system variable in Section 5.1.4, "Server System Variables". (MyISAM only.) INSERT_METHOD If you want to insert data into a MERGE table, you must specify with INSERT_METHOD the table into which the row should be inserted. INSERT_METHOD is an option useful for MERGE tables only. Use a value of FIRST or LAST to have inserts go to the first or last table, or a value of NO to prevent inserts. See Section 14.10, "The MERGE Storage Engine". KEY_BLOCK_SIZE For compressed InnoDB tables, optionally specifies the size in bytes to use for pages. The value is treated as a hint; a different size could be used if necessary. A value of 0 represents that the default compressed page size. See Section 14.4.3, "InnoDB Data Compression" for usage details. Individual index definitions can specify a KEY_BLOCK_SIZE value of their own to override the table value. MAX_ROWS The maximum number of rows you plan to store in the table. This is not a hard limit, but rather a hint to the storage engine that the table must be able to store at least this many rows. The NDB storage engine treats this value as a maxmimum. If you plan to create very large MySQL Cluster tables (containing millions of rows), you should use this option to insure that NDB allocates sufficient number of index slots in the hash table used for storing hashes of the table's primary keys by setting MAX_ROWS = 2 * rows, where rows is the number of rows that you expect to insert into the table. The maximum MAX_ROWS value is 4294967295; larger values are truncated to this limit. MIN_ROWS The minimum number of rows you plan to store in the table. The MEMORY storage engine uses this option as a hint about memory use. PACK_KEYS PACK_KEYS takes effect only with MyISAM tables. Set this option to 1 if you want to have smaller indexes. This usually makes updates slower and reads faster. Setting the option to 0 disables all packing of keys. Setting it to DEFAULT tells the storage engine to pack only long CHAR, VARCHAR, BINARY, or VARBINARY columns. If you do not use PACK_KEYS, the default is to pack strings, but not numbers. If you use PACK_KEYS=1, numbers are packed as well. When packing binary number keys, MySQL uses prefix compression: Every key needs one extra byte to indicate how many bytes of the previous key are the same for the next key. The pointer to the row is stored in high-byte-first order directly after the key, to improve compression.
This means that if you have many equal keys on two consecutive rows, all following "same" keys usually only take two bytes (including the pointer to the row). Compare this to the ordinary case where the following keys takes storage_size_for_key + pointer_size (where the pointer size is usually 4). Conversely, you get a significant benefit from prefix compression only if you have many numbers that are the same. If all keys are totally different, you use one byte more per key, if the key is not a key that can have NULL values. (In this case, the packed key length is stored in the same byte that is used to mark if a key is NULL.) PASSWORD This option is unused. If you have a need to scramble your .frm files and make them unusable to any other MySQL server, please contact our sales department. ROW_FORMAT Defines the physical format in which the rows are stored. The choices differ depending on the storage engine used for the table. For InnoDB tables: For MyISAM tables, the option value can be FIXED or DYNAMIC for static or variable-length row format. myisampack sets the type to COMPRESSED. See Section 14.5.3, "MyISAM Table Storage Formats". Note When executing a CREATE TABLE statement, if you specify a row format which is not supported by the storage engine that is used for the table, the table is created using that storage engine's default row format. The information reported in this column in response to SHOW TABLE STATUS is the actual row format used. This may differ from the value in the Create_options column because the original CREATE TABLE definition is retained during creation. UNION UNION is used when you want to access a collection of identical MyISAM tables as one. This works only with MERGE tables. See Section 14.10, "The MERGE Storage Engine". You must have SELECT, UPDATE, and DELETE privileges for the tables you map to a MERGE table. Note Formerly, all tables used had to be in the same database as the MERGE table itself. This restriction no longer applies.
partition_options can be used to control partitioning of the table created with CREATE TABLE. Important Not all options shown in the syntax for partition_options at the beginning of this section are available for all partitioning types. Please see the listings for the following individual types for information specific to each type, and see Chapter 18, Partitioning, for more complete information about the workings of and uses for partitioning in MySQL, as well as additional examples of table creation and other statements relating to MySQL partitioning. If used, a partition_options clause begins with PARTITION BY. This clause contains the function that is used to determine the partition; the function returns an integer value ranging from 1 to num, where num is the number of partitions. (The maximum number of user-defined partitions which a table may contain is 1024; the number of subpartitions-discussed later in this section-is included in this maximum.) The choices that are available for this function in MySQL 5.5 are shown in the following list: HASH(expr): Hashes one or more columns to create a key for placing and locating rows. expr is an expression using one or more table columns. This can be any valid MySQL expression (including MySQL functions) that yields a single integer value. For example, these are both valid CREATE TABLE statements using PARTITION BY HASH: CREATE TABLE t1 (col1 INT, col2 CHAR(5)) PARTITION BY HASH(col1);CREATE TABLE t1 (col1 INT, col2 CHAR(5), col3 DATETIME) PARTITION BY HASH ( YEAR(col3) ); You may not use either VALUES LESS THAN or VALUES IN clauses with PARTITION BY HASH. PARTITION BY HASH uses the remainder of expr divided by the number of partitions (that is, the modulus). For examples and additional information, see Section 18.2.4, "HASH Partitioning". The LINEAR keyword entails a somewhat different algorithm. In this case, the number of the partition in which a row is stored is calculated as the result of one or more logical AND operations. For discussion and examples of linear hashing, see Section 18.2.4.1, "LINEAR HASH Partitioning". KEY(column_list): This is similar to HASH, except that MySQL supplies the hashing function so as to guarantee an even data distribution. The column_list argument is simply a list of 1 or more table columns (maximum: 16). This example shows a simple table partitioned by key, with 4 partitions: CREATE TABLE tk (col1 INT, col2 CHAR(5), col3 DATE) PARTITION BY KEY(col3) PARTITIONS 4; For tables that are partitioned by key, you can employ linear partitioning by using the LINEAR keyword. This has the same effect as with tables that are partitioned by HASH. That is, the partition number is found using the & operator rather than the modulus (see Section 18.2.4.1, "LINEAR HASH Partitioning", and Section 18.2.5, "KEY Partitioning", for details). This example uses linear partitioning by key to distribute data between 5 partitions: CREATE TABLE tk (col1 INT, col2 CHAR(5), col3 DATE) PARTITION BY LINEAR KEY(col3) PARTITIONS 5; You may not use either VALUES LESS THAN or VALUES IN clauses with PARTITION BY KEY. RANGE(expr): In this case, expr shows a range of values using a set of VALUES LESS THAN operators. When using range partitioning, you must define at least one partition using VALUES LESS THAN. You cannot use VALUES IN with range partitioning. Note For tables partitioned by RANGE, VALUES LESS THAN must be used with either an integer literal value or an expression that evaluates to a single integer value. In MySQL 5.5, you can overcome this limitation in a table that is defined using PARTITION BY RANGE COLUMNS, as described later in this section. Suppose that you have a table that you wish to partition on a column containing year values, according to the following scheme. A table implementing such a partitioning scheme can be realized by the CREATE TABLE statement shown here: CREATE TABLE t1 ( year_col INT, some_data INT)PARTITION BY RANGE (year_col) ( PARTITION p0 VALUES LESS THAN (1991), PARTITION p1 VALUES LESS THAN (1995), PARTITION p2 VALUES LESS THAN (1999), PARTITION p3 VALUES LESS THAN (2002), PARTITION p4 VALUES LESS THAN (2006), PARTITION p5 VALUES LESS THAN MAXVALUE); PARTITION ... VALUES LESS THAN ... statements work in a consecutive fashion. VALUES LESS THAN MAXVALUE works to specify "leftover" values that are greater than the maximum value otherwise specified. Note that VALUES LESS THAN clauses work sequentially in a manner similar to that of the case portions of a switch ... case block (as found in many programming languages such as C, Java, and PHP). That is, the clauses must be arranged in such a way that the upper limit specified in each successive VALUES LESS THAN is greater than that of the previous one, with the one referencing MAXVALUE coming last of all in the list. RANGE COLUMNS(column_list): This variant on RANGE was introduced in MySQL 5.5.0 to facilitate partition pruning for queries using range conditions on multiple columns (that is, having conditions such as WHERE a = 1 AND b < 10 or WHERE a = 1 AND b = 10 AND c < 10). It enables you to specify value ranges in multiple columns by using a list of columns in the COLUMNS clause and a set of column values in each PARTITION ... VALUES LESS THAN (value_list) partition definition clause. (In the simplest case, this set consists of a single column.) The maximum number of columns that can be referenced in the column_list and value_list is 16. The column_list used in the COLUMNS clause may contain only names of columns; each column in the list must be one of the following MySQL data types: the integer types; the string types; and time or date column types. Columns using BLOB, TEXT, SET, ENUM, BIT, or spatial data types are not permitted; columns that use floating-point number types are also not permitted. You also may not use functions or arithmetic expressions in the COLUMNS clause. The VALUES LESS THAN clause used in a partition definition must specify a literal value for each column that appears in the COLUMNS() clause; that is, the list of values used for each VALUES LESS THAN clause must contain the same number of values as there are columns listed in the COLUMNS clause. An attempt to use more or fewer values in a VALUES LESS THAN clause than there are in the COLUMNS clause causes the statement to fail with the error Inconsistency in usage of column lists for partitioning.... You cannot use NULL for any value appearing in VALUES LESS THAN. It is possible to use MAXVALUE more than once for a given column other than the first, as shown in this example: CREATE TABLE rc ( a INT NOT NULL, b INT NOT NULL)PARTITION BY RANGE COLUMNS(a,b) ( PARTITION p0 VALUES LESS THAN (10,5), PARTITION p1 VALUES LESS THAN (20,10), PARTITION p2 VALUES LESS THAN (MAXVALUE,15), PARTITION p3 VALUES LESS THAN (MAXVALUE,MAXVALUE)); Each value used in a VALUES LESS THAN value list must match the type of the corresponding column exactly; no conversion is made. For example, you cannot use the string '1' for a value that matches a column that uses an integer type (you must use the numeral 1 instead), nor can you use the numeral 1 for a value that matches a column that uses a string type (in such a case, you must use a quoted string: '1'). For more information, see Section 18.2.1, "RANGE Partitioning", and Section 18.4, "Partition Pruning". LIST(expr): This is useful when assigning partitions based on a table column with a restricted set of possible values, such as a state or country code. In such a case, all rows pertaining to a certain state or country can be assigned to a single partition, or a partition can be reserved for a certain set of states or countries. It is similar to RANGE, except that only VALUES IN may be used to specify permissible values for each partition. VALUES IN is used with a list of values to be matched. For instance, you could create a partitioning scheme such as the following: CREATE TABLE client_firms ( id INT, name VARCHAR(35))PARTITION BY LIST (id) ( PARTITION r0 VALUES IN (1, 5, 9, 13, 17, 21), PARTITION r1 VALUES IN (2, 6, 10, 14, 18, 22), PARTITION r2 VALUES IN (3, 7, 11, 15, 19, 23), PARTITION r3 VALUES IN (4, 8, 12, 16, 20, 24)); When using list partitioning, you must define at least one partition using VALUES IN. You cannot use VALUES LESS THAN with PARTITION BY LIST. Note For tables partitioned by LIST, the value list used with VALUES IN must consist of integer values only. In MySQL 5.5, you can overcome this limitation using partitioning by LIST COLUMNS, which is described later in this section. LIST COLUMNS(column_list): This variant on LIST was introduced in MySQL 5.5.0 to facilitate partition pruning for queries using comparison conditions on multiple columns (that is, having conditions such as WHERE a = 5 AND b = 5 or WHERE a = 1 AND b = 10 AND c = 5). It enables you to specify values in multiple columns by using a list of columns in the COLUMNS clause and a set of column values in each PARTITION ... VALUES IN (value_list) partition definition clause. The rules governing regarding data types for the column list used in LIST COLUMNS(column_list) and the value list used in VALUES IN(value_list) are the same as those for the column list used in RANGE COLUMNS(column_list) and the value list used in VALUES LESS THAN(value_list), respectively, except that in the VALUES IN clause, MAXVALUE is not permitted, and you may use NULL. There is one important difference between the list of values used for VALUES IN with PARTITION BY LIST COLUMNS as opposed to when it is used with PARTITION BY LIST. When used with PARTITION BY LIST COLUMNS, each element in the VALUES IN clause must be a set of column values; the number of values in each set must be the same as the number of columns used in the COLUMNS clause, and the data types of these values must match those of the columns (and occur in the same order). In the simplest case, the set consists of a single column. The maximum number of columns that can be used in the column_list and in the elements making up the value_list is 16. The table defined by the following CREATE TABLE statement provides an example of a table using LIST COLUMNS partitioning: CREATE TABLE lc ( a INT NULL, b INT NULL)PARTITION BY LIST COLUMNS(a,b) ( PARTITION p0 VALUES IN( (0,0), (NULL,NULL) ), PARTITION p1 VALUES IN( (0,1), (0,2), (0,3), (1,1), (1,2) ), PARTITION p2 VALUES IN( (1,0), (2,0), (2,1), (3,0), (3,1) ), PARTITION p3 VALUES IN( (1,3), (2,2), (2,3), (3,2), (3,3) )); The number of partitions may optionally be specified with a PARTITIONS num clause, where num is the number of partitions. If both this clause and any PARTITION clauses are used, num must be equal to the total number of any partitions that are declared using PARTITION clauses. Note Whether or not you use a PARTITIONS clause in creating a table that is partitioned by RANGE or LIST, you must still include at least one PARTITION VALUES clause in the table definition (see below). A partition may optionally be divided into a number of subpartitions. This can be indicated by using the optional SUBPARTITION BY clause. Subpartitioning may be done by HASH or KEY. Either of these may be LINEAR. These work in the same way as previously described for the equivalent partitioning types. (It is not possible to subpartition by LIST or RANGE.) The number of subpartitions can be indicated using the SUBPARTITIONS keyword followed by an integer value. Rigorous checking of the value used in PARTITIONS or SUBPARTITIONS clauses is applied and this value must adhere to the following rules: The value must be a positive, nonzero integer. No leading zeros are permitted. The value must be an integer literal, and cannot not be an expression. For example, PARTITIONS 0.2E+01 is not permitted, even though 0.2E+01 evaluates to 2. (Bug #15890)
Note The expression (expr) used in a PARTITION BY clause cannot refer to any columns not in the table being created; such references are specifically not permitted and cause the statement to fail with an error. (Bug #29444) Each partition may be individually defined using a partition_definition clause. The individual parts making up this clause are as follows: PARTITION partition_name: This specifies a logical name for the partition. A VALUES clause: For range partitioning, each partition must include a VALUES LESS THAN clause; for list partitioning, you must specify a VALUES IN clause for each partition. This is used to determine which rows are to be stored in this partition. See the discussions of partitioning types in Chapter 18, Partitioning, for syntax examples. An optional COMMENT clause may be used to specify a string that describes the partition. Example: COMMENT = 'Data for the years previous to 1999' DATA DIRECTORY and INDEX DIRECTORY may be used to indicate the directory where, respectively, the data and indexes for this partition are to be stored. Both the data_dir and the index_dir must be absolute system path names. Example: CREATE TABLE th (id INT, name VARCHAR(30), adate DATE)PARTITION BY LIST(YEAR(adate))( PARTITION p1999 VALUES IN (1995, 1999, 2003) DATA DIRECTORY = '/var/appdata/95/data' INDEX DIRECTORY = '/var/appdata/95/idx', PARTITION p2000 VALUES IN (1996, 2000, 2004) DATA DIRECTORY = '/var/appdata/96/data' INDEX DIRECTORY = '/var/appdata/96/idx', PARTITION p2001 VALUES IN (1997, 2001, 2005) DATA DIRECTORY = '/var/appdata/97/data' INDEX DIRECTORY = '/var/appdata/97/idx', PARTITION p2002 VALUES IN (1998, 2002, 2006) DATA DIRECTORY = '/var/appdata/98/data' INDEX DIRECTORY = '/var/appdata/98/idx'); DATA DIRECTORY and INDEX DIRECTORY behave in the same way as in the CREATE TABLE statement's table_option clause as used for MyISAM tables. One data directory and one index directory may be specified per partition. If left unspecified, the data and indexes are stored by default in the table's database directory. On Windows, the DATA DIRECTORY and INDEX DIRECTORY options are not supported for individual partitions or subpartitions. These options are ignored on Windows, except that a warning is generated. (Bug #30459) Note The DATA DIRECTORY and INDEX DIRECTORY options are ignored for creating partitioned tables if NO_DIR_IN_CREATE is in effect. (Bug #24633) MAX_ROWS and MIN_ROWS may be used to specify, respectively, the maximum and minimum number of rows to be stored in the partition. The values for max_number_of_rows and min_number_of_rows must be positive integers. As with the table-level options with the same names, these act only as "suggestions" to the server and are not hard limits. The optional TABLESPACE clause may be used to designate a tablespace for the partition. Used for MySQL Cluster only. The partitioning handler accepts a [STORAGE] ENGINE option for both PARTITION and SUBPARTITION. Currently, the only way in which this can be used is to set all partitions or all subpartitions to the same storage engine, and an attempt to set different storage engines for partitions or subpartitions in the same table will give rise to the error ERROR 1469 (HY000): The mix of handlers in the partitions is not permitted in this version of MySQL. We expect to lift this restriction on partitioning in a future MySQL release. The NODEGROUP option can be used to make this partition act as part of the node group identified by node_group_id. This option is applicable only to MySQL Cluster. The partition definition may optionally contain one or more subpartition_definition clauses. Each of these consists at a minimum of the SUBPARTITION name, where name is an identifier for the subpartition. Except for the replacement of the PARTITION keyword with SUBPARTITION, the syntax for a subpartition definition is identical to that for a partition definition. Subpartitioning must be done by HASH or KEY, and can be done only on RANGE or LIST partitions. See Section 18.2.6, "Subpartitioning".
Partitions can be modified, merged, added to tables, and dropped from tables. For basic information about the MySQL statements to accomplish these tasks, see Section 13.1.7, "ALTER TABLE Syntax". For more detailed descriptions and examples, see Section 18.3, "Partition Management". Important The original CREATE TABLE statement, including all specifications and table options are stored by MySQL when the table is created. The information is retained so that if you change storage engines, collations or other settings using an ALTER TABLE statement, the original table options specified are retained. This enables you to change between InnoDB and MyISAM table types even though the row formats supported by the two engines are different. Because the text of the original statement is retained, but due to the way that certain values and options may be silently reconfigured (such as the ROW_FORMAT), the active table definition (accessible through DESCRIBE or with SHOW TABLE STATUS) and the table creation string (accessible through SHOW CREATE TABLE) will report different values. You can create one table from another by adding a SELECT statement at the end of the CREATE TABLE statement: CREATE TABLE new_tbl SELECT * FROM orig_tbl; For more information, see Section 13.1.17.1, "CREATE TABLE ... SELECT Syntax". Use LIKE to create an empty table based on the definition of another table, including any column attributes and indexes defined in the original table: CREATE TABLE new_tbl LIKE orig_tbl; The copy is created using the same version of the table storage format as the original table. The SELECT privilege is required on the original table. LIKE works only for base tables, not for views. Important Beginning with MySQL 5.5.3, you cannot execute CREATE TABLE or CREATE TABLE ... LIKE while a LOCK TABLES statement is in effect. Also as of MySQL 5.5.3, CREATE TABLE ... LIKE makes the same checks as CREATE TABLE and does not just copy the .frm file. This means that if the current SQL mode is different from the mode in effect when the original table was created, the table definition might be considered invalid for the new mode and the statement will fail. CREATE TABLE ... LIKE does not preserve any DATA DIRECTORY or INDEX DIRECTORY table options that were specified for the original table, or any foreign key definitions. If the original table is a TEMPORARY table, CREATE TABLE ... LIKE does not preserve TEMPORARY. To create a TEMPORARY destination table, use CREATE TEMPORARY TABLE ... LIKE. 13.1.17.1. CREATE TABLE ...SELECT Syntax You can create one table from another by adding a SELECT statement at the end of the CREATE TABLE statement: CREATE TABLE new_tbl SELECT * FROM orig_tbl; MySQL creates new columns for all elements in the SELECT. For example: mysql> CREATE TABLE test (a INT NOT NULL AUTO_INCREMENT, -> PRIMARY KEY (a), KEY(b)) -> ENGINE=MyISAM SELECT b,c FROM test2; This creates a MyISAM table with three columns, a, b, and c. The ENGINE option is part of the CREATE TABLE statement, and should not be used following the SELECT; this would result in a syntax error. The same is true for other CREATE TABLE options such as CHARSET. Notice that the columns from the SELECT statement are appended to the right side of the table, not overlapped onto it. Take the following example: mysql> SELECT * FROM foo;+---+| n |+---+| 1 |+---+mysql> CREATE TABLE bar (m INT) SELECT n FROM foo;Query OK, 1 row affected (0.02 sec)Records: 1 Duplicates: 0 Warnings: 0mysql> SELECT * FROM bar;+------+---+| m | n |+------+---+| NULL | 1 |+------+---+1 row in set (0.00 sec) For each row in table foo, a row is inserted in bar with the values from foo and default values for the new columns. In a table resulting from CREATE TABLE ... SELECT, columns named only in the CREATE TABLE part come first. Columns named in both parts or only in the SELECT part come after that. The data type of SELECT columns can be overridden by also specifying the column in the CREATE TABLE part. If any errors occur while copying the data to the table, it is automatically dropped and not created. You can precede the SELECT by IGNORE or REPLACE to indicate how to handle rows that duplicate unique key values. With IGNORE, new rows that duplicate an existing row on a unique key value are discarded. With REPLACE, new rows replace rows that have the same unique key value. If neither IGNORE nor REPLACE is specified, duplicate unique key values result in an error. Because the ordering of the rows in the underlying SELECT statements cannot always be determined, CREATE TABLE ... IGNORE SELECT and CREATE TABLE ... REPLACE SELECT statements in MySQL 5.5.18 and later are flagged as unsafe for statement-based replication. With this change, such statements produce a warning in the log when using statement-based mode and are logged using the row-based format when using MIXED mode. See also Section 16.1.2.1, "Advantages and Disadvantages of Statement-Based and Row-Based Replication". CREATE TABLE ... SELECT does not automatically create any indexes for you. This is done intentionally to make the statement as flexible as possible. If you want to have indexes in the created table, you should specify these before the SELECT statement: mysql> CREATE TABLE bar (UNIQUE (n)) SELECT n FROM foo; Some conversion of data types might occur. For example, the AUTO_INCREMENT attribute is not preserved, and VARCHAR columns can become CHAR columns. Retrained attributes are NULL (or NOT NULL) and, for those columns that have them, CHARACTER SET, COLLATION, COMMENT, and the DEFAULT clause. When creating a table with CREATE TABLE ... SELECT, make sure to alias any function calls or expressions in the query. If you do not, the CREATE statement might fail or result in undesirable column names. CREATE TABLE artists_and_works SELECT artist.name, COUNT(work.artist_id) AS number_of_works FROM artist LEFT JOIN work ON artist.id = work.artist_id GROUP BY artist.id; You can also explicitly specify the data type for a generated column: CREATE TABLE foo (a TINYINT NOT NULL) SELECT b+1 AS a FROM bar; For CREATE TABLE ... SELECT, if IF NOT EXISTS is given and the destination table already exists, the result is version dependent. Before MySQL 5.5.6, MySQL handles the statement as follows: The table definition given in the CREATE TABLE part is ignored. No error occurs, even if the definition does not match that of the existing table. MySQL attempts to insert the rows from the SELECT part anyway. If there is a mismatch between the number of columns in the table and the number of columns produced by the SELECT part, the selected values are assigned to the rightmost columns. For example, if the table contains n columns and the SELECT produces m columns, where m < n, the selected values are assigned to the m rightmost columns in the table. Each of the initial n � m columns is assigned its default value, either that specified explicitly in the column definition or the implicit column data type default if the definition contains no default. If the SELECT part produces too many columns (m > n), an error occurs. If strict SQL mode is enabled and any of these initial columns do not have an explicit default value, the statement fails with an error.
The following example illustrates IF NOT EXISTS handling: mysql> CREATE TABLE t1 (i1 INT DEFAULT 0, i2 INT, i3 INT, i4 INT);Query OK, 0 rows affected (0.05 sec)mysql> CREATE TABLE IF NOT EXISTS t1 (c1 CHAR(10)) SELECT 1, 2;Query OK, 1 row affected, 1 warning (0.01 sec)Records: 1 Duplicates: 0 Warnings: 0mysql> SELECT * FROM t1;+------+------+------+------+| i1 | i2 | i3 | i4 |+------+------+------+------+| 0 | NULL | 1 | 2 |+------+------+------+------+1 row in set (0.00 sec) As of MySQL 5.5.6, handling of CREATE TABLE IF NOT EXISTS ... SELECT statements was changed for the case that the destination table already exists. This change also involves a change in MySQL 5.1 beginning with 5.1.51. Previously, for CREATE TABLE IF NOT EXISTS ... SELECT, MySQL produced a warning that the table exists, but inserted the rows and wrote the statement to the binary log anyway. By contrast, CREATE TABLE ... SELECT (without IF NOT EXISTS) failed with an error, but MySQL inserted no rows and did not write the statement to the binary log. MySQL now handles both statements the same way when the destination table exists, in that neither statement inserts rows or is written to the binary log. The difference between them is that MySQL produces a warning when IF NOT EXISTS is present and an error when it is not.
This change means that, for the preceding example, the CREATE TABLE IF NOT EXISTS ... SELECT statement inserts nothing into the destination table as of MySQL 5.5.6. This change in handling of IF NOT EXISTS results in an incompatibility for statement-based replication from a MySQL 5.1 master with the original behavior and a MySQL 5.5 slave with the new behavior. Suppose that CREATE TABLE IF NOT EXISTS ... SELECT is executed on the master and the destination table exists. The result is that rows are inserted on the master but not on the slave. (Row-based replication does not have this problem.) To address this issue, statement-based binary logging for CREATE TABLE IF NOT EXISTS ... SELECT is changed in MySQL 5.1 as of 5.1.51: This change provides forward compatibility for statement-based replication from MySQL 5.1 to 5.5 because when the destination table exists, the rows will be inserted on both the master and slave. To take advantage of this compatibility measure, the 5.1 server must be at least 5.1.51 and the 5.5 server must be at least 5.5.6. To upgrade an existing 5.1-to-5.5 replication scenario, upgrade the master first to 5.1.51 or higher. Note that this differs from the usual replication upgrade advice of upgrading the slave first. A workaround for applications that wish to achieve the original effect (rows inserted regardless of whether the destination table exists) is to use CREATE TABLE IF NOT EXISTS and INSERT ... SELECT statements rather than CREATE TABLE IF NOT EXISTS ... SELECT statements. Along with the change just described, the following related change was made: Previously, if an existing view was named as the destination table for CREATE TABLE IF NOT EXISTS ... SELECT, rows were inserted into the underlying base table and the statement was written to the binary log. As of MySQL 5.1.51 and 5.5.6, nothing is inserted or logged. To ensure that the binary log can be used to re-create the original tables, MySQL does not permit concurrent inserts during CREATE TABLE ... SELECT. 13.1.17.2. Silent Column Specification Changes In some cases, MySQL silently changes column specifications from those given in a CREATE TABLE or ALTER TABLE statement. These might be changes to a data type, to attributes associated with a data type, or to an index specification. All changes are subject to the internal row-size limit of 65,535 bytes, which may cause some attempts at data type changes to fail. See Section E.10.4, "Table Column-Count and Row-Size Limits". TIMESTAMP display sizes are discarded. Also note that TIMESTAMP columns are NOT NULL by default. Columns that are part of a PRIMARY KEY are made NOT NULL even if not declared that way. Trailing spaces are automatically deleted from ENUM and SET member values when the table is created. MySQL maps certain data types used by other SQL database vendors to MySQL types. See Section 11.8, "Using Data Types from Other Database Engines". If you include a USING clause to specify an index type that is not permitted for a given storage engine, but there is another index type available that the engine can use without affecting query results, the engine uses the available type. If strict SQL mode is not enabled, a VARCHAR column with a length specification greater than 65535 is converted to TEXT, and a VARBINARY column with a length specification greater than 65535 is converted to BLOB. Otherwise, an error occurs in either of these cases. Specifying the CHARACTER SET binary attribute for a character data type causes the column to be created as the corresponding binary data type: CHAR becomes BINARY, VARCHAR becomes VARBINARY, and TEXT becomes BLOB. For the ENUM and SET data types, this does not occur; they are created as declared. Suppose that you specify a table using this definition: CREATE TABLE t( c1 VARCHAR(10) CHARACTER SET binary, c2 TEXT CHARACTER SET binary, c3 ENUM('a','b','c') CHARACTER SET binary); The resulting table has this definition: CREATE TABLE t( c1 VARBINARY(10), c2 BLOB, c3 ENUM('a','b','c') CHARACTER SET binary);
To see whether MySQL used a data type other than the one you specified, issue a DESCRIBE or SHOW CREATE TABLE statement after creating or altering the table. Certain other data type changes can occur if you compress a table using myisampack. See Section 14.5.3.3, "Compressed Table Characteristics". 13.1.18. CREATE TABLESPACE SyntaxCREATE TABLESPACE tablespace_name ADD DATAFILE 'file_name' USE LOGFILE GROUP logfile_group [EXTENT_SIZE [=] extent_size] [INITIAL_SIZE [=] initial_size] [AUTOEXTEND_SIZE [=] autoextend_size] [MAX_SIZE [=] max_size] [NODEGROUP [=] nodegroup_id] [WAIT] [COMMENT [=] comment_text] ENGINE [=] engine_name This statement is used to create a tablespace, which can contain one or more data files, providing storage space for tables. One data file is created and added to the tablespace using this statement. Additional data files may be added to the tablespace by using the ALTER TABLESPACE statement (see Section 13.1.8, "ALTER TABLESPACE Syntax"). For rules covering the naming of tablespaces, see Section 9.2, "Schema Object Names". Note All MySQL Cluster Disk Data objects share the same namespace. This means that each Disk Data object must be uniquely named (and not merely each Disk Data object of a given type). For example, you cannot have a tablespace and a log file group with the same name, or a tablespace and a data file with the same name. A log file group of one or more UNDO log files must be assigned to the tablespace to be created with the USE LOGFILE GROUP clause. logfile_group must be an existing log file group created with CREATE LOGFILE GROUP (see Section 13.1.14, "CREATE LOGFILE GROUP Syntax"). Multiple tablespaces may use the same log file group for UNDO logging. The EXTENT_SIZE sets the size, in bytes, of the extents used by any files belonging to the tablespace. The default value is 1M. The minimum size is 32K, and theoretical maximum is 2G, although the practical maximum size depends on a number of factors. In most cases, changing the extent size does not have any measurable effect on performance, and the default value is recommended for all but the most unusual situations. An extent is a unit of disk space allocation. One extent is filled with as much data as that extent can contain before another extent is used. In theory, up to 65,535 (64K) extents may used per data file; however, the recommended maximum is 32,768 (32K). The recommended maximum size for a single data file is 32G-that is, 32K extents � 1 MB per extent. In addition, once an extent is allocated to a given partition, it cannot be used to store data from a different partition; an extent cannot store data from more than one partition. This means, for example that a tablespace having a single datafile whose INITIAL_SIZE is 256 MB and whose EXTENT_SIZE is 128M has just two extents, and so can be used to store data from at most two different disk data table partitions. You can see how many extents remain free in a given data file by querying the INFORMATION_SCHEMA.FILES table, and so derive an estimate for how much space remains free in the file. For further discussion and examples, see Section 20.29.1, "The INFORMATION_SCHEMA FILES Table". The INITIAL_SIZE parameter sets the data file's total size in bytes. Once the file has been created, its size cannot be changed; however, you can add more data files to the tablespace using ALTER TABLESPACE ... ADD DATAFILE. See Section 13.1.8, "ALTER TABLESPACE Syntax". INITIAL_SIZE is optional; its default value is 128M. On 32-bit systems, the maximum supported value for INITIAL_SIZE is 4G. (Bug #29186) When setting EXTENT_SIZE or INITIAL_SIZE (either or both), you may optionally follow the number with a one-letter abbreviation for an order of magnitude, similar to those used in my.cnf. Generally, this is one of the letters M (for megabytes) or G (for gigabytes). INITIAL_SIZE, EXTENT_SIZE, and UNDO_BUFFER_SIZE are subject to rounding as follows: EXTENT_SIZE and UNDO_BUFFER_SIZE are each rounded up to the nearest whole multiple of 32K. INITIAL_SIZE is rounded down to the nearest whole multiple of 32K. For data files, INITIAL_SIZE is subject to further rounding; the result just obtained is rounded up to the nearest whole multiple of EXTENT_SIZE (after any rounding).
The rounding just described is done explicitly, and a warning is issued by the MySQL Server when any such rounding is performed. The rounded values are also used by the NDB kernel for calculating INFORMATION_SCHEMA.FILES column values and other purposes. However, to avoid an unexpected result, we suggest that you always use whole multiples of 32K in specifying these options. AUTOEXTEND_SIZE, MAX_SIZE, NODEGROUP, WAIT, and COMMENT are parsed but ignored, and so currently have no effect. These options are intended for future expansion. The ENGINE parameter determines the storage engine which uses this tablespace, with engine_name being the name of the storage engine. Currently, engine_name must be one of the values NDB or NDBCLUSTER. When CREATE TABLESPACE is used with ENGINE = NDB, a tablespace and associated data file are created on each Cluster data node. You can verify that the data files were created and obtain information about them by querying the INFORMATION_SCHEMA.FILES table. For example: mysql> SELECT LOGFILE_GROUP_NAME, FILE_NAME, EXTRA -> FROM INFORMATION_SCHEMA.FILES -> WHERE TABLESPACE_NAME = 'newts' AND FILE_TYPE = 'DATAFILE';+--------------------+-------------+----------------+| LOGFILE_GROUP_NAME | FILE_NAME | EXTRA |+--------------------+-------------+----------------+| lg_3 | newdata.dat | CLUSTER_NODE=3 || lg_3 | newdata.dat | CLUSTER_NODE=4 |+--------------------+-------------+----------------+2 rows in set (0.01 sec) (See Section 20.29.1, "The INFORMATION_SCHEMA FILES Table".) CREATE TABLESPACE is useful only with Disk Data storage for MySQL Cluster. See Section 17.5.12, "MySQL Cluster Disk Data Tables". 13.1.19. CREATE TRIGGER SyntaxCREATE [DEFINER = { user | CURRENT_USER }] TRIGGER trigger_name trigger_time trigger_event ON tbl_name FOR EACH ROW trigger_body This statement creates a new trigger. A trigger is a named database object that is associated with a table, and that activates when a particular event occurs for the table. The trigger becomes associated with the table named tbl_name, which must refer to a permanent table. You cannot associate a trigger with a TEMPORARY table or a view. CREATE TRIGGER requires the TRIGGER privilege for the table associated with the trigger. The statement might also require the SUPER privilege, depending on the DEFINER value, as described later in this section. If binary logging is enabled, CREATE TRIGGER might require the SUPER privilege, as described in Section 19.7, "Binary Logging of Stored Programs". The DEFINER clause determines the security context to be used when checking access privileges at trigger activation time. See later in this section for more information. trigger_time is the trigger action time. It can be BEFORE or AFTER to indicate that the trigger activates before or after each row to be modified. trigger_event indicates the kind of statement that activates the trigger. The trigger_event can be one of the following: It is important to understand that the trigger_event does not represent a literal type of SQL statement that activates the trigger so much as it represents a type of table operation. For example, an INSERT trigger is activated by not only INSERT statements but also LOAD DATA statements because both statements insert rows into a table. A potentially confusing example of this is the INSERT INTO ... ON DUPLICATE KEY UPDATE ... syntax: a BEFORE INSERT trigger will activate for every row, followed by either an AFTER INSERT trigger or both the BEFORE UPDATE and AFTER UPDATE triggers, depending on whether there was a duplicate key for the row. There cannot be two triggers for a given table that have the same trigger action time and event. For example, you cannot have two BEFORE UPDATE triggers for a table. But you can have a BEFORE UPDATE and a BEFORE INSERT trigger, or a BEFORE UPDATE and an AFTER UPDATE trigger. trigger_body is the statement to execute when the trigger activates. If you want to execute multiple statements, use the BEGIN ... END compound statement construct. This also enables you to use the same statements that are permissible within stored routines. See Section 13.6.1, "BEGIN ... END Compound-Statement Syntax". Some statements are not permitted in triggers; see Section E.1, "Restrictions on Stored Programs". You can refer to columns in the subject table (the table associated with the trigger) by using the aliases OLD and NEW. OLD.col_name refers to a column of an existing row before it is updated or deleted. NEW.col_name refers to the column of a new row to be inserted or an existing row after it is updated. MySQL stores the sql_mode system variable setting that is in effect at the time a trigger is created, and always executes the trigger with this setting in force, regardless of the server SQL mode in effect when the event begins executing. Note Currently, cascaded foreign key actions do not activate triggers. The DEFINER clause specifies the MySQL account to be used when checking access privileges at trigger activation time. If a user value is given, it should be a MySQL account specified as 'user_name'@'host_name' (the same format used in the GRANT statement), CURRENT_USER, or CURRENT_USER(). The default DEFINER value is the user who executes the CREATE TRIGGER statement. This is the same as specifying DEFINER = CURRENT_USER explicitly. If you specify the DEFINER clause, these rules determine the valid DEFINER user values: If you do not have the SUPER privilege, the only permitted user value is your own account, either specified literally or by using CURRENT_USER. You cannot set the definer to some other account. If you have the SUPER privilege, you can specify any syntactically valid account name. If the account does not actually exist, a warning is generated. Although it is possible to create a trigger with a nonexistent DEFINER account, it is not a good idea for such triggers to be activated until the account actually does exist. Otherwise, the behavior with respect to privilege checking is undefined.
MySQL takes the DEFINER user into account when checking trigger privileges as follows: At CREATE TRIGGER time, the user who issues the statement must have the TRIGGER privilege. At trigger activation time, privileges are checked against the DEFINER user. This user must have these privileges: The TRIGGER privilege. The SELECT privilege for the subject table if references to table columns occur using OLD.col_name or NEW.col_name in the trigger definition. The UPDATE privilege for the subject table if table columns are targets of SET NEW.col_name = value assignments in the trigger definition. Whatever other privileges normally are required for the statements executed by the trigger.
For more information about trigger security, see Section 19.6, "Access Control for Stored Programs and Views". Within a trigger, the CURRENT_USER() function returns the account used to check privileges at trigger activation time. This is the DEFINER user, not the user whose actions caused the trigger to be activated. For information about user auditing within triggers, see Section 6.3.11, "SQL-Based MySQL Account Activity Auditing". If you use LOCK TABLES to lock a table that has triggers, the tables used within the trigger are also locked, as described in Section 13.3.5.2, "LOCK TABLES and Triggers". In MySQL 5.5, you can write triggers containing direct references to tables by name, such as the trigger named testref shown in this example: CREATE TABLE test1(a1 INT);CREATE TABLE test2(a2 INT);CREATE TABLE test3(a3 INT NOT NULL AUTO_INCREMENT PRIMARY KEY);CREATE TABLE test4( a4 INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b4 INT DEFAULT 0);delimiter |CREATE TRIGGER testref BEFORE INSERT ON test1 FOR EACH ROW BEGIN INSERT INTO test2 SET a2 = NEW.a1; DELETE FROM test3 WHERE a3 = NEW.a1; UPDATE test4 SET b4 = b4 + 1 WHERE a4 = NEW.a1; END;|delimiter ;INSERT INTO test3 (a3) VALUES (NULL), (NULL), (NULL), (NULL), (NULL), (NULL), (NULL), (NULL), (NULL), (NULL);INSERT INTO test4 (a4) VALUES (0), (0), (0), (0), (0), (0), (0), (0), (0), (0); Suppose that you insert the following values into table test1 as shown here: mysql> INSERT INTO test1 VALUES -> (1), (3), (1), (7), (1), (8), (4), (4);Query OK, 8 rows affected (0.01 sec)Records: 8 Duplicates: 0 Warnings: 0 As a result, the data in the four tables will be as follows: mysql> SELECT * FROM test1;+------+| a1 |+------+| 1 || 3 || 1 || 7 || 1 || 8 || 4 || 4 |+------+8 rows in set (0.00 sec)mysql> SELECT * FROM test2;+------+| a2 |+------+| 1 || 3 || 1 || 7 || 1 || 8 || 4 || 4 |+------+8 rows in set (0.00 sec)mysql> SELECT * FROM test3;+----+| a3 |+----+| 2 || 5 || 6 || 9 || 10 |+----+5 rows in set (0.00 sec)mysql> SELECT * FROM test4;+----+------+| a4 | b4 |+----+------+| 1 | 3 || 2 | 0 || 3 | 1 || 4 | 2 || 5 | 0 || 6 | 0 || 7 | 1 || 8 | 1 || 9 | 0 || 10 | 0 |+----+------+10 rows in set (0.00 sec) 13.1.20. CREATE VIEW SyntaxCREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}] [DEFINER = { user | CURRENT_USER }] [SQL SECURITY { DEFINER | INVOKER }] VIEW view_name [(column_list)] AS select_statement [WITH [CASCADED | LOCAL] CHECK OPTION] The CREATE VIEW statement creates a new view, or replaces an existing one if the OR REPLACE clause is given. If the view does not exist, CREATE OR REPLACE VIEW is the same as CREATE VIEW. If the view does exist, CREATE OR REPLACE VIEW is the same as ALTER VIEW. The select_statement is a SELECT statement that provides the definition of the view. (When you select from the view, you select in effect using the SELECT statement.) select_statement can select from base tables or other views. The view definition is "frozen" at creation time, so changes to the underlying tables afterward do not affect the view definition. For example, if a view is defined as SELECT * on a table, new columns added to the table later do not become part of the view. The ALGORITHM clause affects how MySQL processes the view. The DEFINER and SQL SECURITY clauses specify the security context to be used when checking access privileges at view invocation time. The WITH CHECK OPTION clause can be given to constrain inserts or updates to rows in tables referenced by the view. These clauses are described later in this section. The CREATE VIEW statement requires the CREATE VIEW privilege for the view, and some privilege for each column selected by the SELECT statement. For columns used elsewhere in the SELECT statement you must have the SELECT privilege. If the OR REPLACE clause is present, you must also have the DROP privilege for the view. CREATE VIEW might also require the SUPER privilege, depending on the DEFINER value, as described later in this section. When a view is referenced, privilege checking occurs as described later in this section. A view belongs to a database. By default, a new view is created in the default database. To create the view explicitly in a given database, specify the name as db_name.view_name when you create it: mysql> CREATE VIEW test.v AS SELECT * FROM t; Within a database, base tables and views share the same namespace, so a base table and a view cannot have the same name. Columns retrieved by the SELECT statement can be simple references to table columns. They can also be expressions that use functions, constant values, operators, and so forth. Views must have unique column names with no duplicates, just like base tables. By default, the names of the columns retrieved by the SELECT statement are used for the view column names. To define explicit names for the view columns, the optional column_list clause can be given as a list of comma-separated identifiers. The number of names in column_list must be the same as the number of columns retrieved by the SELECT statement. Unqualified table or view names in the SELECT statement are interpreted with respect to the default database. A view can refer to tables or views in other databases by qualifying the table or view name with the proper database name. A view can be created from many kinds of SELECT statements. It can refer to base tables or other views. It can use joins, UNION, and subqueries. The SELECT need not even refer to any tables. The following example defines a view that selects two columns from another table, as well as an expression calculated from those columns: mysql> CREATE TABLE t (qty INT, price INT);mysql> INSERT INTO t VALUES(3, 50);mysql> CREATE VIEW v AS SELECT qty, price, qty*price AS value FROM t;mysql> SELECT * FROM v;+------+-------+-------+| qty | price | value |+------+-------+-------+| 3 | 50 | 150 |+------+-------+-------+ A view definition is subject to the following restrictions: The SELECT statement cannot contain a subquery in the FROM clause. The SELECT statement cannot refer to system or user variables. Within a stored program, the definition cannot refer to program parameters or local variables. The SELECT statement cannot refer to prepared statement parameters. Any table or view referred to in the definition must exist. However, after a view has been created, it is possible to drop a table or view that the definition refers to. In this case, use of the view results in an error. To check a view definition for problems of this kind, use the CHECK TABLE statement. The definition cannot refer to a TEMPORARY table, and you cannot create a TEMPORARY view. Any tables named in the view definition must exist at definition time. You cannot associate a trigger with a view. Aliases for column names in the SELECT statement are checked against the maximum column length of 64 characters (not the maximum alias length of 256 characters).
ORDER BY is permitted in a view definition, but it is ignored if you select from a view using a statement that has its own ORDER BY. For other options or clauses in the definition, they are added to the options or clauses of the statement that references the view, but the effect is undefined. For example, if a view definition includes a LIMIT clause, and you select from the view using a statement that has its own LIMIT clause, it is undefined which limit applies. This same principle applies to options such as ALL, DISTINCT, or SQL_SMALL_RESULT that follow the SELECT keyword, and to clauses such as INTO, FOR UPDATE, LOCK IN SHARE MODE, and PROCEDURE. If you create a view and then change the query processing environment by changing system variables, that may affect the results that you get from the view: mysql> CREATE VIEW v (mycol) AS SELECT 'abc';Query OK, 0 rows affected (0.01 sec)mysql> SET sql_mode = '';Query OK, 0 rows affected (0.00 sec)mysql> SELECT "mycol" FROM v;+-------+| mycol |+-------+| mycol |+-------+1 row in set (0.01 sec)mysql> SET sql_mode = 'ANSI_QUOTES';Query OK, 0 rows affected (0.00 sec)mysql> SELECT "mycol" FROM v;+-------+| mycol |+-------+| abc |+-------+1 row in set (0.00 sec) The DEFINER and SQL SECURITY clauses determine which MySQL account to use when checking access privileges for the view when a statement is executed that references the view. The valid SQL SECURITY characteristic values are DEFINER and INVOKER. These indicate that the required privileges must be held by the user who defined or invoked the view, respectively. The default SQL SECURITY value is DEFINER. If a user value is given for the DEFINER clause, it should be a MySQL account specified as 'user_name'@'host_name' (the same format used in the GRANT statement), CURRENT_USER, or CURRENT_USER(). The default DEFINER value is the user who executes the CREATE VIEW statement. This is the same as specifying DEFINER = CURRENT_USER explicitly. If you specify the DEFINER clause, these rules determine the valid DEFINER user values: If you do not have the SUPER privilege, the only valid user value is your own account, either specified literally or by using CURRENT_USER. You cannot set the definer to some other account. If you have the SUPER privilege, you can specify any syntactically valid account name. If the account does not actually exist, a warning is generated. Although it is possible to create a view with a nonexistent DEFINER account, an error occurs when the view is referenced if the SQL SECURITY value is DEFINER but the definer account does not exist.
For more information about view security, see Section 19.6, "Access Control for Stored Programs and Views". Within a view definition, CURRENT_USER returns the view's DEFINER value by default. For views defined with the SQL SECURITY INVOKER characteristic, CURRENT_USER returns the account for the view's invoker. For information about user auditing within views, see Section 6.3.11, "SQL-Based MySQL Account Activity Auditing". Within a stored routine that is defined with the SQL SECURITY DEFINER characteristic, CURRENT_USER returns the routine's DEFINER value. This also affects a view defined within such a routine, if the view definition contains a DEFINER value of CURRENT_USER. View privileges are checked like this: At view definition time, the view creator must have the privileges needed to use the top-level objects accessed by the view. For example, if the view definition refers to table columns, the creator must have some privilege for each column in the select list of the definition, and the SELECT privilege for each column used elsewhere in the definition. If the definition refers to a stored function, only the privileges needed to invoke the function can be checked. The privileges required at function invocation time can be checked only as it executes: For different invocations, different execution paths within the function might be taken. The user who references a view must have appropriate privileges to access it (SELECT to select from it, INSERT to insert into it, and so forth.) When a view has been referenced, privileges for objects accessed by the view are checked against the privileges held by the view DEFINER account or invoker, depending on whether the SQL SECURITY characteristic is DEFINER or INVOKER, respectively. If reference to a view causes execution of a stored function, privilege checking for statements executed within the function depend on whether the function SQL SECURITY characteristic is DEFINER or INVOKER. If the security characteristic is DEFINER, the function runs with the privileges of the DEFINER account. If the characteristic is INVOKER, the function runs with the privileges determined by the view's SQL SECURITY characteristic.
Example: A view might depend on a stored function, and that function might invoke other stored routines. For example, the following view invokes a stored function f(): CREATE VIEW v AS SELECT * FROM t WHERE t.id = f(t.name); Suppose that f() contains a statement such as this: IF name IS NULL then CALL p1();ELSE CALL p2();END IF; The privileges required for executing statements within f() need to be checked when f() executes. This might mean that privileges are needed for p1() or p2(), depending on the execution path within f(). Those privileges must be checked at runtime, and the user who must possess the privileges is determined by the SQL SECURITY values of the view v and the function f(). The DEFINER and SQL SECURITY clauses for views are extensions to standard SQL. In standard SQL, views are handled using the rules for SQL SECURITY DEFINER. The standard says that the definer of the view, which is the same as the owner of the view's schema, gets applicable privileges on the view (for example, SELECT) and may grant them. MySQL has no concept of a schema "owner", so MySQL adds a clause to identify the definer. The DEFINER clause is an extension where the intent is to have what the standard has; that is, a permanent record of who defined the view. This is why the default DEFINER value is the account of the view creator. The optional ALGORITHM clause is a MySQL extension to standard SQL. It affects how MySQL processes the view. ALGORITHM takes three values: MERGE, TEMPTABLE, or UNDEFINED. The default algorithm is UNDEFINED if no ALGORITHM clause is present. For more information, see Section 19.5.2, "View Processing Algorithms". Some views are updatable. That is, you can use them in statements such as UPDATE, DELETE, or INSERT to update the contents of the underlying table. For a view to be updatable, there must be a one-to-one relationship between the rows in the view and the rows in the underlying table. There are also certain other constructs that make a view nonupdatable. The WITH CHECK OPTION clause can be given for an updatable view to prevent inserts or updates to rows except those for which the WHERE clause in the select_statement is true. In a WITH CHECK OPTION clause for an updatable view, the LOCAL and CASCADED keywords determine the scope of check testing when the view is defined in terms of another view. The LOCAL keyword restricts the CHECK OPTION only to the view being defined. CASCADED causes the checks for underlying views to be evaluated as well. When neither keyword is given, the default is CASCADED. For more information about updatable views and the WITH CHECK OPTION clause, see Section 19.5.3, "Updatable and Insertable Views". 13.1.21. DROP DATABASE SyntaxDROP {DATABASE | SCHEMA} [IF EXISTS] db_name DROP DATABASE drops all tables in the database and deletes the database. Be very careful with this statement! To use DROP DATABASE, you need the DROP privilege on the database. DROP SCHEMA is a synonym for DROP DATABASE. IF EXISTS is used to prevent an error from occurring if the database does not exist. If the default database is dropped, the default database is unset (the DATABASE() function returns NULL). If you use DROP DATABASE on a symbolically linked database, both the link and the original database are deleted. DROP DATABASE returns the number of tables that were removed. This corresponds to the number of .frm files removed. The DROP DATABASE statement removes from the given database directory those files and directories that MySQL itself may create during normal operation: If other files or directories remain in the database directory after MySQL removes those just listed, the database directory cannot be removed. In this case, you must remove any remaining files or directories manually and issue the DROP DATABASE statement again. You can also drop databases with mysqladmin. See Section 4.5.2, "mysqladmin - Client for Administering a MySQL Server". 13.1.22. DROP EVENT SyntaxDROP EVENT [IF EXISTS] event_name This statement drops the event named event_name. The event immediately ceases being active, and is deleted completely from the server. If the event does not exist, the error ERROR 1517 (HY000): Unknown event 'event_name' results. You can override this and cause the statement to generate a warning for nonexistent events instead using IF EXISTS. This statement requires the EVENT privilege for the schema to which the event to be dropped belongs. 13.1.23. DROP FUNCTION Syntax The DROP FUNCTION statement is used to drop stored functions and user-defined functions (UDFs): 13.1.24. DROP INDEX SyntaxDROP [ONLINE|OFFLINE] INDEX index_name ON tbl_name DROP INDEX drops the index named index_name from the table tbl_name. This statement is mapped to an ALTER TABLE statement to drop the index. See Section 13.1.7, "ALTER TABLE Syntax". To drop a primary key, the index name is always PRIMARY, which must be specified as a quoted identifier because PRIMARY is a reserved word: DROP INDEX `PRIMARY` ON t; Indexes on variable-width columns of NDBCLUSTER tables are dropped online; that is, without any table copying. The table is not locked against access from other MySQL Cluster API nodes, although it is locked against other operations on the same API node for the duration of the operation. This is done automatically by the server whenever it determines that it is possible to do so; you do not have to use any special SQL syntax or server options to cause it to happen. In standard MySQL 5.5 releases, it is not possible to override the server when it determines that an index is to be dropped without table copying. In MySQL Cluster, you can drop indexes offline (which causes the table to be locked for all API nodes in the cluster) using the OFFLINE keyword. The rules and limitations governing DROP OFFLINE INDEX and DROP ONLINE INDEX are the same as for ALTER OFFLINE TABLE ... DROP INDEX and ALTER ONLINE TABLE ... DROP INDEX. You cannot cause the noncopying dropping of an index that would normally be dropped offline by using the ONLINE keyword: If it is not possible to perform the DROP operation without table copying, the server ignores the ONLINE keyword. For more information, see Section 13.1.7.2, "ALTER TABLE Online Operations". Note The ONLINE and OFFLINE keywords are available only in MySQL Cluster; attempting to use these keywords in standard MySQL Server 5.5 releases results in a syntax error. 13.1.25. DROP LOGFILE GROUP SyntaxDROP LOGFILE GROUP logfile_group ENGINE [=] engine_name This statement drops the log file group named logfile_group. The log file group must already exist or an error results. (For information on creating log file groups, see Section 13.1.14, "CREATE LOGFILE GROUP Syntax".) Important Before dropping a log file group, you must drop all tablespaces that use that log file group for UNDO logging. The required ENGINE clause provides the name of the storage engine used by the log file group to be dropped. Currently, the only permitted values for engine_name are NDB and NDBCLUSTER. DROP LOGFILE GROUP is useful only with Disk Data storage for MySQL Cluster. See Section 17.5.12, "MySQL Cluster Disk Data Tables". 13.1.26. DROP PROCEDURE andDROP FUNCTION SyntaxDROP {PROCEDURE | FUNCTION} [IF EXISTS] sp_name This statement is used to drop a stored procedure or function. That is, the specified routine is removed from the server. You must have the ALTER ROUTINE privilege for the routine. (If the automatic_sp_privileges system variable is enabled, that privilege and EXECUTE are granted automatically to the routine creator when the routine is created and dropped from the creator when the routine is dropped. See Section 19.2.2, "Stored Routines and MySQL Privileges".) The IF EXISTS clause is a MySQL extension. It prevents an error from occurring if the procedure or function does not exist. A warning is produced that can be viewed with SHOW WARNINGS. DROP FUNCTION is also used to drop user-defined functions (see Section 13.7.3.2, "DROP FUNCTION Syntax"). 13.1.27. DROP SERVER SyntaxDROP SERVER [ IF EXISTS ] server_name Drops the server definition for the server named server_name. The corresponding row in the mysql.servers table is deleted. This statement requires the SUPER privilege. Dropping a server for a table does not affect any FEDERATED tables that used this connection information when they were created. See Section 13.1.16, "CREATE SERVER Syntax". DROP SERVER does not cause an automatic commit. In MySQL 5.5, DROP SERVER is not written to the binary log, regardless of the logging format that is in use. 13.1.28. DROP TABLE SyntaxDROP [TEMPORARY] TABLE [IF EXISTS] tbl_name [, tbl_name] ... [RESTRICT | CASCADE] DROP TABLE removes one or more tables. You must have the DROP privilege for each table. All table data and the table definition are removed, so be careful with this statement! If any of the tables named in the argument list do not exist, MySQL returns an error indicating by name which nonexisting tables it was unable to drop, but it also drops all of the tables in the list that do exist. Note that for a partitioned table, DROP TABLE permanently removes the table definition, all of its partitions, and all of the data which was stored in those partitions. It also removes the partitioning definition (.par) file associated with the dropped table. Use IF EXISTS to prevent an error from occurring for tables that do not exist. A NOTE is generated for each nonexistent table when using IF EXISTS. See Section 13.7.5.41, "SHOW WARNINGS Syntax". RESTRICT and CASCADE are permitted to make porting easier. In MySQL 5.5, they do nothing. Note DROP TABLE automatically commits the current active transaction, unless you use the TEMPORARY keyword. The TEMPORARY keyword has the following effects: The statement drops only TEMPORARY tables. The statement does not end an ongoing transaction. No access rights are checked. (A TEMPORARY table is visible only to the session that created it, so no check is necessary.)
Using TEMPORARY is a good way to ensure that you do not accidentally drop a non-TEMPORARY table. 13.1.30. DROP TRIGGER SyntaxDROP TRIGGER [IF EXISTS] [schema_name.]trigger_name This statement drops a trigger. The schema (database) name is optional. If the schema is omitted, the trigger is dropped from the default schema. DROP TRIGGER requires the TRIGGER privilege for the table associated with the trigger. Use IF EXISTS to prevent an error from occurring for a trigger that does not exist. A NOTE is generated for a nonexistent trigger when using IF EXISTS. See Section 13.7.5.41, "SHOW WARNINGS Syntax". Triggers for a table are also dropped if you drop the table. Note When upgrading from a version of MySQL older than MySQL 5.0.10 to 5.0.10 or newer-including all MySQL 5.5 releases-you must drop all triggers and re-create them. Otherwise, DROP TRIGGER does not work for older triggers after the upgrade. See Upgrading from MySQL 4.1 to 5.0, for a suggested upgrade procedure. 13.1.31. DROP VIEW SyntaxDROP VIEW [IF EXISTS] view_name [, view_name] ... [RESTRICT | CASCADE] DROP VIEW removes one or more views. You must have the DROP privilege for each view. If any of the views named in the argument list do not exist, MySQL returns an error indicating by name which nonexisting views it was unable to drop, but it also drops all of the views in the list that do exist. The IF EXISTS clause prevents an error from occurring for views that don't exist. When this clause is given, a NOTE is generated for each nonexistent view. See Section 13.7.5.41, "SHOW WARNINGS Syntax". RESTRICT and CASCADE, if given, are parsed and ignored. 13.1.32. RENAME TABLE SyntaxRENAME TABLE tbl_name TO new_tbl_name [, tbl_name2 TO new_tbl_name2] ... This statement renames one or more tables. The rename operation is done atomically, which means that no other session can access any of the tables while the rename is running. For example, if you have an existing table old_table, you can create another table new_table that has the same structure but is empty, and then replace the existing table with the empty one as follows (assuming that backup_table does not already exist): CREATE TABLE new_table (...);RENAME TABLE old_table TO backup_table, new_table TO old_table; If the statement renames more than one table, renaming operations are done from left to right. If you want to swap two table names, you can do so like this (assuming that tmp_table does not already exist): RENAME TABLE old_table TO tmp_table, new_table TO old_table, tmp_table TO new_table; As long as two databases are on the same file system, you can use RENAME TABLE to move a table from one database to another: RENAME TABLE current_db.tbl_name TO other_db.tbl_name; If there are any triggers associated with a table which is moved to a different database using RENAME TABLE, then the statement fails with the error Trigger in wrong schema. RENAME TABLE also works for views, as long as you do not try to rename a view into a different database. Any privileges granted specifically for the renamed table or view are not migrated to the new name. They must be changed manually. When you execute RENAME, you cannot have any locked tables or active transactions. You must also have the ALTER and DROP privileges on the original table, and the CREATE and INSERT privileges on the new table. If MySQL encounters any errors in a multiple-table rename, it does a reverse rename for all renamed tables to return everything to its original state. You cannot use RENAME to rename a TEMPORARY table. However, you can use ALTER TABLE instead: mysql> ALTER TABLE orig_name RENAME new_name; 13.1.33. TRUNCATE TABLE SyntaxTRUNCATE [TABLE] tbl_name TRUNCATE TABLE empties a table completely. It requires the DROP privilege. Logically, TRUNCATE TABLE is similar to a DELETE statement that deletes all rows, or a sequence of DROP TABLE and CREATE TABLE statements. To achieve high performance, it bypasses the DML method of deleting data. Thus, it cannot be rolled back, it does not cause ON DELETE triggers to fire, and it cannot be performed for InnoDB tables with parent-child foreign key relationships. Although TRUNCATE TABLE is similar to DELETE, it is classified as a DDL statement rather than a DML statement. It differs from DELETE in the following ways in MySQL 5.5: Truncate operations drop and re-create the table, which is much faster than deleting rows one by one, particularly for large tables. Truncate operations cause an implicit commit, and so cannot be rolled back. Truncation operations cannot be performed if the session holds an active table lock. TRUNCATE TABLE fails for an InnoDB table if there are any FOREIGN KEY constraints from other tables that reference the table. Foreign key constraints between columns of the same table are permitted. Truncation operations do not return a meaningful value for the number of deleted rows. The usual result is "0 rows affected," which should be interpreted as "no information." As long as the table format file tbl_name.frm is valid, the table can be re-created as an empty table with TRUNCATE TABLE, even if the data or index files have become corrupted. Any AUTO_INCREMENT value is reset to its start value. This is true even for MyISAM and InnoDB, which normally do not reuse sequence values. When used with partitioned tables, TRUNCATE TABLE preserves the partitioning; that is, the data and index files are dropped and re-created, while the partition definitions (.par) file is unaffected. The TRUNCATE TABLE statement does not invoke ON DELETE triggers.
TRUNCATE TABLE for a table closes all handlers for the table that were opened with HANDLER OPEN. TRUNCATE TABLE is treated for purposes of binary logging and replication as DROP TABLE followed by CREATE TABLE-that is, as DDL rather than DML. This is due to the fact that, when using InnoDB and other transactional storage engines where the transaction isolation level does not permit statement-based logging (READ COMMITTED or READ UNCOMMITTED), the statement was not logged and replicated when using STATEMENT or MIXED logging mode. (Bug #36763) However, it is still applied on replication slaves using InnoDB in the manner described previously. TRUNCATE TABLE can be used with Performance Schema summary tables, but the effect is to reset the summary columns to 0 or NULL, not to remove rows. See Section 21.7.4, "Performance Schema Summary Tables". Copyright © 1997, 2013, Oracle and/or its affiliates. All rights reserved. Legal Notices |
| |
