MySQL8-中文参考-五十九-

布客飞龙
64 阅读1小时+

MySQL8 中文参考（五十九）

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

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）可能会有所帮助：

  1. 将不要删除的行选择到一个与原始表具有相同结构的空表中：

    INSERT INTO t_copy SELECT * FROM t WHERE ... ;

  2. 使用RENAME TABLE原子性地将原始表移开并将副本重命名为原始名称：

    RENAME TABLE t TO t_old, t_copy TO t;

  3. 删除原始表：

    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可能会导致索引中的浪费空间无法回收。以下是这种情况的一个示例：

  1. 创建一个包含带有索引的AUTO_INCREMENT列的表。

  2. 插入多行到表中。每次插入都会导致一个索引值被添加到索引的高端。

  3. 使用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系统变量命名的目录。

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

  1. 通过执行此语句锁定表以确保一致的快照，以防在导出过程中对其进行修改：

    mysql> FLUSH TABLES hr.employees, hr.managers WITH READ LOCK;

    在锁定期间，表仍然可以使用，但仅用于读取访问。

  2. 在文件系统级别，将.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

  3. 解锁表：

    mysql> UNLOCK TABLES;

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

  1. 导入模式必须存在。如有必要，执行此语句以创建它：

    mysql> CREATE SCHEMA hr;

  2. 在文件系统级别，将.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

  3. 通过执行一个命名了.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处理规则的描述稍后在本节中描述。

    字符转义序列
    \0ASCII NUL（X'00'）字符
    \b退格字符
    \n换行（换行）字符
    \r回车字符
    \t制表符。
    \ZASCII 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。如果您的数据中的字段值包含刚才列出的任何字符，可能不是一个好主意指定一个空的转义字符。

在某些情况下，字段和行处理选项会相互作用：

  • 如果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 权限仅影响客户端是否可以读取服务器上的文件；它与客户端是否可以读取本地文件系统上的文件无关。

avatar
文章
阅读
粉丝