Chapter 13 SQL Statement Syntax


内容表

13.1 Data Definition Statements
13.1.1 Atomic Data Definition Statement Support
13.1.2 ALTER DATABASE Syntax
13.1.3 ALTER EVENT Syntax
13.1.4 ALTER FUNCTION Syntax
13.1.5 ALTER INSTANCE Syntax
13.1.6 ALTER LOGFILE GROUP Syntax
13.1.7 ALTER PROCEDURE Syntax
13.1.8 ALTER SERVER Syntax
13.1.9 ALTER TABLE Syntax
13.1.10 ALTER TABLESPACE Syntax
13.1.11 ALTER VIEW Syntax
13.1.12 CREATE DATABASE Syntax
13.1.13 CREATE EVENT Syntax
13.1.14 CREATE FUNCTION Syntax
13.1.15 CREATE INDEX Syntax
13.1.16 CREATE LOGFILE GROUP Syntax
13.1.17 CREATE PROCEDURE and CREATE FUNCTION Syntax
13.1.18 CREATE SERVER Syntax
13.1.19 CREATE SPATIAL REFERENCE SYSTEM Syntax
13.1.20 CREATE TABLE Syntax
13.1.21 CREATE TABLESPACE Syntax
13.1.22 CREATE TRIGGER Syntax
13.1.23 CREATE VIEW Syntax
13.1.24 DROP DATABASE Syntax
13.1.25 DROP EVENT Syntax
13.1.26 DROP FUNCTION Syntax
13.1.27 DROP INDEX Syntax
13.1.28 DROP LOGFILE GROUP Syntax
13.1.29 DROP PROCEDURE and DROP FUNCTION Syntax
13.1.30 DROP SERVER Syntax
13.1.31 DROP SPATIAL REFERENCE SYSTEM Syntax
13.1.32 DROP TABLE Syntax
13.1.33 DROP TABLESPACE Syntax
13.1.34 DROP TRIGGER Syntax
13.1.35 DROP VIEW Syntax
13.1.36 RENAME TABLE Syntax
13.1.37 TRUNCATE TABLE Syntax
13.2 Data Manipulation Statements
13.2.1 CALL Syntax
13.2.2 DELETE Syntax
13.2.3 DO Syntax
13.2.4 HANDLER Syntax
13.2.5 IMPORT TABLE Syntax
13.2.6 INSERT Syntax
13.2.7 LOAD DATA Syntax
13.2.8 LOAD XML Syntax
13.2.9 REPLACE Syntax
13.2.10 SELECT Syntax
13.2.11 Subquery Syntax
13.2.12 UPDATE Syntax
13.2.13 WITH Syntax (Common Table Expressions)
13.3 Transactional and Locking Statements
13.3.1 START TRANSACTION, COMMIT, and ROLLBACK Syntax
13.3.2 Statements That Cannot Be Rolled Back
13.3.3 Statements That Cause an Implicit Commit
13.3.4 SAVEPOINT, ROLLBACK TO SAVEPOINT, and RELEASE SAVEPOINT Syntax
13.3.5 LOCK INSTANCE FOR BACKUP and UNLOCK INSTANCE Syntax
13.3.6 LOCK TABLES and UNLOCK TABLES Syntax
13.3.7 SET TRANSACTION Syntax
13.3.8 XA Transactions
13.4 Replication Statements
13.4.1 SQL Statements for Controlling Master Servers
13.4.2 SQL Statements for Controlling Slave Servers
13.4.3 SQL Statements for Controlling Group Replication
13.5 Prepared SQL Statement Syntax
13.5.1 PREPARE Syntax
13.5.2 EXECUTE Syntax
13.5.3 DEALLOCATE PREPARE Syntax
13.6 Compound-Statement Syntax
13.6.1 BEGIN ... END Compound-Statement Syntax
13.6.2 Statement Label Syntax
13.6.3 DECLARE Syntax
13.6.4 Variables in Stored Programs
13.6.5 Flow Control Statements
13.6.6 Cursors
13.6.7 Condition Handling
13.7 Database Administration Statements
13.7.1 Account Management Statements
13.7.2 Resource Group Management Statements
13.7.3 Table Maintenance Statements
13.7.4 Component, Plugin, and User-Defined Function Statements
13.7.5 SET Syntax
13.7.6 SHOW Syntax
13.7.7 Other Administrative Statements
13.8 Utility Statements
13.8.1 DESCRIBE Syntax
13.8.2 EXPLAIN Syntax
13.8.3 HELP Syntax
13.8.4 USE Syntax

This chapter describes the syntax for the SQL statements supported by MySQL.

13.1 Data Definition Statements

13.1.1 Atomic Data Definition Statement Support

MySQL 8.0 supports atomic Data Definition Language (DDL) statements. This feature is referred to as atomic DDL . An atomic DDL statement combines the data dictionary updates, storage engine operations, and binary log writes associated with a DDL operation into a single, atomic transaction. The transaction is either committed, with applicable changes persisted to the data dictionary, storage engine, and binary log, or is rolled back, even if the server halts during the operation.

Atomic DDL is made possible by the introduction of the MySQL data dictionary in MySQL 8.0. In earlier MySQL versions, metadata was stored in metadata files, nontransactional tables, and storage engine-specific dictionaries, which necessitated intermediate commits. Centralized, transactional metadata storage provided by the MySQL data dictionary removed this barrier, making it possible to restructure DDL statement operations into atomic transactions.

The atomic DDL feature is described under the following topics in this section:

Supported DDL Statements

The atomic DDL feature supports both table and non-table DDL statements. Table-related DDL operations require storage engine support, whereas non-table DDL operations do not. Currently, only the InnoDB storage engine supports atomic DDL.

  • Supported table DDL statements include CREATE , ALTER , and DROP statements for databases, tablespaces, tables, and indexes, and the TRUNCATE TABLE statement.

  • Supported non-table DDL statements include:

    • CREATE and DROP statements, and, if applicable, ALTER statements for stored programs, triggers, views, and user-defined functions (UDFs).

    • Account management statements: CREATE , ALTER , DROP , and, if applicable, RENAME statements for users and roles, as well as GRANT and REVOKE statements.

The following statements are not supported by the atomic DDL feature:

Atomic DDL Characteristics

The characteristics of atomic DDL statements include the following:

  • Metadata updates, binary log writes, and storage engine operations, where applicable, are combined into a single transaction.

  • There are no intermediate commits at the SQL layer during the DDL operation.

  • Where applicable:

    • The state of data dictionary, routine, event, and UDF caches is consistent with the status of the DDL operation, meaning that caches are updated to reflect whether or not the DDL operation was completed successfully or rolled back.

    • The storage engine methods involved in a DDL operation do not perform intermediate commits, and the storage engine registers itself as part of the DDL transaction.

    • The storage engine supports redo and rollback of DDL operations, which is performed in the Post-DDL phase of the DDL operation.

  • The visible behaviour of DDL operations is atomic, which changes the behavior of some DDL statements. See Changes in DDL Statement Behavior .

Note

DDL statements, atomic or otherwise, implicitly end any transaction that is active in the current session, as if you had done a COMMIT before executing the statement. This means that DDL statements cannot be performed within another transaction, within transaction control statements such as START TRANSACTION ... COMMIT , or combined with other statements within the same transaction.

Changes in DDL Statement Behavior

This section describes changes in DDL statement behavior due to the introduction of atomic DDL support.

  • DROP TABLE operations are fully atomic if all named tables use an atomic DDL-supported storage engine. The statement either drops all tables successfully or is rolled back.

    DROP TABLE fails with an error if a named table does not exist, and no changes are made, regardless of the storage engine. This change in behavior is demonstrated in the following example, where the DROP TABLE statement fails because a named table does not exist:

    mysql> CREATE TABLE t1 (c1 INT);
    mysql> DROP TABLE t1, t2;
    ERROR 1051 (42S02): Unknown table 'test.t2'
    mysql> SHOW TABLES;
    +----------------+
    | Tables_in_test |
    +----------------+
    | t1             |
    +----------------+
                                            

    Prior to the introduction of atomic DDL, DROP TABLE reports an error for the named table that does not exist but succeeds for the named table that does exist:

    mysql> CREATE TABLE t1 (c1 INT);
    mysql> DROP TABLE t1, t2;
    ERROR 1051 (42S02): Unknown table 'test.t2'
    mysql> SHOW TABLES;
    Empty set (0.00 sec)
                                            
    Note

    Due to this change in behavior, a partially completed DROP TABLE statement on a MySQL 5.7 master fails when replicated on a MySQL 8.0 slave. To avoid this failure scenario, use IF EXISTS syntax in DROP TABLE statements to prevent errors from occurring for tables that do not exist.

  • DROP DATABASE is atomic if all tables use an atomic DDL-supported storage engine. The statement either drops all objects successfully or is rolled back. However, removal of the database directory from the file system occurs last and is not part of the atomic transaction. If removal of the database directory fails due to a file system error or server halt, the DROP DATABASE transaction is not rolled back.

  • For tables that do not use an atomic DDL-supported storage engine, table deletion occurs outside of the atomic DROP TABLE or DROP DATABASE transaction. Such table deletions are written to the binary log individually, which limits the discrepancy between the storage engine, data dictionary, and binary log to one table at most in the case of an interrupted DROP TABLE or DROP DATABASE operation. For operations that drop multiple tables, the tables that do not use an atomic DDL-supported storage engine are dropped before tables that do.

  • CREATE TABLE , ALTER TABLE , RENAME TABLE , TRUNCATE TABLE , CREATE TABLESPACE , and DROP TABLESPACE operations for tables that use an atomic DDL-supported storage engine are either fully committed or rolled back if the server halts during their operation. In earlier MySQL releases, interruption of these operations could cause discrepancies between the storage engine, data dictionary, and binary log, or leave behind orphan files. RENAME TABLE operations are only atomic if all named tables use an atomic DDL-supported storage engine.

  • DROP VIEW fails if a named view does not exist, and no changes are made. The change in behavior is demonstrated in this example, where the DROP VIEW statement fails because a named view does not exist:

    mysql> CREATE VIEW test.viewA AS SELECT * FROM t;
    mysql> DROP VIEW test.viewA, test.viewB;
    ERROR 1051 (42S02): Unknown table 'test.viewB'
    mysql> SHOW FULL TABLES IN test WHERE TABLE_TYPE LIKE 'VIEW';
    +----------------+------------+
    | Tables_in_test | Table_type |
    +----------------+------------+
    | viewA          | VIEW       |
    +----------------+------------+
                                            

    Prior to the introduction of atomic DDL, DROP VIEW returns an error for the named view that does not exist but succeeds for the named view that does exist:

    mysql> CREATE VIEW test.viewA AS SELECT * FROM t;
    mysql> DROP VIEW test.viewA, test.viewB;
    ERROR 1051 (42S02): Unknown table 'test.viewB'
    mysql> SHOW FULL TABLES IN test WHERE TABLE_TYPE LIKE 'VIEW';
    Empty set (0.00 sec)
                                            
    Note

    Due to this change in behavior, a partially completed DROP VIEW operation on a MySQL 5.7 master fails when replicated on a MySQL 8.0 slave. To avoid this failure scenario, use IF EXISTS syntax in DROP VIEW statements to prevent an error from occurring for views that do not exist.

  • Partial execution of account management statements is no longer permitted. Account management statements either succeed for all named users or roll back and have no effect if an error occurs. In earlier MySQL versions, account management statements that name multiple users could succeed for some users and fail for others.

    The change in behavior is demonstrated in this example, where the second CREATE USER statement returns an error but fails because it cannot succeed for all named users.

    mysql> CREATE USER userA;
    mysql> CREATE USER userA, userB;
    ERROR 1396 (HY000): Operation CREATE USER failed for 'userA'@'%'
    mysql> SELECT User FROM mysql.user WHERE User LIKE 'user%';
    +-------+
    | User  |
    +-------+
    | userA |
    +-------+
                                            

    Prior to the introduction of atomic DDL, the second CREATE USER statement returns an error for the named user that does not exist but succeeds for the named user that does exist:

    mysql> CREATE USER userA;
    mysql> CREATE USER userA, userB;
    ERROR 1396 (HY000): Operation CREATE USER failed for 'userA'@'%'
    mysql> SELECT User FROM mysql.user WHERE User LIKE 'user%';
    +-------+
    | User  |
    +-------+
    | userA |
    | userB |
    +-------+
                                            
    Note

    Due to this change in behavior, partially completed account management statements on a MySQL 5.7 master fail when replicated on a MySQL 8.0 slave. To avoid this failure scenario, use IF EXISTS or IF NOT EXISTS syntax, as appropriate, in account management statements to prevent errors related to named users.

Storage Engine Support

Currently, only the InnoDB storage engine supports atomic DDL. Storage engines that do not support atomic DDL are exempted from DDL atomicity. DDL operations involving exempted storage engines remain capable of introducing inconsistencies that can occur when operations are interrupted or only partially completed.

To support redo and rollback of DDL operations, InnoDB writes DDL logs to the mysql.innodb_ddl_log table, which is a hidden data dictionary table that resides in the mysql.ibd data dictionary tablespace.

To view DDL logs that are written to the mysql.innodb_ddl_log table during a DDL operation, enable the innodb_print_ddl_logs configuration option. For more information, see Viewing DDL Logs .

Note

The redo logs for changes to the mysql.innodb_ddl_log table are flushed to disk immediately regardless of the innodb_flush_log_at_trx_commit setting. Flushing the redo logs immediately avoids situations where data files are modified by DDL operations but the redo logs for changes to the mysql.innodb_ddl_log table resulting from those operations are not persisted to disk. Such a situation could cause errors during rollback or recovery.

The InnoDB storage engine executes DDL operations in phases. DDL operations such as ALTER TABLE may perform the Prepare and Perform phases multiple times prior to the Commit phase.

  1. Prepare : Create the required objects and write the DDL logs to the mysql.innodb_ddl_log table. The DDL logs define how to roll forward and roll back the DDL operation.

  2. Perform : Perform the DDL operation. For example, perform a create routine for a CREATE TABLE operation.

  3. Commit : Update the data dictionary and commit the data dictionary transaction.

  4. Post-DDL : Replay and remove DDL logs from the mysql.innodb_ddl_log table. To ensure that rollback can be performed safely without introducing inconsistencies, file operations such as renaming or removing data files are performed in this final phase. This phase also removes dynamic metadata from the mysql.innodb_dynamic_metadata data dictionary table for DROP TABLE , TRUNCATE TABLE , and other DDL operations that rebuild the table.

DDL logs are replayed and removed from the mysql.innodb_ddl_log table during the Post-DDL phase, regardless of whether the transaction is committed or rolled back. DDL logs should only remain in the mysql.innodb_ddl_log table if the server is halted during a DDL operation. In this case, the DDL logs are replayed and removed after recovery.

In a recovery situation, a DDL transaction may be committed or rolled back when the server is restarted. If the data dictionary transaction that was performed during the Commit phase of a DDL operation is present in the redo log and binary log, the operation is considered successful and is rolled forward. Otherwise, the incomplete data dictionary transaction is rolled back when InnoDB replays data dictionary redo logs, and the DDL transaction is rolled back.

Viewing DDL Logs

To view DDL logs that are written to the mysql.innodb_ddl_log data dictionary table during atomic DDL operations that involve the InnoDB storage engine, enable innodb_print_ddl_logs to have MySQL write the DDL logs to stderr . Depending on the host operating system and MySQL configuration, stderr may be the error log, terminal, or console window. See Section 5.4.2.2, “Default Error Log Destination Configuration” .

InnoDB writes DDL logs to the mysql.innodb_ddl_log table to support redo and rollback of DDL operations. The mysql.innodb_ddl_log table is a hidden data dictionary table that resides in the mysql.ibd data dictionary tablespace. Like other hidden data dictionary tables, the mysql.innodb_ddl_log table cannot be accessed directly in non-debug versions of MySQL. (See Section 14.1, “Data Dictionary Schema” .) The structure of the mysql.innodb_ddl_log table corresponds to this definition:

CREATE TABLE mysql.innodb_ddl_log (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  thread_id BIGINT UNSIGNED NOT NULL,
  type INT UNSIGNED NOT NULL,
  space_id INT UNSIGNED,
  page_no INT UNSIGNED,
  index_id BIGINT UNSIGNED,
  table_id BIGINT UNSIGNED,
  old_file_path VARCHAR(512) COLLATE UTF8_BIN,
  new_file_path VARCHAR(512) COLLATE UTF8_BIN,
  KEY(thread_id)
);
                            
  • id : A unique identifier for a DDL log record.

  • thread_id : Each DDL log record is assigned a thread_id , which is used to replay and remove DDL logs that belong to a particular DDL transaction. DDL transactions that involve multiple data file operations generate multiple DDL log records.

  • type : The DDL operation type. Types include FREE (drop an index tree), DELETE (delete a file), RENAME (rename a file), or DROP (drop metadata from the mysql.innodb_dynamic_metadata data dictionary table).

  • space_id : The tablespace ID.

  • page_no : A page that contains allocation information; an index tree root page, for example.

  • index_id : The index ID.

  • table_id : The table ID.

  • old_file_path : The old tablespace file path. Used by DDL operations that create or drop tablespace files; also used by DDL operations that rename a tablespace.

  • new_file_path : The new tablespace file path. Used by DDL operations that rename tablespace files.

This example demonstrates enabling innodb_print_ddl_logs to view DDL logs written to strderr for a CREATE TABLE operation.

mysql> SET GLOBAL innodb_print_ddl_logs=1;
mysql> CREATE TABLE t1 (c1 INT) ENGINE = InnoDB;
                            
[Note] [000000] InnoDB: DDL log insert : [DDL record: DELETE SPACE, id=18, thread_id=7,
space_id=5, old_file_path=./test/t1.ibd]
[Note] [000000] InnoDB: DDL log delete : by id 18
[Note] [000000] InnoDB: DDL log insert : [DDL record: REMOVE CACHE, id=19, thread_id=7,
table_id=1058, new_file_path=test/t1]
[Note] [000000] InnoDB: DDL log delete : by id 19
[Note] [000000] InnoDB: DDL log insert : [DDL record: FREE, id=20, thread_id=7,
space_id=5, index_id=132, page_no=4]
[Note] [000000] InnoDB: DDL log delete : by id 20
[Note] [000000] InnoDB: DDL log post ddl : begin for thread id : 7
[Note] [000000] InnoDB: DDL log post ddl : end for thread id : 7
                            

13.1.2 ALTER DATABASE Syntax

ALTER {DATABASE | SCHEMA} [db_name]
    alter_specification ...
alter_specification:
    [DEFAULT] CHARACTER SET [=] charset_name
  | [DEFAULT] COLLATE [=] collation_name
                        

ALTER DATABASE enables you to change the overall characteristics of a database. These characteristics are stored in the data dictionary. To use ALTER DATABASE , you need the ALTER privilege on the database. ALTER SCHEMA is a synonym for ALTER DATABASE .

The database name can be omitted from the first syntax, in which case the statement applies to the default database.

National Language Characteristics

The CHARACTER SET clause changes the default database character set. The COLLATE clause changes the default database collation. Chapter 10, Character Sets, Collations, Unicode , discusses character set and collation names.

You can see what character sets and collations are available using, respectively, the SHOW CHARACTER SET and SHOW COLLATION statements. See Section 13.7.6.3, “SHOW CHARACTER SET Syntax” , and Section 13.7.6.4, “SHOW COLLATION Syntax” , for more information.

If you change the default character set or collation for a database, stored routines that use the database defaults must be dropped and recreated so that they use the new defaults. (In a stored routine, variables with character data types use the database defaults if the character set or collation are not specified explicitly. See Section 13.1.17, “CREATE PROCEDURE and CREATE FUNCTION Syntax” .)

13.1.3 ALTER EVENT Syntax

ALTER
    [DEFINER = { user | CURRENT_USER }]
    EVENT event_name
    [ON SCHEDULE schedule]
    [ON COMPLETION [NOT] PRESERVE]
    [RENAME TO new_event_name]
    [ENABLE | DISABLE | DISABLE ON SLAVE]
    [COMMENT 'string']
    [DO event_body]
                        

The ALTER EVENT statement changes one or more of the characteristics of an existing event without the need to drop and recreate it. The syntax for each of the DEFINER , ON SCHEDULE , ON COMPLETION , COMMENT , ENABLE / DISABLE , and DO clauses is exactly the same as when used with CREATE EVENT . (See Section 13.1.13, “CREATE EVENT Syntax” .)

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

ALTER EVENT works only with an existing event:

mysql> ALTER EVENT no_such_event 
     >     ON SCHEDULE 
     >       EVERY '2:3' DAY_HOUR;
ERROR 1517 (HY000): Unknown event 'no_such_event'
                        

In each of the following examples, assume that the event named myevent is defined as shown here:

CREATE EVENT myevent
    ON SCHEDULE
      EVERY 6 HOUR
    COMMENT 'A sample comment.'
    DO
      UPDATE myschema.mytable SET mycol = mycol + 1;
                        

The following statement changes the schedule for myevent from once every six hours starting immediately to once every twelve hours, starting four hours from the time the statement is run:

ALTER EVENT myevent
    ON SCHEDULE
      EVERY 12 HOUR
    STARTS CURRENT_TIMESTAMP + INTERVAL 4 HOUR;
                        

It is possible to change multiple characteristics of an event in a single statement. This example changes the SQL statement executed by myevent to one that deletes all records from mytable ; it also changes the schedule for the event such that it executes once, one day after this ALTER EVENT statement is run.

ALTER EVENT myevent
    ON SCHEDULE
      AT CURRENT_TIMESTAMP + INTERVAL 1 DAY
    DO
      TRUNCATE TABLE myschema.mytable;
                        

Specify the options in an ALTER EVENT statement only for those characteristics that you want to change; omitted options keep their existing values. This includes any default values for CREATE EVENT such as ENABLE .

To disable myevent , use this ALTER EVENT statement:

ALTER EVENT myevent
    DISABLE;
                        

The ON SCHEDULE clause may use expressions involving built-in MySQL functions and user variables to obtain any of the timestamp or interval values which it contains. You cannot use stored routines or user-defined functions in such expressions, and you cannot use any table references; however, you can use SELECT FROM DUAL . This is true for both ALTER EVENT and CREATE EVENT statements. References to stored routines, user-defined functions, and tables in such cases are specifically not permitted, and fail with an error (see Bug #22830).

Although an ALTER EVENT statement that contains another ALTER EVENT statement in its DO clause appears to succeed, when the server attempts to execute the resulting scheduled event, the execution fails with an error.

To rename an event, use the ALTER EVENT statement's RENAME TO clause. This statement renames the event myevent to yourevent :

ALTER EVENT myevent
    RENAME TO yourevent;
                        

You can also move an event to a different database using ALTER EVENT ... RENAME TO ... and db_name.event_name notation, as shown here:

ALTER EVENT olddb.myevent
    RENAME TO newdb.myevent;
                        

To execute the previous statement, the user executing it must have the EVENT privilege on both the olddb and newdb databases.

Note

There is no RENAME EVENT statement.

The value DISABLE ON SLAVE is used on a replication slave instead of ENABLE or DISABLE to indicate an event that was created on the master and replicated to the slave, but that is not executed on the slave. Normally, DISABLE ON SLAVE is set automatically as required; however, there are some circumstances under which you may want or need to change it manually. See Section 17.4.1.16, “Replication of Invoked Features” , for more information.

13.1.4 ALTER FUNCTION Syntax

ALTER FUNCTION func_name [characteristic ...]
characteristic:
    COMMENT 'string'
  | LANGUAGE SQL
  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  | SQL SECURITY { DEFINER | INVOKER }
                        

This statement can be used to change the characteristics of a stored function. More than one change may be specified in an ALTER FUNCTION statement. However, you cannot change the parameters or body of a stored function using this statement; to make such changes, you must drop and re-create the function using DROP FUNCTION and CREATE FUNCTION .

You must have the ALTER ROUTINE privilege for the function. (That privilege is granted automatically to the function creator.) If binary logging is enabled, the ALTER FUNCTION statement might also require the SUPER privilege, as described in Section 24.7, “Binary Logging of Stored Programs” .

13.1.5 ALTER INSTANCE Syntax

ALTER INSTANCE ROTATE INNODB MASTER KEY
                        

ALTER INSTANCE defines actions applicable to a MySQL server instance.

The ALTER INSTANCE ROTATE INNODB MASTER KEY statement is used to rotate the master encryption key used for InnoDB tablespace encryption. A keyring plugin must be installed and configured to use this statement. By default, the MySQL server loads the keyring_file plugin. Key rotation requires the ENCRYPTION_KEY_ADMIN or SUPER privilege.

ALTER INSTANCE ROTATE INNODB MASTER KEY supports concurrent DML. However, it cannot be run concurrently with CREATE TABLE ... ENCRYPTION or ALTER TABLE ... ENCRYPTION operations, and locks are taken to prevent conflicts that could arise from concurrent execution of these statements. If one of the conflicting statements is running, it must complete before another can proceed.

ALTER INSTANCE actions are written to the binary log so that they can be executed on replicated servers.

For additional ALTER INSTANCE ROTATE INNODB MASTER KEY usage information, see Section 15.6.3.9, “Tablespace Encryption” . For information about the keyring_file plugin, see Section 6.5.4, “The MySQL Keyring” .

13.1.6 ALTER LOGFILE GROUP Syntax

ALTER LOGFILE GROUP logfile_group
    ADD UNDOFILE 'file_name'
    [INITIAL_SIZE [=] size]
    [WAIT]
    ENGINE [=] engine_name
                        

This statement adds an UNDO file named ' file_name ' to an existing log file group logfile_group . An ALTER LOGFILE GROUP statement has one and only one ADD UNDOFILE clause. No DROP UNDOFILE clause is currently supported.

Note

All NDB Cluster Disk Data objects share the same namespace. This means that each Disk Data object must be uniquely named (and not merely each Disk Data object of a given type). For example, you cannot have a tablespace and an undo log file with the same name, or an undo log file and a data file with the same name.

The optional INITIAL_SIZE parameter sets the UNDO file's initial size in bytes; if not specified, the initial size defaults to 134217728 (128 MB). You may optionally follow size with a one-letter abbreviation for an order of magnitude, similar to those used in my.cnf . Generally, this is one of the letters M (megabytes) or G (gigabytes). (Bug #13116514, Bug #16104705, Bug #62858)

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

The minimum allowed value for INITIAL_SIZE is 1048576 (1 MB). (Bug #29574)

Note

WAIT is parsed but otherwise ignored. This keyword currently has no effect, and is intended for future expansion.

The ENGINE parameter (required) determines the storage engine which is used by this log file group, with engine_name being the name of the storage engine. Currently, the only accepted values for engine_name are NDBCLUSTER and NDB . The two values are equivalent.

Here is an example, which assumes that the log file group lg_3 has already been created using CREATE LOGFILE GROUP (see Section 13.1.16, “CREATE LOGFILE GROUP Syntax” ):

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

When ALTER LOGFILE GROUP is used with ENGINE = NDBCLUSTER (alternatively, ENGINE = NDB ), an UNDO log file is created on each NDB Cluster data node. You can verify that the UNDO files were created and obtain information about them by querying the INFORMATION_SCHEMA.FILES table. For example:

mysql> SELECT FILE_NAME, LOGFILE_GROUP_NUMBER, EXTRA
    -> FROM INFORMATION_SCHEMA.FILES
    -> WHERE LOGFILE_GROUP_NAME = 'lg_3';
+-------------+----------------------+----------------+
| FILE_NAME   | LOGFILE_GROUP_NUMBER | EXTRA          |
+-------------+----------------------+----------------+
| newdata.dat |                    0 | CLUSTER_NODE=3 |
| newdata.dat |                    0 | CLUSTER_NODE=4 |
| undo_10.dat |                   11 | CLUSTER_NODE=3 |
| undo_10.dat |                   11 | CLUSTER_NODE=4 |
+-------------+----------------------+----------------+
4 rows in set (0.01 sec)
                        

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

Memory used for UNDO_BUFFER_SIZE comes from the global pool whose size is determined by the value of the SharedGlobalMemory data node configuration parameter. This includes any default value implied for this option by the setting of the InitialLogFileGroup data node configuration parameter.

ALTER LOGFILE GROUP is useful only with Disk Data storage for NDB Cluster. For more information, see Section 22.5.13, “NDB Cluster Disk Data Tables” .

13.1.7 ALTER PROCEDURE Syntax

ALTER PROCEDURE proc_name [characteristic ...]
characteristic:
    COMMENT 'string'
  | LANGUAGE SQL
  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  | SQL SECURITY { DEFINER | INVOKER }
                        

This statement can be used to change the characteristics of a stored procedure. More than one change may be specified in an ALTER PROCEDURE statement. However, you cannot change the parameters or body of a stored procedure using this statement; to make such changes, you must drop and re-create the procedure using DROP PROCEDURE and CREATE PROCEDURE .

You must have the ALTER ROUTINE privilege for the procedure. By default, that privilege is granted automatically to the procedure creator. This behavior can be changed by disabling the automatic_sp_privileges system variable. See Section 24.2.2, “Stored Routines and MySQL Privileges” .

13.1.8 ALTER SERVER Syntax

ALTER SERVER  server_name
    OPTIONS (option [, option] ...)
                        

Alters the server information for server_name , adjusting any of the options permitted in the CREATE SERVER statement. The corresponding fields in the mysql.servers table are updated accordingly. This statement requires the SUPER privilege.

For example, to update the USER option:

ALTER SERVER s OPTIONS (USER 'sally');
                        

ALTER SERVER causes an implicit commit. See Section 13.3.3, “Statements That Cause an Implicit Commit” .

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

13.1.9 ALTER TABLE Syntax

ALTER TABLE tbl_name
    [alter_specification [, alter_specification] ...]
    [partition_options]
alter_specification:
    table_options
  | ADD [COLUMN] col_name column_definition
        [FIRST | AFTER col_name]
  | ADD [COLUMN] (col_name column_definition,...)
  | ADD {INDEX|KEY} [index_name]
        [index_type] (key_part,...) [index_option] ...
  | ADD {FULLTEXT|SPATIAL} [INDEX|KEY] [index_name]
        (key_part,...) [index_option] ...
  | ADD [CONSTRAINT [symbol]] PRIMARY KEY
        [index_type] (key_part,...)
        [index_option] ...
  | ADD [CONSTRAINT [symbol]] UNIQUE [INDEX|KEY]
        [index_name] [index_type] (key_part,...)
        [index_option] ...
  | ADD [CONSTRAINT [symbol]] FOREIGN KEY
        [index_name] (col_name,...)
        reference_definition
  | ADD check_constraint_definition
  | DROP CHECK symbol
  | ALTER CHECK symbol [NOT] ENFORCED
  | ALGORITHM [=] {DEFAULT|INSTANT|INPLACE|COPY}
  | ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}
  | ALTER INDEX index_name {VISIBLE | INVISIBLE}
  | CHANGE [COLUMN] old_col_name new_col_name column_definition
        [FIRST|AFTER col_name]
  | [DEFAULT] CHARACTER SET [=] charset_name [COLLATE [=] collation_name]
  | CONVERT TO CHARACTER SET charset_name [COLLATE collation_name]
  | {DISABLE|ENABLE} KEYS
  | {DISCARD|IMPORT} TABLESPACE
  | DROP [COLUMN] col_name
  | DROP {INDEX|KEY} index_name
  | DROP PRIMARY KEY
  | DROP FOREIGN KEY fk_symbol
  | FORCE
  | LOCK [=] {DEFAULT|NONE|SHARED|EXCLUSIVE}
  | MODIFY [COLUMN] col_name column_definition
        [FIRST | AFTER col_name]
  | ORDER BY col_name [, col_name] ...
  | RENAME COLUMN old_col_name TO new_col_name
  | RENAME {INDEX|KEY} old_index_name TO new_index_name
  | RENAME [TO|AS] new_tbl_name
  | {WITHOUT|WITH} VALIDATION
  | ADD PARTITION (partition_definition)
  | DROP PARTITION partition_names
  | DISCARD PARTITION {partition_names | ALL} TABLESPACE
  | IMPORT PARTITION {partition_names | ALL} TABLESPACE
  | TRUNCATE PARTITION {partition_names | ALL}
  | COALESCE PARTITION number
  | REORGANIZE PARTITION partition_names INTO (partition_definitions)
  | EXCHANGE PARTITION partition_name WITH TABLE tbl_name [{WITH|WITHOUT} VALIDATION]
  | ANALYZE PARTITION {partition_names | ALL}
  | CHECK PARTITION {partition_names | ALL}
  | OPTIMIZE PARTITION {partition_names | ALL}
  | REBUILD PARTITION {partition_names | ALL}
  | REPAIR PARTITION {partition_names | ALL}
  | REMOVE PARTITIONING
  | UPGRADE PARTITIONING
key_part: {col_name [(length)] | (expr)} [ASC | DESC]
index_type:
    USING {BTREE | HASH}
index_option:
    KEY_BLOCK_SIZE [=] value
  | index_type
  | WITH PARSER parser_name
  | COMMENT 'string'
  | {VISIBLE | INVISIBLE}
check_constraint_definition:
    [CONSTRAINT [symbol]] CHECK (expr) [[NOT] ENFORCED]
table_options:
    table_option [[,] table_option] ...
table_option:
    AUTO_INCREMENT [=] value
  | AVG_ROW_LENGTH [=] value
  | [DEFAULT] CHARACTER SET [=] charset_name
  | CHECKSUM [=] {0 | 1}
  | [DEFAULT] COLLATE [=] collation_name
  | COMMENT [=] 'string'
  | COMPRESSION [=] {'ZLIB'|'LZ4'|'NONE'}
  | CONNECTION [=] 'connect_string'
  | {DATA|INDEX} DIRECTORY [=] 'absolute path to directory'
  | DELAY_KEY_WRITE [=] {0 | 1}
  | ENCRYPTION [=] {'Y' | 'N'}
  | ENGINE [=] engine_name
  | INSERT_METHOD [=] { NO | FIRST | LAST }
  | KEY_BLOCK_SIZE [=] value
  | MAX_ROWS [=] value
  | MIN_ROWS [=] value
  | PACK_KEYS [=] {0 | 1 | DEFAULT}
  | PASSWORD [=] 'string'
  | ROW_FORMAT [=] {DEFAULT|DYNAMIC|FIXED|COMPRESSED|REDUNDANT|COMPACT}
  | STATS_AUTO_RECALC [=] {DEFAULT|0|1}
  | STATS_PERSISTENT [=] {DEFAULT|0|1}
  | STATS_SAMPLE_PAGES [=] value
  | TABLESPACE tablespace_name [STORAGE {DISK|MEMORY|DEFAULT}]
  | UNION [=] (tbl_name[,tbl_name]...)
partition_options:
    (see CREATE TABLE options)
                        

ALTER TABLE changes the structure of a table. For example, you can add or delete columns, create or destroy indexes, change the type of existing columns, or rename columns or the table itself. You can also change characteristics such as the storage engine used for the table or the table comment.

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

Table Options

table_options signifies table options of the kind that can be used in the CREATE TABLE statement, such as ENGINE , AUTO_INCREMENT , AVG_ROW_LENGTH , MAX_ROWS , ROW_FORMAT , or TABLESPACE .

For descriptions of all table options, see Section 13.1.20, “CREATE TABLE Syntax” . However, ALTER TABLE ignores DATA DIRECTORY and INDEX DIRECTORY when given as table options. ALTER TABLE permits them only as partitioning options, and requires that you have the FILE privilege.

Use of table options with ALTER TABLE provides a convenient way of altering single table characteristics. For example:

  • If t1 is currently not an InnoDB table, this statement changes its storage engine to InnoDB :

    ALTER TABLE t1 ENGINE = InnoDB;
                                        
  • To change the InnoDB table to use compressed row-storage format:

    ALTER TABLE t1 ROW_FORMAT = COMPRESSED;
                                        
  • To enable or disable encryption for an InnoDB table in a file-per-table tablespace:

    ALTER TABLE t1 ENCRYPTION='Y';
    ALTER TABLE t1 ENCRYPTION='N';
                                        

    A keyring plugin must be installed and configured to use the ENCRYPTION option. For more information, see Section 15.6.3.9, “Tablespace Encryption” .

  • To reset the current auto-increment value:

    ALTER TABLE t1 AUTO_INCREMENT = 13;
                                        

    You cannot reset the counter to a value less than or equal to the value that is currently in use. For both InnoDB and MyISAM , if the value is less than or equal to the maximum value currently in the AUTO_INCREMENT column, the value is reset to the current maximum AUTO_INCREMENT column value plus one.

  • To change the default table character set:

    ALTER TABLE t1 CHARACTER SET = utf8;
                                        

    另请参阅 Changing the Character Set .

  • To add (or change) a table comment:

    ALTER TABLE t1 COMMENT = 'New table comment';
                                        
  • Use ALTER TABLE with the TABLESPACE option to move InnoDB tables between existing general tablespaces , file-per-table tablespaces, and the system tablespace . See Moving Tables Between Tablespaces Using ALTER TABLE .

    • ALTER TABLE ... TABLESPACE operations always cause a full table rebuild, even if the TABLESPACE attribute has not changed from its previous value.

    • ALTER TABLE ... TABLESPACE syntax does not support moving a table from a temporary tablespace to a persistent tablespace.

    • The DATA DIRECTORY clause, which is supported with CREATE TABLE ... TABLESPACE , is not supported with ALTER TABLE ... TABLESPACE , and is ignored if specified.

    • For more information about the capabilities and limitations of the TABLESPACE option, see CREATE TABLE .

  • MySQL NDB Cluster 8.0 supports setting NDB_TABLE options for controlling a table's partition balance (fragment count type), read-from-any-replica capability, full replication, or any combination of these, as part of the table comment for an ALTER TABLE statement in the same manner as for CREATE TABLE , as shown in this example:

    ALTER TABLE t1 COMMENT = "NDB_TABLE=READ_BACKUP=0,PARTITION_BALANCE=FOR_RA_BY_NODE";
                                        

    Bear in mind that ALTER TABLE ... COMMENT ... discards any existing comment for the table. See Setting NDB_TABLE options , for additional information and examples.

To verify that the table options were changed as intended, use SHOW CREATE TABLE , or query the INFORMATION_SCHEMA.TABLES table.

Performance and Space Requirements

ALTER TABLE operations are processed using one of the following algorithms:

  • COPY : Operations are performed on a copy of the original table, and table data is copied from the original table to the new table row by row. Concurrent DML is not permitted.

  • INPLACE : Operations avoid copying table data but may rebuild the table in place. An exclusive metadata lock on the table may be taken briefly during preparation and execution phases of the operation. Typically, concurrent DML is supported.

  • INSTANT : Operations only modify metadata in the data dictionary. No exclusive metadata locks are taken on the table during preparation and execution, and table data is unaffected, making operations instantaneous. Concurrent DML is permitted. (Introduced in MySQL 8.0.12)

The ALGORITHM clause is optional. If the ALGORITHM clause is omitted, MySQL uses ALGORITHM=INSTANT for storage engines and ALTER TABLE clauses that support it. Otherwise, ALGORITHM=INPLACE is used. If ALGORITHM=INPLACE is not supported, ALGORITHM=COPY is used.

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

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

The exception referred to earlier is that ALTER TABLE blocks reads (not just writes) at the point where it is ready to clear outdated table structures from the table and table definition caches. At this point, it must acquire an exclusive lock. To do so, it waits for current readers to finish, and blocks new reads and writes.

An ALTER TABLE operation that uses the COPY algorithm prevents concurrent DML operations. Concurrent queries are still allowed. That is, a table-copying operation always includes at least the concurrency restrictions of LOCK=SHARED (allow queries but not DML). You can further restrict concurrency for operations that support the LOCK clause by specifying LOCK=EXCLUSIVE , which prevents DML and queries. 更多信息,请见 Concurrency Control .

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

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

For information about space requirements for online DDL operations, see Section 15.12.3, “Online DDL Space Requirements” .

ALTER TABLE operations that support the INPLACE algorithm include:

  • ALTER TABLE operations supported by the InnoDB online DDL feature. See Section 15.12.1, “Online DDL Operations” .

  • Renaming a table. MySQL renames files that correspond to the table tbl_name without making a copy. (You can also use the RENAME TABLE statement to rename tables. See Section 13.1.36, “RENAME TABLE Syntax” .) Privileges granted specifically for the renamed table are not migrated to the new name. They must be changed manually.

  • Operations that only modify table metadata. These operations are immediate because the server does not touch table contents. Metadata-only operations include:

    • Renaming a column.

    • Changing the default value of a column (except for NDB tables).

    • Modifying the definition of an ENUM or SET column by adding new enumeration or set members to the end of the list of valid member values, as long as the storage size of the data type does not change. For example, adding a member to a SET column that has 8 members changes the required storage per value from 1 byte to 2 bytes; this requires a table copy. Adding members in the middle of the list causes renumbering of existing members, which requires a table copy.

    • Changing the definition of a spatial column to remove the SRID attribute. (Adding or changing an SRID attribute does require a rebuild and cannot be done in place because the server must verify that all values have the specified SRID value.)

    • As of MySQL 8.0.14, changing a column character set, when these conditions apply:

      • The column data type is CHAR , VARCHAR , a TEXT type, or ENUM .

      • The character set change is from utf8mb3 to utf8mb4 , or any character set to binary .

      • There is no index on the column.

    • As of MySQL 8.0.14, changing a generated column, when these conditions apply:

      • For InnoDB tables, statements that modify generated stored columns but do not change their type, expression, or nullability.

      • For non- InnoDB tables, statements that modify generated stored or virtual columns but do not change their type, expression, or nullability.

      An example of such a change is a change to the column comment.

  • Renaming an index.

  • Adding or dropping a secondary index, for InnoDB and NDB tables. See Section 15.12.1, “Online DDL Operations” .

  • For NDB tables, operations that add and drop indexes on variable-width columns. These operations occur online, without table copying and without blocking concurrent DML actions for most of their duration. See Section 22.5.14, “Online Operations with ALTER TABLE in NDB Cluster” .

  • Modifying index visibility with an ALTER INDEX operation.

  • Column modifications of tables containing generated columns that depend on columns with a DEFAULT value if the modified columns are not involved in the generated column expressions. For example, changing the NULL property of a separate column can be done in place without a table rebuild.

ALTER TABLE operations that support the INSTANT algorithm include:

  • Adding a column. This feature is referred to as Instant ADD COLUMN . Limitations apply. See Section 15.12.1, “Online DDL Operations” .

  • Adding or dropping a virtual column.

  • Adding or dropping a column default value.

  • Modifying the definition of an ENUM or SET column. The same restrictions apply as described above for ALGORITHM=INSTANT .

  • Changing the index type.

  • Renaming a table. The same restrictions apply as described above for ALGORITHM=INSTANT .

For more information about operations that support ALGORITHM=INSTANT , see Section 15.12.1, “Online DDL Operations” .

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

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

The WITHOUT VALIDATION and WITH VALIDATION clauses affect whether ALTER TABLE performs an in-place operation for virtual generated column modifications. See Section 13.1.9.2, “ALTER TABLE and Generated Columns” .

NDB Cluster 8.0 supports online operations using the same ALGORITHM=INPLACE syntax used with the standard MySQL Server. See Section 22.5.14, “Online Operations with ALTER TABLE in NDB Cluster” , for more information.

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

ALTER TABLE with ADD PARTITION , DROP PARTITION , COALESCE PARTITION , REBUILD PARTITION , or REORGANIZE PARTITION does not create temporary tables (except when used with NDB tables); however, these operations can and do create temporary partition files.

ADD or DROP operations for RANGE or LIST partitions are immediate operations or nearly so. ADD or COALESCE operations for HASH or KEY partitions copy data between all partitions, unless LINEAR HASH or LINEAR KEY was used; this is effectively the same as creating a new table, although the ADD or COALESCE operation is performed partition by partition. REORGANIZE operations copy only changed partitions and do not touch unchanged ones.

For MyISAM tables, you can speed up index re-creation (the slowest part of the alteration process) by setting the myisam_sort_buffer_size system variable to a high value.

Concurrency Control

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

Only LOCK = DEFAULT is permitted for operations that use ALGORITHM=INSTANT . The other LOCK clause parameters are not applicable.

The parameters for the LOCK clause are:

  • LOCK = DEFAULT
                                        

    Maximum level of concurrency for the given ALGORITHM clause (if any) and ALTER TABLE operation: Permit concurrent reads and writes if supported. If not, permit concurrent reads if supported. If not, enforce exclusive access.

  • LOCK = NONE
                                        

    If supported, permit concurrent reads and writes. Otherwise, an error occurs.

  • LOCK = SHARED
                                        

    If supported, permit concurrent reads but block writes. Writes are blocked even if concurrent writes are supported by the storage engine for the given ALGORITHM clause (if any) and ALTER TABLE operation. If concurrent reads are not supported, an error occurs.

  • LOCK = EXCLUSIVE
                                        

    Enforce exclusive access. This is done even if concurrent reads/writes are supported by the storage engine for the given ALGORITHM clause (if any) and ALTER TABLE operation.

Adding and Dropping Columns

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

To add a column at a specific position within a table row, use FIRST or AFTER col_name . The default is to add the column last.

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

If columns are dropped from a table, the columns are also removed from any index of which they are a part. If all columns that make up an index are dropped, the index is dropped as well. If you use CHANGE or MODIFY to shorten a column for which an index exists on the column, and the resulting column length is less than the index length, MySQL shortens the index automatically.

For ALTER TABLE ... ADD , if the column has an expression default value that uses a nondeterministic function, the statement may produce a warning or error. For details, see Section 17.1.3.6, “Restrictions on Replication with GTIDs” .

Renaming, Redefining, and Reordering Columns

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

  • CHANGE :

    • Can rename a column and change its definition, or both.

    • Has more capability than MODIFY or RENAME COLUMN , but at the expense of convenience for some operations. CHANGE requires naming the column twice if not renaming it, and requires respecifying the column definition if only renaming it.

    • With FIRST or AFTER , can reorder columns.

  • MODIFY :

    • Can change a column definition but not its name.

    • More convenient than CHANGE to change a column definition without renaming it.

    • With FIRST or AFTER , can reorder columns.

  • RENAME COLUMN :

    • Can change a column name but not its definition.

    • More convenient than CHANGE to rename a column without changing its definition.

  • ALTER : Used only to change a column default value.

CHANGE is a MySQL extension to standard SQL. MODIFY and RENAME COLUMN are MySQL extensions for Oracle compatibility.

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

ALTER TABLE t1 CHANGE a b BIGINT NOT NULL;
                        

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

ALTER TABLE t1 CHANGE b b INT NOT NULL;
                        

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

ALTER TABLE t1 MODIFY b INT NOT NULL;
                        

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

ALTER TABLE t1 CHANGE b a INT NOT NULL;
                        

RENAME COLUMN is more convenient to change the name without changing the definition because it requires only the old and new names:

ALTER TABLE t1 RENAME COLUMN b TO a;
                        

In general, you cannot rename a column to a name that already exists in the table. However, this is sometimes not the case, such as when you swap names or move them through a cycle. If a table has columns named a , b , and c , these are valid operations:

-- swap a and b
ALTER TABLE t1 RENAME COLUMN a TO b,
               RENAME COLUMN b TO a;
-- "rotate" a, b, c through a cycle
ALTER TABLE t1 RENAME COLUMN a TO b,
               RENAME COLUMN b TO c,
               RENAME COLUMN c TO a;
                        

For column definition changes using CHANGE or MODIFY , the definition must include the data type and all attributes that should apply to the new column, other than index attributes such as PRIMARY KEY or UNIQUE . Attributes present in the original definition but not specified for the new definition are not carried forward. Suppose that a column col1 is defined as INT UNSIGNED DEFAULT 1 COMMENT 'my column' and you modify the column as follows, intending to change only INT to BIGINT :

ALTER TABLE t1 MODIFY col1 BIGINT;
                        

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

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

For data type changes using CHANGE or MODIFY , MySQL tries to convert existing column values to the new type as well as possible.

Warning

This conversion may result in alteration of data. For example, if you shorten a string column, values may be truncated. To prevent the operation from succeeding if conversions to the new data type would result in loss of data, enable strict SQL mode before using ALTER TABLE (see Section 5.1.11, “Server SQL Modes” ).

If you use CHANGE or MODIFY to shorten a column for which an index exists on the column, and the resulting column length is less than the index length, MySQL shortens the index automatically.

For columns renamed by CHANGE or RENAME COLUMN , MySQL automatically renames these references to the renamed column:

  • Indexes that refer to the old column, including invisible indexes and disabled MyISAM indexes.

  • Foreign keys that refer to the old column.

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

  • Generated column and partition expressions that refer to the renamed column. You must use CHANGE to redefine such expressions in the same ALTER TABLE statement as the one that renames the column.

  • Views and stored programs that refer to the renamed column. You must manually alter the definition of these objects to refer to the new column name.

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

ALTER ... SET DEFAULT or ALTER ... DROP DEFAULT specify a new default value for a column or remove the old default value, respectively. If the old default is removed and the column can be NULL , the new default is NULL . If the column cannot be NULL , MySQL assigns a default value as described in Section 11.7, “Data Type Default Values” .

Primary Keys and Indexes

DROP PRIMARY KEY drops the primary key . If there is no primary key, an error occurs. For information about the performance characteristics of primary keys, especially for InnoDB tables, see Section 8.3.2, “Primary Key Optimization” .

If you add a UNIQUE INDEX or PRIMARY KEY to a table, MySQL stores it before any nonunique index to permit detection of duplicate keys as early as possible.

DROP INDEX removes an index. This is a MySQL extension to standard SQL. See Section 13.1.27, “DROP INDEX Syntax” . To determine index names, use SHOW INDEX FROM tbl_name .

Some storage engines permit you to specify an index type when creating an index. The syntax for the index_type specifier is USING type_name . For details about USING , see Section 13.1.15, “CREATE INDEX Syntax” . The preferred position is after the column list. Support for use of the option before the column list will be removed in a future MySQL release.

index_option values specify additional options for an index. USING is one such option. For details about permissible index_option values, see Section 13.1.15, “CREATE INDEX Syntax” .

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

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

For MyISAM tables, key updating can be controlled explicitly. Use ALTER TABLE ... DISABLE KEYS to tell MySQL to stop updating nonunique indexes. Then use ALTER TABLE ... ENABLE KEYS to re-create missing indexes. MyISAM does this with a special algorithm that is much faster than inserting keys one by one, so disabling keys before performing bulk insert operations should give a considerable speedup. Using ALTER TABLE ... DISABLE KEYS requires the INDEX privilege in addition to the privileges mentioned earlier.

While the nonunique indexes are disabled, they are ignored for statements such as SELECT and EXPLAIN that otherwise would use them.

After an ALTER TABLE statement, it may be necessary to run ANALYZE TABLE to update index cardinality information. See Section 13.7.6.22, “SHOW INDEX Syntax” .

The ALTER INDEX operation permits an index to be made visible or invisible. An invisible index is not used by the optimizer. Modification of index visibility applies to indexes other than primary keys (either explicit or implicit). This feature is storage engine neutral (supported for any engine). For more information, see Section 8.3.12, “Invisible Indexes” .

Foreign Keys and Other Constraints

The FOREIGN KEY and REFERENCES clauses are supported by the InnoDB and NDB storage engines, which implement ADD [CONSTRAINT [ symbol ]] FOREIGN KEY [ index_name ] (...) REFERENCES ... (...) . See Section 15.6.1.5, “InnoDB and FOREIGN KEY Constraints” . For other storage engines, the clauses are parsed but ignored.

For ALTER TABLE , unlike CREATE TABLE , ADD FOREIGN KEY ignores index_name if given and uses an automatically generated foreign key name. As a workaround, include the CONSTRAINT clause to specify the foreign key name:

ADD CONSTRAINT name FOREIGN KEY (....) ...
                        
Important

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

Note

Partitioned InnoDB tables do not support foreign keys. This restriction does not apply to NDB tables, including those explicitly partitioned by [LINEAR] KEY . For more information, see Section 23.6.2, “Partitioning Limitations Relating to Storage Engines” .

MySQL Server and NDB Cluster both support the use of ALTER TABLE to drop foreign keys:

ALTER TABLE tbl_name DROP FOREIGN KEY fk_symbol;
                        

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

The server prohibits changes to foreign key columns that have the potential to cause loss of referential integrity. It also prohibits changes to the data type of such columns that may be unsafe. For example, changing VARCHAR(20) to VARCHAR(30) is permitted, but changing it to VARCHAR(1024) is not because that alters the number of length bytes required to store individual values. A workaround is to use ALTER TABLE ... DROP FOREIGN KEY before changing the column definition and ALTER TABLE ... ADD FOREIGN KEY afterward.

ALTER TABLE tbl_name RENAME new_tbl_name changes internally generated foreign key constraint names and user-defined foreign key constraint names that contain the string tbl_name _ibfk_ to reflect the new table name. InnoDB interprets foreign key constraint names that contain the string tbl_name _ibfk_ as internally generated names.

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

ADD CHECK (expr)
                        

As of MySQL 8.0.16, ALTER TABLE permits CHECK constraints for existing tables to be added, dropped, or altered:

  • Add a new constraint:

    ALTER TABLE tbl_name
        ADD CONSTRAINT [symbol] CHECK (expr) [[NOT] ENFORCED];
                                        

    The meaning of constraint syntax elements is the same as for CREATE TABLE . See Section 13.1.20.7, “CHECK Constraints” .

  • Drop an existing constraint named symbol :

    ALTER TABLE tbl_name
        DROP CHECK symbol;
                                        
  • Alter whether an existing constraint named symbol is enforced:

    ALTER TABLE tbl_name
        ALTER CHECK symbol [NOT] ENFORCED;
                                        

The DROP CHECK and ALTER CHECK clauses are MySQL extensions to standard SQL.

If a table alteration causes a violation of an enforced CHECK constraint, an error occurs and the table is not modified. Examples of operations for which an error occurs:

  • Attempts to add the AUTO_INCREMENT attribute to a column that is used in a CHECK constraint.

  • Attempts to add an enforced CHECK constraint or enforce a nonenforced CHECK constraint for which existing rows violate the constraint condition.

  • Attempts to modify, rename, or drop a column that is used in a CHECK constraint, unless that constraint is also dropped in the same statement. Exception: If a CHECK constraint refers only to a single column, dropping the column automatically drops the constraint.

ALTER TABLE tbl_name RENAME new_tbl_name changes internally generated and user-defined CHECK constraint names that begin with the string tbl_name _chk_ to reflect the new table name. MySQL interprets CHECK constraint names that begin with the string tbl_name _chk_ as internally generated names.

Changing the Character Set

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

ALTER TABLE tbl_name CONVERT TO CHARACTER SET charset_name;
                        

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

For a column that has a data type of VARCHAR or one of the TEXT types, CONVERT TO CHARACTER SET changes the data type as necessary to ensure that the new column is long enough to store as many characters as the original column. For example, a TEXT column has two length bytes, which store the byte-length of values in the column, up to a maximum of 65,535. For a latin1 TEXT column, each character requires a single byte, so the column can store up to 65,535 characters. If the column is converted to utf8 , each character might require up to three bytes, for a maximum possible length of 3 × 65,535 = 196,605 bytes. That length does not fit in a TEXT column's length bytes, so MySQL converts the data type to MEDIUMTEXT , which is the smallest string type for which the length bytes can record a value of 196,605. Similarly, a VARCHAR column might be converted to MEDIUMTEXT .

To avoid data type changes of the type just described, do not use CONVERT TO CHARACTER SET . Instead, use MODIFY to change individual columns. For example:

ALTER TABLE t MODIFY latin1_text_col TEXT CHARACTER SET utf8;
ALTER TABLE t MODIFY latin1_varchar_col VARCHAR(M) CHARACTER SET utf8;
                        

If you specify CONVERT TO CHARACTER SET binary , the CHAR , VARCHAR , and TEXT columns are converted to their corresponding binary string types ( BINARY , VARBINARY , BLOB ). This means that the columns no longer will have a character set and a subsequent CONVERT TO operation will not apply to them.

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

Warning

The CONVERT TO operation converts column values between the original and named character sets. This is not what you want if you have a column in one character set (like latin1 ) but the stored values actually use some other, incompatible character set (like utf8 ). In this case, you have to do the following for each such column:

ALTER TABLE t1 CHANGE c1 c1 BLOB;
ALTER TABLE t1 CHANGE c1 c1 TEXT CHARACTER SET utf8;
                            

The reason this works is that there is no conversion when you convert to or from BLOB columns.

To change only the default character set for a table, use this statement:

ALTER TABLE tbl_name DEFAULT CHARACTER SET charset_name;
                        

The word DEFAULT is optional. The default character set is the character set that is used if you do not specify the character set for columns that you add to a table later (for example, with ALTER TABLE ... ADD column ).

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

Discarding and Importing InnoDB Tablespaces

An InnoDB table created in its own file-per-table tablespace can be discarded and imported using the DISCARD TABLESPACE and IMPORT TABLESPACE options. These options can be used to import a file-per-table tablespace from a backup or to copy a file-per-table tablespace from one database server to another. See Section 15.6.3.7, “Copying Tablespaces to Another Instance” .

Row Order for MyISAM Tables

ORDER BY enables you to create the new table with the rows in a specific order. This option is useful primarily when you know that you query the rows in a certain order most of the time. By using this option after major changes to the table, you might be able to get higher performance. In some cases, it might make sorting easier for MySQL if the table is in order by the column that you want to order it by later.

Note

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

ORDER BY syntax permits one or more column names to be specified for sorting, each of which optionally can be followed by ASC or DESC to indicate ascending or descending sort order, respectively. The default is ascending order. Only column names are permitted as sort criteria; arbitrary expressions are not permitted. This clause should be given last after any other clauses.

ORDER BY does not make sense for InnoDB tables because InnoDB always orders table rows according to the clustered index .

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

Partitioning Options

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

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

For more information about partition options, see Section 13.1.20, “CREATE TABLE Syntax” , and Section 13.1.9.1, “ALTER TABLE Partition Operations” . For information about and examples of ALTER TABLE ... EXCHANGE PARTITION statements, see Section 23.3.3, “Exchanging Partitions and Subpartitions with Tables” .

13.1.9.1 ALTER TABLE Partition Operations

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

  • Simply using a partition_options clause with ALTER TABLE on a partitioned table repartitions the table according to the partitioning scheme defined by the partition_options . This clause always begins with PARTITION BY , and follows the same syntax and other rules as apply to the partition_options clause for CREATE TABLE (for more detailed information, see Section 13.1.20, “CREATE TABLE Syntax” ), and can also be used to partition an existing table that is not already partitioned. For example, consider a (nonpartitioned) table defined as shown here:

    CREATE TABLE t1 (
        id INT,
        year_col INT
    );
                                            

    This table can be partitioned by HASH , using the id column as the partitioning key, into 8 partitions by means of this statement:

    ALTER TABLE t1
        PARTITION BY HASH(id)
        PARTITIONS 8;
                                            

    MySQL supports an ALGORITHM option with [SUB]PARTITION BY [LINEAR] KEY . ALGORITHM=1 causes the server to use the same key-hashing functions as MySQL 5.1 when computing the placement of rows in partitions; ALGORITHM=2 means that the server employs the key-hashing functions implemented and used by default for new KEY partitioned tables in MySQL 5.5 and later. (Partitioned tables created with the key-hashing functions employed in MySQL 5.5 and later cannot be used by a MySQL 5.1 server.) Not specifying the option has the same effect as using ALGORITHM=2 . This option is intended for use chiefly when upgrading or downgrading [LINEAR] KEY partitioned tables between MySQL 5.1 and later MySQL versions, or for creating tables partitioned by KEY or LINEAR KEY on a MySQL 5.5 or later server which can be used on a MySQL 5.1 server.

    The table that results from using an ALTER TABLE ... PARTITION BY statement must follow the same rules as one created using CREATE TABLE ... PARTITION BY . This includes the rules governing the relationship between any unique keys (including any primary key) that the table might have, and the column or columns used in the partitioning expression, as discussed in Section 23.6.1, “Partitioning Keys, Primary Keys, and Unique Keys” . The CREATE TABLE ... PARTITION BY rules for specifying the number of partitions also apply to ALTER TABLE ... PARTITION BY .

    The partition_definition clause for ALTER TABLE ADD PARTITION supports the same options as the clause of the same name for the CREATE TABLE statement. (See Section 13.1.20, “CREATE TABLE Syntax” , for the syntax and description.) Suppose that you have the partitioned table created as shown here:

    CREATE TABLE t1 (
        id INT,
        year_col INT
    )
    PARTITION BY RANGE (year_col) (
        PARTITION p0 VALUES LESS THAN (1991),
        PARTITION p1 VALUES LESS THAN (1995),
        PARTITION p2 VALUES LESS THAN (1999)
    );
                                            

    You can add a new partition p3 to this table for storing values less than 2002 as follows:

    ALTER TABLE t1 ADD PARTITION (PARTITION p3 VALUES LESS THAN (2002));
                                            

    DROP PARTITION can be used to drop one or more RANGE or LIST partitions. This statement cannot be used with HASH or KEY partitions; instead, use COALESCE PARTITION (see later in this section). Any data that was stored in the dropped partitions named in the partition_names list is discarded. For example, given the table t1 defined previously, you can drop the partitions named p0 and p1 as shown here:

    ALTER TABLE t1 DROP PARTITION p0, p1;
                                            
    Note

    DROP PARTITION does not work with tables that use the NDB storage engine. See Section 23.3.1, “Management of RANGE and LIST Partitions” , and Section 22.1.7, “Known Limitations of NDB Cluster” .

    ADD PARTITION and DROP PARTITION do not currently support IF [NOT] EXISTS .

    The DISCARD PARTITION ... TABLESPACE and IMPORT PARTITION ... TABLESPACE options extend the Transportable Tablespace feature to individual InnoDB table partitions. Each InnoDB table partition has its own tablespace file ( .idb file). The Transportable Tablespace feature makes it easy to copy the tablespaces from a running MySQL server instance to another running instance, or to perform a restore on the same instance. Both options take a comma-separated list of one or more partition names. For example:

    ALTER TABLE t1 DISCARD PARTITION p2, p3 TABLESPACE;
                                            
    ALTER TABLE t1 IMPORT PARTITION p2, p3 TABLESPACE;
                                            

    When running DISCARD PARTITION ... TABLESPACE and IMPORT PARTITION ... TABLESPACE on subpartitioned tables, both partition and subpartition names are allowed. When a partition name is specified, subpartitions of that partition are included.

    The Transportable Tablespace feature also supports copying or restoring partitioned InnoDB tables (all partitions at once). For additional information, see Section 15.6.3.7, “Copying Tablespaces to Another Instance” , as well as, Section 15.6.3.7.1, “Transportable Tablespace Examples” .

    Renames of partitioned tables are supported. You can rename individual partitions indirectly using ALTER TABLE ... REORGANIZE PARTITION ; however, this operation copies the partition's data.

    To delete rows from selected partitions, use the TRUNCATE PARTITION option. This option takes a list of one or more comma-separated partition names. Consider the table t1 created by this statement:

    CREATE TABLE t1 (
        id INT,
        year_col INT
    )
    PARTITION BY RANGE (year_col) (
        PARTITION p0 VALUES LESS THAN (1991),
        PARTITION p1 VALUES LESS THAN (1995),
        PARTITION p2 VALUES LESS THAN (1999),
        PARTITION p3 VALUES LESS THAN (2003),
        PARTITION p4 VALUES LESS THAN (2007)
    );
                                            

    To delete all rows from partition p0 , use the following statement:

    ALTER TABLE t1 TRUNCATE PARTITION p0;
                                            

    The statement just shown has the same effect as the following DELETE statement:

    DELETE FROM t1 WHERE year_col < 1991;
                                            

    When truncating multiple partitions, the partitions do not have to be contiguous: This can greatly simplify delete operations on partitioned tables that would otherwise require very complex WHERE conditions if done with DELETE statements. For example, this statement deletes all rows from partitions p1 and p3 :

    ALTER TABLE t1 TRUNCATE PARTITION p1, p3;
                                            

    An equivalent DELETE statement is shown here:

    DELETE FROM t1 WHERE
        (year_col >= 1991 AND year_col < 1995)
        OR
        (year_col >= 2003 AND year_col < 2007);
                                            

    If you use the ALL keyword in place of the list of partition names, the statement acts on all table partitions.

    TRUNCATE PARTITION merely deletes rows; it does not alter the definition of the table itself, or of any of its partitions.

    To verify that the rows were dropped, check the INFORMATION_SCHEMA.PARTITIONS table, using a query such as this one:

    SELECT PARTITION_NAME, TABLE_ROWS
        FROM INFORMATION_SCHEMA.PARTITIONS
        WHERE TABLE_NAME = 't1';
                                            

    COALESCE PARTITION can be used with a table that is partitioned by HASH or KEY to reduce the number of partitions by number . Suppose that you have created table t2 as follows:

    CREATE TABLE t2 (
        name VARCHAR (30),
        started DATE
    )
    PARTITION BY HASH( YEAR(started) )
    PARTITIONS 6;
                                            

    To reduce the number of partitions used by t2 from 6 to 4, use the following statement:

    ALTER TABLE t2 COALESCE PARTITION 2;
                                            

    The data contained in the last number partitions will be merged into the remaining partitions. In this case, partitions 4 and 5 will be merged into the first 4 partitions (the partitions numbered 0, 1, 2, and 3).

    To change some but not all the partitions used by a partitioned table, you can use REORGANIZE PARTITION . This statement can be used in several ways:

    • To merge a set of partitions into a single partition. This is done by naming several partitions in the partition_names list and supplying a single definition for partition_definition .

    • To split an existing partition into several partitions. Accomplish this by naming a single partition for partition_names and providing multiple partition_definitions .

    • To change the ranges for a subset of partitions defined using VALUES LESS THAN or the value lists for a subset of partitions defined using VALUES IN .

    Note

    For partitions that have not been explicitly named, MySQL automatically provides the default names p0 , p1 , p2 , and so on. The same is true with regard to subpartitions.

    For more detailed information about and examples of ALTER TABLE ... REORGANIZE PARTITION statements, see Section 23.3.1, “Management of RANGE and LIST Partitions” .

  • To exchange a table partition or subpartition with a table, use the ALTER TABLE ... EXCHANGE PARTITION statement—that is, to move any existing rows in the partition or subpartition to the nonpartitioned table, and any existing rows in the nonpartitioned table to the table partition or subpartition.

    For usage information and examples, see Section 23.3.3, “Exchanging Partitions and Subpartitions with Tables” .

  • Several options provide partition maintenance and repair functionality analogous to that implemented for nonpartitioned tables by statements such as CHECK TABLE and REPAIR TABLE (which are also supported for partitioned tables; for more information, see Section 13.7.3, “Table Maintenance Statements” ). These include ANALYZE PARTITION , CHECK PARTITION , OPTIMIZE PARTITION , REBUILD PARTITION , and REPAIR PARTITION . Each of these options takes a partition_names clause consisting of one or more names of partitions, separated by commas. The partitions must already exist in the target table. You can also use the ALL keyword in place of partition_names , in which case the statement acts on all table partitions. For more information and examples, see Section 23.3.4, “Maintenance of Partitions” .

    InnoDB does not currently support per-partition optimization; ALTER TABLE ... OPTIMIZE PARTITION causes the entire table to rebuilt and analyzed, and an appropriate warning to be issued. (Bug #11751825, Bug #42822) To work around this problem, use ALTER TABLE ... REBUILD PARTITION and ALTER TABLE ... ANALYZE PARTITION instead.

    The ANALYZE PARTITION , CHECK PARTITION , OPTIMIZE PARTITION , and REPAIR PARTITION options are not supported for tables which are not partitioned.

  • ALTER TABLE ... UPGRADE PARTITIONING attempts to upgrade a partitioned InnoDB table that was created with the old generic partitioning handler to the InnoDB native partitioning employed in MySQL 8.0. In addition, the mysql_upgrade utility checks for such partitioned InnoDB tables and attempts to upgrade them to native partitioning as part of its normal operations.

  • REMOVE PARTITIONING enables you to remove a table's partitioning without otherwise affecting the table or its data. This option can be combined with other ALTER TABLE options such as those used to add, drop, or rename columns or indexes.

  • Using the ENGINE option with ALTER TABLE changes the storage engine used by the table without affecting the partitioning. The target storage engine must provide its own partitioning handler. Only the InnoDB and NDB storage engines have native partitioning handlers; NDB is not currently supported in MySQL 8.0.

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

The ADD PARTITION , DROP PARTITION , COALESCE PARTITION , REORGANIZE PARTITION , ANALYZE PARTITION , CHECK PARTITION , and REPAIR PARTITION options cannot be combined with other alter specifications in a single ALTER TABLE , since the options just listed act on individual partitions. For more information, see Section 13.1.9.1, “ALTER TABLE Partition Operations” .

Only a single instance of any one of the following options can be used in a given ALTER TABLE statement: PARTITION BY , ADD PARTITION , DROP PARTITION , TRUNCATE PARTITION , EXCHANGE PARTITION , REORGANIZE PARTITION , or COALESCE PARTITION , ANALYZE PARTITION , CHECK PARTITION , OPTIMIZE PARTITION , REBUILD PARTITION , REMOVE PARTITIONING .

For example, the following two statements are invalid:

ALTER TABLE t1 ANALYZE PARTITION p1, ANALYZE PARTITION p2;
ALTER TABLE t1 ANALYZE PARTITION p1, CHECK PARTITION p2;
                            

In the first case, you can analyze partitions p1 and p2 of table t1 concurrently using a single statement with a single ANALYZE PARTITION option that lists both of the partitions to be analyzed, like this:

ALTER TABLE t1 ANALYZE PARTITION p1, p2;
                            

In the second case, it is not possible to perform ANALYZE and CHECK operations on different partitions of the same table concurrently. Instead, you must issue two separate statements, like this:

ALTER TABLE t1 ANALYZE PARTITION p1;
ALTER TABLE t1 CHECK PARTITION p2;
                            

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

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

For more information about these statements, see Section 23.3.4, “Maintenance of Partitions” .

13.1.9.2 ALTER TABLE and Generated Columns

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

  • Generated columns can be added.

    CREATE TABLE t1 (c1 INT);
    ALTER TABLE t1 ADD COLUMN c2 INT GENERATED ALWAYS AS (c1 + 1) STORED;
                                            
  • The data type and expression of generated columns can be modified.

    CREATE TABLE t1 (c1 INT, c2 INT GENERATED ALWAYS AS (c1 + 1) STORED);
    ALTER TABLE t1 MODIFY COLUMN c2 TINYINT GENERATED ALWAYS AS (c1 + 5) STORED;
                                            
  • Generated columns can be renamed or dropped, if no other column refers to them.

    CREATE TABLE t1 (c1 INT, c2 INT GENERATED ALWAYS AS (c1 + 1) STORED);
    ALTER TABLE t1 CHANGE c2 c3 INT GENERATED ALWAYS AS (c1 + 1) STORED;
    ALTER TABLE t1 DROP COLUMN c3;
                                            
  • Virtual generated columns cannot be altered to stored generated columns, or vice versa. To work around this, drop the column, then add it with the new definition.

    CREATE TABLE t1 (c1 INT, c2 INT GENERATED ALWAYS AS (c1 + 1) VIRTUAL);
    ALTER TABLE t1 DROP COLUMN c2;
    ALTER TABLE t1 ADD COLUMN c2 INT GENERATED ALWAYS AS (c1 + 1) STORED;
                                            
  • Nongenerated columns can be altered to stored but not virtual generated columns.

    CREATE TABLE t1 (c1 INT, c2 INT);
    ALTER TABLE t1 MODIFY COLUMN c2 INT GENERATED ALWAYS AS (c1 + 1) STORED;
                                            
  • Stored but not virtual generated columns can be altered to nongenerated columns. The stored generated values become the values of the nongenerated column.

    CREATE TABLE t1 (c1 INT, c2 INT GENERATED ALWAYS AS (c1 + 1) STORED);
    ALTER TABLE t1 MODIFY COLUMN c2 INT;
                                            
  • ADD COLUMN is not an in-place operation for stored columns (done without using a temporary table) because the expression must be evaluated by the server. For stored columns, indexing changes are done in place, and expression changes are not done in place. Changes to column comments are done in place.

  • For non-partitioned tables, ADD COLUMN and DROP COLUMN are in-place operations for virtual columns. However, adding or dropping a virtual column cannot be performed in place in combination with other ALTER TABLE operations.

    For partitioned tables, ADD COLUMN and DROP COLUMN are not in-place operations for virtual columns.

  • InnoDB supports secondary indexes on virtual generated columns. Adding or dropping a secondary index on a virtual generated column is an in-place operation. For more information, see Section 13.1.20.10, “Secondary Indexes and Generated Columns” .

  • When a VIRTUAL generated column is added to a table or modified, it is not ensured that data being calculated by the generated column expression will not be out of range for the column. This can lead to inconsistent data being returned and unexpectedly failed statements. To permit control over whether validation occurs for such columns, ALTER TABLE supports WITHOUT VALIDATION and WITH VALIDATION clauses:

    • With WITHOUT VALIDATION (the default if neither clause is specified), an in-place operation is performed (if possible), data integrity is not checked, and the statement finishes more quickly. However, later reads from the table might report warnings or errors for the column if values are out of range.

    • With WITH VALIDATION , ALTER TABLE copies the table. If an out-of-range or any other error occurs, the statement fails. Because a table copy is performed, the statement takes longer.

    WITHOUT VALIDATION and WITH VALIDATION are permitted only with ADD COLUMN , CHANGE COLUMN , and MODIFY COLUMN operations. Otherwise, an ER_WRONG_USAGE error occurs.

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

  • An ALTER TABLE statement that changes the default value of a column col_name may also change the value of a generated column expression that refers to the column using col_name , which may change the value of a generated column expression that refers to the column using DEFAULT( col_name ) . For this reason, ALTER TABLE operations that change the definition of a column cause a table rebuild if any generated column expression uses DEFAULT() .

13.1.9.3 ALTER TABLE Examples

Begin with a table t1 created as shown here:

CREATE TABLE t1 (a INTEGER, b CHAR(10));
                            

To rename the table from t1 to t2 :

ALTER TABLE t1 RENAME t2;
                            

To change column a from INTEGER to TINYINT NOT NULL (leaving the name the same), and to change column b from CHAR(10) to CHAR(20) as well as renaming it from b to c :

ALTER TABLE t2 MODIFY a TINYINT NOT NULL, CHANGE b c CHAR(20);
                            

To add a new TIMESTAMP column named d :

ALTER TABLE t2 ADD d TIMESTAMP;
                            

To add an index on column d and a UNIQUE index on column a :

ALTER TABLE t2 ADD INDEX (d), ADD UNIQUE (a);
                            

To remove column c :

ALTER TABLE t2 DROP COLUMN c;
                            

To add a new AUTO_INCREMENT integer column named c :

ALTER TABLE t2 ADD c INT UNSIGNED NOT NULL AUTO_INCREMENT,
  ADD PRIMARY KEY (c);
                            

We indexed c (as a PRIMARY KEY ) because AUTO_INCREMENT columns must be indexed, and we declare c as NOT NULL because primary key columns cannot be NULL .

For NDB tables, it is also possible to change the storage type used for a table or column. For example, consider an NDB table created as shown here:

mysql> CREATE TABLE t1 (c1 INT) TABLESPACE ts_1 ENGINE NDB;
Query OK, 0 rows affected (1.27 sec)
                            

To convert this table to disk-based storage, you can use the following ALTER TABLE statement:

mysql> ALTER TABLE t1 TABLESPACE ts_1 STORAGE DISK;
Query OK, 0 rows affected (2.99 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> SHOW CREATE TABLE t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `c1` int(11) DEFAULT NULL
) /*!50100 TABLESPACE ts_1 STORAGE DISK */
ENGINE=ndbcluster DEFAULT CHARSET=latin1
1 row in set (0.01 sec)
                            

It is not necessary that the tablespace was referenced when the table was originally created; however, the tablespace must be referenced by the ALTER TABLE :

mysql> CREATE TABLE t2 (c1 INT) ts_1 ENGINE NDB;
Query OK, 0 rows affected (1.00 sec)
mysql> ALTER TABLE t2 STORAGE DISK;
ERROR 1005 (HY000): Can't create table 'c.#sql-1750_3' (errno: 140)
mysql> ALTER TABLE t2 TABLESPACE ts_1 STORAGE DISK;
Query OK, 0 rows affected (3.42 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> SHOW CREATE TABLE t2\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t2` (
  `c1` int(11) DEFAULT NULL
) /*!50100 TABLESPACE ts_1 STORAGE DISK */
ENGINE=ndbcluster DEFAULT CHARSET=latin1
1 row in set (0.01 sec)
                            

To change the storage type of an individual column, you can use ALTER TABLE ... MODIFY [COLUMN] . For example, suppose you create an NDB Cluster Disk Data table with two columns, using this CREATE TABLE statement:

mysql> CREATE TABLE t3 (c1 INT, c2 INT)
    ->     TABLESPACE ts_1 STORAGE DISK ENGINE NDB;
Query OK, 0 rows affected (1.34 sec)
                            

To change column c2 from disk-based to in-memory storage, include a STORAGE MEMORY clause in the column definition used by the ALTER TABLE statement, as shown here:

mysql> ALTER TABLE t3 MODIFY c2 INT STORAGE MEMORY;
Query OK, 0 rows affected (3.14 sec)
Records: 0  Duplicates: 0  Warnings: 0
                            

You can make an in-memory column into a disk-based column by using STORAGE DISK in a similar fashion.

Column c1 uses disk-based storage, since this is the default for the table (determined by the table-level STORAGE DISK clause in the CREATE TABLE statement). However, column c2 uses in-memory storage, as can be seen here in the output of SHOW CREATE TABLE :

mysql> SHOW CREATE TABLE t3\G
*************************** 1. row ***************************
       Table: t3
Create Table: CREATE TABLE `t3` (
  `c1` int(11) DEFAULT NULL,
  `c2` int(11) /*!50120 STORAGE MEMORY */ DEFAULT NULL
) /*!50100 TABLESPACE ts_1 STORAGE DISK */ ENGINE=ndbcluster DEFAULT CHARSET=latin1
1 row in set (0.02 sec)
                            

When you add an AUTO_INCREMENT column, column values are filled in with sequence numbers automatically. For MyISAM tables, you can set the first sequence number by executing SET INSERT_ID= value before ALTER TABLE or by using the AUTO_INCREMENT= value table option.

With MyISAM tables, if you do not change the AUTO_INCREMENT column, the sequence number is not affected. If you drop an AUTO_INCREMENT column and then add another AUTO_INCREMENT column, the numbers are resequenced beginning with 1.

When replication is used, adding an AUTO_INCREMENT column to a table might not produce the same ordering of the rows on the slave and the master. This occurs because the order in which the rows are numbered depends on the specific storage engine used for the table and the order in which the rows were inserted. If it is important to have the same order on the master and slave, the rows must be ordered before assigning an AUTO_INCREMENT number. Assuming that you want to add an AUTO_INCREMENT column to the table t1 , the following statements produce a new table t2 identical to t1 but with an AUTO_INCREMENT column:

CREATE TABLE t2 (id INT AUTO_INCREMENT PRIMARY KEY)
SELECT * FROM t1 ORDER BY col1, col2;
                            

This assumes that the table t1 has columns col1 and col2 .

This set of statements will also produce a new table t2 identical to t1 , with the addition of an AUTO_INCREMENT column:

CREATE TABLE t2 LIKE t1;
ALTER TABLE t2 ADD id INT AUTO_INCREMENT PRIMARY KEY;
INSERT INTO t2 SELECT * FROM t1 ORDER BY col1, col2;
                            
Important

To guarantee the same ordering on both master and slave, all columns of t1 must be referenced in the ORDER BY clause.

Regardless of the method used to create and populate the copy having the AUTO_INCREMENT column, the final step is to drop the original table and then rename the copy:

DROP TABLE t1;
ALTER TABLE t2 RENAME t1;
                            

13.1.10 ALTER TABLESPACE Syntax

ALTER [UNDO] TABLESPACE tablespace_name
  NDB only:
    {ADD|DROP} DATAFILE 'file_name'
    [INITIAL_SIZE [=] size]
    [WAIT]
  InnoDB and NDB:
    [RENAME TO tablespace_name]
  InnoDB only:
    [SET {ACTIVE|INACTIVE}]
    [ENCRYPTION [=] {'Y' | 'N'}]
  InnoDB and NDB:
    [ENGINE [=] engine_name]
                        

This statement is used with NDB and InnoDB tablespaces. It can be used to add a new data file to, or to drop a data file from an NDB tablespace. It can also be used to rename an NDB Cluster Disk Data tablespace, rename an InnoDB general tablespace, encrypt an InnoDB general tablespace, or mark an InnoDB undo tablespace as active or inactive.

The UNDO keyword, introduced in MySQL 8.0.14, is used with the SET {ACTIVE|INACTIVE} clause to mark an InnoDB undo tablespace as active or inactive. For more information, see Section 15.6.3.4, “Undo Tablespaces” .

The ADD DATAFILE variant enables you to specify an initial size for an NDB Disk Data tablespace using an INITIAL_SIZE clause, where size is measured in bytes; the default value is 134217728 (128 MB). You may optionally follow size with a one-letter abbreviation for an order of magnitude, similar to those used in my.cnf . Generally, this is one of the letters M (megabytes) or G (gigabytes).

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

INITIAL_SIZE is rounded, explicitly, as for CREATE TABLESPACE .

Once a data file has been created, its size cannot be changed; however, you can add more data files to an NDB tablespace using additional ALTER TABLESPACE ... ADD DATAFILE statements.

When ALTER TABLESPACE ... ADD DATAFILE is used with ENGINE = NDB , a data file is created on each Cluster data node, but only one row is generated in the INFORMATION_SCHEMA.FILES table. See the description of this table, as well as Section 22.5.13.1, “NDB Cluster Disk Data Objects” , for more information. ADD DATAFILE is not supported with InnoDB tablespaces.

Using DROP DATAFILE with ALTER TABLESPACE drops the data file ' file_name ' from an NDB tablespace. You cannot drop a data file from a tablespace which is in use by any table; in other words, the data file must be empty (no extents used). See Section 22.5.13.1, “NDB Cluster Disk Data Objects” . In addition, any data file to be dropped must previously have been added to the tablespace with CREATE TABLESPACE or ALTER TABLESPACE . DROP DATAFILE is not supported with InnoDB tablespaces.

WAIT is parsed but otherwise ignored. It is intended for future expansion.

The ENGINE clause, which specifies the storage engine used by the tablespace, is deprecated and will be removed in a future release. The tablespace storage engine is known by the data dictionary, making the ENGINE clause obsolete. If the storage engine is specified, it must match the tablespace storage engine defined in the data dictionary. The only values for engine_name compatible with NDB tablespaces are NDB and NDBCLUSTER .

RENAME TO operations are implicitly performed in autocommit mode, regardless of the autocommit setting.

A RENAME TO operation cannot be performed while LOCK TABLES or FLUSH TABLES WITH READ LOCK is in effect for tables that reside in the tablespace.

Exclusive metadata locks are taken on tables that reside in a general tablespace while the tablespace is renamed, which prevents concurrent DDL. Concurrent DML is supported.

The CREATE TABLESPACE privilege is required to rename an InnoDB general tablespace.

The ENCRYPTION option is used to enable or disable page-level data encryption for an InnoDB general tablespace or the mysql system tablespace. Option values are not case-sensitive. Encryption support for general tablespaces was introduced in MySQL 8.0.13. Encryption support for the mysql system tablespace is available as of MySQL 8.0.16. A keyring plugin must be installed and configured to encrypt a tablespace using the ENCRYPTION option.

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

The INPLACE algorithm is used when altering the ENCRYPTION attribute of a general tablespace or the mysql system tablespace. The INPLACE algorithm permits concurrent DML on tables that reside in the tablespace. Concurrent DDL is blocked.

更多信息,请见 Section 15.6.3.9, “Tablespace Encryption” .

13.1.11 ALTER VIEW Syntax

ALTER
    [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
    [DEFINER = { user | CURRENT_USER }]
    [SQL SECURITY { DEFINER | INVOKER }]
    VIEW view_name [(column_list)]
    AS select_statement
    [WITH [CASCADED | LOCAL] CHECK OPTION]
                        

This statement changes the definition of a view, which must exist. The syntax is similar to that for CREATE VIEW see Section 13.1.23, “CREATE VIEW Syntax” ). This statement requires the CREATE VIEW and DROP privileges for the view, and some privilege for each column referred to in the SELECT statement. ALTER VIEW is permitted only to the definer or users with the SET_USER_ID or SUPER privilege.

13.1.12 CREATE DATABASE Syntax

CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name
    [create_specification] ...
create_specification:
    [DEFAULT] CHARACTER SET [=] charset_name
  | [DEFAULT] COLLATE [=] collation_name
                        

CREATE DATABASE creates a database with the given name. To use this statement, you need the CREATE privilege for the database. CREATE SCHEMA is a synonym for CREATE DATABASE .

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

CREATE DATABASE is not permitted within a session that has an active LOCK TABLES statement.

create_specification options specify database characteristics. Database characteristics are stored in the data dictionary. The CHARACTER SET clause specifies the default database character set. The COLLATE clause specifies the default database collation. Chapter 10, Character Sets, Collations, Unicode , discusses character set and collation names.

A database in MySQL is implemented as a directory containing files that correspond to tables in the database. Because there are no tables in a database when it is initially created, the CREATE DATABASE statement creates only a directory under the MySQL data directory. Rules for permissible database names are given in Section 9.2, “Schema Object Names” . If a database name contains special characters, the name for the database directory contains encoded versions of those characters as described in Section 9.2.3, “Mapping of Identifiers to File Names” .

Creating a database directory by manually creating a directory under the data directory (for example, with mkdir ) is unsupported in MySQL 8.0.

You can also use the mysqladmin program to create databases. See Section 4.5.2, “ mysqladmin — Client for Administering a MySQL Server” .

13.1.13 CREATE EVENT Syntax

CREATE
    [DEFINER = { user | CURRENT_USER }]
    EVENT
    [IF NOT EXISTS]
    event_name
    ON SCHEDULE schedule
    [ON COMPLETION [NOT] PRESERVE]
    [ENABLE | DISABLE | DISABLE ON SLAVE]
    [COMMENT 'string']
    DO event_body;
schedule:
    AT timestamp [+ INTERVAL interval] ...
  | EVERY interval
    [STARTS timestamp [+ INTERVAL interval] ...]
    [ENDS timestamp [+ INTERVAL interval] ...]
interval:
    quantity {YEAR | QUARTER | MONTH | DAY | HOUR | MINUTE |
              WEEK | SECOND | YEAR_MONTH | DAY_HOUR | DAY_MINUTE |
              DAY_SECOND | HOUR_MINUTE | HOUR_SECOND | MINUTE_SECOND}
                        

This statement creates and schedules a new event. The event will not run unless the Event Scheduler is enabled. For information about checking Event Scheduler status and enabling it if necessary, see Section 24.4.2, “Event Scheduler Configuration” .

CREATE EVENT requires the EVENT privilege for the schema in which the event is to be created. It might also require the SET_USER_ID or SUPER privilege, depending on the DEFINER value, as described later in this section.

The minimum requirements for a valid CREATE EVENT statement are as follows:

  • The keywords CREATE EVENT plus an event name, which uniquely identifies the event in a database schema.

  • An ON SCHEDULE clause, which determines when and how often the event executes.

  • A DO clause, which contains the SQL statement to be executed by an event.

This is an example of a minimal CREATE EVENT statement:

CREATE EVENT myevent
    ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 HOUR
    DO
      UPDATE myschema.mytable SET mycol = mycol + 1;
                        

The previous statement creates an event named myevent . This event executes once—one hour following its creation—by running an SQL statement that increments the value of the myschema.mytable table's mycol column by 1.

The event_name must be a valid MySQL identifier with a maximum length of 64 characters. Event names are not case-sensitive, so you cannot have two events named myevent and MyEvent in the same schema. In general, the rules governing event names are the same as those for names of stored routines. See Section 9.2, “Schema Object Names” .

An event is associated with a schema. If no schema is indicated as part of event_name , the default (current) schema is assumed. To create an event in a specific schema, qualify the event name with a schema using schema_name . event_name syntax.

The DEFINER clause specifies the MySQL account to be used when checking access privileges at event execution time. If a user value is given, it should be a MySQL account specified as ' user_name '@' host_name ' , CURRENT_USER , or CURRENT_USER() . The default DEFINER value is the user who executes the CREATE EVENT statement. This is the same as specifying DEFINER = CURRENT_USER explicitly.

If you specify the DEFINER clause, these rules determine the valid DEFINER user values:

  • If you do not have the SET_USER_ID or SUPER privilege, the only permitted user value is your own account, either specified literally or by using CURRENT_USER . You cannot set the definer to some other account.

  • If you have the SET_USER_ID or SUPER privilege, you can specify any syntactically valid account name. If the account does not exist, a warning is generated.

  • Although it is possible to create an event with a nonexistent DEFINER account, an error occurs at event execution time if the account does not exist.

For more information about event security, see Section 24.6, “Access Control for Stored Programs and Views” .

Within an event, the CURRENT_USER() function returns the account used to check privileges at event execution time, which is the DEFINER user. For information about user auditing within events, see Section 6.3.13, “SQL-Based MySQL Account Activity Auditing” .

IF NOT EXISTS has the same meaning for CREATE EVENT as for CREATE TABLE : If an event named event_name already exists in the same schema, no action is taken, and no error results. (However, a warning is generated in such cases.)

The ON SCHEDULE clause determines when, how often, and for how long the event_body defined for the event repeats. This clause takes one of two forms:

  • AT timestamp is used for a one-time event. It specifies that the event executes one time only at the date and time given by timestamp , which must include both the date and time, or must be an expression that resolves to a datetime value. You may use a value of either the DATETIME or TIMESTAMP type for this purpose. If the date is in the past, a warning occurs, as shown here:

    mysql> SELECT NOW();
    +---------------------+
    | NOW()               |
    +---------------------+
    | 2006-02-10 23:59:01 |
    +---------------------+
    1 row in set (0.04 sec)
    mysql> CREATE EVENT e_totals
        ->     ON SCHEDULE AT '2006-02-10 23:59:00'
        ->     DO INSERT INTO test.totals VALUES (NOW());
    Query OK, 0 rows affected, 1 warning (0.00 sec)
    mysql> SHOW WARNINGS\G
    *************************** 1. row ***************************
      Level: Note
       Code: 1588
    Message: Event execution time is in the past and ON COMPLETION NOT
             PRESERVE is set. The event was dropped immediately after
             creation.
                                        

    CREATE EVENT statements which are themselves invalid—for whatever reason—fail with an error.

    You may use CURRENT_TIMESTAMP to specify the current date and time. In such a case, the event acts as soon as it is created.

    To create an event which occurs at some point in the future relative to the current date and time—such as that expressed by the phrase three weeks from now —you can use the optional clause + INTERVAL interval . The interval portion consists of two parts, a quantity and a unit of time, and follows the syntax rules described in Temporal Intervals , except that you cannot use any units keywords that involving microseconds when defining an event. With some interval types, complex time units may be used. For example, two minutes and ten seconds can be expressed as + INTERVAL '2:10' MINUTE_SECOND .

    You can also combine intervals. For example, AT CURRENT_TIMESTAMP + INTERVAL 3 WEEK + INTERVAL 2 DAY is equivalent to three weeks and two days from now . Each portion of such a clause must begin with + INTERVAL .

  • To repeat actions at a regular interval, use an EVERY clause. The EVERY keyword is followed by an interval as described in the previous discussion of the AT keyword. ( + INTERVAL is not used with EVERY .) For example, EVERY 6 WEEK means every six weeks .

    Although + INTERVAL clauses are not permitted in an EVERY clause, you can use the same complex time units permitted in a + INTERVAL .

    An EVERY clause may contain an optional STARTS clause. STARTS is followed by a timestamp value that indicates when the action should begin repeating, and may also use + INTERVAL interval to specify an amount of time from now . For example, EVERY 3 MONTH STARTS CURRENT_TIMESTAMP + INTERVAL 1 WEEK means every three months, beginning one week from now . Similarly, you can express every two weeks, beginning six hours and fifteen minutes from now as EVERY 2 WEEK STARTS CURRENT_TIMESTAMP + INTERVAL '6:15' HOUR_MINUTE . Not specifying STARTS is the same as using STARTS CURRENT_TIMESTAMP —that is, the action specified for the event begins repeating immediately upon creation of the event.

    An EVERY clause may contain an optional ENDS clause. The ENDS keyword is followed by a timestamp value that tells MySQL when the event should stop repeating. You may also use + INTERVAL interval with ENDS ; for instance, EVERY 12 HOUR STARTS CURRENT_TIMESTAMP + INTERVAL 30 MINUTE ENDS CURRENT_TIMESTAMP + INTERVAL 4 WEEK is equivalent to every twelve hours, beginning thirty minutes from now, and ending four weeks from now . Not using ENDS means that the event continues executing indefinitely.

    ENDS supports the same syntax for complex time units as STARTS does.

    You may use STARTS , ENDS , both, or neither in an EVERY clause.

    If a repeating event does not terminate within its scheduling interval, the result may be multiple instances of the event executing simultaneously. If this is undesirable, you should institute a mechanism to prevent simultaneous instances. For example, you could use the GET_LOCK() function, or row or table locking.

The ON SCHEDULE clause may use expressions involving built-in MySQL functions and user variables to obtain any of the timestamp or interval values which it contains. You may not use stored functions or user-defined functions in such expressions, nor may you use any table references; however, you may use SELECT FROM DUAL . This is true for both CREATE EVENT and ALTER EVENT statements. References to stored functions, user-defined functions, and tables in such cases are specifically not permitted, and fail with an error (see Bug #22830).

Times in the ON SCHEDULE clause are interpreted using the current session time_zone value. This becomes the event time zone; that is, the time zone that is used for event scheduling and is in effect within the event as it executes. These times are converted to UTC and stored along with the event time zone in the mysql.event table. This enables event execution to proceed as defined regardless of any subsequent changes to the server time zone or daylight saving time effects. For additional information about representation of event times, see Section 24.4.4, “Event Metadata” . See also Section 13.7.6.18, “SHOW EVENTS Syntax” , and Section 25.10, “The INFORMATION_SCHEMA EVENTS Table” .

Normally, once an event has expired, it is immediately dropped. You can override this behavior by specifying ON COMPLETION PRESERVE . Using ON COMPLETION NOT PRESERVE merely makes the default nonpersistent behavior explicit.

You can create an event but prevent it from being active using the DISABLE keyword. Alternatively, you can use ENABLE to make explicit the default status, which is active. This is most useful in conjunction with ALTER EVENT (see Section 13.1.3, “ALTER EVENT Syntax” ).

A third value may also appear in place of ENABLE or DISABLE ; DISABLE ON SLAVE is set for the status of an event on a replication slave to indicate that the event was created on the master and replicated to the slave, but is not executed on the slave. See Section 17.4.1.16, “Replication of Invoked Features” .

You may supply a comment for an event using a COMMENT clause. comment may be any string of up to 64 characters that you wish to use for describing the event. The comment text, being a string literal, must be surrounded by quotation marks.

The DO clause specifies an action carried by the event, and consists of an SQL statement. Nearly any valid MySQL statement that can be used in a stored routine can also be used as the action statement for a scheduled event. (See Section C.1, “Restrictions on Stored Programs” .) For example, the following event e_hourly deletes all rows from the sessions table once per hour, where this table is part of the site_activity schema:

CREATE EVENT e_hourly
    ON SCHEDULE
      EVERY 1 HOUR
    COMMENT 'Clears out sessions table each hour.'
    DO
      DELETE FROM site_activity.sessions;
                        

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

A CREATE EVENT statement that contains an ALTER EVENT statement in its DO clause appears to succeed; however, when the server attempts to execute the resulting scheduled event, the execution fails with an error.

Note

Statements such as SELECT or SHOW that merely return a result set have no effect when used in an event; the output from these is not sent to the MySQL Monitor, nor is it stored anywhere. However, you can use statements such as SELECT ... INTO and INSERT INTO ... SELECT that store a result. (See the next example in this section for an instance of the latter.)

The schema to which an event belongs is the default schema for table references in the DO clause. Any references to tables in other schemas must be qualified with the proper schema name.

As with stored routines, you can use compound-statement syntax in the DO clause by using the BEGIN and END keywords, as shown here:

delimiter |
CREATE EVENT e_daily
    ON SCHEDULE
      EVERY 1 DAY
    COMMENT 'Saves total number of sessions then clears the table each day'
    DO
      BEGIN
        INSERT INTO site_activity.totals (time, total)
          SELECT CURRENT_TIMESTAMP, COUNT(*)
            FROM site_activity.sessions;
        DELETE FROM site_activity.sessions;
      END |
delimiter ;
                        

This example uses the delimiter command to change the statement delimiter. See Section 24.1, “Defining Stored Programs” .

More complex compound statements, such as those used in stored routines, are possible in an event. This example uses local variables, an error handler, and a flow control construct:

delimiter |
CREATE EVENT e
    ON SCHEDULE
      EVERY 5 SECOND
    DO
      BEGIN
        DECLARE v INTEGER;
        DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN END;
        SET v = 0;
        WHILE v < 5 DO
          INSERT INTO t1 VALUES (0);
          UPDATE t2 SET s1 = s1 + 1;
          SET v = v + 1;
        END WHILE;
    END |
delimiter ;
                        

There is no way to pass parameters directly to or from events; however, it is possible to invoke a stored routine with parameters within an event:

CREATE EVENT e_call_myproc
    ON SCHEDULE
      AT CURRENT_TIMESTAMP + INTERVAL 1 DAY
    DO CALL myproc(5, 27);
                        

If an event's definer has privileges sufficient to set global system variables (see Section 5.1.9.1, “System Variable Privileges” ), the event can read and write global variables. As granting such privileges entails a potential for abuse, extreme care must be taken in doing so.

Generally, any statements that are valid in stored routines may be used for action statements executed by events. For more information about statements permissible within stored routines, see Section 24.2.1, “Stored Routine Syntax” . You can create an event as part of a stored routine, but an event cannot be created by another event.

13.1.14 CREATE FUNCTION Syntax

The CREATE FUNCTION statement is used to create stored functions and user-defined functions (UDFs):

13.1.15 CREATE INDEX Syntax

CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name
    [index_type]
    ON tbl_name (key_part,...)
    [index_option]
    [algorithm_option | lock_option] ...
key_part: {col_name [(length)] | (expr)} [ASC | DESC]
index_option:
    KEY_BLOCK_SIZE [=] value
  | index_type
  | WITH PARSER parser_name
  | COMMENT 'string'
  | {VISIBLE | INVISIBLE}
index_type:
    USING {BTREE | HASH}
algorithm_option:
    ALGORITHM [=] {DEFAULT | INPLACE | COPY}
lock_option:
    LOCK [=] {DEFAULT | NONE | SHARED | EXCLUSIVE}
                        

Normally, you create all indexes on a table at the time the table itself is created with CREATE TABLE . See Section 13.1.20, “CREATE TABLE Syntax” . This guideline is especially important for InnoDB tables, where the primary key determines the physical layout of rows in the data file. CREATE INDEX enables you to add indexes to existing tables.

CREATE INDEX is mapped to an ALTER TABLE statement to create indexes. See Section 13.1.9, “ALTER TABLE Syntax” . CREATE INDEX cannot be used to create a PRIMARY KEY ; use ALTER TABLE instead. For more information about indexes, see Section 8.3.1, “How MySQL Uses Indexes” .

InnoDB supports secondary indexes on virtual columns. For more information, see Section 13.1.20.10, “Secondary Indexes and Generated Columns” .

When the innodb_stats_persistent setting is enabled, run the ANALYZE TABLE statement for an InnoDB table after creating an index on that table.

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

A key_part specification can end with ASC or DESC to specify whether index values are stored in ascending or descending order. The default is ascending if no order specifier is given. ASC and DESC are not permitted for HASH indexes. As of MySQL 8.0.12, ASC and DESC are not permitted for SPATIAL indexes.

The following sections describe different aspects of the CREATE INDEX statement:

Column Prefix Key Parts

For string columns, indexes can be created that use only the leading part of column values, using col_name ( length ) syntax to specify an index prefix length:

If a specified index prefix exceeds the maximum column data type size, CREATE INDEX handles the index as follows:

  • For a nonunique index, either an error occurs (if strict SQL mode is enabled), or the index length is reduced to lie within the maximum column data type size and a warning is produced (if strict SQL mode is not enabled).

  • For a unique index, an error occurs regardless of SQL mode because reducing the index length might enable insertion of nonunique entries that do not meet the specified uniqueness requirement.

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

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

If names in the column usually differ in the first 10 characters, lookups performed using this index should not be much slower than using an index created from the entire name column. Also, using column prefixes for indexes can make the index file much smaller, which could save a lot of disk space and might also speed up INSERT operations.

Functional Key Parts

A normal index indexes column values or prefixes of column values. For example, in the following table, the index entry for a given t1 row includes the full col1 value and a prefix of the col2 value consisting of its first 10 bytes:

CREATE TABLE t1 (
  col1 VARCHAR(10),
  col2 VARCHAR(20),
  INDEX (col1, col2(10))
);
                            

MySQL 8.0.13 and higher supports functional key parts that index expression values rather than column or column prefix values. Use of functional key parts enables indexing of values not stored directly in the table. Examples:

CREATE TABLE t1 (col1 INT, col2 INT, INDEX func_index ((ABS(col1))));
CREATE INDEX idx1 ON t1 ((col1 + col2));
CREATE INDEX idx2 ON t1 ((col1 + col2), (col1 - col2), col1);
ALTER TABLE t1 ADD INDEX ((col1 * 40) DESC);
                            

An index with multiple key parts can mix nonfunctional and functional key parts.

ASC and DESC are supported for functional key parts.

Functional key parts must adhere to the following rules. An error occurs if a key part definition contains disallowed constructs.

  • In index definitions, enclose expressions within parentheses to distinguish them from columns or column prefixes. For example, this is permitted; the expressions are enclosed within parentheses:

    INDEX ((col1 + col2), (col3 - col4))
                                            

    This produces an error; the expressions are not enclosed within parentheses:

    INDEX (col1 + col2, col3 - col4)
                                            
  • A functional key part cannot consist solely of a column name. For example, this is not permitted:

    INDEX ((col1), (col2))
                                            

    Instead, write the key parts as nonfunctional key parts, without parentheses:

    INDEX (col1, col2)
                                            
  • A functional key part expression cannot refer to column prefixes. For a workaround, see the discussion of SUBSTRING() and CAST() later in this section.

  • Functional key parts are not permitted in foreign key specifications.

For CREATE TABLE ... LIKE , the destination table preserves functional key parts from the original table.

Functional indexes are implemented as hidden virtual generated columns, which has these implications:

UNIQUE is supported for indexes that include functional key parts. However, primary keys cannot include functional key parts. A primary key requires the generated column to be stored, but functional key parts are implemented as virtual generated columns, not stored generated columns.

SPATIAL and FULLTEXT indexes cannot have functional key parts.

If a table contains no primary key, InnoDB automatically promotes the first UNIQUE NOT NULL index to the primary key. This is not supported for UNIQUE NOT NULL indexes that have functional key parts.

Nonfunctional indexes raise a warning if there are duplicate indexes. Indexes that contain functional key parts do not have this feature.

To remove a column that is referenced by a functional key part, the index must be removed first. Otherwise, an error occurs.

Although nonfunctional key parts support a prefix length specification, this is not possible for functional key parts. The solution is to use SUBSTRING() (or CAST() , as described later in this section). For a functional key part containing the SUBSTRING() function to be used in a query, the WHERE clause must contain SUBSTRING() with the same arguments. In the following example, only the second SELECT is able to use the index because that is the only query in which the arguments to SUBSTRING() match the index specification:

CREATE TABLE tbl (
  col1 LONGTEXT,
  INDEX idx1 ((SUBSTRING(col1, 1, 10)))
);
SELECT * FROM tbl WHERE SUBSTRING(col1, 1, 9) = '123456789';
SELECT * FROM tbl WHERE SUBSTRING(col1, 1, 10) = '1234567890';
                            

Functional key parts enable indexing of values that cannot be indexed otherwise, such as JSON values. However, this must be done correctly to achieve the desired effect. For example, this syntax does not work:

CREATE TABLE employees (
  data JSON,
  INDEX ((data->>'$.name'))
);
                            

The syntax fails because:

To index the JSON column, you could try using the CAST() function as follows:

CREATE TABLE employees (
  data JSON,
  INDEX ((CAST(data->>'$.name' AS CHAR(30))))
);
                            

The hidden generated column is assigned the VARCHAR(30) data type, which can be indexed. But this approach produces a new issue when trying to use the index:

  • CAST() returns a string with the collation utf8mb4_0900_ai_ci (the server default collation).

  • JSON_UNQUOTE() returns a string with the collation utf8mb4_bin (hard coded).

As a result, there is a collation mismatch between the indexed expression in the preceding table definition and the WHERE clause expression in the following query:

SELECT * FROM employees WHERE data->>'$.name' = 'James';
                            

The index is not used because the expressions in the query and the index differ. To support this kind of scenario for functional key parts, the optimizer automatically strips CAST() when looking for an index to use, but only if the collation of the indexed expression matches that of the query expression. For an index with a functional key part to be used, either of the following two solutions work (although they differ somewhat in effect):

  • Solution 1. Assign the indexed expression the same collation as JSON_UNQUOTE() :

    CREATE TABLE employees (
      data JSON,
      INDEX idx ((CAST(data->>"$.name" AS CHAR(30)) COLLATE utf8mb4_bin))
    );
    INSERT INTO employees VALUES
      ('{ "name": "james", "salary": 9000 }'),
      ('{ "name": "James", "salary": 10000 }'),
      ('{ "name": "Mary", "salary": 12000 }'),
      ('{ "name": "Peter", "salary": 8000 }');
    SELECT * FROM employees WHERE data->>'$.name' = 'James';
                                            

    The ->> operator is the same as JSON_UNQUOTE(JSON_EXTRACT(...)) , and JSON_UNQUOTE() returns a string with collation utf8mb4_bin . The comparison is thus case sensitive, and only one row matches:

    +------------------------------------+
    | data                               |
    +------------------------------------+
    | {"name": "James", "salary": 10000} |
    +------------------------------------+
                                            
  • Solution 2. Specify the full expression in the query:

    CREATE TABLE employees (
      data JSON,
      INDEX idx ((CAST(data->>"$.name" AS CHAR(30))))
    );
    INSERT INTO employees VALUES
      ('{ "name": "james", "salary": 9000 }'),
      ('{ "name": "James", "salary": 10000 }'),
      ('{ "name": "Mary", "salary": 12000 }'),
      ('{ "name": "Peter", "salary": 8000 }');
    SELECT * FROM employees WHERE CAST(data->>'$.name' AS CHAR(30)) = 'James';
                                            

    CAST() returns a string with collation utf8mb4_0900_ai_ci , so the comparison case insensitive and two rows match:

    +------------------------------------+
    | data                               |
    +------------------------------------+
    | {"name": "james", "salary": 9000}  |
    | {"name": "James", "salary": 10000} |
    +------------------------------------+
                                            

Be aware that although the the optimizer supports automatically stripping CAST() with indexed generated columns, the following approach does not work because it produces a different result with and without an index (Bug#27337092):

mysql> CREATE TABLE employees (
         data JSON,
         generated_col VARCHAR(30) AS (CAST(data->>'$.name' AS CHAR(30)))
       );
Query OK, 0 rows affected, 1 warning (0.03 sec)
mysql> INSERT INTO employees (data)
       VALUES ('{"name": "james"}'), ('{"name": "James"}');
Query OK, 2 rows affected, 1 warning (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 1
mysql> SELECT * FROM employees WHERE data->>'$.name' = 'James';
+-------------------+---------------+
| data              | generated_col |
+-------------------+---------------+
| {"name": "James"} | James         |
+-------------------+---------------+
1 row in set (0.00 sec)
mysql> ALTER TABLE employees ADD INDEX idx (generated_col);
Query OK, 0 rows affected, 1 warning (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 1
mysql> SELECT * FROM employees WHERE data->>'$.name' = 'James';
+-------------------+---------------+
| data              | generated_col |
+-------------------+---------------+
| {"name": "james"} | james         |
| {"name": "James"} | James         |
+-------------------+---------------+
2 rows in set (0.01 sec)
                            

Unique Indexes

A UNIQUE index creates a constraint such that all values in the index must be distinct. An error occurs if you try to add a new row with a key value that matches an existing row. If you specify a prefix value for a column in a UNIQUE index, the column values must be unique within the prefix length. A UNIQUE index permits multiple NULL values for columns that can contain NULL .

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

  • _rowid refers to the PRIMARY KEY column if there is a PRIMARY KEY consisting of a single integer column. If there is a PRIMARY KEY but it does not consist of a single integer column, _rowid cannot be used.

  • Otherwise, _rowid refers to the column in the first UNIQUE NOT NULL index if that index consists of a single integer column. If the first UNIQUE NOT NULL index does not consist of a single integer column, _rowid cannot be used.

Full-Text Indexes

FULLTEXT indexes are supported only for InnoDB and MyISAM tables and can include only CHAR , VARCHAR , and TEXT columns. Indexing always happens over the entire column; column prefix indexing is not supported and any prefix length is ignored if specified. See Section 12.9, “Full-Text Search Functions” , for details of operation.

Spatial Indexes

The MyISAM , InnoDB , NDB , and ARCHIVE storage engines support spatial columns such as POINT and GEOMETRY . ( Section 11.5, “Spatial Data Types” , describes the spatial data types.) However, support for spatial column indexing varies among engines. Spatial and nonspatial indexes on spatial columns are available according to the following rules.

Spatial indexes on spatial columns have these characteristics:

  • Available only for InnoDB and MyISAM tables. Specifying SPATIAL INDEX for other storage engines results in an error.

  • As of MySQL 8.0.12, an index on a spatial column must be a SPATIAL index. The SPATIAL keyword is thus optional but implicit for creating an index on a spatial column.

  • Available for single spatial columns only. A spatial index cannot be created over multiple spatial columns.

  • Indexed columns must be NOT NULL .

  • Column prefix lengths are prohibited. The full width of each column is indexed.

  • Not permitted for a primary key or unique index.

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

  • Permitted for any storage engine that supports spatial columns except ARCHIVE .

  • Columns can be NULL unless the index is a primary key.

  • The index type for a non- SPATIAL index depends on the storage engine. Currently, B-tree is used.

  • Permitted for a column that can have NULL values only for InnoDB , MyISAM , and MEMORY tables.

Index Options

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

  • KEY_BLOCK_SIZE [=] value

    For MyISAM tables, KEY_BLOCK_SIZE optionally specifies the size in bytes to use for index key blocks. The value is treated as a hint; a different size could be used if necessary. A KEY_BLOCK_SIZE value specified for an individual index definition overrides a table-level KEY_BLOCK_SIZE value.

    KEY_BLOCK_SIZE is not supported at the index level for InnoDB tables. See Section 13.1.20, “CREATE TABLE Syntax” .

  • index_type

    Some storage engines permit you to specify an index type when creating an index. For example:

    CREATE TABLE lookup (id INT) ENGINE = MEMORY;
    CREATE INDEX id_index ON lookup (id) USING BTREE;
                                            

    Table 13.1, “Index Types Per Storage Engine” shows the permissible index type values supported by different storage engines. Where multiple index types are listed, the first one is the default when no index type specifier is given. Storage engines not listed in the table do not support an index_type clause in index definitions.

    Table 13.1 Index Types Per Storage Engine

    Storage Engine Permissible Index Types
    InnoDB BTREE
    MyISAM BTREE
    MEMORY / HEAP HASH , BTREE
    NDB HASH , BTREE (see note in text)

    The index_type clause cannot be used for FULLTEXT INDEX or (prior to MySQL 8.0.12) SPATIAL INDEX specifications. Full-text index implementation is storage engine dependent. Spatial indexes are implemented as R-tree indexes.

    If you specify an index type that is not valid for a given storage engine, but another index type is available that the engine can use without affecting query results, the engine uses the available type. The parser recognizes RTREE as a type name. As of MySQL 8.0.12, this is permitted only for SPATIAL indexes. Prior to 8.0.12, RTREE cannot be specified for any storage engine.

    BTREE indexes are implemented by the NDB storage engine as T-tree indexes.

    Note

    For indexes on NDB table columns, the USING option can be specified only for a unique index or primary key. USING HASH prevents the creation of an ordered index; otherwise, creating a unique index or primary key on an NDB table automatically results in the creation of both an ordered index and a hash index, each of which indexes the same set of columns.

    For unique indexes that include one or more NULL columns of an NDB table, the hash index can be used only to look up literal values, which means that IS [NOT] NULL conditions require a full scan of the table. One workaround is to make sure that a unique index using one or more NULL columns on such a table is always created in such a way that it includes the ordered index; that is, avoid employing USING HASH when creating the index.

    If you specify an index type that is not valid for a given storage engine, but another index type is available that the engine can use without affecting query results, the engine uses the available type. The parser recognizes RTREE as a type name, but currently this cannot be specified for any storage engine.

    Note

    Use of the index_type option before the ON tbl_name clause is deprecated; support for use of the option in this position will be removed in a future MySQL release. If an index_type option is given in both the earlier and later positions, the final option applies.

    TYPE type_name is recognized as a synonym for USING type_name . However, USING is the preferred form.

    The following tables show index characteristics for the storage engines that support the index_type option.

    Table 13.2 InnoDB Storage Engine Index Characteristics

    Index Class Index Type Stores NULL VALUES Permits Multiple NULL Values IS NULL Scan Type IS NOT NULL Scan Type
    Primary key BTREE No No N/A N/A
    Unique BTREE Yes Yes Index Index
    Key BTREE Yes Yes Index Index
    FULLTEXT N/A Yes Yes Table Table
    SPATIAL N/A No No N/A N/A

    Table 13.3 MyISAM Storage Engine Index Characteristics

    Index Class Index Type Stores NULL VALUES Permits Multiple NULL Values IS NULL Scan Type IS NOT NULL Scan Type
    Primary key BTREE No No N/A N/A
    Unique BTREE Yes Yes Index Index
    Key BTREE Yes Yes Index Index
    FULLTEXT N/A Yes Yes Table Table
    SPATIAL N/A No No N/A N/A

    Table 13.4 MEMORY Storage Engine Index Characteristics

    Index Class Index Type Stores NULL VALUES Permits Multiple NULL Values IS NULL Scan Type IS NOT NULL Scan Type
    Primary key BTREE No No N/A N/A
    Unique BTREE Yes Yes Index Index
    Key BTREE Yes Yes Index Index
    Primary key HASH No No N/A N/A
    Unique HASH Yes Yes Index Index
    Key HASH Yes Yes Index Index

    Table 13.5 NDB Storage Engine Index Characteristics

    Index Class Index Type Stores NULL VALUES Permits Multiple NULL Values IS NULL Scan Type IS NOT NULL Scan Type
    Primary key BTREE No No Index Index
    Unique BTREE Yes Yes Index Index
    Key BTREE Yes Yes Index Index
    Primary key HASH No No Table (see note 1) Table (see note 1)
    Unique HASH Yes Yes Table (see note 1) Table (see note 1)
    Key HASH Yes Yes Table (see note 1) Table (see note 1)

    Table note:

    1. USING HASH prevents creation of an implicit ordered index.

  • WITH PARSER parser_name

    This option can be used only with FULLTEXT indexes. It associates a parser plugin with the index if full-text indexing and searching operations need special handling. InnoDB and MyISAM support full-text parser plugins. See Full-Text Parser Plugins and Section 29.2.4.4, “Writing Full-Text Parser Plugins” for more information.

  • COMMENT ' string '

    Index definitions can include an optional comment of up to 1024 characters.

    The MERGE_THRESHOLD for index pages can be configured for individual indexes using the index_option COMMENT clause of the CREATE INDEX statement. For example:

    CREATE TABLE t1 (id INT);
    CREATE INDEX id_index ON t1 (id) COMMENT 'MERGE_THRESHOLD=40';
                                            

    If the page-full percentage for an index page falls below the MERGE_THRESHOLD value when a row is deleted or when a row is shortened by an update operation, InnoDB attempts to merge the index page with a neighboring index page. The default MERGE_THRESHOLD value is 50, which is the previously hardcoded value.

    MERGE_THRESHOLD can also be defined at the index level and table level using CREATE TABLE and ALTER TABLE statements. For more information, see Section 15.8.11, “Configuring the Merge Threshold for Index Pages” .

  • VISIBLE , INVISIBLE

    Specify index visibility. Indexes are visible by default. An invisible index is not used by the optimizer. Specification of index visibility applies to indexes other than primary keys (either explicit or implicit). For more information, see Section 8.3.12, “Invisible Indexes” .

Table Copying and Locking Options

ALGORITHM and LOCK clauses may be given to influence the table copying method and level of concurrency for reading and writing the table while its indexes are being modified. They have the same meaning as for the ALTER TABLE statement. For more information, see Section 13.1.9, “ALTER TABLE Syntax”

NDB Cluster supports online operations using the same ALGORITHM=INPLACE syntax used with the standard MySQL Server. See Section 22.5.14, “Online Operations with ALTER TABLE in NDB Cluster” , for more information.

13.1.16 CREATE LOGFILE GROUP Syntax

CREATE LOGFILE GROUP logfile_group
    ADD UNDOFILE 'undo_file'
    [INITIAL_SIZE [=] initial_size]
    [UNDO_BUFFER_SIZE [=] undo_buffer_size]
    [REDO_BUFFER_SIZE [=] redo_buffer_size]
    [NODEGROUP [=] nodegroup_id]
    [WAIT]
    [COMMENT [=] 'string']
    ENGINE [=] engine_name
                        

This statement creates a new log file group named logfile_group having a single UNDO file named ' undo_file '. A CREATE LOGFILE GROUP statement has one and only one ADD UNDOFILE clause. For rules covering the naming of log file groups, see Section 9.2, “Schema Object Names” .

Note

All NDB Cluster Disk Data objects share the same namespace. This means that each Disk Data object must be uniquely named (and not merely each Disk Data object of a given type). For example, you cannot have a tablespace and a log file group with the same name, or a tablespace and a data file with the same name.

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

The optional INITIAL_SIZE parameter sets the UNDO file's initial size; if not specified, it defaults to 128M (128 megabytes). The optional UNDO_BUFFER_SIZE parameter sets the size used by the UNDO buffer for the log file group; The default value for UNDO_BUFFER_SIZE is 8M (eight megabytes); this value cannot exceed the amount of system memory available. Both of these parameters are specified in bytes. You may optionally follow either or both of these with a one-letter abbreviation for an order of magnitude, similar to those used in my.cnf . Generally, this is one of the letters M (for megabytes) or G (for gigabytes).

Memory used for UNDO_BUFFER_SIZE comes from the global pool whose size is determined by the value of the SharedGlobalMemory data node configuration parameter. This includes any default value implied for this option by the setting of the InitialLogFileGroup data node configuration parameter.

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

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

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

The ENGINE option determines the storage engine to be used by this log file group, with engine_name being the name of the storage engine. In MySQL 8.0, this must be NDB (or NDBCLUSTER ). If ENGINE is not set, MySQL tries to use the engine specified by the default_storage_engine server system variable (formerly storage_engine ). In any case, if the engine is not specified as NDB or NDBCLUSTER , the CREATE LOGFILE GROUP statement appears to succeed but actually fails to create the log file group, as shown here:

mysql> CREATE LOGFILE GROUP lg1
    ->     ADD UNDOFILE 'undo.dat' INITIAL_SIZE = 10M;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> SHOW WARNINGS;
+-------+------+------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                        |
+-------+------+------------------------------------------------------------------------------------------------+
| Error | 1478 | Table storage engine 'InnoDB' does not support the create option 'TABLESPACE or LOGFILE GROUP' |
+-------+------+------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> DROP LOGFILE GROUP lg1 ENGINE = NDB;
ERROR 1529 (HY000): Failed to drop LOGFILE GROUP
mysql> CREATE LOGFILE GROUP lg1
    ->     ADD UNDOFILE 'undo.dat' INITIAL_SIZE = 10M
    ->     ENGINE = NDB;
Query OK, 0 rows affected (2.97 sec)
                        

The fact that the CREATE LOGFILE GROUP statement does not actually return an error when a non- NDB storage engine is named, but rather appears to succeed, is a known issue which we hope to address in a future release of NDB Cluster.

REDO_BUFFER_SIZE , NODEGROUP , WAIT , and COMMENT are parsed but ignored, and so have no effect in MySQL 8.0. These options are intended for future expansion.

When used with ENGINE [=] NDB , a log file group and associated UNDO log file are created on each Cluster data node. You can verify that the UNDO files were created and obtain information about them by querying the INFORMATION_SCHEMA.FILES table. For example:

mysql> SELECT LOGFILE_GROUP_NAME, LOGFILE_GROUP_NUMBER, EXTRA
    -> FROM INFORMATION_SCHEMA.FILES
    -> WHERE FILE_NAME = 'undo_10.dat';
+--------------------+----------------------+----------------+
| LOGFILE_GROUP_NAME | LOGFILE_GROUP_NUMBER | EXTRA          |
+--------------------+----------------------+----------------+
| lg_3               |                   11 | CLUSTER_NODE=3 |
| lg_3               |                   11 | CLUSTER_NODE=4 |
+--------------------+----------------------+----------------+
2 rows in set (0.06 sec)
                        

CREATE LOGFILE GROUP is useful only with Disk Data storage for NDB Cluster. See Section 22.5.13, “NDB Cluster Disk Data Tables” .

13.1.17 CREATE PROCEDURE and CREATE FUNCTION Syntax

CREATE
    [DEFINER = { user | CURRENT_USER }]
    PROCEDURE sp_name ([proc_parameter[,...]])
    [characteristic ...] routine_body
CREATE
    [DEFINER = { user | CURRENT_USER }]
    FUNCTION sp_name ([func_parameter[,...]])
    RETURNS type
    [characteristic ...] routine_body
proc_parameter:
    [ IN | OUT | INOUT ] param_name type
func_parameter:
    param_name type
type:
    Any valid MySQL data type
characteristic:
    COMMENT 'string'
  | LANGUAGE SQL
  | [NOT] DETERMINISTIC
  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  | SQL SECURITY { DEFINER | INVOKER }
routine_body:
    Valid SQL routine statement
                        

These statements create stored routines. By default, a routine is associated with the default database. To associate the routine explicitly with a given database, specify the name as db_name.sp_name when you create it.

The CREATE FUNCTION statement is also used in MySQL to support UDFs (user-defined functions). See Section 29.4, “Adding New Functions to MySQL” . A UDF can be regarded as an external stored function. Stored functions share their namespace with UDFs. See Section 9.2.4, “Function Name Parsing and Resolution” , for the rules describing how the server interprets references to different kinds of functions.

To invoke a stored procedure, use the CALL statement (see Section 13.2.1, “CALL Syntax” ). To invoke a stored function, refer to it in an expression. The function returns a value during expression evaluation.

CREATE PROCEDURE and CREATE FUNCTION require the CREATE ROUTINE privilege. They might also require the SET_USER_ID or SUPER privilege, depending on the DEFINER value, as described later in this section. If binary logging is enabled, CREATE FUNCTION might require the SUPER privilege, as described in Section 24.7, “Binary Logging of Stored Programs” .

By default, MySQL automatically grants the ALTER ROUTINE and EXECUTE privileges to the routine creator. This behavior can be changed by disabling the automatic_sp_privileges system variable. See Section 24.2.2, “Stored Routines and MySQL Privileges” .

The DEFINER and SQL SECURITY clauses specify the security context to be used when checking access privileges at routine execution time, as described later in this section.

If the routine name is the same as the name of a built-in SQL function, a syntax error occurs unless you use a space between the name and the following parenthesis when defining the routine or invoking it later. For this reason, avoid using the names of existing SQL functions for your own stored routines.

The IGNORE_SPACE SQL mode applies to built-in functions, not to stored routines. It is always permissible to have spaces after a stored routine name, regardless of whether IGNORE_SPACE is enabled.

The parameter list enclosed within parentheses must always be present. If there are no parameters, an empty parameter list of () should be used. Parameter names are not case sensitive.

Each parameter is an IN parameter by default. To specify otherwise for a parameter, use the keyword OUT or INOUT before the parameter name.

Note

Specifying a parameter as IN , OUT , or INOUT is valid only for a PROCEDURE . For a FUNCTION , parameters are always regarded as IN parameters.

An IN parameter passes a value into a procedure. The procedure might modify the value, but the modification is not visible to the caller when the procedure returns. An OUT parameter passes a value from the procedure back to the caller. Its initial value is NULL within the procedure, and its value is visible to the caller when the procedure returns. An INOUT parameter is initialized by the caller, can be modified by the procedure, and any change made by the procedure is visible to the caller when the procedure returns.

For each OUT or INOUT parameter, pass a user-defined variable in the CALL statement that invokes the procedure so that you can obtain its value when the procedure returns. If you are calling the procedure from within another stored procedure or function, you can also pass a routine parameter or local routine variable as an OUT or INOUT parameter. If you are calling the procedure from within a trigger, you can also pass NEW. col_name as an OUT or INOUT parameter.

For information about the effect of unhandled conditions on procedure parameters, see Section 13.6.7.8, “Condition Handling and OUT or INOUT Parameters” .

Routine parameters cannot be referenced in statements prepared within the routine; see Section C.1, “Restrictions on Stored Programs” .

The following example shows a simple stored procedure that uses an OUT parameter:

mysql> delimiter //
mysql> CREATE PROCEDURE simpleproc (OUT param1 INT)
    -> BEGIN
    ->   SELECT COUNT(*) INTO param1 FROM t;
    -> END//
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ;
mysql> CALL simpleproc(@a);
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT @a;
+------+
| @a   |
+------+
| 3    |
+------+
1 row in set (0.00 sec)
                        

The example uses the mysql client delimiter command to change the statement delimiter from ; to // while the procedure is being defined. This enables the ; delimiter used in the procedure body to be passed through to the server rather than being interpreted by mysql itself. See Section 24.1, “Defining Stored Programs” .

The RETURNS clause may be specified only for a FUNCTION , for which it is mandatory. It indicates the return type of the function, and the function body must contain a RETURN value statement. If the RETURN statement returns a value of a different type, the value is coerced to the proper type. For example, if a function specifies an ENUM or SET value in the RETURNS clause, but the RETURN statement returns an integer, the value returned from the function is the string for the corresponding ENUM member of set of SET members.

The following example function takes a parameter, performs an operation using an SQL function, and returns the result. In this case, it is unnecessary to use delimiter because the function definition contains no internal ; statement delimiters:

mysql> CREATE FUNCTION hello (s CHAR(20))
mysql> RETURNS CHAR(50) DETERMINISTIC
    -> RETURN CONCAT('Hello, ',s,'!');
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT hello('world');
+----------------+
| hello('world') |
+----------------+
| Hello, world!  |
+----------------+
1 row in set (0.00 sec)
                        

Parameter types and function return types can be declared to use any valid data type. The COLLATE attribute can be used if preceded by a CHARACTER SET specification.

The routine_body consists of a valid SQL routine statement. This can be a simple statement such as SELECT or INSERT , or a compound statement written using BEGIN and END . Compound statements can contain declarations, loops, and other control structure statements. The syntax for these statements is described in Section 13.6, “Compound-Statement Syntax” .

MySQL permits routines to contain DDL statements, such as CREATE and DROP . MySQL also permits stored procedures (but not stored functions) to contain SQL transaction statements such as COMMIT . Stored functions may not contain statements that perform explicit or implicit commit or rollback. Support for these statements is not required by the SQL standard, which states that each DBMS vendor may decide whether to permit them.

Statements that return a result set can be used within a stored procedure but not within a stored function. This prohibition includes SELECT statements that do not have an INTO var_list clause and other statements such as SHOW , EXPLAIN , and CHECK TABLE . For statements that can be determined at function definition time to return a result set, a Not allowed to return a result set from a function error occurs ( ER_SP_NO_RETSET ). For statements that can be determined only at runtime to return a result set, a PROCEDURE %s can't return a result set in the given context error occurs ( ER_SP_BADSELECT ).

USE statements within stored routines are not permitted. When a routine is invoked, an implicit USE db_name is performed (and undone when the routine terminates). The causes the routine to have the given default database while it executes. References to objects in databases other than the routine default database should be qualified with the appropriate database name.

For additional information about statements that are not permitted in stored routines, see Section C.1, “Restrictions on Stored Programs” .

For information about invoking stored procedures from within programs written in a language that has a MySQL interface, see Section 13.2.1, “CALL Syntax” .

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

The switch from the SQL mode of the invoker to that of the routine occurs after evaluation of arguments and assignment of the resulting values to routine parameters. If you define a routine in strict SQL mode but invoke it in nonstrict mode, assignment of arguments to routine parameters does not take place in strict mode. If you require that expressions passed to a routine be assigned in strict SQL mode, you should invoke the routine with strict mode in effect.

The COMMENT characteristic is a MySQL extension, and may be used to describe the stored routine. This information is displayed by the SHOW CREATE PROCEDURE and SHOW CREATE FUNCTION statements.

The LANGUAGE characteristic indicates the language in which the routine is written. The server ignores this characteristic; only SQL routines are supported.

A routine is considered deterministic if it always produces the same result for the same input parameters, and not deterministic otherwise. If neither DETERMINISTIC nor NOT DETERMINISTIC is given in the routine definition, the default is NOT DETERMINISTIC . To declare that a function is deterministic, you must specify DETERMINISTIC explicitly.

Assessment of the nature of a routine is based on the honesty of the creator: MySQL does not check that a routine declared DETERMINISTIC is free of statements that produce nondeterministic results. However, misdeclaring a routine might affect results or affect performance. Declaring a nondeterministic routine as DETERMINISTIC might lead to unexpected results by causing the optimizer to make incorrect execution plan choices. Declaring a deterministic routine as NONDETERMINISTIC might diminish performance by causing available optimizations not to be used.

If binary logging is enabled, the DETERMINISTIC characteristic affects which routine definitions MySQL accepts. See Section 24.7, “Binary Logging of Stored Programs” .

A routine that contains the NOW() function (or its synonyms) or RAND() is nondeterministic, but it might still be replication-safe. For NOW() , the binary log includes the timestamp and replicates correctly. RAND() also replicates correctly as long as it is called only a single time during the execution of a routine. (You can consider the routine execution timestamp and random number seed as implicit inputs that are identical on the master and slave.)

Several characteristics provide information about the nature of data use by the routine. In MySQL, these characteristics are advisory only. The server does not use them to constrain what kinds of statements a routine will be permitted to execute.

  • CONTAINS SQL indicates that the routine does not contain statements that read or write data. This is the default if none of these characteristics is given explicitly. Examples of such statements are SET @x = 1 or DO RELEASE_LOCK('abc') , which execute but neither read nor write data.

  • NO SQL indicates that the routine contains no SQL statements.

  • READS SQL DATA indicates that the routine contains statements that read data (for example, SELECT ), but not statements that write data.

  • MODIFIES SQL DATA indicates that the routine contains statements that may write data (for example, INSERT or DELETE ).

The SQL SECURITY characteristic can be DEFINER or INVOKER to specify the security context; that is, whether the routine executes using the privileges of the account named in the routine DEFINER clause or the user who invokes it. This account must have permission to access the database with which the routine is associated. The default value is DEFINER . The user who invokes the routine must have the EXECUTE privilege for it, as must the DEFINER account if the routine executes in definer security context.

The DEFINER clause specifies the MySQL account to be used when checking access privileges at routine execution time for routines that have the SQL SECURITY DEFINER characteristic.

If a user value is given for the DEFINER clause, it should be a MySQL account specified as ' user_name '@' host_name ' , CURRENT_USER , or CURRENT_USER() . The default DEFINER value is the user who executes the CREATE PROCEDURE or CREATE FUNCTION statement. This is the same as specifying DEFINER = CURRENT_USER explicitly.

If you specify the DEFINER clause, these rules determine the valid DEFINER user values:

  • If you do not have the SET_USER_ID or SUPER privilege, the only permitted user value is your own account, either specified literally or by using CURRENT_USER . You cannot set the definer to some other account.

  • If you have the SET_USER_ID or SUPER privilege, you can specify any syntactically valid account name. If the account does not exist, a warning is generated.

  • Although it is possible to create a routine with a nonexistent DEFINER account, an error occurs at routine execution time if the SQL SECURITY value is DEFINER but the definer account does not exist.

For more information about stored routine security, see Section 24.6, “Access Control for Stored Programs and Views” .

Within a stored routine that is defined with the SQL SECURITY DEFINER characteristic, CURRENT_USER returns the routine's DEFINER value. For information about user auditing within stored routines, see Section 6.3.13, “SQL-Based MySQL Account Activity Auditing” .

Consider the following procedure, which displays a count of the number of MySQL accounts listed in the mysql.user system table:

CREATE DEFINER = 'admin'@'localhost' PROCEDURE account_count()
BEGIN
  SELECT 'Number of accounts:', COUNT(*) FROM mysql.user;
END;
                        

The procedure is assigned a DEFINER account of 'admin'@'localhost' no matter which user defines it. It executes with the privileges of that account no matter which user invokes it (because the default security characteristic is DEFINER ). The procedure succeeds or fails depending on whether invoker has the EXECUTE privilege for it and 'admin'@'localhost' has the SELECT privilege for the mysql.user table.

Now suppose that the procedure is defined with the SQL SECURITY INVOKER characteristic:

CREATE DEFINER = 'admin'@'localhost' PROCEDURE account_count()
SQL SECURITY INVOKER
BEGIN
  SELECT 'Number of accounts:', COUNT(*) FROM mysql.user;
END;
                        

The procedure still has a DEFINER of 'admin'@'localhost' , but in this case, it executes with the privileges of the invoking user. Thus, the procedure succeeds or fails depending on whether the invoker has the EXECUTE privilege for it and the SELECT privilege for the mysql.user table.

The server handles the data type of a routine parameter, local routine variable created with DECLARE , or function return value as follows:

  • Assignments are checked for data type mismatches and overflow. Conversion and overflow problems result in warnings, or errors in strict SQL mode.

  • Only scalar values can be assigned. For example, a statement such as SET x = (SELECT 1, 2) is invalid.

  • For character data types, if CHARACTER SET is includedd in the declaration, the specified character set and its default collation is used. If the COLLATE attribute is also present, that collation is used rather than the default collation.

    If CHARACTER SET and COLLATE are not present, the database character set and collation in effect at routine creation time are used. To avoid having the server use the database character set and collation, provide an explicit CHARACTER SET and a COLLATE attribute for character data parameters.

    If you change the database default character set or collation, stored routines that use the database defaults must be dropped and recreated so that they use the new defaults.

    The database character set and collation are given by the value of the character_set_database and collation_database system variables. For more information, see Section 10.3.3, “Database Character Set and Collation” .

13.1.18 CREATE SERVER Syntax

CREATE SERVER server_name
    FOREIGN DATA WRAPPER wrapper_name
    OPTIONS (option [, option] ...)
option:
  { HOST character-literal
  | DATABASE character-literal
  | USER character-literal
  | PASSWORD character-literal
  | SOCKET character-literal
  | OWNER character-literal
  | PORT numeric-literal }
                        

This statement creates the definition of a server for use with the FEDERATED storage engine. The CREATE SERVER statement creates a new row in the servers table in the mysql database. This statement requires the SUPER privilege.

The server_name should be a unique reference to the server. Server definitions are global within the scope of the server, it is not possible to qualify the server definition to a specific database. server_name has a maximum length of 64 characters (names longer than 64 characters are silently truncated), and is case insensitive. You may specify the name as a quoted string.

The wrapper_name is an identifier and may be quoted with single quotation marks.

For each option you must specify either a character literal or numeric literal. Character literals are UTF-8, support a maximum length of 64 characters and default to a blank (empty) string. String literals are silently truncated to 64 characters. Numeric literals must be a number between 0 and 9999, default value is 0.

Note

The OWNER option is currently not applied, and has no effect on the ownership or operation of the server connection that is created.

The CREATE SERVER statement creates an entry in the mysql.servers table that can later be used with the CREATE TABLE statement when creating a FEDERATED table. The options that you specify will be used to populate the columns in the mysql.servers table. The table columns are Server_name , Host , Db , Username , Password , Port and Socket .

For example:

CREATE SERVER s
FOREIGN DATA WRAPPER mysql
OPTIONS (USER 'Remote', HOST '198.51.100.106', DATABASE 'test');
                        

Be sure to specify all options necessary to establish a connection to the server. The user name, host name, and database name are mandatory. Other options might be required as well, such as password.

The data stored in the table can be used when creating a connection to a FEDERATED table:

CREATE TABLE t (s1 INT) ENGINE=FEDERATED CONNECTION='s';
                        

更多信息,请见 Section 16.8, “The FEDERATED Storage Engine” .

CREATE SERVER causes an implicit commit. See Section 13.3.3, “Statements That Cause an Implicit Commit” .

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

13.1.19 CREATE SPATIAL REFERENCE SYSTEM Syntax

CREATE OR REPLACE SPATIAL REFERENCE SYSTEM
    srid srs_attribute ...
CREATE SPATIAL REFERENCE SYSTEM
    [IF NOT EXISTS]
    srid srs_attribute ...
srs_attribute: {
    NAME 'srs_name'
  | DEFINITION 'definition'
  | ORGANIZATION 'org_name' IDENTIFIED BY org_id
  | DESCRIPTION 'description'
}
srid, org_id: 32-bit unsigned integer
                        

This statement creates a spatial reference system (SRS) definition and stores it in the data dictionary. The definition can be inspected using the INFORMATION_SCHEMA ST_SPATIAL_REFERENCE_SYSTEMS table. This statement requires the SUPER privilege.

If neither OR REPLACE nor IF NOT EXISTS is specified, an error occurs if an SRS definition with the SRID value already exists.

With CREATE OR REPLACE syntax, any existing SRS definition with the same SRID value is replaced, unless the SRID value is used by some column in an existing table. In that case, an error occurs. For example:

mysql> CREATE OR REPLACE SPATIAL REFERENCE SYSTEM 4326 ...;
ERROR 3716 (SR005): Can't modify SRID 4326. There is at
least one column depending on it.
                        

To identify which column or columns use the SRID, use this query:

SELECT * FROM INFORMATION_SCHEMA.ST_GEOMETRY_COLUMNS WHERE SRS_ID=4326;
                        

With CREATE ... IF NOT EXISTS syntax, any existing SRS definition with the same SRID value causes the new definition to be ignored and a warning occurs.

SRID values must be in the range of 32-bit unsigned integers, with these restrictions:

  • SRID 0 is a valid SRID but cannot be used with CREATE SPATIAL REFERENCE SYSTEM .

  • If the value is in a reserved SRID range, a warning occurs. Reserved ranges are [0, 32767] (reserved by EPSG), [60,000,000, 69,999,999] (reserved by EPSG), and [2,000,000,000, 2,147,483,647] (reserved by MySQL). EPSG stands for the European Petroleum Survey Group .

  • Users should not create SRSs with SRIDs in the reserved ranges. Doing so runs the risk that the SRIDs will conflict with future SRS definitions distributed with MySQL, with the result that the new system-provided SRSs are not installed for MySQL upgrades or that the user-defined SRSs are overwritten.

Attributes for the statement must satisfy these conditions:

  • Attributes can be given in any order, but no attribute can be given more than once.

  • The NAME and DEFINITION attributes are mandatory.

  • The NAME srs_name attribute value must be unique. The combination of the ORGANIZATION org_name and org_id attribute values must be unique.

  • The NAME srs_name attribute value and ORGANIZATION org_name attribute value cannot be empty or begin or end with whitespace.

  • String values in attribute specifications cannot contain control characters, including newline.

  • The following table shows the maximum lengths for string attribute values.

    Table 13.6 CREATE SPATIAL REFERENCE SYSTEM Attribute Lengths

    Attribute Maximum Length (characters)
    NAME 80
    DEFINITION 4096
    ORGANIZATION 256
    DESCRIPTION 2048

Here is an example CREATE SPATIAL REFERENCE SYSTEM statement. The DEFINITION value is reformatted across multiple lines for readability. (For the statement to be legal, the value actually must be given on a single line.)

CREATE SPATIAL REFERENCE SYSTEM 4120
NAME 'Greek'
ORGANIZATION 'EPSG' IDENTIFIED BY 4120
DEFINITION
  'GEOGCS["Greek",DATUM["Greek",SPHEROID["Bessel 1841",
  6377397.155,299.1528128,AUTHORITY["EPSG","7004"]],
  AUTHORITY["EPSG","6120"]],PRIMEM["Greenwich",0,
  AUTHORITY["EPSG","8901"]],UNIT["degree",0.017453292519943278,
  AUTHORITY["EPSG","9122"]],AXIS["Lat",NORTH],AXIS["Lon",EAST],
  AUTHORITY["EPSG","4120"]]';
                        

The grammar for SRS definitions is based on the grammar defined in OpenGIS Implementation Specification: Coordinate Transformation Services , Revision 1.00, OGC 01-009, January 12, 2001, Section 7.2. This specification is available at http://www.opengeospatial.org/standards/ct .

MySQL incorporates these changes to the specification:

  • Only the <horz cs> production rule is implemented (that is, geographic and projected SRSs).

  • There is an optional, nonstandard <authority> clause for <parameter> . This makes it possible to recognize projection parameters by authority instead of name.

  • SRS definitions may not contain newlines.

13.1.20 CREATE TABLE Syntax

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    (create_definition,...)
    [table_options]
    [partition_options]
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    [(create_definition,...)]
    [table_options]
    [partition_options]
    [IGNORE | REPLACE]
    [AS] query_expression
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    { LIKE old_tbl_name | (LIKE old_tbl_name) }
create_definition:
    col_name column_definition
  | {INDEX|KEY} [index_name] [index_type] (key_part,...)
      [index_option] ...
  | {FULLTEXT|SPATIAL} [INDEX|KEY] [index_name] (key_part,...)
      [index_option] ...
  | [CONSTRAINT [symbol]] PRIMARY KEY
      [index_type] (key_part,...)
      [index_option] ...
  | [CONSTRAINT [symbol]] UNIQUE [INDEX|KEY]
      [index_name] [index_type] (key_part,...)
      [index_option] ...
  | [CONSTRAINT [symbol]] FOREIGN KEY
      [index_name] (col_name,...)
      reference_definition
  | [CONSTRAINT [symbol]] CHECK (expr) [[NOT] ENFORCED]
column_definition:
    data_type [NOT NULL | NULL] [DEFAULT {literal | (expr)} ]
      [AUTO_INCREMENT] [UNIQUE [KEY]] [[PRIMARY] KEY]
      [COMMENT 'string']
      [COLLATE collation_name]
      [COLUMN_FORMAT {FIXED|DYNAMIC|DEFAULT}]
      [STORAGE {DISK|MEMORY|DEFAULT}]
      [check_constraint_definition | reference_definition]
  | data_type
      [COLLATE collation_name]
      [GENERATED ALWAYS] AS (expr)
      [VIRTUAL | STORED] [NOT NULL | NULL]
      [UNIQUE [KEY]] [[PRIMARY] KEY]
      [COMMENT 'string']
      [check_constraint_definition | reference_definition]
data_type:
    (see Chapter 11, Data Types)
key_part: {col_name [(length)] | (expr)} [ASC | DESC]
index_type:
    USING {BTREE | HASH}
index_option:
    KEY_BLOCK_SIZE [=] value
  | index_type
  | WITH PARSER parser_name
  | COMMENT 'string'
  | {VISIBLE | INVISIBLE}
check_constraint_definition:
    [CONSTRAINT [symbol]] CHECK (expr) [[NOT] ENFORCED]
reference_definition:
    REFERENCES tbl_name (key_part,...)
      [MATCH FULL | MATCH PARTIAL | MATCH SIMPLE]
      [ON DELETE reference_option]
      [ON UPDATE reference_option]
reference_option:
    RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT
table_options:
    table_option [[,] table_option] ...
table_option:
    AUTO_INCREMENT [=] value
  | AVG_ROW_LENGTH [=] value
  | [DEFAULT] CHARACTER SET [=] charset_name
  | CHECKSUM [=] {0 | 1}
  | [DEFAULT] COLLATE [=] collation_name
  | COMMENT [=] 'string'
  | COMPRESSION [=] {'ZLIB'|'LZ4'|'NONE'}
  | CONNECTION [=] 'connect_string'
  | {DATA|INDEX} DIRECTORY [=] 'absolute path to directory'
  | DELAY_KEY_WRITE [=] {0 | 1}
  | ENCRYPTION [=] {'Y' | 'N'}
  | ENGINE [=] engine_name
  | INSERT_METHOD [=] { NO | FIRST | LAST }
  | KEY_BLOCK_SIZE [=] value
  | MAX_ROWS [=] value
  | MIN_ROWS [=] value
  | PACK_KEYS [=] {0 | 1 | DEFAULT}
  | PASSWORD [=] 'string'
  | ROW_FORMAT [=] {DEFAULT|DYNAMIC|FIXED|COMPRESSED|REDUNDANT|COMPACT}
  | STATS_AUTO_RECALC [=] {DEFAULT|0|1}
  | STATS_PERSISTENT [=] {DEFAULT|0|1}
  | STATS_SAMPLE_PAGES [=] value
  | TABLESPACE tablespace_name [STORAGE {DISK|MEMORY|DEFAULT}]
  | UNION [=] (tbl_name[,tbl_name]...)
partition_options:
    PARTITION BY
        { [LINEAR] HASH(expr)
        | [LINEAR] KEY [ALGORITHM={1|2}] (column_list)
        | RANGE{(expr) | COLUMNS(column_list)}
        | LIST{(expr) | COLUMNS(column_list)} }
    [PARTITIONS num]
    [SUBPARTITION BY
        { [LINEAR] HASH(expr)
        | [LINEAR] KEY [ALGORITHM={1|2}] (column_list) }
      [SUBPARTITIONS num]
    ]
    [(partition_definition [, partition_definition] ...)]
partition_definition:
    PARTITION partition_name
        [VALUES
            {LESS THAN {(expr | value_list) | MAXVALUE}
            |
            IN (value_list)}]
        [[STORAGE] ENGINE [=] engine_name]
        [COMMENT [=] 'string' ]
        [DATA DIRECTORY [=] 'data_dir']
        [INDEX DIRECTORY [=] 'index_dir']
        [MAX_ROWS [=] max_number_of_rows]
        [MIN_ROWS [=] min_number_of_rows]
        [TABLESPACE [=] tablespace_name]
        [(subpartition_definition [, subpartition_definition] ...)]
subpartition_definition:
    SUBPARTITION logical_name
        [[STORAGE] ENGINE [=] engine_name]
        [COMMENT [=] 'string' ]
        [DATA DIRECTORY [=] 'data_dir']
        [INDEX DIRECTORY [=] 'index_dir']
        [MAX_ROWS [=] max_number_of_rows]
        [MIN_ROWS [=] min_number_of_rows]
        [TABLESPACE [=] tablespace_name]
query_expression:
    SELECT ...   (Some valid select or union statement)
                        

CREATE TABLE creates a table with the given name. You must have the CREATE privilege for the table.

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

For information about the physical representation of a table, see Section 13.1.20.2, “Files Created by CREATE TABLE” .

The original CREATE TABLE statement, including all specifications and table options are stored by MySQL when the table is created. For more information, see Section 13.1.20.1, “CREATE TABLE Statement Retention” .

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

Table Name

  • tbl_name

    The table name can be specified as db_name.tbl_name to create the table in a specific database. This works regardless of whether there is a default database, assuming that the database exists. If you use quoted identifiers, quote the database and table names separately. For example, write `mydb`.`mytbl` , not `mydb.mytbl` .

    Rules for permissible table names are given in Section 9.2, “Schema Object Names” .

  • IF NOT EXISTS

    Prevents an error from occurring if the table exists. However, there is no verification that the existing table has a structure identical to that indicated by the CREATE TABLE statement.

Temporary Tables

You can use the TEMPORARY keyword when creating a table. A TEMPORARY table is visible only within the current session, and is dropped automatically when the session is closed. For more information, see Section 13.1.20.3, “CREATE TEMPORARY TABLE Syntax” .

Table Cloning and Copying

Column Data Types and Attributes

There is a hard limit of 4096 columns per table, but the effective maximum may be less for a given table and depends on the factors discussed in Section C.10.4, “Limits on Table Column Count and Row Size” .

  • data_type

    data_type represents the data type in a column definition. For a full description of the syntax available for specifying column data types, as well as information about the properties of each type, see Chapter 11, Data Types .

    • Some attributes do not apply to all data types. AUTO_INCREMENT applies only to integer and floating-point types. Prior to MySQL 8.0.13, DEFAULT does not apply to the BLOB , TEXT , GEOMETRY , and JSON types.

    • Character data types ( CHAR , VARCHAR , the TEXT types, ENUM , SET , and any synonyms) synonyms) can include CHARACTER SET to specify the character set for the column. CHARSET is a synonym for CHARACTER SET . A collation for the character set can be specified with the COLLATE attribute, along with any other attributes. For details, see Chapter 10, Character Sets, Collations, Unicode . 范例:

      CREATE TABLE t (c CHAR(20) CHARACTER SET utf8 COLLATE utf8_bin);
                                                      

      MySQL 8.0 interprets length specifications in character column definitions in characters. Lengths for BINARY and VARBINARY are in bytes.

    • For CHAR , VARCHAR , BINARY , and VARBINARY columns, indexes can be created that use only the leading part of column values, using col_name ( length ) syntax to specify an index prefix length. BLOB and TEXT columns also can be indexed, but a prefix length must be given. Prefix lengths are given in characters for nonbinary string types and in bytes for binary string types. That is, index entries consist of the first length characters of each column value for CHAR , VARCHAR , and TEXT columns, and the first length bytes of each column value for BINARY , VARBINARY , and BLOB columns. Indexing only a prefix of column values like this can make the index file much smaller. For additional information about index prefixes, see Section 13.1.15, “CREATE INDEX Syntax” .

      Only the InnoDB and MyISAM storage engines support indexing on BLOB and TEXT columns. For example:

      CREATE TABLE test (blob_col BLOB, INDEX(blob_col(10)));
                                                      

      If a specified index prefix exceeds the maximum column data type size, CREATE TABLE handles the index as follows:

      • For a nonunique index, either an error occurs (if strict SQL mode is enabled), or the index length is reduced to lie within the maximum column data type size and a warning is produced (if strict SQL mode is not enabled).

      • For a unique index, an error occurs regardless of SQL mode because reducing the index length might enable insertion of nonunique entries that do not meet the specified uniqueness requirement.

    • JSON columns cannot be indexed. You can work around this restriction by creating an index on a generated column that extracts a scalar value from the JSON column. See Indexing a Generated Column to Provide a JSON Column Index , for a detailed example.

  • NOT NULL | NULL

    If neither NULL nor NOT NULL is specified, the column is treated as though NULL had been specified.

    In MySQL 8.0, only the InnoDB , MyISAM , and MEMORY storage engines support indexes on columns that can have NULL values. In other cases, you must declare indexed columns as NOT NULL or an error results.

  • DEFAULT

    Specifies a default value for a column. For more information about default value handling, including the case that a column definition includes no explicit DEFAULT value, see Section 11.7, “Data Type Default Values” .

    If the NO_ZERO_DATE or NO_ZERO_IN_DATE SQL mode is enabled and a date-valued default is not correct according to that mode, CREATE TABLE produces a warning if strict SQL mode is not enabled and an error if strict mode is enabled. For example, with NO_ZERO_IN_DATE enabled, c1 DATE DEFAULT '2010-00-00' produces a warning.

  • AUTO_INCREMENT

    An integer or floating-point column can have the additional attribute AUTO_INCREMENT . When you insert a value of NULL (recommended) or 0 into an indexed AUTO_INCREMENT column, the column is set to the next sequence value. Typically this is value +1 , where value is the largest value for the column currently in the table. AUTO_INCREMENT sequences begin with 1 .

    To retrieve an AUTO_INCREMENT value after inserting a row, use the LAST_INSERT_ID() SQL function or the mysql_insert_id() C API function. See Section 12.15, “Information Functions” , and Section 28.7.7.38, “mysql_insert_id()” .

    If the NO_AUTO_VALUE_ON_ZERO