How to add foreign key in mysql phpmyadmin

13.1.20.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 CREATE TABLE or ALTER TABLE statement includes the following:

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

Foreign key constraint naming is governed by the following rules:

• The CONSTRAINT symbol value is used, if defined.

• If the CONSTRAINT symbol clause is not defined, or a symbol is not included following the CONSTRAINT keyword, a constraint name name is generated automatically.

  Prior to MySQL 8.0.16, if the CONSTRAINT symbol clause was not defined, or a symbol was not included following the CONSTRAINT keyword, both InnoDB and NDB storage engines would use the FOREIGN_KEY index_name if defined. In MySQL 8.0.16 and higher, the FOREIGN_KEY index_name is ignored.

• The CONSTRAINT symbol value, if defined, must be unique in the database. A duplicate symbol results in an error similar to: ERROR 1005 (HY000): Can't create table 'test.fk1' (errno: 121).

• NDB Cluster stores foreign names using the same lettercase with which they are created. Prior to version 8.0.20, when processing SELECT and other SQL statements, NDB compared the names of foreign keys in such statements with the names as stored in a case-sensitive fashion when lower_case_table_names was equal to 0. In NDB 8.0.20 and later, this value no longer has any effect on how such comparisons are made, and they are always done without regard to lettercase. (Bug #30512043)

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:

• Parent and child tables must use the same storage engine, and they cannot be defined as temporary tables.

• Creating a foreign key constraint requires the REFERENCES privilege on the parent table.

• Corresponding columns in the foreign key and the referenced key must have similar data types. The size and sign of fixed precision types such as INTEGER and DECIMAL must be the same. The length of string types need not be the same. For nonbinary (character) string columns, the character set and collation must be the same.

• MySQL supports foreign key references between one column and another within a table. (A column cannot have a foreign key reference to itself.) In these cases, a “child table record” refers to a dependent record within the same table.

• MySQL requires indexes on foreign keys and referenced keys so that foreign key checks can be fast and not require a table scan. In the referencing table, there must be an index where the foreign key columns are listed as the first columns in the same order. Such an index is created on the referencing table automatically if it does not exist. This index might be silently dropped later if you create another index that can be used to enforce the foreign key constraint. index_name, if given, is used as described previously.

• InnoDB permits a foreign key to reference any index column or group of columns. However, in the referenced table, there must be an index where the referenced columns are the first columns in the same order. Hidden columns that InnoDB adds to an index are also considered (see Section 15.6.2.1, “Clustered and Secondary Indexes”).

  NDB requires an explicit unique key (or primary key) on any column referenced as a foreign key. InnoDB does not, which is an extension of standard SQL.

• Index prefixes on foreign key columns are not supported. Consequently, BLOB and TEXT columns cannot be included in a foreign key because indexes on those columns must always include a prefix length.

• InnoDB does not currently support foreign keys for tables with user-defined partitioning. This includes both parent and child tables.

  This restriction does not apply for NDB tables that are partitioned by KEY or LINEAR KEY (the only user partitioning types supported by the NDB storage engine); these may have foreign key references or be the targets of such references.

• A table in a foreign key relationship cannot be altered to use another storage engine. To change the storage engine, you must drop any foreign key constraints first.

• A foreign key constraint cannot reference a virtual generated column.

For information about how the MySQL implementation of foreign key constraints differs from the SQL standard, see Section 1.6.2.3, “FOREIGN KEY Constraint Differences”.

When an UPDATE or 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:

• CASCADE: Delete or update the row from the parent table and automatically delete or update the matching rows in the child table. Both ON DELETE CASCADE and ON UPDATE CASCADE are supported. Between two tables, do not define several ON UPDATE CASCADE clauses that act on the same column in the parent table or in the child table.

  If a FOREIGN KEY clause is defined on both tables in a foreign key relationship, making both tables a parent and child, an ON UPDATE CASCADE or ON DELETE CASCADE subclause defined for one FOREIGN KEY clause must be defined for the other in order for cascading operations to succeed. If an ON UPDATE CASCADE or ON DELETE CASCADE subclause is only defined for one FOREIGN KEY clause, cascading operations fail with an error.

  Cascaded foreign key actions do not activate triggers.

• SET NULL: Delete or update the row from the parent table and set the foreign key column or columns in the child table to NULL. Both ON DELETE SET NULL and ON UPDATE SET NULL clauses are supported.

  If you specify a SET NULL action, make sure that you have not declared the columns in the child table as NOT NULL.

• RESTRICT: Rejects the delete or update operation for the parent table. Specifying RESTRICT (or NO ACTION) is the same as omitting the ON DELETE or ON UPDATE clause.

• NO ACTION: A keyword from standard SQL. In MySQL, equivalent to RESTRICT. The MySQL Server rejects the delete or update operation for the parent table if there is a related foreign key value in the referenced table. Some database systems have deferred checks, and NO ACTION is a deferred check. In MySQL, foreign key constraints are checked immediately, so NO ACTION is the same as RESTRICT.

• SET DEFAULT: This action is recognized by the MySQL parser, but both InnoDB and NDB reject table definitions containing ON DELETE SET DEFAULT or ON UPDATE SET DEFAULT clauses.

For storage engines that support foreign keys, MySQL rejects any INSERT or 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 NO ACTION.

As the default, an ON DELETE NO ACTION or ON UPDATE NO ACTION clause that is specified explicitly does not appear in SHOW CREATE TABLE output or in tables dumped with mysqldump. RESTRICT, which is an equivalent non-default keyword, appears in SHOW CREATE TABLE output and in tables dumped with mysqldump.

For NDB tables, ON UPDATE CASCADE is not supported where the reference is to the parent table's primary key.

As of NDB 8.0.16: For NDB tables, ON DELETE CASCADE is not supported where the child table contains one or more columns of any of the TEXT or 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.

Foreign Key Constraint Examples

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

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:

Adding Foreign Key Constraints

You can add a foreign key constraint to an existing table using the following ALTER TABLE syntax:

The foreign key can be self referential (referring to the same table). When you add a foreign key constraint to a table using 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 ALTER TABLE syntax:

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 SHOW CREATE TABLE:

Adding and dropping a foreign key in the same ALTER TABLE statement is supported for ALTER TABLE ... ALGORITHM=INPLACE. It is not supported for ALTER TABLE ... ALGORITHM=COPY.

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 NDB tables as it does for InnoDB tables.

The foreign_key_checks variable is dynamic and supports both global and session scopes. For information about using system variables, see Section 5.1.9, “Using System Variables”.

Disabling foreign key checking is useful when:

• Dropping a table that is referenced by a foreign key constraint. A referenced table can only be dropped after foreign_key_checks is disabled. When you drop a table, constraints defined on the table are also dropped.

• Reloading tables in different order than required by their foreign key relationships. For example, mysqldump produces correct definitions of tables in the dump file, including foreign key constraints for child tables. To make it easier to reload dump files for tables with foreign key relationships, mysqldump automatically includes a statement in the dump output that disables foreign_key_checks. This enables you to import the tables in any order in case the dump file contains tables that are not correctly ordered for foreign keys. Disabling foreign_key_checks also speeds up the import operation by avoiding foreign key checks.

• Executing LOAD DATA operations, to avoid foreign key checking.

• Performing an ALTER TABLE operation on a table that has a foreign key relationship.

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

• Recreating a table that was previously dropped returns an error if the table definition does not conform to the foreign key constraints that reference the table. The table must have the correct column names and types. It must also have indexes on the referenced keys. If these requirements are not satisfied, MySQL returns Error 1005 that refers to errno: 150 in the error message, which means that a foreign key constraint was not correctly formed.

• Altering a table returns an error (errno: 150) if a foreign key definition is incorrectly formed for the altered table.

• Dropping an index required by a foreign key constraint. The foreign key constraint must be removed before dropping the index.

• Creating a foreign key constraint where a column references a nonmatching column type.

Disabling foreign_key_checks has these additional implications:

• It is permitted to drop a database that contains tables with foreign keys that are referenced by tables outside the database.

• It is permitted to drop a table with foreign keys referenced by other tables.

• Enabling foreign_key_checks does not trigger a scan of table data, which means that rows added to a table while foreign_key_checks is disabled are not checked for consistency when foreign_key_checks is re-enabled.

MySQL extends metadata locks, as necessary, to tables that are related by a foreign key constraint. Extending metadata locks prevents conflicting DML and DDL operations from executing concurrently on related tables. This feature also enables updates to foreign key metadata when a parent table is modified. In earlier MySQL releases, foreign key metadata, which is owned by the child table, could not be updated safely.

If a table is locked explicitly with LOCK TABLES, any tables related by a foreign key constraint are opened and locked implicitly. For foreign key checks, a shared read-only lock (LOCK TABLES READ) is taken on related tables. For cascading updates, a shared-nothing write lock (LOCK TABLES WRITE) is taken on related tables that are involved in the operation.

Foreign Key Definitions and Metadata

To view a foreign key definition, use SHOW CREATE TABLE:

You can obtain information about foreign keys from the INFORMATION_SCHEMA.KEY_COLUMN_USAGE table. An example of a query against this table is shown here:

You can obtain information specific to InnoDB foreign keys from the INNODB_FOREIGN and INNODB_FOREIGN_COLS tables. Example queries are show here:

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 SHOW ENGINE INNODB STATUS output.

Warning

If a user has table-level privileges for all parent tables, ER_NO_REFERENCED_ROW_2 and ER_ROW_IS_REFERENCED_2 error messages for foreign key operations expose information about parent tables. If a user does not have table-level privileges for all parent tables, more generic error messages are displayed instead (ER_NO_REFERENCED_ROW and ER_ROW_IS_REFERENCED).

An exception is that, for stored programs defined to execute with DEFINER privileges, the user against which privileges are assessed is the user in the program DEFINER clause, not the invoking user. If that user has table-level parent table privileges, parent table information is still displayed. In this case, it is the responsibility of the stored program creator to hide the information by including appropriate condition handlers.

Page 2

13.1.20.6 CHECK Constraints

Prior to MySQL 8.0.16, CREATE TABLE permits only the following limited version of table CHECK constraint syntax, which is parsed and ignored:

As of MySQL 8.0.16, CREATE TABLE permits the core features of table and column CHECK constraints, for all storage engines. CREATE TABLE permits the following CHECK constraint syntax, for both table constraints and column constraints:

The optional symbol specifies a name for the constraint. If omitted, MySQL generates a name from the table name, a literal _chk_, and an ordinal number (1, 2, 3, ...). Constraint names have a maximum length of 64 characters. They are case-sensitive, but not accent-sensitive.

expr specifies the constraint condition as a boolean expression that must evaluate to TRUE or UNKNOWN (for NULL values) for each row of the table. If the condition evaluates to FALSE, it fails and a constraint violation occurs. The effect of a violation depends on the statement being executed, as described later in this section.

The optional enforcement clause indicates whether the constraint is enforced:

• If omitted or specified as ENFORCED, the constraint is created and enforced.

• If specified as NOT ENFORCED, the constraint is created but not enforced.

A CHECK constraint is specified as either a table constraint or column constraint:

• A table constraint does not appear within a column definition and can refer to any table column or columns. Forward references are permitted to columns appearing later in the table definition.

• A column constraint appears within a column definition and can refer only to that column.

Consider this table definition:

The definition includes table constraints and column constraints, in named and unnamed formats:

• The first constraint is a table constraint: It occurs outside any column definition, so it can (and does) refer to multiple table columns. This constraint contains forward references to columns not defined yet. No constraint name is specified, so MySQL generates a name.

• The next three constraints are column constraints: Each occurs within a column definition, and thus can refer only to the column being defined. One of the constraints is named explicitly. MySQL generates a name for each of the other two.

• The last two constraints are table constraints. One of them is named explicitly. MySQL generates a name for the other one.

As mentioned, MySQL generates a name for any CHECK constraint specified without one. To see the names generated for the preceding table definition, use SHOW CREATE TABLE:

The SQL standard specifies that all types of constraints (primary key, unique index, foreign key, check) belong to the same namespace. In MySQL, each constraint type has its own namespace per schema (database). Consequently, CHECK constraint names must be unique per schema; no two tables in the same schema can share a CHECK constraint name. (Exception: A TEMPORARY table hides a non-TEMPORARY table of the same name, so it can have the same CHECK constraint names as well.)

Beginning generated constraint names with the table name helps ensure schema uniqueness because table names also must be unique within the schema.

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

• Nongenerated and generated columns are permitted, except columns with the AUTO_INCREMENT attribute and columns in other tables.

• Literals, deterministic built-in functions, and operators are permitted. A function is deterministic if, given the same data in tables, multiple invocations produce the same result, independently of the connected user. Examples of functions that are nondeterministic and fail this definition: CONNECTION_ID(), CURRENT_USER(), NOW().

• Stored functions and loadable functions are not permitted.

• Stored procedure and function parameters are not permitted.

• Variables (system variables, user-defined variables, and stored program local variables) are not permitted.

• Subqueries are not permitted.

Foreign key referential actions (ON UPDATE, ON DELETE) are prohibited on columns used in CHECK constraints. Likewise, CHECK constraints are prohibited on columns used in foreign key referential actions.

CHECK constraints are evaluated for INSERT, UPDATE, REPLACE, LOAD DATA, and LOAD XML statements and an error occurs if a constraint evaluates to FALSE. If an error occurs, handling of changes already applied differs for transactional and nontransactional storage engines, and also depends on whether strict SQL mode is in effect, as described in Strict SQL Mode.

CHECK constraints are evaluated for INSERT IGNORE, UPDATE IGNORE, LOAD DATA ... IGNORE, and LOAD XML ... IGNORE statements and a warning occurs if a constraint evaluates to FALSE. The insert or update for any offending row is skipped.

If the constraint 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 Section 12.3, “Type Conversion in Expression Evaluation”. If type conversion fails or results in a loss of precision, an error occurs.

Note

Constraint expression evaluation uses the SQL mode in effect at evaluation time. 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.

The INFORMATION_SCHEMA.CHECK_CONSTRAINTS table provides information about CHECK constraints defined on tables. See Section 26.3.5, “The INFORMATION_SCHEMA CHECK_CONSTRAINTS Table”.

Page 3

13.1.20.7 Silent Column Specification Changes

In some cases, MySQL silently changes column specifications from those given in a CREATE TABLE or ALTER TABLE statement. These might be changes to a data type, to attributes associated with a data type, or to an index specification.

All changes are subject to the internal row-size limit of 65,535 bytes, which may cause some attempts at data type changes to fail. See Section 8.4.7, “Limits on Table Column Count and Row Size”.

• Columns that are part of a PRIMARY KEY are made NOT NULL even if not declared that way.

• Trailing spaces are automatically deleted from ENUM and SET member values when the table is created.

• MySQL maps certain data types used by other SQL database vendors to MySQL types. See Section 11.9, “Using Data Types from Other Database Engines”.

• If you include a USING clause to specify an index type that is not permitted for a given storage engine, but there is another index type available that the engine can use without affecting query results, the engine uses the available type.

• If strict SQL mode is not enabled, a VARCHAR column with a length specification greater than 65535 is converted to TEXT, and a VARBINARY column with a length specification greater than 65535 is converted to BLOB. Otherwise, an error occurs in either of these cases.

• Specifying the CHARACTER SET binary attribute for a character data type causes the column to be created as the corresponding binary data type: CHAR becomes BINARY, VARCHAR becomes VARBINARY, and TEXT becomes BLOB. For the ENUM and SET data types, this does not occur; they are created as declared. Suppose that you specify a table using this definition:

  CREATE TABLE t ( c1 VARCHAR(10) CHARACTER SET binary, c2 TEXT CHARACTER SET binary, c3 ENUM('a','b','c') CHARACTER SET binary );

  The resulting table has this definition:

  CREATE TABLE t ( c1 VARBINARY(10), c2 BLOB, c3 ENUM('a','b','c') CHARACTER SET binary );

To see whether MySQL used a data type other than the one you specified, issue a DESCRIBE or SHOW CREATE TABLE statement after creating or altering the table.

Certain other data type changes can occur if you compress a table using myisampack. See Section 16.2.3.3, “Compressed Table Characteristics”.

Page 4

13.1.20.8 CREATE TABLE and Generated Columns

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 also supported by the NDB storage engine.

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

Selecting from the table yields this result:

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:

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:

• VIRTUAL: Column values are not stored, but are evaluated when rows are read, immediately after any BEFORE triggers. A virtual column takes no storage.

  InnoDB supports secondary indexes on virtual columns. See Section 13.1.20.9, “Secondary Indexes and Generated Columns”.

• STORED: Column values are evaluated and stored when rows are inserted or updated. A stored column does require storage space and can be indexed.

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.

• Literals, deterministic built-in functions, and operators are permitted. A function is deterministic if, given the same data in tables, multiple invocations produce the same result, independently of the connected user. Examples of functions that are nondeterministic and fail this definition: CONNECTION_ID(), CURRENT_USER(), NOW().

• Stored functions and loadable functions are not permitted.

• Stored procedure and function parameters are not permitted.

• Variables (system variables, user-defined variables, and stored program local variables) are not permitted.

• Subqueries are not permitted.

• A generated column definition can refer to other generated columns, but only those occurring earlier in the table definition. A generated column definition can refer to any base (nongenerated) column in the table whether its definition occurs earlier or later.

• The AUTO_INCREMENT attribute cannot be used in a generated column definition.

• An AUTO_INCREMENT column cannot be used as a base column in a generated column definition.

• If expression evaluation causes truncation or provides incorrect input to a function, the CREATE TABLE statement terminates with an error and the DDL operation is rejected.

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 Section 12.3, “Type Conversion in Expression Evaluation”.

If a generated column uses the TIMESTAMP data type, the setting for explicit_defaults_for_timestamp is ignored. In such cases, if this variable is disabled then NULL is not converted to CURRENT_TIMESTAMP. In MySQL 8.0.22 and later, if the column is also declared as NOT NULL, attempting to insert NULL is explicitly rejected with ER_BAD_NULL_ERROR.

Note

Expression evaluation uses the SQL mode in effect at evaluation time. 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 CREATE TABLE ... LIKE, the destination table preserves generated column information from the original table.

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

Partitioning by generated columns is permitted. See 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 INSERT, REPLACE, and 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:

• Virtual generated columns can be used as a way to simplify and unify queries. A complicated condition can be defined as a generated column and referred to from multiple queries on the table to ensure that all of them use exactly the same condition.

• Stored generated columns can be used as a materialized cache for complicated conditions that are costly to calculate on the fly.

• Generated columns can simulate functional indexes: Use a generated column to define a functional expression and index it. This can be useful for working with columns of types that cannot be indexed directly, such as JSON columns; see Indexing a Generated Column to Provide a JSON Column Index, for a detailed example.

  For stored generated columns, the disadvantage of this approach is that values are stored twice; once as the value of the generated column and once in the index.

• If a generated column is indexed, the optimizer recognizes query expressions that match the column definition and uses indexes from the column as appropriate during query execution, even if a query does not refer to the column directly by name. For details, see Section 8.3.11, “Optimizer Use of Generated Column Indexes”.

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:

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:

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

Page 5

13.1.20.9 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 INSERT and 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.

Indexing a Generated Column to Provide a JSON Column Index

As noted elsewhere, 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:

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

When you use EXPLAIN on a 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 SHOW WARNINGS immediately following this EXPLAIN statement:

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.

In MySQL 8.0.21 and later, it is also possible to create an index on a JSON column using the JSON_VALUE() function with an expression that can be used to optimize queries employing the expression. See the description of that function for more information and examples.

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. NDB handles a JSON column value internally as a 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 NDB 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:

We can populate this table using the following INSERT statement:

Now NDB can use index i, as shown here:

You should keep in mind that a stored generated column, as well as any index on such a column, uses DataMemory.

Page 6

13.1.20.10 Invisible Columns

MySQL supports invisible columns as of MySQL 8.0.23. An invisible column is normally hidden to queries, but can be accessed if explicitly referenced. Prior to MySQL 8.0.23, all columns are visible.

As an illustration of when invisible columns may be useful, suppose that an application uses SELECT * queries to access a table, and must continue to work without modification even if the table is altered to add a new column that the application does not expect to be there. In a SELECT * query, the * evaluates to all table columns, except those that are invisible, so the solution is to add the new column as an invisible column. The column remains “hidden” from SELECT * queries, and the application continues to work as previously. A newer version of the application can refer to the invisible column if necessary by explicitly referencing it.

The following sections detail how MySQL treats invisible columns.

DDL Statements and Invisible Columns

Columns are visible by default. To explicitly specify visibility for a new column, use a VISIBLE or INVISIBLE keyword as part of the column definition for CREATE TABLE or ALTER TABLE:

To alter the visibility of an existing column, use a VISIBLE or INVISIBLE keyword with one of the ALTER TABLE column-modification clauses:

A table must have at least one visible column. Attempting to make all columns invisible produces an error.

Invisible columns support the usual column attributes: NULL, NOT NULL, AUTO_INCREMENT, and so forth.

Generated columns can be invisible.

Index definitions can name invisible columns, including definitions for PRIMARY KEY and UNIQUE indexes. Although a table must have at least one visible column, an index definition need not have any visible columns.

An invisible column dropped from a table is dropped in the usual way from any index definition that names the column.

Foreign key constraints can be defined on invisible columns, and foreign key constraints can reference invisible columns.

CHECK constraints can be defined on invisible columns. For new or modified rows, violation of a CHECK constraint on an invisible column produces an error.

CREATE TABLE ... LIKE includes invisible columns, and they are invisible in the new table.

CREATE TABLE ... SELECT does not include invisible columns, unless they are explicitly referenced in the SELECT part. However, even if explicitly referenced, a column that is invisible in the existing table is visible in the new table:

If invisibility should be preserved, provide a definition for the invisible column in the CREATE TABLE part of the CREATE TABLE ... SELECT statement:

Views can refer to invisible columns by explicitly referencing them in the SELECT statement that defines the view. Changing a column's visibility subsequent to defining a view that references the column does not change view behavior.

DML Statements and Invisible Columns

For SELECT statements, an invisible column is not part of the result set unless explicitly referenced in the select list. In a select list, the * and tbl_name.* shorthands do not include invisible columns. Natural joins do not include invisible columns.

Consider the following statement sequence:

The first SELECT does not reference the invisible column col2 in the select list (because * does not include invisible columns), so col2 does not appear in the statement result. The second SELECT explicitly references col2, so the column appears in the result.

The statement TABLE t1 produces the same output as the first SELECT statement. Since there is no way to specify columns in a TABLE statement, TABLE never displays invisible columns.

For statements that create new rows, an invisible column is assigned its implicit default value unless explicitly referenced and assigned a value. For information about implicit defaults, see Implicit Default Handling.

For INSERT (and REPLACE, for non-replaced rows), implicit default assignment occurs with a missing column list, an empty column list, or a nonempty column list that does not include the invisible column:

For the first two INSERT statements, the VALUES() list must provide a value for each visible column and no invisible column. For the third INSERT statement, the VALUES() list must provide the same number of values as the number of named columns; the same is true when you use VALUES ROW() rather than VALUES().

For LOAD DATA and LOAD XML, implicit default assignment occurs with a missing column list or a nonempty column list that does not include the invisible column. Input rows should not include a value for the invisible column.

To assign a value other than the implicit default for the preceding statements, explicitly name the invisible column in the column list and provide a value for it.

INSERT INTO ... SELECT * and REPLACE INTO ... SELECT * do not include invisible columns because * does not include invisible columns. Implicit default assignment occurs as described previously.

For statements that insert or ignore new rows, or that replace or modify existing rows, based on values in a PRIMARY KEY or UNIQUE index, MySQL treats invisible columns the same as visible columns: Invisible columns participate in key value comparisons. Specifically, if a new row has the same value as an existing row for a unique key value, these behaviors occur whether the index columns are visible or invisible:

To update invisible columns for UPDATE statements, name them and assign a value, just as for visible columns.

Invisible Column Metadata

Information about whether a column is visible or invisible is available from the EXTRA column of the INFORMATION_SCHEMA.COLUMNS table or SHOW COLUMNS output. For example:

Columns are visible by default, so in that case, EXTRA displays no visibility information. For invisible columns, EXTRA displays INVISIBLE.

SHOW CREATE TABLE displays invisible columns in the table definition, with the INVISIBLE keyword in a version-specific comment:

mysqldump and mysqlpump use SHOW CREATE TABLE, so they include invisible columns in dumped table definitions. They also include invisible column values in dumped data.

Reloading a dump file into an older version of MySQL that does not support invisible columns causes the version-specific comment to be ignored, which creates any invisible columns as visible.

The Binary Log and Invisible Columns

MySQL treats invisible columns as follows with respect to events in the binary log:

• Table-creation events include the INVISIBLE attribute for invisible columns.

• Invisible columns are treated like visible columns in row events. They are included if needed according to the binlog_row_image system variable setting.

• When row events are applied, invisible columns are treated like visible columns in row events. In particular, the algorithm and index to use are chosen according to the slave_rows_search_algorithms system variable setting.

• Invisible columns are treated like visible columns when computing writesets. In particular, writesets include indexes defined on invisible columns.

• The mysqlbinlog command includes visibility in column metadata.

Page 7

13.1.20.11 Generated Invisible Primary Keys

Beginning with MySQL 8.0.30, MySQL supports generated invisible primary keys when running in GIPK mode. When running in this mode, for any InnoDB table that is created without an explicit primary key, the MySQL server automatically adds a generated invisible primary key (GIPK) to the table. This section describes how to enable and disable GIPK mode, and the behavior of CREATE TABLE and of the generated primary key when this mode is enabled.

GIPK mode is controlled by the sql_generate_invisible_primary_key server system variable. By default, the value of this variable is OFF, which means that GIPK mode is disabled; to enable GIPK mode, set the variable to ON. To illustrate how table creation is affected by GIPK mode, we begin by creating two identical tables, neither having a primary key, the first (table auto_0) with GIPK mode disabled, and the second (auto_1) after enabling it, as shown here:

The setting for sql_generate_invisible_primary_key is not replicated, and is ignored by replication applier threads. This means that the replica does not generate a primary key for any replicated table which was not created with a primary key on the source.

Use SHOW CREATE TABLE to see the difference in how the tables were actually created, like this:

Since auto_1 had no primary key specified by the CREATE TABLE statement used to create it, GIPK mode causes MySQL to add to this table the invisible column my_row_id and a primary key on that column. Since GIPK mode was disabled when auto_0 was created, no such addition was performed on that table.

When a primary key is added to a table by the server in GIPK mode, the column and key name is always my_row_id. For this reason, when GIPK mode is enabled, you cannot create a table having a column named my_row_id unless the table creation statement also specifies an explicit primary key. (You are not required to name the column or key my_row_id in such cases.)

my_row_id is an invisible column, which means it is not shown in the output of SELECT * or TABLE; the column must be selected explicitly, by name (see Section 13.1.20.10, “Invisible Columns”).

When GIPK mode is in effect, the generated primary key cannot be altered other than to switch it between VISIBLE and INVISIBLE. To make the generated invisible primary key on auto_1 visible, execute this ALTER TABLE statement:

To make this generated primary key invisible again, issue ALTER TABLE auto_1 ALTER COLUMN my_row_id SET INVISIBLE.

A primary key generated by GIPK mode is always invisible by default.

Whenever GIPK mode is in effect, you cannot drop a generated primary key if either of the following 2 conditions would result:

• The table is left with no primary key.

• The primary key is dropped, but not the primary key column.

GIPK mode applies only to tables using the InnoDB storage engine. It has no effect on tables using other MySQL storage engines. You can use an ALTER TABLE statement to change the storage engine used by a table that has a generated invisible primary key; the primary key and column remain in place, but the table and key no longer receive any special treatment.

By default, GIPKs are shown in the output of SHOW CREATE TABLE, SHOW COLUMNS, and SHOW INDEX, and are visible in the Information Schema COLUMNS and STATISTICS tables. You can cause generated invisible primary keys to be hidden instead in such cases by setting the show_gipk_in_create_table_and_information_schema system variable to OFF. By default, this variable is ON, as shown here:

As can be seen from the following query against the COLUMNS table, my_row_id is visible among the columns of auto_1:

After show_gipk_in_create_table_and_information_schema is set to OFF, my_row_id can no longer be seen in the COLUMNS table, as shown here:

GIPK mode supports row-based replication of CREATE TABLE ... SELECT; the information written to the binary log for this statement in such cases includes the GIPK definition, and thus is replicated correctly. Statement-based replication of CREATE TABLE ... SELECT is not supported in GIPK mode.

When creating or importing backups of installations in which GIPK mode is in use, it is possible to exclude generated invisible PK columns and values. The --skip-generated-invisible-primary-key option for mysqldump causes GIPK information to be excluded in the program's output. If you are importing a dump file that contains GIPK keys and values, you can also use --skip-generated-invisible-primary-key with mysqlpump to cause these to be suppressed (and thus not imported).

Page 8

13.1.20.12 Setting NDB Comment Options

• NDB_COLUMN Options

• NDB_TABLE Options

It is possible to set a number of options specific to NDB Cluster in the table comment or column comments of an NDB 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 BLOB, MEDIUMBLOB, LONGBLOB, TEXT, MEDIUMTEXT, LONGTEXT, and JSON columns. Beginning with NDB 8.0.30, a column comment can also be used to control the inline size of a blob column. NDB_COLUMN comments do not support TINYBLOB or TINYTEXT columns, since these have an inline part (only) of fixed size, and no separate parts to store elsewhere.

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 ALTER TABLE statement can also be used to specify an NDB_COLUMN option. Beginning with version 8.0.30, NDB supports two column comment options BLOB_INLINE_SIZE and MAX_BLOB_PART_SIZE. (Prior to NDB 8.0.30, only MAX_BLOB_PART_SIZE is supported.) Syntax for this option is shown here:

BLOB_INLINE_SIZE specifies the number of bytes to be stored inline by the column; its expected value is an integer in the range 1 - 29980, inclusive. Setting a value greater than 29980 raises an error; setting a value less than 1 is allowed, but causes the default inline size for the column type to be used.

You should be aware that the maximum value for this option is actually the maximum number of bytes that can be stored in one row of an NDB table; every column in the row contributes to this total.

You should also keep in mind, especially when working with TEXT columns, that the value set by MAX_BLOB_PART_SIZE or BLOB_INLINE_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, first create a table with two BLOB columns, one (b1) with no extra options, and another (b2) with a setting for BLOB_INLINE_SIZE, as shown here:

You can see the BLOB_INLINE_SIZE settings for the BLOB columns by querying the ndbinfo.blobs table, like this:

You can also check the output from the ndb_desc utility, as shown here, with the relevant lines displayed using emphasized text:

For MAX_BLOB_PART_SIZE, 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 TEXT or 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). Unlike BLOB_INLINE_SIZE, MAX_BLOB_PART_SIZE has no effect on JSON columns.

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

From the system shell, run the ndb_desc utility to obtain information about the table just created, as shown in this example:

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 also query the ndbinfo.blobs table to see this, as shown here:

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 SHOW CREATE TABLE:

The output of ndb_desc shows that the blob part sizes of the columns have been changed as expected:

You can also see the change by running the query against ndbinfo.blobs again:

It is possible to set both BLOB_INLINE_SIZE and MAX_BLOB_PART_SIZE for a blob column, as shown in this CREATE TABLE statement:

Querying the blobs table shows us that the statement worked as expected:

You can also verify that the statement worked by checking the output of ndb_desc.

Changing a column's blob part size must be done using a copying ALTER TABLE; this operation cannot be performed online (see Section 23.6.12, “Online Operations with ALTER TABLE in NDB Cluster”).

For more information about how NDB stores columns of blob types, see String Type Storage Requirements.

NDB_TABLE Options

In MySQL NDB Cluster, the table comment in a CREATE TABLE or 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:

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: Using 1 corresponds to having ndb_table_no_logging enabled, but has no actual effect. Provided as a placeholder, mostly for completeness of ALTER TABLE statements.

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. Beginning with NDB 8.0.19, 1 is the default for READ_BACKUP, and the default for ndb_read_backup is ON (previously, read from any replica was disabled by default).

You can set READ_BACKUP for an existing table online, using an ALTER TABLE statement similar to one of those shown here:

For more information about the ALGORITHM option for ALTER TABLE, see Section 23.6.12, “Online Operations with ALTER TABLE in NDB Cluster”.

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 setting used if READ_BACKUP is set to 1.

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

   These partitions can be primary or backup partitions.

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

   These partitions can be primary or backup partitions.

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

   These partitions can be primary or backup partitions.

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

   These partitions can be primary or backup partitions.

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 but continues to be supported for backward compatibility; it is subject to removal in a future release of MySQL NDB Cluster. (Bug #81759, Bug #23544301)

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

The comment is displayed as part of the output of SHOW CREATE TABLE. The text of the comment is also available from querying the MySQL Information Schema TABLES table, as in this example:

This comment syntax is also supported with ALTER TABLE statements for NDB tables, as shown here:

Beginning with NDB 8.0.21, 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:

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

Prior to NDB 8.0.21, 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 necessary 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 ndb_desc. 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.

Page 9

13.1.21 CREATE TABLESPACE Statement

This statement is used to create a tablespace. The precise syntax and semantics depend on the storage engine used. In standard MySQL releases, this is always an InnoDB tablespace. MySQL NDB Cluster also supports tablespaces using the NDB storage engine.

CREATE TABLESPACE syntax is used to create general tablespaces or undo tablespaces. The UNDO keyword, introduced in MySQL 8.0.14, must be specified to create an undo tablespace.

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, use CREATE TABLE tbl_name ... TABLESPACE [=] tablespace_name or ALTER TABLE tbl_name TABLESPACE [=] tablespace_name to add tables to the tablespace. For more information, see Section 15.6.3.3, “General Tablespaces”.

Undo tablespaces contain undo logs. Undo tablespaces can be created in a chosen location by specifying a fully qualified data file path. For more information, see Section 15.6.3.4, “Undo 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 Section 23.6.11, “NDB Cluster Disk Data Tables”). One data file is created and added to the tablespace using this statement. Additional data files may be added to the tablespace by using the ALTER TABLESPACE statement (see Section 13.1.10, “ALTER TABLESPACE Statement”).

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 CREATE LOGFILE GROUP (see Section 13.1.16, “CREATE LOGFILE GROUP Statement”). 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:

• EXTENT_SIZE is rounded up to the nearest whole multiple of 32K.

• INITIAL_SIZE is rounded down to the nearest whole multiple of 32K; this result is rounded up to the nearest whole multiple of EXTENT_SIZE (after any rounding).

Note

NDB reserves 4% of a tablespace for data node restart operations. This reserved space cannot be used for data storage.

The rounding just described is done explicitly, and a warning is issued by the MySQL Server when any such rounding is performed. The rounded values are also used by the NDB kernel for calculating INFORMATION_SCHEMA.FILES column values and other purposes. However, to avoid an unexpected result, we suggest that you always use whole multiples of 32K in specifying these options.

When CREATE TABLESPACE is used with ENGINE [=] NDB, a tablespace and associated data file are created on each Cluster data node. You can verify that the data files were created and obtain information about them by querying the INFORMATION_SCHEMA.FILES table. (See the example later in this section.)

(See Section 26.3.15, “The INFORMATION_SCHEMA FILES Table”.)

Options

• ADD DATAFILE: Defines the name of a tablespace data file. This option is always required when creating an NDB tablespace; for InnoDB in MySQL 8.0.14 and later, it is required only when creating an undo tablespace. The file_name, including any specified path, must be quoted with single or double quotation marks. File names (not counting the file extension) and directory names must be at least one byte in length. Zero length file names and directory names are not supported.

  Because there are considerable differences in how InnoDB and NDB treat data files, the two storage engines are covered separately in the discussion that follows.

  InnoDB data files.  An InnoDB tablespace supports only a single data file, whose name must include a .ibd extension.

  To place an InnoDB general tablespace data file in a location outside of the data directory, include a fully qualified path or a path relative to the data directory. Only a fully qualified path is permitted for undo tablespaces. If you do not specify a path, a general tablespace is created in the data directory. An undo tablespace created without specifying a path is created in the directory defined by the innodb_undo_directory variable. If the innodb_undo_directory variable is undefined, undo tablespaces are created in the data directory.

  To avoid conflicts with implicitly created file-per-table tablespaces, creating an InnoDB general tablespace in a subdirectory under the data directory is not supported. When creating a general tablespace or undo tablespace outside of the data directory, the directory must exist and must be known to InnoDB prior to creating the tablespace. To make a directory known to InnoDB, add it to the innodb_directories value or to one of the variables whose values are appended to the innodb_directories value. innodb_directories is a read-only variable. Configuring it requires restarting the server.

  If the ADD DATAFILE clause is not specified when creating an InnoDB tablespace, a tablespace data file with a unique file name is created implicitly. The unique file name is a 128 bit UUID formatted into five groups of hexadecimal numbers separated by dashes (aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee). A file extension is added if required by the storage engine. An .ibd file extension is added for InnoDB general tablespace data files. In a replication environment, the data file name created on the replication source server is not the same as the data file name created on the replica.

  As of MySQL 8.0.17, the ADD DATAFILE clause does not permit circular directory references when creating an InnoDB tablespace. For example, the circular directory reference (/../) in the following statement is not permitted:

  CREATE TABLESPACE ts1 ADD DATAFILE ts1.ibd 'any_directory/../ts1.ibd';

  An exception to this restriction exists on Linux, where a circular directory reference is permitted if the preceding directory is a symbolic link. For example, the data file path in the example above is permitted if any_directory is a symbolic link. (It is still permitted for data file paths to begin with '../'.)

  NDB data files.  An NDB tablespace supports multiple data files which can have any legal file names; more data files can be added to an NDB Cluster tablespace following its creation by using an ALTER TABLESPACE statement.

  An NDB tablespace data file is created by default in the data node file system directory—that is, the directory named ndb_nodeid_fs/TS under the data node's data directory (DataDir), where nodeid is the data node's NodeId. To place the data file in a location other than the default, include an absolute directory path or a path relative to the default location. If the directory specified does not exist, NDB attempts to create it; the system user account under which the data node process is running must have the appropriate permissions to do so.

  When determining the path used for a data file, NDB does not expand the ~ (tilde) character.

  When multiple data nodes are run on the same physical host, the following considerations apply:

  • You cannot specify an absolute path when creating a data file.

  • It is not possible to create tablespace data files outside the data node file system directory, unless each data node has a separate data directory.

  • If each data node has its own data directory, data files can be created anywhere within this directory.

  • If each data node has its own data directory, it may also be possible to create a data file outside the node's data directory using a relative path, as long as this path resolves to a unique location on the host file system for each data node running on that host.

• FILE_BLOCK_SIZE: This option—which is specific to InnoDB general tablespaces, and is ignored by NDB—defines the block size for the tablespace data file. Values can be specified in bytes or kilobytes. For example, an 8 kilobyte file block size can be specified as 8192 or 8K. If you do not specify this option, FILE_BLOCK_SIZE defaults to the innodb_page_size value. FILE_BLOCK_SIZE is required when you intend to use the tablespace for storing compressed InnoDB tables (ROW_FORMAT=COMPRESSED). In this case, you must define the tablespace FILE_BLOCK_SIZE when creating the tablespace.

  If FILE_BLOCK_SIZE is equal the innodb_page_size value, the tablespace can contain only tables having an uncompressed row format (COMPACT, REDUNDANT, and DYNAMIC). Tables with a COMPRESSED row format have a different physical page size than uncompressed tables. Therefore, compressed tables cannot coexist in the same tablespace as uncompressed tables.

  For a general tablespace to contain compressed tables, FILE_BLOCK_SIZE must be specified, and the FILE_BLOCK_SIZE value must be a valid compressed page size in relation to the innodb_page_size value. Also, the physical page size of the compressed table (KEY_BLOCK_SIZE) must be equal to FILE_BLOCK_SIZE/1024. For example, if innodb_page_size=16K, and FILE_BLOCK_SIZE=8K, the KEY_BLOCK_SIZE of the table must be 8. For more information, see Section 15.6.3.3, “General Tablespaces”.

• USE LOGFILE GROUP: Required for NDB, this is the name of a log file group previously created using CREATE LOGFILE GROUP. Not supported for InnoDB, where it fails with an error.

• EXTENT_SIZE: This option is specific to NDB, and is not supported by InnoDB, where it fails with an error. EXTENT_SIZE sets the size, in bytes, of the extents used by any files belonging to the tablespace. The default value is 1M. The minimum size is 32K, and theoretical maximum is 2G, although the practical maximum size depends on a number of factors. In most cases, changing the extent size does not have any measurable effect on performance, and the default value is recommended for all but the most unusual situations.

  An extent is a unit of disk space allocation. One extent is filled with as much data as that extent can contain before another extent is used. In theory, up to 65,535 (64K) extents may used per data file; however, the recommended maximum is 32,768 (32K). The recommended maximum size for a single data file is 32G—that is, 32K extents × 1 MB per extent. In addition, once an extent is allocated to a given partition, it cannot be used to store data from a different partition; an extent cannot store data from more than one partition. This means, for example that a tablespace having a single datafile whose INITIAL_SIZE (described in the following item) is 256 MB and whose EXTENT_SIZE is 128M has just two extents, and so can be used to store data from at most two different disk data table partitions.

  You can see how many extents remain free in a given data file by querying the INFORMATION_SCHEMA.FILES table, and so derive an estimate for how much space remains free in the file. For further discussion and examples, see Section 26.3.15, “The INFORMATION_SCHEMA FILES Table”.

• INITIAL_SIZE: This option is specific to NDB, and is not supported by InnoDB, where it fails with an error.

  The INITIAL_SIZE parameter sets the total size in bytes of the data file that was specific using ADD DATATFILE. Once this file has been created, its size cannot be changed; however, you can add more data files to the tablespace using ALTER TABLESPACE ... ADD DATAFILE.

  INITIAL_SIZE is optional; its default value is 134217728 (128 MB).

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

• AUTOEXTEND_SIZE: Ignored by MySQL prior to MySQL 8.0.23; From MySQL 8.0.23, defines the amount by which InnoDB extends the size of the tablespace when it becomes full. The setting must be a multiple of 4MB. The default setting is 0, which causes the tablespace to be extended according to the implicit default behavior. For more information, see Section 15.6.3.9, “Tablespace AUTOEXTEND_SIZE Configuration”.

  Has no effect in any release of MySQL NDB Cluster 8.0, regardless of the storage engine used.

• MAX_SIZE: Currently ignored by MySQL; reserved for possible future use. Has no effect in any release of MySQL 8.0 or MySQL NDB Cluster 8.0, regardless of the storage engine used.

• NODEGROUP: Currently ignored by MySQL; reserved for possible future use. Has no effect in any release of MySQL 8.0 or MySQL NDB Cluster 8.0, regardless of the storage engine used.

• WAIT: Currently ignored by MySQL; reserved for possible future use. Has no effect in any release of MySQL 8.0 or MySQL NDB Cluster 8.0, regardless of the storage engine used.

• COMMENT: Currently ignored by MySQL; reserved for possible future use. Has no effect in any release of MySQL 8.0 or MySQL NDB Cluster 8.0, regardless of the storage engine used.

• The ENCRYPTION clause enables or disables page-level data encryption for an InnoDB general tablespace. Encryption support for general tablespaces was introduced in MySQL 8.0.13.

  As of MySQL 8.0.16, if the ENCRYPTION clause is not specified, the default_table_encryption setting controls whether encryption is enabled. The ENCRYPTION clause overrides the default_table_encryption setting. However, if the table_encryption_privilege_check variable is enabled, the TABLE_ENCRYPTION_ADMIN privilege is required to use an ENCRYPTION clause setting that differs from the default_table_encryption setting.

  A keyring plugin must be installed and configured before an encryption-enabled tablespace can be created.

  When a general tablespace is encrypted, all tables residing in the tablespace are encrypted. Likewise, a table created in an encrypted tablespace is encrypted.

  For more information, see Section 15.13, “InnoDB Data-at-Rest Encryption”

• ENGINE: Defines the storage engine which uses the tablespace, where engine_name is the name of the storage engine. Currently, only the InnoDB storage engine is supported by standard MySQL 8.0 releases. MySQL NDB Cluster supports both NDB and InnoDB tablespaces. The value of the default_storage_engine system variable is used for ENGINE if the option is not specified.

• The ENGINE_ATTRIBUTE option (available as of MySQL 8.0.21) is used to specify tablespace attributes for primary storage engines. The option is reserved for future use.

  Permitted values are a string literal containing a valid JSON document or an empty string (''). Invalid JSON is rejected.

  CREATE TABLESPACE ts1 ENGINE_ATTRIBUTE='{"key":"value"}';

  ENGINE_ATTRIBUTE values can be repeated without error. In this case, the last specified value is used.

  ENGINE_ATTRIBUTE values are not checked by the server, nor are they cleared when the table's storage engine is changed.

• For the rules covering the naming of MySQL tablespaces, see Section 9.2, “Schema Object Names”. In addition to these rules, the slash character (“/”) is not permitted, nor can you use names beginning with innodb_, as this prefix is reserved for system use.

• Creation of temporary general tablespaces is not supported.

• General tablespaces do not support temporary tables.

• The TABLESPACE option may be used with CREATE TABLE or ALTER TABLE to assign an InnoDB table partition or subpartition to a file-per-table tablespace. All partitions must belong to the same storage engine. Assigning table partitions to shared InnoDB tablespaces is not supported. Shared tablespaces include the InnoDB system tablespace and general tablespaces.

• General tablespaces support the addition of tables of any row format using CREATE TABLE ... TABLESPACE. innodb_file_per_table does not need to be enabled.

• innodb_strict_mode is not applicable to general tablespaces. Tablespace management rules are strictly enforced independently of innodb_strict_mode. If CREATE TABLESPACE parameters are incorrect or incompatible, the operation fails regardless of the innodb_strict_mode setting. When a table is added to a general tablespace using CREATE TABLE ... TABLESPACE or ALTER TABLE ... TABLESPACE, innodb_strict_mode is ignored but the statement is evaluated as if innodb_strict_mode is enabled.

• Use DROP TABLESPACE to remove a tablespace. All tables must be dropped from a tablespace using DROP TABLE prior to dropping the tablespace. Before dropping an NDB Cluster tablespace you must also remove all its data files using one or more ALTER TABLESPACE ... DROP DATATFILE statements. See Section 23.6.11.1, “NDB Cluster Disk Data Objects”.

• All parts of an InnoDB table added to an InnoDB general tablespace reside in the general tablespace, including indexes and BLOB pages.

  For an NDB table assigned to a tablespace, only those columns which are not indexed are stored on disk, and actually use the tablespace data files. Indexes and indexed columns for all NDB tables are always kept in memory.

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

• A general tablespace is not associated with any database or schema.

• ALTER TABLE ... DISCARD TABLESPACE and ALTER TABLE ...IMPORT TABLESPACE are not supported for tables that belong to a general tablespace.

• The server uses tablespace-level metadata locking for DDL that references general tablespaces. By comparison, the server uses table-level metadata locking for DDL that references file-per-table tablespaces.

• A generated or existing tablespace cannot be changed to a general tablespace.

• There is no conflict between general tablespace names and file-per-table tablespace names. The “/” character, which is present in file-per-table tablespace names, is not permitted in general tablespace names.

• mysqldump and mysqlpump do not dump InnoDB CREATE TABLESPACE statements.

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

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.

This example demonstrates creating a general tablespace without specifying the ADD DATAFILE clause, which is optional as of MySQL 8.0.14.

This example demonstrates creating an undo tablespace.

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 CREATE LOGFILE GROUP statement shown here:

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

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

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.FILES table, as shown here:

For additional information and examples, see Section 23.6.11.1, “NDB Cluster Disk Data Objects”.

Page 10

13.1.22 CREATE TRIGGER Statement

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.

IF NOT EXISTS prevents an error from occurring if a trigger having the same name, on the same table, exists in the same schema. This option is supported with CREATE TRIGGER beginning with MySQL 8.0.29.

This section describes CREATE TRIGGER syntax. For additional discussion, see Section 25.3.1, “Trigger Syntax and Examples”.

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 Section 25.6, “Stored Object Access Control”. If binary logging is enabled, CREATE TRIGGER might require the SUPER privilege, as discussed in Section 25.7, “Stored Program Binary 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:

• INSERT: The trigger activates whenever a new row is inserted into the table (for example, through INSERT, LOAD DATA, and REPLACE statements).

• UPDATE: The trigger activates whenever a row is modified (for example, through UPDATE statements).

• DELETE: The trigger activates whenever a row is deleted from the table (for example, through DELETE and REPLACE statements). DROP TABLE and TRUNCATE TABLE statements on the table do not activate this trigger, because they do not use DELETE. Dropping a partition does not activate DELETE triggers, either.

The trigger_event does not represent a literal type of SQL statement that activates the trigger so much as it represents a type of table operation. For example, an INSERT trigger activates not only for INSERT statements but also LOAD DATA statements because both statements insert rows into a table.

A potentially confusing example of this is the INSERT INTO ... ON DUPLICATE KEY UPDATE ... syntax: a BEFORE INSERT trigger 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 BEGIN ... END compound statement construct. This also enables you to use the same statements that are permitted within stored routines. See Section 13.6.1, “BEGIN ... END Compound Statement”. Some statements are not permitted in triggers; see Section 25.8, “Restrictions on Stored Programs”.

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 Section 13.1.20.8, “CREATE TABLE and 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 Section 25.6, “Stored Object Access Control”. 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 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:

• At CREATE TRIGGER time, the user who issues the statement must have the TRIGGER privilege.

• At trigger activation time, privileges are checked against the DEFINER user. This user must have these privileges:

  • The TRIGGER privilege for the subject table.

  • The SELECT privilege for the subject table if references to table columns occur using OLD.col_name or NEW.col_name in the trigger body.

  • The UPDATE privilege for the subject table if table columns are targets of SET NEW.col_name = value assignments in the trigger body.

  • Whatever other privileges normally are required for the statements executed by the trigger.

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 Section 6.2.23, “SQL-Based Account Activity Auditing”.

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

For additional discussion of trigger use, see Section 25.3.1, “Trigger Syntax and Examples”.

Page 11

13.1.23 CREATE VIEW Statement

The 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, CREATE OR REPLACE VIEW is the same as CREATE VIEW. If the view does exist, CREATE OR REPLACE VIEW replaces it.

For information about restrictions on view use, see Section 25.9, “Restrictions on Views”.

The select_statement is a SELECT statement that provides the definition of the view. (Selecting from the view selects, in effect, using the SELECT statement.) The select_statement can select from base tables, other views. Beginning with MySQL 8.0.19, the SELECT statement can use a VALUES statement as its source, or can be replaced with a TABLE statement, as with CREATE TABLE ... SELECT.

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 CREATE VIEW statement requires the CREATE VIEW privilege for the view, and some privilege for each column selected by the SELECT statement. For columns used elsewhere in the SELECT statement, you must have the SELECT privilege. If the OR REPLACE clause is present, you must also have the DROP privilege for the view. If the DEFINER clause is present, the privileges required depend on the user value, as discussed in Section 25.6, “Stored Object Access Control”.

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:

Unqualified table or view names in the 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 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 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 SELECT statement.

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

The following example defines a view that selects two columns from another table as well as an expression calculated from those columns:

A view definition is subject to the following restrictions:

• The SELECT statement cannot refer to system variables or user-defined variables.

• Within a stored program, the SELECT statement cannot refer to program parameters or local variables.

• The SELECT statement cannot refer to prepared statement parameters.

• Any table or view referred to in the definition must exist. If, after the view has been created, a table or view that the definition refers to is dropped, use of the view results in an error. To check a view definition for problems of this kind, use the CHECK TABLE statement.

• The definition cannot refer to a TEMPORARY table, and you cannot create a TEMPORARY view.

• You cannot associate a trigger with a view.

• Aliases for column names in the SELECT statement are checked against the maximum column length of 64 characters (not the maximum alias length of 256 characters).

ORDER BY is permitted in a view definition, but it is ignored if you select from a view using a statement that has its own ORDER BY.

For other options or clauses in the definition, they are added to the options or clauses of the statement that references the view, but the effect is undefined. For example, if a view definition includes a LIMIT clause, and you select from the view using a statement that has its own LIMIT clause, it is undefined which limit applies. This same principle applies to options such as ALL, DISTINCT, or SQL_SMALL_RESULT that follow the SELECT keyword, and to clauses such as INTO, FOR UPDATE, FOR SHARE, 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:

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 Section 25.6, “Stored Object Access Control”. 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 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 Section 6.2.23, “SQL-Based 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:

• At view definition time, the view creator must have the privileges needed to use the top-level objects accessed by the view. For example, if the view definition refers to table columns, the creator must have some privilege for each column in the select list of the definition, and the SELECT privilege for each column used elsewhere in the definition. If the definition refers to a stored function, only the privileges needed to invoke the function can be checked. The privileges required at function invocation time can be checked only as it executes: For different invocations, different execution paths within the function might be taken.

• The user who references a view must have appropriate privileges to access it (SELECT to select from it, INSERT to insert into it, and so forth.)

• When a view has been referenced, privileges for objects accessed by the view are checked against the privileges held by the view DEFINER account or invoker, depending on whether the SQL SECURITY characteristic is DEFINER or INVOKER, respectively.

• If reference to a view causes execution of a stored function, privilege checking for statements executed within the function depend on whether the function SQL SECURITY characteristic is DEFINER or INVOKER. If the security characteristic is DEFINER, the function runs with the privileges of the DEFINER account. If the characteristic is INVOKER, the function runs with the privileges determined by the view's SQL SECURITY characteristic.

Example: A view might depend on a stored function, and that function might invoke other stored routines. For example, the following view invokes a stored function f():

Suppose that f() contains a statement such as this:

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 Section 25.5.2, “View Processing Algorithms”, as well as Section 8.2.2.4, “Optimizing Derived Tables, View References, and Common Table Expressions with Merging or Materialization”.

Some views are updatable. That is, you can use them in statements such as UPDATE, DELETE, or INSERT to update the contents of the underlying table. For a view to be updatable, there must be a one-to-one relationship between the rows in the view and the rows in the underlying table. There are also certain other constructs that make a view nonupdatable.

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 Section 13.1.20.8, “CREATE TABLE and 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 Section 25.5.3, “Updatable and Insertable Views”, and Section 25.5.4, “The View WITH CHECK OPTION Clause”.

Page 12

13.1.24 DROP DATABASE Statement

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

Important

When a database is dropped, privileges granted specifically for the database are not automatically dropped. They must be dropped manually. See Section 13.7.1.6, “GRANT Statement”.

IF EXISTS is used to prevent an error from occurring if the database does not exist.

If the default database is dropped, the default database is unset (the DATABASE() function returns NULL).

If you use DROP DATABASE on a symbolically linked database, both the link and the original database are deleted.

DROP DATABASE returns the number of tables that were removed.

The DROP DATABASE statement removes from the given database directory those files and directories that MySQL itself may create during normal operation. This includes all files with the extensions shown in the following list:

• .BAK

• .DAT

• .HSH

• .MRG

• .MYD

• .MYI

• .cfg

• .db

• .ibd

• .ndb

If other files or directories remain in the database directory after MySQL removes those just listed, the database directory cannot be removed. In this case, you must remove any remaining files or directories manually and issue the DROP DATABASE statement again.

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 Section 13.1.20.2, “CREATE TEMPORARY TABLE Statement”.

You can also drop databases with mysqladmin. See Section 4.5.2, “mysqladmin — A MySQL Server Administration Program”.

Page 13

13.1.25 DROP EVENT Statement

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.