MySQL8-中文参考-二十六-

MySQL8 中文参考（二十六）

原文：docs.oracle.com/javase/tutorial/reallybigindex.html

原文：dev.mysql.com/doc/refman/8.0/en/create-table-secondary-indexes.html

15.1.20.9 二级索引和生成列

InnoDB支持虚拟生成列上的二级索引。不支持其他索引类型。在虚拟列上定义的二级索引有时被称为“虚拟索引”。

可以在一个或多个虚拟列上或在虚拟列和常规列或存储生成列的组合上创建二级索引。包含虚拟列的二级索引可以被定义为UNIQUE。

当在虚拟生成列上创建二级索引时，生成列的值会实现在索引记录中。如果索引是一个覆盖索引（包含查询检索的所有列），生成列的值将从索引结构中的实现值中检索，而不是实时计算。

使用虚拟列上的二级索引时需要考虑额外的写入成本，因为在INSERT和UPDATE操作期间，在二级索引记录中实现虚拟列值时执行计算。即使有额外的写入成本，虚拟列上的二级索引可能比生成存储列更可取，后者实现在聚簇索引中，导致需要更多磁盘空间和内存的更大表。如果在虚拟列上未定义二级索引，则读取时会有额外的成本，因为每次检查列的行时都必须计算虚拟列值。

虚拟列的索引列值被 MVCC 记录，以避免在回滚或清除操作期间重新计算生成列值。对于COMPACT和REDUNDANT行格式，记录值的数据长度受索引键限制的限制为 767 字节，对于DYNAMIC和COMPRESSED行格式，为 3072 字节。

在虚拟列上添加或删除二级索引是一个原地操作。

为提供 JSON 列索引而对生成列建立索引

如其他地方所述，JSON列不能直接建立索引。要创建引用此类列的索引，可以定义一个生成列，提取应该建立索引的信息，然后在生成列上创建索引，如下例所示：

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

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

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

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

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

（我们已经将此示例中最后一条语句的输出包装起来以适应查看区域。）

当您在包含使用->或->>运算符的一个或多个表达式的SELECT或其他 SQL 语句上使用EXPLAIN时，这些表达式将被转换为使用JSON_EXTRACT()和（如果需要）JSON_UNQUOTE()的等效形式，如下所示，在EXPLAIN语句后立即显示的SHOW WARNINGS输出中：

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

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

请参阅->和->>运算符的描述，以及JSON_EXTRACT()和JSON_UNQUOTE()函数的描述，获取更多信息和示例。

这种技术还可以用于提供间接引用其他类型列的索引，这些列不能直接进行索引，例如GEOMETRY列。

在 MySQL 8.0.21 及更高版本中，还可以使用JSON_VALUE()函数在JSON列上创建索引，使用可以优化查询的表达式。有关该函数的更多信息和示例，请参阅该函数的描述。

NDB Cluster 中的 JSON 列和间接索引

在 MySQL NDB Cluster 中，也可以使用 JSON 列的间接索引，但需要符合以下条件：

NDB将JSON列值在内部处理为BLOB。这意味着任何具有一个或多个 JSON 列的 NDB 表必须具有主键，否则无法记录在二进制日志中。
NDB存储引擎不支持虚拟列的索引。由于生成列的默认值是VIRTUAL，因此必须明确指定要应用间接索引的生成列为STORED。

用于创建此处显示的jempn表的CREATE TABLE语句是先前显示的jemp表的版本，经过修改以使其与NDB兼容：

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

我们可以使用以下INSERT语句填充这个表：

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

现在NDB可以使用索引i，如下所示：

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

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

你应该记住，存储的生成列以及该列上的任何索引都使用DataMemory。

原文：dev.mysql.com/doc/refman/8.0/en/invisible-columns.html

15.1.20.10 不可见列

MySQL 从 MySQL 8.0.23 开始支持不可见列。不可见列通常对查询隐藏，但如果显式引用，则可以访问。在 MySQL 8.0.23 之前，所有列都是可见的。

作为不可见列可能有用的示例，假设一个应用程序使用SELECT *查询来访问表，并且必须继续工作而无需修改，即使表被修改以添加一个应用程序不希望存在的新列。在SELECT *查询中，*会计算所有表列，除了那些不可见的列，因此解决方案是将新列添加为不可见列。该列仍然对SELECT *查询“隐藏”，应用程序继续像以前一样工作。如果必要，新版本的应用程序可以通过显式引用来引用不可见列。

以下各节详细介绍了 MySQL 如何处理不可见列。

DDL 语句和不可见列
DML 语句和不可见列
不可见列元数据
二进制日志和不可见列

DDL 语句和不可见列

列默认为可见。要为新列明确指定可见性，请在CREATE TABLE或ALTER TABLE的列定义中使用VISIBLE或INVISIBLE关键字：

CREATE TABLE t1 (
  i INT,
  j DATE INVISIBLE
) ENGINE = InnoDB;
ALTER TABLE t1 ADD COLUMN k INT INVISIBLE;

要更改现有列的可见性，请在ALTER TABLE列修改子句中使用VISIBLE或INVISIBLE关键字之一：

ALTER TABLE t1 CHANGE COLUMN j j DATE VISIBLE;
ALTER TABLE t1 MODIFY COLUMN j DATE INVISIBLE;
ALTER TABLE t1 ALTER COLUMN j SET VISIBLE;

表必须至少有一个可见列。尝试使所有列不可见会产生错误。

不可见列支持通常的列属性：NULL、NOT NULL、AUTO_INCREMENT等。

生成列可以是不可见的。

索引定义可以命名不可见列，包括PRIMARY KEY和UNIQUE索引的定义。虽然表必须至少有一个可见列，但索引定义不需要有任何可见列。

从表中删除的不可见列会像通常一样从命名该列的任何索引定义中删除。

外键约束可以定义在不可见列上，并且外键约束可以引用不可见列。

CHECK约束可以定义在不可见列上。对于新的或修改的行，违反不可见列上的CHECK约束会产生错误。

CREATE TABLE ... LIKE 包含不可见列，并且在新表中也是不可见的。

CREATE TABLE ... SELECT 不包括不可见列，除非它们在SELECT部分中被明确引用。然而，即使被明确引用，现有表中不可见的列在新表中也是可见的：

mysql> CREATE TABLE t1 (col1 INT, col2 INT INVISIBLE);
mysql> CREATE TABLE t2 AS SELECT col1, col2 FROM t1;
mysql> SHOW CREATE TABLE t2\G
*************************** 1\. row ***************************
       Table: t2
Create Table: CREATE TABLE `t2` (
  `col1` int DEFAULT NULL,
  `col2` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

如果要保留不可见性，请在CREATE TABLE部分的CREATE TABLE ... SELECT语句中为不可见列提供定义：

mysql> CREATE TABLE t1 (col1 INT, col2 INT INVISIBLE);
mysql> CREATE TABLE t2 (col2 INT INVISIBLE) AS SELECT col1, col2 FROM t1;
mysql> SHOW CREATE TABLE t2\G
*************************** 1\. row ***************************
       Table: t2
Create Table: CREATE TABLE `t2` (
  `col1` int DEFAULT NULL,
  `col2` int DEFAULT NULL /*!80023 INVISIBLE */
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

视图可以通过在定义视图的SELECT语句中明确引用它们来引用不可见列。在定义引用该列的视图之后更改列的可见性不会改变视图行为。

DML 语句和不可见列

对于SELECT语句，除非在选择列表中明确引用，否则不可见列不会成为结果集的一部分。在选择列表中，*和*tbl_name*.*的简写不包括不可见列。自然连接不包括不可见列。

考虑以下语句序列：

mysql> CREATE TABLE t1 (col1 INT, col2 INT INVISIBLE);
mysql> INSERT INTO t1 (col1, col2) VALUES(1, 2), (3, 4);

mysql> SELECT * FROM t1;
+------+
| col1 |
+------+
|    1 |
|    3 |
+------+

mysql> SELECT col1, col2 FROM t1;
+------+------+
| col1 | col2 |
+------+------+
|    1 |    2 |
|    3 |    4 |
+------+------+

第一个SELECT在选择列表中不引用不可见列col2（因为*不包括不可见列），所以col2不会出现在语句结果中。第二个SELECT明确引用col2，因此该列会出现在结果中。

语句TABLE t1 产生与第一个SELECT语句相同的输出。由于在TABLE语句中无法指定列，因此TABLE永远不会显示不可见列。

对于创建新行的语句，除非显式引用并赋值，否则不可见列将被分配其隐式默认值。有关隐式默认值的信息，请参阅隐式默认值处理。

对于INSERT（和REPLACE，对于未替换的行），当缺少列列表、空列列表或不包括不可见列的非空列列表时，隐式默认赋值会发生：

CREATE TABLE t1 (col1 INT, col2 INT INVISIBLE);
INSERT INTO t1 VALUES(...);
INSERT INTO t1 () VALUES(...);
INSERT INTO t1 (col1) VALUES(...);

对于前两个INSERT语句，VALUES()列表必须为每个可见列提供一个值，而不包括不可见列。对于第三个INSERT语句，VALUES()列表必须提供与命名列数相同的值；当您使用VALUES ROW()而不是VALUES()时也是如此。

对于LOAD DATA和LOAD XML，如果缺少列列表或非空列列表不包括不可见列，则会发生隐式默认赋值。输入行不应包含不可见列的值。

对于前述语句，如果要为不可见列分配除隐式默认值之外的值，请在列列表中明确命名不可见列并为其提供值。

INSERT INTO ... SELECT *和REPLACE INTO ... SELECT *不包括不可见列，因为*不包括不可见列。隐式默认赋值如前所述发生。

对于根据PRIMARY KEY或UNIQUE索引中的值插入或忽略新行，或替换或修改现有行的语句，MySQL 将不可见列视为可见列的相同方式处理：不可见列参与键值比较。具体来说，如果新行与唯一键值的现有行具有相同的值，则无论索引列是可见还是不可见，这些行为都会发生：

使用IGNORE修饰符，INSERT、LOAD DATA和LOAD XML会忽略新行。
REPLACE用新行替换现有行。使用REPLACE修饰符，LOAD DATA和LOAD XML也是如此。
INSERT ... ON DUPLICATE KEY UPDATE更新现有行。

对于UPDATE语句更新不可见列，与可见列一样，需要命名并分配值。

不可见列元数据

列是否可见的信息可以从信息模式COLUMNS表的EXTRA列或SHOW COLUMNS输出中获取。例如：

mysql> SELECT TABLE_NAME, COLUMN_NAME, EXTRA
       FROM INFORMATION_SCHEMA.COLUMNS
       WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = 't1';
+------------+-------------+-----------+
| TABLE_NAME | COLUMN_NAME | EXTRA     |
+------------+-------------+-----------+
| t1         | i           |           |
| t1         | j           |           |
| t1         | k           | INVISIBLE |
+------------+-------------+-----------+

列默认可见，因此在这种情况下，EXTRA不显示可见性信息。对于不可见列，EXTRA显示INVISIBLE。

SHOW CREATE TABLE在表定义中显示不可见列，版本特定注释中包含INVISIBLE关键字：

mysql> SHOW CREATE TABLE t1\G
*************************** 1\. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `i` int DEFAULT NULL,
  `j` int DEFAULT NULL,
  `k` int DEFAULT NULL /*!80023 INVISIBLE */
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

mysqldump和mysqlpump使用SHOW CREATE TABLE，因此它们在转储表定义时包括不可见列。它们还在转储数据时包括不可见列值。

将转储文件重新加载到不支持不可见列的旧版本的 MySQL 中会忽略特定于版本的注释，从而将任何不可见列创建为可见列。

二进制日志和不可见列

MySQL 在二进制日志中处理不可见列如下：

表创建事件为不可见列包括INVISIBLE属性。
不可见列在行事件中被视为可见列。根据binlog_row_image系统变量设置，如果需要，则包括它们。
应用行事件时，不可见列在行事件中被视为可见列。特别是，根据slave_rows_search_algorithms系统变量设置选择要使用的算法和索引。
不可见列在计算写入集时被视为可见列。特别是，写入集包括在不可见列上定义的索引。
mysqlbinlog 命令在列元数据中包含可见性。

原文：dev.mysql.com/doc/refman/8.0/en/create-table-gipks.html

15.1.20.11 生成的隐式主键

从 MySQL 8.0.30 开始，MySQL 支持为没有显式主键的任何InnoDB表创建生成的隐式主键。当sql_generate_invisible_primary_key服务器系统变量设置为ON时，MySQL 服务器会自动向任何这样的表添加一个生成的隐式主键（GIPK）。

默认情况下，sql_generate_invisible_primary_key的值为OFF，这意味着禁用 GIPK 的自动添加。为了说明这如何影响表的创建，我们首先创建两个相同的表，都没有主键，唯一的区别是第一个（表auto_0）在创建时sql_generate_invisible_primary_key设置为OFF，而第二个（auto_1）在将其设置为ON后创建，如下所示：

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

mysql> CREATE TABLE auto_0 (c1 VARCHAR(50), c2 INT);
Query OK, 0 rows affected (0.02 sec)

mysql> SET sql_generate_invisible_primary_key=ON;
Query OK, 0 rows affected (0.00 sec)

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

mysql> CREATE TABLE auto_1 (c1 VARCHAR(50), c2 INT);
Query OK, 0 rows affected (0.04 sec)

比较这些SHOW CREATE TABLE语句的输出，看看表实际上是如何创建的：

mysql> SHOW CREATE TABLE auto_0\G
*************************** 1\. row ***************************
       Table: auto_0
Create Table: CREATE TABLE `auto_0` (
  `c1` varchar(50) DEFAULT NULL,
  `c2` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 1 row in set (0.00 sec)

mysql> SHOW CREATE TABLE auto_1\G
*************************** 1\. row ***************************
       Table: auto_1
Create Table: CREATE TABLE `auto_1` (
  `my_row_id` bigint unsigned NOT NULL AUTO_INCREMENT /*!80023 INVISIBLE */,
  `c1` varchar(50) DEFAULT NULL,
  `c2` int DEFAULT NULL,
  PRIMARY KEY (`my_row_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 1 row in set (0.00 sec)

由于auto_1在创建时未指定主键，设置sql_generate_invisible_primary_key = ON会导致 MySQL 向该表添加不可见列my_row_id和在该列上的主键。由于在创建auto_0时sql_generate_invisible_primary_key为OFF，因此在该表上没有进行此类添加。

当服务器向表添加主键时，列和键名始终为my_row_id。因此，在以这种方式启用生成的隐式主键时，除非表创建语句还指定了显式主键，否则不能创建具有列名为my_row_id的表。（在这种情况下，您不需要在列或键中命名为my_row_id。）

my_row_id是一个不可见列，这意味着它不会显示在SELECT *或TABLE的输出中；必须通过名称显式选择该列。请参阅第 15.1.20.10 节，“不可见列”。

启用 GIPK 时，生成的主键除了在VISIBLE和INVISIBLE之间切换之外，不能进行其他更改。要使auto_1上的生成的隐式主键可见，请执行此ALTER TABLE语句：

mysql> ALTER TABLE auto_1 ALTER COLUMN my_row_id SET VISIBLE;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW CREATE TABLE auto_1\G
*************************** 1\. row ***************************
       Table: auto_1
Create Table: CREATE TABLE `auto_1` (
  `my_row_id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `c1` varchar(50) DEFAULT NULL,
  `c2` int DEFAULT NULL,
  PRIMARY KEY (`my_row_id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 1 row in set (0.01 sec)

要再次使此生成的主键不可见，请执行ALTER TABLE auto_1 ALTER COLUMN my_row_id SET INVISIBLE。

生成的隐式主键默认情况下始终是不可见的。

启用 GIPK 时，如果满足以下任一条件，则无法删除生成的主键：

表没有主键。
主键被删除，但主键列未被删除。

sql_generate_invisible_primary_key的影响仅适用于使用InnoDB存储引擎的表。您可以使用ALTER TABLE语句更改表使用的存储引擎，该表具有生成的不可见主键；在这种情况下，主键和列保持不变，但表和键不再接受任何特殊处理。

默认情况下，GIPK 会显示在SHOW CREATE TABLE、SHOW COLUMNS和SHOW INDEX的输出中，并且在信息模式的COLUMNS和STATISTICS表中可见。您可以通过将show_gipk_in_create_table_and_information_schema系统变量设置为OFF来隐藏这些情况下生成的不可见主键。默认情况下，此变量为ON，如下所示：

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

如下查询COLUMNS表可见，my_row_id在auto_1的列中可见：

mysql> SELECT COLUMN_NAME, ORDINAL_POSITION, DATA_TYPE, COLUMN_KEY
 -> FROM INFORMATION_SCHEMA.COLUMNS
 -> WHERE TABLE_NAME = "auto_1";
+-------------+------------------+-----------+------------+
| COLUMN_NAME | ORDINAL_POSITION | DATA_TYPE | COLUMN_KEY |
+-------------+------------------+-----------+------------+
| my_row_id   |                1 | bigint    | PRI        |
| c1          |                2 | varchar   |            |
| c2          |                3 | int       |            |
+-------------+------------------+-----------+------------+
3 rows in set (0.01 sec)

在将show_gipk_in_create_table_and_information_schema设置为OFF后，my_row_id不再在COLUMNS表中可见，如下所示：

mysql> SET show_gipk_in_create_table_and_information_schema = OFF;
Query OK, 0 rows affected (0.00 sec)

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

mysql> SELECT COLUMN_NAME, ORDINAL_POSITION, DATA_TYPE, COLUMN_KEY
 -> FROM INFORMATION_SCHEMA.COLUMNS
 -> WHERE TABLE_NAME = "auto_1";
+-------------+------------------+-----------+------------+
| COLUMN_NAME | ORDINAL_POSITION | DATA_TYPE | COLUMN_KEY |
+-------------+------------------+-----------+------------+
| c1          |                2 | varchar   |            |
| c2          |                3 | int       |            |
+-------------+------------------+-----------+------------+
2 rows in set (0.00 sec)

sql_generate_invisible_primary_key的设置不会被复制，并且被复制的应用程序线程会忽略它。这意味着在源上设置此变量对副本没有影响。在 MySQL 8.0.32 及更高版本中，您可以通过在CHANGE REPLICATION SOURCE TO语句中使用REQUIRE_TABLE_PRIMARY_KEY_CHECK = GENERATE来使副本在给定复制通道上为没有主键的表添加 GIPK。

GIPK 与基于行的CREATE TABLE ... SELECT复制一起工作；在这种情况下，写入二进制日志的信息包括 GIPK 定义，因此正确复制。不支持sql_generate_invisible_primary_key = ON的基于语句的CREATE TABLE ... SELECT复制。

在创建或导入使用 GIPK 的安装备份时，可以排除生成的不可见主键列和值。--skip-generated-invisible-primary-key 选项用于 mysqldump，导致在程序输出中排除 GIPK 信息。如果您正在导入包含生成的不可见主键和值的转储文件，还可以使用 --skip-generated-invisible-primary-key 与 mysqlpump 一起使用，以使这些信息被抑制（因此不会被导入）。

原文：dev.mysql.com/doc/refman/8.0/en/create-table-ndb-comment-options.html

15.1.20.12 设置 NDB 注释选项

NDB_COLUMN 选项
NDB_TABLE 选项

可以在 NDB 表的表注释或列注释中设置许多特定于 NDB Cluster 的选项。通过使用 NDB_TABLE 在表注释中嵌入用于控制从任何副本读取和分区平衡的表级选项。

NDB_COLUMN 可以用于列注释，将 NDB 用于存储 blob 值的 blob 部分表列的大小设置为最大值。这适用于 BLOB、MEDIUMBLOB、LONGBLOB、TEXT、MEDIUMTEXT、LONGTEXT 和 JSON 列。从 NDB 8.0.30 开始，列注释还可以用于控制 blob 列的内联大小。 NDB_COLUMN 注释不支持 TINYBLOB 或 TINYTEXT 列，因为这些列具有固定大小的内联部分（仅）和无法存储在其他位置的单独部分。

NDB_TABLE 可以用于表注释，用于设置与分区平衡和表是否完全复制等相关的选项。

本节的其余部分描述了这些选项及其用法。

NDB_COLUMN 选项

在 NDB Cluster 中，CREATE TABLE 或 ALTER TABLE 语句中的列注释也可以用于指定 NDB_COLUMN 选项。从版本 8.0.30 开始，NDB 支持两个列注释选项 BLOB_INLINE_SIZE 和 MAX_BLOB_PART_SIZE。（在 NDB 8.0.30 之前，仅支持 MAX_BLOB_PART_SIZE。）此选项的语法如下所示：

COMMENT 'NDB_COLUMN=*speclist*'

*speclist* := *spec*[,*spec*]

spec := 
    BLOB_INLINE_SIZE=*value*
  | MAX_BLOB_PART_SIZE[={0|1}]

BLOB_INLINE_SIZE 指定要由该列内联存储的字节数；其预期值为 1 - 29980 范围内的整数。设置大于 29980 的值会引发错误；允许设置小于 1 的值，但会导致使用列类型的默认内联大小。

您应该知道，此选项的最大值实际上是可以存储在一个 NDB 表的一行中的最大字节数；行中的每列都会对此总数做出贡献。

您还应该记住，特别是在处理 TEXT 列时，由 MAX_BLOB_PART_SIZE 或 BLOB_INLINE_SIZE 设置的值表示列大小（以字节为单位）。它不表示字符数，字符数根据列使用的字符集和排序规则而变化。

要查看此选项的效果，请首先创建一个具有两个BLOB列的表，一个（b1）没有额外选项，另一个（b2）设置了BLOB_INLINE_SIZE，如下所示：

mysql> CREATE TABLE t1 (
 ->    a INT NOT NULL PRIMARY KEY,
 ->    b1 BLOB,
 ->    b2 BLOB COMMENT 'NDB_COLUMN=BLOB_INLINE_SIZE=8000'
 ->  ) ENGINE NDB;
Query OK, 0 rows affected (0.32 sec)

您可以通过查询ndbinfo.blobs表来查看BLOB列的BLOB_INLINE_SIZE设置，如下所示：

mysql> SELECT 
 ->   column_name AS 'Column Name', 
 ->   inline_size AS 'Inline Size', 
 ->   part_size AS 'Blob Part Size' 
 -> FROM ndbinfo.blobs 
 -> WHERE table_name = 't1';
+-------------+-------------+----------------+
| Column Name | Inline Size | Blob Part Size |
+-------------+-------------+----------------+
| b1          |         256 |           2000 |
| b2          |        8000 |           2000 |
+-------------+-------------+----------------+
2 rows in set (0.01 sec)

您还可以检查ndb_desc实用程序的输出，如下所示，相关行以强调文本显示：

$> ndb_desc -d test t1
-- t --
Version: 1
Fragment type: HashMapPartition
K Value: 6
Min load factor: 78
Max load factor: 80
Temporary table: no
Number of attributes: 3
Number of primary keys: 1
Length of frm data: 945
Max Rows: 0
Row Checksum: 1
Row GCI: 1
SingleUserMode: 0
ForceVarPart: 1
PartitionCount: 2
FragmentCount: 2
PartitionBalance: FOR_RP_BY_LDM
ExtraRowGciBits: 0
ExtraRowAuthorBits: 0
TableStatus: Retrieved
Table options: readbackup
HashMap: DEFAULT-HASHMAP-3840-2
-- Attributes --
a Int PRIMARY KEY DISTRIBUTION KEY AT=FIXED ST=MEMORY
*b1 Blob(256,2000,0) NULL AT=MEDIUM_VAR ST=MEMORY BV=2 BT=NDB$BLOB_64_1
b2 Blob(8000,2000,0) NULL AT=MEDIUM_VAR ST=MEMORY BV=2 BT=NDB$BLOB_64_2* -- Indexes -- 
PRIMARY KEY(a) - UniqueHashIndex
PRIMARY(a) - OrderedIndex

对于MAX_BLOB_PART_SIZE，=号和其后的值是可选的。使用除 0 或 1 之外的任何值会导致语法错误。

在列注释中使用MAX_BLOB_PART_SIZE的效果是将TEXT或BLOB列的 blob 部分大小设置为NDB支持的最大字节数（13948）。此选项可应用于 MySQL 支持的除TINYBLOB或TINYTEXT之外的任何 blob 列类型（BLOB、MEDIUMBLOB、LONGBLOB、TEXT、MEDIUMTEXT、LONGTEXT）。与BLOB_INLINE_SIZE不同，MAX_BLOB_PART_SIZE对JSON列没有影响。

要查看此选项的效果，我们首先在mysql客户端中运行以下 SQL 语句，创建一个具有两个BLOB列的表，一个（c1）没有额外选项，另一个（c2）具有MAX_BLOB_PART_SIZE：

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

从系统 shell 中运行ndb_desc实用程序，以获取有关刚刚创建的表的信息，如此示例所示：

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

输出中的列信息在Attributes下列出；对于c1和c2列，它在这里以强调文本显示。对于c1，blob 部分大小为 2000，即默认值；对于c2，它为 13948，由MAX_BLOB_PART_SIZE设置。

您还可以查询ndbinfo.blobs表来查看此内容，如下所示：

mysql> SELECT 
 ->   column_name AS 'Column Name', 
 ->   inline_size AS 'Inline Size', 
 ->   part_size AS 'Blob Part Size' 
 -> FROM ndbinfo.blobs 
 -> WHERE table_name = 't2';
+-------------+-------------+----------------+
| Column Name | Inline Size | Blob Part Size |
+-------------+-------------+----------------+
| c1          |         256 |           2000 |
| c2          |         256 |          13948 |
+-------------+-------------+----------------+
2 rows in set (0.00 sec)

您可以使用类似于这样的ALTER TABLE语句更改NDB表的给定 blob 列的 blob 部分大小，并使用SHOW CREATE TABLE在之后验证更改：

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

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

mysql> EXIT
Bye

ndb_desc的输出显示列的 blob 部分大小已按预期更改：

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

您还可以通过再次运行针对ndbinfo.blobs的查询来查看更改：

mysql> SELECT 
 ->   column_name AS 'Column Name', 
 ->   inline_size AS 'Inline Size', 
 ->   part_size AS 'Blob Part Size' 
 -> FROM ndbinfo.blobs 
 -> WHERE table_name = 't2';
+-------------+-------------+----------------+
| Column Name | Inline Size | Blob Part Size |
+-------------+-------------+----------------+
| c1          |         256 |          13948 |
| c2          |         256 |           2000 |
+-------------+-------------+----------------+
2 rows in set (0.00 sec)

可以为 blob 列同时设置BLOB_INLINE_SIZE和MAX_BLOB_PART_SIZE，如此CREATE TABLE语句所示：

mysql> CREATE TABLE test.t3 (
 ->   p INT NOT NULL PRIMARY KEY,
 ->   c1 JSON,
 ->   c2 JSON COMMENT 'NDB_COLUMN=BLOB_INLINE_SIZE=5000,MAX_BLOB_PART_SIZE'
 -> ) ENGINE NDB;
Query OK, 0 rows affected (0.28 sec)

查询blobs表显示该语句按预期工作：

mysql> SELECT 
 ->   column_name AS 'Column Name', 
 ->   inline_size AS 'Inline Size', 
 ->   part_size AS 'Blob Part Size' 
 -> FROM ndbinfo.blobs 
 -> WHERE table_name = 't3';
+-------------+-------------+----------------+
| Column Name | Inline Size | Blob Part Size |
+-------------+-------------+----------------+
| c1          |        4000 |           8100 |
| c2          |        5000 |           8100 |
+-------------+-------------+----------------+
2 rows in set (0.00 sec)

您还可以通过检查 ndb_desc 的输出来验证该语句是否有效。

更改列的 blob 部分大小必须使用复制的 ALTER TABLE 来完成；此操作无法在线执行（参见第 25.6.12 节，“NDB 集群中的 ALTER TABLE 在线操作”）。

有关 NDB 如何存储 blob 类型列的更多信息，请参阅字符串类型存储要求。

NDB_TABLE 选项

对于 NDB 集群表，在 CREATE TABLE 或 ALTER TABLE 语句中的表注释也可以用于指定一个 NDB_TABLE 选项，该选项由一个或多个名称-值对组成，如果需要，用逗号分隔，跟在字符串 NDB_TABLE= 后面。名称和值的完整语法如下所示：

COMMENT="NDB_TABLE=*ndb_table_option*[,*ndb_table_option*[,...]]"

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

引号字符串内不允许有空格。字符串不区分大小写。

可以通过表注释中的四个 NDB 表选项来设置这种方式。这几个选项将在接下来的几段中详细描述。

NOLOGGING：默认情况下，NDB 表是记录并进行检查点的。这使得它们在整个集群失败时是持久的。在创建或更改表时使用 NOLOGGING 意味着该表不会被重做日志记录或包含在本地检查点中。在这种情况下，该表仍然在数据节点之间复制以实现高可用性，并使用事务进行更新，但对其所做的更改不会记录在数据节点的重做日志中，并且其内容不会被检查点到磁盘上；在从集群故障中恢复时，集群会保留表定义，但不保留任何行，也就是说，该表是空的。

使用这种非记录表减少了数据节点对磁盘 I/O 和存储的需求，以及用于检查点的 CPU。这可能适用于频繁更新的短期数据，并且在极少数情况下发生总集群故障时可以接受所有数据的丢失。

还可以使用 ndb_table_no_logging 系统变量，使得在此变量生效时创建或更改的任何 NDB 表都表现得好像已经使用 NOLOGGING 注释创建。与直接使用注释时不同，在 SHOW CREATE TABLE 的输出中没有任何内容表明它是一个非记录表。推荐使用表注释方法，因为它提供了对该功能的每个表的控制，并且表模式的这一方面嵌入在表创建语句中，可以很容易地被 SQL 工具找到。

READ_BACKUP: 将此选项设置为 1 的效果与启用ndb_read_backup相同；允许从任何副本读取。这样做极大地提高了从表中读取的性能，对写入性能的影响相对较小。从 NDB 8.0.19 开始，READ_BACKUP的默认值为 1，ndb_read_backup的默认值为ON（以前，默认情况下，从任何副本读取是禁用的）。

您可以在线为现有表设置READ_BACKUP，使用类似于以下示例的ALTER TABLE语句：

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

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

有关ALTER TABLE的ALGORITHM选项的更多信息，请参阅第 25.6.12 节，“NDB Cluster 中的 ALTER TABLE 在线操作”。

PARTITION_BALANCE: 提供对分区分配和放置的额外控制。支持以下四种方案：

FOR_RP_BY_NODE: 每个节点一个分区。

每个节点上只有一个 LDM 存储主分区。每个分区在所有节点上的相同 LDM（相同 ID）中存储。
FOR_RA_BY_NODE: 每个节点组一个分区。

每个节点存储一个分区，可以是主复制品或备份复制品。每个分区在所有节点上都存储在相同的 LDM 中。
FOR_RP_BY_LDM: 每个节点上每个 LDM 一个分区；默认设置。

如果将READ_BACKUP设置为 1，则使用此设置。
FOR_RA_BY_LDM: 每个节点组中每个 LDM 一个分区。

这些分区可以是主分区或备份分区。
FOR_RA_BY_LDM_X_2: 每个节点组中每个 LDM 有两个分区。

这些分区可以是主分区或备份分区。
FOR_RA_BY_LDM_X_3: 每个节点组中每个 LDM 有三个分区。

这些分区可以是主分区或备份分区。
FOR_RA_BY_LDM_X_4: 每个节点组中每个 LDM 有四个分区。

这些分区可以是主分区或备份分区。

PARTITION_BALANCE是设置每个表分区数的首选接口。使用MAX_ROWS强制分区数已被弃用，但仍继续支持以确保向后兼容性；它可能在将来的 MySQL NDB Cluster 版本中被移除（Bug #81759，Bug #23544301）。

FULLY_REPLICATED控制表是否完全复制，即每个数据节点是否有表的完整副本。要启用表的完全复制，使用FULLY_REPLICATED=1。

这个设置也可以使用ndb_fully_replicated系统变量来控制。将其设置为ON会默认为所有新的NDB表启用该选项；默认值为OFF。ndb_data_node_neighbour系统变量也用于完全复制的表，以确保访问完全复制的表时，我们访问与此 MySQL 服务器本地的数据节点。

下面显示了创建NDB表时使用此类注释的CREATE TABLE语句示例：

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

该注释显示为SHOW CREATE TABLE的输出的一部分。注释的文本也可以通过查询 MySQL 信息模式TABLES表来获取，例如：

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

此注释语法也适用于NDB表的ALTER TABLE语句，如下所示：

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

从 NDB 8.0.21 开始，TABLE_COMMENT列显示了在ALTER TABLE语句后重新创建表所需的注释，如下所示：

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

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

请记住，与ALTER TABLE一起使用的表注释会替换表可能已有的任何现有注释。

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

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

在 NDB 8.0.21 之前，与ALTER TABLE一起使用的表注释会替换表可能已有的任何现有注释。这意味着（例如）READ_BACKUP值不会传递到由ALTER TABLE语句设置的新注释中，并且任何未指定的值都会恢复为默认值。（BUG#30428829）因此，使用 SQL 不再有任何方法来检索先前为注释设置的值。为了防止注释值恢复为默认值，需要保留现有注释字符串中的任何此类值，并将它们包含在传递给ALTER TABLE的注释中。

在ndb_desc的输出中，您还可以看到PARTITION_BALANCE选项的价值。ndb_desc还显示了表中是否设置了READ_BACKUP和FULLY_REPLICATED选项。有关此程序的更多信息，请参阅其描述。

15.1.21 创建表空间语句

原文：dev.mysql.com/doc/refman/8.0/en/create-tablespace.html

CREATE [UNDO] TABLESPACE *tablespace_name*

  *InnoDB and NDB:*
    [ADD DATAFILE '*file_name*']
    [AUTOEXTEND_SIZE [=] *value*]

  *InnoDB only:*
    [FILE_BLOCK_SIZE = value]
    [ENCRYPTION [=] {'Y' | 'N'}]

  *NDB only:*
    USE LOGFILE GROUP *logfile_group*
    [EXTENT_SIZE [=] *extent_size*]
    [INITIAL_SIZE [=] *initial_size*]
    [MAX_SIZE [=] *max_size*]
    [NODEGROUP [=] *nodegroup_id*]
    [WAIT]
    [COMMENT [=] '*string*']

  *InnoDB and NDB:*
    [ENGINE [=] *engine_name*]

  *Reserved for future use:*
    [ENGINE_ATTRIBUTE [=] '*string*']

此语句用于创建一个表空间。精确的语法和语义取决于所使用的存储引擎。在标准 MySQL 版本中，这始终是一个InnoDB表空间。MySQL NDB Cluster 还支持使用NDB存储引擎的表空间。

InnoDB 的考虑事项
NDB Cluster 的考虑事项
选项
注意事项
InnoDB 示例
NDB 示例

InnoDB 的考虑事项

CREATE TABLESPACE语法用于创建通用表空间或撤销表空间。在 MySQL 8.0.14 中引入的UNDO关键字必须指定以创建撤销表空间。

通用表空间是一个共享表空间。它可以容纳多个表，并支持所有表行格式。通用表空间可以相对于数据目录或独立于数据目录创建。

创建InnoDB通用表空间后，使用CREATE TABLE *tbl_name* ... TABLESPACE [=] *tablespace_name*或ALTER TABLE *tbl_name* TABLESPACE [=] *tablespace_name*来将表添加到表空间中。更多信息，请参见第 17.6.3.3 节，“通用表空间”。

撤销表空间包含撤销日志。可以通过指定完全限定的数据文件路径在所选位置创建撤销表空间。更多信息，请参见第 17.6.3.4 节，“撤销表空间”。

NDB Cluster 的考虑事项

此语句用于创建一个表空间，可以包含一个或多个数据文件，为 NDB Cluster Disk Data 表提供存储空间（参见第 25.6.11 节，“NDB Cluster Disk Data Tables”）。使用此语句创建一个数据文件并将其添加到表空间中。可以使用ALTER TABLESPACE语句（参见第 15.1.10 节，“ALTER TABLESPACE Statement”）向表空间添加其他数据文件。

注意

所有 NDB 集群磁盘数据对象共享相同的命名空间。这意味着每个磁盘数据对象必须具有唯一的名称（而不仅仅是给定类型的每个磁盘数据对象）。例如，您不能拥有具有相同名称的表空间和日志文件组，或者具有相同名称的表空间和数据文件。

一个或多个UNDO日志文件的日志文件组必须分配给要使用USE LOGFILE GROUP子句创建的表空间。logfile_group必须是使用CREATE LOGFILE GROUP创建的现有日志文件组（参见 Section 15.1.16, “CREATE LOGFILE GROUP Statement”）。多个表空间可以使用相同的日志文件组进行UNDO日志记录。

在设置EXTENT_SIZE或INITIAL_SIZE时，您可以选择在数字后面跟随一个数量级的单字母缩写，类似于my.cnf中使用的缩写。通常，这是M（表示兆字节）或G（表示千兆字节）中的一个字母。

INITIAL_SIZE和EXTENT_SIZE将按以下方式进行四舍五入：

EXTENT_SIZE四舍五入到最接近的 32K 的整数倍。
INITIAL_SIZE向下四舍五入到最接近的 32K 的整数倍；此结果向上四舍五入到最接近EXTENT_SIZE的整数倍（在任何四舍五入之后）。

注意

NDB为数据节点重新启动操作保留了表空间的 4%。此保留空间不能用于数据存储。

描述的四舍五入是显式完成的，并且当 MySQL 服务器执行任何此类四舍五入时会发出警告。这些四舍五入的值也被 NDB 内核用于计算INFORMATION_SCHEMA.FILES列值和其他用途。然而，为了避免意外结果，我们建议您在指定这些选项时始终使用 32K 的整数倍。

当使用ENGINE [=] NDB进行CREATE TABLESPACE时，在每个集群数据节点上创建一个表空间和关联的数据文件。您可以通过查询信息模式FILES表来验证数据文件是否已创建并获取有关它们的信息。（请参见本节后面的示例。）

（请参见 Section 28.3.15, “The INFORMATION_SCHEMA FILES Table”。）

选项

ADD DATAFILE：定义表空间数据文件的名称。在创建NDB表空间时，此选项始终是必需的；对于 MySQL 8.0.14 及更高版本的InnoDB，仅在创建撤销表空间时才是必需的。*file_name*，包括任何指定的路径，必须用单引号或双引号括起来。文件名（不包括文件扩展名）和目录名必须至少为一个字节的长度。不支持零长度的文件名和目录名。

由于InnoDB和NDB在处理数据文件方面存在相当大的差异，因此在接下来的讨论中，这两种存储引擎将分别进行讨论。

InnoDB 数据文件。 一个InnoDB表空间仅支持单个数据文件，其名称必须包含.ibd扩展名。

要将InnoDB通用表空间数据文件放在数据目录之外的位置，包括完全限定的路径或相对于数据目录的路径。仅允许为撤销表空间指定完全限定路径。如果不指定路径，则通用表空间将在数据目录中创建。未指定路径创建的撤销表空间将在由innodb_undo_directory变量定义的目录中创建。如果未定义innodb_undo_directory变量，则撤销表空间将在数据目录中创建。

为避免与隐式创建的按表创建文件表空间发生冲突，不支持在数据目录的子目录下创建InnoDB通用表空间。在数据目录之外创建通用表空间或撤销表空间时，目录必须存在，并且在创建表空间之前必须为InnoDB所知。要使目录为InnoDB所知，将其添加到innodb_directories值或将其添加到其值附加到innodb_directories值的变量之一。innodb_directories是一个只读变量。配置它需要重新启动服务器。

如果在创建InnoDB表空间时未指定ADD DATAFILE子句，则将隐式创建具有唯一文件名的表空间数据文件。唯一文件名是一个 128 位 UUID，格式为由短横线分隔的五组十六进制数字。如果存储引擎需要，将添加文件扩展名。对于InnoDB通用表空间数据文件，将添加.ibd文件扩展名。在复制环境中，在复制源服务器上创建的数据文件名与在副本上创建的数据文件名不同。

截至 MySQL 8.0.17，ADD DATAFILE子句在创建InnoDB表空间时不允许循环目录引用。例如，以下语句中的循环目录引用（/../）是不允许的：
```
CREATE TABLESPACE ts1 ADD DATAFILE ts1.ibd '*any_directory*/../ts1.ibd';
```
在 Linux 上存在一个例外，如果前面的目录是一个符号链接，则允许循环目录引用。例如，如果any_directory是一个符号链接，则上面示例中的数据文件路径是允许的。（数据文件路径仍然可以以'../'开头。）

NDB 数据文件。 一个NDB表空间支持多个数据文件，这些文件可以具有任何合法的文件名；可以使用ALTER TABLESPACE语句在创建后向 NDB 集群表空间添加更多数据文件。

默认情况下，NDB表空间数据文件会在数据节点文件系统目录中创建，即在数据节点的数据目录（DataDir）下名为ndb_*nodeid*_fs/TS的目录中，其中nodeid是数据节点的NodeId。要将数据文件放在除默认位置之外的位置，请包含绝对目录路径或相对于默认位置的路径。如果指定的目录不存在，NDB会尝试创建它；数据节点进程正在运行的系统用户帐户必须具有适当的权限才能这样做。

注意

在确定数据文件路径时，NDB不会展开~（波浪号）字符。

当在同一物理主机上运行多个数据节点时，以下注意事项适用：
- 创建数据文件时不能指定绝对路径。
- 除非每个数据节点都有单独的数据目录，否则无法在数据节点文件系统目录之外创建表空间数据文件。
- 如果每个数据节点都有自己的数据目录，则数据文件可以在该目录的任何位置创建。
- 如果每个数据节点都有自己的数据目录，那么也可以通过相对路径在节点的数据目录之外创建数据文件，只要这个路径对于在主机文件系统上运行的每个数据节点来说都解析为唯一位置。
FILE_BLOCK_SIZE：此选项专门针对InnoDB通用表空间，对于NDB是被忽略的，它定义了表空间数据文件的块大小。值可以用字节或千字节来指定。例如，可以将 8 千字节的文件块大小指定为 8192 或 8K。如果不指定此选项，FILE_BLOCK_SIZE默认为innodb_page_size的值。当您打算将表空间用于存储压缩的InnoDB表（ROW_FORMAT=COMPRESSED）时，必须定义表空间的FILE_BLOCK_SIZE。

如果FILE_BLOCK_SIZE等于innodb_page_size值，则表空间只能包含具有未压缩行格式（COMPACT，REDUNDANT和DYNAMIC）的表。具有COMPRESSED行格式的表具有与未压缩表不同的物理页大小。因此，压缩表不能与未压缩表共存于同一表空间中。

对于包含压缩表的通用表空间，必须指定FILE_BLOCK_SIZE，并且FILE_BLOCK_SIZE的值必须是与innodb_page_size值相关的有效压缩页大小。此外，压缩表的物理页大小（KEY_BLOCK_SIZE）必须等于FILE_BLOCK_SIZE/1024。例如，如果innodb_page_size=16K，而FILE_BLOCK_SIZE=8K，则表的KEY_BLOCK_SIZE必须为 8。更多信息，请参见 Section 17.6.3.3, “通用表空间”。
USE LOGFILE GROUP：对于NDB必需，这是先前使用CREATE LOGFILE GROUP创建的日志文件组的名称。对于InnoDB不支持，会导致错误。
EXTENT_SIZE：此选项特定于 NDB，在 InnoDB 中不受支持，会导致错误。EXTENT_SIZE设置表空间中任何文件使用的 extent 的大小（以字节为单位）。默认值为 1M。最小大小为 32K，理论上的最大值为 2G，尽管实际最大大小取决于许多因素。在大多数情况下，更改 extent 大小对性能没有任何可测量的影响，建议除了最不寻常的情况外，使用默认值。

一个 extent 是磁盘空间分配的单位。一个 extent 填满尽可能多的数据，然后使用另一个 extent。理论上，每个数据文件最多可以使用 65,535（64K）个 extent；然而，建议的最大值为 32,768（32K）。单个数据文件的建议最大大小为 32G，即 32K extents × 每个 extent 1MB。此外，一旦将 extent 分配给给定分区，就不能用于存储来自不同分区的数据；一个 extent 不能存储来自多个分区的数据。这意味着，例如，一个具有单个数据文件的表空间，其INITIAL_SIZE（在下一项中描述）为 256 MB，EXTENT_SIZE为 128M，只有两个 extent，因此最多可以用于存储来自最多两个不同磁盘数据表分区的数据。

通过查询信息模式FILES表，您可以看到给定数据文件中剩余的空间有多少，从而估算文件中剩余的空间量。有关进一步讨论和示例，请参见 Section 28.3.15, “The INFORMATION_SCHEMA FILES Table”。
INITIAL_SIZE：此选项特定于NDB，不受InnoDB支持，在那里会出现错误。

INITIAL_SIZE参数设置了使用ADD DATATFILE指定的数据文件的总大小（以字节为单位）。创建了此文件后，其大小不能更改；但是，您可以使用ALTER TABLESPACE ... ADD DATAFILE向表空间添加更多数据文件。

INITIAL_SIZE是可选的；其默认值为 134217728（128 MB）。

在 32 位系统上，INITIAL_SIZE的最大支持值为 4294967296（4 GB）。
AUTOEXTEND_SIZE：在 MySQL 8.0.23 之前被 MySQL 忽略；从 MySQL 8.0.23 开始，定义了InnoDB在表空间变满时扩展的量。设置必须是 4MB 的倍数。默认设置为 0，这将导致表空间根据隐式默认行为进行扩展。有关更多信息，请参见 Section 17.6.3.9, “Tablespace AUTOEXTEND_SIZE Configuration”。

在任何 MySQL NDB Cluster 8.0 的版本中都没有任何影响，无论使用的存储引擎是什么。
MAX_SIZE：当前 MySQL 忽略；保留以备可能的将来使用。在 MySQL 8.0 或 MySQL NDB Cluster 8.0 的任何版本中都没有任何影响，无论使用的存储引擎是什么。
NODEGROUP：当前 MySQL 忽略；保留以备可能的将来使用。在 MySQL 8.0 或 MySQL NDB Cluster 8.0 的任何版本中都没有任何影响，无论使用的存储引擎是什么。
WAIT：当前 MySQL 忽略；保留以备可能的将来使用。在 MySQL 8.0 或 MySQL NDB Cluster 8.0 的任何版本中都没有任何影响，无论使用的存储引擎是什么。
COMMENT：当前 MySQL 忽略；保留以备可能的将来使用。在 MySQL 8.0 或 MySQL NDB Cluster 8.0 的任何版本中都没有任何影响，无论使用的存储引擎是什么。
ENCRYPTION子句为InnoDB通用表空间启用或禁用页面级数据加密。MySQL 8.0.13 引入了对通用表空间的加密支持。

截至 MySQL 8.0.16，如果未指定 ENCRYPTION 子句，则default_table_encryption 设置控制是否启用加密。ENCRYPTION 子句会覆盖default_table_encryption 设置。但是，如果启用了table_encryption_privilege_check 变量，则需要TABLE_ENCRYPTION_ADMIN 权限才能使用与default_table_encryption 设置不同的 ENCRYPTION 子句设置。

必须在创建启用加密的表空间之前安装和配置一个密钥环插件。

当通用表空间加密时，驻留在表空间中的所有表都会被加密。同样，创建在加密表空间中的表也会被加密。

更多信息，请参见第 17.13 节，“InnoDB 数据静态加密”
ENGINE：定义使用表空间的存储引擎，其中 engine_name 是存储引擎的名称。目前，标准 MySQL 8.0 发行版仅支持 InnoDB 存储引擎。MySQL NDB Cluster 支持 NDB 和 InnoDB 表空间。如果未指定选项，则 ENGINE 使用 default_storage_engine 系统变量的值。
ENGINE_ATTRIBUTE 选项（自 MySQL 8.0.21 起可用）用于指定主存储引擎的表空间属性。该选项保留供将来使用。

允许的值是包含有效JSON文档的字符串文字或空字符串（''）。无效的JSON会被拒绝。
```
CREATE TABLESPACE ts1 ENGINE_ATTRIBUTE='{"*key*":"*value*"}';
```
ENGINE_ATTRIBUTE 值可以重复而不会出错。在这种情况下，将使用最后指定的值。

ENGINE_ATTRIBUTE 值不会被服务器检查，也不会在表的存储引擎更改时清除。

注意

有关 MySQL 表空间命名规则，请参见第 11.2 节，“模式对象名称”。除了这些规则外，斜杠字符（“/”）不允许使用，也不能使用以 innodb_ 开头的名称，因为此前缀保留供系统使用。
创建临时通用表空间不受支持。
通用表空间不支持临时表。
TABLESPACE选项可用于将InnoDB表分区或子分区分配给文件-每表表空间，可与CREATE TABLE或ALTER TABLE一起使用。所有分区必须属于相同的存储引擎。不支持将表分区分配给共享的 InnoDB 表空间。共享表空间包括 InnoDB 系统表空间和通用表空间。
通用表空间支持使用CREATE TABLE ... TABLESPACE添加任何行格式的表。不需要启用innodb_file_per_table。
innodb_strict_mode不适用于通用表空间。表空间管理规则严格执行，与innodb_strict_mode无关。如果CREATE TABLESPACE参数不正确或不兼容，则无论innodb_strict_mode设置如何，操作都会失败。当使用CREATE TABLE ... TABLESPACE或ALTER TABLE ... TABLESPACE将表添加到通用表空间时，将忽略innodb_strict_mode，但该语句将被评估为启用了innodb_strict_mode。
使用DROP TABLESPACE来删除表空间。在删除表空间之前，必须使用DROP TABLE删除表空间中的所有表。在删除 NDB Cluster 表空间之前，还必须使用一个或多个ALTER TABLESPACE ... DROP DATATFILE语句删除所有数据文件。参见 Section 25.6.11.1, “NDB Cluster Disk Data Objects”。
添加到 InnoDB 通用表空间的 InnoDB 表的所有部分都驻留在通用表空间中，包括索引和BLOB页面。

对于分配给表空间的 NDB 表，只有未建立索引的列存储在磁盘上，并实际使用表空间数据文件。所有 NDB 表的索引和已建立索引的列始终保留在内存中。
与系统表空间类似，截断或删除存储在通用表空间中的表会在通用表空间的.ibd 数据文件中创建内部的可用空间，该空间只能用于新的 InnoDB 数据。与文件-每表表空间不同，空间不会像对操作系统释放那样被释放。
通用表空间不与任何数据库或模式相关联。
ALTER TABLE ... DISCARD TABLESPACE 和 ALTER TABLE ...IMPORT TABLESPACE 不支持属于通用表空间的表。
服务器对引用通用表空间的 DDL 使用表空间级元数据锁定。相比之下，服务器对引用每个表的文件表空间的 DDL 使用表级元数据锁定。
生成的或现有的表空间不能更改为通用表空间。
通用表空间名称与每个表的文件表空间名称之间没有冲突。通用表空间名称中不允许出现文件表空间名称中的“/”字符。
mysqldump 和 mysqlpump 不会转储InnoDB CREATE TABLESPACE语句。

InnoDB 示例

此示例演示了创建一个通用表空间，并添加三个不同行格式的未压缩表。

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

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

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

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

此示例演示了创建一个通用表空间并添加一个压缩表。该示例假定默认的innodb_page_size值为 16K。8192 的FILE_BLOCK_SIZE要求压缩表具有 8 的KEY_BLOCK_SIZE。

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

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

此示例演示了创建一个通用表空间而不指定ADD DATAFILE子句，这在 MySQL 8.0.14 中是可选的。

mysql> CREATE TABLESPACE `ts3` ENGINE=INNODB;

此示例演示了创建撤销表空间。

mysql> CREATE UNDO TABLESPACE *undo_003* ADD DATAFILE '*undo_003*.ibu';

NDB 示例

假设您希望使用名为mydata-1.dat的数据文件创建一个名为myts的 NDB Cluster 磁盘数据表空间。一个NDB表空间总是需要使用一个或多个撤销日志文件组的日志文件。对于此示例，我们首先创建一个名为mylg的日志文件组，其中包含一个名为myundo-1.dat的撤销长文件，使用此处显示的CREATE LOGFILE GROUP语句：

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

现在，您可以使用以下语句创建先前描述的表空间：

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

您现在可以使用带有TABLESPACE和STORAGE DISK选项的CREATE TABLE语句来创建一个磁盘数据表，类似于下面显示的内容：

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

需要注意的是，由于id、lname和fname列都被索引，因此mytable中只有dob和joined列实际上存储在磁盘上。

如前所述，当CREATE TABLESPACE与ENGINE [=] NDB一起使用时，将在每个 NDB Cluster 数据节点上创建一个表空间和相关的数据文件。您可以通过查询信息模式FILES表来验证数据文件是否已创建并获取有关它们的信息，如下所示：

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

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

有关更多信息和示例，请参见第 25.6.11.1 节，“NDB 集群磁盘数据对象”。

15.1.22 CREATE TRIGGER Statement

原文：dev.mysql.com/doc/refman/8.0/en/create-trigger.html

CREATE
    [DEFINER = *user*]
    TRIGGER [IF NOT EXISTS] *trigger_name*
    *trigger_time* *trigger_event*
    ON *tbl_name* FOR EACH ROW
    [*trigger_order*]
    *trigger_body*

*trigger_time*: { BEFORE | AFTER }

*trigger_event*: { INSERT | UPDATE | DELETE }

*trigger_order*: { FOLLOWS | PRECEDES } *other_trigger_name*

此语句创建一个新触发器。触发器是与表关联的命名数据库对象，当表发生特定事件时激活。触发器与名为 tbl_name 的表关联，该表必须引用永久表。您不能将触发器与 TEMPORARY 表或视图关联。

触发器名称存在于模式命名空间中，这意味着所有触发器在模式内必须具有唯一名称。不同模式中的触发器可以具有相同的名称。

IF NOT EXISTS 可以防止在同一模式中存在具有相同名称、在同一表上的触发器时发生错误。此选项从 MySQL 8.0.29 开始支持。

本节描述了 CREATE TRIGGER 语法。有关更多讨论，请参见第 27.3.1 节“触发器语法和示例”。

CREATE TRIGGER 需要与触发器关联的表的 TRIGGER 权限。如果存在 DEFINER 子句，则所需的权限取决于 user 值，如第 27.6 节“存储对象访问控制” 中所讨论的。如果启用了二进制日志记录，则 CREATE TRIGGER 可能需要 SUPER 权限，如第 27.7 节“存储程序二进制日志记录” 中所述。

DEFINER 子句确定在触发器激活时用于检查访问权限的安全上下文，如本节后面所述。

trigger_time 是触发器动作时间。它可以是 BEFORE 或 AFTER，表示触发器在修改每行之前或之后激活。

在触发器激活之前会进行基本列值检查，因此您不能使用 BEFORE 触发器将不适合列类型的值转换为有效值。

trigger_event 表示激活触发器的操作类型。这些 trigger_event 值是允许的：

INSERT: 当向表中插入新行时触发器会激活（例如，通过 INSERT、LOAD DATA 和 REPLACE 语句）。
UPDATE: 当行被修改时触发器会激活（例如，通过 UPDATE 语句）。
DELETE：该触发器在从表中删除行时激活（例如，通过DELETE和REPLACE语句）。对表进行DROP TABLE和TRUNCATE TABLE操作不会激活该触发器，因为它们不使用DELETE。删除分区也不会激活DELETE触发器。

trigger_event 不代表激活触发器的 SQL 语句类型，而更多地代表一种表操作类型。例如，一个INSERT触发器不仅激活INSERT语句，还激活LOAD DATA语句，因为这两个语句都向表中插入行。

这种情况可能会令人困惑的一个例子是INSERT INTO ... ON DUPLICATE KEY UPDATE ...语法：一个BEFORE INSERT触发器为每一行激活，接着是一个AFTER INSERT触发器或者BEFORE UPDATE和AFTER UPDATE触发器，具体取决于该行是否存在重复键。

注意

级联外键操作不会触发触发器。

可以为给定表定义多个具有相同触发事件和动作时间的触发器。例如，可以为表定义两个BEFORE UPDATE触发器。默认情况下，具有相同触发事件和动作时间的触发器按照它们创建的顺序激活。要影响触发器顺序，请指定一个trigger_order子句，指示FOLLOWS或PRECEDES以及一个同样具有相同触发事件和动作时间的现有触发器的名称。使用FOLLOWS，新触发器在现有触发器之后激活。使用PRECEDES，新触发器在现有触发器之前激活。

trigger_body 是触发器激活时要执行的语句。要执行多个语句，请使用BEGIN ... END复合语句结构。这还使您能够使用存储过程中允许的相同语句。请参阅 Section 15.6.1, “BEGIN ... END Compound Statement”。某些语句在触发器中不允许使用；请参阅 Section 27.8, “Restrictions on Stored Programs”。

在触发器主体中，您可以通过使用别名OLD和NEW引用主题表（与触发器关联的表）中的列。OLD.*col_name*指的是更新或删除之前现有行的列。NEW.*col_name*指的是要插入的新行或更新后的现有行的列。

触发器不能使用NEW.*col_name*或使用OLD.*col_name*来引用生成列。有关生成列的信息，请参见第 15.1.20.8 节，“CREATE TABLE and Generated Columns”。

MySQL 在创建触发器时存储sql_mode系统变量设置，并始终以此设置执行触发器主体，无论触发器开始执行时当前服务器 SQL 模式如何。

DEFINER子句指定在触发器激活时检查访问权限时要使用的 MySQL 帐户。如果存在DEFINER子句，则user值应为指定为'*user_name*'@'*host_name*'、CURRENT_USER或CURRENT_USER()的 MySQL 帐户。允许的user值取决于您拥有的权限，如第 27.6 节“存储对象访问控制”中所讨论的。还请参阅该部分以获取有关触发器安全性的其他信息。

如果省略DEFINER子句，则默认定义者是执行CREATE TRIGGER语句的用户。这与明确指定DEFINER = CURRENT_USER相同。

MySQL 在检查触发器权限时考虑DEFINER用户如下：

在CREATE TRIGGER时，发出该语句的用户必须具有TRIGGER权限。
在触发器激活时，权限将针对DEFINER用户进行检查。此用户必须具有以下权限：
- 主题表的TRIGGER权限。
- 如果触发器主体中使用OLD.*col_name*或NEW.*col_name*引用表列，则需要对主题表具有SELECT权限。
- 如果表列是触发器主体中SET NEW.*col_name* = *value*的目标，则需要对主题表具有UPDATE权限。
- 触发器执行的语句通常需要的其他权限。

在触发器体内，CURRENT_USER函数返回在触发器激活时用于检查权限的账户。这是DEFINER用户，而不是触发器激活的用户。有关触发器内用户审计的信息，请参阅 Section 8.2.23, “SQL-Based Account Activity Auditing”。

如果你使用LOCK TABLES来锁定一个带有触发器的表，那么触发器中使用的表也会被锁定，就像在 LOCK TABLES and Triggers 中描述的那样。

欲了解更多关于触发器使用的讨论，请参阅 Section 27.3.1, “Trigger Syntax and Examples”。

15.1.23 创建视图语句

原文：dev.mysql.com/doc/refman/8.0/en/create-view.html

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

CREATE VIEW语句创建一个新视图，如果给出OR REPLACE子句，则替换现有视图。如果视图不存在，CREATE OR REPLACE VIEW与CREATE VIEW相同。如果视图存在，CREATE OR REPLACE VIEW将其替换。

有关视图使用限制的信息，请参阅第 27.9 节，“视图限制”。

select_statement是一个SELECT语句，提供了视图的定义。（从视图中选择实际上是使用SELECT语句进行选择。）select_statement可以从基本表或其他视图中进行选择。从 MySQL 8.0.19 开始，SELECT语句可以使用VALUES语句作为其来源，或者可以被替换为TABLE语句，就像CREATE TABLE ... SELECT一样。

视图定义在创建时“冻结”，不受后续对基础表定义的更改的影响。例如，如果一个视图被定义为在表上SELECT *，那么稍后添加到表中的新列不会成为视图的一部分，而从表中删除的列在从视图中选择时会导致错误。

ALGORITHM子句影响 MySQL 处理视图的方式。DEFINER和SQL SECURITY子句指定在视图调用时检查访问权限时要使用的安全上下文。WITH CHECK OPTION子句可以用于限制对视图引用的表中的行的插入或更新。这些子句稍后在本节中描述。

CREATE VIEW语句需要视图的CREATE VIEW权限，并且对SELECT语句中选择的每个列都需要一些权限。对于在SELECT语句中的其他地方使用的列，您必须具有SELECT权限。如果存在OR REPLACE子句，则还必须具有视图的DROP权限。如果存在DEFINER子句，则所需的权限取决于user值，如第 27.6 节，“存储对象访问控制”中所讨论的那样。

当引用视图时，权限检查将按照本节后面描述的方式进行。

视图属于数据库。默认情况下，新视图将在默认数据库中创建。要在特定数据库中显式创建视图，请使用db_name.view_name语法，以数据库名称限定视图名称：

CREATE VIEW test.v AS SELECT * FROM t;

SELECT语句中的未限定表或视图名称也会根据默认数据库进行解释。视图可以通过使用适当的数据库名称限定表或视图名称来引用其他数据库中的表或视图。

在数据库中，基本表和视图共享相同的命名空间，因此基本表和视图不能具有相同的名称。

由SELECT语句检索的列可以是对表列的简单引用，也可以是使用函数、常量值、运算符等的表达式。

视图必须具有唯一的列名，不能有重复，就像基本表一样。默认情况下，由SELECT语句检索的列的名称用于视图列名。要为视图列定义显式名称，请指定可选的column_list子句作为逗号分隔的标识符列表。column_list中的名称数量必须与由SELECT语句检索的列的数量相同。

视图可以从许多种类的SELECT语句创建。它可以引用基本表或其他视图。它可以使用连接、UNION和子查询。SELECT甚至不需要引用任何表：

CREATE VIEW v_today (today) AS SELECT CURRENT_DATE;

以下示例定义了一个视图，从另一个表中选择了两列，以及从这些列计算出的表达式：

mysql> CREATE TABLE t (qty INT, price INT);
mysql> INSERT INTO t VALUES(3, 50);
mysql> CREATE VIEW v AS SELECT qty, price, qty*price AS value FROM t;
mysql> SELECT * FROM v;
+------+-------+-------+
| qty  | price | value |
+------+-------+-------+
|    3 |    50 |   150 |
+------+-------+-------+

视图定义受以下限制：

SELECT语句不能引用系统变量或用户定义的变量。
在存储程序中，SELECT语句不能引用程序参数或局部变量。
SELECT语句不能引用准备好的语句参数。
定义中引用的任何表或视图必须存在。如果在创建视图之后，定义引用的表或视图被删除，则使用该视图会导致错误。要检查此类问题的视图定义，请使用CHECK TABLE语句。
定义不能引用TEMPORARY表，也不能创建TEMPORARY视图。
不能将触发器与视图关联。
在SELECT语句中，列名的别名会被检查，其最大长度为 64 个字符（而不是最大别名长度为 256 个字符）。

视图定义中允许使用ORDER BY，但如果使用具有自己ORDER BY的语句从视图中进行选择，则会被忽略。

对于定义中的其他选项或子句，它们会被添加到引用视图的语句的选项或子句中，但效果是未定义的。例如，如果视图定义包括LIMIT子句，并且您使用具有自己LIMIT子句的语句从视图中进行选择，则未定义哪个限制适用。这个原则也适用于跟随SELECT关键字的ALL、DISTINCT或SQL_SMALL_RESULT等选项，以及诸如INTO、FOR UPDATE、FOR SHARE、LOCK IN SHARE MODE和PROCEDURE等子句。

如果更改查询处理环境，可能会影响从视图中获取的结果：

mysql> CREATE VIEW v (mycol) AS SELECT 'abc';
Query OK, 0 rows affected (0.01 sec)

mysql> SET sql_mode = '';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT "mycol" FROM v;
+-------+
| mycol |
+-------+
| mycol |
+-------+
1 row in set (0.01 sec)

mysql> SET sql_mode = 'ANSI_QUOTES';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT "mycol" FROM v;
+-------+
| mycol |
+-------+
| abc   |
+-------+
1 row in set (0.00 sec)

DEFINER和SQL SECURITY子句确定在执行引用视图的语句时使用哪个 MySQL 账户来检查访问权限。有效的SQL SECURITY特性值为DEFINER（默认）和INVOKER。这表示所需的权限必须由定义或调用视图的用户持有。

如果存在DEFINER子句，则user值应为 MySQL 账户，指定为'*user_name*'@'*host_name*、CURRENT_USER或CURRENT_USER()。允许的user值取决于您拥有的权限，如第 27.6 节“存储对象访问控制”中所讨论的。还请参阅该部分以获取有关视图安全性的其他信息。

如果省略了DEFINER子句，则默认的定义者是执行CREATE VIEW语句的用户。这与明确指定DEFINER = CURRENT_USER相同。

在视图定义中，CURRENT_USER函数默认返回视图的DEFINER值。对于使用SQL SECURITY INVOKER特性定义的视图，CURRENT_USER返回视图调用者的账户。有关视图内用户审计的信息，请参阅第 8.2.23 节“基于 SQL 的账户活动审计”。

在使用SQL SECURITY DEFINER特性定义的存储过程中，CURRENT_USER返回该存储过程的DEFINER值。如果视图定义中包含CURRENT_USER的DEFINER值，这也会影响到在此类存储过程中定义的视图。

MySQL 检查视图权限的方式如下：

在视图定义时，视图创建者必须具有使用视图访问的顶层对象所需的权限。例如，如果视图定义引用表列，则创建者必须对定义中的每个列具有某些权限，并且对定义中其他地方使用的每个列都需要SELECT权限。如果定义引用了一个存储函数，则只能检查调用函数所需的权限。在函数调用时需要的权限只能在执行时检查：对于不同的调用，函数内的不同执行路径可能被采取。
引用视图的用户必须具有适当的权限来访问它（SELECT用于从中选择，INSERT用于插入等）。
当引用了一个视图时，会根据视图DEFINER账户或调用者持有的权限进行对象访问权限检查，具体取决于SQL SECURITY特性是DEFINER还是INVOKER。
如果引用视图导致执行存储函数，则在函数内执行的语句的权限检查取决于函数的SQL SECURITY特性是DEFINER还是INVOKER。如果安全特性是DEFINER，则函数以DEFINER账户的权限运行。如果特性是INVOKER，则函数以视图的SQL SECURITY特性确定的权限运行。

例如：一个视图可能依赖于一个存储函数，而该函数可能调用其他存储过程。例如，以下视图调用了一个存储函数f()：

CREATE VIEW v AS SELECT * FROM t WHERE t.id = f(t.name);

假设f()包含如下语句：

IF name IS NULL then
  CALL p1();
ELSE
  CALL p2();
END IF;

在执行f()时，需要检查执行语句所需的权限。这可能意味着在f()内部执行时需要p1()或p2()的权限，具体取决于f()内的执行路径。这些权限必须在运行时检查，而需要拥有这些权限的用户由视图v和函数f()的SQL SECURITY值确定。

视图的DEFINER和SQL SECURITY子句是标准 SQL 的扩展。在标准 SQL 中，视图使用SQL SECURITY DEFINER规则处理。标准规定视图的定义者，即视图模式的所有者，获得视图的适用权限（例如，SELECT）并可以授予它们。MySQL 没有“模式所有者”的概念，因此 MySQL 添加了一个子句来标识定义者。DEFINER子句是一个扩展，其目的是拥有标准的内容；也就是说，永久记录谁定义了视图。这就是为什么默认的DEFINER值是视图创建者的帐户。

可选的ALGORITHM子句是 MySQL 对标准 SQL 的扩展。它影响 MySQL 处理视图的方式。ALGORITHM有三个值：MERGE、TEMPTABLE或UNDEFINED。有关更多信息，请参见 Section 27.5.2, “View Processing Algorithms”，以及 Section 10.2.2.4, “Optimizing Derived Tables, View References, and Common Table Expressions with Merging or Materialization”。

一些视图是可更新的。也就是说，你可以在UPDATE、DELETE或INSERT等语句中使用它们来更新底层表的内容。要使视图可更新，视图中的行与底层表中的行之间必须是一对一的关系。还有一些其他构造使视图不可更新。

视图中的生成列被认为是可更新的，因为可以对其进行赋值。但是，如果显式更新这样的列，唯一允许的值是DEFAULT。有关生成列的信息，请参见 Section 15.1.20.8, “CREATE TABLE and Generated Columns”。

可以为可更新视图提供WITH CHECK OPTION子句，以防止插入或更新行，除非select_statement中的WHERE子句为真。

在可更新视图的WITH CHECK OPTION子句中，LOCAL和CASCADED关键字确定了在视图以另一个视图的形式定义时进行检查测试的范围。LOCAL关键字将CHECK OPTION限制在正在定义的视图中。CASCADED会导致对底层视图的检查也被评估。当没有给出关键字时，默认值为CASCADED。

关于可更新视图和WITH CHECK OPTION子句的更多信息，请参见第 27.5.3 节，“可更新和可插入视图”，以及第 27.5.4 节，“带有 CHECK OPTION 子句的视图”。

15.1.24 DROP DATABASE 语句

原文：dev.mysql.com/doc/refman/8.0/en/drop-database.html

DROP {DATABASE | SCHEMA} [IF EXISTS] *db_name*

DROP DATABASE会删除数据库中的所有表并删除数据库。对于这个语句要非常小心！要使用DROP DATABASE，您需要在数据库上拥有DROP权限。DROP SCHEMA是DROP DATABASE的同义词。

重要提示

当一个数据库被删除时，为该数据库专门授予的权限不会自动删除。必须手动删除它们。参见 Section 15.7.1.6, “GRANT Statement”。

IF EXISTS用于防止数据库不存在��发生错误。

如果默认数据库被删除，那么默认数据库将被取消设置（DATABASE()函数返回NULL）。

如果你在一个符号链接的数据库上使用DROP DATABASE，那么链接和原始数据库都会被删除。

DROP DATABASE会返回被删除的表的数量。

DROP DATABASE语句会从给定的数据库目录中删除 MySQL 在正常操作期间可能创建的文件和目录。这包括以下列表中显示的所有带有扩展名的文件：

.BAK
.DAT
.HSH
.MRG
.MYD
.MYI
.cfg
.db
.ibd
.ndb

如果在 MySQL 删除了刚列出的文件后，数据库目录中仍然存在其他文件或目录，则无法删除数据库目录。在这种情况下，您必须手动删除任何剩余的文件或目录，并再次发出DROP DATABASE语句。

删除数据库不会移除在该数据库中创建的任何TEMPORARY表。TEMPORARY表在创建它们的会话结束时会自动删除。参见 Section 15.1.20.2, “CREATE TEMPORARY TABLE Statement”。

你也可以使用mysqladmin来删除数据库。参见 Section 6.5.2, “mysqladmin — A MySQL Server Administration Program”。

15.1.25 删除事件语句

原文：dev.mysql.com/doc/refman/8.0/en/drop-event.html

DROP EVENT [IF EXISTS] *event_name*

这个语句会删除名为event_name的事件。该事件立即停止活动，并从服务器完全删除。

如果事件不存在，则会出现错误 ERROR 1517 (HY000): 未知事件 'event_name'。您可以使用 IF EXISTS 来覆盖此错误，并使语句对不存在的事件生成警告。

这个语句需要对要删除事件所属模式的EVENT权限。

15.1.26 `DROP FUNCTION` 语句

原文：dev.mysql.com/doc/refman/8.0/en/drop-function.html

DROP FUNCTION 语句用于删除存储函数和可加载函数：

有关删除存储函数的信息，请参阅第 15.1.29 节，“DROP PROCEDURE 和 DROP FUNCTION 语句”。
有关删除可加载函数的信息，请参阅第 15.7.4.2 节，“可加载函数的 DROP FUNCTION 语句”。

15.1.27 DROP INDEX 语句

原文：dev.mysql.com/doc/refman/8.0/en/drop-index.html

DROP INDEX *index_name* ON *tbl_name*
    [*algorithm_option* | *lock_option*] ...

*algorithm_option*:
    ALGORITHM [=] {DEFAULT | INPLACE | COPY}

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

DROP INDEX 从表 tbl_name 中删除名为 index_name 的索引。此语句被映射为一个 ALTER TABLE 语句来删除索引。请参见 Section 15.1.9, “ALTER TABLE Statement”。

要删除主键，索引名称始终为 PRIMARY，必须将其指定为带引号的标识符，因为 PRIMARY 是一个保留字：

DROP INDEX `PRIMARY` ON t;

对于NDB表中的可变宽度列的索引是在线删除的；也就是说，不需要进行任何表复制。尽管表在操作期间针对相同的 API 节点被锁定，但不会阻止其他 NDB Cluster API 节点访问该表。服务器会在确定可能进行此操作时自动执行；您不需要使用任何特殊的 SQL 语法或服务器选项来触发此操作。

ALGORITHM 和 LOCK 子句可以用来影响表复制方法和读写表时的并发级别，当其索引正在被修改时。它们与ALTER TABLE语句具有相同的含义。更多信息，请参见 Section 15.1.9, “ALTER TABLE Statement”

MySQL NDB Cluster 支持使用标准 MySQL Server 中支持的相同 ALGORITHM=INPLACE 语法进行在线操作。更多信息，请参见 Section 25.6.12, “Online Operations with ALTER TABLE in NDB Cluster”。

第 15.1.28 节 DROP LOGFILE GROUP 语句

原文：dev.mysql.com/doc/refman/8.0/en/drop-logfile-group.html

DROP LOGFILE GROUP *logfile_group*
    ENGINE [=] *engine_name*

此语句删除名为logfile_group的日志文件组。日志文件组必须已经存在，否则将出现错误。（有关创建日志文件组的信息，请参见第 15.1.16 节，“CREATE LOGFILE GROUP 语句”。）

重要提示

在删除日志文件组之前，您必须删除所有使用该日志文件组进行UNDO日志记录的表空间。

必需的ENGINE子句提供了要删除的日志文件组所使用的存储引擎的名称。目前，engine_name的唯一允许值为NDB和NDBCLUSTER。

DROP LOGFILE GROUP仅适用于 NDB Cluster 的 Disk Data 存储。请参阅第 25.6.11 节，“NDB Cluster Disk Data Tables”。

15.1.29 DROP PROCEDURE and DROP FUNCTION Statements

原文：dev.mysql.com/doc/refman/8.0/en/drop-procedure.html

DROP {PROCEDURE | FUNCTION} [IF EXISTS] *sp_name*

这些语句用于删除存储例程（存储过程或函数）。也就是说，指定的例程将从服务器中移除。(DROP FUNCTION 也用于删除可加载函数；参见 Section 15.7.4.2, “DROP FUNCTION Statement for Loadable Functions”.)

要删除存储例程，您必须具有 ALTER ROUTINE 权限。（如果启用了 automatic_sp_privileges 系统变量，则在创建例程时自动授予该权限和 EXECUTE 给例程创建者，并在删除例程时从创建者那里撤销。请参见 Section 27.2.2, “Stored Routines and MySQL Privileges”.)

另外，如果例程的定义者具有 SYSTEM_USER 权限，则删除它的用户也必须具有此权限。这在 MySQL 8.0.16 及更高版本中执行。

IF EXISTS 子句是 MySQL 的扩展。如果存储过程或函数不存在，它可以防止错误发生。会产生一个警告，可以通过 SHOW WARNINGS 查看。

DROP FUNCTION 也用于删除可加载函数（参见 Section 15.7.4.2, “DROP FUNCTION Statement for Loadable Functions”).

15.1.30 DROP SERVER 语句

原文：dev.mysql.com/doc/refman/8.0/en/drop-server.html

DROP SERVER [ IF EXISTS ] *server_name*

删除名为*server_name*的服务器定义。mysql.servers表中的相应行将被删除。此语句需要SUPER权限。

删除表的服务器不会影响任何在创建时使用此连接信息的FEDERATED表。请参阅第 15.1.18 节，“CREATE SERVER Statement”。

DROP SERVER 会导致隐式提交。请参阅第 15.3.3 节，“导致隐式提交的语句”。

DROP SERVER 不会被写入二进制日志，无论使用的日志格式是什么。

15.1.31 删除空间参考系统语句

原文：dev.mysql.com/doc/refman/8.0/en/drop-spatial-reference-system.html

DROP SPATIAL REFERENCE SYSTEM
    [IF EXISTS]
    *srid*

*srid*: *32-bit unsigned integer*

此语句从数据字典中删除一个空间参考系统（SRS）定义。它需要SUPER权限。

示例：

DROP SPATIAL REFERENCE SYSTEM 4120;

如果不存在具有 SRID 值的 SRS 定义，则会发生错误，除非指定了 IF EXISTS。在这种情况下，会发出警告而不是错误。

如果某个现有表中的某列使用了 SRID 值，则会发生错误。例如：

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

要确定哪些列使用了 SRID，请使用以下查询：

SELECT * FROM INFORMATION_SCHEMA.ST_GEOMETRY_COLUMNS WHERE SRS_ID=4326;

SRID 值必须在 32 位无符号整数范围内，有以下限制：

SRID 0 是一个有效的 SRID，但不能与删除空间参考系统一起使用。
如果值在保留的 SRID 范围内，会发出警告。保留范围为 [0, 32767]（由 EPSG 保留）、[60,000,000, 69,999,999]（由 EPSG 保留）和 [2,000,000,000, 2,147,483,647]（由 MySQL 保留）。EPSG 代表欧洲石油调查组。
用户不应删除具有保留范围内 SRID 的 SRS。如果删除了系统安装的 SRS，则可能会在 MySQL 升级时重新创建 SRS 定义。

15.1.32 DROP TABLE 语句

原文：dev.mysql.com/doc/refman/8.0/en/drop-table.html

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

DROP TABLE 可以移除一个或多个表。对于每个表，您必须拥有 DROP 权限。

请谨慎 使用这个语句！对于每个表格，它会删除表格定义和所有表格数据。如果表格被分区，该语句会删除表格定义、所有分区、存储在这些分区中的所有数据以及与被删除表格相关的所有分区定义。

删除表格也会删除表格的任何触发器。

DROP TABLE 会导致隐式提交，除非与 TEMPORARY 关键字一起使用。请参阅 Section 15.3.3, “Statements That Cause an Implicit Commit”。

重要

当删除表格时，专门为表格授予的权限不会自动删除。必须手动删除它们。请参阅 Section 15.7.1.6, “GRANT Statement”。

如果参数列表中命名的任何表格不存在，则 DROP TABLE 的行为取决于是否给出 IF EXISTS 子句：

没有 IF EXISTS，该语句将因无法删除不存在的表格而失败，并且不会进行任何更改。
使用 IF EXISTS，对于不存在的表格不会发生错误。该语句会删除所有存在的命名表格，并为每个不存在的表格生成一个 NOTE 诊断信息。这些注释可以通过 SHOW WARNINGS 显示。请参阅 Section 15.7.7.42, “SHOW WARNINGS Statement”。

在一些异常情况下，IF EXISTS 对于删除表格也是有用的，即在数据字典中存在条目但存储引擎中没有管理的表格的情况下。（例如，如果在从存储引擎中删除表格后但在删除数据字典条目之前发生异常服务器退出的情况。）

TEMPORARY 关键字具有以下效果：

该语句仅删除 TEMPORARY 表。
该语句不会导致隐式提交。
不会检查访问权限。TEMPORARY 表只能在创建它的会话中可见，因此不需要检查。

包含 TEMPORARY 关键字是防止意外删除非TEMPORARY表的好方法。

RESTRICT 和 CASCADE 关键字无效。它们被允许是为了更容易从其他数据库系统进行移植。

DROP TABLE 不支持所有 innodb_force_recovery 设置。请参阅 Section 17.21.3, “Forcing InnoDB Recovery”。

15.1.33 DROP TABLESPACE Statement

原文：dev.mysql.com/doc/refman/8.0/en/drop-tablespace.html

DROP [UNDO] TABLESPACE *tablespace_name*
    [ENGINE [=] *engine_name*]

此语句删除先前使用CREATE TABLESPACE创建的表空间。它受NDB和InnoDB存储引擎支持。

在 MySQL 8.0.14 中引入的UNDO关键字必须在删除撤销表空间时指定。只能删除使用CREATE UNDO TABLESPACE语法创建的撤销表空间。撤销表空间必须处于空状态才能删除。有关更多信息，请参见 Section 17.6.3.4, “Undo Tablespaces”。

ENGINE设置使用表空间的存储引擎，其中engine_name是存储引擎的名称。目前，支持值为InnoDB和NDB。如果未设置，则使用default_storage_engine的值。如果与用于创建表空间的存储引擎不同，则DROP TABLESPACE语句将失败。

*tablespace_name*是 MySQL 中区分大小写的标识符。

对于InnoDB通用表空间，在执行DROP TABLESPACE操作之前，必须从表空间中删除所有表。如果表空间不为空，DROP TABLESPACE会返回错误。

要删除的NDB表空间不能包含任何数据文件；换句话说，在您可以删除NDB表空间之前，必须先使用ALTER TABLESPACE ... DROP DATAFILE命令删除每个数据文件。

注意

当表空间中的最后一个表被删除时，通用InnoDB表空间不会自动删除。必须使用DROP TABLESPACE *tablespace_name*显式删除表空间。
DROP DATABASE操作可以删除属于通用表空间的表，但无法删除表空间，即使操作删除了属于表空间的所有表。必须使用DROP TABLESPACE *tablespace_name*显式删除表空间。
与系统表空间类似，截断或删除存储在通用表空间中的表会在通用表空间的.ibd 数据文件中创建内部的可用空间，该空间只能用于新的InnoDB数据。与针对每个表的文件表空间不同，空间不会像对操作系统一样释放回去。

InnoDB 示例

这个示例演示了如何删除一个InnoDB通用表空间。通用表空间ts1是用一个表创建的。在删除表空间之前，必须先删除表。

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

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

mysql> DROP TABLE t1;

mysql> DROP TABLESPACE ts1;

本示例演示了如何删除一个撤销表空间。撤销表空间必须处于空状态才能被删除。有关更多信息，请参见第 17.6.3.4 节，“Undo Tablespaces”。

mysql> DROP UNDO TABLESPACE *undo_003*;

NDB 示例

本示例演示了如何在首先创建表空间后删除一个NDB表空间myts，假设存在一个名为mylg的日志文件组（参见第 15.1.16 节，“CREATE LOGFILE GROUP Statement”）。

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

必须使用ALTER TABLESPACE语句，如下所示，从表空间中删除所有数据文件，然后才能删除表空间：

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

mysql> DROP TABLESPACE myts;

15.1.34 DROP TRIGGER Statement

原文：dev.mysql.com/doc/refman/8.0/en/drop-trigger.html

DROP TRIGGER [IF EXISTS] [*schema_name*.]*trigger_name*

这个语句会触发一个触发器。模式（数据库）名称是可选的。如果省略了模式，触发器将从默认模式中删除。DROP TRIGGER需要与触发器关联的表的TRIGGER权限。

使用IF EXISTS可以防止出现触发器不存在的错误。在使用IF EXISTS时，会为不存在的触发器生成一个NOTE。参见 Section 15.7.7.42, “SHOW WARNINGS Statement”。

如果删除表，则该表的触发器也会被删除。

15.1.35 DROP VIEW Statement

原文：dev.mysql.com/doc/refman/8.0/en/drop-view.html

DROP VIEW [IF EXISTS]
    *view_name* [, *view_name*] ...
    [RESTRICT | CASCADE]

DROP VIEW用于移除一个或多个视图。对于每个视图，您必须具有DROP权限。

如果参数列表中命名的任何视图不存在，则该语句将失败，并显示一个错误，指示无法删除的不存在视图的名称，并且不会进行任何更改。

注意

在 MySQL 5.7 及更早版本中，如果参数列表中命名的任何视图不存在，DROP VIEW会返回错误，但也会删除列表中存在的所有视图。由于 MySQL 8.0 中行为的更改，当在 MySQL 5.7 复制源服务器上复制到 MySQL 8.0 副本时，对于部分完成的DROP VIEW操作会失败。为避免此失败场景，在DROP VIEW语句中使用IF EXISTS语法可防止出现视图不存在的错误。更多信息，请参见 Section 15.1.1, “Atomic Data Definition Statement Support”。

IF EXISTS子句可防止出现视图不存在的错误。当使用此子句时，对于每个不存在的视图会生成一个NOTE。参见 Section 15.7.7.42, “SHOW WARNINGS Statement”。

RESTRICT和CASCADE，如果给定，将被解析并忽略。

15.1.36 RENAME TABLE Statement

原文：dev.mysql.com/doc/refman/8.0/en/rename-table.html

RENAME TABLE
    *tbl_name* TO *new_tbl_name*
    [, *tbl_name2* TO *new_tbl_name2*] ...

RENAME TABLE重命名一个或多个表。你必须对原始表具有ALTER和DROP权限，对新表具有CREATE和INSERT权限。

例如，要将名为old_table的表重命名为new_table，可以使用以下语句：

RENAME TABLE old_table TO new_table;

该语句等同于以下ALTER TABLE语句：

ALTER TABLE old_table RENAME new_table;

RENAME TABLE，与ALTER TABLE不同，可以在单个语句中重命名多个表：

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

重命名操作是从左到右执行的。因此，要交换两个表名，可以这样做（假设中间名为tmp_table的表不存在）：

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

表的元数据锁按名称顺序获取，在某些情况下，当多个事务同时执行时，这可能会影响操作结果。参见 Section 10.11.4, “Metadata Locking”。

从 MySQL 8.0.13 开始，可以重命名被LOCK TABLES语句锁定的表，前提是它们被使用WRITE锁定或是在多表重命名操作中从之前步骤中重命名WRITE锁定的表的结果。例如，这是允许的：

LOCK TABLE old_table1 WRITE;
RENAME TABLE old_table1 TO new_table1,
             new_table1 TO new_table2;

这是不允许的：

LOCK TABLE old_table1 READ;
RENAME TABLE old_table1 TO new_table1,
             new_table1 TO new_table2;

在 MySQL 8.0.13 之前，要执行RENAME TABLE，不能有使用LOCK TABLES锁定的表。

满足事务表锁定条件后，重命名操作是原子性的；在重命名过程中，其他会话无法访问任何表。

如果在RENAME TABLE过程中发生任何错误，该语句将失败，不会进行任何更改。

你可以使用RENAME TABLE将一个表从一个数据库移动到另一个数据库：

RENAME TABLE *current_db.tbl_name* TO *other_db.tbl_name;*

使用这种方法将所有表从一个数据库移动到另一个数据库实际上是重命名数据库（MySQL 没有单个语句执行此操作），只是原始数据库仍然存在，尽管没有表。

像RENAME TABLE一样，ALTER TABLE ... RENAME也可以用于将表移动到不同的数据库。无论使用哪种语句，如果重命名操作将表移动到位于不同文件系统上的数据库，操作结果的成功与否取决于特定平台，并取决于用于移动表文件的底层操作系统调用。

如果一个表有触发器，尝试将表重命名到不同的数据库会失败，并显示错误 Trigger in wrong schema (ER_TRG_IN_WRONG_SCHEMA)。

未加密的表可以移动到启用加密的数据库，反之亦然。但是，如果启用了table_encryption_privilege_check变量，则如果表的加密设置与默认数据库加密不同，则需要TABLE_ENCRYPTION_ADMIN权限。

要重命名TEMPORARY表，RENAME TABLE 不起作用。请改用ALTER TABLE。

RENAME TABLE 对视图有效，但视图不能重命名到不同的数据库。

为重命名的表或视图专门授予的任何权限不会迁移到新名称。它们必须手动更改。

RENAME TABLE *tbl_name* TO *new_tbl_name* 会更改内部生成的以及以字符串“tbl_nameibfk”开头的用户定义的外键约束名称，以反映新表名。InnoDB将以字符串“tbl_nameibfk”开头的外键约束名称解释为内部生成的名称。

指向重命名表的外键约束名称会自动更新，除非存在冲突，否则语句将因错误而失败。如果重命名的约束名称已经存在，则会发生冲突。在这种情况下，您必须删除并重新创建外键以使其正常工作。

RENAME TABLE *tbl_name* TO *new_tbl_name* 会更改以字符串“tbl_namechk”开头的内部生成和用户定义的CHECK约束名称，以反映新表名。MySQL 将以字符串“tbl_namechk”开头的CHECK约束名称解释为内部生成的名称。示例：

mysql> SHOW CREATE TABLE t1\G
*************************** 1\. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `i1` int(11) DEFAULT NULL,
  `i2` int(11) DEFAULT NULL,
  CONSTRAINT `t1_chk_1` CHECK ((`i1` > 0)),
  CONSTRAINT `t1_chk_2` CHECK ((`i2` < 0))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 1 row in set (0.02 sec)

mysql> RENAME TABLE t1 TO t3;
Query OK, 0 rows affected (0.03 sec)

mysql> SHOW CREATE TABLE t3\G
*************************** 1\. row ***************************
       Table: t3
Create Table: CREATE TABLE `t3` (
  `i1` int(11) DEFAULT NULL,
  `i2` int(11) DEFAULT NULL,
  CONSTRAINT `t3_chk_1` CHECK ((`i1` > 0)),
  CONSTRAINT `t3_chk_2` CHECK ((`i2` < 0))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 1 row in set (0.01 sec)

15.1.37 截断表语句

原文：dev.mysql.com/doc/refman/8.0/en/truncate-table.html

TRUNCATE [TABLE] *tbl_name*

TRUNCATE TABLE会完全清空表。它需要DROP权限。从逻辑上讲，TRUNCATE TABLE类似于删除所有行的DELETE语句，或一系列DROP TABLE和CREATE TABLE语句。

为了实现高性能，TRUNCATE TABLE绕过了删除数据的 DML 方法。因此，它不会触发ON DELETE触发器，不能用于具有父子外键关系的InnoDB表，也不能像 DML 操作那样回滚。但是，对于使用原子 DDL 支持的存储引擎的表的TRUNCATE TABLE操作，如果服务器在操作期间停止，则要么完全提交，要么回滚。有关更多信息，请参见第 15.1.1 节，“原子数据定义语句支持”。

虽然TRUNCATE TABLE类似于DELETE，但它被归类为 DDL 语句而不是 DML 语句。它与DELETE在以下方面有所不同：

截断操作会删除并重新创建表，这比逐行删除行要快得多，特别是对于大表而言。
截断操作会导致隐式提交，因此无法回滚。参见第 15.3.3 节，“导致隐式提交的语句”。
如果会话持有活动表锁，则无法执行截断操作。
如果有其他表引用该表的FOREIGN KEY约束，则TRUNCATE TABLE对InnoDB表或NDB表会失败。允许同一表的列之间的外键约束。
截断操作不会返回有意义的已删除行数值。通常的结果是“0 行受影响”，应该解释为“没有信息”。
只要表定义有效，即使数据或索引文件已损坏，也可以使用TRUNCATE TABLE将表重新创建为空表。
任何AUTO_INCREMENT值都将重置为其起始值。即使对于通常不重用序列值的MyISAM和InnoDB也是如此。
当与分区表一起使用时，TRUNCATE TABLE保留分区；也就是说，数据和索引文件被删除并重新创建，而分区定义不受影响。
TRUNCATE TABLE语句不会触发ON DELETE触发器。
支持截断损坏的InnoDB表。

对于二进制日志记录和复制的目的，TRUNCATE TABLE被视为 DDL 而不是 DML，并始终作为一个语句记录。

对于一个表，TRUNCATE TABLE会关闭所有使用HANDLER OPEN打开的处理程序。

在 MySQL 5.7 及更早版本中，在具有大缓冲池和启用innodb_adaptive_hash_index的系统上，TRUNCATE TABLE操作可能会导致系统性能暂时下降，因为在删除表的自适应哈希索引条目时会发生 LRU 扫描（Bug #68184）。在 MySQL 8.0 中，将TRUNCATE TABLE重新映射为DROP TABLE和CREATE TABLE避免了问题的 LRU 扫描。

TRUNCATE TABLE可以用于性能模式摘要表，但效果是将摘要列重置为 0 或NULL，而不是删除行。参见 Section 29.12.20, “性能模式摘要表”。

截断位于文件表表空间中的InnoDB表会删除现有的表空间并创建一个新的。从 MySQL 8.0.21 开始，如果表空间是在早期版本中创建的并位于未知目录中，InnoDB会在默认位置创建新的表空间，并将以下警告写入错误日志：数据目录位置必须在已知目录中。数据目录位置将被忽略，并且文件将被放入默认 datadir 位置。已知目录是由datadir、innodb_data_home_dir和innodb_directories变量定义的目录。要让TRUNCATE TABLE在当前位置创建表空间，请在运行TRUNCATE TABLE之前将目录添加到innodb_directories设置中。

15.2 数据操作语句

原文：dev.mysql.com/doc/refman/8.0/en/sql-data-manipulation-statements.html

15.2.1 CALL 语句

15.2.2 删除语句

15.2.3 DO 语句

15.2.4 EXCEPT 子句

15.2.5 处理器语句

15.2.6 导入表语句

15.2.7 插入语句

15.2.8 INTERSECT 子句

15.2.9 加载数据语句

15.2.10 加载 XML 语句

15.2.11 带括号的查询表达式

15.2.12 替换语句

15.2.13 查询语句

15.2.14 使用 UNION、INTERSECT 和 EXCEPT 进行集合操作

15.2.15 子查询

15.2.16 TABLE 语句

15.2.17 更新语句

15.2.18 UNION 子句

15.2.19 VALUES 语句

15.2.20 WITH（通用表达式）

15.2.1 CALL 语句

原文：dev.mysql.com/doc/refman/8.0/en/call.html

CALL *sp_name*([*parameter*[,...]])
CALL *sp_name*[()]

CALL 语句调用之前使用 CREATE PROCEDURE 之前定义的存储过程。

不带参数的存储过程可以在不使用括号的情况下调用。也就是说，CALL p() 和 CALL p 是等效的。

CALL 可以通过声明为 OUT 或 INOUT 参数的参数将值传回给调用者。当过程返回时，客户端程序还可以获取在例程内执行的最终语句影响的行数：在 SQL 级别，调用 ROW_COUNT() 函数；从 C API，调用 mysql_affected_rows() 函数。

有关未处理条件对过程参数的影响的信息，请参见第 15.6.7.8 节，“条件处理和 OUT 或 INOUT 参数”。

要通过 OUT 或 INOUT 参数从过程中获取一个值，需要通过用户变量传递参数，然后在过程返回后检查变量的值。（如果你是从另一个存储过程或函数中调用该过程，也可以将例程参数或本地例程变量作为 IN 或 INOUT 参数传递。）对于 INOUT 参数，在传递给过程之前初始化其值。以下过程具有一个 OUT 参数，该过程将该参数设置为当前服务器版本，并具有一个 INOUT 值，该过程将该值从其当前值增加一：

DELIMITER //

CREATE PROCEDURE p (OUT ver_param VARCHAR(25), INOUT incr_param INT)
BEGIN
  # Set value of OUT parameter
  SELECT VERSION() INTO ver_param;
  # Increment value of INOUT parameter
  SET incr_param = incr_param + 1;
END //

DELIMITER ;

在调用过程之前，初始化要作为 INOUT 参数传递的变量。调用过程后，您可以看到这两个变量的值已设置或修改：

mysql> SET @increment = 10;
mysql> CALL p(@version, @increment);
mysql> SELECT @version, @increment;
+----------+------------+
| @version | @increment |
+----------+------------+
| 8.0.36   |         11 |
+----------+------------+

在使用 PREPARE 和 EXECUTE 语句准备的语句中，可以使用占位符来表示 IN 参数、OUT 参数和 INOUT 参数。这些类型的参数可以如下使用：

mysql> SET @increment = 10;
mysql> PREPARE s FROM 'CALL p(?, ?)';
mysql> EXECUTE s USING @version, @increment;
mysql> SELECT @version, @increment;
+----------+------------+
| @version | @increment |
+----------+------------+
| 8.0.36   |         11 |
+----------+------------+

要编写使用CALL SQL 语句执行生成结果集的存储过程的 C 程序，必须启用CLIENT_MULTI_RESULTS标志。这是因为每个CALL都会返回一个结果来指示调用状态，除了存储过程内执行的可能返回的任何结果集。如果使用CALL执行包含准备语句的任何存储过程，也必须启用CLIENT_MULTI_RESULTS。无法确定加载此类存储过程时这些语句是否生成结果集，因此必须假定它们会生成结果集。

当您调用mysql_real_connect()时，可以通过显式传递CLIENT_MULTI_RESULTS标志或隐式传递CLIENT_MULTI_STATEMENTS（也会启用CLIENT_MULTI_RESULTS）来启用CLIENT_MULTI_RESULTS。CLIENT_MULTI_RESULTS默认情况下是启用的。

要处理使用mysql_query()或mysql_real_query()执行的CALL语句的结果，请使用调用mysql_next_result()的循环来确定是否还有更多结果。有关示例，请参见多语句执行支持。

C 程序可以使用准备语句接口来执行CALL语句并访问OUT和INOUT参数。这是通过处理CALL语句的结果，使用调用mysql_stmt_next_result()的循环来确定是否还有更多结果。有关示例，请参见准备 CALL 语句支持。提供 MySQL 接口的语言可以使用准备的CALL语句直接检索OUT和INOUT过程参数。

当存储程序引用的对象的元数据发生更改时，会检测到并在下次执行程序时自动重新解析受影响的语句。有关更多信息，请参见第 10.10.3 节，“准备语句和存储程序的缓存”。

15.2.2 DELETE 语句

原文：dev.mysql.com/doc/refman/8.0/en/delete.html

DELETE是一条从表中删除行的 DML 语句。

DELETE语句可以以WITH")子句开头，以定义在DELETE内可访问的公共表达式。请参阅第 15.2.20 节，“WITH (Common Table Expressions)”")。

单表语法

DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM *tbl_name* [[AS] *tbl_alias*]
    [PARTITION (*partition_name* [, *partition_name*] ...)]
    [WHERE *where_condition*]
    [ORDER BY ...]
    [LIMIT *row_count*]

DELETE语句从tbl_name中删除行并返回已删除行的数量。要检查已删除行的数量，请调用第 14.15 节，“信息函数”中描述的ROW_COUNT()函数。

主要子句

可选的WHERE子句中的条件标识要删除的行。如果没有WHERE子句，则删除所有行。

where_condition是一个表达式，对于要删除的每一行都计算为 true。它的指定方式如第 15.2.13 节，“SELECT 语句”中描述的那样。

如果指定了ORDER BY子句，则按指定的顺序删除行。LIMIT子句限制可以删除的行数。这些子句适用于单表删除，但不适用于多表删除。

多表语法

DELETE [LOW_PRIORITY] [QUICK] [IGNORE]
    *tbl_name*[.*] [, *tbl_name*[.*]] ...
    FROM *table_references*
    [WHERE *where_condition*]

DELETE [LOW_PRIORITY] [QUICK] [IGNORE]
    FROM *tbl_name*[.*] [, *tbl_name*[.*]] ...
    USING *table_references*
    [WHERE *where_condition*]

权限

您需要在表上具有DELETE权限才能从中删除行。对于仅读取的列（例如WHERE子句中命名的列），您只需要具有SELECT权限。

性能

当您不需要知道已删除行的数量时，TRUNCATE TABLE语句是比没有WHERE子句的DELETE语句更快地清空表的方法。与DELETE不同，TRUNCATE TABLE不能在事务内使用，也不能在表上加锁。请参阅第 15.1.37 节，“TRUNCATE TABLE 语句”和第 15.3.6 节，“LOCK TABLES 和 UNLOCK TABLES 语句”。

删除操作的速度也可能受到第 10.2.5.3 节，“优化 DELETE 语句”中讨论的因素的影响。

为确保给定的DELETE语句不会花费太多时间，MySQL 特定的LIMIT *row_count*子句用于DELETE指定要删除的最大行数。如果要删除的行数大于限制，则重复DELETE语句，直到受影响的行数小于LIMIT值。

子查询

你不能在子查询中从一个表中删除并从同一个表中选择。

分区表支持

DELETE支持使用PARTITION子句进行显式分区选择，该子句接受一个逗号分隔的一个或多个分区或子分区（或两者）的名称列表，从中选择要删除的行。未包含在列表中的分区将被忽略。给定一个具有名为p0的分区的分区表t，执行语句DELETE FROM t PARTITION (p0)对表具有与执行ALTER TABLE t TRUNCATE PARTITION (p0)相同的效果；在这两种情况下，分区p0中的所有行都将被删除。

PARTITION可以与WHERE条件一起使用，在这种情况下，条件仅在列出的分区中的行上进行测试。例如，DELETE FROM t PARTITION (p0) WHERE c < 5仅删除条件c < 5为真的分区p0中的行；任何其他分区中的行都不会被检查，因此不受DELETE影响。

PARTITION子句也可以在多表DELETE语句中使用。您可以在FROM选项中命名的每个表中使用最多一个此类选项。

有关更多信息和示例，请参阅 Section 26.5, “Partition Selection”。

自增列

如果删除包含AUTO_INCREMENT列的最大值的行，则对于MyISAM或InnoDB表，该值不会被重用。如果在autocommit模式下使用DELETE FROM *tbl_name*（没有WHERE子句）从表中删除所有行，则除了InnoDB和MyISAM之外的所有存储引擎都会重新开始序列。关于InnoDB表的此行为有一些例外情况，如 Section 17.6.1.6, “AUTO_INCREMENT Handling in InnoDB”中所讨论的。

对于MyISAM表，您可以在多列键中指定一个AUTO_INCREMENT辅助列。在这种情况下，即使对于MyISAM表，从序列顶部删除的值也会被重用。请参阅 Section 5.6.9, “Using AUTO_INCREMENT”获取更多信息和示例。

修饰符

DELETE语句支持以下修饰符：

如果指定了LOW_PRIORITY修饰符，服务器会延迟执行DELETE，直到没有其他客户端从表中读取数据。这仅影响只使用表级锁定的存储引擎（如MyISAM，MEMORY和MERGE）。
对于MyISAM表，如果使用QUICK修饰符，存储引擎在删除期间不会合并索引叶子，这可能会加快某些类型的删除操作。
IGNORE修饰符导致 MySQL 在删除行的过程中忽略可忽略的错误。（在解析阶段遇到的错误会按照通常的方式处理。）由于使用IGNORE而被忽略的错误会作为警告返回。有关更多信息，请参阅 IGNORE 对语句执行的影响。

删除顺序

如果DELETE语句包括ORDER BY子句，则按照子句指定的顺序删除行。这主要与LIMIT结合使用。例如，以下语句查找与WHERE子句匹配的行，按timestamp_column排序，并删除第一行（最旧的行）：

DELETE FROM somelog WHERE user = 'jcole'
ORDER BY timestamp_column LIMIT 1;

ORDER BY还有助于按照所需顺序删除行，以避免引用完整性违规。

InnoDB 表

如果要从大表中删除许多行，可能会超出InnoDB表的锁表大小。为避免此问题，或者仅仅为了最小化表保持锁定的时间，以下策略（完全不使用DELETE）可能会有所帮助：

将不要删除的行选择到一个与原始表具有相同结构的空表中：
```
INSERT INTO t_copy SELECT * FROM t WHERE ... ;
```
使用RENAME TABLE原子性地将原始表移开并将副本重命名为原始名称：
```
RENAME TABLE t TO t_old, t_copy TO t;
```
删除原始表：
```
DROP TABLE t_old;
```

在执行RENAME TABLE期间，没有其他会话可以访问涉及的表，因此重命名操作不会受到并发问题的影响。请参阅 Section 15.1.36, “RENAME TABLE Statement”。

MyISAM 表

在MyISAM表中，删除的行会保留在一个链表中，随后的INSERT操作会重用旧的行位置。为了回收未使用的空间并减小文件大小，使用OPTIMIZE TABLE语句或myisamchk实用程序重新组织表。OPTIMIZE TABLE更容易使用，但myisamchk更快。请参阅 Section 15.7.3.4, “OPTIMIZE TABLE Statement”和 Section 6.6.4, “myisamchk — MyISAM Table-Maintenance Utility”。

QUICK修饰符影响删除操作时是否合并索引叶子。DELETE QUICK对于删除的行的索引值被后续插入的类似索引值替换的应用程序最有用。在这种情况下，被删除值留下的空洞会被重用。

当删除的值导致跨越新插入发生的索引值范围的索引块不足时，DELETE QUICK是无用的。在这种情况下，使用QUICK可能会导致索引中的浪费空间无法回收。以下是这种情况的一个示例：

创建一个包含带有索引的AUTO_INCREMENT列的表。
插入多行到表中。每次插入都会导致一个索引值被添加到索引的高端。
使用DELETE QUICK删除列范围的低端的一块行。

在这种情况下，与被删除的索引值相关联的索引块变得不足，但由于使用了QUICK，它们不会与其他索引块合并。当新的插入发生时，它们仍然不足，因为新行没有在被删除范围内的索引值。此外，即使稍后使用DELETE而不使用QUICK，它们仍然保持不足，除非一些被删除的索引值恰好位于在不足块内或相邻块内的索引块中。在这些情况下，为了回收未使用的索引空间，请使用OPTIMIZE TABLE。

如果要从表中删除许多行，使用DELETE QUICK后跟OPTIMIZE TABLE可能更快。这将重建索引，而不是执行许多索引块合并操作。

多表删除

您可以在DELETE语句中指定多个表，根据WHERE子句中的条件从一个或多个表中删除行。在多表DELETE语句中不能使用ORDER BY或LIMIT。table_references子句列出了参与连接的表，如第 15.2.13.2 节“JOIN 子句”中所述。

对于第一个多表语法，只删除在FROM子句之前列出的表中匹配的行。对于第二个多表语法，只删除在FROM子句（在USING子句之前）中列出的表中匹配的行。其效果是您可以同时从多个表中删除行，并且还可以使用仅用于搜索的其他表：

DELETE t1, t2 FROM t1 INNER JOIN t2 INNER JOIN t3
WHERE t1.id=t2.id AND t2.id=t3.id;

或：

DELETE FROM t1, t2 USING t1 INNER JOIN t2 INNER JOIN t3
WHERE t1.id=t2.id AND t2.id=t3.id;

这些语句在搜索要删除的行时使用了所有三个表，但只从表t1和t2中删除匹配的行。

前面的示例使用了INNER JOIN，但多表DELETE语句可以使用在SELECT语句中允许的其他类型的连接，例如LEFT JOIN。例如，要删除在t1中存在但在t2中没有匹配的行，请使用LEFT JOIN：

DELETE t1 FROM t1 LEFT JOIN t2 ON t1.id=t2.id WHERE t2.id IS NULL;

该语法允许在每个tbl_name后面使用.*以与Access兼容。

如果您使用涉及具有外键约束的InnoDB表的多表DELETE语句，MySQL 优化器可能以与其父/子关系不同的顺序处理表。在这种情况下，语句将失败并回滚。相反，您应该从单个表中删除，并依赖InnoDB提供的ON DELETE功能来相应地修改其他表。

注意

如果为表声明了别名，则在引用该表时必须使用该别名：

DELETE t1 FROM test AS t1, test2 WHERE ...

多表DELETE语句中的表别名应仅在语句的table_references部分中声明。在其他地方，允许别名引用，但不允许别名声明。

正确：

DELETE a1, a2 FROM t1 AS a1 INNER JOIN t2 AS a2
WHERE a1.id=a2.id;

DELETE FROM a1, a2 USING t1 AS a1 INNER JOIN t2 AS a2
WHERE a1.id=a2.id;

错误：

DELETE t1 AS a1, t2 AS a2 FROM t1 INNER JOIN t2
WHERE a1.id=a2.id;

DELETE FROM t1 AS a1, t2 AS a2 USING t1 INNER JOIN t2
WHERE a1.id=a2.id;

从 MySQL 8.0.16 开始，还支持单表DELETE语句的表别名。 (Bug #89410,Bug #27455809)

15.2.3 DO 语句

原文：dev.mysql.com/doc/refman/8.0/en/do.html

DO *expr* [, *expr*] ...

DO执行表达式但不返回任何结果。在大多数情况下，DO相当于SELECT *expr*, ...，但它的优势在于当你不关心结果时速度稍快。

DO主要用于具有副作用的函数，例如RELEASE_LOCK()。

示例：这个SELECT语句暂停，同时产生一个结果集：

mysql> SELECT SLEEP(5);
+----------+
| SLEEP(5) |
+----------+
|        0 |
+----------+
1 row in set (5.02 sec)

另一方面，DO暂停而不产生结果集。

mysql> DO SLEEP(5);
Query OK, 0 rows affected (4.99 sec)

例如，在禁止产生结果集的存储函数或触发器中，这可能很有用。

DO仅执行表达式。它不能在所有可以使用SELECT的情况下使用。例如，DO id FROM t1是无效的，因为它引用了一个表。

15.2.4 EXCEPT 子句

原文：dev.mysql.com/doc/refman/8.0/en/except.html

*query_expression_body* EXCEPT [ALL | DISTINCT] *query_expression_body*
    [EXCEPT [ALL | DISTINCT] *query_expression_body*]
    [...]

*query_expression_body*:
    *See Section 15.2.14, “Set Operations with UNION, INTERSECT, and EXCEPT”*

EXCEPT将第一个查询块的结果限制为那些在第二个查询块中（也）找不到的行。与UNION和INTERSECT一样，任一查询块都可以使用SELECT、TABLE或VALUES中的任何一个。下面是一个使用在第 15.2.8 节，“INTERSECT 子句”中定义的表a、b和c的示例：

mysql> TABLE a EXCEPT TABLE b;
+------+------+
| m    | n    |
+------+------+
|    2 |    3 |
+------+------+
1 row in set (0.00 sec)

mysql> TABLE a EXCEPT TABLE c;
+------+------+
| m    | n    |
+------+------+
|    1 |    2 |
|    2 |    3 |
+------+------+
2 rows in set (0.00 sec)

mysql> TABLE b EXCEPT TABLE c;
+------+------+
| m    | n    |
+------+------+
|    1 |    2 |
+------+------+
1 row in set (0.00 sec)

与UNION和INTERSECT一样，如果未指定DISTINCT或ALL，默认为DISTINCT。

DISTINCT会移除关系两侧发现的重复项，如下所示：

mysql> TABLE c EXCEPT DISTINCT TABLE a;
+------+------+
| m    | n    |
+------+------+
|    1 |    3 |
+------+------+
1 row in set (0.00 sec)

mysql> TABLE c EXCEPT ALL TABLE a;
+------+------+
| m    | n    |
+------+------+
|    1 |    3 |
|    1 |    3 |
+------+------+
2 rows in set (0.00 sec)

（第一个语句的效果与TABLE c EXCEPT TABLE a相同。）

与UNION或INTERSECT不同，EXCEPT 不是可交换的——也就是说，结果取决于操作数的顺序，如下所示：

mysql> TABLE a EXCEPT TABLE c;
+------+------+
| m    | n    |
+------+------+
|    1 |    2 |
|    2 |    3 |
+------+------+
2 rows in set (0.00 sec)

mysql> TABLE c EXCEPT TABLE a;
+------+------+
| m    | n    |
+------+------+
|    1 |    3 |
+------+------+
1 row in set (0.00 sec)

与UNION一样，要比较的结果集必须具有相同数量的列。结果集列类型也与UNION一样确定。

EXCEPT在 MySQL 8.0.31 中添加。

15.2.5 HANDLER 语句

原文：dev.mysql.com/doc/refman/8.0/en/handler.html

HANDLER *tbl_name* OPEN [ [AS] *alias*]

HANDLER *tbl_name* READ *index_name* { = | <= | >= | < | > } (*value1*,*value2*,...)
    [ WHERE *where_condition* ] [LIMIT ... ]
HANDLER *tbl_name* READ *index_name* { FIRST | NEXT | PREV | LAST }
    [ WHERE *where_condition* ] [LIMIT ... ]
HANDLER *tbl_name* READ { FIRST | NEXT }
    [ WHERE *where_condition* ] [LIMIT ... ]

HANDLER *tbl_name* CLOSE

HANDLER语句提供对表存储引擎接口的直接访问。适用于InnoDB和MyISAM表。

HANDLER ... OPEN语句打开一个表，使其可以通过后续的HANDLER ... READ语句访问。此表对象不会被其他会话共享，并且直到会话调用HANDLER ... CLOSE或会话终止时才会关闭。

如果使用别名打开表，则必须使用别名而不是表名来引用其他HANDLER语句中打开的表。如果不使用别名，而是使用由数据库名限定的表名打开表，则进一步引用必须使用未限定的表名。例如，对于使用mydb.mytable打开的表，进一步引用必须使用mytable。

第一个HANDLER ... READ语法获取一个满足给定值和WHERE条件的索引的行。如果有多列索引，请将索引列值指定为逗号分隔的列表。要么为索引中的所有列指定值，要么为索引列的最左前缀指定值。假设索引my_idx按顺序包括三列名为col_a、col_b和col_c。HANDLER语句可以为索引中的所有三列或最左前缀的列指定值。例如：

HANDLER ... READ my_idx = (col_a_val,col_b_val,col_c_val) ...
HANDLER ... READ my_idx = (col_a_val,col_b_val) ...
HANDLER ... READ my_idx = (col_a_val) ...

要使用HANDLER接口引用表的PRIMARY KEY，请使用引号标识符PRIMARY：

HANDLER *tbl_name* READ `PRIMARY` ...

第二个HANDLER ... READ语法按照索引顺序从表中获取与WHERE条件匹配的行。

第三个HANDLER ... READ语法按照自然行顺序从表中获取与WHERE条件匹配的行。当需要进行全表扫描时，比HANDLER *tbl_name* READ *index_name*更快。自然行顺序是MyISAM表数据文件中存储行的顺序。此语句也适用于InnoDB表，但没有这样的概念，因为没有单独的数据文件。

没有LIMIT子句，所有形式的HANDLER ... READ如果有可用行，则获取一行。要返回特定数量的行，请包含LIMIT子句。其语法与SELECT语句相同。参见第 15.2.13 节，“SELECT Statement”。

HANDLER ... CLOSE关闭使用HANDLER ... OPEN打开的表。

使用HANDLER接口而不是普通的SELECT语句有几个原因：

HANDLER比SELECT更快：
- 为HANDLER ... OPEN分配一个指定的存储引擎处理程序对象。该对象将用于该表的后续HANDLER语句；不需要为每个语句重新初始化。
- 解析工作较少。
- 没有优化器或查询检查开销。
- 处理程序接口不必提供数据的一致外观（例如，允许脏读取），因此存储引擎可以使用SELECT通常不允许的优化。
HANDLER使得将 MySQL 应用程序移植到使用低级ISAM-like 接口的应用程序更容易。（参见第 17.20 节，“InnoDB memcached 插件”，了解适应使用键值存储范式的应用程序的另一种方法。）
HANDLER使您能够以一种难以（甚至不可能）用SELECT实现的方式遍历数据库。HANDLER接口是在处理提供交互式用户界面到数据库的应用程序时查看数据的更自然方式。

HANDLER是一种较低级别的语句。例如，它不提供一致性。也就是说，HANDLER ... OPEN不会对表进行快照，并且不会锁定表。这意味着在发出HANDLER ... OPEN语句后，表数据可以被修改（由当前会话或其他会话），并且这些修改可能只对HANDLER ... NEXT或HANDLER ... PREV扫描部分可见。

可以关闭并标记为重新打开打开的处理程序，此时处理程序将失去在表中的位置。当以下两种情况同时成立时会发生这种情况：

任何会话在处理程序的表上执行FLUSH TABLES或 DDL 语句。
打开处理程序的会话执行使用表的非HANDLER语句。

对于一个表的TRUNCATE TABLE会关闭使用HANDLER OPEN打开的表的所有处理程序。

如果使用FLUSH TABLES *tbl_name* WITH READ LOCK刷新表时，该表是用HANDLER打开的，那么处理程序会被隐式刷新并且失去位置。

15.2.6 IMPORT TABLE Statement

原文：dev.mysql.com/doc/refman/8.0/en/import-table.html

IMPORT TABLE FROM *sdi_file* [, *sdi_file*] ...

IMPORT TABLE语句根据.sdi（序列化字典信息）元数据文件中包含的信息导入MyISAM表。IMPORT TABLE需要FILE权限来读取.sdi和表内容文件，以及CREATE权限用于创建表。

表可以使用mysqldump从一个服务器导出以编写 SQL 语句文件，并使用mysql在另一个服务器上导入以处理转储文件。IMPORT TABLE提供了一个更快的替代方案，使用“原始”表文件。

在导入之前，提供表内容的文件必须放置在适当的模式目录中，而.sdi文件必须位于服务器可访问的目录中。例如，.sdi文件可以放置在由secure_file_priv系统变量命名的目录中，或者（如果secure_file_priv为空）放置在服务器数据目录下的一个目录中。

以下示例描述了如何从一个服务器的hr模式中导出名为employees和managers的MyISAM表，并将其导入到另一个服务器的hr模式中。该示例使用以下假设（要在自己的系统上执行类似操作，请根据需要修改路径名）：

对于导出服务器，export_basedir代表其基本目录，其数据目录为*export_basedir*/data。
对于导入服务器，import_basedir代表其基本目录，其数据目录为*import_basedir*/data。
表文件从导出服务器导出到/tmp/export目录，该目录是安全的（不可被其他用户访问）。
导入服务器使用/tmp/mysql-files作为其secure_file_priv系统变量命名的目录。

要从导出服务器导出表，请使用以下过程：

通过执行此语句锁定表以确保一致的快照，以防在导出过程中对其进行修改：
```
mysql> FLUSH TABLES hr.employees, hr.managers WITH READ LOCK;
```
在锁定期间，表仍然可以使用，但仅用于读取访问。
在文件系统级别，将.sdi和表内容文件从hr模式目录复制到安全导出目录：
- .sdi文件位于hr模式目录中，但可能与表名不完全相同。例如，employees和managers表的.sdi文件可能被命名为employees_125.sdi和managers_238.sdi。
- 对于MyISAM表，内容文件是其.MYD数据文件和.MYI索引文件。
鉴于这些文件名，复制命令如下：
```
$> cd *export_basedir*/data/hr
$> cp employees_125.sdi /tmp/export
$> cp managers_238.sdi /tmp/export
$> cp employees.{MYD,MYI} /tmp/export
$> cp managers.{MYD,MYI} /tmp/export
```
解锁表：
```
mysql> UNLOCK TABLES;
```

要将表导入导入服务器，请使用以下过程：

导入模式必须存在。如有必要，执行此语句以创建它：
```
mysql> CREATE SCHEMA hr;
```

在文件系统级别，将.sdi文件复制到导入服务器secure_file_priv目录/tmp/mysql-files。同时，将表内容文件复制到hr模式目录：

$> cd /tmp/export
$> cp employees_125.sdi /tmp/mysql-files
$> cp managers_238.sdi /tmp/mysql-files
$> cp employees.{MYD,MYI} *import_basedir*/data/hr
$> cp managers.{MYD,MYI} *import_basedir*/data/hr

通过执行一个命名了.sdi文件的IMPORT TABLE语句来导入表：

mysql> IMPORT TABLE FROM
       '/tmp/mysql-files/employees.sdi',
       '/tmp/mysql-files/managers.sdi';

如果secure_file_priv系统变量为空，则.sdi文件不需要放在由该变量命名的导入服务器目录中；它可以放在服务器可访问的任何目录中，包括导入表的模式目录。但是，如果.sdi文件放在该目录中，它可能会被重写；导入操作为表创建一个新的.sdi文件，如果操作使用相同的文件名创建新文件，则会覆盖旧��.sdi文件。

每个sdi_file值必须是一个字符串文字，用于命名表的.sdi文件或与.sdi文件匹配的模式。如果字符串是一个模式，任何前导目录路径和.sdi文件名后缀必须以文字形式给出。模式字符仅允许在文件名的基本部分中：

?匹配任意单个字符
*匹配任意字符序列，包括无字符

使用模式，先前的IMPORT TABLE语句可以这样编写（假设/tmp/mysql-files目录不包含与模式匹配的其他.sdi文件）：

IMPORT TABLE FROM '/tmp/mysql-files/*.sdi';

要解释.sdi文件路径名的位置，服务器使用与IMPORT TABLE相同的规则，就像服务器端用于LOAD DATA的规则一样（即非LOCAL规则）。请参阅 Section 15.2.9, “LOAD DATA Statement”，特别注意用于解释相对路径名的规则。

如果无法定位.sdi或表文件，则IMPORT TABLE会失败。导入表后，服务器会尝试打开它，并报告检测到的任何问题作为警告。要尝试修复以纠正任何报告的问题，请使用REPAIR TABLE。

IMPORT TABLE不会写入二进制日志。

限制和限制条件

IMPORT TABLE仅适用于非TEMPORARY MyISAM表。它不适用于使用事务存储引擎创建的表，使用CREATE TEMPORARY TABLE创建的表或视图。

在导入操作中使用的.sdi文件必须在具有与导入服务器相同的数据字典版本和 sdi 版本的服务器上生成。生成服务器的版本信息可以在.sdi文件中找到：

{
   "mysqld_version_id":80019,
   "dd_version":80017,
   "sdi_version":80016,
   ...
}

要确定导入服务器的数据字典和 sdi 版本，您可以检查导入服务器上最近创建的表的.sdi文件。

在执行IMPORT TABLE语句之前，必须将表数据和索引文件放入导入服务器的模式目录中，除非在导出服务器上定义的表使用DATA DIRECTORY或INDEX DIRECTORY表选项。在这种情况下，在执行IMPORT TABLE语句之前，使用这些替代方案修改导入过程：

将数据和索引文件放入导入服务器主机上与导出服务器主机相同的目录中，并在导入服务器模式目录中创建符号链接指向这些文件。
将数据和索引文件放入导入服务器主机目录，该目录与导出服务器主机上的目录不同，并在导入服务器模式目录中创建符号链接指向这些文件。此外，修改.sdi文件以反映不同的文件位置。
将数据和索引文件放入导入服务器主机上的模式目录，并修改.sdi文件以删除数据和索引目录表选项。

存储在.sdi文件中的任何排序 ID 必须引用导出和导入服务器上相同的排序规则。

表的触发器信息不会序列化到表的.sdi文件中，因此触发器不会被导入操作恢复。

在执行IMPORT TABLE语句之前，对.sdi文件进行一些编辑是允许的，而其他编辑可能会有问题，甚至可能导致导入操作失败：

如果数据和索引文件在导出和导入服务器之间的位置不同，则需要��改数据目录和索引目录表选项。
要将表导入到导入服务器上与导出服务器上不同的模式中，需要更改模式名称。
可能需要更改模式和表名称以适应导出和导入服务器上文件系统区分大小写语义或lower_case_table_names设置的差异。在.sdi文件中更改表名称可能需要同时重命名表文件。
在某些情况下，允许更改列定义。更改数据类型可能会导致问题。

15.2.7 插入语句

原文：dev.mysql.com/doc/refman/8.0/en/insert.html

15.2.7.1 插入...选择语句

15.2.7.2 插入...在重复键更新语句中

15.2.7.3 延迟插入语句

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
    [INTO] *tbl_name*
    [PARTITION (*partition_name* [, *partition_name*] ...)]
    [(*col_name* [, *col_name*] ...)]
    { {VALUES | VALUE} (*value_list*) [, (*value_list*)] ... }
    [AS *row_alias*[(*col_alias* [, *col_alias*] ...)]]
    [ON DUPLICATE KEY UPDATE *assignment_list*]

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
    [INTO] *tbl_name*
    [PARTITION (*partition_name* [, *partition_name*] ...)]
    SET *assignment_list*
    [AS *row_alias*[(*col_alias* [, *col_alias*] ...)]]
    [ON DUPLICATE KEY UPDATE *assignment_list*]

INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE]
    [INTO] *tbl_name*
    [PARTITION (*partition_name* [, *partition_name*] ...)]
    [(*col_name* [, *col_name*] ...)]
    { SELECT ... 
      | TABLE *table_name* 
      | VALUES *row_constructor_list*
    }
    [ON DUPLICATE KEY UPDATE *assignment_list*]

*value*:
    {*expr* | DEFAULT}

*value_list*:
    *value* [, *value*] ...

*row_constructor_list*:
    ROW(*value_list*)[, ROW(*value_list*)][, ...]

*assignment*:
    *col_name* = 
          *value*
        | [*row_alias*.]*col_name*
        | [*tbl_name*.]*col_name*
        | [*row_alias*.]*col_alias*

*assignment_list*:
    *assignment* [, *assignment*] ...

INSERT将新行插入到现有表中。INSERT ... VALUES、INSERT ... VALUES ROW()和INSERT ... SET形式的语句根据明确指定的值插入行。INSERT ... SELECT形式从另一个表或表中选择的行插入。您还可以在 MySQL 8.0.19 及更高版本中使用INSERT ... TABLE从单个表中插入行。带有ON DUPLICATE KEY UPDATE子句的INSERT使得如果要插入的行会导致在UNIQUE索引或PRIMARY KEY中出现重复值，则现有行可以被更新。在 MySQL 8.0.19 及更高版本中，可以使用带有一个或多个可选列别名的行别名与ON DUPLICATE KEY UPDATE一起引用要插入的行。

有关INSERT ... SELECT和INSERT ... ON DUPLICATE KEY UPDATE的更多信息，请参见第 15.2.7.1 节，“插入...选择语句”和第 15.2.7.2 节，“插入...在重复键更新语句中”。

在 MySQL 8.0 中，服务器接受但忽略DELAYED关键字。关于这一点的原因，请参见第 15.2.7.3 节，“延迟插入语句”，

插入到表中需要表的INSERT权限。如果使用ON DUPLICATE KEY UPDATE子句，并且重复键导致执行UPDATE，则语句需要更新要更新的列的UPDATE权限。对于只读取但不修改的列，您只需要SELECT权限（例如，在ON DUPLICATE KEY UPDATE子句中仅在赋值的右侧引用的列）。

在插入分区表时，可以控制哪些分区和子分区接受新行。PARTITION子句接受表的一个或多个分区或子分区（或两者）的逗号分隔名称列表。如果给定INSERT语句要插入的任何行与列出的分区之一不匹配，则该INSERT语句将失败，并显示错误消息找到一个不匹配给定分区集的行。有关更多信息和示例，请参见第 26.5 节，“分区选择”。

tbl_name是应该插入行的表。按照以下方式指定语句提供值的列：

在表名后提供一个用逗号分隔的列名的括号列表。在这种情况下，每个命名列的值必须由VALUES列表、VALUES ROW()列表或SELECT语句提供。对于INSERT TABLE形式，源表中的列数必须与要插入的列数相匹配。
如果对于INSERT ... VALUES或INSERT ... SELECT没有指定列名列表，则必须通过VALUES列表、SELECT语句或TABLE语句为表中的每个列提供值。如果不知道表中列的顺序，请使用DESCRIBE *tbl_name*来查找。
SET子句通过列名明确指定，以及为每个列分配的值。

列值可以以多种方式给出：

如果未启用严格的 SQL 模式，则未明确给定值的任何列都将设置为其默认（显式或隐式）值。例如，如果指定的列列表未命名表中的所有列，则未命名列将设置为其默认值。默认值分配在第 13.6 节，“数据类型默认值”中描述。另请参阅第 1.6.3.3 节，“对无效数据的强制约束”。

如果启用了严格的 SQL 模式，INSERT语句将在没有为没有默认值的每个列指定显式值时生成错误。参见第 7.1.11 节，“服务器 SQL 模式”。
如果列列表和VALUES列表都为空，INSERT将创建一行，其中每个列都设置为其默认值：
```
INSERT INTO *tbl_name* () VALUES();
```
如果未启用严格模式，MySQL 将对任何没有明确定义默认值的列使用隐式默认值。如果启用了严格模式，如果任何列没有默认值，则会发生错误。
使用关键字DEFAULT将列明确设置为其默认值。这样可以更轻松地编写INSERT语句，为除少数列外的所有列分配值，因为它使您可以避免编写不包括表中每列值的不完整VALUES列表。否则，您必须提供与VALUES列表中每个值对应的列名列表。
如果插入生成的列，则唯一允许的值是DEFAULT。有关生成列的信息，请参见第 15.1.20.8 节，“CREATE TABLE and Generated Columns”。
在表达式中，您可以使用DEFAULT(*col_name*)来为列col_name生成默认值。
如果提供列值的表达式expr的数据类型与列数据类型不匹配，则可能发生类型转换。给定值的转换可能导致根据列类型而插入不同的值。例如，将字符串'1999.0e-2'插入到INT - INTEGER, INT, SMALLINT, TINYINT, MEDIUMINT, BIGINT")、FLOAT - FLOAT, DOUBLE")、DECIMAL(10,6) - DECIMAL, NUMERIC")或YEAR列中，分别插入值1999、19.9921、19.992100或1999。存储在INT - INTEGER, INT, SMALLINT, TINYINT, MEDIUMINT, BIGINT")和YEAR列中的值为1999，因为字符串转换为数字仅查看字符串的初始部分，该部分可能被视为有效整数或年份。对于FLOAT - FLOAT, DOUBLE")和DECIMAL - DECIMAL, NUMERIC")列，字符串转换为数字将整个字符串视为有效的数值。
表达式expr可以引用先前在值列表中设置的任何列。例如，您可以这样做，因为col2的值引用了先前分配的col1：
```
INSERT INTO *tbl_name* (col1,col2) VALUES(15,col1*2);
```
但是以下内容是不合法的，因为col1的值引用了在col1之后分配的col2：
```
INSERT INTO *tbl_name* (col1,col2) VALUES(col2*2,15);
```
对于包含AUTO_INCREMENT值的列会出现异常。因为AUTO_INCREMENT值是在其他值分配之后生成的，对AUTO_INCREMENT列的任何引用在赋值时返回0。

使用VALUES语法的INSERT语句可以插入多行。要做到这一点，包含多个逗号分隔的列值列表，列表用括号括起并用逗号分隔。示例：

INSERT INTO *tbl_name* (a,b,c)
    VALUES(1,2,3), (4,5,6), (7,8,9);

每个值列表必须包含与要插入的每行的值数量完全相同的值。以下语句是无效的，因为它包含一个包含九个值的列表，而不是三个包含三个值的列表：

INSERT INTO *tbl_name* (a,b,c) VALUES(1,2,3,4,5,6,7,8,9);

在这种情况下，VALUE是VALUES的同义词。两者都不暗示值列表的数量，也不暗示每个列表的值的数量。无论是单个值列表还是多个列表，以及每个列表中的值的数量，都可以使用任一项。

使用VALUES ROW()语法的INSERT语句也可以插入多行。在这种情况下，每个值列表必须包含在一个ROW()（行构造函数）中，就像这样：

INSERT INTO *tbl_name* (a,b,c)
    VALUES ROW(1,2,3), ROW(4,5,6), ROW(7,8,9);

可以使用ROW_COUNT() SQL 函数或mysql_affected_rows() C API 函数获取INSERT的受影响行数。请参阅第 14.15 节，“信息函数”和 mysql_affected_rows()。

如果您使用INSERT ... VALUES或INSERT ... VALUES ROW()插入多个值列表，或者INSERT ... SELECT或INSERT ... TABLE，则语句以以下格式返回信息字符串：

Records: *N1* Duplicates: *N2* Warnings: *N3*

如果您正在使用 C API，可以通过调用mysql_info()函数获取信息字符串。请参阅 mysql_info()。

Records表示语句处理的行数。（这不一定是实际插入的行数，因为Duplicates可能不为零。）Duplicates表示由于重复某些现有唯一索引值而无法插入的行数。Warnings表示尝试插入某种方式有问题的列值的次数。警告可能在以下任何条件下发生：

将NULL插入已声明为NOT NULL的列。对于多行INSERT语句或INSERT INTO ... SELECT语句，该列设置为列数据类型的隐式默认值。对于数值类型，这是0，对于字符串类型，这是空字符串（''），对于日期和时间类型，这是“零”值。由于服务器不会检查SELECT的结果集是否返回单行，因此INSERT INTO ... SELECT语句与多行插入处理方式相同。（对于单行INSERT，当将NULL插入到NOT NULL列时，不会发出警告。相反，该语句将因错误而失败。）
将数值列设置为超出列范围的值。该值被截断为范围的最近端点。
将值赋给数值列，例如'10.34 a'。尾随的非数字文本被剥离，剩余的数值部分被插入。如果字符串值没有前导数值部分，则该列设置为0。
将字符串插入字符串列（CHAR、VARCHAR、TEXT或BLOB），其超过列的最大长度。该值被截断为列的最大长度。
将值插入日期或时间列，该值对于数据类型是非法的。该列设置为该类型的适当零值。
有关涉及AUTO_INCREMENT列值的INSERT示例，请参见第 5.6.9 节“使用 AUTO_INCREMENT”。

如果INSERT向具有AUTO_INCREMENT列的表中插入一行，则可以使用LAST_INSERT_ID() SQL 函数或mysql_insert_id() C API 函数找到用于该列的值。

注意

这两个函数的行为并不总是相同。关于与AUTO_INCREMENT列有关的INSERT语句的行为在第 14.15 节“信息函数”和 mysql_insert_id()中进一步讨论。

INSERT语句支持以下修饰符：

如果使用LOW_PRIORITY修饰符，INSERT的执行将延迟，直到没有其他客户端从表中读取数据。这包括在现有客户端正在读取数据时开始读取数据的其他客户端，以及INSERT LOW_PRIORITY语句正在等待的情况。因此，发出INSERT LOW_PRIORITY语句的客户端可能需要等待很长时间。

LOW_PRIORITY仅影响仅使用表级锁定的存储引擎（如MyISAM，MEMORY和MERGE）。

注意

LOW_PRIORITY通常不应与MyISAM表一起使用，因为这样做会禁用并发插入。参见第 10.11.3 节，“并发插入”。
如果指定了HIGH_PRIORITY，它会覆盖服务器在启动时使用--low-priority-updates选项的效果。它还会导致不使用并发插入。参见第 10.11.3 节，“并发插入”。

HIGH_PRIORITY仅影响仅使用表级锁定的存储引擎（如MyISAM，MEMORY和MERGE）。
如果使用IGNORE修饰符，执行INSERT语句时发生的可忽略错误将被忽略。例如，如果没有IGNORE，在表中重复现有UNIQUE索引或PRIMARY KEY值的行会导致重复键错误并中止语句。使用IGNORE，该行将被丢弃，不会发生错误。被忽略的错误会生成警告。

IGNORE对于插入到未找到匹配给定值的分区表具有类似的效果。如果没有IGNORE，这样的INSERT语句将因错误而中止。当使用INSERT IGNORE时，对于包含不匹配值的行，插入操作会悄悄失败，但会插入匹配的行。例如，请参见第 26.2.2 节，“LIST 分区”。

如果未指定IGNORE，会触发错误的数据转换将中止语句。使用IGNORE，无效值将调整为最接近的值并插入；会产生警告，但语句不会中止。您可以使用mysql_info() C API 函数确定实际插入表中的行数。

有关更多信息，请参见 IGNORE 对语句执行的影响。

你可以使用REPLACE来覆盖旧行，而不是使用INSERT。REPLACE是对待包含重复旧行的唯一键值的新行的处理的对应项：新行取代旧行而不是被丢弃。参见 Section 15.2.12, “REPLACE Statement”。
如果你指定了ON DUPLICATE KEY UPDATE，并且插入了一行会导致在UNIQUE索引或PRIMARY KEY中出现重复值的情况，旧行将被UPDATE。每行的受影响行数为 1，如果该行被插入为新行，则为 2，如果更新了现有行，则为 0。如果在连接到mysqld时，通过在mysql_real_connect() C API 函数中指定CLIENT_FOUND_ROWS标志，受影响行数为 1（而不是 0），如果现有行被设置为其当前值。参见 Section 15.2.7.2, “INSERT ... ON DUPLICATE KEY UPDATE Statement”。
INSERT DELAYED在 MySQL 5.6 中已被弃用，并计划最终移除。在 MySQL 8.0 中，DELAYED修饰符被接受但被忽略。请改用INSERT（不带DELAYED）。参见 Section 15.2.7.3, “INSERT DELAYED Statement”。

原文：dev.mysql.com/doc/refman/8.0/en/insert-select.html

15.2.7.1 INSERT ... SELECT Statement

INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE]
    [INTO] *tbl_name*
    [PARTITION (*partition_name* [, *partition_name*] ...)]
    [(*col_name* [, *col_name*] ...)]
    {   SELECT ... 
      | TABLE *table_name* 
      | VALUES *row_constructor_list*
    }
    [ON DUPLICATE KEY UPDATE *assignment_list*]

*value*:
    {*expr* | DEFAULT}

*value_list*:
    *value* [, *value*] ...

*row_constructor_list*:
    ROW(*value_list*)[, ROW(*value_list*)][, ...]

*assignment*:
    *col_name* = 
          *value*
        | [*row_alias*.]*col_name*
        | [*tbl_name*.]*col_name*
        | [*row_alias*.]*col_alias*

*assignment_list*:
    *assignment* [, *assignment*] ...

使用 INSERT ... SELECT，你可以快速地从 SELECT 语句的结果中向表中插入许多行，该语句可以从一个或多个表中选择。例如：

INSERT INTO tbl_temp2 (fld_id)
  SELECT tbl_temp1.fld_order_id
  FROM tbl_temp1 WHERE tbl_temp1.fld_order_id > 100;

从 MySQL 8.0.19 开始，你可以使用 TABLE 语句代替 SELECT，如下所示：

INSERT INTO ta TABLE tb;

TABLE tb 等同于 SELECT * FROM tb。当需要将源表中的所有列插入目标表，并且不需要使用 WHERE 进行过滤时，这可能很有用。此外，可以使用 ORDER BY 按一个或多个列对 TABLE 中的行进行排序，并且可以使用 LIMIT 子句限制插入的行数。更多信息，请参见 Section 15.2.16, “TABLE Statement”。

INSERT ... SELECT 语句遵循以下条件，并且除非另有说明，也适用于 INSERT ... TABLE：

指定 IGNORE 来忽略可能导致重复键违规的行。
INSERT 语句的目标表可以出现在查询的 SELECT 部分的 FROM 子句中，或者作为由 TABLE 命名的表。但是，在子查询中不能插入到同一表并从同一表中选择。

当从同一表中选择并插入时，MySQL 会创建一个内部临时表来保存 SELECT 中的行，然后将这些行插入目标表。但是，当 t 是一个 TEMPORARY 表时，你不能使用 INSERT INTO t ... SELECT ... FROM t，因为 TEMPORARY 表在同一语句中不能被引用两次。出于同样的原因，当 t 是一个临时表时，你也不能使用 INSERT INTO t ... TABLE t。请参见 Section 10.4.4, “Internal Temporary Table Use in MySQL”，以及 Section B.3.6.2, “TEMPORARY Table Problems”。
AUTO_INCREMENT 列的工作方式与往常一样。
为了确保二进制日志可以用于重新创建原始表，MySQL 不允许对 INSERT ... SELECT 或 INSERT ... TABLE 语句进行并发插入（参见 Section 10.11.3, “Concurrent Inserts”）。
为了避免在SELECT和INSERT引用相同表时出现模糊的列引用问题，请为SELECT部分中使用的每个表提供唯一别名，并在该部分中使用适当的别名限定列名。

TABLE语句不支持别名。

您可以明确选择要使用PARTITION子句的源表或目标表（或两者）的哪些分区或子分区（或两者）。当PARTITION与语句的SELECT部分中的源表名称一起使用时，仅从其分区列表中命名的分区或子分区中选择行。当PARTITION与语句的INSERT部分的目标表名称一起使用时，必须能够将所有选定的行插入到分区列表后面命名的分区或子分区中。否则，INSERT ... SELECT语句将失败。有关更多信息和示例，请参阅 Section 26.5, “Partition Selection”。

TABLE不支持PARTITION子句。

对于INSERT ... SELECT语句，请参阅 Section 15.2.7.2, “INSERT ... ON DUPLICATE KEY UPDATE Statement”，了解在ON DUPLICATE KEY UPDATE子句中可以引用SELECT列的条件。这也适用于INSERT ... TABLE。

当没有ORDER BY子句的SELECT或TABLE语句返回行时，返回行的顺序是不确定的。这意味着，在使用复制时，不能保证这样的SELECT在源和副本上以相同的顺序返回行，这可能导致它们之间的不一致。为了防止这种情况发生，始终编写要使用ORDER BY子句进行复制的INSERT ... SELECT或INSERT ... TABLE语句，以在源和副本上产生相同的行顺序。另请参阅 Section 19.5.1.18, “Replication and LIMIT”。

由于这个问题，INSERT ... SELECT ON DUPLICATE KEY UPDATE 和 INSERT IGNORE ... SELECT 语句被标记为基于语句的复制不安全。在使用基于语句模式时，这些语句会在错误日志中产生警告，并在使用MIXED模式时以基于行的格式写入二进制日志。（Bug #11758262, Bug #50439）

请参阅 Section 19.2.1.1, “基于语句和基于行的复制的优缺点”。

原文：dev.mysql.com/doc/refman/8.0/en/insert-on-duplicate.html

15.2.7.2 INSERT ... ON DUPLICATE KEY UPDATE Statement

如果您指定了一个ON DUPLICATE KEY UPDATE子句，并且要插入的行会导致在UNIQUE索引或PRIMARY KEY中出现重复值，则会更新旧行。例如，如果列a被声明为UNIQUE并包含值1，则以下两个语句具有类似的效果：

INSERT INTO t1 (a,b,c) VALUES (1,2,3)
  ON DUPLICATE KEY UPDATE c=c+1;

UPDATE t1 SET c=c+1 WHERE a=1;

效果并不完全相同：对于InnoDB表，其中a是自增列，INSERT语句会增加自增值，但UPDATE不会。

如果列b也是唯一的，那么INSERT等同于这个UPDATE语句：

UPDATE t1 SET c=c+1 WHERE a=1 OR b=2 LIMIT 1;

如果a=1 OR b=2匹配多行，只会更新一行。一般来说，应尽量避免在具有多个唯一索引的表上使用ON DUPLICATE KEY UPDATE子句。

使用ON DUPLICATE KEY UPDATE，每行的受影响行数为 1，如果该行被插入为新行，则为 2，如果更新现有行，则为 0。如果在连接到mysqld时，通过在mysql_real_connect() C API 函数中指定CLIENT_FOUND_ROWS标志，受影响行数为 1（而不是 0），如果将现有行设置为其当前值。

如果表中包含一个AUTO_INCREMENT列，并且INSERT ... ON DUPLICATE KEY UPDATE插入或更新一行，则LAST_INSERT_ID()函数将返回AUTO_INCREMENT值。

ON DUPLICATE KEY UPDATE子句可以包含多个列赋值，用逗号分隔。

在ON DUPLICATE KEY UPDATE子句中的赋值表达式中，您可以使用VALUES(*col_name*)函数引用INSERT部分的列值。换句话说，在ON DUPLICATE KEY UPDATE子句中，VALUES(*col_name*)指的是如果没有发生重复键冲突，将要插入的col_name的值。这个函数在多行插入中特别有用。VALUES()函数只在ON DUPLICATE KEY UPDATE子句或INSERT语句中有意义，否则返回NULL。例如：

INSERT INTO t1 (a,b,c) VALUES (1,2,3),(4,5,6)
  ON DUPLICATE KEY UPDATE c=VALUES(a)+VALUES(b);

该语句与以下两个语句相同：

INSERT INTO t1 (a,b,c) VALUES (1,2,3)
  ON DUPLICATE KEY UPDATE c=3;
INSERT INTO t1 (a,b,c) VALUES (4,5,6)
  ON DUPLICATE KEY UPDATE c=9;

注意

从 MySQL 8.0.20 开始，使用VALUES()来引用新行和列已经不推荐，并且可能在未来的 MySQL 版本中被移除。相反，使用行和列别名，如本节接下来几段所述。

从 MySQL 8.0.19 开始，可以使用行别名，可选地，在VALUES或SET子句后面插入一个或多个列，并在AS关键字之前。使用行别名new，之前使用VALUES()访问新列值的语句可以以这种形式编写：

INSERT INTO t1 (a,b,c) VALUES (1,2,3),(4,5,6) AS new
  ON DUPLICATE KEY UPDATE c = new.a+new.b;

如果此外，你使用列别名m、n和p，你可以在赋值子句中省略行别名，并像这样编写相同的语句：

INSERT INTO t1 (a,b,c) VALUES (1,2,3),(4,5,6) AS new(m,n,p)
  ON DUPLICATE KEY UPDATE c = m+n;

在这种情况下使用列别名时，即使在赋值子句中没有直接使用它，你仍然必须在VALUES子句后面使用行别名。

从 MySQL 8.0.20 开始，使用UPDATE子句中的VALUES()的INSERT ... SELECT ... ON DUPLICATE KEY UPDATE语句会发出警告：

INSERT INTO t1
  SELECT c, c+d FROM t2
  ON DUPLICATE KEY UPDATE b = VALUES(b);

你可以通过使用子查询来消除此类警告，如下所示：

INSERT INTO t1
  SELECT * FROM (SELECT c, c+d AS e FROM t2) AS dt
  ON DUPLICATE KEY UPDATE b = e;

你也可以在SET子句中使用行和列别名，如前面提到的。刚刚显示的两个INSERT ... ON DUPLICATE KEY UPDATE语句中使用SET而不是VALUES可以像这样完成：

INSERT INTO t1 SET a=1,b=2,c=3 AS new
  ON DUPLICATE KEY UPDATE c = new.a+new.b;

INSERT INTO t1 SET a=1,b=2,c=3 AS new(m,n,p)
  ON DUPLICATE KEY UPDATE c = m+n;

行别名不能与表名相同。如果不使用列别名，或者如果它们与列名相同，则必须在ON DUPLICATE KEY UPDATE子句中使用行别名来区分它们。列别名必须与它们应用的行别名唯一（即，不得有指向同一行列的相同列别名）。

对于INSERT ... SELECT语句，在ON DUPLICATE KEY UPDATE子句中可以引用的SELECT查询表达式的可接受形式如下：

引用单个表查询中的列，该表可能是一个派生表。
引用跨多个表连接的查询中的列。
引用DISTINCT查询中的列。
引用其他表中的列，只要SELECT没有使用GROUP BY。一个副作用是你必须限定对非唯一列名的引用。

不支持从UNION中引用列。为了解决这个限制，将UNION重写为派生表，以便其行可以被视为单表结果集。例如，这个语句会产生错误：

INSERT INTO t1 (a, b)
  SELECT c, d FROM t2
  UNION
  SELECT e, f FROM t3
ON DUPLICATE KEY UPDATE b = b + c;

相反，使用等效语句将UNION重写为派生表：

INSERT INTO t1 (a, b)
SELECT * FROM
  (SELECT c, d FROM t2
   UNION
   SELECT e, f FROM t3) AS dt
ON DUPLICATE KEY UPDATE b = b + c;

将查询重写为派生表的技术还可以使GROUP BY查询中的列引用成为可能。

因为INSERT ... SELECT语句的结果取决于SELECT中行的排序，而这种顺序并不总是能够保证，所以在记录源和副本的INSERT ... SELECT ON DUPLICATE KEY UPDATE语句时可能会出现分歧。因此，当使用基于语句的复制时，INSERT ... SELECT ON DUPLICATE KEY UPDATE语句被标记为不安全。这种语句在使用基于语句的模式时会在错误日志中产生警告，并在使用MIXED模式时以基于行的格式写入二进制日志。对于针对具有多个唯一或主键的表的INSERT ... ON DUPLICATE KEY UPDATE语句也被标记为不安全。（Bug #11765650, Bug #58637）

参见 Section 19.2.1.1, “基于语句和基于行的复制的优缺点”。

原文：dev.mysql.com/doc/refman/8.0/en/insert-delayed.html

15.2.7.3 INSERT DELAYED Statement

INSERT DELAYED ...

DELAYED选项用于INSERT语句，是 MySQL 对标准 SQL 的扩展。在 MySQL 的早期版本中，它可以用于某些类型的表（如MyISAM），这样当客户端使用INSERT DELAYED时，客户端立即收到服务器的确认，并且该行被排队等待在表不被其他线程使用时插入。

在 MySQL 5.6 中，DELAYED插入和替换已被弃用。在 MySQL 8.0 中，不再支持DELAYED。服务器会识别但忽略DELAYED关键字，将插入处理为非延迟插入，并生成一个ER_WARN_LEGACY_SYNTAX_CONVERTED警告：INSERT DELAYED 不再受支持。该语句已转换为 INSERT。DELAYED关键字计划在将来的版本中移除。

15.2.8 INTERSECT 子句

原文：dev.mysql.com/doc/refman/8.0/en/intersect.html

*query_expression_body* INTERSECT [ALL | DISTINCT] *query_expression_body*
    [INTERSECT [ALL | DISTINCT] *query_expression_body*]
    [...]

*query_expression_body*:
    *See Section 15.2.14, “Set Operations with UNION, INTERSECT, and EXCEPT”*

INTERSECT 限制了来自多个查询块的结果，使其仅包含所有查询块中共同的行。示例：

mysql> TABLE a;
+------+------+
| m    | n    |
+------+------+
|    1 |    2 |
|    2 |    3 |
|    3 |    4 |
+------+------+
3 rows in set (0.00 sec)

mysql> TABLE b;
+------+------+
| m    | n    |
+------+------+
|    1 |    2 |
|    1 |    3 |
|    3 |    4 |
+------+------+
3 rows in set (0.00 sec)

mysql> TABLE c;
+------+------+
| m    | n    |
+------+------+
|    1 |    3 |
|    1 |    3 |
|    3 |    4 |
+------+------+
3 rows in set (0.00 sec)

mysql> TABLE a INTERSECT TABLE b;
+------+------+
| m    | n    |
+------+------+
|    1 |    2 |
|    3 |    4 |
+------+------+
2 rows in set (0.00 sec)

mysql> TABLE a INTERSECT TABLE c;
+------+------+
| m    | n    |
+------+------+
|    3 |    4 |
+------+------+
1 row in set (0.00 sec)

与 UNION 和 EXCEPT 一样，如果未指定 DISTINCT 或 ALL，则默认为 DISTINCT。

DISTINCT 可以从交集的任一侧去除重复项，如下所示：

mysql> TABLE c INTERSECT DISTINCT TABLE c;
+------+------+
| m    | n    |
+------+------+
|    1 |    3 |
|    3 |    4 |
+------+------+
2 rows in set (0.00 sec)

mysql> TABLE c INTERSECT ALL TABLE c;
+------+------+
| m    | n    |
+------+------+
|    1 |    3 |
|    1 |    3 |
|    3 |    4 |
+------+------+
3 rows in set (0.00 sec)

（TABLE c INTERSECT TABLE c 相当于刚刚显示的两个语句中的第一个。）

与 UNION 一样，操作数必须具有相同数量的列。结果集列类型也与 UNION 相同。

INTERSECT 的优先级高于 UNION 和 EXCEPT，因此这两个语句是等效的：

TABLE r EXCEPT TABLE s INTERSECT TABLE t;

TABLE r EXCEPT (TABLE s INTERSECT TABLE t);

对于 INTERSECT ALL，左侧表中任何唯一行的最大支持重复次数为 4294967295。

INTERSECT 在 MySQL 8.0.31 中添加。

15.2.9 LOAD DATA 语句

原文：dev.mysql.com/doc/refman/8.0/en/load-data.html

LOAD DATA
    [LOW_PRIORITY | CONCURRENT] [LOCAL]
    INFILE '*file_name*'
    [REPLACE | IGNORE]
    INTO TABLE *tbl_name*
    [PARTITION (*partition_name* [, *partition_name*] ...)]
    [CHARACTER SET *charset_name*]
    [{FIELDS | COLUMNS}
        [TERMINATED BY '*string*']
        [[OPTIONALLY] ENCLOSED BY '*char*']
        [ESCAPED BY '*char*']
    ]
    [LINES
        [STARTING BY '*string*']
        [TERMINATED BY '*string*']
    ]
    [IGNORE *number* {LINES | ROWS}]
    [(*col_name_or_user_var*
        [, *col_name_or_user_var*] ...)]
    [SET *col_name*={*expr* | DEFAULT}
        [, *col_name*={*expr* | DEFAULT}] ...]

LOAD DATA 语句以非常高的速度从文本文件中读取行到表中。文件可以从服务器主机或客户端主机读取，这取决于是否给出 LOCAL 修饰符。LOCAL 还会影响数据解释和错误处理。

LOAD DATA 是 SELECT ... INTO OUTFILE 的补充。（参见 Section 15.2.13.1, “SELECT ... INTO 语句”。）要将表中的数据写入文件，请使用 SELECT ... INTO OUTFILE。要将文件中的数据读回表中，请使用 LOAD DATA。这两个语句的 FIELDS 和 LINES 子句的语法相同。

mysqlimport 实用程序提供了另一种加载数据文件的方式；它通过向服务器发送 LOAD DATA 语句来操作。请参阅 Section 6.5.5, “mysqlimport — 数据导入程序”。

有关 INSERT 与 LOAD DATA 的效率以及加速 LOAD DATA 的信息，请参阅 Section 10.2.5.1, “优化 INSERT 语句”。

非 LOCAL 与 LOCAL 操作
输入文件字符集
输入文件位置
安全要求
重复键和错误处理
索引处理
字段和行处理
列列表规范
输入预处理
列值赋值
分区表支持
并发考虑
语句结果信息
复制考虑
杂项主题

非 LOCAL 与 LOCAL 操作的区别

LOCAL修饰符影响LOAD DATA的这些方面，与非LOCAL操作相比：

它改变了输入文件的预期位置；参见输入文件位置。
它改变了语句的安全要求；参见安全要求。
它对输入文件内容的解释和错误处理具有与IGNORE修饰符相同的效果；参见重复键和错误处理，以及列值分配。

只有在服务器和客户端都配置为允许时，LOCAL才有效。例如，如果mysqld启动时禁用了local_infile系统变量，LOCAL会产生错误。参见第 8.1.6 节，“LOAD DATA LOCAL 的安全考虑”。

输入文件字符集

文件名必须以文字字符串形式给出。在 Windows 上，路径名中的反斜杠应指定为正斜杠或双反斜杠。服务器使用由character_set_filesystem系统变量指示的字符集解释文件名。

默认情况下，服务器使用由character_set_database系统变量指示的字符集解释文件内容。如果文件内容使用与此默认值不同的字符集，最好通过使用CHARACTER SET子句指定该字符集。字符集为binary表示“无转换”。

SET NAMES和character_set_client的设置不影响文件内容的解释。

LOAD DATA 将文件中的所有字段解释为具有相同字符集，而不管字段值加载到的列的数据类型如何。为了正确解释文件，您必须确保它是用正确的字符集编写的。例如，如果您使用 mysqldump -T 写入数据文件，或者通过在 mysql 中发出 SELECT ... INTO OUTFILE 语句来写入数据文件，请务必使用 --default-character-set 选项以在加载文件时使用的字符集中写入输出。

注意

不可能加载使用 ucs2、utf16、utf16le 或 utf32 字符集的数��文件。

输入文件位置

这些规则确定了 LOAD DATA 输入文件的位置：

如果未指定 LOCAL，则文件必须位于服务器主机上。服务器直接读取文件，定位如下：
- 如果文件名是绝对路径名，服务器将按照给定的路径使用它。
- 如果文件名是带有前导组件的相对路径名，服务器将在其数据目录相对于文件查找。
- 如果文件名没有前导组件，服务器将在默认数据库的数据库目录中查找文件。
如果指定了 LOCAL，则文件必须位于客户端主机上。客户端程序读取文件，定位如下：
- 如果文件名是绝对路径名，客户端程序将按照给定的路径使用它。
- 如果文件名是相对路径名，客户端程序将在其调用目录相对于文件查找。
当使用 LOCAL 时，客户端程序读取文件并将其内容发送到服务器。服务器在存储临时文件的目录中创建文件的副本。请参阅 Section B.3.3.5, “Where MySQL Stores Temporary Files”。在此目录中没有足够空间来存储副本可能导致 LOAD DATA LOCAL 语句失败。

非 LOCAL 规则意味着服务器将相对于其数据目录读取名为 ./myfile.txt 的文件，而将名为 myfile.txt 的文件从默认数据库的数据库目录中读取。例如，如果在 db1 是默认数据库的情况下执行以下 LOAD DATA 语句，服务器将从 db1 的数据库目录中读取文件 data.txt，即使该语句明确将文件加载到 db2 数据库中的表中：

LOAD DATA INFILE 'data.txt' INTO TABLE db2.my_table;

注意

服务器还使用非LOCAL规则来定位IMPORT TABLE语句的.sdi文件。

安全要求

对于非LOCAL加载操作，服务器会读取位于服务器主机上的文本文件，因此必须满足以下安全要求：

您必须具有FILE权限。请参阅 Section 8.2.2, “MySQL 提供的权限”。
该操作受secure_file_priv系统变量设置的影响：
- 如果变量值是非空目录名称，则文件必须位于该目录中。
- 如果变量值为空（这是不安全的），文件只需对服务器可读。

对于LOCAL加载操作，客户端程序会读取位于客户端主机上的文本文件。由于文件内容通过客户端传输到服务器，使用LOCAL比服务器直接访问文件要慢一点。另一方面，您不需要FILE权限，并且文件可以位于客户端程序可以访问的任何目录中。

重复键和错误处理

REPLACE和IGNORE修饰符控制对具有唯一键值（PRIMARY KEY或UNIQUE索引值）上现有表行重复的新（输入）行的处理：

使用REPLACE，具有与现有行中唯一键值相同值的新行将替换现有行。请参阅 Section 15.2.12, “REPLACE 语句”。
使用IGNORE，具有与唯一键值上现有行重复的新行将被丢弃。有关更多信息，请参阅 IGNORE 对语句执行的影响。

LOCAL修饰符与IGNORE具有相同的效果。这是因为服务器无法在操作中途停止文件的传输。

如果未指定REPLACE，IGNORE或LOCAL，当发现重复键值时会发生错误，并且文本文件的其余部分将被忽略。

除了影响刚刚描述的重复键处理之外，IGNORE和LOCAL还会影响错误处理：

没有IGNORE或LOCAL，数据解释错误会终止操作。
使用IGNORE或LOCAL，数据解释错误变为警告，并且加载操作会继续，即使 SQL 模式是限制性的。有关示例，请参阅列值分配。

索引处理

在加载操作期间忽略外键约束，请在执行LOAD DATA之前执行SET foreign_key_checks = 0语句。

如果在空的MyISAM表上使用LOAD DATA，则所有非唯一索引将在单独的批处理中创建（与REPAIR TABLE一样）。通常，当您有许多索引时，这使得LOAD DATA速度更快。在一些极端情况下，您可以通过在将文件加载到表中之前使用ALTER TABLE ... DISABLE KEYS关闭它们来更快地创建索引，并在加载文件后使用ALTER TABLE ... ENABLE KEYS重新创建索引。请参见第 10.2.5.1 节，“优化 INSERT 语句”。

字段和行处理

对于LOAD DATA和SELECT ... INTO OUTFILE语句，FIELDS和LINES子句的语法相同。两个子句都是可选的，但如果两者都指定，则FIELDS必须在LINES之前。

如果指定了FIELDS子句，则其每个子句（TERMINATED BY，[OPTIONALLY] ENCLOSED BY和ESCAPED BY）也是可选的，除非您必须至少指定其中一个。这些子句的参数只允许包含 ASCII 字符。

如果不指定FIELDS或LINES子句，则默认值与您编写以下内容相同：

FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\'
LINES TERMINATED BY '\n' STARTING BY ''

在 SQL 语句中的字符串中，反斜杠是 MySQL 转义字符。因此，要指定一个字面上的反斜杠，您必须为解释为单个反斜杠的值指定两个反斜杠。转义序列'\t'和'\n'分别指定制表符和换行符。

换句话说，当读取输入时，LOAD DATA的默认值如下：

在换行符处查找行边界。
不要跳过任何行前缀。
在制表符处将行分割为字段。
不要期望字段被包含在任何引号字符内。
将由转义字符\引导的字符解释为转义序列。例如，\t，\n和\\分别表示制表符，换行符和反斜杠。有关转义序列的完整列表，请参见稍后讨论的FIELDS ESCAPED BY。

相反，当写入输出时，SELECT ... INTO OUTFILE的默认值如下：

在字段之间写入制表符。
不要在任何引号字符内包含字段。
使用\来转义字段值中出现的制表符，换行符或\。
在行末写入换行符。

注意

对于在 Windows 系统上生成的文本文件，正确的文件读取可能需要LINES TERMINATED BY '\r\n'，因为 Windows 程序通常使用两个字符作为行终止符。一些程序，如WordPad，在写文件时可能使用\r作为行终止符。要读取这样的文件，请使用LINES TERMINATED BY '\r'。

如果所有输入行都有一个要忽略的公共前缀，你可以使用LINES STARTING BY '*prefix_string*'来跳过前缀及其之前的任何内容。如果一行不包含前缀，则整行将被跳过。假设你发出以下语句：

LOAD DATA INFILE '/tmp/test.txt' INTO TABLE test
  FIELDS TERMINATED BY ','  LINES STARTING BY 'xxx';

如果数据文件看起来像这样：

xxx"abc",1
something xxx"def",2
"ghi",3

结果行是("abc",1)和("def",2)。文件中的第三行被跳过，因为它不包含前缀。

IGNORE *number* LINES子句可用于忽略文件开头的行。例如，你可以使用IGNORE 1 LINES跳过包含列名的初始标题行：

LOAD DATA INFILE '/tmp/test.txt' INTO TABLE test IGNORE 1 LINES;

当你使用SELECT ... INTO OUTFILE与LOAD DATA结合，将数据库中的数据写入文件，然后稍后将文件中的数据读回数据库时，两个语句的字段和行处理选项必须匹配。否则，LOAD DATA无法正确解释文件的内容。假设你使用SELECT ... INTO OUTFILE来写一个以逗号分隔字段的文件：

SELECT * INTO OUTFILE 'data.txt'
  FIELDS TERMINATED BY ','
  FROM table2;

要读取逗号分隔的文件，正确的语句是：

LOAD DATA INFILE 'data.txt' INTO TABLE table2
  FIELDS TERMINATED BY ',';

如果你尝试用下面显示的语句读取文件，它将无法工作，因为它指示LOAD DATA在字段之间查找制表符：

LOAD DATA INFILE 'data.txt' INTO TABLE table2
  FIELDS TERMINATED BY '\t';

可能的结果是每个输入行被解释为单个字段。

LOAD DATA可用于读取从外部来源获取的文件。例如，许多程序可以以逗号分隔值（CSV）格式导出数据，使得行由逗号分隔的字段并用双引号括起，带有列名的初始行。如果这样的文件中的行以回车/换行对终止，那么这里显示的语句说明了你将用于加载文件的字段和行处理选项：

LOAD DATA INFILE 'data.txt' INTO TABLE *tbl_name*
  FIELDS TERMINATED BY ',' ENCLOSED BY '"'
  LINES TERMINATED BY '\r\n'
  IGNORE 1 LINES;

如果输入值不一定被引号括起来，可以在ENCLOSED BY选项之前使用OPTIONALLY。

任何字段或行处理选项都可以指定空字符串（''）。如果不为空，则FIELDS [OPTIONALLY] ENCLOSED BY和FIELDS ESCAPED BY值必须是单个字符。FIELDS TERMINATED BY、LINES STARTING BY和LINES TERMINATED BY值可以是多个字符。例如，要写入由回车/换行对终止的行，或者读取包含这些行的文件，请指定LINES TERMINATED BY '\r\n'子句。

要读取包含以%%为分隔的笑话的文件，可以这样做

CREATE TABLE jokes
  (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  joke TEXT NOT NULL);
LOAD DATA INFILE '/tmp/jokes.txt' INTO TABLE jokes
  FIELDS TERMINATED BY ''
  LINES TERMINATED BY '\n%%\n' (joke);

FIELDS [OPTIONALLY] ENCLOSED BY控制字段的引用。对于输出（SELECT ... INTO OUTFILE），如果省略OPTIONALLY这个词，所有字段都将被ENCLOSED BY字符包围。这里展示了这种输出的示例（使用逗号作为字段分隔符）：

"1","a string","100.20"
"2","a string containing a , comma","102.20"
"3","a string containing a \" quote","102.20"
"4","a string containing a \", quote and comma","102.20"

如果指定了OPTIONALLY，则ENCLOSED BY字符仅用于封装具有字符串数据类型的列的值（例如CHAR、BINARY、TEXT或ENUM)：

1,"a string",100.20
2,"a string containing a , comma",102.20
3,"a string containing a \" quote",102.20
4,"a string containing a \", quote and comma",102.20

字段值内部的ENCLOSED BY字符通过在其前面加上ESCAPED BY字符来转义。此外，如果指定空的ESCAPED BY值，则可能会无意中生成无法被LOAD DATA正确读取的输出。例如，如果转义字符为空，则前面显示的输出将如下所示。请注意，第四行中的第二个字段包含在引号后面的逗号，这（错误地）似乎终止了该字段：

1,"a string",100.20
2,"a string containing a , comma",102.20
3,"a string containing a " quote",102.20
4,"a string containing a ", quote and comma",102.20

对于输入，如果存在ENCLOSED BY字符，则会从字段值的两端剥离该字符。（无论是否指定OPTIONALLY，都会这样处理；OPTIONALLY对输入解释没有影响。）由ESCAPED BY字符引导的ENCLOSED BY字符的出现被解释为当前字段值的一部分。

如果字段以ENCLOSED BY字符开头，则只有在该字符后跟字段或行TERMINATED BY序列时，才会识别该字符的实例作为终止字段值。为避免歧义，在字段值内部的ENCLOSED BY字符的出现可以加倍，并且被解释为字符的单个实例。例如，如果指定了ENCLOSED BY '"'，则引号将被处理如下：

"The ""BIG"" boss"  -> The "BIG" boss
The "BIG" boss      -> The "BIG" boss
The ""BIG"" boss    -> The ""BIG"" boss

FIELDS ESCAPED BY控制如何读取或写入特殊字符：

对于输入，如果FIELDS ESCAPED BY字符不为空，则会剥离该字符的出现，并且以下字符会被视为字段值的一部分。有一些例外的两字符序列，第一个字符是转义字符。这些序列在下表中显示（使用\表示转义字符）。有关NULL处理规则的描述稍后在本节中描述。

字符转义序列

\0 ASCII NUL（X'00'）字符

\b 退格字符

\n 换行（换行）字符

\r 回车字符

\t 制表符。

\Z ASCII 26（Control+Z）

\N 空值

有关\转义语法的更多信息，请参见第 11.1.1 节，“字符串文字”。

如果FIELDS ESCAPED BY字符为空，则不会发生转义序列解释。
对于输出，如果FIELDS ESCAPED BY字符不为空，则用于在输出时前缀以下字符：
- FIELDS ESCAPED BY字符。
- FIELDS [OPTIONALLY] ENCLOSED BY字符。
- FIELDS TERMINATED BY和LINES TERMINATED BY值的第一个字符，如果ENCLOSED BY字符为空或未指定。
- ASCII 0（实际上在转义字符后写的是 ASCII 0，而不是零值字节）。
如果FIELDS ESCAPED BY字符为空，则不会转义任何字符，NULL会输出为NULL，而不是\N。如果您的数据中的字段值包含刚才列出的任何字符，可能不是一个好主意指定一个空的转义字符。

字符	转义序列
`\0`	ASCII NUL（`X'00'`）字符
`\b`	退格字符
`\n`	换行（换行）字符
`\r`	回车字符
`\t`	制表符。
`\Z`	ASCII 26（Control+Z）
`\N`	空值

在某些情况下，字段和行处理选项会相互作用：

如果LINES TERMINATED BY是空字符串且FIELDS TERMINATED BY不为空，则行也以FIELDS TERMINATED BY结束。
如果FIELDS TERMINATED BY和FIELDS ENCLOSED BY值都为空（''），则使用固定行（非分隔）格式。使用固定行格式，字段之间不使用分隔符（但仍然可以有行终止符）。相反，列值使用足够宽度的字段宽度读取和写入以容纳字段中的所有值。对于TINYINT - INTEGER, INT, SMALLINT, TINYINT, MEDIUMINT, BIGINT")、SMALLINT - INTEGER, INT, SMALLINT, TINYINT, MEDIUMINT, BIGINT")、MEDIUMINT - INTEGER, INT, SMALLINT, TINYINT, MEDIUMINT, BIGINT")、INT - INTEGER, INT, SMALLINT, TINYINT, MEDIUMINT, BIGINT")和BIGINT - INTEGER, INT, SMALLINT, TINYINT, MEDIUMINT, BIGINT")，字段宽度分别为 4、6、8、11 和 20，无论声明的显示宽度是多少。

LINES TERMINATED BY仍用于分隔行。如果一行不包含所有字段，则其余列将设置为默认值。如果您没有行终止符，应将其设置为''。在这种情况下，文本文件必须为每行包含所有字段。

固定行格式还会影响NULL值的处理，如后面所述。

注意

如果使用多字节字符集，则固定大小格式无法工作。

根据使用的FIELDS和LINES选项，NULL值的处理方式会有所不同：

对于默认的FIELDS和LINES值，NULL被写为输出的字段值\N，并且输入时字段值\N被读取为NULL（假设ESCAPED BY字符为\）。
如果FIELDS ENCLOSED BY不为空，则包含字面单词NULL的字段被读取为NULL值。这与包含在FIELDS ENCLOSED BY字符中的单词NULL不同，后者被读取为字符串'NULL'。
如果FIELDS ESCAPED BY为空，则NULL会被写为单词NULL。
使用固定行格式（当FIELDS TERMINATED BY和FIELDS ENCLOSED BY均为空时使用）时，NULL被写为空字符串。这导致表中的NULL值和空字符串在写入文件时无法区分，因为两者都被写为空字符串。如果您需要在读取文件时能够区分这两者，请不要使用固定行格式。

尝试将NULL加载到NOT NULL列中会根据列值分配中描述的规则产生警告或错误。

一些情况不受LOAD DATA支持：

固定大小行（FIELDS TERMINATED BY和FIELDS ENCLOSED BY均为空）以及BLOB或TEXT列。
如果您指定一个与另一个相同或前缀相同的分隔符，LOAD DATA无法正确解释输入。例如，以下FIELDS子句会导致问题：
```
FIELDS TERMINATED BY '"' ENCLOSED BY '"'
```
如果FIELDS ESCAPED BY为空，则包含FIELDS ENCLOSED BY或LINES TERMINATED BY后跟FIELDS TERMINATED BY值的字段值会导致LOAD DATA过早停止读取字段或行。这是因为LOAD DATA无法正确确定字段或行值的结束位置。

列列表规范

以下示例加载persondata表的所有列：

LOAD DATA INFILE 'persondata.txt' INTO TABLE persondata;

默认情况下，在LOAD DATA语句末尾未提供列列表时，预期输入行包含每个表列的字段。如果您只想加载表的部分列，请指定列列表：

LOAD DATA INFILE 'persondata.txt' INTO TABLE persondata
(*col_name_or_user_var* [, *col_name_or_user_var*] ...);

如果输入文件中字段的顺序与表中列的顺序不同，则还必须指定列列表。否则，MySQL 无法确定如何将输入字段与表列匹配。

输入预处理

LOAD DATA语法中的每个col_name_or_user_var实例都是列名或用户变量。使用用户变量，SET子句使您能够在将结果分配给列之前对其值执行预处理转换。

SET子句中的用户变量可以以多种方式使用。以下示例直接使用第一个输入列作为t1.column1的值，并将第二个输入列分配给一个用户变量，该用户变量在用于t1.column2的值之前经历了除法操作：

LOAD DATA INFILE 'file.txt'
  INTO TABLE t1
  (column1, @var1)
  SET column2 = @var1/100;

SET子句可用于提供不是从输入文件派生的值。以下语句将column3设置为当前日期和时间：

LOAD DATA INFILE 'file.txt'
  INTO TABLE t1
  (column1, column2)
  SET column3 = CURRENT_TIMESTAMP;

你也可以通过将其分配给用户变量并不将变量分配给任何表列来丢弃输入值：

LOAD DATA INFILE 'file.txt'
  INTO TABLE t1
  (column1, @dummy, column2, @dummy, column3);

列/变量列表和SET子句的使用受以下限制：

SET子句中的赋值应该只在赋值运算符的左侧具有列名。
您可以在SET赋值的右侧使用子查询。返回要分配给列的值的子查询只能是标量子查询。此外，您不能使用子查询从正在加载的表中进行选择。
被IGNORE *number* LINES子句忽略的行不会被处理为列/变量列表或SET子句。
当使用固定行格式加载数据时，不能使用用户变量，因为用户变量没有显示宽度。

列值分配

要处理输入行，LOAD DATA将其拆分为字段，并根据列/变量列表和SET子句中的值使用这些值，如果它们存在的话。然后将生成的行插入表中。如果表中有BEFORE INSERT或AFTER INSERT触发器，则在插入行之前或之后分别激活它们。

字段值的解释和分配到表列取决于这些因素：

SQL 模式（sql_mode系统变量的值）。该模式可以以各种方式是非限制性的，或者是限制性的。例如，可以启用严格的 SQL 模式，或者模式可以包括值，如NO_ZERO_DATE或NO_ZERO_IN_DATE。
IGNORE和LOCAL修饰符的存在或缺失。

这些因素结合起来产生了由LOAD DATA进行的限制性或非限制性数据解释：

如果 SQL 模式是限制性的，且未指定 IGNORE 或 LOCAL 修饰符，则数据解释是限制性的。错误会终止加载操作。
如果 SQL 模式是非限制性的，或者指定了 IGNORE 或 LOCAL 修饰符，则数据解释是非限制性的。（特别是，如果未指定 REPLACE 修饰符，则指定任一修饰符覆盖了限制性 SQL 模式。）错误变为警告，加载操作继续进行。

限制性数据解释使用以下规则：

字段过多或过少会导致错误。
将NULL（即\N）赋给非NULL列会导致错误。
超出列数据类型范围的值会导致错误。
无效值产生错误。例如，数值列的值如 'x' 会导致错误，而不是转换为 0。

相比之下，非限制性数据解释使用以下规则：

如果输入行字段太多，则额外字段将被忽略，并且警告数量会增加。
如果输入行字段太少，则为缺少输入字段的列分配它们的默认值。默认值分配在第 13.6 节，“数据类型默认值”中描述。
将NULL（即\N）赋给非NULL列会将隐式默认值分配给列数据类型。隐式默认值在第 13.6 节，“数据类型默认值”中描述。
无效值产生警告而不是错误，并转换为列数据类型的“最接近”有效值。例如：
- 对于数值列的值如 'x' 会转换为 0。
- 超出范围的数值或时间值将被剪切到列数据类型范围的最近端点。
- 对于 DATETIME、DATE 或 TIME 列的无效值将插入为隐式默认值，无论 SQL 模式 NO_ZERO_DATE 设置如何。隐式默认值是该类型的适当“零”值（'0000-00-00 00:00:00'、'0000-00-00' 或 '00:00:00'）。请参见第 13.2 节，“日期和时间数据类型”。
LOAD DATA 对待空字段值与缺少字段不同：
- 对于字符串类型，该列被设置为空字符串。
- 对于数值类型，该列被设置为0。
- 对于日期和时间类型，该列被设置为该类型的适当“零”值。请参见第 13.2 节，“日期和时间数据类型”。
这些是在 INSERT 或 UPDATE 语句中明确将空字符串分配给字符串、数值、日期或时间类型时的结果。

只有当列中存在NULL值（即\N）且该列未声明允许NULL值，或者TIMESTAMP 列的默认值为当前时间戳且在指定字段列表时被省略时，TIMESTAMP 列才设置为当前日期和时间。

LOAD DATA 将所有输入视为字符串，因此不能像使用INSERT 语句那样在ENUM 或SET 列中使用数值。所有ENUM 和SET 值必须指定为字符串。

不能直接使用二进制表示法（例如，b'011010'）加载BIT 值。为解决此问题，请使用SET子句去除前导的b'和尾随的'，并执行二进制到十进制的转换，以便 MySQL 正确将值加载到BIT 列中：

$> cat /tmp/bit_test.txt
b'10'
b'1111111'
$> mysql test
mysql> LOAD DATA INFILE '/tmp/bit_test.txt'
       INTO TABLE bit_test (@var1)
       SET b = CAST(CONV(MID(@var1, 3, LENGTH(@var1)-3), 2, 10) AS UNSIGNED);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Deleted: 0  Skipped: 0  Warnings: 0

mysql> SELECT BIN(b+0) FROM bit_test;
+----------+
| BIN(b+0) |
+----------+
| 10       |
| 1111111  |
+----------+
2 rows in set (0.00 sec)

对于以0b二进制表示法（例如，0b011010）的BIT 值，请改用以下SET子句以去除前导的0b：

SET b = CAST(CONV(MID(@var1, 3, LENGTH(@var1)-2), 2, 10) AS UNSIGNED)

分区表支持

LOAD DATA 支持使用PARTITION子句显式选择分区，其中包含一个或多个逗号分隔的分区、子分区或两者的名称列表。当使用此子句时，如果文件中的任何行无法插入到列表中命名的任何分区或子分区中，则该语句将失败，并显示错误消息“找到一个不匹配给定分区集的行”。有关更多信息和示例，请参见第 26.5 节，“分区选择”。

并发考虑事项

使用LOW_PRIORITY修饰符，LOAD DATA 语句的执行将延迟，直到没有其他客户端从表中读取数据。这仅影响仅使用表级锁定的存储引擎（如MyISAM、MEMORY和MERGE）。

使用CONCURRENT修饰符和满足并发插入条件的MyISAM表（即中间不包含空闲块），其他线程可以在LOAD DATA 执行时从表中检索数据。即使没有其他线程同时使用表，此修饰符也会稍微影响LOAD DATA 的性能。

语句结果信息

当LOAD DATA 语句完成时，将以以下格式返回信息字符串：

Records: 1  Deleted: 0  Skipped: 0  Warnings: 0

警告发生的情况与使用INSERT语句插入值时相同（参见 Section 15.2.7, “INSERT Statement”），只是当输入行中字段过少或过多时，LOAD DATA也会生成警告。

你可以使用SHOW WARNINGS获取前max_error_count个警告的列表，以了解出了什么问题。请参见 Section 15.7.7.42, “SHOW WARNINGS Statement”。

如果你正在使用 C API，可以通过调用mysql_info()函数获取有关语句的信息。请参见 mysql_info()。

复制注意事项

LOAD DATA被认为在基于语句的复制中是不安全的。如果你在binlog_format=STATEMENT下使用LOAD DATA，每个要应用更改的副本都会创建一个包含数据的临时文件。即使源端启用了二进制日志加密，这个临时文件也不会被加密。如果需要加密，应该使用基于行或混合的二进制日志格式，副本不会创建临时文件。关于LOAD DATA和复制之间的交互更多信息，请参见 Section 19.5.1.19, “Replication and LOAD DATA”。

杂项主题

在 Unix 上，如果需要LOAD DATA从管道中读取数据，可以使用以下技术（示例将/目录的列表加载到表db1.t1中）：

mkfifo /mysql/data/db1/ls.dat
chmod 666 /mysql/data/db1/ls.dat
find / -ls > /mysql/data/db1/ls.dat &
mysql -e "LOAD DATA INFILE 'ls.dat' INTO TABLE t1" db1

在这里，你必须在生成要加载的数据的命令和mysql命令上分别运行在不同的终端，或者在后台运行数据生成过程（如前面的示例所示）。如果不这样做，管道会一直阻塞，直到mysql进程读取数据。

15.2.10 LOAD XML Statement

原文：dev.mysql.com/doc/refman/8.0/en/load-xml.html

LOAD XML
    [LOW_PRIORITY | CONCURRENT] [LOCAL]
    INFILE '*file_name*'
    [REPLACE | IGNORE]
    INTO TABLE [*db_name*.]*tbl_name*
    [CHARACTER SET *charset_name*]
    [ROWS IDENTIFIED BY '<*tagname*>']
    [IGNORE *number* {LINES | ROWS}]
    [(*field_name_or_user_var*
        [, *field_name_or_user_var*] ...)]
    [SET *col_name*={*expr* | DEFAULT}
        [, *col_name*={*expr* | DEFAULT}] ...]

LOAD XML语句将数据从 XML 文件读取到表中。file_name必须作为文字字符串给出。可选的ROWS IDENTIFIED BY子句中的tagname也必须作为文字字符串给出，并且必须用尖括号（<和>）括起来。

LOAD XML作为运行mysql客户端以 XML 输出模式（即使用--xml选项启动客户端）的补充。要将表中数据写入 XML 文件，可以从系统 shell 中调用带有--xml和-e选项的mysql客户端，如下所示：

$> mysql --xml -e 'SELECT * FROM mydb.mytable' > file.xml

要将文件读取回表中，请使用LOAD XML。默认情况下，<row>元素被视为数据库表行的等价物；可以使用ROWS IDENTIFIED BY子句进行更改。

此语句支持三种不同的 XML 格式：

列名作为属性，列值作为属性值：

<*row* *column1*="*value1*" *column2*="*value2*" .../>

列名作为标签，列值作为这些标签的内容：

<*row*>
  <*column1*>*value1*</*column1*>
  <*column2*>*value2*</*column2*>
</*row*>

列名是<field>标签的name属性，而值是这些标签的内容：
```
<row>
  <field name='*column1*'>*value1*</field>
  <field name='*column2*'>*value2*</field>
</row>
```
这是其他 MySQL 工具（如mysqldump）使用的格式。

所有三种格式可以在同一个 XML 文件中使用；导入程序会自动检测每一行的格式并正确解释。标签是根据标签或属性名和列名进行匹配的。

在 MySQL 8.0.21 之前，LOAD XML不支持源 XML 中的CDATA部分。（Bug #30753708，Bug #98199）

以下子句对于LOAD XML与对于LOAD DATA基本上是相同的方式工作：

LOW_PRIORITY或CONCURRENT
LOCAL
REPLACE或IGNORE
CHARACTER SET
SET

更多关于这些子句的信息，请参见第 15.2.9 节，“LOAD DATA Statement”。

(*field_name_or_user_var*, ...)是一个由一个或多个逗号分隔的 XML 字段或用户变量列表。用于此目的的用户变量的名称必须与 XML 文件中的字段名称匹配，并以@为前缀。您可以使用字段名称仅选择所需的字段。用户变量可用于存储相应的字段值以供后续重用。

IGNORE *number* LINES或IGNORE *number* ROWS子句导致 XML 文件中的前number行被跳过。这类似于LOAD DATA语句的IGNORE ... LINES子句。

假设我们有一个名为person的表，如下所示创建：

USE test;

CREATE TABLE person (
    person_id INT NOT NULL PRIMARY KEY,
    fname VARCHAR(40) NULL,
    lname VARCHAR(40) NULL,
    created TIMESTAMP
);

进一步假设此表最初为空。

现在假设我们有一个简单的 XML 文件person.xml，其内容如下所示：

<list>
  <person person_id="1" fname="Kapek" lname="Sainnouine"/>
  <person person_id="2" fname="Sajon" lname="Rondela"/>
  <person person_id="3"><fname>Likame</fname><lname>Örrtmons</lname></person>
  <person person_id="4"><fname>Slar</fname><lname>Manlanth</lname></person>
  <person><field name="person_id">5</field><field name="fname">Stoma</field>
    <field name="lname">Milu</field></person>
  <person><field name="person_id">6</field><field name="fname">Nirtam</field>
    <field name="lname">Sklöd</field></person>
  <person person_id="7"><fname>Sungam</fname><lname>Dulbåd</lname></person>
  <person person_id="8" fname="Sraref" lname="Encmelt"/>
</list>

在此示例文件中表示了先前讨论的每种允许的 XML 格式。

要将person.xml中的数据导入person表中，可以使用以下语句：

mysql> LOAD XML LOCAL INFILE 'person.xml'
 ->   INTO TABLE person
 ->   ROWS IDENTIFIED BY '<person>';

Query OK, 8 rows affected (0.00 sec)
Records: 8  Deleted: 0  Skipped: 0  Warnings: 0

在这里，我们假设person.xml位于 MySQL 数据目录中。如果找不到文件，将出现以下错误：

ERROR 2 (HY000): File '/person.xml' not found (Errcode: 2)

ROWS IDENTIFIED BY '<person>'子句意味着 XML 文件中的每个<person>元素被视为要导入数据的表中的一行。在这种情况下，这是test数据库中的person表。

从服务器的响应可以看出，有 8 行被导入到test.person表中。可以通过简单的SELECT语句进行验证：

mysql> SELECT * FROM person;
+-----------+--------+------------+---------------------+
| person_id | fname  | lname      | created             |
+-----------+--------+------------+---------------------+
|         1 | Kapek  | Sainnouine | 2007-07-13 16:18:47 |
|         2 | Sajon  | Rondela    | 2007-07-13 16:18:47 |
|         3 | Likame | Örrtmons   | 2007-07-13 16:18:47 |
|         4 | Slar   | Manlanth   | 2007-07-13 16:18:47 |
|         5 | Stoma  | Nilu       | 2007-07-13 16:18:47 |
|         6 | Nirtam | Sklöd      | 2007-07-13 16:18:47 |
|         7 | Sungam | Dulbåd     | 2007-07-13 16:18:47 |
|         8 | Sreraf | Encmelt    | 2007-07-13 16:18:47 |
+-----------+--------+------------+---------------------+
8 rows in set (0.00 sec)

正如本节前面所述，任何或所有 3 种允许的 XML 格式都可以出现在单个文件中，并且可以使用LOAD XML进行读取。

刚刚展示的导入操作的反向操作——即将 MySQL 表数据转储到 XML 文件中——可以使用系统 shell 中的mysql客户端来完成，如下所示：

$> mysql --xml -e "SELECT * FROM test.person" > person-dump.xml
$> cat person-dump.xml
<?xml version="1.0"?>

<resultset statement="SELECT * FROM test.person" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <row>
	<field name="person_id">1</field>
	<field name="fname">Kapek</field>
	<field name="lname">Sainnouine</field>
  </row>

  <row>
	<field name="person_id">2</field>
	<field name="fname">Sajon</field>
	<field name="lname">Rondela</field>
  </row>

  <row>
	<field name="person_id">3</field>
	<field name="fname">Likema</field>
	<field name="lname">Örrtmons</field>
  </row>

  <row>
	<field name="person_id">4</field>
	<field name="fname">Slar</field>
	<field name="lname">Manlanth</field>
  </row>

  <row>
	<field name="person_id">5</field>
	<field name="fname">Stoma</field>
	<field name="lname">Nilu</field>
  </row>

  <row>
	<field name="person_id">6</field>
	<field name="fname">Nirtam</field>
	<field name="lname">Sklöd</field>
  </row>

  <row>
	<field name="person_id">7</field>
	<field name="fname">Sungam</field>
	<field name="lname">Dulbåd</field>
  </row>

  <row>
	<field name="person_id">8</field>
	<field name="fname">Sreraf</field>
	<field name="lname">Encmelt</field>
  </row>
</resultset>

注意

--xml选项使mysql客户端使用 XML 格式进行输出；-e选项使客户端执行紧随选项后的 SQL 语句。参见第 6.5.1 节，“mysql — The MySQL Command-Line Client”。

您可以通过创建person表的副本并将转储文件导入新表来验证转储是否有效，如下所示：

mysql> USE test;
mysql> CREATE TABLE person2 LIKE person;
Query OK, 0 rows affected (0.00 sec)

mysql> LOAD XML LOCAL INFILE 'person-dump.xml'
 ->   INTO TABLE person2;
Query OK, 8 rows affected (0.01 sec)
Records: 8  Deleted: 0  Skipped: 0  Warnings: 0

mysql> SELECT * FROM person2;
+-----------+--------+------------+---------------------+
| person_id | fname  | lname      | created             |
+-----------+--------+------------+---------------------+
|         1 | Kapek  | Sainnouine | 2007-07-13 16:18:47 |
|         2 | Sajon  | Rondela    | 2007-07-13 16:18:47 |
|         3 | Likema | Örrtmons   | 2007-07-13 16:18:47 |
|         4 | Slar   | Manlanth   | 2007-07-13 16:18:47 |
|         5 | Stoma  | Nilu       | 2007-07-13 16:18:47 |
|         6 | Nirtam | Sklöd      | 2007-07-13 16:18:47 |
|         7 | Sungam | Dulbåd     | 2007-07-13 16:18:47 |
|         8 | Sreraf | Encmelt    | 2007-07-13 16:18:47 |
+-----------+--------+------------+---------------------+
8 rows in set (0.00 sec)

XML 文件中的每个字段都不必与相应表中的列匹配。没有相应列的字段将被跳过。您可以通过首先清空person2表并删除created列，然后使用我们之前使用的相同的LOAD XML语句来查看这一点，如下所示：

mysql> TRUNCATE person2;
Query OK, 8 rows affected (0.26 sec)

mysql> ALTER TABLE person2 DROP COLUMN created;
Query OK, 0 rows affected (0.52 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW CREATE TABLE person2\G
*************************** 1\. row ***************************
       Table: person2
Create Table: CREATE TABLE `person2` (
  `person_id` int NOT NULL,
  `fname` varchar(40) DEFAULT NULL,
  `lname` varchar(40) DEFAULT NULL,
  PRIMARY KEY (`person_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 1 row in set (0.00 sec)

mysql> LOAD XML LOCAL INFILE 'person-dump.xml'
 ->   INTO TABLE person2;
Query OK, 8 rows affected (0.01 sec)
Records: 8  Deleted: 0  Skipped: 0  Warnings: 0

mysql> SELECT * FROM person2;
+-----------+--------+------------+
| person_id | fname  | lname      |
+-----------+--------+------------+
|         1 | Kapek  | Sainnouine |
|         2 | Sajon  | Rondela    |
|         3 | Likema | Örrtmons   |
|         4 | Slar   | Manlanth   |
|         5 | Stoma  | Nilu       |
|         6 | Nirtam | Sklöd      |
|         7 | Sungam | Dulbåd     |
|         8 | Sreraf | Encmelt    |
+-----------+--------+------------+
8 rows in set (0.00 sec)

XML 文件中每行中给定字段的顺序不会影响LOAD XML的操作；字段顺序可以在行与行之间变化，并且不需要与表中相应列的顺序相同。

如前所述，你可以使用一个(*field_name_or_user_var*, ...)列表选择一个或多个 XML 字段（仅选择所需字段）或用户变量（存储相应字段值以供以后使用）。当你想要将数据从 XML 文件插入到表列中的名称与 XML 字段不匹配时，用户变量尤其有用。为了看到这是如何工作的，我们首先创建一个名为individual的表，其结构与person表相匹配，但列名不同：

mysql> CREATE TABLE individual (
 ->     individual_id INT NOT NULL PRIMARY KEY,
 ->     name1 VARCHAR(40) NULL,
 ->     name2 VARCHAR(40) NULL,
 ->     made TIMESTAMP
 -> );
Query OK, 0 rows affected (0.42 sec)

在这种情况下，你不能简单地直接将 XML 文件加载到表中，因为字段和列名不匹配：

mysql> LOAD XML INFILE '../bin/person-dump.xml' INTO TABLE test.individual;
ERROR 1263 (22004): Column set to default value; NULL supplied to NOT NULL column 'individual_id' at row 1

这是因为 MySQL 服务器会查找与目标表的列名匹配的字段名。你可以通过将字段值选择到用户变量中，然后使用SET将目标表的列设置为这些变量的值来解决这个问题。你可以在单个语句中执行这两个操作，如下所示：

mysql> LOAD XML INFILE '../bin/person-dump.xml'
 ->     INTO TABLE test.individual (@person_id, @fname, @lname, @created)
 ->     SET individual_id=@person_id, name1=@fname, name2=@lname, made=@created;
Query OK, 8 rows affected (0.05 sec)
Records: 8  Deleted: 0  Skipped: 0  Warnings: 0

mysql> SELECT * FROM individual;
+---------------+--------+------------+---------------------+
| individual_id | name1  | name2      | made                |
+---------------+--------+------------+---------------------+
|             1 | Kapek  | Sainnouine | 2007-07-13 16:18:47 |
|             2 | Sajon  | Rondela    | 2007-07-13 16:18:47 |
|             3 | Likema | Örrtmons   | 2007-07-13 16:18:47 |
|             4 | Slar   | Manlanth   | 2007-07-13 16:18:47 |
|             5 | Stoma  | Nilu       | 2007-07-13 16:18:47 |
|             6 | Nirtam | Sklöd      | 2007-07-13 16:18:47 |
|             7 | Sungam | Dulbåd     | 2007-07-13 16:18:47 |
|             8 | Srraf  | Encmelt    | 2007-07-13 16:18:47 |
+---------------+--------+------------+---------------------+
8 rows in set (0.00 sec)

用户变量的名称必须与 XML 文件中相应字段的名称匹配，并添加必需的@前缀以指示它们是变量。用户变量不需要按照相应字段的顺序列出或分配。

使用ROWS IDENTIFIED BY '<*tagname*>'子句，可以将来自相同 XML 文件的数据导入到具有不同定义的数据库表中。例如，假设你有一个名为address.xml的文件，其中包含以下 XML：

<?xml version="1.0"?>

<list>
  <person person_id="1">
    <fname>Robert</fname>
    <lname>Jones</lname>
    <address address_id="1" street="Mill Creek Road" zip="45365" city="Sidney"/>
    <address address_id="2" street="Main Street" zip="28681" city="Taylorsville"/>
  </person>

  <person person_id="2">
    <fname>Mary</fname>
    <lname>Smith</lname>
    <address address_id="3" street="River Road" zip="80239" city="Denver"/>
    <!-- <address address_id="4" street="North Street" zip="37920" city="Knoxville"/> -->
  </person>

</list>

在清空表中的所有现有记录并显示其结构后，你可以再次使用本节中先前定义的test.person表，如下所示：

mysql< TRUNCATE person;
Query OK, 0 rows affected (0.04 sec)

mysql< SHOW CREATE TABLE person\G
*************************** 1\. row ***************************
       Table: person
Create Table: CREATE TABLE `person` (
  `person_id` int(11) NOT NULL,
  `fname` varchar(40) DEFAULT NULL,
  `lname` varchar(40) DEFAULT NULL,
  `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`person_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 1 row in set (0.00 sec)

现在在test数据库中使用以下CREATE TABLE语句创建一个address表：

CREATE TABLE address (
    address_id INT NOT NULL PRIMARY KEY,
    person_id INT NULL,
    street VARCHAR(40) NULL,
    zip INT NULL,
    city VARCHAR(40) NULL,
    created TIMESTAMP
);

要将 XML 文件中的数据导入到person表中，请执行以下LOAD XML语句，该语句指定了行应由<person>元素指定，如下所示；

mysql> LOAD XML LOCAL INFILE 'address.xml'
 ->   INTO TABLE person
 ->   ROWS IDENTIFIED BY '<person>';
Query OK, 2 rows affected (0.00 sec)
Records: 2  Deleted: 0  Skipped: 0  Warnings: 0

你可以通过SELECT语句验证记录是否已导入：

mysql> SELECT * FROM person;
+-----------+--------+-------+---------------------+
| person_id | fname  | lname | created             |
+-----------+--------+-------+---------------------+
|         1 | Robert | Jones | 2007-07-24 17:37:06 |
|         2 | Mary   | Smith | 2007-07-24 17:37:06 |
+-----------+--------+-------+---------------------+
2 rows in set (0.00 sec)

由于 XML 文件中的<address>元素在person表中没有对应的列，因此它们被跳过。

要将<address>元素中的数据导入到address表中，请使用以下显示的LOAD XML语句：

mysql> LOAD XML LOCAL INFILE 'address.xml'
 ->   INTO TABLE address
 ->   ROWS IDENTIFIED BY '<address>';
Query OK, 3 rows affected (0.00 sec)
Records: 3  Deleted: 0  Skipped: 0  Warnings: 0

你可以通过类似于这样的SELECT语句查看导入的数据：

mysql> SELECT * FROM address;
+------------+-----------+-----------------+-------+--------------+---------------------+
| address_id | person_id | street          | zip   | city         | created             |
+------------+-----------+-----------------+-------+--------------+---------------------+
|          1 |         1 | Mill Creek Road | 45365 | Sidney       | 2007-07-24 17:37:37 |
|          2 |         1 | Main Street     | 28681 | Taylorsville | 2007-07-24 17:37:37 |
|          3 |         2 | River Road      | 80239 | Denver       | 2007-07-24 17:37:37 |
+------------+-----------+-----------------+-------+--------------+---------------------+
3 rows in set (0.00 sec)

被 XML 注释包围的<address>元素中的数据不会被导入。然而，由于address表中有一个person_id列，因此每个<address>元素的父级<person>元素的person_id属性值会被导入到address表中。

安全注意事项。 与 LOAD DATA 语句一样，从客户端主机到服务器主机的 XML 文件传输是由 MySQL 服务器发起的。理论上，可以构建一个经过修补的服务器，告诉客户端程序传输服务器选择的文件，而不是客户端在 LOAD XML 语句中命名的文件。这样的服务器可以访问客户端主机上客户端用户具有读取权限的任何文件。

在 Web 环境中，客户端通常从 Web 服务器连接到 MySQL。可以运行任何命令来针对 MySQL 服务器的用户可以使用 LOAD XML LOCAL 读取 Web 服务器进程具有读取权限的任何文件。在这种环境中，相对于 MySQL 服务器的客户端实际上是 Web 服务器，而不是连接到 Web 服务器的用户运行的远程程序。

您可以通过使用 --local-infile=0 或 --local-infile=OFF 启动服务器来禁用客户端加载 XML 文件。在启动 mysql 客户端时，也可以使用此选项禁用客户端会话期间的 LOAD XML。

要阻止客户端从服务器加载 XML 文件，不要向相应的 MySQL 用户帐户授予 FILE 权限，或者如果客户端用户帐户已经具有该权限，则撤销此权限。

重要

撤销 FILE 权限（或者一开始不授予它）只会阻止用户执行 LOAD XML 语句（以及 LOAD_FILE() 函数；它不阻止用户执行 LOAD XML LOCAL。要禁止此语句，必须使用 --local-infile=OFF 启动服务器或客户端。

换句话说，FILE 权限仅影响客户端是否可以读取服务器上的文件；它与客户端是否可以读取本地文件系统上的文件无关。

15.2.11 带括号的查询表达式

译文：dev.mysql.com/doc/refman/8.0/en/parenthesized-query-expressions.html

*parenthesized_query_expression*:
    ( *query_expression* [*order_by_clause*] [*limit_clause*] )
      [*order_by_clause*]
      [*limit_clause*]
      [*into_clause*]

*query_expression*:
    *query_block* [*set_op* *query_block* [*set_op* *query_block* ...]]
      [*order_by_clause*]
      [*limit_clause*]
      [*into_clause*]

*query_block*:
    SELECT ... | TABLE | VALUES

*order_by_clause*:
    ORDER BY as for SELECT

*limit_clause*:
    LIMIT as for SELECT

*into_clause*:
    INTO as for SELECT

*set_op*:
    UNION | INTERSECT | EXCEPT

MySQL 8.0.22 及更高版本支持根据前述语法的带括号的查询表达式。在其最简单形式下，带括号的查询表达式包含一个返回结果集的单个SELECT或其他语句，没有后续的可选子句：

(SELECT 1);
(SELECT * FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = 'mysql');

TABLE t;

VALUES ROW(2, 3, 4), ROW(1, -2, 3);

(支持从 MySQL 8.0.19 开始提供的TABLE和VALUES语句。)

一个带括号的查询表达式也可以包含通过一个或多个集合操作连接的查询，例如UNION，并以任意或所有可选子句结束：

mysql> (SELECT 1 AS result UNION SELECT 2);
+--------+
| result |
+--------+
|      1 |
|      2 |
+--------+
mysql> (SELECT 1 AS result UNION SELECT 2) LIMIT 1;
+--------+
| result |
+--------+
|      1 |
+--------+
mysql> (SELECT 1 AS result UNION SELECT 2) LIMIT 1 OFFSET 1;
+--------+
| result |
+--------+
|      2 |
+--------+
mysql> (SELECT 1 AS result UNION SELECT 2)
       ORDER BY result DESC LIMIT 1;
+--------+
| result |
+--------+
|      2 |
+--------+
mysql> (SELECT 1 AS result UNION SELECT 2)
       ORDER BY result DESC LIMIT 1 OFFSET 1;
+--------+
| result |
+--------+
|      1 |
+--------+
mysql> (SELECT 1 AS result UNION SELECT 3 UNION SELECT 2)
       ORDER BY result LIMIT 1 OFFSET 1 INTO @var;
mysql> SELECT @var;
+------+
| @var |
+------+
|    2 |
+------+

除了UNION之外，INTERSECT和EXCEPT集合操作符从 MySQL 8.0.31 开始提供。INTERSECT在UNION和EXCEPT之前起作用，因此以下两个语句是等效的：

SELECT a FROM t1 EXCEPT SELECT b FROM t2 INTERSECT SELECT c FROM t3;

SELECT a FROM t1 EXCEPT (SELECT b FROM t2 INTERSECT SELECT c FROM t3);

带括号的查询表达式也用作查询表达式，因此查询表达式通常由查询块组成，也可以由带括号的查询表达式组成：

(TABLE t1 ORDER BY a) UNION (TABLE t2 ORDER BY b) ORDER BY z;

查询块可以有尾随的ORDER BY和LIMIT子句，在外部集合操作、ORDER BY和LIMIT之前应用。

不能有带有尾随ORDER BY或LIMIT的查询块，而不将其包装在括号中，但可以以各种方式使用括号进行强制执行：

要在每个查询块上强制执行LIMIT：

(SELECT 1 LIMIT 1) UNION (VALUES ROW(2) LIMIT 1);

(VALUES ROW(1), ROW(2) LIMIT 2) EXCEPT (SELECT 2 LIMIT 1);

要在查询块和整个查询表达式上强制执行LIMIT：
```
(SELECT 1 LIMIT 1) UNION (SELECT 2 LIMIT 1) LIMIT 1;
```
要在整个查询表达式上强制执行LIMIT（不带括号）：
```
VALUES ROW(1), ROW(2) INTERSECT VALUES ROW(2), ROW(1) LIMIT 1;
```
混合强制：在第一个查询块和整个查询表达式上强制执行LIMIT：
```
(SELECT 1 LIMIT 1) UNION SELECT 2 LIMIT 1;
```

本节中描述的语法受到一定的限制：

如果括号内有另一个INTO子句，则不允许为查询表达式添加尾随的INTO子句。
在 MySQL 8.0.31 之前，当ORDER BY或LIMIT出现在带括号的查询表达式中并且也应用于外部查询时，结果是未定义的。在 MySQL 8.0.31 及更高版本中，这将按照 SQL 标准处理。

在 MySQL 8.0.31 之前，带括号的查询表达式不允许多层次的ORDER BY或LIMIT操作，并且包含这些操作的语句将被拒绝，并显示ER_NOT_SUPPORTED_YET。在 MySQL 8.0.31 及更高版本中，此限制已被取消，并允许嵌套的带括号的查询表达式。支持的最大嵌套级别为 63；这是在解析器执行任何简化或合并之后。

这里展示了这种语句的一个示例：
```
mysql> (SELECT 'a' UNION SELECT 'b' LIMIT 2) LIMIT 3;
+---+
| a |
+---+
| a |
| b |
+---+
2 rows in set (0.00 sec)
```
你应该注意，在 MySQL 8.0.31 及更高版本中，当折叠括号表达式体时，MySQL 遵循 SQL 标准语义，因此更高的外部限制不能覆盖更低的内部限制。例如，(SELECT ... LIMIT 5) LIMIT 10 最多只能返回五行。

15.2.12 REPLACE Statement

原文：dev.mysql.com/doc/refman/8.0/en/replace.html

REPLACE [LOW_PRIORITY | DELAYED]
    [INTO] *tbl_name*
    [PARTITION (*partition_name* [, *partition_name*] ...)]
    [(*col_name* [, *col_name*] ...)]
    { {VALUES | VALUE} (*value_list*) [, (*value_list*)] ...
      |
      VALUES *row_constructor_list*
    }

REPLACE [LOW_PRIORITY | DELAYED]
    [INTO] *tbl_name*
    [PARTITION (*partition_name* [, *partition_name*] ...)]
    SET *assignment_list*

REPLACE [LOW_PRIORITY | DELAYED]
    [INTO] *tbl_name*
    [PARTITION (*partition_name* [, *partition_name*] ...)]
    [(*col_name* [, *col_name*] ...)]
    {SELECT ... | TABLE *table_name*}

*value*:
    {*expr* | DEFAULT}

*value_list*:
    *value* [, *value*] ...

*row_constructor_list*:
    ROW(*value_list*)[, ROW(*value_list*)][, ...]

*assignment*:
    *col_name* = *value*

*assignment_list*:
    *assignment* [, *assignment*] ...

REPLACE的工作方式与INSERT完全相同，唯一的区别是，如果表中的旧行与PRIMARY KEY或UNIQUE索引的新行具有相同的值，则在插入新行之前会删除旧行。请参见第 15.2.7 节，“INSERT Statement”。

REPLACE是 MySQL 对 SQL 标准的扩展。它要么插入，要么删除并插入。对于另一个 MySQL 对标准 SQL 的扩展——要么插入，要么更新，请参见第 15.2.7.2 节，“INSERT ... ON DUPLICATE KEY UPDATE Statement”。

DELAYED插入和替换在 MySQL 5.6 中已弃用。在 MySQL 8.0 中，不再支持DELAYED。服务器会识别但忽略DELAYED关键字，将替换处理为非延迟替换，并生成一个ER_WARN_LEGACY_SYNTAX_CONVERTED警告：REPLACE DELAYED 不再受支持。该语句已转换为 REPLACE。DELAYED关键字计划在将来的版本中移除。

注意

REPLACE仅在表具有PRIMARY KEY或UNIQUE索引时才有意义。否则，它将等同于INSERT，因为没有索引可用于确定新行是否重复。

所有列的值都取自REPLACE语句中指定的值。任何缺失的列都将设置为它们的默认值，就像INSERT一样。您不能引用当前行的值并在新行中使用它们。如果您使用类似SET *col_name* = *col_name* + 1的赋值，右侧的列名引用将被视为DEFAULT(*col_name*)，因此该赋值等效于SET *col_name* = DEFAULT(*col_name*) + 1。

在 MySQL 8.0.19 及更高版本中，您可以使用VALUES ROW()指定REPLACE尝试插入的列值。

要使用REPLACE，您必须对表具有INSERT和DELETE权限。

如果一个生成的列被显式替换，唯一允许的值是DEFAULT。有关生成列的信息，请参见第 15.1.20.8 节，“CREATE TABLE and Generated Columns”。

REPLACE支持使用PARTITION子句显式选择分区，后面跟着逗号分隔的分区、子分区或两者名称列表。与INSERT一样，如果无法将新行插入这些分区或子分区中的任何一个，REPLACE语句将失败，并显示错误信息“Found a row not matching the given partition set”。有关更多信息和示例，请参见第 26.5 节，“分区选择”。

REPLACE语句返回一个计数，指示受影响的行数。这是删除和插入的行数之和。如果对于单行REPLACE，计数为 1，则插入了一行且未删除任何行。如果计数大于 1，则在插入新行之前删除了一个或多个旧行。如果表包含多个唯一索引，并且新行在不同唯一索引中重复值以替换一个以上的旧行是可能的。

受影响的行数计数使得很容易确定REPLACE是否仅添加了一行还是还替换了任何行：检查计数是否为 1（添加）或大于 1（替换）。

如果使用 C API，则可以使用mysql_affected_rows()函数获取受影响的行数计数。

不能在子查询中将新行替换到表中并从同一表中进行选择。

MySQL 使用以下算法进行REPLACE（以及LOAD DATA ... REPLACE）：

尝试将新行插入表中
当由于主键或唯一索引发生重复键错误而插入失败时：
1. 从表中删除具有重复键值的冲突行
2. 再次尝试将新行插入表中

在重复键错误的情况下，存储引擎可能将REPLACE作为更新而不是删除加插入来执行，但语义是相同的。除了存储引擎如何递增Handler_*xxx*状态变量可能有所不同外，没有其他用户可见的影响。

因为REPLACE ... SELECT语句的结果取决于从SELECT中的行的排序，而这种顺序并不能始终保证，所以在记录这些语句时，源和副本可能会发生分歧。因此，REPLACE ... SELECT语句被标记为不安全的基于语句的复制。当使用基于语句的模式时，这些语句在错误日志中产生警告，并在使用MIXED模式时以基于行的格式写入二进制日志。另请参阅 Section 19.2.1.1, “Advantages and Disadvantages of Statement-Based and Row-Based Replication”。

MySQL 8.0.19 及更高版本支持TABLE以及带有REPLACE的SELECT，就像它对INSERT一样。有关更多信息和示例，请参见 Section 15.2.7.1, “INSERT ... SELECT Statement”。

当修改一个现有的非分区表以适应分区，或者修改已经分区表的分区时，您可能考虑修改表的主键（参见 Section 26.6.1, “Partitioning Keys, Primary Keys, and Unique Keys”）。您应该意识到，如果这样做，REPLACE语句的结果可能会受到影响，就像您修改非分区表的主键时一样。考虑以下由CREATE TABLE语句创建的表：

CREATE TABLE test (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  data VARCHAR(64) DEFAULT NULL,
  ts TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id)
);

当我们创建这个表并在 mysql 客户端中运行所示的语句时，结果如下：

mysql> REPLACE INTO test VALUES (1, 'Old', '2014-08-20 18:47:00');
Query OK, 1 row affected (0.04 sec)

mysql> REPLACE INTO test VALUES (1, 'New', '2014-08-20 18:47:42');
Query OK, 2 rows affected (0.04 sec)

mysql> SELECT * FROM test;
+----+------+---------------------+
| id | data | ts                  |
+----+------+---------------------+
|  1 | New  | 2014-08-20 18:47:42 |
+----+------+---------------------+
1 row in set (0.00 sec)

现在我们创建一个几乎与第一个表相同的第二个表，唯一不同的是主键现在涵盖了 2 列，如下所示（加粗文本）：

CREATE TABLE test2 (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  data VARCHAR(64) DEFAULT NULL,
  ts TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  *PRIMARY KEY (id, ts)* );

当我们在test2上运行与原始test表相同的两个REPLACE语句时，我们得到不同的结果：

mysql> REPLACE INTO test2 VALUES (1, 'Old', '2014-08-20 18:47:00');
Query OK, 1 row affected (0.05 sec)

mysql> REPLACE INTO test2 VALUES (1, 'New', '2014-08-20 18:47:42');
Query OK, 1 row affected (0.06 sec)

mysql> SELECT * FROM test2;
+----+------+---------------------+
| id | data | ts                  |
+----+------+---------------------+
|  1 | Old  | 2014-08-20 18:47:00 |
|  1 | New  | 2014-08-20 18:47:42 |
+----+------+---------------------+
2 rows in set (0.00 sec)

这是因为在test2上运行时，id和ts列的值必须与现有行的值匹配才能替换行；否则，将插入一行。

15.2.13 SELECT Statement

原文：dev.mysql.com/doc/refman/8.0/en/select.html

15.2.13.1 SELECT ... INTO Statement

15.2.13.2 JOIN Clause

SELECT
    [ALL | DISTINCT | DISTINCTROW ]
    [HIGH_PRIORITY]
    [STRAIGHT_JOIN]
    [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
    [SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
    *select_expr* [, *select_expr*] ...
    [*into_option*]
    [FROM *table_references*
      [PARTITION *partition_list*]]
    [WHERE *where_condition*]
    [GROUP BY {*col_name* | *expr* | *position*}, ... [WITH ROLLUP]]
    [HAVING *where_condition*]
    [WINDOW *window_name* AS (*window_spec*)
        [, *window_name* AS (*window_spec*)] ...]
    [ORDER BY {*col_name* | *expr* | *position*}
      [ASC | DESC], ... [WITH ROLLUP]]
    [LIMIT {[*offset*,] *row_count* | *row_count* OFFSET *offset*}]
    [*into_option*]
    [FOR {UPDATE | SHARE}
        [OF *tbl_name* [, *tbl_name*] ...]
        [NOWAIT | SKIP LOCKED]
      | LOCK IN SHARE MODE]
    [*into_option*]

*into_option*: {
    INTO OUTFILE '*file_name*'
        [CHARACTER SET *charset_name*]
        *export_options*
  | INTO DUMPFILE '*file_name*'
  | INTO *var_name* [, *var_name*] ...
}

SELECT 用于检索从一个或多个表中选择的行，并且可以包括 UNION 操作和子查询。从 MySQL 8.0.31 开始，还支持 INTERSECT 和 EXCEPT 操作。UNION、INTERSECT 和 EXCEPT 运算符将在本节后面更详细地描述。另请参阅 Section 15.2.15, “Subqueries”。

SELECT 语句可以以 WITH") 子句开头，以定义在 SELECT 中可访问的常用表达式。请参阅 Section 15.2.20, “WITH (Common Table Expressions)”")。

SELECT 语句最常用的子句包括：

每个 select_expr 表示要检索的列。必须至少有一个 select_expr。
table_references 指示要检索行的表或表。其语法在 Section 15.2.13.2, “JOIN Clause” 中描述。
SELECT 支持使用 PARTITION 子句显式选择分区，后跟表名中的分区或子分区列表（或两者都有）在 table_reference 中（参见 Section 15.2.13.2, “JOIN Clause”）。在这种情况下，仅从列出的分区中选择行，忽略表的任何其他分区。有关更多信息和示例，请参阅 Section 26.5, “Partition Selection”。
如果给出 WHERE 子句，则指示行必须满足的条件或条件。where_condition 是一个表达式，对于要选择的每一行都会计算为 true。如果没有 WHERE 子句，则该语句选择所有行。

在 WHERE 表达式中，您可以使用 MySQL 支持的任何函数和运算符，但不能使用聚合（组）函数。请参阅 Section 11.5, “Expressions”，以及 Chapter 14, Functions and Operators。

SELECT 也可用于检索计算而不参考任何表的行。

例如：

mysql> SELECT 1 + 1;
 -> 2

在没有引用任何表的情况下，您可以指定 DUAL 作为虚拟表名：

mysql> SELECT 1 + 1 FROM DUAL;
 -> 2

DUAL纯粹是为那些要求所有SELECT语句应具有FROM和可能其他子句的人方便而设计的。MySQL 可能会忽略这些子句。如果没有引用表，则 MySQL 不需要FROM DUAL。

通常，必须按照语法描述中显示的顺序给出使用的子句。例如，HAVING子句必须在任何GROUP BY子句之后和任何ORDER BY子句之前。如果存在INTO子句，则可以出现在语法描述指示的任何位置，但在给定语句中只能出现一次，而不是在多个位置。有关INTO的更多信息，请参见第 15.2.13.1 节，“SELECT ... INTO 语句”。

select_expr项列表包括指示要检索哪些列的选择列表。项指定列或表达式，或可以使用*-简写：

仅由单个未限定的*组成的选择列表可以用作从所有表中选择所有列的简写：
```
SELECT * FROM t1 INNER JOIN t2 ...
```
*tbl_name*.* 可以用作从命名表中选择所有列的限定简写：
```
SELECT t1.*, t2.* FROM t1 INNER JOIN t2 ...
```
如果表具有不可见列，则*和*tbl_name*.* 不包括它们。要包括不可见列，必须明确引用它们。
在选择列表中与其他项目一起使用未限定的*可能会产生解析错误。例如：
```
SELECT id, * FROM t1
```
为避免此问题，请使用限定的*tbl_name*.* 引用：
```
SELECT id, t1.* FROM t1
```
在选择列表中为每个表使用限定的*tbl_name*.* 引用：
```
SELECT AVG(score), t1.* FROM t1 ...
```

以下列表提供了有关其他SELECT子句的其他信息：

可以使用AS *alias_name*为select_expr指定别名。别名用作表达式的列名，并且可以在GROUP BY、ORDER BY或HAVING子句中使用。例如：
```
SELECT CONCAT(last_name,', ',first_name) AS full_name
  FROM mytable ORDER BY full_name;
```
在使用标识符为select_expr指定别名时，AS关键字是可选的。前面的示例可以这样写：
```
SELECT CONCAT(last_name,', ',first_name) full_name
  FROM mytable ORDER BY full_name;
```
但是，由于AS是可选的，如果忘记在两个select_expr表达式之间加逗号，可能会出现一个微妙的问题：MySQL 将第二个解释为别名。例如，在以下语句中，columnb被视为别名：
```
SELECT columna columnb FROM mytable;
```
因此，养成在指定列别名时明确使用AS的习惯是一个好习惯。

在WHERE子句中不允许引用列别名，因为在执行WHERE子句时可能尚未确定列值。请参见第 B.3.4.4 节，“列别名问题”。
FROM *table_references* 子句表示要检索行的表或表。如果命名多个表，则正在执行连接。有关连接语法的信息，请参见第 15.2.13.2 节，“JOIN 子句”。对于每个指定的表，您可以选择指定别名。
```
*tbl_name* [[AS] *alias*] [*index_hint*]
```
使用索引提示可以为优化器提供有关在查询处理期间如何选择索引的信息。有关指定这些提示的语法的描述，请参见第 10.9.4 节，“索引提示”。

你可以使用SET max_seeks_for_key=*value*作为一种替代方法，强制 MySQL 优先选择键扫描而不是表扫描。参见第 7.1.8 节，“服务器系统变量”。
你可以将默认数据库中的表称为tbl_name，或者作为db_name.tbl_name来明确指定数据库。你可以将列称为col_name，tbl_name.col_name，或db_name.tbl_name.col_name。除非引用会产生歧义，否则不需要为列引用指定tbl_name或db_name.tbl_name前缀。参见第 11.2.2 节，“标识符限定符”，了解需要更明确的列引用形式的歧义示例。

可以使用*tbl_name* AS *alias_name*或tbl_name alias_name对表引用进行别名。以下语句是等效的：

SELECT t1.name, t2.salary FROM employee AS t1, info AS t2
  WHERE t1.name = t2.name;

SELECT t1.name, t2.salary FROM employee t1, info t2
  WHERE t1.name = t2.name;

选择输出的列可以在ORDER BY和GROUP BY子句中使用列名、列别名或列位置进行引用。列位置是整数，从 1 开始：
```
SELECT college, region, seed FROM tournament
  ORDER BY region, seed;

SELECT college, region AS r, seed AS s FROM tournament
  ORDER BY r, s;

SELECT college, region, seed FROM tournament
  ORDER BY 2, 3;
```
要按照倒序排序，将DESC（降序）关键字添加到ORDER BY子句中你要排序的列的名称中。默认是升序；可以使用ASC关键字明确指定。

如果ORDER BY出现在带括号的查询表达式中，并且也应用在外部查询中，结果是未定义的，并且可能在 MySQL 的将来版本中发生变化。

使用列位置已被弃用，因为该语法已从 SQL 标准中删除。
在 MySQL 8.0.13 之前，MySQL 支持了一个非标准语法扩展，允许为GROUP BY列使用显式的ASC或DESC标识符。MySQL 8.0.12 及更高版本支持带有分组函数的ORDER BY，因此不再需要使用此扩展。这也意味着在使用GROUP BY时可以对任意列进行排序，就像这样：
```
SELECT a, b, COUNT(c) AS t FROM test_table GROUP BY a,b ORDER BY a,t DESC;
```
截至 MySQL 8.0.13，不再支持GROUP BY扩展：不允许为GROUP BY列使用ASC或DESC标识符。
当你使用ORDER BY或GROUP BY对SELECT中的列进行排序时，服务器仅使用由max_sort_length系统变量指示的初始字节数对值进行排序。
MySQL 扩展了对GROUP BY的使用，允许选择未在GROUP BY子句中提及的字段。如果您的查询未获得预期结果，请阅读 Section 14.19，“Aggregate Functions”中关于GROUP BY的描述。
GROUP BY允许使用WITH ROLLUP修饰符。请参见 Section 14.19.2，“GROUP BY Modifiers”。

以前，在具有WITH ROLLUP修饰符的查询中不允许使用ORDER BY。从 MySQL 8.0.12 开始取消了此限制。请参见 Section 14.19.2，“GROUP BY Modifiers”。
HAVING子句与WHERE子句一样，指定选择条件。WHERE子句指定选择列表中的列的条件，但不能引用聚合函数。HAVING子句指定对由GROUP BY子句形成的组的条件。查询结果仅包括满足HAVING条件的组。（如果没有GROUP BY存在，则所有行隐式形成单个聚合组。）

HAVING子句几乎是在最后应用的，就在项目发送到客户端之前，没有优化。（LIMIT在HAVING之后应用。）

SQL 标准要求HAVING只能引用GROUP BY子句中的列或聚合函数中使用的列。然而，MySQL 支持对此行为的扩展，并允许HAVING引用SELECT列表中的列以及外部子查询中的列。

如果HAVING子句引用的列存在歧义，将发出警告。在以下语句中，col2存在歧义，因为它既用作别名又用作列名：
```
SELECT COUNT(col1) AS col2 FROM t GROUP BY col2 HAVING col2 = 2;
```
标准 SQL 行为优先，因此如果HAVING列名既在GROUP BY中使用，又作为选择列列表中的别名列，则优先使用GROUP BY列。

不要将应该在WHERE子句中的项目放在HAVING中。例如，不要写如下内容：

SELECT *col_name* FROM *tbl_name* HAVING *col_name* > 0;

改为写成：

SELECT *col_name* FROM *tbl_name* WHERE *col_name* > 0;

HAVING子句可以引用聚合函数，而WHERE子句不能：
```
SELECT user, MAX(salary) FROM users
  GROUP BY user HAVING MAX(salary) > 10;
```
（在某些较旧版本的 MySQL 中不起作用。）
MySQL 允许重复列名。也就是说，可以有多个具有相同名称的select_expr。这是对标准 SQL 的扩展。因为 MySQL 还允许GROUP BY和HAVING引用select_expr值，这可能导致歧义：
```
SELECT 12 AS a, a FROM t GROUP BY a;
```
在该语句中，两列都具有名称a。为确保正确使用列进行分组，请为每个select_expr使用不同的名称。
如果存在WINDOW子句，则定义了可以被窗口函数引用的命名窗口。详情请参见 Section 14.20.4，“Named Windows”。
MySQL 通过在ORDER BY子句中搜索select_expr值，然后在FROM子句中的表列中搜索来解析未限定的列或别名引用。对于GROUP BY或HAVING子句，它会先在FROM子句中搜索，然后再在select_expr值中搜索。（对于GROUP BY和HAVING，这与 MySQL 5.0 之前的行为不同，该行为使用与ORDER BY相同的规则。）
LIMIT子句可用于限制SELECT语句返回的行数。LIMIT接受一个或两个数字参数，这两个参数必须都是非负整数常量，但有以下例外：
- 在准备好的语句中，可以使用?占位符标记指定LIMIT参数。
- 在存储程序中，可以使用整数值例程参数或本地变量指定LIMIT参数。
使用两个参数时，第一个参数指定要返回的第一行的偏移量，第二个参数指定要返回的最大行数。初始行的偏移量为 0（而不是 1）：
```
SELECT * FROM tbl LIMIT 5,10;  # Retrieve rows 6-15
```
要检索从某个偏移量到结果集末尾的所有行，可以使用一个很大的数字作为第二个参数。以下语句检索从第 96 行到最后的所有行：
```
SELECT * FROM tbl LIMIT 95,18446744073709551615;
```
使用一个参数时，该值指定从结果集开头返回的行数：
```
SELECT * FROM tbl LIMIT 5;     # Retrieve first 5 rows
```
换句话说，LIMIT *row_count*等同于LIMIT 0, *row_count*。

对于准备好的语句，可以使用占位符。以下语句从tbl表中返回一行：
```
SET @a=1;
PREPARE STMT FROM 'SELECT * FROM tbl LIMIT ?';
EXECUTE STMT USING @a;
```
以下语句从tbl表中返回第二到第六行：
```
SET @skip=1; SET @numrows=5;
PREPARE STMT FROM 'SELECT * FROM tbl LIMIT ?, ?';
EXECUTE STMT USING @skip, @numrows;
```
为了与 PostgreSQL 兼容，MySQL 还支持LIMIT *row_count* OFFSET *offset*语法。

如果LIMIT出现在括号查询表达式中，并且也应用于外部查询，则结果是未定义的，并且可能在 MySQL 的将来版本中更改。
SELECT ... INTO形式的SELECT允许将查询结果写入文件或存储在变量中。更多信息，请参见 Section 15.2.13.1, “SELECT ... INTO Statement”。
如果在使用页面或行锁的存储引擎中使用FOR UPDATE，则查询检查的行将被写锁定，直到当前事务结束。

你不能在CREATE TABLE *new_table* SELECT ... FROM *old_table* ...等语句中将FOR UPDATE作为SELECT的一部分。（如果尝试这样做，将会收到错误消息“在创建'new_table'时无法更新表'old_table'。”）

FOR SHARE 和 LOCK IN SHARE MODE 设置共享锁，允许其他事务读取检查的行，但不允许更新或删除它们。 FOR SHARE 和 LOCK IN SHARE MODE 是等效的。但是，FOR SHARE，像 FOR UPDATE 一样，支持 NOWAIT，SKIP LOCKED 和 OF *tbl_name* 选项。 FOR SHARE 是 LOCK IN SHARE MODE 的替代，但 LOCK IN SHARE MODE 仍可用于向后兼容。

NOWAIT 会导致 FOR UPDATE 或 FOR SHARE 查询立即执行，如果由于另一个事务持有的锁而无法获得行锁，则返回错误。

SKIP LOCKED 会导致 FOR UPDATE 或 FOR SHARE 查询立即执行，从结果集中排除被另一个事务锁定的行。

NOWAIT 和 SKIP LOCKED 选项对基于语句的复制不安全。

注意

跳过被锁定行的查询会返回数据的不一致视图。因此，SKIP LOCKED 不适用于一般的事务工作。但是，当多个会话访问相同的类似队列的表时，可以使用它来避免锁争用。

OF *tbl_name* 适用于对指定表执行 FOR UPDATE 和 FOR SHARE 查询。例如：
```
SELECT * FROM t1, t2 FOR SHARE OF t1 FOR UPDATE OF t2;
```
当省略 OF *tbl_name* 时，查询块引用的所有表都会被锁定。因此，在不与另一个锁定子句结合使用 OF *tbl_name* 的情况下使用锁定子句会返回错误。在多个锁定子句中指定相同的表会返回错误。如果在 SELECT 语句中指定了别名作为表名，则锁定子句只能使用该别名。如果 SELECT 语句没有明确指定别名，则锁定子句只能指定实际表名。

有关 FOR UPDATE 和 FOR SHARE 的更多信息，请参见 Section 17.7.2.4, “Locking Reads”。有关 NOWAIT 和 SKIP LOCKED 选项的更多信息，请参见 Locking Read Concurrency with NOWAIT and SKIP LOCKED。

在 SELECT 关键字之后，您可以使用许多修饰符来影响语句的操作。 HIGH_PRIORITY，STRAIGHT_JOIN 和以 SQL_ 开头的修饰符是 MySQL 对标准 SQL 的扩展。

ALL 和 DISTINCT 修饰符指定是否应返回重复行。 ALL（默认）指定应返回所有匹配行，包括重复行。 DISTINCT 指定从结果集中删除重复行。指定两个修饰符是错误的。 DISTINCTROW 是 DISTINCT 的同义词。

在 MySQL 8.0.12 及更高版本中，DISTINCT 可以与使用 WITH ROLLUP 的查询一起使用。 (Bug #87450, Bug #26640100)
HIGH_PRIORITY 给予SELECT比更新表的语句更高的优先级。你应该只对非常快速且必须一次完成的查询使用这个选项。在表被锁定以供读取时发出的SELECT HIGH_PRIORITY查询即使有一个更新语句在等待表空闲也会运行。这只影响只使用表级锁定的存储引擎（如MyISAM、MEMORY和MERGE）。

HIGH_PRIORITY 不能与 SELECT 语句一起使用，这些语句是 UNION 的一部分。
STRAIGHT_JOIN 强制优化器按照 FROM 子句中列出的顺序连接表。如果优化器以非最佳顺序连接表，可以使用这个选项加快查询速度。STRAIGHT_JOIN 也可以在 table_references 列表中使用。参见第 15.2.13.2 节，“JOIN 子句”。

STRAIGHT_JOIN 不适用于优化器将其视为const或system表的任何表。这样的表产生一行，是在查询执行的优化阶段读取的，并且在查询执行继续之前，其列的引用被替换为适当的列值。这些表在EXPLAIN显示的查询计划中首先出现。参见第 10.8.1 节，“使用 EXPLAIN 优化查询”。这个例外可能不适用于在外连接的NULL补充侧使用的const或system表（即LEFT JOIN的右侧表或RIGHT JOIN的左侧表）。
SQL_BIG_RESULT 或 SQL_SMALL_RESULT 可以与 GROUP BY 或 DISTINCT 一起使用，告诉优化器结果集有很多行或很小，分别。对于 SQL_BIG_RESULT，如果创建了磁盘临时表，MySQL 直接使用它们，并倾向于对 GROUP BY 元素使用排序而不是使用带有键的临时表。对于 SQL_SMALL_RESULT，MySQL 使用内存临时表来存储结果表，而不是使用排序。这通常不需要。
SQL_BUFFER_RESULT 强制结果放入临时表中。这有助于 MySQL 提前释放表锁，并在向客户端发送结果集需要很长时间的情况下提供帮助。这个修饰符只能用于顶层SELECT语句，不能用于子查询或后续的UNION。
SQL_CALC_FOUND_ROWS 告诉 MySQL 计算结果集中会有多少行，忽略任何 LIMIT 子句。然后可以使用 SELECT FOUND_ROWS() 检索行数。参见第 14.15 节，“信息函数”。

注意

SQL_CALC_FOUND_ROWS 查询修饰符和配套的FOUND_ROWS()函数在 MySQL 8.0.17 中已被弃用；预计它们将在未来的 MySQL 版本中被移除。请查看FOUND_ROWS()的描述以获取有关替代策略的信息。
在 MySQL 8.0 之前，SQL_CACHE 和 SQL_NO_CACHE 修饰符与查询缓存一起使用。查询缓存在 MySQL 8.0 中被移除。SQL_CACHE 修饰符也被移除。SQL_NO_CACHE 已被弃用，并且没有效果；预计它将在未来的 MySQL 版本中被移除。

原文：dev.mysql.com/doc/refman/8.0/en/select-into.html

15.2.13.1 SELECT ... INTO Statement

SELECT ... INTO形式的SELECT允许将查询结果存储在变量中或写入文件：

SELECT ... INTO *var_list*选择列值并将其存储到变量中。
SELECT ... INTO OUTFILE将所选行写入文件。可以指定列和行终止符以生成特定的输出格式。
SELECT ... INTO DUMPFILE将一行数据写入文件，不进行任何格式化。

给定的SELECT语句最多可以包含一个INTO子句，尽管如SELECT语法描述所示（参见第 15.2.13 节，“SELECT Statement”），INTO可以出现在不同的位置：

在FROM之前。示例：
```
SELECT * INTO @myvar FROM t1;
```

在尾随锁定子句之前。示例：

SELECT * FROM t1 INTO @myvar FOR UPDATE;

在SELECT的末尾。示例：

SELECT * FROM t1 FOR UPDATE INTO @myvar;

在 MySQL 8.0.20 中支持语句末尾的INTO位置，并且是首选位置。在 MySQL 8.0.20 中，位于锁定子句之前的位置已被弃用；预计在未来的 MySQL 版本中将删除对其的支持。换句话说，INTO在FROM之后但不在SELECT的末尾会产生警告。

不应在嵌套的SELECT中使用INTO子句，因为这样的SELECT必须将其结果返回给外部上下文。在UNION语句中对INTO的使用也受到约束；请参见第 15.2.18 节，“UNION Clause”。

对于INTO *var_list*变体：

var_list命名一个或多个变量的列表，每个变量可以是用户定义的变量、存储过程或函数参数，或存储程序本地变量。（在准备的SELECT ... INTO *var_list*语句中，只允许使用用户定义的变量；请参见第 15.6.4.2 节，“本地变量范围和解析”。）
所选值被分配给变量。变量的数量必须与列的数量匹配。查询应返回一行数据。如果查询未返回任何行，则会出现带有错误代码 1329 的警告（No data），并且变量值保持不变。如果查询返回多行数据，则会出现错误 1172（Result consisted of more than one row）。如果可能语句可能检索多行数据，可以使用LIMIT 1将结果集限制为一行。
```
SELECT id, data INTO @x, @y FROM test.t1 LIMIT 1;
```

INTO *var_list*也可以与TABLE语句一起使用，但受到以下限制：

变量的数量必须与表中的列数相匹配。
如果表包含多行，则必须使用LIMIT 1将结果集限制为单行。LIMIT 1必须在INTO关键字之前。

这里显示了这种语句的一个示例：

TABLE employees ORDER BY lname DESC LIMIT 1
    INTO @id, @fname, @lname, @hired, @separated, @job_code, @store_id;

您还可以从生成单行的VALUES语句中选择值到一组用户变量中。在这种情况下，您必须使用表别名，并且必须将值列表中的每个值分配给一个变量。这里显示的两个语句中的每一个都等同于SET @x=2, @y=4, @z=8：

SELECT * FROM (VALUES ROW(2,4,8)) AS t INTO @x,@y,@z;

SELECT * FROM (VALUES ROW(2,4,8)) AS t(a,b,c) INTO @x,@y,@z;

用户变量名称不区分大小写。请参阅第 11.4 节，“用户定义变量”。

SELECT ... INTO OUTFILE '*file_name*'形式的SELECT将所选行写入文件。文件在服务器主机上创建，因此您必须具有FILE权限才能使用此语法。file_name不能是现有文件，这样可以防止修改文件，例如/etc/passwd和数据库表。character_set_filesystem系统变量控制文件名的解释。

SELECT ... INTO OUTFILE语句旨在使在服务器主机上将表转储到文本文件成为可能。要在其他主机上创建结果文件，通常使用SELECT ... INTO OUTFILE是不合适的，因为无法相对于服务器主机文件系统写入文件路径，除非可以使用服务器主机文件系统上的网络映射路径访问远程主机上文件的位置。

或者，如果远程主机上安装了 MySQL 客户端软件，您可以使用客户端命令，例如mysql -e "SELECT ..." > *file_name*在该主机上生成文件。

SELECT ... INTO OUTFILE是LOAD DATA的补充。列值被写入并转换为CHARACTER SET子句中指定的字符集。如果没有这样的子句，值将使用binary字符集进行转储。实际上，没有字符集转换。如果结果集包含几种字符集的列，则输出数据文件也是如此，可能无法正确重新加载文件。

语句中 export_options 部分的语法由与 LOAD DATA 语句一起使用的相同 FIELDS 和 LINES 子句组成。有关 FIELDS 和 LINES 子句的信息，包括它们的默认值和允许的值，请参阅 Section 15.2.9, “LOAD DATA Statement”。

FIELDS ESCAPED BY 控制如何写入特殊字符。如果 FIELDS ESCAPED BY 字符不为空，则在必要时用作前缀，避免输出时后续字符的歧义：

FIELDS ESCAPED BY 字符
FIELDS [OPTIONALLY] ENCLOSED BY 字符
FIELDS TERMINATED BY 和 LINES TERMINATED BY 值的第一个字符
ASCII NUL（零值字节；实际写入转义字符后的内容是 ASCII 0，而不是零值字节）

FIELDS TERMINATED BY、ENCLOSED BY、ESCAPED BY 或 LINES TERMINATED BY 字符必须转义，以便您可以可靠地读取文件。ASCII NUL 被转义，以便在某些分页器中更容易查看。

生成的文件不需要符合 SQL 语法，因此不需要转义其他内容。

如果 FIELDS ESCAPED BY 字符为空，则不会转义任何字符，NULL 输出为 NULL，而不是 \N。如果你的数据中的字段值包含刚才列出的字符之一，可能不是一个好主意指定一个空的转义字符。

当您想要将表的所有列转储到文本文件时，也可以使用 INTO OUTFILE 与 TABLE 语句。在这种情况下，可以使用 ORDER BY 和 LIMIT 控制排序和行数；这些子句必须在 INTO OUTFILE 之前。TABLE ... INTO OUTFILE 支持与 SELECT ... INTO OUTFILE 相同的 export_options，并且受到写入文件系统的相同限制。这里展示了这种语句的一个示例：

TABLE employees ORDER BY lname LIMIT 1000
    INTO OUTFILE '/tmp/employee_data_1.txt'
    FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"', ESCAPED BY '\'
    LINES TERMINATED BY '\n';

你也可以使用 SELECT ... INTO OUTFILE 与 VALUES 语句将值直接写入文件。这里有一个示例：

SELECT * FROM (VALUES ROW(1,2,3),ROW(4,5,6),ROW(7,8,9)) AS t
    INTO OUTFILE '/tmp/select-values.txt';

您必须使用表别名；列别名也受支持，并且可以选择性地用于仅从所需列中写入值。您还可以使用 SELECT ... INTO OUTFILE 支持的任何或所有导出选项来将输出格式化到文件中。

这里有一个示例，生成一个以逗号分隔值（CSV）格式的文件，许多程序使用这种格式：

SELECT a,b,a+b INTO OUTFILE '/tmp/result.txt'
  FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
  LINES TERMINATED BY '\n'
  FROM test_table;

如果使用 INTO DUMPFILE 而不是 INTO OUTFILE，MySQL 只会将一行写入文件，不会有任何列或行终止，并且不执行任何转义处理。这对于选择 BLOB 值并将其存储在文件中很有用。

TABLE也支持INTO DUMPFILE。如果表包含多行，则还必须使用LIMIT 1将输出限制为单行。 INTO DUMPFILE也可以与SELECT * FROM (VALUES ROW()[, ...]) AS *table_alias* [LIMIT 1]一起使用。请参阅 Section 15.2.19，“VALUES Statement”。

注意

由INTO OUTFILE或INTO DUMPFILE创建的任何文件都归属于运行mysqld的操作系统用户。（出于这个和其他原因，您绝对不应该以root身份运行mysqld。）从 MySQL 8.0.17 开始，文件创建的 umask 为 0640；您必须具有足够的访问权限来操作文件内容。在 MySQL 8.0.17 之前，umask 为 0666，文件可被服务器主机上的所有用户写入。

如果secure_file_priv系统变量设置为非空目录名称，则要写入的文件必须位于该目录中。

在由事件调度程序执行的事件的一部分中发生的SELECT ... INTO语句的上下文中，诊断消息（不仅是错误，还包括警告）将被写入错误日志，并且在 Windows 上，将被写入应用程序事件日志。有关更多信息，请参阅 Section 27.4.5，“事件调度程序状态”。

从 MySQL 8.0.22 开始，支持定期同步由SELECT INTO OUTFILE和SELECT INTO DUMPFILE写入的输出文件，通过设置在该版本中引入的select_into_disk_sync服务器系统变量来启用。可以使用select_into_buffer_size和select_into_disk_sync_delay分别设置输出缓冲区大小和可选延迟。有关更多信息，请参阅这些系统变量的描述。

posted @ 2024-06-23 00:40 绝不原创的飞龙阅读(10) 评论(0) 编辑收藏举报

刷新页面返回顶部

龙哥盟

掠夺·扩张·投机·博弈

MySQL8-中文参考-二十六-

MySQL8 中文参考（二十六）

15.1.20.9 二级索引和生成列

为提供 JSON 列索引而对生成列建立索引

NDB Cluster 中的 JSON 列和间接索引

15.1.20.10 不可见列

DDL 语句和不可见列

DML 语句和不可见列

不可见列元数据

二进制日志和不可见列

15.1.20.11 生成的隐式主键

15.1.20.12 设置 NDB 注释选项

NDB_COLUMN 选项

NDB_TABLE 选项

15.1.21 创建表空间语句

InnoDB 的考虑事项

NDB Cluster 的考虑事项

选项

注意

InnoDB 示例

NDB 示例

15.1.22 CREATE TRIGGER Statement

15.1.23 创建视图语句

15.1.24 DROP DATABASE 语句

15.1.25 删除事件语句

15.1.26 DROP FUNCTION 语句

15.1.27 DROP INDEX 语句

第 15.1.28 节 DROP LOGFILE GROUP 语句

15.1.29 DROP PROCEDURE and DROP FUNCTION Statements

15.1.30 DROP SERVER 语句

15.1.31 删除空间参考系统语句

15.1.32 DROP TABLE 语句

15.1.33 DROP TABLESPACE Statement

注意

InnoDB 示例

NDB 示例

15.1.34 DROP TRIGGER Statement

15.1.35 DROP VIEW Statement

15.1.36 RENAME TABLE Statement

15.1.37 截断表语句

15.2 数据操作语句

15.2.1 CALL 语句

15.2.2 DELETE 语句

单表语法

主要子句

多表语法

权限

性能

子查询

分区表支持

自增列

修饰符

删除顺序

InnoDB 表

MyISAM 表

多表删除

15.2.3 DO 语句

15.2.4 EXCEPT 子句

15.2.5 HANDLER 语句

15.2.6 IMPORT TABLE Statement

限制和限制条件

15.2.7 插入语句

15.2.7.1 INSERT ... SELECT Statement

15.2.7.2 INSERT ... ON DUPLICATE KEY UPDATE Statement

15.2.7.3 INSERT DELAYED Statement

15.2.8 INTERSECT 子句

15.2.9 LOAD DATA 语句

非 LOCAL 与 LOCAL 操作的区别

输入文件字符集

输入文件位置

安全要求

重复键和错误处理

索引处理

字段和行处理

列列表规范

输入预处理

列值分配

分区表支持

15.1.26 `DROP FUNCTION` 语句