13.1 Data Definition Statements

 File: manual.info.tmp, Node: alter-database, Next: alter-event, Prev: sql-data-definition-statements, Up: sql-data-definition-statements

13.1.1 ALTER DATABASE Statement

 ALTER {DATABASE | SCHEMA} [DB_NAME]
     ALTER_OPTION ...
 ALTER {DATABASE | SCHEMA} DB_NAME
     UPGRADE DATA DIRECTORY NAME

 ALTER_OPTION: {
     [DEFAULT] CHARACTER SET [=] CHARSET_NAME
   | [DEFAULT] COLLATE [=] COLLATION_NAME
 }

note 'ALTER DATABASE': 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. This statement requires the 'ALTER' privilege on the database. note 'ALTER SCHEMA': alter-database. is a synonym for *note 'ALTER DATABASE': alter-database.

The database name can be omitted from the first syntax, in which case the statement applies to the default database. An error occurs if there is no default database.

Character Set and Collation Options

The 'CHARACTER SET' clause changes the default database character set. The 'COLLATE' clause changes the default database collation. For information about character set and collation names, see *note charset::.

To see the available character sets and collations, use the note 'SHOW CHARACTER SET': show-character-set. and note 'SHOW COLLATION': show-collation. statements, respectively. See note show-character-set::, and note show-collation::.

A stored routine that uses the database defaults when the routine is created includes those defaults as part of its definition. (In a stored routine, variables with character data types use the database defaults if the character set or collation are not specified explicitly. See *note create-procedure::.) If you change the default character set or collation for a database, any stored routines that are to use the new defaults must be dropped and recreated.

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 *note identifier-mapping::). This clause is for use under these conditions:

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.

Note:

The 'UPGRADE DATA DIRECTORY NAME' clause is deprecated in MySQL 5.7 and removed in MySQL 8.0. If it is necessary to convert MySQL 5.0 database or table names, a workaround is to upgrade a MySQL 5.0 installation to MySQL 5.1 before upgrading to MySQL 8.0.

 File: manual.info.tmp, Node: alter-event, Next: alter-function, Prev: alter-database, Up: sql-data-definition-statements

13.1.2 ALTER EVENT Statement

 ALTER
     [DEFINER = USER]
     EVENT EVENT_NAME
     [ON SCHEDULE SCHEDULE]
     [ON COMPLETION [NOT] PRESERVE]
     [RENAME TO NEW_EVENT_NAME]
     [ENABLE | DISABLE | DISABLE ON SLAVE]
     [COMMENT 'STRING']
     [DO EVENT_BODY]

The note 'ALTER EVENT': 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 note 'DO': do. clauses is exactly the same as when used with note 'CREATE EVENT': create-event. (See note create-event::.)

Any user can alter an event defined on a database for which that user has the 'EVENT' privilege. When a user executes a successful *note 'ALTER EVENT': alter-event. statement, that user becomes the definer for the affected event.

*note 'ALTER 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 *note 'ALTER EVENT': 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 note 'ALTER EVENT': alter-event. statement only for those characteristics that you want to change; omitted options keep their existing values. This includes any default values for note 'CREATE EVENT': create-event. such as 'ENABLE'.

To disable 'myevent', use this *note 'ALTER EVENT': 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 loadable functions in such expressions, and you cannot use any table references; however, you can use 'SELECT FROM DUAL'. This is true for both note 'ALTER EVENT': alter-event. and note 'CREATE EVENT': create-event. statements. References to stored routines, loadable functions, and tables in such cases are specifically not permitted, and fail with an error (see Bug #22830).

Although an note 'ALTER EVENT': alter-event. statement that contains another note 'ALTER EVENT': alter-event. statement in its *note 'DO': 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 *note 'ALTER EVENT': 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 replica instead of 'ENABLE' or 'DISABLE' to indicate an event that was created on the source and replicated to the replica, but that is not executed on the replica. 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 *note replication-features-invoked::, for more information.

 File: manual.info.tmp, Node: alter-function, Next: alter-instance, Prev: alter-event, Up: sql-data-definition-statements

13.1.3 ALTER FUNCTION Statement

 ALTER 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 note 'ALTER FUNCTION': 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 note 'DROP FUNCTION': drop-function. and *note 'CREATE FUNCTION': 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 note 'ALTER FUNCTION': alter-function. statement might also require the 'SUPER' privilege, as described in note stored-programs-logging::.

 File: manual.info.tmp, Node: alter-instance, Next: alter-logfile-group, Prev: alter-function, Up: sql-data-definition-statements

13.1.4 ALTER INSTANCE Statement

 ALTER INSTANCE ROTATE INNODB MASTER KEY

'ALTER INSTANCE', introduced in MySQL 5.7.11, defines actions applicable to a MySQL server instance. The statement supports these actions:

 File: manual.info.tmp, Node: alter-logfile-group, Next: alter-procedure, Prev: alter-instance, Up: sql-data-definition-statements

13.1.5 ALTER LOGFILE GROUP Statement

 ALTER 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 *note 'ALTER LOGFILE GROUP': alter-logfile-group. statement has one and only one 'ADD UNDOFILE' clause. No 'DROP UNDOFILE' clause is currently supported.

Note:

All NDB 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 defaults to 134217728 (128 MB). 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' (megabytes) or 'G' (gigabytes). (Bug #13116514, Bug #16104705, Bug #62858)

On 32-bit systems, the maximum supported value for 'INITIAL_SIZE' is 4294967296 (4 GB). (Bug #29186)

The minimum allowed value for 'INITIAL_SIZE' is 1048576 (1 MB). (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 'note 'NDBCLUSTER': mysql-cluster.' and 'note 'NDB': mysql-cluster.'. The two values are equivalent.

Here is an example, which assumes that the log file group 'lg_3' has already been created using note 'CREATE LOGFILE GROUP': create-logfile-group. (see note create-logfile-group::):

 ALTER LOGFILE GROUP lg_3
     ADD UNDOFILE 'undo_10.dat'
     INITIAL_SIZE=32M
     ENGINE=NDBCLUSTER;

When note 'ALTER LOGFILE GROUP': alter-logfile-group. is used with 'ENGINE = NDBCLUSTER' (alternatively, 'ENGINE = NDB'), an 'UNDO' log file is created on each NDB Cluster data node. You can verify that the 'UNDO' files were created and obtain information about them by querying the Information Schema note 'FILES': information-schema-files-table. 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 *note 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.

note 'ALTER LOGFILE GROUP': alter-logfile-group. is useful only with Disk Data storage for NDB Cluster. For more information, see note mysql-cluster-disk-data::.

 File: manual.info.tmp, Node: alter-procedure, Next: alter-server, Prev: alter-logfile-group, Up: sql-data-definition-statements

13.1.6 ALTER PROCEDURE Statement

 ALTER 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 note 'ALTER PROCEDURE': 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 note 'DROP PROCEDURE': drop-procedure. and *note 'CREATE PROCEDURE': 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 *note stored-routines-privileges::.

 File: manual.info.tmp, Node: alter-server, Next: alter-table, Prev: alter-procedure, Up: sql-data-definition-statements

13.1.7 ALTER SERVER Statement

 ALTER SERVER  SERVER_NAME
     OPTIONS (OPTION [, OPTION] ...)

Alters the server information for 'SERVER_NAME', adjusting any of the options permitted in the *note 'CREATE SERVER': 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' causes an implicit commit. See *note implicit-commit::.

'ALTER SERVER' is not written to the binary log, regardless of the logging format that is in use.

 File: manual.info.tmp, Node: alter-table, Next: alter-tablespace, Prev: alter-server, Up: sql-data-definition-statements

13.1.8 ALTER TABLE Statement

*note 'ALTER TABLE': 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.

There are several additional aspects to the 'ALTER TABLE' statement, described under the following topics in this section:

Table Options

TABLE_OPTIONS signifies table options of the kind that can be used in the *note 'CREATE TABLE': create-table. statement, such as 'ENGINE', 'AUTO_INCREMENT', 'AVG_ROW_LENGTH', 'MAX_ROWS', 'ROW_FORMAT', or 'TABLESPACE'.

For descriptions of all table options, see note create-table::. However, note 'ALTER TABLE': alter-table. ignores 'DATA DIRECTORY' and 'INDEX DIRECTORY' when given as table options. *note 'ALTER TABLE': alter-table. permits them only as partitioning options, and, as of MySQL 5.7.17, requires that you have the 'FILE' privilege.

Use of table options with *note 'ALTER TABLE': alter-table. provides a convenient way of altering single table characteristics. For example:

To verify that the table options were changed as intended, use note 'SHOW CREATE TABLE': show-create-table, or query the Information Schema note 'TABLES': information-schema-tables-table. table.

Performance and Space Requirements

*note 'ALTER TABLE': alter-table. operations are processed using one of the following algorithms:

For tables using the *note 'NDB': mysql-cluster. storage engine, these algorithms work as follows:

See *note mysql-cluster-online-operations::, for more information.

The 'ALGORITHM' clause is optional. If the 'ALGORITHM' clause is omitted, MySQL uses 'ALGORITHM=INPLACE' for storage engines and *note 'ALTER TABLE': alter-table. clauses that support it. Otherwise, 'ALGORITHM=COPY' is used.

Specifying an 'ALGORITHM' clause requires the operation to use the specified algorithm for clauses and storage engines that support it, or fail with an error otherwise. Specifying 'ALGORITHM=DEFAULT' is the same as omitting the 'ALGORITHM' clause.

note 'ALTER TABLE': alter-table. operations that use the 'COPY' algorithm wait for other operations that are modifying the table to complete. After alterations are applied to the table copy, data is copied over, the original table is deleted, and the table copy is renamed to the name of the original table. While the note 'ALTER TABLE': alter-table. operation executes, the original table is readable by other sessions (with the exception noted shortly). Updates and writes to the table started after the *note 'ALTER TABLE': alter-table. operation begins are stalled until the new table is ready, then are automatically redirected to the new table. The temporary copy of the table is created in the database directory of the original table unless it is a 'RENAME TO' operation that moves the table to a database that resides in a different directory.

The exception referred to earlier is that *note 'ALTER TABLE': alter-table. blocks reads (not just writes) at the point where it is ready to install a new version of the table '.frm' file, discard the old file, and clear outdated table structures from the table and table definition caches. At this point, it must acquire an exclusive lock. To do so, it waits for current readers to finish, and blocks new reads and writes.

An note 'ALTER TABLE': alter-table. operation that uses the 'COPY' algorithm prevents concurrent DML operations. Concurrent queries are still allowed. That is, a table-copying operation always includes at least the concurrency restrictions of 'LOCK=SHARED' (allow queries but not DML). You can further restrict concurrency for operations that support the 'LOCK' clause by specifying 'LOCK=EXCLUSIVE', which prevents DML and queries. For more information, see note alter-table-concurrency::.

To force use of the 'COPY' algorithm for an *note 'ALTER TABLE': alter-table. operation that would otherwise not use it, enable the 'old_alter_table' system variable or specify 'ALGORITHM=COPY'. If there is a conflict between the 'old_alter_table' setting and an 'ALGORITHM' clause with a value other than 'DEFAULT', the 'ALGORITHM' clause takes precedence.

For 'InnoDB' tables, an *note 'ALTER TABLE': alter-table. operation that uses the 'COPY' algorithm on a table that resides in a shared tablespace can increase the amount of space used by the tablespace. Such operations require as much additional space as the data in the table plus indexes. For a table residing in a shared tablespace, the additional space used during the operation is not released back to the operating system as it is for a table that resides in a file-per-table tablespace.

For information about space requirements for online DDL operations, see *note innodb-online-ddl-space-requirements::.

*note 'ALTER TABLE': alter-table. operations that support the 'INPLACE' algorithm include:

*note 'ALTER TABLE': alter-table. upgrades MySQL 5.5 temporal columns to 5.6 format for 'ADD COLUMN', 'CHANGE COLUMN', 'MODIFY COLUMN', 'ADD INDEX', and 'FORCE' operations. This conversion cannot be done using the 'INPLACE' algorithm because the table must be rebuilt, so specifying 'ALGORITHM=INPLACE' in these cases results in an error. Specify 'ALGORITHM=COPY' if necessary.

If an 'ALTER TABLE' operation on a multicolumn index used to partition a table by 'KEY' changes the order of the columns, it can only be performed using 'ALGORITHM=COPY'.

The 'WITHOUT VALIDATION' and 'WITH VALIDATION' clauses affect whether note 'ALTER TABLE': alter-table. performs an in-place operation for virtual generated column modifications. See note alter-table-generated-columns::.

NDB Cluster formerly supported online 'ALTER TABLE' operations using the 'ONLINE' and 'OFFLINE' keywords. These keywords are no longer supported; their use causes a syntax error. MySQL NDB Cluster 7.5 (and later) supports online operations using the same 'ALGORITHM=INPLACE' syntax used with the standard MySQL Server. 'NDB' does not support changing a tablespace online. See *note mysql-cluster-online-operations::, for more information.

'ALTER TABLE' with 'DISCARD ... PARTITION ... TABLESPACE' or 'IMPORT ... PARTITION ... TABLESPACE' does not create any temporary tables or temporary partition files.

'ALTER TABLE' with 'ADD PARTITION', 'DROP PARTITION', 'COALESCE PARTITION', 'REBUILD PARTITION', or 'REORGANIZE PARTITION' does not create temporary tables (except when used with *note 'NDB': mysql-cluster. 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.

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.

Concurrency Control

For *note 'ALTER TABLE': alter-table. operations that support it, you can use the 'LOCK' clause to control the level of concurrent reads and writes on a table while it is being altered. Specifying a non-default value for this clause enables you to require a certain amount of concurrent access or exclusivity during the alter operation, and halts the operation if the requested degree of locking is not available. The parameters for the 'LOCK' clause are:

Adding and Dropping Columns

Use 'ADD' to add new columns to a table, and 'DROP' to remove existing columns. 'DROP COL_NAME' is a MySQL extension to standard SQL.

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.

If a table contains only one column, the column cannot be dropped. If what you intend is to remove the table, use the *note 'DROP TABLE': drop-table. statement instead.

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.

Renaming, Redefining, and Reordering Columns

The 'CHANGE', 'MODIFY', and 'ALTER' clauses enable the names and definitions of existing columns to be altered. They have these comparative characteristics:

'CHANGE' is a MySQL extension to standard SQL. 'MODIFY' is a MySQL extension for Oracle compatibility.

To alter a column to change both its name and definition, use 'CHANGE', specifying the old and new names and the new definition. For example, to rename an 'INT NOT NULL' column from 'a' to 'b' and change its definition to use the 'BIGINT' data type while retaining the 'NOT NULL' attribute, do this:

 ALTER TABLE t1 CHANGE a b BIGINT NOT NULL;

To change a column definition but not its name, use 'CHANGE' or 'MODIFY'. With 'CHANGE', the syntax requires two column names, so you must specify the same name twice to leave the name unchanged. For example, to change the definition of column 'b', do this:

 ALTER TABLE t1 CHANGE b b INT NOT NULL;

'MODIFY' is more convenient to change the definition without changing the name because it requires the column name only once:

 ALTER TABLE t1 MODIFY b INT NOT NULL;

To change a column name but not its definition, use 'CHANGE'. The syntax requires a column definition, so to leave the definition unchanged, you must respecify the definition the column currently has. For example, to rename an 'INT NOT NULL' column from 'b' to 'a', do this:

 ALTER TABLE t1 CHANGE b a INT NOT NULL;

For column definition changes using 'CHANGE' or 'MODIFY', the 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, intending to change only 'INT' to 'BIGINT':

 ALTER TABLE t1 MODIFY col1 BIGINT;

That statement changes the data type from 'INT' to 'BIGINT', but it also drops the 'UNSIGNED', 'DEFAULT', and 'COMMENT' attributes. To retain them, the statement must include them explicitly:

 ALTER TABLE t1 MODIFY col1 BIGINT UNSIGNED DEFAULT 1 COMMENT 'my column';

For data type changes 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 note 'ALTER TABLE': alter-table. (see note sql-mode::).

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.

For columns renamed by 'CHANGE', MySQL automatically renames these references to the renamed column:

For columns renamed by 'CHANGE', MySQL does not automatically rename these references to the renamed column:

To reorder columns within a table, use 'FIRST' and 'AFTER' in 'CHANGE' or 'MODIFY' operations.

'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 *note data-type-defaults::.

'ALTER ... SET DEFAULT' cannot be used with the 'CURRENT_TIMESTAMP' function.

Primary Keys and Indexes

'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 *note primary-key-optimization::.

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.

note 'DROP INDEX': drop-index. removes an index. This is a MySQL extension to standard SQL. See note drop-index::. To determine index names, use 'SHOW INDEX FROM TBL_NAME'.

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 *note create-index::. The preferred position is after the column list. You should expect support for use of the option before the column list to be removed in a future MySQL release.

INDEX_OPTION values specify additional options for an index. For details about permissible INDEX_OPTION values, see *note create-index::.

'RENAME INDEX OLD_INDEX_NAME TO NEW_INDEX_NAME' renames an index. This is a MySQL extension to standard SQL. The content of the table remains unchanged. OLD_INDEX_NAME must be the name of an existing index in the table that is not dropped by the same *note 'ALTER TABLE': alter-table. statement. NEW_INDEX_NAME is the new index name, which cannot duplicate the name of an index in the resulting table after changes have been applied. Neither index name can be 'PRIMARY'.

If you use note 'ALTER TABLE': alter-table. on a 'MyISAM' table, all nonunique indexes are created in a separate batch (as for note 'REPAIR TABLE': repair-table.). This should make *note 'ALTER TABLE': alter-table. much faster when you have many indexes.

For 'MyISAM' tables, key updating can be controlled explicitly. Use 'ALTER TABLE ... DISABLE KEYS' to tell MySQL to stop updating nonunique indexes. Then use 'ALTER TABLE ... ENABLE KEYS' to re-create missing indexes. 'MyISAM' 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 note 'SELECT': select. and note 'EXPLAIN': explain. that otherwise would use them.

After an note 'ALTER TABLE': alter-table. statement, it may be necessary to run note 'ANALYZE TABLE': analyze-table. to update index cardinality information. See *note show-index::.

Foreign Keys and Other Constraints

The 'FOREIGN KEY' and 'REFERENCES' clauses are supported by the 'InnoDB' and 'NDB' storage engines, which implement 'ADD [CONSTRAINT [SYMBOL]] FOREIGN KEY [INDEX_NAME] (...) REFERENCES ... (...)'. See *note constraint-foreign-key::. For other storage engines, the clauses are parsed but ignored.

The 'CHECK' constraint clause is parsed but ignored by all storage engines. See note create-table::. 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 note differences-from-ansi::.

For note 'ALTER TABLE': alter-table, unlike note 'CREATE TABLE': create-table, 'ADD FOREIGN KEY' ignores INDEX_NAME if given and uses an automatically generated foreign key name. As a workaround, include the 'CONSTRAINT' clause to specify the foreign key name:

 ADD CONSTRAINT NAME FOREIGN KEY (....) ...

Important:

MySQL silently ignores inline 'REFERENCES' specifications, where the references are defined as part of the column specification. MySQL accepts only 'REFERENCES' clauses defined as part of a separate 'FOREIGN KEY' specification.

Note:

Partitioned 'InnoDB' tables do not support foreign keys. This restriction does not apply to 'NDB' tables, including those explicitly partitioned by '[LINEAR] KEY'. For more information, see *note partitioning-limitations-storage-engines::.

MySQL Server and NDB Cluster both support the use of *note 'ALTER TABLE': alter-table. to drop foreign keys:

 ALTER TABLE TBL_NAME DROP FOREIGN KEY FK_SYMBOL;

Adding and dropping a foreign key in the same note 'ALTER TABLE': alter-table. statement is supported for note 'ALTER TABLE ... ALGORITHM=INPLACE': alter-table. but not for *note 'ALTER TABLE ... ALGORITHM=COPY': alter-table.

The server prohibits changes to foreign key columns that have the potential to cause loss of referential integrity. A workaround is to use note 'ALTER TABLE ... DROP FOREIGN KEY': alter-table. before changing the column definition and note 'ALTER TABLE ... ADD FOREIGN KEY': alter-table. afterward. Examples of prohibited changes include:

'ALTER TABLE TBL_NAME RENAME NEW_TBL_NAME' changes internally generated foreign key constraint names and user-defined foreign key constraint names that begin with the string 'TBL_NAME_ibfk_' to reflect the new table name. 'InnoDB' interprets foreign key constraint names that begin with the string 'TBL_NAME_ibfk_' as internally generated names.

Changing the Character Set

To change the table default character set and all character columns (note 'CHAR': char, note 'VARCHAR': char, *note 'TEXT': blob.) to a new character set, use a statement like this:

 ALTER TABLE TBL_NAME CONVERT TO CHARACTER SET CHARSET_NAME;

The statement also changes the collation of all character columns. If you specify no 'COLLATE' clause to indicate which collation to use, the statement uses default collation for the character set. If this collation is inappropriate for the intended table use (for example, if it would change from a case-sensitive collation to a case-insensitive collation), specify a collation explicitly.

For a column that has a data type of note 'VARCHAR': char. or one of the note 'TEXT': blob. types, 'CONVERT TO CHARACTER SET' changes 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 note 'TEXT': blob. 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' note 'TEXT': blob. 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 x 65,535 = 196,605 bytes. That length does not fit in a note 'TEXT': blob. column's length bytes, so MySQL converts the data type to note 'MEDIUMTEXT': blob, which is the smallest string type for which the length bytes can record a value of 196,605. Similarly, a note 'VARCHAR': char. column might be converted to note 'MEDIUMTEXT': blob.

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 note 'CHAR': char, note 'VARCHAR': char, and note 'TEXT': blob. columns are converted to their corresponding binary string types (note 'BINARY': binary-varbinary, note 'VARBINARY': binary-varbinary, note 'BLOB': blob.). This means that the columns no longer have a character set and a subsequent 'CONVERT TO' operation does not apply to them.

If CHARSET_NAME is 'DEFAULT' in a 'CONVERT TO CHARACTER SET' operation, the character set named by the 'character_set_database' system variable is used.

Warning:

The 'CONVERT TO' operation converts column values between the original and named 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 *note 'BLOB': 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').

When the 'foreign_key_checks' system variable is enabled, which is the default setting, character set conversion is not permitted on tables that include a character string column used in a foreign key constraint. The workaround is to disable 'foreign_key_checks' before performing the character set conversion. You must perform the conversion on both tables involved in the foreign key constraint before re-enabling 'foreign_key_checks'. If you re-enable 'foreign_key_checks' after converting only one of the tables, an 'ON DELETE CASCADE' or 'ON UPDATE CASCADE' operation could corrupt data in the referencing table due to implicit conversion that occurs during these operations (Bug #45290, Bug #74816).

Discarding and Importing InnoDB Tablespaces

An 'InnoDB' table created in its own file-per-table tablespace can be imported from a backup or from another MySQL server instance using 'DISCARD TABLEPACE' and 'IMPORT TABLESPACE' clauses. See *note innodb-table-import::.

Row Order for MyISAM Tables

'ORDER BY' enables you to create the new table with the rows in a specific order. This option is useful primarily when you know that you 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.

Note:

The table does not remain in the specified order after inserts and deletes.

'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 because 'InnoDB' always orders table rows according to the clustered index.

When used on a partitioned table, 'ALTER TABLE ... ORDER BY' orders rows within each partition only.

Partitioning Options

PARTITION_OPTIONS signifies options that can be used with partitioned tables for repartitioning, to add, drop, discard, import, merge, and split partitions, and to perform partitioning maintenance.

It is possible for an *note 'ALTER TABLE': alter-table. statement to contain a 'PARTITION BY' or 'REMOVE PARTITIONING' clause in an addition to other alter specifications, but the 'PARTITION BY' or 'REMOVE PARTITIONING' clause must be specified last after any other specifications. The 'ADD PARTITION', 'DROP PARTITION', 'DISCARD PARTITION', 'IMPORT PARTITION', 'COALESCE PARTITION', 'REORGANIZE PARTITION', 'EXCHANGE PARTITION', 'ANALYZE PARTITION', 'CHECK PARTITION', and 'REPAIR PARTITION' options cannot be combined with other alter specifications in a single 'ALTER TABLE', since the options just listed act on individual partitions.

For more information about partition options, see note create-table::, and note alter-table-partition-operations::. For information about and examples of 'ALTER TABLE ... EXCHANGE PARTITION' statements, see *note partitioning-management-exchange::.

Prior to MySQL 5.7.6, partitioned 'InnoDB' tables used the generic 'ha_partition' partitioning handler employed by 'MyISAM' and other storage engines not supplying their own partitioning handlers; in MySQL 5.7.6 and later, such tables are created using the 'InnoDB' storage engine's own (or 'native') partitioning handler. Beginning with MySQL 5.7.9, you can upgrade an 'InnoDB' table that was created in MySQL 5.7.6 or earlier (that is, created using 'ha_partition') to the 'InnoDB' native partition handler using 'ALTER TABLE ... UPGRADE PARTITIONING'. (Bug #76734, Bug #20727344) This 'ALTER TABLE' syntax does not accept any other options and can be used only on a single table at a time. You can also use *note 'mysql_upgrade': mysql-upgrade. in MySQL 5.7.9 or later to upgrade older partitioned 'InnoDB' tables to the native partitioning handler.

 File: manual.info.tmp, Node: alter-table-partition-operations, Next: alter-table-generated-columns, Prev: alter-table, Up: alter-table

13.1.8.1 ALTER TABLE Partition Operations .........................................

Partitioning-related clauses for *note 'ALTER TABLE': alter-table. can be used with partitioned tables for repartitioning, to add, drop, discard, import, merge, and split partitions, and to perform partitioning maintenance.

When 'ALTER TABLE ... EXCHANGE PARTITION' or 'ALTER TABLE ... TRUNCATE PARTITION' is run against a partitioned table that uses note 'MyISAM': myisam-storage-engine. (or another storage engine that makes use of table-level locking), only those partitions that are actually read from are locked. (This does not apply to partitioned tables using a storage enginethat employs row-level locking, such as note 'InnoDB': innodb-storage-engine.) See *note partitioning-limitations-locking::.

It is possible for an *note 'ALTER TABLE': alter-table. statement to contain a 'PARTITION BY' or 'REMOVE PARTITIONING' clause in an addition to other alter specifications, but the 'PARTITION BY' or 'REMOVE PARTITIONING' clause must be specified last after any other specifications.

The 'ADD PARTITION', 'DROP PARTITION', 'COALESCE PARTITION', 'REORGANIZE PARTITION', 'ANALYZE PARTITION', 'CHECK PARTITION', and 'REPAIR PARTITION' options cannot be combined with other alter specifications in a single 'ALTER TABLE', since the options just listed act on individual partitions. For more information, see *note alter-table-partition-operations::.

Only a single instance of any one of the following options can be used in a given *note 'ALTER TABLE': alter-table. statement: 'PARTITION BY', 'ADD PARTITION', 'DROP PARTITION', 'TRUNCATE PARTITION', 'EXCHANGE 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;

'REBUILD' operations are currently unsupported for subpartitions. The 'REBUILD' keyword is expressly disallowed with subpartitions, and causes 'ALTER TABLE' to fail with an error if so used.

'CHECK PARTITION 'and' REPAIR PARTITION' operations fail when the partition to be checked or repaired contains any duplicate key errors.

For more information about these statements, see *note partitioning-maintenance::.

 File: manual.info.tmp, Node: alter-table-generated-columns, Next: alter-table-examples, Prev: alter-table-partition-operations, Up: alter-table

13.1.8.2 ALTER TABLE and Generated Columns ..........................................

'ALTER TABLE' operations permitted for generated columns are 'ADD', 'MODIFY', and 'CHANGE'.

 File: manual.info.tmp, Node: alter-table-examples, Prev: alter-table-generated-columns, Up: alter-table

13.1.8.3 ALTER TABLE Examples .............................

Begin with a table 't1' 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 *note 'INTEGER': integer-types. 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 *note 'TIMESTAMP': datetime. 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 note 'NDB': mysql-cluster. tables, it is also possible to change the storage type used for a table or column. For example, consider an note 'NDB': mysql-cluster. 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 *note 'ALTER TABLE': 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: 0

 mysql> SHOW CREATE TABLE t1\G
 *************************** 1. row ***************************
        Table: t1
 Create Table: CREATE TABLE `t1` (
   `c1` int(11) DEFAULT NULL
 ) /*!50100 TABLESPACE ts_1 STORAGE DISK */
 ENGINE=ndbcluster DEFAULT CHARSET=latin1
 1 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 *note 'ALTER TABLE': 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: 0
 mysql> SHOW CREATE TABLE t2\G
 *************************** 1. row ***************************
        Table: t1
 Create Table: CREATE TABLE `t2` (
   `c1` int(11) DEFAULT NULL
 ) /*!50100 TABLESPACE ts_1 STORAGE DISK */
 ENGINE=ndbcluster DEFAULT CHARSET=latin1
 1 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 an NDB Cluster Disk Data table with two columns, using this *note 'CREATE TABLE': 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 note 'CREATE TABLE': create-table. statement). However, column 'c2' uses in-memory storage, as can be seen here in the output of SHOW note 'CREATE TABLE': create-table.:

 mysql> SHOW CREATE TABLE t3\G
 *************************** 1. row ***************************
        Table: t3
 Create 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=latin1
 1 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 *note 'ALTER TABLE': alter-table. or by using the 'AUTO_INCREMENT=VALUE' table option.

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 replica and the source. 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 source and replica, 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 also produces 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 source and replica, 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 TABLE t1;
 ALTER TABLE t2 RENAME t1;

 File: manual.info.tmp, Node: alter-tablespace, Next: alter-view, Prev: alter-table, Up: sql-data-definition-statements

13.1.9 ALTER TABLESPACE Statement

 ALTER TABLESPACE TABLESPACE_NAME
     {ADD | DROP} DATAFILE 'FILE_NAME'
     [INITIAL_SIZE [=] SIZE]
     [WAIT]
     ENGINE [=] ENGINE_NAME

This statement is 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 134217728 (128 MB). 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' (megabytes) or 'G' (gigabytes).

Note:

All NDB 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 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 4294967296 (4 GB). (Bug #29186)

'INITIAL_SIZE' is rounded, explicitly, as for *note 'CREATE TABLESPACE': 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 note 'ALTER TABLESPACE': 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 note mysql-cluster-disk-data-objects::. In addition, any data file to be dropped must previously have been added to the tablespace with note 'CREATE TABLESPACE': create-tablespace. or note 'ALTER TABLESPACE': 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 note 'NDB': mysql-cluster. and note 'NDBCLUSTER': mysql-cluster.

'WAIT' is parsed but otherwise ignored, and so has no effect in MySQL 5.7. 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 *note 'FILES': information-schema-files-table. 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 *note information-schema-files-table::.

note 'ALTER TABLESPACE': alter-tablespace. is useful only with Disk Data storage for NDB Cluster. See note mysql-cluster-disk-data::.

 File: manual.info.tmp, Node: alter-view, Next: create-database, Prev: alter-tablespace, Up: sql-data-definition-statements

13.1.10 ALTER VIEW Statement

 ALTER
     [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
     [DEFINER = 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 note 'CREATE VIEW': create-view. see note create-view::). This statement requires the 'CREATE VIEW' and 'DROP' privileges for the view, and some privilege for each column referred to in the note 'SELECT': select. statement. note 'ALTER VIEW': alter-view. is permitted only to the definer or users with the 'SUPER' privilege.

 File: manual.info.tmp, Node: create-database, Next: create-event, Prev: alter-view, Up: sql-data-definition-statements

13.1.11 CREATE DATABASE Statement

 CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] DB_NAME
     [CREATE_OPTION] ...

 CREATE_OPTION: [DEFAULT] {
     CHARACTER SET [=] CHARSET_NAME
   | COLLATE [=] COLLATION_NAME
 }

note 'CREATE DATABASE': create-database. creates a database with the given name. To use this statement, you need the 'CREATE' privilege for the database. note 'CREATE SCHEMA': create-database. is a synonym for *note 'CREATE DATABASE': create-database.

An error occurs if the database exists and you did not specify 'IF NOT EXISTS'.

note 'CREATE DATABASE': create-database. is not permitted within a session that has an active note 'LOCK TABLES': lock-tables. statement.

Each CREATE_OPTION specifies a database characteristic. Database characteristics are stored in the 'db.opt' file in the database directory. The 'CHARACTER SET' option specifies the default database character set. The 'COLLATE' option specifies the default database collation. For information about character set and collation names, see *note charset::.

To see the available character sets and collations, use the note 'SHOW CHARACTER SET': show-character-set. and note 'SHOW COLLATION': show-collation. statements, respectively. See note show-character-set::, and note show-collation::.

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 note 'CREATE DATABASE': 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 note identifiers::. If a database name contains special characters, the name for the database directory contains encoded versions of those characters as described in *note identifier-mapping::.

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 *note 'SHOW DATABASES': show-databases.

When you create a database, let the server manage the directory and the files in it. Manipulating database directories and files directly can cause inconsistencies and unexpected results.

MySQL has no limit on the number of databases. The underlying file system may have a limit on the number of directories.

You can also use the note 'mysqladmin': mysqladmin. program to create databases. See note mysqladmin::.

 File: manual.info.tmp, Node: create-event, Next: create-function, Prev: create-database, Up: sql-data-definition-statements

13.1.12 CREATE EVENT Statement

 CREATE
     [DEFINER = USER]
     EVENT
     [IF NOT EXISTS]
     EVENT_NAME
     ON SCHEDULE SCHEDULE
     [ON COMPLETION [NOT] PRESERVE]
     [ENABLE | DISABLE | DISABLE ON SLAVE]
     [COMMENT 'STRING']
     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 does not run unless the Event Scheduler is enabled. For information about checking Event Scheduler status and enabling it if necessary, see *note events-configuration::.

note 'CREATE EVENT': create-event. requires the 'EVENT' privilege for the schema in which the event is to be created. If the 'DEFINER' clause is present, the privileges required depend on the USER value, as discussed in note stored-objects-security::.

The minimum requirements for a valid *note 'CREATE EVENT': create-event. statement are as follows:

This is an example of a minimal *note 'CREATE EVENT': 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 *note identifiers::.

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 the 'DEFINER' clause is present, the USER value should be a MySQL account specified as ''USER_NAME'@'HOST_NAME'', 'CURRENT_USER', or 'CURRENT_USER()'. The permitted USER values depend on the privileges you hold, as discussed in *note stored-objects-security::. Also see that section for additional information about event security.

If the 'DEFINER' clause is omitted, the default definer is the user who executes the *note 'CREATE EVENT': create-event. statement. This is the same as specifying 'DEFINER = CURRENT_USER' explicitly.

Within an event body, 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 *note account-activity-auditing::.

'IF NOT EXISTS' has the same meaning for note 'CREATE EVENT': create-event. as for note 'CREATE TABLE': 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:

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 loadable functions in such expressions, nor may you use any table references; however, you may use 'SELECT FROM DUAL'. This is true for both note 'CREATE EVENT': create-event. and note 'ALTER EVENT': alter-event. statements. References to stored functions, loadable 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 note events-metadata::. See also note show-events::, and *note 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 note 'ALTER EVENT': alter-event. (see note alter-event::).

A third value may also appear in place of 'ENABLE' or 'DISABLE'; 'DISABLE ON SLAVE' is set for the status of an event on a replica to indicate that the event was created on the source and replicated to the replica, but is not executed on the replica. See *note replication-features-invoked::.

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 note 'DO': 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 note stored-program-restrictions::.) 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 in effect when an event is created or altered, and always executes the event with this setting in force, regardless of the current server SQL mode when the event begins executing.

A note 'CREATE EVENT': create-event. statement that contains an note 'ALTER EVENT': alter-event. statement in its *note 'DO': 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 note 'SELECT': select. or note 'SHOW': 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 note 'SELECT ... INTO': select. and note 'INSERT INTO ... SELECT': insert-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 *note 'DO': 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 *note 'DO': 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 *note stored-programs-defining::.

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 privileges sufficient to set global system variables (see *note system-variable-privileges::), the event can read and write global variables. As granting such privileges 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 *note stored-routines-syntax::. You can create an event as part of a stored routine, but an event cannot be created by another event.

 File: manual.info.tmp, Node: create-function, Next: create-index, Prev: create-event, Up: sql-data-definition-statements

13.1.13 CREATE FUNCTION Statement

The *note 'CREATE FUNCTION': create-function. statement is used to create stored functions and loadable functions:

 File: manual.info.tmp, Node: create-index, Next: create-logfile-group, Prev: create-function, Up: sql-data-definition-statements

13.1.14 CREATE INDEX Statement

 CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX INDEX_NAME
     [INDEX_TYPE]
     ON TBL_NAME (KEY_PART,...)
     [INDEX_OPTION]
     [ALGORITHM_OPTION | LOCK_OPTION] ...

 KEY_PART:
     COL_NAME [(LENGTH)] [ASC | DESC]

 INDEX_OPTION: {
     KEY_BLOCK_SIZE [=] VALUE
   | INDEX_TYPE
   | WITH PARSER PARSER_NAME
   | COMMENT 'STRING'
 }

 INDEX_TYPE:
     USING {BTREE | HASH}

 ALGORITHM_OPTION:
     ALGORITHM [=] {DEFAULT | INPLACE | COPY}

 LOCK_OPTION:
     LOCK [=] {DEFAULT | NONE | SHARED | EXCLUSIVE}

Normally, you create all indexes on a table at the time the table itself is created with note 'CREATE TABLE': create-table. See note create-table::. This guideline is especially important for note 'InnoDB': innodb-storage-engine. tables, where the primary key determines the physical layout of rows in the data file. note 'CREATE INDEX': create-index. enables you to add indexes to existing tables.

note 'CREATE INDEX': create-index. is mapped to an note 'ALTER TABLE': alter-table. statement to create indexes. See note alter-table::. note 'CREATE INDEX': create-index. cannot be used to create a 'PRIMARY KEY'; use note 'ALTER TABLE': alter-table. instead. For more information about indexes, see note mysql-indexes::.

note 'InnoDB': innodb-storage-engine. supports secondary indexes on virtual columns. For more information, see note create-table-secondary-indexes::.

When the 'innodb_stats_persistent' setting is enabled, run the note 'ANALYZE TABLE': analyze-table. statement for an note 'InnoDB': innodb-storage-engine. table after creating an index on that table.

An index specification of the form '(KEY_PART1, KEY_PART2, ...)' creates an index with multiple key parts. Index key values are formed by concatenating the values of the given key parts. For example '(col1, col2, col3)' specifies a multiple-column index with index keys consisting of values from 'col1', 'col2', and 'col3'.

A KEY_PART 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.

The following sections describe different aspects of the *note 'CREATE INDEX': create-index. statement:

Column Prefix Key Parts

For string 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:

As of MySQL 5.7.17, if a specified index prefix exceeds the maximum column data type size, *note 'CREATE INDEX': create-index. handles the index as follows:

The statement shown here creates an index using the first 10 characters of the 'name' column (assuming that 'name' has a nonbinary string type):

 CREATE INDEX part_of_name ON customer (name(10));

If names in the column usually differ in the first 10 characters, lookups performed using this index should not be much slower than using 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 *note 'INSERT': insert. operations.

Unique Indexes

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. If you specify a prefix value for a column in a 'UNIQUE' index, the column values must be unique within the prefix length. A 'UNIQUE' index permits multiple 'NULL' values for columns that can contain 'NULL'.

If a table has a 'PRIMARY KEY' or 'UNIQUE NOT NULL' index that consists of a single column that has an integer type, you can use '_rowid' to refer to the indexed column in *note 'SELECT': select. statements, as follows:

Full-Text Indexes

'FULLTEXT' indexes are supported only for note 'InnoDB': innodb-storage-engine. and note 'MyISAM': myisam-storage-engine. tables and can include only note 'CHAR': char, note 'VARCHAR': char, and note 'TEXT': blob. columns. Indexing always happens over the entire column; column prefix indexing is not supported and any prefix length is ignored if specified. See note fulltext-search::, for details of operation.

Spatial Indexes

The note 'MyISAM': myisam-storage-engine, note 'InnoDB': innodb-storage-engine, note 'NDB': mysql-cluster, and note 'ARCHIVE': archive-storage-engine. storage engines support spatial columns such as note 'POINT': spatial-type-overview. and note 'GEOMETRY': spatial-type-overview. (*note spatial-types::, describes the spatial data types.) However, support for spatial column indexing varies among engines. Spatial and nonspatial indexes on spatial columns are available according to the following rules.

Spatial indexes on spatial columns (created using 'SPATIAL INDEX') have these characteristics:

Nonspatial indexes on spatial columns (created with 'INDEX', 'UNIQUE', or 'PRIMARY KEY') have these characteristics:

Index Options

Following the key part list, index options can be given. An INDEX_OPTION value can be any of the following:

Table Copying and Locking Options

'ALGORITHM' and 'LOCK' clauses may be given to influence the table copying method and level of concurrency for reading and writing the table while its indexes are being modified. They have the same meaning as for the note 'ALTER TABLE': alter-table. statement. For more information, see note alter-table::

NDB Cluster formerly supported online 'CREATE INDEX' operations using an alternative syntax that is no longer supported. NDB Cluster now supports online operations using the same 'ALGORITHM=INPLACE' syntax used with the standard MySQL Server. See *note mysql-cluster-online-operations::, for more information.

 File: manual.info.tmp, Node: create-logfile-group, Next: create-procedure, Prev: create-index, Up: sql-data-definition-statements

13.1.15 CREATE LOGFILE GROUP Statement

 CREATE 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 [=] 'STRING']
     ENGINE [=] ENGINE_NAME

This statement creates a new log file group named LOGFILE_GROUP having a single 'UNDO' file named 'UNDO_FILE'. A note 'CREATE LOGFILE GROUP': create-logfile-group. statement has one and only one 'ADD UNDOFILE' clause. For rules covering the naming of log file groups, see note identifiers::.

Note:

All NDB 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.

There can be only one log file group per NDB Cluster instance at any given time.

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).

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.

The maximum permitted for 'UNDO_BUFFER_SIZE' is 629145600 (600 MB).

On 32-bit systems, the maximum supported value for 'INITIAL_SIZE' is 4294967296 (4 GB). (Bug #29186)

The minimum allowed value for 'INITIAL_SIZE' is 1048576 (1 MB).

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.7, this must be note 'NDB': mysql-cluster. (or note 'NDBCLUSTER': mysql-cluster.). 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 note 'NDB': mysql-cluster. or note 'NDBCLUSTER': mysql-cluster, 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 GROUP

 mysql> 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 NDB Cluster.

REDO_BUFFER_SIZE, 'NODEGROUP', 'WAIT', and 'COMMENT' are parsed but ignored, and so have no effect in MySQL 5.7. 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 *note 'FILES': information-schema-files-table. 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)

note 'CREATE LOGFILE GROUP': create-logfile-group. is useful only with Disk Data storage for NDB Cluster. See note mysql-cluster-disk-data::.

 File: manual.info.tmp, Node: create-procedure, Next: create-server, Prev: create-logfile-group, Up: sql-data-definition-statements

13.1.16 CREATE PROCEDURE and CREATE FUNCTION Statements

 CREATE
     [DEFINER = USER]
     PROCEDURE SP_NAME ([PROC_PARAMETER[,...]])
     [CHARACTERISTIC ...] ROUTINE_BODY

 CREATE
     [DEFINER = USER]
     FUNCTION SP_NAME ([FUNC_PARAMETER[,...]])
     RETURNS TYPE
     [CHARACTERISTIC ...] ROUTINE_BODY

 PROC_PARAMETER:
     [ IN | OUT | INOUT ] PARAM_NAME TYPE

 FUNC_PARAMETER:
     PARAM_NAME TYPE

 TYPE:
     ANY VALID MYSQL DATA TYPE

 CHARACTERISTIC: {
     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 are used to create a stored routine (a stored procedure or function). That is, the specified routine becomes known to the server. By default, a stored 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 loadable functions. See note create-function-loadable::. A loadable function can be regarded as an external stored function. Stored functions share their namespace with loadable functions. See note function-resolution::, for the rules describing how the server interprets references to different kinds of functions.

To invoke a stored procedure, use the note 'CALL': call. statement (see note call::). To invoke a stored function, refer to it in an expression. The function returns a value during expression evaluation.

note 'CREATE PROCEDURE': create-procedure. and note 'CREATE FUNCTION': create-function. require the 'CREATE ROUTINE' privilege. If the 'DEFINER' clause is present, the privileges required depend on the USER value, as discussed in note stored-objects-security::. If binary logging is enabled, note 'CREATE FUNCTION': create-function. might require the 'SUPER' privilege, as discussed in *note stored-programs-logging::.

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 *note stored-routines-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 *note 'CALL': 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 'OUT' or 'INOUT' parameter. If you are calling the procedure from within a trigger, you can also pass 'NEW.COL_NAME' as an 'OUT' or 'INOUT' parameter.

For information about the effect of unhandled conditions on procedure parameters, see *note conditions-and-parameters::.

Routine parameters cannot be referenced in statements prepared within the routine; see *note stored-program-restrictions::.

The following example shows a simple stored procedure that, given a country code, counts the number of cities for that country that appear in the 'city' table of the 'world' database. The country code is passed using an 'IN' parameter, and the city count is returned using an 'OUT' parameter:

 mysql> delimiter //

 mysql> CREATE PROCEDURE citycount (IN country CHAR(3), OUT cities INT)
        BEGIN
          SELECT COUNT(*) INTO cities FROM world.city
          WHERE CountryCode = country;
        END//
 Query OK, 0 rows affected (0.01 sec)

 mysql> delimiter ;

 mysql> CALL citycount('JPN', @cities); -- cities in Japan
 Query OK, 1 row affected (0.00 sec)

 mysql> SELECT @cities;
 +---------+
 | @cities |
 +---------+
 |     248 |
 +---------+
 1 row in set (0.00 sec)

 mysql> CALL citycount('FRA', @cities); -- cities in France
 Query OK, 1 row affected (0.00 sec)

 mysql> SELECT @cities;
 +---------+
 | @cities |
 +---------+
 |      40 |
 +---------+
 1 row in set (0.00 sec)

The example uses the note 'mysql': 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 note 'mysql': mysql. itself. See *note stored-programs-defining::.

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 note 'RETURN': return. statement returns a value of a different type, the value is coerced to the proper type. For example, if a function specifies an note 'ENUM': enum. or note 'SET': set. value in the 'RETURNS' clause, but the note 'RETURN': return. statement returns an integer, the value returned from the function is the string for the corresponding note 'ENUM': enum. member of set of note 'SET': 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))
     ->   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. The 'COLLATE' attribute can be used if preceded by a 'CHARACTER SET' specification.

The ROUTINE_BODY consists of a valid SQL routine statement. This can be a simple statement such as note 'SELECT': select. or note 'INSERT': 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 note sql-compound-statements::. In practice, stored functions tend to use compound statements, unless the body consists of a single note 'RETURN': return. statement.

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 *note 'COMMIT': 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 note 'SELECT': select. statements that do not have an 'INTO VAR_LIST' clause and other statements such as note 'SHOW': show, note 'EXPLAIN': explain, and note 'CHECK TABLE': 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' (https://dev.mysql.com/doc/mysql-errors/5.7/en/server-error-reference.html#error_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' (https://dev.mysql.com/doc/mysql-errors/5.7/en/server-error-reference.html#error_er_sp_badselect)).

*note 'USE': 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 *note stored-program-restrictions::.

For information about invoking stored procedures from within programs written in a language that has a MySQL interface, see *note call::.

MySQL stores the 'sql_mode' system variable setting in effect when a routine is created or altered, and always executes the routine with this setting in force, regardless of the current server SQL mode when the routine begins executing.

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 note 'SHOW CREATE PROCEDURE': show-create-procedure. and note 'SHOW CREATE FUNCTION': 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 *note stored-programs-logging::.

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 source and replica.)

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 is permitted to execute.

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 the 'DEFINER' clause is present, the USER value should be a MySQL account specified as ''USER_NAME'@'HOST_NAME'', 'CURRENT_USER', or 'CURRENT_USER()'. The permitted USER values depend on the privileges you hold, as discussed in *note stored-objects-security::. Also see that section for additional information about stored routine security.

If the 'DEFINER' clause is omitted, the default definer is the user who executes the note 'CREATE PROCEDURE': create-procedure. or note 'CREATE FUNCTION': create-function. statement. This is the same as specifying 'DEFINER = CURRENT_USER' explicitly.

Within the body of a stored routine that is defined with the 'SQL SECURITY DEFINER' characteristic, the 'CURRENT_USER' function returns the routine's 'DEFINER' value. For information about user auditing within stored routines, see *note account-activity-auditing::.

Consider the following procedure, which displays a count of the number of MySQL accounts listed in the 'mysql.user' system 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 INVOKER
 BEGIN
   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 *note 'DECLARE': declare, or function return value as follows:

 File: manual.info.tmp, Node: create-server, Next: create-table, Prev: create-procedure, Up: sql-data-definition-statements

13.1.17 CREATE SERVER Statement

 CREATE 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' is an identifier and may be quoted with single quotation marks.

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 *note 'CREATE TABLE': create-table. statement when creating a 'FEDERATED' table. The options that you specify are 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 s
 FOREIGN DATA WRAPPER mysql
 OPTIONS (USER 'Remote', HOST '198.51.100.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 *note federated-storage-engine::.

'CREATE SERVER' causes an implicit commit. See *note implicit-commit::.

'CREATE SERVER' is not written to the binary log, regardless of the logging format that is in use.

 File: manual.info.tmp, Node: create-table, Next: create-tablespace, Prev: create-server, Up: sql-data-definition-statements

13.1.18 CREATE TABLE Statement

*note 'CREATE TABLE': create-table. creates a table with the given name. You must have the 'CREATE' privilege for the table.

By default, tables are created in the default database, using the *note 'InnoDB': innodb-storage-engine. storage engine. An error occurs if the table exists, if there is no default database, or if the database does not exist.

MySQL has no limit on the number of tables. The underlying file system may have a limit on the number of files that represent tables. Individual storage engines may impose engine-specific constraints. 'InnoDB' permits up to 4 billion tables.

For information about the physical representation of a table, see *note create-table-files::.

There are several aspects to the *note 'CREATE TABLE': create-table. statement, described under the following topics in this section:

Table Name

Temporary Tables

You can use the 'TEMPORARY' keyword when creating a table. A 'TEMPORARY' table is visible only within the current session, and is dropped automatically when the session is closed. For more information, see *note create-temporary-table::.

Table Cloning and Copying

Column Data Types and Attributes

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 *note column-count-limit::.

Indexes and Foreign Keys

Several keywords apply to creation of indexes and foreign keys. For general background in addition to the following descriptions, see note create-index::, and note create-table-foreign-keys::.

Table Options

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 *note 'ALTER TABLE': alter-table. to convert the table to use a different storage engine.

Table Partitioning

PARTITION_OPTIONS can be used to control partitioning of the table created with *note 'CREATE TABLE': create-table.

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 *note 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.

Partitions can be modified, merged, added to tables, and dropped from tables. For basic information about the MySQL statements to accomplish these tasks, see note alter-table::. For more detailed descriptions and examples, see note partitioning-management::.

Partitioning by Generated Columns

Partitioning by generated columns is permitted. For example:

 CREATE TABLE t1 (
   s1 INT,
   s2 INT AS (EXP(s1)) STORED
 )
 PARTITION BY LIST (s2) (
   PARTITION p1 VALUES IN (1)
 );

Partitioning sees a generated column as a regular column, which enables workarounds for limitations on functions that are not permitted for partitioning (see *note partitioning-limitations-functions::). The preceding example demonstrates this technique: 'EXP()' cannot be used directly in the 'PARTITION BY' clause, but a generated column defined using 'EXP()' is permitted.

 File: manual.info.tmp, Node: create-table-files, Next: create-temporary-table, Prev: create-table, Up: create-table

13.1.18.1 Files Created by CREATE TABLE .......................................

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 an 'InnoDB' table created in a file-per-table tablespace or general tablespace, table data and associated indexes are stored in a .ibd file in the database directory. When an 'InnoDB' table is created in the system tablespace, table data and indexes are stored in the ibdata* files that represent the system tablespace. The 'innodb_file_per_table' option controls whether tables are created in file-per-table tablespaces or the system tablespace, by default. The 'TABLESPACE' option can be used to place a table in a file-per-table tablespace, general tablespace, or the system tablespace, regardless of the 'innodb_file_per_table' setting.

For 'MyISAM' tables, the storage engine creates data and index files. Thus, for each 'MyISAM' table TBL_NAME, there are three disk files.

File Purpose

'TBL_NAME.frm' Table format (definition) file

'TBL_NAME.MYD' Data file

'TBL_NAME.MYI' Index file

note 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 note identifier-mapping::.

Limits Imposed by .frm File Structure

As described previously, each table has an '.frm' file that contains the table definition. The server uses the following expression to check some of the table information stored in the file against an upper limit of 64KB:

 if (info_length+(ulong) create_fields.elements*FCOMP+288+
     n_length+int_length+com_length > 65535L || int_count > 255)

The portion of the information stored in the '.frm' file that is checked against the expression cannot grow beyond the 64KB limit, so if the table definition reaches this size, no more columns can be added.

The relevant factors in the expression are:

The expression just described has several implications for permitted table definitions:

 File: manual.info.tmp, Node: create-temporary-table, Next: create-table-like, Prev: create-table-files, Up: create-table

13.1.18.2 CREATE TEMPORARY TABLE Statement ..........................................

You can use the 'TEMPORARY' keyword when creating a table. A 'TEMPORARY' table is visible only within the current session, and is dropped automatically when the session is closed. This means that two different sessions 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.)

note 'CREATE TABLE': create-table. causes an implicit commit, except when used with the 'TEMPORARY' keyword. See note implicit-commit::.

'TEMPORARY' tables have a very loose relationship with databases (schemas). Dropping a database does not automatically drop any 'TEMPORARY' tables created within that database. Also, you can create a 'TEMPORARY' table in a nonexistent database if you qualify the table name with the database name in the 'CREATE TABLE' statement. In this case, all subsequent references to the table must be qualified with the database name.

To create a temporary table, you must have the 'CREATE TEMPORARY TABLES' privilege. After a session has created a temporary table, the server performs no further privilege checks on the table. The creating session can perform any operation on the table, such as note 'DROP TABLE': drop-table, note 'INSERT': insert, note 'UPDATE': update, or note 'SELECT': select.

One implication of this behavior is that a session can manipulate its temporary tables even if the current user has no privilege to create them. Suppose that the current user does not have the 'CREATE TEMPORARY TABLES' privilege but is able to execute a definer-context stored procedure that executes with the privileges of a user who does have 'CREATE TEMPORARY TABLES' and that creates a temporary table. While the procedure executes, the session uses the privileges of the defining user. After the procedure returns, the effective privileges revert to those of the current user, which can still see the temporary table and perform any operation on it.

Note:

Support for 'TABLESPACE = innodb_file_per_table' and 'TABLESPACE = innodb_temporary' clauses with *note 'CREATE TEMPORARY TABLE': create-table. is deprecated as of MySQL 5.7.24; expect it to be removed in a future version of MySQL.

 File: manual.info.tmp, Node: create-table-like, Next: create-table-select, Prev: create-temporary-table, Up: create-table

13.1.18.3 CREATE TABLE ... LIKE Statement .........................................

Use 'CREATE TABLE ... 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:

You cannot execute 'CREATE TABLE' or 'CREATE TABLE ... LIKE' while a *note 'LOCK TABLES': lock-tables. statement is in effect.

note 'CREATE TABLE ... LIKE': create-table. makes the same checks as note 'CREATE TABLE': 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 fails.

For 'CREATE TABLE ... LIKE', the destination table preserves generated column information from the original table.

'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'.

 File: manual.info.tmp, Node: create-table-select, Next: create-table-foreign-keys, Prev: create-table-like, Up: create-table

13.1.18.4 CREATE TABLE ... SELECT Statement ...........................................

You can create one table from another by adding a note 'SELECT': select. statement at the end of the note 'CREATE TABLE': create-table. statement:

 CREATE TABLE NEW_TBL [AS] SELECT * FROM ORIG_TBL;

MySQL creates new columns for all elements in the *note 'SELECT': select. For example:

 mysql> CREATE TABLE test (a INT NOT NULL AUTO_INCREMENT,
     ->        PRIMARY KEY (a), KEY(b))
     ->        ENGINE=InnoDB SELECT b,c FROM test2;

This creates an note 'InnoDB': innodb-storage-engine. table with three columns, 'a', 'b', and 'c'. The 'ENGINE' option is part of the note 'CREATE TABLE': create-table. statement, and should not be used following the note 'SELECT': select.; this would result in a syntax error. The same is true for other note 'CREATE TABLE': create-table. options such as 'CHARSET'.

Notice that the columns from the *note 'SELECT': 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: 0

 mysql> 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 note 'CREATE TABLE ... SELECT': create-table, columns named only in the note 'CREATE TABLE': create-table. part come first. Columns named in both parts or only in the note 'SELECT': select. part come after that. The data type of note 'SELECT': select. columns can be overridden by also specifying the column in the *note 'CREATE TABLE': 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 note 'SELECT': select. by 'IGNORE' or 'REPLACE' to indicate how to handle rows that duplicate unique key values. With 'IGNORE', 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. For more information, see note ignore-effect-on-execution::.

Because the ordering of the rows in the underlying note 'SELECT': select. statements cannot always be determined, 'CREATE TABLE ... IGNORE SELECT' and 'CREATE TABLE ... REPLACE SELECT' statements are flagged as unsafe for statement-based replication. Such statements produce a warning in the error log when using statement-based mode and are written to the binary log using the row-based format when using 'MIXED' mode. See also note replication-sbr-rbr::.

note 'CREATE TABLE ... SELECT': create-table. 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 note 'SELECT': select. statement:

 mysql> CREATE TABLE bar (UNIQUE (n)) SELECT n FROM foo;

For 'CREATE TABLE ... SELECT', the destination table does not preserve information about whether columns in the selected-from table are generated columns. The *note 'SELECT': select. part of the statement cannot assign values to generated columns in the destination table.

Some conversion of data types might occur. For example, the 'AUTO_INCREMENT' attribute is not preserved, and note 'VARCHAR': char. columns can become note 'CHAR': 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 *note 'CREATE TABLE ... SELECT': 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 column in the created table:

 CREATE TABLE foo (a TINYINT NOT NULL) SELECT b+1 AS a FROM bar;

For *note 'CREATE TABLE ... SELECT': create-table, if 'IF NOT EXISTS' is given and the target table exists, nothing is inserted into the destination table, and the statement is not logged.

To ensure that the binary log can be used to re-create the original tables, MySQL does not permit concurrent inserts during *note 'CREATE TABLE ... SELECT': create-table.

You cannot use 'FOR UPDATE' as part of the note 'SELECT': select. in a statement such as note 'CREATE TABLE NEW_TABLE SELECT ... FROM OLD_TABLE ...': create-table-select. If you attempt to do so, the statement fails.

 File: manual.info.tmp, Node: create-table-foreign-keys, Next: silent-column-changes, Prev: create-table-select, Up: create-table

13.1.18.5 FOREIGN KEY Constraints .................................

MySQL supports foreign keys, which permit cross-referencing related data across tables, and foreign key constraints, which help keep the related data consistent.

A foreign key relationship involves a parent table that holds the initial column values, and a child table with column values that reference the parent column values. A foreign key constraint is defined on the child table.

The essential syntax for a defining a foreign key constraint in a note 'CREATE TABLE': create-table. or note 'ALTER TABLE': alter-table. statement includes the following:

 [CONSTRAINT [SYMBOL]] FOREIGN KEY
     [INDEX_NAME] (COL_NAME, ...)
     REFERENCES TBL_NAME (COL_NAME,...)
     [ON DELETE REFERENCE_OPTION]
     [ON UPDATE REFERENCE_OPTION]

 REFERENCE_OPTION:
     RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT

Foreign key constraint usage is described under the following topics in this section:

Identifiers

Foreign key constraint naming is governed by the following rules:

Table and column identifiers in a 'FOREIGN KEY ... REFERENCES' clause can be quoted within backticks ('`'). Alternatively, double quotation marks ('"') can be used if the 'ANSI_QUOTES' SQL mode is enabled. The 'lower_case_table_names' system variable setting is also taken into account.

Conditions and Restrictions

Foreign key constraints are subject to the following conditions and restrictions:

For information about how the MySQL implementation of foreign key constraints differs from the SQL standard, see *note ansi-diff-foreign-keys::.

Referential Actions

When an note 'UPDATE': update. or note 'DELETE': delete. operation affects a key value in the parent table that has matching rows in the child table, the result depends on the referential action specified by 'ON UPDATE' and 'ON DELETE' subclauses of the 'FOREIGN KEY' clause. Referential actions include:

For storage engines that support foreign keys, MySQL rejects any note 'INSERT': insert. or note 'UPDATE': update. operation that attempts to create a foreign key value in a child table if there is no matching candidate key value in the parent table.

For an 'ON DELETE' or 'ON UPDATE' that is not specified, the default action is always 'RESTRICT'.

For *note 'NDB': mysql-cluster. tables, 'ON UPDATE CASCADE' is not supported where the reference is to the parent table's primary key.

As of NDB 7.5.14 and NDB 7.6.10: For note 'NDB': mysql-cluster. tables, 'ON DELETE CASCADE' is not supported where the child table contains one or more columns of any of the note 'TEXT': blob. or *note 'BLOB': blob. types. (Bug #89511, Bug #27484882)

'InnoDB' performs cascading operations using a depth-first search algorithm on the records of the index that corresponds to the foreign key constraint.

A foreign key constraint on a stored generated column cannot use 'CASCADE', 'SET NULL', or 'SET DEFAULT' as 'ON UPDATE' referential actions, nor can it use 'SET NULL' or 'SET DEFAULT' as 'ON DELETE' referential actions.

A foreign key constraint on the base column of a stored generated column cannot use 'CASCADE', 'SET NULL', or 'SET DEFAULT' as 'ON UPDATE' or 'ON DELETE' referential actions.

In MySQL 5.7.13 and earlier, 'InnoDB' does not permit defining a foreign key constraint with a cascading referential action on the base column of an indexed virtual generated column. This restriction is lifted in MySQL 5.7.14.

In MySQL 5.7.13 and earlier, 'InnoDB' does not permit defining cascading referential actions on non-virtual foreign key columns that are explicitly included in a virtual index. This restriction is lifted in MySQL 5.7.14.

Foreign Key Constraint Examples

This simple example relates 'parent' and 'child' tables through a single-column foreign key:

 CREATE TABLE parent (
     id INT NOT NULL,
     PRIMARY KEY (id)
 ) ENGINE=INNODB;

 CREATE TABLE child (
     id INT,
     parent_id INT,
     INDEX par_ind (parent_id),
     FOREIGN KEY (parent_id)
         REFERENCES parent(id)
         ON DELETE CASCADE
 ) ENGINE=INNODB;

This is a more complex example in which a 'product_order' table has foreign keys for two other tables. One foreign key references a two-column index in the 'product' table. The other references a single-column index in the 'customer' table:

 CREATE TABLE product (
     category INT NOT NULL, id INT NOT NULL,
     price DECIMAL,
     PRIMARY KEY(category, id)
 )   ENGINE=INNODB;

 CREATE TABLE customer (
     id INT NOT NULL,
     PRIMARY KEY (id)
 )   ENGINE=INNODB;

 CREATE TABLE product_order (
     no INT NOT NULL AUTO_INCREMENT,
     product_category INT NOT NULL,
     product_id INT NOT NULL,
     customer_id INT NOT NULL,

     PRIMARY KEY(no),
     INDEX (product_category, product_id),
     INDEX (customer_id),

     FOREIGN KEY (product_category, product_id)
       REFERENCES product(category, id)
       ON UPDATE CASCADE ON DELETE RESTRICT,

     FOREIGN KEY (customer_id)
       REFERENCES customer(id)
 )   ENGINE=INNODB;

Adding Foreign Key Constraints

You can add a foreign key constraint to an existing table using the following *note 'ALTER TABLE': alter-table. syntax:

 ALTER TABLE TBL_NAME
     ADD [CONSTRAINT [SYMBOL]] FOREIGN KEY
     [INDEX_NAME] (COL_NAME, ...)
     REFERENCES TBL_NAME (COL_NAME,...)
     [ON DELETE REFERENCE_OPTION]
     [ON UPDATE REFERENCE_OPTION]

The foreign key can be self referential (referring to the same table). When you add a foreign key constraint to a table using *note 'ALTER TABLE': alter-table, remember to first create an index on the column(s) referenced by the foreign key.

Dropping Foreign Key Constraints

You can drop a foreign key constraint using the following *note 'ALTER TABLE': alter-table. syntax:

 ALTER TABLE TBL_NAME DROP FOREIGN KEY FK_SYMBOL;

If the 'FOREIGN KEY' clause defined a 'CONSTRAINT' name when you created the constraint, you can refer to that name to drop the foreign key constraint. Otherwise, a constraint name was generated internally, and you must use that value. To determine the foreign key constraint name, use *note 'SHOW CREATE TABLE': show-create-table.:

 mysql> SHOW CREATE TABLE child\G
 *************************** 1. row ***************************
        Table: child
 Create Table: CREATE TABLE `child` (
   `id` int(11) DEFAULT NULL,
   `parent_id` int(11) DEFAULT NULL,
   KEY `par_ind` (`parent_id`),
   CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`)
   REFERENCES `parent` (`id`) ON DELETE CASCADE
 ) ENGINE=InnoDB DEFAULT CHARSET=latin1

 mysql> ALTER TABLE child DROP FOREIGN KEY `child_ibfk_1`;

Adding and dropping a foreign key in the same note 'ALTER TABLE': alter-table. statement is supported for note 'ALTER TABLE ... ALGORITHM=INPLACE': alter-table. It is not supported for *note 'ALTER TABLE ... ALGORITHM=COPY': alter-table.

Foreign Key Checks

In MySQL, InnoDB and NDB tables support checking of foreign key constraints. Foreign key checking is controlled by the 'foreign_key_checks' variable, which is enabled by default. Typically, you leave this variable enabled during normal operation to enforce referential integrity. The 'foreign_key_checks' variable has the same effect on note 'NDB': mysql-cluster. tables as it does for note 'InnoDB': innodb-storage-engine. tables.

The 'foreign_key_checks' variable is dynamic and supports both global and session scopes. For information about using system variables, see *note using-system-variables::.

Disabling foreign key checking is useful when:

When 'foreign_key_checks' is disabled, foreign key constraints are ignored, with the following exceptions:

Disabling 'foreign_key_checks' has these additional implications:

Foreign Key Definitions and Metadata

To view a foreign key definition, use *note 'SHOW CREATE TABLE': show-create-table.:

 mysql> SHOW CREATE TABLE child\G
 *************************** 1. row ***************************
        Table: child
 Create Table: CREATE TABLE `child` (
   `id` int(11) DEFAULT NULL,
   `parent_id` int(11) DEFAULT NULL,
   KEY `par_ind` (`parent_id`),
   CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`)
   REFERENCES `parent` (`id`) ON DELETE CASCADE
 ) ENGINE=InnoDB DEFAULT CHARSET=latin1

You can obtain information about foreign keys from the Information Schema *note 'KEY_COLUMN_USAGE': information-schema-key-column-usage-table. table. An example of a query against this table is shown here:

 mysql> SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, CONSTRAINT_NAME
        FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
        WHERE REFERENCED_TABLE_SCHEMA IS NOT NULL;
 +--------------+------------+-------------+-----------------+
 | TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME | CONSTRAINT_NAME |
 +--------------+------------+-------------+-----------------+
 | test         | child      | parent_id   | child_ibfk_1    |
 +--------------+------------+-------------+-----------------+

You can obtain information specific to 'InnoDB' foreign keys from the note 'INNODB_SYS_FOREIGN': information-schema-innodb-sys-foreign-table. and note 'INNODB_SYS_FOREIGN_COLS': information-schema-innodb-sys-foreign-cols-table. tables. Example queries are show here:

 mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN \G
 *************************** 1. row ***************************
       ID: test/child_ibfk_1
 FOR_NAME: test/child
 REF_NAME: test/parent
   N_COLS: 1
     TYPE: 1

 mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN_COLS \G
 *************************** 1. row ***************************
           ID: test/child_ibfk_1
 FOR_COL_NAME: parent_id
 REF_COL_NAME: id
          POS: 0

Foreign Key Errors

In the event of a foreign key error involving 'InnoDB' tables (usually Error 150 in the MySQL Server), information about the latest foreign key error can be obtained by checking *note 'SHOW ENGINE INNODB STATUS': show-engine. output.

 mysql> SHOW ENGINE INNODB STATUS\G
 ...
 ------------------------
 LATEST FOREIGN KEY ERROR
 ------------------------
 2014-10-16 18:35:18 0x7fc2a95c1700 Transaction:
 TRANSACTION 1814, ACTIVE 0 sec inserting
 mysql tables in use 1, locked 1
 4 lock struct(s), heap size 1136, 3 row lock(s), undo log entries 3
 MySQL thread id 2, OS thread handle 140474041767680, query id 74 localhost
 root update
 INSERT INTO child VALUES
     (NULL, 1)
     , (NULL, 2)
     , (NULL, 3)
     , (NULL, 4)
     , (NULL, 5)
     , (NULL, 6)
 Foreign key constraint fails for table `mysql`.`child`:
 ,
   CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `parent`
   (`id`) ON DELETE CASCADE ON UPDATE CASCADE
 Trying to add in child table, in index par_ind tuple:
 DATA TUPLE: 2 fields;
  0: len 4; hex 80000003; asc     ;;
  1: len 4; hex 80000003; asc     ;;

 But in parent table `mysql`.`parent`, in index PRIMARY,
 the closest match we can find is record:
 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
  0: len 4; hex 80000004; asc     ;;
  1: len 6; hex 00000000070a; asc       ;;
  2: len 7; hex aa0000011d0134; asc       4;;
 ...

Warning:

'ER_NO_REFERENCED_ROW_2' (https://dev.mysql.com/doc/mysql-errors/5.7/en/server-error-reference.html#error_er_no_referenced_row_2) and 'ER_ROW_IS_REFERENCED_2' (https://dev.mysql.com/doc/mysql-errors/5.7/en/server-error-reference.html#error_er_row_is_referenced_2) error messages for foreign key operations expose information about parent tables, even if the user has no parent table access privileges. To hide information about parent tables, include the appropriate condition handlers in application code and stored programs.

 File: manual.info.tmp, Node: silent-column-changes, Next: create-table-generated-columns, Prev: create-table-foreign-keys, Up: create-table

13.1.18.6 Silent Column Specification Changes .............................................

In some cases, MySQL silently changes column specifications from those given in a note 'CREATE TABLE': create-table. or note 'ALTER TABLE': 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 *note column-count-limit::.

To see whether MySQL used a data type other than the one you specified, issue a note 'DESCRIBE': describe. or note 'SHOW CREATE TABLE': show-create-table. statement after creating or altering the table.

Certain other data type changes can occur if you compress a table using note 'myisampack': myisampack. See note compressed-format::.

 File: manual.info.tmp, Node: create-table-generated-columns, Next: create-table-secondary-indexes, Prev: silent-column-changes, Up: create-table

13.1.18.7 CREATE TABLE and Generated Columns ............................................

*note 'CREATE TABLE': create-table. supports the specification of generated columns. Values of a generated column are computed from an expression included in the column definition.

Generated columns are supported by the *note 'NDB': mysql-cluster. storage engine beginning with MySQL NDB Cluster 7.5.3.

The following simple example shows a table that stores the lengths of the sides of right triangles in the 'sidea' and 'sideb' columns, and computes the length of the hypotenuse in 'sidec' (the square root of the sums of the squares of the other sides):

 CREATE TABLE triangle (
   sidea DOUBLE,
   sideb DOUBLE,
   sidec DOUBLE AS (SQRT(sidea * sidea + sideb * sideb))
 );
 INSERT INTO triangle (sidea, sideb) VALUES(1,1),(3,4),(6,8);

Selecting from the table yields this result:

 mysql> SELECT * FROM triangle;
 +-------+-------+--------------------+
 | sidea | sideb | sidec              |
 +-------+-------+--------------------+
 |     1 |     1 | 1.4142135623730951 |
 |     3 |     4 |                  5 |
 |     6 |     8 |                 10 |
 +-------+-------+--------------------+

Any application that uses the 'triangle' table has access to the hypotenuse values without having to specify the expression that calculates them.

Generated column definitions have this syntax:

 COL_NAME DATA_TYPE [GENERATED ALWAYS] AS (EXPR)
   [VIRTUAL | STORED] [NOT NULL | NULL]
   [UNIQUE [KEY]] [[PRIMARY] KEY]
   [COMMENT 'STRING']

'AS (EXPR)' indicates that the column is generated and defines the expression used to compute column values. 'AS' may be preceded by 'GENERATED ALWAYS' to make the generated nature of the column more explicit. Constructs that are permitted or prohibited in the expression are discussed later.

The 'VIRTUAL' or 'STORED' keyword indicates how column values are stored, which has implications for column use:

The default is 'VIRTUAL' if neither keyword is specified.

It is permitted to mix 'VIRTUAL' and 'STORED' columns within a table.

Other attributes may be given to indicate whether the column is indexed or can be 'NULL', or provide a comment.

Generated column expressions must adhere to the following rules. An error occurs if an expression contains disallowed constructs.

If the expression evaluates to a data type that differs from the declared column type, implicit coercion to the declared type occurs according to the usual MySQL type-conversion rules. See *note type-conversion::.

Note:

If any component of the expression depends on the SQL mode, different results may occur for different uses of the table unless the SQL mode is the same during all uses.

For *note 'CREATE TABLE ... LIKE': create-table-like, the destination table preserves generated column information from the original table.

For note 'CREATE TABLE ... SELECT': create-table-select, the destination table does not preserve information about whether columns in the selected-from table are generated columns. The note 'SELECT': select. part of the statement cannot assign values to generated columns in the destination table.

Partitioning by generated columns is permitted. See *note create-table-partitioning::.

A foreign key constraint on a stored generated column cannot use 'CASCADE', 'SET NULL', or 'SET DEFAULT' as 'ON UPDATE' referential actions, nor can it use 'SET NULL' or 'SET DEFAULT' as 'ON DELETE' referential actions.

A foreign key constraint on the base column of a stored generated column cannot use 'CASCADE', 'SET NULL', or 'SET DEFAULT' as 'ON UPDATE' or 'ON DELETE' referential actions.

A foreign key constraint cannot reference a virtual generated column.

Triggers cannot use 'NEW.COL_NAME' or use 'OLD.COL_NAME' to refer to generated columns.

For note 'INSERT': insert, note 'REPLACE': replace, and *note 'UPDATE': update, if a generated column is inserted into, replaced, or updated explicitly, the only permitted value is 'DEFAULT'.

A generated column in a view is considered updatable because it is possible to assign to it. However, if such a column is updated explicitly, the only permitted value is 'DEFAULT'.

Generated columns have several use cases, such as these:

Example:

Suppose that a table 't1' contains 'first_name' and 'last_name' columns and that applications frequently construct the full name using an expression like this:

 SELECT CONCAT(first_name,' ',last_name) AS full_name FROM t1;

One way to avoid writing out the expression is to create a view 'v1' on 't1', which simplifies applications by enabling them to select 'full_name' directly without using an expression:

 CREATE VIEW v1 AS
 SELECT *, CONCAT(first_name,' ',last_name) AS full_name FROM t1;

 SELECT full_name FROM v1;

A generated column also enables applications to select 'full_name' directly without the need to define a view:

 CREATE TABLE t1 (
   first_name VARCHAR(10),
   last_name VARCHAR(10),
   full_name VARCHAR(255) AS (CONCAT(first_name,' ',last_name))
 );

 SELECT full_name FROM t1;

 File: manual.info.tmp, Node: create-table-secondary-indexes, Next: create-table-ndb-comment-options, Prev: create-table-generated-columns, Up: create-table

13.1.18.8 Secondary Indexes and Generated Columns .................................................

'InnoDB' supports secondary indexes on virtual generated columns. Other index types are not supported. A secondary index defined on a virtual column is sometimes referred to as a 'virtual index'.

A secondary index may be created on one or more virtual columns or on a combination of virtual columns and regular columns or stored generated columns. Secondary indexes that include virtual columns may be defined as 'UNIQUE'.

When a secondary index is created on a virtual generated column, generated column values are materialized in the records of the index. If the index is a covering index (one that includes all the columns retrieved by a query), generated column values are retrieved from materialized values in the index structure instead of computed 'on the fly'.

There are additional write costs to consider when using a secondary index on a virtual column due to computation performed when materializing virtual column values in secondary index records during note 'INSERT': insert. and note 'UPDATE': update. operations. Even with additional write costs, secondary indexes on virtual columns may be preferable to generated stored columns, which are materialized in the clustered index, resulting in larger tables that require more disk space and memory. If a secondary index is not defined on a virtual column, there are additional costs for reads, as virtual column values must be computed each time the column's row is examined.

Values of an indexed virtual column are MVCC-logged to avoid unnecessary recomputation of generated column values during rollback or during a purge operation. The data length of logged values is limited by the index key limit of 767 bytes for 'COMPACT' and 'REDUNDANT' row formats, and 3072 bytes for 'DYNAMIC' and 'COMPRESSED' row formats.

Adding or dropping a secondary index on a virtual column is an in-place operation.

Prior to 5.7.16, a foreign key constraint cannot reference a secondary index defined on a virtual generated column.

In MySQL 5.7.13 and earlier, 'InnoDB' does not permit defining a foreign key constraint with a cascading referential action on the base column of an indexed generated virtual column. This restriction is lifted in MySQL 5.7.14.

Indexing a Generated Column to Provide a JSON Column Index

As noted elsewhere, *note 'JSON': json. columns cannot be indexed directly. To create an index that references such a column indirectly, you can define a generated column that extracts the information that should be indexed, then create an index on the generated column, as shown in this example:

 mysql> CREATE TABLE jemp (
     ->     c JSON,
     ->     g INT GENERATED ALWAYS AS (c->"$.id"),
     ->     INDEX i (g)
     -> );
 Query OK, 0 rows affected (0.28 sec)

 mysql> INSERT INTO jemp (c) VALUES
      >   ('{"id": "1", "name": "Fred"}'), ('{"id": "2", "name": "Wilma"}'),
      >   ('{"id": "3", "name": "Barney"}'), ('{"id": "4", "name": "Betty"}');
 Query OK, 4 rows affected (0.04 sec)
 Records: 4  Duplicates: 0  Warnings: 0

 mysql> SELECT c->>"$.name" AS name
      >     FROM jemp WHERE g > 2;
 +--------+
 | name   |
 +--------+
 | Barney |
 | Betty  |
 +--------+
 2 rows in set (0.00 sec)

 mysql> EXPLAIN SELECT c->>"$.name" AS name
      >    FROM jemp WHERE g > 2\G
 *************************** 1. row ***************************
            id: 1
   select_type: SIMPLE
         table: jemp
    partitions: NULL
          type: range
 possible_keys: i
           key: i
       key_len: 5
           ref: NULL
          rows: 2
      filtered: 100.00
         Extra: Using where
 1 row in set, 1 warning (0.00 sec)

 mysql> SHOW WARNINGS\G
 *************************** 1. row ***************************
   Level: Note
    Code: 1003
 Message: /* select#1 */ select json_unquote(json_extract(`test`.`jemp`.`c`,'$.name'))
 AS `name` from `test`.`jemp` where (`test`.`jemp`.`g` > 2)
 1 row in set (0.00 sec)

(We have wrapped the output from the last statement in this example to fit the viewing area.)

The '->' operator is supported in MySQL 5.7.9 and later. The '->>' operator is supported beginning with MySQL 5.7.13.

When you use note 'EXPLAIN': explain. on a note 'SELECT': select. or other SQL statement containing one or more expressions that use the '->' or '->>' operator, these expressions are translated into their equivalents using 'JSON_EXTRACT()' and (if needed) 'JSON_UNQUOTE()' instead, as shown here in the output from *note 'SHOW WARNINGS': show-warnings. immediately following this 'EXPLAIN' statement:

 mysql> EXPLAIN SELECT c->>"$.name"
      > FROM jemp WHERE g > 2 ORDER BY c->"$.name"\G
 *************************** 1. row ***************************
            id: 1
   select_type: SIMPLE
         table: jemp
    partitions: NULL
          type: range
 possible_keys: i
           key: i
       key_len: 5
           ref: NULL
          rows: 2
      filtered: 100.00
         Extra: Using where; Using filesort
 1 row in set, 1 warning (0.00 sec)

 mysql> SHOW WARNINGS\G
 *************************** 1. row ***************************
   Level: Note
    Code: 1003
 Message: /* select#1 */ select json_unquote(json_extract(`test`.`jemp`.`c`,'$.name')) AS
 `c->>"$.name"` from `test`.`jemp` where (`test`.`jemp`.`g` > 2) order by
 json_extract(`test`.`jemp`.`c`,'$.name')
 1 row in set (0.00 sec)

See the descriptions of the '->' and '->>' operators, as well as those of the 'JSON_EXTRACT()' and 'JSON_UNQUOTE()' functions, for additional information and examples.

This technique also can be used to provide indexes that indirectly reference columns of other types that cannot be indexed directly, such as 'GEOMETRY' columns.

JSON columns and indirect indexing in NDB Cluster

It is also possible to use indirect indexing of JSON columns in MySQL NDB Cluster, subject to the following conditions:

  1. note 'NDB': mysql-cluster. handles a note 'JSON': json. column value internally as a *note 'BLOB': blob. This means that any 'NDB' table having one or more JSON columns must have a primary key, else it cannot be recorded in the binary log.

  2. The *note 'NDB': mysql-cluster. storage engine does not support indexing of virtual columns. Since the default for generated columns is 'VIRTUAL', you must specify explicitly the generated column to which to apply the indirect index as 'STORED'.

The CREATE TABLE statement used to create the table 'jempn' shown here is a version of the 'jemp' table shown previously, with modifications making it compatible with 'NDB':

 CREATE TABLE jempn (
   a BIGINT(20) NOT NULL AUTO_INCREMENT PRIMARY KEY,
   c JSON DEFAULT NULL,
   g INT GENERATED ALWAYS AS (c->"$.name") STORED,
   INDEX i (g)
 ) ENGINE=NDB;

We can populate this table using the following *note 'INSERT': insert. statement:

 INSERT INTO jempn (a, c) VALUES
   (NULL, '{"id": "1", "name": "Fred"}'),
   (NULL, '{"id": "2", "name": "Wilma"}'),
   (NULL, '{"id": "3", "name": "Barney"}'),
   (NULL, '{"id": "4", "name": "Betty"}');

Now 'NDB' can use index 'i', as shown here:

 mysql> EXPLAIN SELECT c->>"$.name" AS name
           FROM jempn WHERE g > 2\G
 *************************** 1. row ***************************
            id: 1
   select_type: SIMPLE
         table: jempn
    partitions: p0,p1
          type: range
 possible_keys: i
           key: i
       key_len: 5
           ref: NULL
          rows: 3
      filtered: 100.00
         Extra: Using where with pushed condition (`test`.`jempn`.`g` > 2)
 1 row in set, 1 warning (0.00 sec)

 mysql> SHOW WARNINGS\G
 *************************** 1. row ***************************
   Level: Note
    Code: 1003
 Message: /* select#1 */ select
 json_unquote(json_extract(`test`.`jempn`.`c`,'$.name')) AS `name` from
 `test`.`jempn` where (`test`.`jempn`.`g` > 2)
 1 row in set (0.00 sec)

You should keep in mind that a stored generated column, as well as any index on such a column, uses 'DataMemory'. In NDB 7.5, an index on a stored generated column also uses 'IndexMemory'.

 File: manual.info.tmp, Node: create-table-ndb-comment-options, Prev: create-table-secondary-indexes, Up: create-table

13.1.18.9 Setting NDB Comment Options .....................................

It is possible to set a number of options specific to NDB Cluster in the table comment or column comments of an *note 'NDB': mysql-cluster. table. Table-level options for controlling read from any replica and partition balance can be embedded in a table comment using 'NDB_TABLE'.

'NDB_COLUMN' can be used in a column comment to set the size of the blob parts table column used for storing parts of blob values by 'NDB' to its maximum. This works for note 'BLOB': blob, 'MEDIUMBLOB', 'LONGBLOB', note 'TEXT': blob, 'MEDIUMTEXT', 'LONGTEXT', and *note 'JSON': json. columns.

'NDB_TABLE' can be used in a table comment to set options relating to partition balance and whether the table is fully replicated, among others.

The remainder of this section describes these options and their use.

NDB_COLUMN Options

In NDB Cluster, a column comment in a 'CREATE TABLE' or *note 'ALTER TABLE': alter-table. statement can also be used to specify an 'NDB_COLUMN' option. NDB 7.5 and 7.6 support a single column comment option 'MAX_BLOB_PART_SIZE'; syntax for this option is shown here:

 COMMENT 'NDB_COLUMN=MAX_BLOB_PART_SIZE[={0|1}]'

The '=' sign and the value following it are optional. Using any value other than 0 or 1 results in a syntax error.

The effect of using 'MAX_BLOB_PART_SIZE' in a column comment is to set the blob part size of a note 'TEXT': blob. or note 'BLOB': blob. column to the maximum number of bytes supported for this by 'NDB' (13948). This option can be applied to any blob column type supported by MySQL except 'TINYBLOB' or 'TINYTEXT' ('BLOB', 'MEDIUMBLOB', 'LONGBLOB', 'TEXT', 'MEDIUMTEXT', 'LONGTEXT'). 'MAX_BLOB_PART_SIZE' has no effect on 'JSON' columns.

You should also keep in mind, especially when working with 'TEXT' columns, that the value set by 'MAX_BLOB_PART_SIZE' represents column size in bytes. It does not indicate the number of characters, which varies according to the character set and collation used by the column.

To see the effects of this option, we first run the following SQL statement in the *note 'mysql': mysql. client to create a table with two 'BLOB' columns, one ('c1') with no extra options, and another ('c2') with 'MAX_BLOB_PART_SIZE':

 mysql> CREATE TABLE test.t (
     ->   p INT PRIMARY KEY,
     ->   c1 BLOB,
     ->   c2 BLOB COMMENT 'NDB_COLUMN=MAX_BLOB_PART_SIZE'
     -> ) ENGINE NDB;
 Query OK, 0 rows affected (0.32 sec)

From the system shell, run the *note 'ndb_desc': mysql-cluster-programs-ndb-desc. utility to obtain information about the table just created, as shown in this example:

 $> ndb_desc -d test t
 -- t --
 Version: 1
 Fragment type: HashMapPartition
 K Value: 6
 Min load factor: 78
 Max load factor: 80
 Temporary table: no
 Number of attributes: 3
 Number of primary keys: 1
 Length of frm data: 324
 Row Checksum: 1
 Row GCI: 1
 SingleUserMode: 0
 ForceVarPart: 1
 FragmentCount: 2
 ExtraRowGciBits: 0
 ExtraRowAuthorBits: 0
 TableStatus: Retrieved
 HashMap: DEFAULT-HASHMAP-3840-2
 -- Attributes --
 p Int PRIMARY KEY DISTRIBUTION KEY AT=FIXED ST=MEMORY
 _c1 Blob(256,2000,0) NULL AT=MEDIUM_VAR ST=MEMORY BV=2 BT=NDB$BLOB_22_1
 c2 Blob(256,13948,0) NULL AT=MEDIUM_VAR ST=MEMORY BV=2 BT=NDB$BLOB_22_2_
 -- Indexes --
 PRIMARY KEY(p) - UniqueHashIndex
 PRIMARY(p) - OrderedIndex

Column information in the output is listed under 'Attributes'; for columns 'c1' and 'c2' it is displayed here in emphasized text. For 'c1', the blob part size is 2000, the default value; for 'c2', it is 13948, as set by 'MAX_BLOB_PART_SIZE'.

You can change the blob part size for a given blob column of an 'NDB' table using an 'ALTER TABLE' statement such as this one, and verifying the changes afterwards using *note 'SHOW CREATE TABLE': show-create-table.:

 mysql> ALTER TABLE test.t
     ->    DROP COLUMN c1,
     ->     ADD COLUMN c1 BLOB COMMENT 'NDB_COLUMN=MAX_BLOB_PART_SIZE',
     ->     CHANGE COLUMN c2 c2 BLOB AFTER c1;
 Query OK, 0 rows affected (0.47 sec)
 Records: 0  Duplicates: 0  Warnings: 0

 mysql> SHOW CREATE TABLE test.t\G
 *************************** 1. row ***************************
        Table: t
 Create Table: CREATE TABLE `t` (
   `p` int(11) NOT NULL,
   `c1` blob COMMENT 'NDB_COLUMN=MAX_BLOB_PART_SIZE',
   `c2` blob,
   PRIMARY KEY (`p`)
 ) ENGINE=ndbcluster DEFAULT CHARSET=latin1
 1 row in set (0.00 sec)

 mysql> EXIT
 Bye

The output of *note 'ndb_desc': mysql-cluster-programs-ndb-desc. shows that the blob part sizes of the columns have been changed as expected:

 $> ndb_desc -d test t
 -- t --
 Version: 16777220
 Fragment type: HashMapPartition
 K Value: 6
 Min load factor: 78
 Max load factor: 80
 Temporary table: no
 Number of attributes: 3
 Number of primary keys: 1
 Length of frm data: 324
 Row Checksum: 1
 Row GCI: 1
 SingleUserMode: 0
 ForceVarPart: 1
 FragmentCount: 2
 ExtraRowGciBits: 0
 ExtraRowAuthorBits: 0
 TableStatus: Retrieved
 HashMap: DEFAULT-HASHMAP-3840-2
 -- Attributes --
 p Int PRIMARY KEY DISTRIBUTION KEY AT=FIXED ST=MEMORY
 _c1 Blob(256,13948,0) NULL AT=MEDIUM_VAR ST=MEMORY BV=2 BT=NDB$BLOB_26_1
 c2 Blob(256,2000,0) NULL AT=MEDIUM_VAR ST=MEMORY BV=2 BT=NDB$BLOB_26_2_
 -- Indexes --
 PRIMARY KEY(p) - UniqueHashIndex
 PRIMARY(p) - OrderedIndex

 NDBT_ProgramExit: 0 - OK

Changing a column's blob part size must be done using a copying 'ALTER TABLE'; this operation cannot be performed online (see *note mysql-cluster-online-operations::).

For more information about how note 'NDB': mysql-cluster. stores columns of blob types, see note data-types-storage-reqs-strings::.

NDB_TABLE Options

For an NDB Cluster table, the table comment in a 'CREATE TABLE' or *note 'ALTER TABLE': alter-table. statement can also be used to specify an 'NDB_TABLE' option, which consists of one or more name-value pairs, separated by commas if need be, following the string 'NDB_TABLE='. Complete syntax for names and values syntax is shown here:

 COMMENT="NDB_TABLE=NDB_TABLE_OPTION[,NDB_TABLE_OPTION[,...]]"

 NDB_TABLE_OPTION: {
     NOLOGGING={1 | 0}
   | READ_BACKUP={1 | 0}
   | PARTITION_BALANCE={FOR_RP_BY_NODE | FOR_RA_BY_NODE | FOR_RP_BY_LDM
                       | FOR_RA_BY_LDM | FOR_RA_BY_LDM_X_2
                       | FOR_RA_BY_LDM_X_3 | FOR_RA_BY_LDM_X_4}
   | FULLY_REPLICATED={1 | 0}
 }

Spaces are not permitted within the quoted string. The string is case-insensitive.

The four 'NDB' table options that can be set as part of a comment in this way are described in more detail in the next few paragraphs.

'NOLOGGING': By default, 'NDB' tables are logged, and checkpointed. This makes them durable to whole cluster failures. Using 'NOLOGGING' when creating or altering a table means that this table is not redo logged or included in local checkpoints. In this case, the table is still replicated across the data nodes for high availability, and updated using transactions, but changes made to it are not recorded in the data node's redo logs, and its content is not checkpointed to disk; when recovering from a cluster failure, the cluster retains the table definition, but none of its rows--that is, the table is empty.

Using such nonlogging tables reduces the data node's demands on disk I/O and storage, as well as CPU for checkpointing CPU. This may be suitable for short-lived data which is frequently updated, and where the loss of all data in the unlikely event of a total cluster failure is acceptable.

It is also possible to use the 'ndb_table_no_logging' system variable to cause any NDB tables created or altered while this variable is in effect to behave as though it had been created with the 'NOLOGGING' comment. Unlike when using the comment directly, there is nothing in this case in the output of *note 'SHOW CREATE TABLE': show-create-table. to indicate that it is a nonlogging table. Using the table comment approach is recommended since it offers per-table control of the feature, and this aspect of the table schema is embedded in the table creation statement where it can be found easily by SQL-based tools.

'READ_BACKUP': Setting this option to 1 has the same effect as though 'ndb_read_backup' were enabled; enables reading from any replica. Doing so greatly improves the performance of reads from the table at a relatively small cost to write performance.

Starting with MySQL NDB Cluster 7.5.3, you can set 'READ_BACKUP' for an existing table online (Bug #80858, Bug #23001617), using an 'ALTER TABLE' statement similar to one of those shown here:

 ALTER TABLE ... ALGORITHM=INPLACE, COMMENT="NDB_TABLE=READ_BACKUP=1";

 ALTER TABLE ... ALGORITHM=INPLACE, COMMENT="NDB_TABLE=READ_BACKUP=0";

Prior to MySQL NDB Cluster 7.5.4, setting 'READ_BACKUP' to 1 also caused 'FRAGMENT_COUNT_TYPE' to be set to 'ONE_PER_LDM_PER_NODE_GROUP'.

For more information about the 'ALGORITHM' option for 'ALTER TABLE', see *note mysql-cluster-online-operations::.

'PARTITION_BALANCE': Provides additional control over assignment and placement of partitions. The following four schemes are supported:

  1. 'FOR_RP_BY_NODE': One partition per node.

    Only one LDM on each node stores a primary partition. Each partition is stored in the same LDM (same ID) on all nodes.

  2. 'FOR_RA_BY_NODE': One partition per node group.

    Each node stores a single partition, which can be either a primary replica or a backup replica. Each partition is stored in the same LDM on all nodes.

  3. 'FOR_RP_BY_LDM': One partition for each LDM on each node; the default.

    This is the same behavior as prior to MySQL NDB Cluster 7.5.2, except for a slightly different mapping of partitions to LDMs, starting with LDM 0 and placing one partition per node group, then moving on to the next LDM.

    In MySQL NDB Cluster 7.5.4 and later, this is the setting used if 'READ_BACKUP' is set to 1. (Bug #82634, Bug #24482114)

  4. 'FOR_RA_BY_LDM': One partition per LDM in each node group.

    These partitions can be primary or backup partitions.

    Prior to MySQL NDB Cluster 7.5.4, this was the setting used if 'READ_BACKUP' was set to 1.

  5. 'FOR_RA_BY_LDM_X_2': Two partitions per LDM in each node group.

    These partitions can be primary or backup partitions.

    This setting was added in NDB 7.5.4.

  6. 'FOR_RA_BY_LDM_X_3': Three partitions per LDM in each node group.

    These partitions can be primary or backup partitions.

    This setting was added in NDB 7.5.4.

  7. 'FOR_RA_BY_LDM_X_4': Four partitions per LDM in each node group.

    These partitions can be primary or backup partitions.

    This setting was added in NDB 7.5.4.

Beginning with NDB 7.5.4, 'PARTITION_BALANCE' is the preferred interface for setting the number of partitions per table. Using 'MAX_ROWS' to force the number of partitions is deprecated as of NDB 7.5.4, continues to be supported in NDB 7.6 for backward compatibility, but is subject to removal in a future release of MySQL NDB Cluster. (Bug #81759, Bug #23544301)

Prior to MySQL NDB Cluster 7.5.4, 'PARTITION_BALANCE' was named 'FRAGMENT_COUNT_TYPE', and accepted as its value one of (in the same order as that of the listing just shown) 'ONE_PER_NODE', 'ONE_PER_NODE_GROUP', 'ONE_PER_LDM_PER_NODE', or 'ONE_PER_LDM_PER_NODE_GROUP'. (Bug #81761, Bug #23547525)

'FULLY_REPLICATED' controls whether the table is fully replicated, that is, whether each data node has a complete copy of the table. To enable full replication of the table, use 'FULLY_REPLICATED=1'.

This setting can also be controlled using the 'ndb_fully_replicated' system variable. Setting it to 'ON' enables the option by default for all new 'NDB' tables; the default is 'OFF', which maintains the previous behavior (as in MySQL NDB Cluster 7.5.1 and earlier, before support for fully replicated tables was introduced). The 'ndb_data_node_neighbour' system variable is also used for fully replicated tables, to ensure that when a fully replicated table is accessed, we access the data node which is local to this MySQL Server.

An example of a 'CREATE TABLE' statement using such a comment when creating an 'NDB' table is shown here:

 mysql> CREATE TABLE t1 (
      >     c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
      >     c2 VARCHAR(100),
      >     c3 VARCHAR(100) )
      > ENGINE=NDB
      >
 COMMENT="NDB_TABLE=READ_BACKUP=0,PARTITION_BALANCE=FOR_RP_BY_NODE";

The comment is displayed as part of the ouput of note 'SHOW CREATE TABLE': show-create-table. The text of the comment is also available from querying the MySQL Information Schema note 'TABLES': information-schema-tables-table. table, as in this example:

 mysql> SELECT TABLE_NAME, TABLE_SCHEMA, TABLE_COMMENT
      > FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME="t1"\G
 *************************** 1. row ***************************
    TABLE_NAME: t1
  TABLE_SCHEMA: test
 TABLE_COMMENT: NDB_TABLE=READ_BACKUP=0,PARTITION_BALANCE=FOR_RP_BY_NODE
 1 row in set (0.01 sec)

This comment syntax is also supported with *note 'ALTER TABLE': alter-table. statements for 'NDB' tables, as shown here:

 mysql> ALTER TABLE t1 COMMENT="NDB_TABLE=PARTITION_BALANCE=FOR_RA_BY_NODE";
 Query OK, 0 rows affected (0.40 sec)
 Records: 0  Duplicates: 0  Warnings: 0

Beginning with NDB 7.6.15, the 'TABLE_COMMENT' column displays the comment that is required to re-create the table as it is following the 'ALTER TABLE' statement, like this:

 mysql> SELECT TABLE_NAME, TABLE_SCHEMA, TABLE_COMMENT
     ->     FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME="t1"\G
 *************************** 1. row ***************************
    TABLE_NAME: t1
  TABLE_SCHEMA: test
 TABLE_COMMENT: NDB_TABLE=READ_BACKUP=0,PARTITION_BALANCE=FOR_RP_BY_NODE
 1 row in set (0.01 sec)

 mysql> SELECT TABLE_NAME, TABLE_SCHEMA, TABLE_COMMENT
      > FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME="t1";
 +------------+--------------+--------------------------------------------------+
 | TABLE_NAME | TABLE_SCHEMA | TABLE_COMMENT                                    |
 +------------+--------------+--------------------------------------------------+
 | t1         | c            | NDB_TABLE=PARTITION_BALANCE=FOR_RA_BY_NODE       |
 | t1         | d            |                                                  |
 +------------+--------------+--------------------------------------------------+
 2 rows in set (0.01 sec)

Keep in mind that a table comment used with 'ALTER TABLE' replaces any existing comment which the table might have.

 mysql> ALTER TABLE t1 COMMENT="NDB_TABLE=PARTITION_BALANCE=FOR_RA_BY_NODE";
 Query OK, 0 rows affected (0.40 sec)
 Records: 0  Duplicates: 0  Warnings: 0

 mysql> SELECT TABLE_NAME, TABLE_SCHEMA, TABLE_COMMENT
      > FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME="t1";
 +------------+--------------+--------------------------------------------------+
 | TABLE_NAME | TABLE_SCHEMA | TABLE_COMMENT                                    |
 +------------+--------------+--------------------------------------------------+
 | t1         | c            | NDB_TABLE=PARTITION_BALANCE=FOR_RA_BY_NODE       |
 | t1         | d            |                                                  |
 +------------+--------------+--------------------------------------------------+
 2 rows in set (0.01 sec)

Prior to NDB 7.6.15, the table comment used with 'ALTER TABLE' replaced any existing comment which the table might have had. This meant that (for example) the 'READ_BACKUP' value was not carried over to the new comment set by the 'ALTER TABLE' statement, and that any unspecified values reverted to their defaults. (BUG#30428829) There was thus no longer any way using SQL to retrieve the value previously set for the comment. To keep comment values from reverting to their defaults, it was necessry to preserve any such values from the existing comment string and include them in the comment passed to 'ALTER TABLE'.

You can also see the value of the 'PARTITION_BALANCE' option in the output of note 'ndb_desc': mysql-cluster-programs-ndb-desc. note 'ndb_desc': mysql-cluster-programs-ndb-desc. also shows whether the 'READ_BACKUP' and 'FULLY_REPLICATED' options are set for the table. See the description of this program for more information.

 File: manual.info.tmp, Node: create-tablespace, Next: create-trigger, Prev: create-table, Up: sql-data-definition-statements

13.1.19 CREATE TABLESPACE Statement

 CREATE TABLESPACE TABLESPACE_NAME

   _InnoDB and NDB:_
     ADD DATAFILE 'FILE_NAME'

   _InnoDB only:_
     [FILE_BLOCK_SIZE = value]

   _NDB only:_
     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 [=] 'STRING']

   _InnoDB and NDB:_
     [ENGINE [=] ENGINE_NAME]

This statement is used to create a tablespace. The precise syntax and semantics depend on the storage engine used. In standard MySQL 5.7 releases, this is always an note 'InnoDB': innodb-storage-engine. tablespace. MySQL NDB Cluster 7.5 also supports tablespaces using the note 'NDB': mysql-cluster. storage engine in addition to those using 'InnoDB'.

Considerations for InnoDB

*note 'CREATE TABLESPACE': create-tablespace. syntax is used to create general tablespaces. A general tablespace is a shared tablespace. It can hold multiple tables, and supports all table row formats. General tablespaces can be created in a location relative to or independent of the data directory.

After creating an 'InnoDB' general tablespace, you can use note 'CREATE TABLE TBL_NAME ... TABLESPACE [=] TABLESPACE_NAME': create-table. or note 'ALTER TABLE TBL_NAME TABLESPACE [=] TABLESPACE_NAME': alter-table. to add tables to the tablespace. For more information, see *note general-tablespaces::.

Considerations for NDB Cluster

This statement is used to create a tablespace, which can contain one or more data files, providing storage space for NDB Cluster Disk Data tables (see note mysql-cluster-disk-data::). 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 note 'ALTER TABLESPACE': alter-tablespace. statement (see *note alter-tablespace::).

Note:

All NDB 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 note 'CREATE LOGFILE GROUP': create-logfile-group. (see note create-logfile-group::). Multiple tablespaces may use the same log file group for 'UNDO' logging.

When setting 'EXTENT_SIZE' or 'INITIAL_SIZE', 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' and 'EXTENT_SIZE' are subject to rounding as follows:

Note:

*note 'NDB': mysql-cluster. reserves 4% of a tablespace for data node restart operations. This reserved space cannot be used for data storage. This restriction applies beginning with NDB 7.6.

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 *note 'FILES': information-schema-files-table. 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.

When note 'CREATE TABLESPACE': 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 note 'FILES': information-schema-files-table. table. (See the example later in this section.)

(See *note information-schema-files-table::.)

Options

Notes

InnoDB Examples

This example demonstrates creating a general tablespace and adding three uncompressed tables of different row formats.

 mysql> CREATE TABLESPACE `ts1` ADD DATAFILE 'ts1.ibd' ENGINE=INNODB;

 mysql> CREATE TABLE t1 (c1 INT PRIMARY KEY) TABLESPACE ts1 ROW_FORMAT=REDUNDANT;

 mysql> CREATE TABLE t2 (c1 INT PRIMARY KEY) TABLESPACE ts1 ROW_FORMAT=COMPACT;

 mysql> CREATE TABLE t3 (c1 INT PRIMARY KEY) TABLESPACE ts1 ROW_FORMAT=DYNAMIC;

This example demonstrates creating a general tablespace and adding a compressed table. The example assumes a default 'innodb_page_size' value of 16K. The 'FILE_BLOCK_SIZE' of 8192 requires that the compressed table have a 'KEY_BLOCK_SIZE' of 8.

 mysql> CREATE TABLESPACE `ts2` ADD DATAFILE 'ts2.ibd' FILE_BLOCK_SIZE = 8192 Engine=InnoDB;

 mysql> CREATE TABLE t4 (c1 INT PRIMARY KEY) TABLESPACE ts2 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;

NDB Example

Suppose that you wish to create an NDB Cluster Disk Data tablespace named 'myts' using a datafile named 'mydata-1.dat'. An 'NDB' tablespace always requires the use of a log file group consisting of one or more undo log files. For this example, we first create a log file group named 'mylg' that contains one undo long file named 'myundo-1.dat', using the *note 'CREATE LOGFILE GROUP': create-logfile-group. statement shown here:

 mysql> CREATE LOGFILE GROUP myg1
     ->     ADD UNDOFILE 'myundo-1.dat'
     ->     ENGINE=NDB;
 Query OK, 0 rows affected (3.29 sec)

Now you can create the tablespace previously described using the following statement:

 mysql> CREATE TABLESPACE myts
     ->     ADD DATAFILE 'mydata-1.dat'
     ->     USE LOGFILE GROUP mylg
     ->     ENGINE=NDB;
 Query OK, 0 rows affected (2.98 sec)

You can now create a Disk Data table using a *note 'CREATE TABLE': create-table. statement with the 'TABLESPACE' and 'STORAGE DISK' options, similar to what is shown here:

 mysql> CREATE TABLE mytable (
     ->     id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
     ->     lname VARCHAR(50) NOT NULL,
     ->     fname VARCHAR(50) NOT NULL,
     ->     dob DATE NOT NULL,
     ->     joined DATE NOT NULL,
     ->     INDEX(last_name, first_name)
     -> )
     ->     TABLESPACE myts STORAGE DISK
     ->     ENGINE=NDB;
 Query OK, 0 rows affected (1.41 sec)

It is important to note that only the 'dob' and 'joined' columns from 'mytable' are actually stored on disk, due to the fact that the 'id', 'lname', and 'fname' columns are all indexed.

As mentioned previously, when 'CREATE TABLESPACE' is used with 'ENGINE [=] NDB', a tablespace and associated data file are created on each NDB Cluster data node. You can verify that the data files were created and obtain information about them by querying the Information Schema *note 'FILES': information-schema-files-table. table, as shown here:

 mysql> SELECT FILE_NAME, FILE_TYPE, LOGFILE_GROUP_NAME, STATUS, EXTRA
     ->     FROM INFORMATION_SCHEMA.FILES
     ->     WHERE TABLESPACE_NAME = 'myts';

 +--------------+------------+--------------------+--------+----------------+
 | file_name    | file_type  | logfile_group_name | status | extra          |
 +--------------+------------+--------------------+--------+----------------+
 | mydata-1.dat | DATAFILE   | mylg               | NORMAL | CLUSTER_NODE=5 |
 | mydata-1.dat | DATAFILE   | mylg               | NORMAL | CLUSTER_NODE=6 |
 | NULL         | TABLESPACE | mylg               | NORMAL | NULL           |
 +--------------+------------+--------------------+--------+----------------+
 3 rows in set (0.01 sec)

For additional information and examples, see *note mysql-cluster-disk-data-objects::.

 File: manual.info.tmp, Node: create-trigger, Next: create-view, Prev: create-tablespace, Up: sql-data-definition-statements

13.1.20 CREATE TRIGGER Statement

 CREATE
     [DEFINER = USER]
     TRIGGER TRIGGER_NAME
     TRIGGER_TIME TRIGGER_EVENT
     ON TBL_NAME FOR EACH ROW
     [TRIGGER_ORDER]
     TRIGGER_BODY

 TRIGGER_TIME: { BEFORE | AFTER }

 TRIGGER_EVENT: { INSERT | UPDATE | DELETE }

 TRIGGER_ORDER: { FOLLOWS | PRECEDES } OTHER_TRIGGER_NAME

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.

Trigger names exist in the schema namespace, meaning that all triggers must have unique names within a schema. Triggers in different schemas can have the same name.

This section describes note 'CREATE TRIGGER': create-trigger. syntax. For additional discussion, see note trigger-syntax::.

note 'CREATE TRIGGER': create-trigger. requires the 'TRIGGER' privilege for the table associated with the trigger. If the 'DEFINER' clause is present, the privileges required depend on the USER value, as discussed in note stored-objects-security::. If binary logging is enabled, note 'CREATE TRIGGER': create-trigger. might require the 'SUPER' privilege, as discussed in note stored-programs-logging::.

The 'DEFINER' clause determines the security context to be used when checking access privileges at trigger activation time, as described later in this section.

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.

Basic column value checks occur prior to trigger activation, so you cannot use 'BEFORE' triggers to convert values inappropriate for the column type to valid values.

TRIGGER_EVENT indicates the kind of operation that activates the trigger. These TRIGGER_EVENT values are permitted:

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 note 'INSERT': insert. trigger activates not only for note 'INSERT': insert. statements but also *note 'LOAD DATA': 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 activates 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.

Note:

Cascaded foreign key actions do not activate triggers.

It is possible to define multiple triggers for a given table that have the same trigger event and action time. For example, you can have two 'BEFORE UPDATE' triggers for a table. By default, triggers that have the same trigger event and action time activate in the order they were created. To affect trigger order, specify a TRIGGER_ORDER clause that indicates 'FOLLOWS' or 'PRECEDES' and the name of an existing trigger that also has the same trigger event and action time. With 'FOLLOWS', the new trigger activates after the existing trigger. With 'PRECEDES', the new trigger activates before the existing trigger.

TRIGGER_BODY is the statement to execute when the trigger activates. To execute multiple statements, use the note 'BEGIN ... END': begin-end. compound statement construct. This also enables you to use the same statements that are permitted within stored routines. See note begin-end::. Some statements are not permitted in triggers; see *note stored-program-restrictions::.

Within the trigger body, 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.

Triggers cannot use 'NEW.COL_NAME' or use 'OLD.COL_NAME' to refer to generated columns. For information about generated columns, see *note create-table-generated-columns::.

MySQL stores the 'sql_mode' system variable setting in effect when a trigger is created, and always executes the trigger body with this setting in force, regardless of the current server SQL mode when the trigger begins executing.

The 'DEFINER' clause specifies the MySQL account to be used when checking access privileges at trigger activation time. If the 'DEFINER' clause is present, the USER value should be a MySQL account specified as ''USER_NAME'@'HOST_NAME'', 'CURRENT_USER', or 'CURRENT_USER()'. The permitted USER values depend on the privileges you hold, as discussed in *note stored-objects-security::. Also see that section for additional information about trigger security.

If the 'DEFINER' clause is omitted, the default definer is the user who executes the *note 'CREATE TRIGGER': create-trigger. statement. This is the same as specifying 'DEFINER = CURRENT_USER' explicitly.

MySQL takes the 'DEFINER' user into account when checking trigger privileges as follows:

Within a trigger body, 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 *note account-activity-auditing::.

If you use note 'LOCK TABLES': lock-tables. to lock a table that has triggers, the tables used within the trigger are also locked, as described in note lock-tables-and-triggers::.

For additional discussion of trigger use, see *note trigger-syntax::.

 File: manual.info.tmp, Node: create-view, Next: drop-database, Prev: create-trigger, Up: sql-data-definition-statements

13.1.21 CREATE VIEW Statement

 CREATE
     [OR REPLACE]
     [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
     [DEFINER = USER]
     [SQL SECURITY { DEFINER | INVOKER }]
     VIEW VIEW_NAME [(COLUMN_LIST)]
     AS SELECT_STATEMENT
     [WITH [CASCADED | LOCAL] CHECK OPTION]

The note 'CREATE VIEW': create-view. statement creates a new view, or replaces an existing view if the 'OR REPLACE' clause is given. If the view does not exist, note 'CREATE OR REPLACE VIEW': create-view. is the same as note 'CREATE VIEW': create-view. If the view does exist, note 'CREATE OR REPLACE VIEW': create-view. replaces it.

For information about restrictions on view use, see *note view-restrictions::.

The SELECT_STATEMENT is a note 'SELECT': select. statement that provides the definition of the view. (Selecting from the view selects, in effect, using the note 'SELECT': select. statement.) The SELECT_STATEMENT can select from base tables or from other views.

The view definition is 'frozen' at creation time and is not affected by subsequent changes to the definitions of the underlying tables. 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, and columns dropped from the table result in an error when selecting from 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 note 'CREATE VIEW': create-view. statement requires the 'CREATE VIEW' privilege for the view, and some privilege for each column selected by the note 'SELECT': select. statement. For columns used elsewhere in the note 'SELECT': 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. If the 'DEFINER' clause is present, the privileges required depend on the USER value, as discussed in note stored-objects-security::.

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, use DB_NAME.VIEW_NAME syntax to qualify the view name with the database name:

 CREATE VIEW test.v AS SELECT * FROM t;

Unqualified table or view names in the *note 'SELECT': select. statement are also 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 appropriate database name.

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 *note 'SELECT': select. statement can be simple references to table columns, or expressions that use functions, constant values, operators, and so forth.

A view must have unique column names with no duplicates, just like a base table. By default, the names of the columns retrieved by the note 'SELECT': select. statement are used for the view column names. To define explicit names for the view columns, specify the optional COLUMN_LIST clause 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 note 'SELECT': select. statement.

A view can be created from many kinds of note 'SELECT': select. statements. It can refer to base tables or other views. It can use joins, note 'UNION': union, and subqueries. The *note 'SELECT': select. need not even refer to any tables:

 CREATE VIEW v_today (today) AS SELECT CURRENT_DATE;

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:

'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 *note 'SELECT': select. keyword, and to clauses such as 'INTO', 'FOR UPDATE', 'LOCK IN SHARE MODE', and 'PROCEDURE'.

The results obtained from a view may be affected if you change the query processing environment by changing system variables:

 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' (the default) and 'INVOKER'. These indicate that the required privileges must be held by the user who defined or invoked the view, respectively.

If the 'DEFINER' clause is present, the USER value should be a MySQL account specified as ''USER_NAME'@'HOST_NAME'', 'CURRENT_USER', or 'CURRENT_USER()'. The permitted USER values depend on the privileges you hold, as discussed in *note stored-objects-security::. Also see that section for additional information about view security.

If the 'DEFINER' clause is omitted, the default definer is the user who executes the *note 'CREATE VIEW': create-view. statement. This is the same as specifying 'DEFINER = CURRENT_USER' explicitly.

Within a view definition, the 'CURRENT_USER' function 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 *note 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'.

MySQL checks view privileges like this:

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'. For more information, see note view-algorithms::, as well as note derived-table-optimization::.

Some views are updatable. That is, you can use them in statements such as note 'UPDATE': update, note 'DELETE': delete, or *note 'INSERT': 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.

A generated column in a view is considered updatable because it is possible to assign to it. However, if such a column is updated explicitly, the only permitted value is 'DEFAULT'. For information about generated columns, see *note create-table-generated-columns::.

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 note view-updatability::, and note view-check-option::.

Views created before MySQL 5.7.3 containing 'ORDER BY INTEGER' can result in errors at view evaluation time. Consider these view definitions, which use 'ORDER BY' with an ordinal number:

 CREATE VIEW v1 AS SELECT x, y, z FROM t ORDER BY 2;
 CREATE VIEW v2 AS SELECT x, 1, z FROM t ORDER BY 2;

In the first case, 'ORDER BY 2' refers to a named column 'y'. In the second case, it refers to a constant 1. For queries that select from either view fewer than 2 columns (the number named in the 'ORDER BY' clause), an error occurs if the server evaluates the view using the MERGE algorithm. Examples:

 mysql> SELECT x FROM v1;
 ERROR 1054 (42S22): Unknown column '2' in 'order clause'
 mysql> SELECT x FROM v2;
 ERROR 1054 (42S22): Unknown column '2' in 'order clause'

As of MySQL 5.7.3, to handle view definitions like this, the server writes them differently into the '.frm' file that stores the view definition. This difference is visible with *note 'SHOW CREATE VIEW': show-create-view. Previously, the '.frm' file contained this for the 'ORDER BY 2' clause:

 For v1: ORDER BY 2
 For v2: ORDER BY 2

As of 5.7.3, the '.frm' file contains this:

 For v1: ORDER BY `t`.`y`
 For v2: ORDER BY ''

That is, for 'v1', 2 is replaced by a reference to the name of the column referred to. For 'v2', 2 is replaced by a constant string expression (ordering by a constant has no effect, so ordering by any constant works).

If you experience view-evaluation errors such as just described, drop and recreate the view so that the '.frm' file contains the updated view representation. Alternatively, for views like 'v2' that order by a constant value, drop and recreate the view with no 'ORDER BY' clause.

 File: manual.info.tmp, Node: drop-database, Next: drop-event, Prev: create-view, Up: sql-data-definition-statements

13.1.22 DROP DATABASE Statement

 DROP {DATABASE | SCHEMA} [IF EXISTS] DB_NAME

note 'DROP DATABASE': drop-database. drops all tables in the database and deletes the database. Be very careful with this statement! To use note 'DROP DATABASE': drop-database, you need the 'DROP' privilege on the database. note 'DROP SCHEMA': drop-database. is a synonym for note 'DROP DATABASE': drop-database.

Important:

When a database is dropped, privileges granted specifically for the database are not automatically dropped. They must be dropped manually. See *note grant::.

'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 *note 'DROP DATABASE': drop-database. on a symbolically linked database, both the link and the original database are deleted.

*note 'DROP DATABASE': drop-database. returns the number of tables that were removed. This corresponds to the number of '.frm' files removed.

The *note 'DROP DATABASE': 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 *note 'DROP DATABASE': drop-database. statement again.

Dropping a database does not remove any 'TEMPORARY' tables that were created in that database. 'TEMPORARY' tables are automatically removed when the session that created them ends. See *note create-temporary-table::.

You can also drop databases with note 'mysqladmin': mysqladmin. See note mysqladmin::.

 File: manual.info.tmp, Node: drop-event, Next: drop-function, Prev: drop-database, Up: sql-data-definition-statements

13.1.23 DROP EVENT Statement

 DROP 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.

 File: manual.info.tmp, Node: drop-function, Next: drop-index, Prev: drop-event, Up: sql-data-definition-statements

13.1.24 DROP FUNCTION Statement

The *note 'DROP FUNCTION': drop-function. statement is used to drop stored functions and loadable functions:

 File: manual.info.tmp, Node: drop-index, Next: drop-logfile-group, Prev: drop-function, Up: sql-data-definition-statements

13.1.25 DROP INDEX Statement

 DROP INDEX INDEX_NAME ON TBL_NAME
     [ALGORITHM_OPTION | LOCK_OPTION] ...

 ALGORITHM_OPTION:
     ALGORITHM [=] {DEFAULT | INPLACE | COPY}

 LOCK_OPTION:
     LOCK [=] {DEFAULT | NONE | SHARED | EXCLUSIVE}

note 'DROP INDEX': drop-index. drops the index named INDEX_NAME from the table TBL_NAME. This statement is mapped to an note 'ALTER TABLE': alter-table. statement to drop the index. See *note alter-table::.

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 *note 'NDB': mysql-cluster. tables are dropped online; that is, without any table copying. The table is not locked against access from other NDB 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.

'ALGORITHM' and 'LOCK' clauses may be given to influence the table copying method and level of concurrency for reading and writing the table while its indexes are being modified. They have the same meaning as for the note 'ALTER TABLE': alter-table. statement. For more information, see note alter-table::

NDB Cluster formerly supported online 'DROP INDEX' operations using the 'ONLINE' and 'OFFLINE' keywords. These keywords are no longer supported in MySQL NDB Cluster 7.5 and later, and their use causes a syntax error. Instead, MySQL NDB Cluster 7.5 and later support online operations using the same 'ALGORITHM=INPLACE' syntax used with the standard MySQL Server. See *note mysql-cluster-online-operations::, for more information.

 File: manual.info.tmp, Node: drop-logfile-group, Next: drop-procedure, Prev: drop-index, Up: sql-data-definition-statements

13.1.26 DROP LOGFILE GROUP Statement

 DROP 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 *note create-logfile-group::.)

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 note 'NDB': mysql-cluster. and note 'NDBCLUSTER': mysql-cluster.

note 'DROP LOGFILE GROUP': drop-logfile-group. is useful only with Disk Data storage for NDB Cluster. See note mysql-cluster-disk-data::.

 File: manual.info.tmp, Node: drop-procedure, Next: drop-server, Prev: drop-logfile-group, Up: sql-data-definition-statements

13.1.27 DROP PROCEDURE and DROP FUNCTION Statements

 DROP {PROCEDURE | FUNCTION} [IF EXISTS] SP_NAME

These statements are used to drop a stored routine (a stored procedure or function). That is, the specified routine is removed from the server. ('DROP FUNCTION' is also used to drop loadable functions; see *note drop-function-loadable::.)

To drop a stored routine, you must have the 'ALTER ROUTINE' privilege for it. (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 *note stored-routines-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 *note 'SHOW WARNINGS': show-warnings.

note 'DROP FUNCTION': drop-function. is also used to drop loadable functions (see note drop-function-loadable::).

 File: manual.info.tmp, Node: drop-server, Next: drop-table, Prev: drop-procedure, Up: sql-data-definition-statements

13.1.28 DROP SERVER Statement

 DROP 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 *note create-server::.

'DROP SERVER' causes an implicit commit. See *note implicit-commit::.

'DROP SERVER' is not written to the binary log, regardless of the logging format that is in use.

 File: manual.info.tmp, Node: drop-table, Next: drop-tablespace, Prev: drop-server, Up: sql-data-definition-statements

13.1.29 DROP TABLE Statement

 DROP [TEMPORARY] TABLE [IF EXISTS]
     TBL_NAME [, TBL_NAME] ...
     [RESTRICT | CASCADE]

*note 'DROP TABLE': drop-table. removes one or more tables. You must have the 'DROP' privilege for each table.

Be careful with this statement! For each table, it removes the table definition and all table data. If the table is partitioned, the statement removes the table definition, all its partitions, all data stored in those partitions, and all partition definitions associated with the dropped table.

Dropping a table also drops any triggers for the table.

note 'DROP TABLE': drop-table. causes an implicit commit, except when used with the 'TEMPORARY' keyword. See note implicit-commit::.

Important:

When a table is dropped, privileges granted specifically for the table are not automatically dropped. They must be dropped manually. See *note grant::.

If any tables named in the argument list do not exist, *note 'DROP TABLE': drop-table. behavior depends on whether the 'IF EXISTS' clause is given:

'IF EXISTS' can also be useful for dropping tables in unusual circumstances under which there is an '.frm' file but no table managed by the storage engine. (For example, if an abnormal server exit occurs after removal of the table from the storage engine but before '.frm' file removal.)

The 'TEMPORARY' keyword has the following effects:

Including the 'TEMPORARY' keyword is a good way to prevent accidentally dropping non-'TEMPORARY' tables.

The 'RESTRICT' and 'CASCADE' keywords do nothing. They are permitted to make porting easier from other database systems.

note 'DROP TABLE': drop-table. is not supported with all 'innodb_force_recovery' settings. See note forcing-innodb-recovery::.

 File: manual.info.tmp, Node: drop-tablespace, Next: drop-trigger, Prev: drop-table, Up: sql-data-definition-statements

13.1.30 DROP TABLESPACE Statement

 DROP TABLESPACE TABLESPACE_NAME
     [ENGINE [=] ENGINE_NAME]

This statement drops a tablespace that was previously created using *note 'CREATE TABLESPACE': create-tablespace. It is supported with all MySQL NDB Cluster 7.5 releases, and with 'InnoDB' in the standard MySQL Server as well.

'ENGINE' sets the storage engine that uses the tablespace, where ENGINE_NAME is the name of the storage engine. Currently, the values 'InnoDB' and 'NDB' are supported. If not set, the value of 'default_storage_engine' is used. If it is not the same as the storage engine used to create the tablespace, the 'DROP TABLESPACE' statement fails.

For an 'InnoDB' tablespace, all tables must be dropped from the tablespace prior to a 'DROP TABLESPACE' operation. If the tablespace is not empty, 'DROP TABLESPACE' returns an error.

As with the 'InnoDB' system tablespace, truncating or dropping 'InnoDB' tables stored in a general tablespace creates free space in the tablespace .ibd data file, which can only be used for new 'InnoDB' data. Space is not released back to the operating system by such operations as it is for file-per-table tablespaces.

An 'NDB' tablespace to be dropped must not contain any data files; in other words, before you can drop an 'NDB' tablespace, you must first drop each of its data files using *note 'ALTER TABLESPACE ... DROP DATAFILE': alter-tablespace.

Notes

InnoDB Example

This example demonstrates how to drop an 'InnoDB' general tablespace. The general tablespace 'ts1' is created with a single table. Before dropping the tablespace, the table must be dropped.

 mysql> CREATE TABLESPACE `ts1` ADD DATAFILE 'ts1.ibd' Engine=InnoDB;

 mysql> CREATE TABLE t1 (c1 INT PRIMARY KEY) TABLESPACE ts1 Engine=InnoDB;

 mysql> DROP TABLE t1;

 mysql> DROP TABLESPACE ts1;

NDB Example

This example shows how to drop an 'NDB' tablespace 'myts' having a data file named 'mydata-1.dat' after first creating the tablespace, and assumes the existence of a log file group named 'mylg' (see *note create-logfile-group::).

 mysql> CREATE TABLESPACE myts
     ->     ADD DATAFILE 'mydata-1.dat'
     ->     USE LOGFILE GROUP mylg
     ->     ENGINE=NDB;

You must remove all data files from the tablespace using *note 'ALTER TABLESPACE': alter-tablespace, as shown here, before it can be dropped:

 mysql> ALTER TABLESPACE myts
     ->     DROP DATAFILE 'mydata-1.dat'
     ->     ENGINE=NDB;

 mysql> DROP TABLESPACE myts;

 File: manual.info.tmp, Node: drop-trigger, Next: drop-view, Prev: drop-tablespace, Up: sql-data-definition-statements

13.1.31 DROP TRIGGER Statement

 DROP 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. *note 'DROP TRIGGER': 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 *note show-warnings::.

Triggers for a table are also dropped if you drop the table.

 File: manual.info.tmp, Node: drop-view, Next: rename-table, Prev: drop-trigger, Up: sql-data-definition-statements

13.1.32 DROP VIEW Statement

 DROP VIEW [IF EXISTS]
     VIEW_NAME [, VIEW_NAME] ...
     [RESTRICT | CASCADE]

*note 'DROP VIEW': drop-view. removes one or more views. You must have the 'DROP' privilege for each view.

If any views named in the argument list do not exist, the statement returns an error indicating by name which nonexisting views it was unable to drop, but also drops all views in the list that do exist.

Note:

In MySQL 8.0, note 'DROP VIEW': drop-view. fails if any views named in the argument list do not exist. Due to the change in behavior, a partially completed note 'DROP VIEW': drop-view. operation on a MySQL 5.7 source fails when replicated to a MySQL 8.0 replica. To avoid this failure scenario, use 'IF EXISTS' syntax in *note 'DROP VIEW': drop-view. statements to prevent an error from occurring for views that do not exist. For more information, see Atomic Data Definition Statement Support (https://dev.mysql.com/doc/refman/8.0/en/atomic-ddl.html).

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 *note show-warnings::.

'RESTRICT' and 'CASCADE', if given, are parsed and ignored.

 File: manual.info.tmp, Node: rename-table, Next: truncate-table, Prev: drop-view, Up: sql-data-definition-statements

13.1.33 RENAME TABLE Statement

 RENAME TABLE
     TBL_NAME TO NEW_TBL_NAME
     [, TBL_NAME2 TO NEW_TBL_NAME2] ...

*note 'RENAME TABLE': rename-table. renames one or more tables. You must have 'ALTER' and 'DROP' privileges for the original table, and 'CREATE' and 'INSERT' privileges for the new table.

For example, to rename a table named 'old_table' to 'new_table', use this statement:

 RENAME TABLE old_table TO new_table;

That statement is equivalent to the following *note 'ALTER TABLE': alter-table. statement:

 ALTER TABLE old_table RENAME new_table;

'RENAME TABLE', unlike *note 'ALTER TABLE': alter-table, can rename multiple tables within a single statement:

 RENAME TABLE old_table1 TO new_table1,
              old_table2 TO new_table2,
              old_table3 TO new_table3;

Renaming operations are performed left to right. Thus, to swap two table names, do this (assuming that a table with the intermediary name 'tmp_table' does not already exist):

 RENAME TABLE old_table TO tmp_table,
              new_table TO old_table,
              tmp_table TO new_table;

Metadata locks on tables are acquired in name order, which in some cases can make a difference in operation outcome when multiple transactions execute concurrently. See *note metadata-locking::.

To execute 'RENAME TABLE', there must be no active transactions or tables locked with 'LOCK TABLES'. With the transaction table locking conditions satisfied, the rename operation is done atomically; no other session can access any of the tables while the rename is in progress.

If any errors occur during a 'RENAME TABLE', the statement fails and no changes are made.

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;

Using this method to move all tables from one database to a different one in effect renames the database (an operation for which MySQL has no single statement), except that the original database continues to exist, albeit with no tables.

Like 'RENAME TABLE', 'ALTER TABLE ... RENAME' can also be used to move a table to a different database. Regardless of the statement used, if the rename operation would move the table to a database located on a different file system, the success of the outcome is platform specific and depends on the underlying operating system calls used to move table files.

If a table has triggers, attempts to rename the table into a different database fail with a 'Trigger in wrong schema' ('ER_TRG_IN_WRONG_SCHEMA' (https://dev.mysql.com/doc/mysql-errors/5.7/en/server-error-reference.html#error_er_trg_in_wrong_schema)) error.

To rename 'TEMPORARY' tables, 'RENAME TABLE' does not work. Use *note 'ALTER TABLE': alter-table. instead.

'RENAME TABLE' works for views, except that views cannot be renamed into a different database.

Any privileges granted specifically for a renamed table or view are not migrated to the new name. They must be changed manually.

'RENAME TABLE TBL_NAME TO NEW_TBL_NAME' changes internally generated foreign key constraint names and user-defined foreign key constraint names that begin with the string 'TBL_NAME_ibfk_' to reflect the new table name. 'InnoDB' interprets foreign key constraint names that begin with the string 'TBL_NAME_ibfk_' as internally generated names.

Foreign key constraint names that point to the renamed table are automatically updated unless there is a conflict, in which case the statement fails with an error. A conflict occurs if the renamed constraint name already exists. In such cases, you must drop and re-create the foreign keys for them to function properly.

 File: manual.info.tmp, Node: truncate-table, Prev: rename-table, Up: sql-data-definition-statements

13.1.34 TRUNCATE TABLE Statement

 TRUNCATE [TABLE] TBL_NAME

*note 'TRUNCATE TABLE': truncate-table. empties a table completely. It requires the 'DROP' privilege.

Logically, note 'TRUNCATE TABLE': truncate-table. is similar to a note 'DELETE': delete. statement that deletes all rows, or a sequence of note 'DROP TABLE': drop-table. and note 'CREATE TABLE': 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 note 'TRUNCATE TABLE': truncate-table. is similar to note 'DELETE': delete, it is classified as a DDL statement rather than a DML statement. It differs from *note 'DELETE': delete. in the following ways:

*note 'TRUNCATE TABLE': truncate-table. is treated for purposes of binary logging and replication as DDL rather than DML, and is always logged as a statement.

note 'TRUNCATE TABLE': truncate-table. for a table closes all handlers for the table that were opened with note 'HANDLER OPEN': handler.

On a system with a large 'InnoDB' buffer pool and 'innodb_adaptive_hash_index' enabled, 'TRUNCATE TABLE' operations may cause a temporary drop in system performance due to an LRU scan that occurs when removing an 'InnoDB' table's adaptive hash index entries. The problem was addressed for *note 'DROP TABLE': drop-table. in MySQL 5.5.23 (Bug #13704145, Bug #64284) but remains a known issue for 'TRUNCATE TABLE' (Bug #68184).

note 'TRUNCATE TABLE': 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 note performance-schema-summary-tables::.

 File: manual.info.tmp, Node: sql-data-manipulation-statements, Next: sql-transactional-statements, Prev: sql-data-definition-statements, Up: sql-statements