数据处理语句

目录

 

CALL声明

DELETE 语法

DO 语法

HANDLER 语法

INSERT 语法

INSERT ... SELECT 语法

INSERT ... ON DUPLICATE KEY UPDATE 语法

INSERT DELAYED 语法

LOAD DATA 语法

LOAD XML 语法

REPLACE 语法

SELECT 语法

SELECT ... INTO语句

JOIN 语法

 UNION子句

子查询语法

子查询作为标量操作数

使用子查询进行比较

包含 ANY，IN 或 SOME 的子查询

包含 ALL 的子查询

行子查询

具有 EXISTS 或 NOT EXISTS 的子查询

相关子查询

派生表

子查询错误

优化子查询

将子查询重写为连接

子查询的限制

UPDATE 语法

---

CALL声明

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

 CALL 语句调用存储过程，在它定义之前，需要有CREATE PROCEDURE语句。

可以不带括号地调用不带参数的存储过程。也就是说，CALL p()并且 CALL p是等效的。

呼叫可以使用声明为OUT或INOUT参数的参数将值传回给调用者。

。当过程返回时，client 程序还可以获取在例程中执行的最终语句受影响的行数：在 SQL level 中，调用ROW_COUNT() function;从 C API，调用mysql_affected_rows() function。

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

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;

在调用该过程之前，通过INOUT参数初始化变量。调用该过程后，将设置或修改两个变量的值：

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

在准备好的CALL语句中一起使用PREPARE and 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 |
+------------------+------------+
| 5.7.20-debug-log |         11 |
+------------------+------------+

要编写使用CALLSQL语句执行产生结果集的存储过程的C程序， 必须启用该CLIENT_MULTI_RESULTS标志。这是因为CALL除了过程中执行的语句可能返回任何结果集之外，每个返回的结果还指示调用状态。如果CALL用于执行包含准备好的语句的任何存储过程，则还必须将CLIENT_MULTI_RESULTS启用。无法确定何时加载这样的过程，这些语句是否会产生结果集，因此有必要假设它们会产生结果集。

CLIENT_MULTI_RESULTS可以在您调用时启用mysql_real_connect()，可以通过传递CLIENT_MULTI_RESULTS 标志本身来显式地传递CLIENT_MULTI_STATEMENTS，也可以通过传递来隐式传递 （这也启用 CLIENT_MULTI_RESULTS）。 CLIENT_MULTI_RESULTS默认情况下启用。

要处理使用mysql_query()或mysql_real_query()执行的CALL 语句的结果，请使用 calls mysql_next_result()循环来确定是否有更多结果。

C 程序可以使用 prepared-statement 接口执行CALL  statements 并访问OUT和INOUT参数。这是通过使用 calls mysql_stmt_next_result()循环来处理CALL 语句的结果来确定是否有更多结果

检测到存储程序引用的 objects 的元数据更改，并在下次执行程序时自动重新分析受影响的语句。

DELETE 语法

Single-Table 语法

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

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

Main Clauses

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

where_condition是一个表达式，对于要删除的每一行，其值为true。

如果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]

特权

您需要 table 上的删除特权才能从中删除行。对于只读取的任何列，您只需要选择特权，例如WHERE子句中指定的列。

 Performance

当您不需要知道已删除行的数量时，TRUNCATE TABLE语句是一种更快的方法来清空 table 而不是没有WHERE子句的 DELETE语句。与 DELETE不同的是，不能在 transaction 中使用，或者如果你对 table 有锁定。

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

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

子查询

您无法从子查询中删除表和选择相同的表。

分区表支持

DELETE支持使用PARTITION选项进行显式分区选择，该选项获取一个或多个分区或子分区(或两者)的 comma-separated 名称列表，从中选择要删除的行。未包含在列表中的分区将被忽略。给定一个带有名为p0的分区的 table t，执行语句DELETE FROM t PARTITION (p0)对 table 执行ALTER TABLE t TRUNCATE PARTITION(p0)具有相同的效果;在这两种情况下，分区p0中的所有行都被删除。

PARTITION可以与WHERE条件一起使用，在这种情况下，仅对列出的分区中的行测试条件。For example, DELETE FROM t PARTITION (p0) WHERE c < 5仅删除条件c < 5为 true 的分区p0中的行;不检查任何其他分区中的行，因此不受DELETE的影响。

PARTITION选项也可以在 multiple-table DELETE statements 中使用。您可以在FROM选项中为每个 table 使用最多一个此类选项。

Auto-Increment 列

如果删除包含AUTO_INCREMENT列的最大 value 的行，则 value 不会重用于MyISAM或InnoDB table。如果在自动提交模式下使用DELETE FROM tbl_name(没有WHERE子句)删除 table 中的所有行，则除了InnoDB和MyISAM之外的所有存储引擎都会重新开始该序列。对于InnoDB表，这种行为有一些例外， as discussed in Section 14.6.1.6, “AUTO_INCREMENT Handling in InnoDB”.

对于MyISAM表，您可以在 multiple-column key 中指定AUTO_INCREMENT辅助列。在这种情况下，即使对于MyISAM表，也会重复使用从序列顶部删除的值。

修饰符

 DELETE 语句支持以下修饰符：

• 如果指定LOW_PRIORITY修饰符，则服务器会延迟执行删除，直到没有其他客户端从 table 读取。这仅影响仅使用 table-level 锁定的存储引擎(例如MyISAM，MEMORY和MERGE)。
• 对于MyISAM表，如果使用QUICK修饰符，则存储引擎在删除期间不合并索引离开，这可能会加速某些类型的删除操作。
• IGNORE修饰符使 MySQL 在删除行的 process 期间忽略错误。（在解析阶段遇到的错误将以通常的方式处理。）由于使用IGNORE而被忽略的错误 将作为警告返回。

删除顺序

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

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

ORDER BY还有助于删除 order 中为避免参照完整性违规所需的行。

InnoDB 表

如果要从大 table 中删除许多行，则可能会超出InnoDB table 的 lock table 大小。要避免此问题，或者只是为了最小化 table 保持锁定的 time，以下策略(根本不使用删除)可能会有所帮助：

• 选择不要删除的行到与原始 table 结构相同的空 table 中：

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

• 使用重命名 TABLE以原子方式移动原始 table 并将副本重命名为原始 name：

RENAME TABLE t TO t_old, t_copy TO t;

• 删除原始 table：

  DROP TABLE t_old;

在RENAME TABLE执行过程中，没有其他会话可以访问所涉及的表 ，因此重命名操作不会遇到并发问题。

Multi-Table 删除

您可以在删除语句中指定多个表，以根据WHERE子句中的条件从一个或多个表中删除行。你不能在 multiple-table DELETE中使用ORDER BY或LIMIT。

table_references子句列出连接中涉及的表，如第 13.2.9.2 节，“JOIN 语法”中所述。

对于第一个 multiple-table 语法，仅删除FROM子句之前列出的表中的匹配行。对于第二个 multiple-table 语法，仅删除FROM子句(USING子句之前)中列出的表中的匹配行。结果是，您可以在同一 time 时从多个表中删除行，并具有仅用于搜索的其他表：

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;

为了与Access的兼容性，在每个tbl_name后面使用.*语法是被允许的。

如果使用涉及InnoDB表的 multiple-table 删除语句，并且存在外部 key 约束，MySQL优化器可能按照不同于父/子关系的顺序处理表。在这种情况下，语句失败并回滚。相反，您应该从单个 table 中删除并依赖InnoDB提供的ON DELETE功能，以便相应地修改其他表。

注意
如果声明 table 的别名，则在引用 table 时必须使用别名：

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

在multiple-table 中的别名 DELETE，应该只在 table_references 部分的语句。在其他地方，允许别名引用，但不允许别名声明。

Correct:

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;

Incorrect:

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;

DO 语法

DO expr [, expr] ...

DO执行表达式但不返回任何结果。在大多数方面，DO是SELECT expr, ...的简写，但有一个优点，即当你不关心结果时它会稍快一些。

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

Example：此 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)

这对于存储的 function 或触发器中非常有用，这可以禁止语句生成结果集。

DO 只执行表达式。它不能用于可以使用SELECT的所有情况。例如，DO id FROM t1无效，因为它引用 table。

HANDLER 语法

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语句提供对 table 存储引擎接口的直接访问。它适用于InnoDB和MyISAM表。

HANDLER ... OPEN语句打开 table，使用后续的HANDLER ... READ statements 可以访问它。此 table object 不会被其他会话共享，并且在 session calls HANDLER ... CLOSE或 session 终止之前不会关闭。

如果使用别名打开表，则其他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接口来引用 table 的PRIMARY KEY，请使用带引号的标识符PRIMARY：

HANDLER tbl_name READ `PRIMARY` ...

第二个HANDLER ... READ语法从 index order 中的 table 中获取与WHERE条件匹配的行。

第三个HANDLER ... READ语法从自然行 order 中的 table 中获取与WHERE条件匹配的行。

当需要完整的 table 扫描时，它比HANDLER tbl_name READ index_name快。自然行顺序是在表数据文件中存储行的顺序。该语句也适用于表，但是没有这样的概念，因为没有单独的数据文件。

如果没有LIMIT子句，则所有形式的HANDLER ... READ都会获取单行(如果有的话)。要 return 特定行数，请包含LIMIT子句。它具有与SELECT语句相同的语法。

用HANDLER ... OPEN.打开的表，用HANDLER ... CLOSE关闭

使用HANDLER接口而不是普通的选择 statements 有几个原因：

HANDLER is faster than SELECT:

• HANDLER比 SELECT快：

1. 为HANDLER ... OPEN分配了一个指定的存储引擎处理程序对象。该对象可用于该表的后续HANDLER语句；它不需要为每个重新初始化。
2. 涉及的解析较少。
3. 没有优化程序或查询检查开销。
4. 处理程序接口不必提供一致的数据外观（例如， 允许 dirty reads），因此存储引擎可以使用SELECT通常不允许的优化。

• HANDLER可以更轻松地移植到使用类似底层ISAM接口的MySQL应用程序。
• HANDLER使您能够以SELECT难以（甚至不可能）完成的方式遍历数据库。当使用为数据库提供交互式用户界面的应用程序时，该 HANDLER接口是查看数据的更自然的方式。
• HANDLER是一个有点低级的陈述。例如，它不提供一致性。也就是说，HANDLER ... OPEN不会拍摄 table 的快照，也不会锁定 table。这意味着在发出HANDLER ... OPEN语句后，可以修改 table 数据(通过当前的 session 或其他会话)，这些修改可能仅部分可见,对与HANDLER ... NEXT或HANDLER ... PREV扫描。

可以关闭一个打开的处理程序并将其标记为重新打开，在这种情况下，处理程序将丢失其在表中的位置。当同时满足以下两种情况时，会发生这种情况：

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

TRUNCATE TABLE for a table closes all handlers for the table that were opened with HANDLER OPEN.

用HANDLER打开用 FLUSH TABLES tbl_name WITH READ LOCK刷新的表，则会隐式刷新处理程序并丢失其位置。

INSERT 语法

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)] ...
    [ON DUPLICATE KEY UPDATE assignment_list]

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
    [INTO] tbl_name
    [PARTITION (partition_name [, partition_name] ...)]
    SET assignment_list
    [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 ...
    [ON DUPLICATE KEY UPDATE assignment_list]

value:
    {expr | DEFAULT}

value_list:
    value [, value] ...

assignment:
    col_name = value

assignment_list:
    assignment [, assignment] ...

INSERT将新行插入到现有表中。 INSERT ... VALUES和 INSERT ... SET形式的语句基于显式指定的值插入行。 INSERT ... SELECT表单将插入从另一个表或多个表中选择的行。如果插入的行造成了 UNIQUE index or PRIMARY KEY.值重复，带有ON DUPLICATE KEY UPDATE子句的 INSERT 能使现有行更新。

有关INSERT ... SELECT和INSERT ... ON DUPLICATE KEY UPDATE的其他信息，请参阅第 13.2.5.1 节，“ INSERT ... SELECT 语法”和部分 13.2.5.2，“ INSERT ... ON DUPLICATE KEY UPDATE 语法”。

在 MySQL 5.7 中，DELAYED关键字被接受但被服务器忽略。

插入 table 需要 table 的插入特权。如果使用ON DUPLICATE KEY UPDATE子句并且重复的 key 导致执行UPDATE，则该语句需要UPDATE特权才能更新列。对于已读但未修改的列，您只需要选择特权(例如，对于仅在ON DUPLICATE KEY UPDATE子句中col_name = expr赋值的右侧引用的列)。、

当插入分区表时，您可以控制哪些分区和子分区接受新行。该 PARTITION选项采用表的一个或多个分区或子分区（或两者）的逗号分隔名称列表。如果给定INSERT语句要插入的任何行与列出的分区之一都不匹配，则该 INSERT语句将失败，并显示错误“ 找到与给定分区集不匹配的行”。

您可以使用REPLACE而不是 INSERT覆盖旧行。 与处理包含重复的旧行的唯一键值的新行REPLACE相对应 INSERT IGNORE：新行替换旧行而不是被丢弃。

tbl_name是应在其中插入行的表。指定该语句为其提供值的列，如下所示：

• tbl_name是应在其中插入行的表。指定该语句为其提供值的列，如下所示：
• 如果您没有为INSERT ... VALUES或INSERT ... SELECT指定列名列表， 则表VALUES或 SELECT语句必须提供表中每列的值。如果您不知道表中各列的顺序，请使用DESCRIBE tbl_name进行查找。 
• SET子句按名称显式指示列，，并与 value 一起指定每个列。

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

• 如果未启用严格SQL模式，则任何未明确指定值的列都将设置为其默认值（显式或隐式）。例如，如果您指定的列列表未命名表中的所有列，则未命名的列将设置为其默认值。如果启用了严格的SQL模式，INSERT语句没有为没有默认值的每个列指定一个显式值，则该语句将生成错误。
• 如果列列表和VALUES列表都为空，则INSERT创建一行并将每列设置为其默认值：

INSERT INTO tbl_name () VALUES();

如果未启用严格模式，则 MySQL 会对没有显式定义的默认值的任何列使用隐式默认值 value。如果启用了严格模式，则如果任何列没有默认 value，则会发生错误。

• 使用关键字DEFAULT将列明确设置为其默认值。这使写入INSERT为少数几个列分配值的语句更加容易 ，因为它使您避免编写不完整的VALUES列表，该列表不包含表中每个列的值。否则，您必须提供与列表中每个值相对应的列名VALUES 列表。
• 如果将生成的列显式插入，则唯一允许的值为DEFAULT。
• 在表达式中，您可以用 DEFAULT(col_name)来生成column col_name的默认值 。
• 如果表达式数据类型不匹配列数据类型，则可能会发生提供列 value 的表达式expr的类型转换。转换给定的 value 可能会导致不同的插入值，具体取决于列类型。例如，将所述字符串'1999.0e-2' 成INT， FLOAT， DECIMAL(10,6)或 YEAR列分别插入的值 1999，19.9921， 19.992100，或1999。存储在 INT和 YEAR列中的值为 1999因为字符串到数字的转换只看了字符串初始部分的多少，才算是有效的整数或年份。对于 FLOAT和 DECIMAL列，字符串到数字的转换将整个字符串视为有效的数值。
• 表达式expr可以引用先前在 value 列表中设置的任何列。例如，您可以执行此操作，因为col2的 value 指的是之前已分配的col1：

 

INSERT INTO tbl_name (col1,col2) VALUES(15,col1*2);

 

但是以下是不合法的，因为col1的 value 指的是col2，它是在col1之后分配的：

INSERT INTO tbl_name (col1,col2) VALUES(col2*2,15);

包含AUTO_INCREMENT值的列发生 exception。因为AUTO_INCREMENT值是在其他 value 赋值之后生成的，所以对赋值中AUTO_INCREMENT列的任何 reference 都会返回0。

INSERT使用 VALUES语法的语句可以插入多行。为此，请包括多个用逗号分隔的列值的列表，列表用括号括起来并用逗号分隔。例：

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在这种情况下是的同义词 。既不暗示值列表的数量，也不暗示每个列表的值的数量。无论是单个值列表还是多个列表，无论每个列表的值数量如何，都可以使用。

可以使用ROW_COUNT() SQL function 或mysql_affected_rows() C API function 获取插入的 affected-rows value。

如果您使用INSERT ... VALUES具有多个值列表或 INSERT ... SELECT的语句，该语句将以以下格式返回信息字符串：

Records: N1 Duplicates: N2 Warnings: N3

如果使用的是C API，则可以通过调用该mysql_info() 函数来获取信息字符串。

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

• 将NULL插入已声明为NOT NULL的列中。对于多行INSERT语句或多行 INSERT INTO ... SELECT 语句，该列设置为列数据类型的隐式默认值。这对于数字类型是0，对于 string 类型是空的 string('')，对于 date 和 time 类型是“零”value。 INSERT INTO ... SELECT statements 的处理方式与 multiple-row 插入相同，因为服务器不检查SELECT的结果集以查看它是否返回单行。 (对于 single-row 插入，当NULL插入NOT NULL列时不会发出警告。相反，语句失败并带有 error.)
• 将数字列设置为位于列范围之外的 value。 value 被剪切到范围的最近端点。
• 将值'10.34 a'分配给数字列。尾随的非数字文本被去除，其余的数字部分被插入。如果字符串值没有前导数字部分，则该列设置为 0。
• 将 string 插入 string 列(CHAR，VARCHAR，文本或BLOB)，超出列的最大长度。 value 被截断为列的最大长度。
• 将 value 插入 date 或 time 列，该列对于数据类型是非法的。该列设置为该类型的适当零值。
• 对于涉及AUTO_INCREMET列值的插入示例，

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

注意
这两个函数的行为并不总是相同。有关AUTO_INCREMENT列的插入 statements 的行为将在第 12.15 节，“信息功能”和第 27.8.7.38 节，“mysqlinsert_id()”中进一步讨论。

插入语句支持以下修饰符：

• 如果使用LOW_PRIORITY修饰符，则将INSERT延迟执行，直到没有其他客户端从表中读取为止。这包括在现有客户端正在阅读以及INSERT LOW_PRIORITY语句正在等待时开始阅读的其他客户端。因此，发出INSERT LOW_PRIORITY声明的客户可能要等待很长时间。LOW_PRIORITY affects only storage engines that use only table-level locking (such as MyISAM, MEMORY, and MERGE).

INSERT ... SELECT 语法

INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE]
    [INTO] tbl_name
    [PARTITION (partition_name [, partition_name] ...)]
    [(col_name [, col_name] ...)]
    SELECT ...
    [ON DUPLICATE KEY UPDATE assignment_list]

value:
    {expr | DEFAULT}

assignment:
    col_name = value

assignment_list:
    assignment [, assignment] ...

使用INSERT ... SELECT，您可以从选择语句的结果中快速插入多个行到 table，这可以从一个或多个表中选择。例如

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

以下条件适用于INSERT ... SELECT statements：

• 指定IGNORE以忽略会导致 duplicate-key 违规的行。
• INSERT语句 的目标表 可能出现在查询部分的FROM子句中 SELECT。但是，您不能插入表并在子查询中从同一表中选择。从同一表中进行选择并插入到同一表中时，MySQL创建一个内部临时表来保存来自的行SELECT，然后将这些行插入目标表中。但是，您不能使用 INSERT INTO t ... SELECT ... FROM twhen t是TEMPORARY 表，因为TEMPORARY不能在同一条语句中两次引用表。
• AUTO_INCREMENT列像往常一样工作。
• 为确保二进制 log 可用于 re-create 原始表，MySQL 不允许INSERT ... SELECT statements 的并发插入
• 当选择和插入引用相同的 table 时，为了避免模糊的列 reference 问题，为选择部分中使用的每个 table 提供唯一的别名，并使用适当的别名限定该部分中的列名。

INSERT ... ON DUPLICATE KEY UPDATE 语法

如果指定ON DUPLICATE KEY UPDATE子句，要插入的行将导致UNIQUE索引或PRIMARY KEY中出现重复的 value，则会出现旧行的UPDATE。

例如，如果将column a声明为UNIQUE并包含value 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 table，效果不相同，其中a是 auto-increment 列.对于 auto-increment 列，INSERT语句会增加 auto-increment value 但UPDATE不会.)

如果column 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部分 INSERT ... ON DUPLICATE KEY UPDATE语句引用列值 。换句话说， 在该子句中引用的是在没有重复键冲突的情况下将被插入的值。此功能在多行插入中特别有用。该 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;

对于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针对具有多个唯一或主键的表的语句也被标记为不安全。（缺陷＃11765650，错误＃58637）

分区 table 上的INSERT ... ON DUPLICATE KEY UPDATE使用存储引擎(如MyISAM 数据)使用 table-level 锁定会锁定 table 中更新分区 key 列的所有分区。 (对于使用InnoDB等采用 row-level locking的存储引擎的表，不会发生这种情况。）

INSERT DELAYED 语法

INSERT DELAYED ...

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

DELAYED在MySQL 5.6中不推荐使用insert和replaces。在MySQL 5.7中， DELAYED不受支持。服务器识别但忽略DELAYED关键字，将插入作为非延迟插入进行处理，并生成 ER_WARN_LEGACY_SYNTAX_CONVERTED警告（“ 不再支持INSERT DELAYED。该语句已转换为INSERT ”）。该DELAYED 关键字计划在将来的版本中删除。

LOAD DATA 语法

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语句以很高的速度将行从文本文件读入表中。 LOAD DATA是SELECT ... INTO OUTFILE的补充。要将数据从表写入文件，请使用 SELECT ... INTO OUTFILE。要将文件读回到表中，请使用 LOAD DATA。两个语句的FIELDSand LINES子句的语法 相同。FIELDS和LINES子句的语法对于两个 statements 都是相同的。

您也可以使用mysqlimport实用程序加载数据文件 。mysqlimport 通过向服务器发送一条LOAD DATA 语句来进行操作。

分区表支持

LOAD DATA使用PARTITION选项以逗号分隔的一个或多个分区，子分区或两者都有的分区列表，来支持显示使用分区选择。当使用此选项，如果文件中的任意一行不能插入任何一个分区或子分区列表，语句将失败，Found a row not matching the given partition set.

对于使用采用 table 锁的存储引擎(例如MyISAM 数据)的分区表， LOAD DATA 无法修剪任何分区锁。这不适用于使用采用 row-level 锁定的存储引擎的表，例如InnoDB。

输入文件名称，位置和内容解释

文件 name 必须以文字 string 的形式给出。在 Windows 上，将路径名中的反斜杠指定为正斜杠或加倍反斜杠。 character_setfilesystem系统变量控制文件 name 字符集的解释。

服务器使用character_set_database系统变量指示的字符集来解释文件中的信息。 SET NAMES和character_setclient的设置不影响输入的解释。如果输入文件的内容使用的字符集与默认值不同，通常最好使用CHARACTER SET子句指定文件的字符集。二进制字符集指定“不转换”。

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

Note

无法加载使用ucs2，utf16，utf16le或utf32字符集的数据 files

并发注意事项

如果使用LOW_PRIORITY修饰符，则会延迟执行LOAD DATA语句，直到没有其他 clients 从 table 读取。这只作用于使用 table-level 锁定的存储引擎(例如MyISAM，MEMORY和MERGE)。

如果使用满足并发插入条件的MyISAM table 指定CONCURRENT修饰符(即，它在中间不包含空闲块)，则其他线程可以在执行 LOAD DATA 时从 table 检索数据。即使没有其他线程同时使用该表，此修饰符也会有点影响LOAD DATA 的性能。

LOCAL修饰符会影响文件的预期位置和错误处理，如稍后所述。只有当您的服务器和 client 都已配置为允许时，LOCAL才有效。例如，如果在禁用local_infile系统变量的情况下启动mysqld，LOCAL将不起作用。

LOCAL修饰符会影响预期找到文件的位置：

• 如果指定了LOCAL，则 client host 上的 client 程序将读取该文件并将其发送到服务器。该文件可以作为完整路径 name 来指定，以指定其确切位置。如果以相对路径 name 的形式给出，name 将相对于启动 client 程序的目录进行解释。

将LOCAL与负载数据一起使用时，会在 MySQL 服务器存储临时 files 的目录中创建该文件的副本。此目录中的副本缺少足够的空间可能导致 LOAD DATA LOCAL语句失败。

• 如果未指定LOCAL，则该文件必须位于服务器 host 上，并由服务器直接读取。服务器使用以下规则来定位文件：

1. 如果文件 name 是绝对路径 name，则服务器将其用作给定的。
2. 如果文件 name 是具有一个或多个前导组件的相对路径 name，则服务器将搜索相对于服务器数据目录的文件。
3. 如果给出没有前导组件的文件 name，则服务器在缺省数据库的数据库目录中查找该文件。

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

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

非LOCAL加载操作读取位于服务器上的文本 files。出于安全原因，此类操作要求您具有文件权限。见第 6.2.2 节，“MySQL 提供的权限”。此外，非LOCAL加载操作受secure_file_priv系统变量设置的约束。如果变量 value 是非空目录 name，则要加载的文件必须位于该目录中。如果变量 value 为空(这是不安全的)，则该文件只需要服务器可读。

使用LOCAL比让服务器直接访问 files 要慢一些，因为文件内容必须由 client 通过连接发送到服务器。另一方面，您不需要 FILE 特权来加载本地 files。

LOCAL也会影响错误处理：

• 使用 LOAD DATA，data-interpretation 和 duplicate-key 错误会终止操作。
• 当 LOAD DATA LOCAL，data-interpretation 和 duplicate-key 错误成为警告并且操作继续，因为服务器无法在操作过程中停止传输文件。对于 duplicate-key 错误，这与指定IGNORE时相同。 IGNORE将在本节后面进一步解释。

Duplicate-Key 处理

REPLACE和IGNORE修饰符控制对唯一键值上的现有行进行复制的输入行的处理：

• 如果指定REPLACE，则输入行将替换现有行。换句话说，对于主 key 或唯一索引具有相同 value 的现有行。
• 如果指定IGNORE，复制唯一 key value 的现有行则会被丢弃。
• 如果未指定任一修饰符，则行为取决于是否指定了LOCAL修饰符。如果没有LOCAL，则在找到重复的 key value 时会发生错误，并且忽略剩余的文本文件。使用LOCAL时，默认行为与指定IGNORE时相同;这是因为服务器无法在操作过程中停止传输文件。

索引处理

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

如果在空MyISAM table 上使用 LOAD DATA，则所有非唯一索引都在单独的批处理中创建(与 REPAIR TABLE一样)。通常，当您有许多索引时，这会使LOAD DATA更快。在某些极端情况下，您可以在将文件加载到 table 之前使用 ALTER TABLE ... DISABLE KEYS 语句关闭他们，并在加载文件后使用 ALTER TABLE ... ENABLE KEYS 去re-create 重建索引以使你能更快的创建索引。

字段和 Line 处理

对于e LOAD DATA和SELECT ... INTO OUTFILE statements，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 ''

未完待续。。。。

LOAD XML 语法

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也必须以文字 string 的形式给出，并且必须用尖括号(<和>)包围。

LOAD XML充当在 XML 输出模式下运行MySQL client 的补充（即，使用--xml选项启动客户端 ）。要将数据从表写入XML文件，可以使用 system shell 中的--xml和-e选项调用MySQL client，如下所示

shell> mysql --xml -e 'SELECT * FROM mydb.mytable' > file.xml

要将文件读回到表中，请使用LOAD XML。

默认情况下，<row>元素是数据库 table 行的等价物;这可以使用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 文件中使用; import 例程自动检测每行的格式并正确解释。根据标签或属性名称和列名称来匹配标签。

以下子句对LOAD XML的工作方式与 LOAD DATA的工作方式基本相同：

• LOW_PRIORITY或CONCURRENT

• LOCAL

• REPLACE或IGNORE

• CHARACTER SET

• SET

(field_name_or_user_var, ...)是一个或多个逗号分隔的XML字段或用户变量的列表。用于此目的的用户变量名称必须与XML文件中以@开头的字段名称相匹配,您可以使用字段名称来仅选择所需的字段。可以使用用户变量来存储相应的字段值，以供后续重用。

IGNORE number LINES或IGNORE number ROWS子句导致跳过 XML 文件中的第一个number行。它类似于LOAD DATA语句的IGNORE ... LINES子句。

假设我们有一个名为person的 table，如下所示创建：

USE test;

CREATE TABLE person (
    person_id INT NOT NULL PRIMARY KEY,
    fname VARCHAR(40) NULL,
    lname VARCHAR(40) NULL,
    created TIMESTAMP
);

进一步假设这个 table 最初是空的。

现在假设我们有一个简单的 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 table 中，您可以使用以下语句：

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>元素被认为等同于 table 中要导入数据的行。在这种情况下，这是test数据库中的person table。

从服务器的响应中可以看出，8 行被导入到test.person table 中。这可以通过简单的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客户端来完成，如下所示：

shell> mysql --xml -e "SELECT * FROM test.person" > person-dump.xml
shell> 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语句。

可以通过创建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 table 并删除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(11) NOT NULL,
  `fname` varchar(40) DEFAULT NULL,
  `lname` varchar(40) DEFAULT NULL,
  PRIMARY KEY (`person_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
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的操作; 字段顺序在行与行之间可以不同，并且不需要与表中相应列的顺序相同。

如前所述，您可以使用一个或多个 XML 字段的(field_name_or_user_var, ...)列表(仅选择所需的字段)或用户变量(用于存储相应的字段值以供以后使用)。当您想要将 XML 文件中的数据插入到 table 列中时，用户变量尤其有用，这些列的名称不与 XML 字段的匹配。要查看其工作原理，我们首先创建一个名为individual的 table，其结构与person table 的结构相匹配，但其列的命名方式不同：

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 文件直接加载到 table 中，因为字段和列名称不匹配 ：

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设置目标表的列等于这些变量的值来解决此问题.可以在一个语句中执行这两个操作，如下所示：

ysql> 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>

在清除 table 中的所有现有记录然后显示其结构后，您可以再次使用本节前面定义的test.person table：

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=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

现在使用以下CREATE TABLE语句在test数据库中创建address table：

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 table，请执行以下 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 table 中没有对应的列，因此会跳过它们。

要将<address>元素中的数据导入address table，请使用此处显示的 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)

<address>不会导入XML注释中包含<address>的元素中的数据。但是，由于address table 中有person_id列，因此每个<address>的 parent <person>元素的person_id属性的 value 将导入address table。

安全注意事项。

与LOAD DATA语句一样，XML 文件从 client host 传输到服务器 host 是由 MySQL 服务器启动的。从理论上讲，可以构建一个打补丁的服务器，该服务器将告诉客户端程序传输服务器选择的文件，而不是LOAD XML语句中 client 命名的文件。这样的服务器可以访问客户端用户具有读取权限的客户端主机上的任何文件。

在Web环境中，客户端通常从Web服务器连接到MySQL。可以对 MySQL 服务器运行任何命令的用户可以使用LOAD XML LOCAL来读取 Web 服务器 process 具有读访问权限的任何 files。在这种环境中，关于MySQL服务器的客户端实际上是Web服务器，而不是由连接到Web服务器的用户运行的远程程序。

您可以通过使用--local-infile=0或--local-infile=OFF启动服务器来禁用 从客户端加载XML文件。在启动mysql客户端时，也可以LOAD XML在客户端会话期间禁用此选项。

REPLACE 语法

REPLACE [LOW_PRIORITY | DELAYED]
    [INTO] tbl_name
    [PARTITION (partition_name [, partition_name] ...)]
    [(col_name [, col_name] ...)]
    {VALUES | VALUE} (value_list) [, (value_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 ...

value:
    {expr | DEFAULT}

value_list:
    value [, value] ...

assignment:
    col_name = value

assignment_list:
    assignment [, assignment] ...

REPLACE 的工作方式与 INSERT完全相同，只是如果 table 中的旧行与PRIMARY KEY或UNIQUE索引的新行具有相同的 value，则在插入新行之前删除旧行。

REPLACE是 SQL 标准的 MySQL 扩展。它可以插入，删除和插入。MySQL 5.6 中不推荐使用DELAYED插入和替换。在 MySQL 5.7 中，不支持DELAYED。服务器识别但忽略DELAYED关键字，将替换作为非延迟替换处理，并生成ER_WARN_LEGACY_SYNTAX_CONVERTED警告。（“ 不再支持REPLACE DELAYED。该语句已转换为REPLACE。 ”）DELAYED 关键字将在以后的版本中删除。

注意

REPLACE仅当表具有PRIMARY KEY或 UNIQUE索引时才有意义。否则，它等同于INSERT，因为没有索引可用于确定新行是否重复另一行。

要使用REPLACE，您必须同时拥有表的INSERT和 DELETE特权。

如果显式替换了生成的列，则唯一允许的值为DEFAULT。

REPLACE支持使用PARTITION关键词进行显式分区选择，该关键词带有分区，子分区或两者的逗号分隔名称列表。与插入一样，如果无法将新行插入任何这些分区或子分区中，则该REPLACE语句将失败，并显示错误“ 找到与给定分区集不匹配的行”。

该REPLACE语句返回一个计数，以指示受影响的行数。这是删除和插入的行的总和。如果单行REPLACE的计数为1，则会插入一行 ，并且不会删除任何行。如果计数大于1，则在插入新行之前删除一个或多个旧行。如果表包含多个唯一索引并且新行复制了不同唯一索引中不同旧行的值，则单行可能会替换一个以上的旧行。

受影响的行数使您可以轻松确定是 REPLACE仅添加一行还是也替换了任何行：检查计数是否为1（添加）或更大（已替换）。

如果使用的是C API，则可以使用mysql_affected_rows()函数获取受影响的行数 。

您不能从子查询中 replace 表和select相同的表

MySQL对REPLACE（和 LOAD DATA ... REPLACE）使用以下算法 ：

• 尝试将新行插入表中

• 插入失败是因为主键或唯一索引发生重复键错误：

1. 从表中删除具有重复键值的冲突行

2. 再试一次将新行插入表中

在重复键错误的情况下，存储引擎可能会执行REPLACE更新操作而不是删除加插入操作，但是语义是相同的。除了存储引擎如何递增 Handler_xxx状态变量的可能差异之外，没有其他用户可见的效果 。

因为REPLACE ... SELECT 语句的结果取决于 SELECT中的行的顺序，并且不能始终保证此顺序，所以在记录这些语句时可能会使主服务器和从服务器发散。因此，REPLACE ... SELECT对于基于语句的复制， 语句被标记为不安全。使用基于语句的模式时，此类语句会在错误日志中产生警告，而使用 MIXED模式时，此类语句将使用基于行的格式写入二进制日志中。

修改未分区的现有表以容纳分区时，或修改已分区表的分区时，可以考虑更改表的主键（请参见 第22.6.1节“分区键，主键和唯一键”）。您应该意识到，这样做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)

现在我们创建第二个 table 几乎与第一个相同，除了主键现在覆盖了两列之外，如图所示（强调文字）：

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 table 相同的两个REPLACE statements 时，我们获得了不同的结果：

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列的值都必须与要替换的行的现有行的值匹配；否则，将插入一行。

REPLACE会影响使用了存储引擎的分区表（例如 MyISAM），采用表级锁锁定仅含有匹配REPLACE statement WHERE clause的行这些分区，只要没有表的列被更新; 否则整个表将被锁定。（对于InnoDB采用行级锁定的存储引擎，不会发生分区锁定。）

SELECT 语法

SELECT
    [ALL | DISTINCT | DISTINCTROW ]
      [HIGH_PRIORITY]
      [STRAIGHT_JOIN]
      [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
      [SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
    select_expr [, select_expr ...]
    [FROM table_references
      [PARTITION partition_list]
    [WHERE where_condition]
    [GROUP BY {col_name | expr | position}
      [ASC | DESC], ... [WITH ROLLUP]]
    [HAVING where_condition]
    [ORDER BY {col_name | expr | position}
      [ASC | DESC], ...]
    [LIMIT {[offset,] row_count | row_count OFFSET offset}]
    [PROCEDURE procedure_name(argument_list)]
    [INTO OUTFILE 'file_name'
        [CHARACTER SET charset_name]
        export_options
      | INTO DUMPFILE 'file_name'
      | INTO var_name [, var_name]]
    [FOR UPDATE | LOCK IN SHARE MODE]]

SELECT用于检索从一个或多个表中选择的行，并且可以包含 UNION 语句和子查询。

最常用的SELECT语句子句 如下：

• 每个select_expr表示您要检索的列。必须至少有一个 select_expr。
• table_references指示要从中检索行的一个或多个表。第13.2.9.2节“ JOIN子句”中描述了其语法。
• SELECT支持使用显式的分区选择，用PARTITION 接分区列表或子分区列表（或两者都有）后面跟一个table_reference中的表名

如果给出WHERE子句，则表示行必须满足要选择的条件。 where_condition是一个表达式，对于要选择的每一行，求值为 true。如果没有WHERE子句，该语句将选择所有行。

在WHERE表达式中，您可以使用 MySQL 支持的任何函数和运算符，但聚合(汇总)函数除外。

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子句可以如语法描述中所示或紧随 select_expr列表之后出现。

select_expr术语 列表包括指示要检索的列的选择列表。术语指定列或表达式，或者可以使用 *简略表示：

• 选择列表中仅有一个不合规的*可以用来简略表示从表中选择所有字段 ：

SELECT * FROM t1 INNER JOIN t2 ...

• tbl_name.*可以用来合规的简略表示选择表名中的所有列：

SELECT t1.*, t2.* FROM t1 INNER JOIN t2 ...

在 select 列表中使用不合格的*与其他项可能会产生解析错误。要避免此问题，请使用合格的tbl_name.* 参考：

SELECT AVG(score), t1.* FROM t1 ...

以下列表提供了有关其他SELECT子句的其他信息：

• 可以使用AS alias_name为select_expr提供别名。别名用作表达式的列 name，可以在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 执行子句时可能尚未确定列值。

• FROM table_references 子句指示要从一个或多个表中检索行。如果您命名多个表，则说明您正在执行联接。有关连接语法的信息，请参见第13.2.9.2节“ JOIN子句”。对于指定的每个表，您可以选择指定别名。

tbl_name [[AS] alias] [index_hint]

索引提示的使用为优化器提供了有关在查询处理期间如何选择索引的信息。有关指定这些提示的语法的描述，请参见 第8.9.4节“索引提示”。

您可以使用 SET max_seeks_for_key=value方式来强制MySQL选择键扫描而不是表扫描。请参见 第5.1.7节“服务器系统变量”。 SET max_seeks_for_key=value

您可以将默认数据库中的表称为 tbl_name，或者明确指定数据库db_name.tbl_name 。您可以描述字段为col_name，tbl_name.col_name, or db_name.tbl_name.col_name. 对于字段查询，你无需明确的指定 tbl_name or db_name.tbl_name前缀，除非这查询时模棱两可的。有关需要更明确的列引用形式的歧义示例，请参见第9.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;

要以相反的顺序进行排序，请在ORDER BY子句中你所要依据的排序字段名后面添加DESC (descending) 关键字 。默认为升序（ascending ）；可以使用ASC关键字明确指定。

如果ORDER BY发生在子查询中并且也应用于外部查询，则最外面 的ORDER BY优先。例如，以下语句的结果按降序而不是升序排序：

(SELECT ... ORDER BY a) ORDER BY a DESC;

不建议使用列位置，因为该语法已从SQL标准中删除。

• MySQL 扩展了GROUP BY子句，以便您也可以在子句中指定的列之后指定ASC和DESC。但是，不赞成使用此语法。要产生给定的排序顺序，请提供一个ORDER BY子句。
• 如果使用GROUP BY，则输出行将根据GROUP BY列进行排序，就像您对相同列具有ORDER BY一样。为了避免GROUP BY产生的排序开销，添加ORDER BY NULL：

SELECT a, COUNT(b) FROM test_table GROUP BY a ORDER BY NULL;

依赖于隐式GROUP BY排序(即，在没有ASC或DESC指示符的情况下排序)或GROUP BY的显式排序(即，通过对GROUP BY列使用显式ASC或DESC指示符)不推荐使用。要生成给定的排序 ，请提供ORDER BY子句。

• 使用ORDER BY或GROUP BY对中的列进行排序时 SELECT，服务器仅使用max_sort_length系统变量指示的初始字节数对值进行排序 。
• MySQL扩展了对GROUP BY的使用，以允许选择该GROUP BY条款中未提及的字段。如果没有从查询中获得期望的结果，请阅读GROUP BY在第12.20节“聚合（GROUP BY）函数”中找到 的描述 。
• GROUP BY允许使用WITH ROLLUP修饰符。请参见 第12.20.2节“按修饰符分组”。
• 该HAVING子句几乎是最后一次应用，即将项目发送到客户端之前，没有进行优化。（LIMIT在 HAVING之后应用。）

SQL 标准要求HAVING必须仅引用GROUP BY 子句字段或用于聚合函数中的字段，但是，MySQL 支持对此行为的扩展，并允许HAVING引用 SELECT 列表中的列和外部子查询中的列。

如果HAVING子句引用不明确的列，则会发出警告。在以下语句中，col2是不明确的，因为它既用作别名又用作列 name：

SELECT COUNT(col1) AS col2 FROM t GROUP BY col2 HAVING col2 = 2;

优先考虑标准 SQL 行为，如果一个HAVING column name 既用于GROUP BY 中也用于输出列表的别名字段中，则优先 考虑GROUP BY column中的字段。

• 请勿HAVING用于该WHERE条款中的项目。例如，不要写以下内容：

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;

在该语句中，两列都具有 name a。要确保使用正确的列进行分组，请为每个select_expr使用不同的名称。

• MySQL通过先搜索select_expr values，然后搜索 FROM子句表中的字段，来解决 ORDER BY 子句中的非限定字段和别名引用。对于GROUP BY或HAVING子句，它在搜索select_expr值之前先搜索FROM子句。（对于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 table 中返回第二行到第六行：

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出现在子查询中并且也应用于外部查询中，则最外面的LIMIT优先。例如，以下语句产生两行，而不是一行：

(SELECT ... LIMIT 1) LIMIT 2;

• PROCEDURE子句指定应该处理结果集中数据的过程。有关示例，请参见 第8.4.2.4节“使用过程分析”，该过程可用于获取有关最佳列数据类型的建议，以帮助减小表的大小。

 UNION语句中不允许使用 PROCEDURE子句。

注意

PROCEDURE 自MySQL 5.7.18起不推荐使用该语法，并已在MySQL 8.0中删除。

• SELECT 的SELECT ... INTO形式使查询结果可以写入文件或存储在变量中。有关更多信息，请参见 第13.2.9.1节“ SELECT ... INTO语句”。
• 如果FOR UPDATE与使用页锁或行锁的存储引擎一起使用，则查询检查的行将被写锁，直到当前事务结束为止。使用 LOCK IN SHARE MODE设置共享锁，该锁允许其他事务读取已检查的行，但不能更新或删除它们。请参见 第14.7.2.4节“锁定读取”。

此外，您不能将FOR UPDATE用作SELECT语句的一部分 。比如CREATE TABLE new_table SELECT ... FROM old_table .... （如果尝试这样做，则该语句将被拒绝，并显示错误 “ Can't update table 'old_table' while 'new_table' is being created.' '。），这在5.5之前是被允许的。

在SELECT关键字之后，可以使用许多影响语句操作的修饰符。HIGH_PRIORITY，STRAIGHT_JOIN和以SQL_开头的修饰符是标准 SQL 的 MySQL extensions。

修饰符略。。。。。

SELECT ... INTO语句

SELECT的SELECT ... INTO形式 使查询结果存储在变量或将其写入文件：

• SELECT ... INTO var_list 选择列值并将其存储到变量中。

• SELECT ... INTO OUTFILE将选定的行写入文件。可以指定列和行终止符以产生特定的输出格式。

• SELECT ... INTO DUMPFILE 将单行写入文件而不进行任何格式化。

SELECT语法描述(请参阅第 13.2.9 节，“ SELECT 语法”)显示语句末尾附近的INTO子句。也可以在select_expr列表后紧跟INTO使用。

不应在嵌套的选择中使用INTO子句，因为这样一个 SELECT必须将其结果返回到外部环境。

该INTO子句可以命名一个或多个变量的列表，这些变量可以是用户定义的变量，存储过程或函数参数或存储的程序局部变量。（在准备好的SELECT ... INTO OUTFILE语句中，仅允许用户定义变量；请参见第13.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;

用户变量名称不区分大小写。请参见 第9.4节“用户定义的变量”。

所述形式的选择的行写入到一个文件中。该文件是在服务器主机上创建的，因此您必须具有使用此语法的特权。file_name不能是现有文件，除其他事情外，它可以防止文件（如/etc/passwd和数据库表）被破坏。character_set_filesystem 系统变量控制文件名的解析。

该SELECT ... INTO OUTFILE语句的主要目的是让您非常快速地将表转储到服务器计算机上的文本文件中。如果要在服务器主机以外的其他主机上创建结果文件，则通常无法使用， SELECT ... INTO OUTFILE因为无法相对于服务器主机的文件系统写入该文件的路径。

但是，如果远程计算机上安装了MySQL客户端软件，则可以改用client命令(如mysql -e "SELECT ..." > file_name) 在客户端主机上生成文件。

如果可以使用服务器文件系统上网络映射的路径访问远程主机上文件的位置，则也可以在服务器主机以外的其他主机上创建结果文件。在这种情况下，目标主机上不需要存在 mysql（或其他MySQL客户端程序）。

SELECT ... INTO OUTFILE是LOAD DATA的补充。将列值转换为该CHARACTER SET 子句中指定的字符集。如果不存在此类子句，则使用binary字符集转储值。实际上，没有字符集转换。如果结果集包含多个字符集中的列，则输出数据文件也会一样，并且您可能无法正确地重新加载文件。

语法语句的export_options 部分包含了相同的 FIELDS and LINES 子句，它可以和 LOAD DATA 语句一起使用。有关FIELDS和LINES子句的信息，请参阅第 13.2.6 节，“LOAD DATA 语法”，包括其默认值和允许值。

FIELDS ESCAPED BY控制如何编写特殊字符。如果FIELDS ESCAPED BY 字符不为空，则在必要时使用该字符作为前缀，以避免出现歧义，在输出中先于以下字符：

• FIELDS ESCAPED BY字符

• FIELDS [OPTIONALLY] ENCLOSED BY字符

• FIELDS TERMINATED BY和LINES TERMINATED BY值的第一个字符

• ASCII NUL(zero-valued 字节;转义字符后面实际写的是 ASCII 0，而不是 zero-valued 字节)

必须对FIELDS TERMINATED BY，ENCLOSED BY，ESCAPED BY或LINES TERMINATED BY字符进行转义，以便您可以可靠地读回文件。ASCII NUL被转义以使其更易于在某些寻呼机上查看。

生成的文件不必符合 SQL 语法，因此不需要转义任何其他内容。

如果FIELDS ESCAPED BY字符为空，则不会转义任何字符并将NULL其输出为NULL，而不是\N。指定一个空的转义字符可能不是一个好主意，特别是如果数据中的字段值包含刚才给出的列表中的任何字符时，尤其如此。

这是一个示例，该示例以许多程序使用的逗号分隔值（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值存储在文件中，这将很有用。

注意
由INTO OUTFILE或INTO DUMPFILE创建的任何文件都可由服务器 host 上的所有用户写入。原因是MySQL服务器无法创建一个文件，该文件由运行该帐户的用户以外的任何人拥有。（你永远不应该为了这个用root运行mysqld 和一些其他原因）因此文件必须是 world-writable，以便你可以操纵它的内容。

如果secure_file_priv系统变量设置为非空目录 name，则要写入的文件必须位于该目录中。

JOIN 语法

MySQL 支持 SELECT 语句以及多表DELETE和 UPDATE语句的table_references部分的以下JOIN语法：

table_references:
    escaped_table_reference [, escaped_table_reference] ...

escaped_table_reference:
    table_reference
  | { OJ table_reference }

table_reference:
    table_factor
  | joined_table

table_factor:
    tbl_name [PARTITION (partition_names)]
        [[AS] alias] [index_hint_list]
  | table_subquery [AS] alias
  | ( table_references )

joined_table:
    table_reference [INNER | CROSS] JOIN table_factor [join_specification]
  | table_reference STRAIGHT_JOIN table_factor
  | table_reference STRAIGHT_JOIN table_factor ON search_condition
  | table_reference {LEFT|RIGHT} [OUTER] JOIN table_reference join_specification
  | table_reference NATURAL [{LEFT|RIGHT} [OUTER]] JOIN table_factor

join_specification:
    ON search_condition
  | USING (join_column_list)

join_column_list:
    column_name [, column_name] ...

index_hint_list:
    index_hint [, index_hint] ...

index_hint:
    USE {INDEX|KEY}
      [FOR {JOIN|ORDER BY|GROUP BY}] ([index_list])
  | {IGNORE|FORCE} {INDEX|KEY}
      [FOR {JOIN|ORDER BY|GROUP BY}] (index_list)

index_list:
    index_name [, index_name] ...

table reference 也称为连接表达式。

table reference(当它引用分区的 table 时)可能包含PARTITION选项，包括 comma-separated 分区，子分区或两者的列表。此选项在 table 的 name 之后，并在任何别名声明之前。此选项的作用是仅从列出的分区或子分区中选择行。将忽略列表中未命名的任何分区或子分区。有关更多信息和示例，请参阅第 22.5 节，“分区选择”。

与标准 SQL 相比，table_factor的语法在 MySQL 中得到了扩展。标准只接受table_reference，而不是一对括号内的列表。

如果将table_reference项目列表中的每个逗号都视为等效于内部联接，则这是一个保守的扩展 。例如：

SELECT * FROM t1 LEFT JOIN (t2, t3, t4)
                 ON (t2.a = t1.a AND t3.b = t1.b AND t4.c = t1.c)

相当于：

SELECT * FROM t1 LEFT JOIN (t2 CROSS JOIN t3 CROSS JOIN t4)
                 ON (t2.a = t1.a AND t3.b = t1.b AND t4.c = t1.c)

在 MySQL 中，JOIN，CROSS JOIN和INNER JOIN是语法等价物(它们可以互相替换)。在标准 SQL 中，它们不等效。 INNER JOIN与ON子句一起使用，否则使用CROSS JOIN。

通常，在仅包含内部联接操作的联接表达式中可以忽略括号。 MySQL 还支持嵌套连接。

可以指定索引提示以影响MySQL优化器如何使用索引。有关更多信息，请参见 第8.9.4节“索引提示”。优化器提示和 optimizer_switch系统变量是影响索引对优化器使用的其他方式。请参见 第8.9.3节“优化器提示”和 第8.9.2节“可切换的优化”。

以下列表描述了在编写联接时要考虑的一般因素：

• table reference 可以使用tbl_name AS alias_name或tbl_name alias_name进行别名：

SELECT t1.name, t2.salary
  FROM employee AS t1 INNER JOIN info AS t2 ON t1.name = t2.name;

SELECT t1.name, t2.salary
  FROM employee t1 INNER JOIN info t2 ON t1.name = t2.name;

• table_subquery也称为FROM子句中的派生 table 或子查询。见第 13.2.10.8 节，“派生表”。此类子查询必须包含别名，以便为子查询结果提供 table name。一个简单的例子如下：

SELECT * FROM (SELECT 1, 2, 3) AS t1;

• 单个连接中可引用的最大表数为 61.这包括通过将派生表和FROM子句中的视图合并到外部查询块中来处理的连接(请参阅第 8.2.2.4 节，“通过合并或实现优化派生表和查看 References”)。
• 在没有连接条件的情况下，INNER JOIN和,(逗号)在语义上是等价的：两者在指定的表之间产生笛卡尔积(即，第一个 table 中的每一行都连接到第二个 table 中的每一行) 。

但是，逗号 operator 的优先级小于INNER JOIN，CROSS JOIN，LEFT JOIN等的优先级。如果在存在连接条件时将逗号连接与其他连接类型混合，则可能会出现Unknown column 'col_name' in 'on clause'形式的错误。有关处理此问题的信息将在本节后面给出。

• 与ON一起使用的search_condition是可以在WHERE子句中使用的表单的任何条件表达式。通常，ON子句用于指定如何连接表的条件，WHERE子句限制要包含在结果集中的行。
• 如果LEFT JOIN中的ON或USING部分中的右表没有匹配的行，则将右 table中所有行的列设置为NULL。您可以使用此事实查找 table 中与另一个 table 中没有对应项的行：

SELECT left_tbl.*
  FROM left_tbl LEFT JOIN right_tbl ON left_tbl.id = right_tbl.id
  WHERE right_tbl.id IS NULL;

本示例查找left_tbl中的所有行，其中id value 在right_tbl中不存在(即left_tbl中的所有行，right_tbl中没有对应的行)。

• USING(join_column_list)子句指定两个表中必须存在的列的列表。如果表a和b都包含c1，c2和c3列，则以下连接将比较两个表中的相应列：

a LEFT JOIN b USING (c1, c2, c3)

• 两个表中的NATURAL [LEFT] JOIN被定义为在语义上等同于INNER JOIN或LEFT JOIN，其中USING子句指定两个表中存在的所有列。
• RIGHT JOIN类似于LEFT JOIN。为了使 code 可跨数据库移植，建议您使用LEFT JOIN而不是RIGHT JOIN。

连接语法描述中显示的{ OJ ... }语法仅用于与 ODBC 兼容。语法中的花括号应按字面编写;它们不是语法描述中其他地方使用的 metasyntax。

SELECT left_tbl.*
    FROM { OJ left_tbl LEFT OUTER JOIN right_tbl
           ON left_tbl.id = right_tbl.id }
    WHERE right_tbl.id IS NULL;

您可以在{ OJ ... }中使用其他类型的连接，例如INNER JOIN或RIGHT OUTER JOIN。这有助于与某些 third-party applications 的兼容性，但不是官方的 ODBC 语法。

• STRAIGHT_JOIN与相似 JOIN，不同之处在于总是先在右表之前读取左表。这可以用于联接优化器以次优顺序处理表的那些（很少）情况。

一些连接示例：

SELECT * FROM table1, table2;

SELECT * FROM table1 INNER JOIN table2 ON table1.id = table2.id;

SELECT * FROM table1 LEFT JOIN table2 ON table1.id = table2.id;

SELECT * FROM table1 LEFT JOIN table2 USING (id);

SELECT * FROM table1 LEFT JOIN table2 ON table1.id = table2.id
  LEFT JOIN table3 ON table2.id = table3.id;

根据 SQL:2003 标准处理带有USING的联接和自然联接，包括外连接变体：

• 不会出现NATURAL连接的冗余列。考虑这组 statements：

CREATE TABLE t1 (i INT, j INT);
CREATE TABLE t2 (k INT, j INT);
INSERT INTO t1 VALUES(1, 1);
INSERT INTO t2 VALUES(1, 1);
SELECT * FROM t1 NATURAL JOIN t2;
SELECT * FROM t1 JOIN t2 USING (j);

在第一个SELECT 语句中，列j出现在两个表中，因此成为联接列，根据标准 SQL，它应该只在输出中出现一次，而不是两次。。类似地，在第二个 SELECT 语句中，j列在USING子句中命名，并且在输出中只出现一次，而不是两次。

因此，statements 产生这个输出：

+------+------+------+
| j    | i    | k    |
+------+------+------+
|    1 |    1 |    1 |
+------+------+------+
+------+------+------+
| j    | i    | k    |
+------+------+------+
|    1 |    1 |    1 |
+------+------+------+

根据标准 SQL ，生成冗余列消除和列 ordering，生成此 display order：

• 首先，两个联接表的公共列合并，按它们在第一个表中出现的顺序
• 第二，第一个表中的唯一列，按照它们在那个表中出现的顺序
• 第三，第二个 table 独有的列，按照它们在那个表中出现的顺序

替换两个共同列的结果为单个列是使用 coalesce 操作定义的。也就是说，对于两个t1.a和t2.a，生成单个联接列a，定义方式a = COALESCE(t1.a, t2.a)，其中：

COALESCE(x, y) = (CASE WHEN x IS NOT NULL THEN x ELSE y END)

如果连接操作是任何其他连接，则连接的结果列由连接表的所有列的串联组成。

合并列定义的结果是，对于外连接，如果两列中的一列始终为NULL，则合并列包含非NULL列的 value。如果两列都不是NULL，则两个 common 列都具有相同的 value，因此选择哪一列作为合并列的 value 无关紧要。解释这一点的一种简单方法是考虑外连接的合并列由JOIN的内部 table 的 common 列表示。假设表t1(a, b)和t2(a, c)具有以下内容：

t1    t2
----  ----
1 x   2 z
2 y   3 w

然后，对于此连接，列a包含t1.a的值：

mysql> SELECT * FROM t1 NATURAL LEFT JOIN t2;
+------+------+------+
| a    | b    | c    |
+------+------+------+
|    1 | x    | NULL |
|    2 | y    | z    |
+------+------+------+

相反，对于此连接，列a包含t2.a的值。

mysql> SELECT * FROM t1 NATURAL RIGHT JOIN t2;
+------+------+------+
| a    | c    | b    |
+------+------+------+
|    2 | z    | y    |
|    3 | w    | NULL |
+------+------+------+

将这些结果与JOIN ... ON的其他等效查询进行比较：

mysql> SELECT * FROM t1 LEFT JOIN t2 ON (t1.a = t2.a);
+------+------+------+------+
| a    | b    | a    | c    |
+------+------+------+------+
|    1 | x    | NULL | NULL |
|    2 | y    |    2 | z    |
+------+------+------+------+

mysql> SELECT * FROM t1 RIGHT JOIN t2 ON (t1.a = t2.a);
+------+------+------+------+
| a    | b    | a    | c    |
+------+------+------+------+
|    2 | y    |    2 | z    |
| NULL | NULL |    3 | w    |
+------+------+------+------+

可以将USING子句重写为比较相应列的ON子句。但是，尽管USING和ON相似，但它们并不完全相同。思考以下两个查询：

a LEFT JOIN b USING (c1, c2, c3)
a LEFT JOIN b ON a.c1 = b.c1 AND a.c2 = b.c2 AND a.c3 = b.c3

关于确定哪些行满足连接条件，两个连接在语义上是相同的。

关于确定为SELECT *扩展显示哪些列，这两个连接在语义上不相同。 USING join 选择相应列的合并 value，而ON join 选择所有表中的所有列。对于USING join，SELECT *选择以下值：

COALESCE(a.c1, b.c1), COALESCE(a.c2, b.c2), COALESCE(a.c3, b.c3)

对于ON join，SELECT *选择以下值：

a.c1, a.c2, a.c3, b.c1, b.c2, b.c3

使用内部联接时，COALESCE(a.c1，b.c1)与a.c1或b.c1相同，因为两列将具有相同的 value。使用外连接(例如LEFT JOIN)，两列中的一列可以是NULL。结果中省略了该列。

• ON子句只能引用其操作数。

例：

CREATE TABLE t1 (i1 INT);
CREATE TABLE t2 (i2 INT);
CREATE TABLE t3 (i3 INT);
SELECT * FROM t1 JOIN t2 ON (i1 = i3) JOIN t3;

该语句因Unknown column 'i3' in 'on clause'错误而失败，因为i3是t3中的一列，它不是ON子句的操作数。要使联接能够被处理，请按以下方式编写语句：

SELECT * FROM t1 JOIN t2 JOIN t3 ON (i1 = i3);

JOIN的优先级高于逗号 operator(,)，因此连接表达式t1, t2 JOIN t3被解释为(t1, (t2 JOIN t3))，而不是((t1, t2) JOIN t3)。这会影响使用ON子句的 statements，因为该子句只能引用连接操作数中的列，并且优先级会影响对这些操作数的解释。

例：

CREATE TABLE t1 (i1 INT, j1 INT);
CREATE TABLE t2 (i2 INT, j2 INT);
CREATE TABLE t3 (i3 INT, j3 INT);
INSERT INTO t1 VALUES(1, 1);
INSERT INTO t2 VALUES(1, 1);
INSERT INTO t3 VALUES(1, 1);
SELECT * FROM t1, t2 JOIN t3 ON (t1.i1 = t3.i3);

JOIN优先于逗号 ，因此ON子句的操作数是t2和t3。因为t1.i1不是任何一个操作数中的列，所以结果是Unknown column 't1.i1' in 'on clause'错误。

要启用联接，请使用以下任一策略：

• 将前两个表明确地用括号分组，以便该ON 子句的操作数为(t1, t2)和 t3：

SELECT * FROM (t1, t2) JOIN t3 ON (t1.i1 = t3.i3);

• 避免使用逗号运算符，而应使用 JOIN：

SELECT * FROM t1 JOIN t2 JOIN t3 ON (t1.i1 = t3.i3);

相同的优先级解释也适用于INNER JOIN，CROSS JOIN，LEFT JOIN，和RIGHT JOIN与逗号混合的操作语句，所有这些都具有比逗号操作符更高的优先级。

• 与SQL：2003标准相比，MySQL的扩展是MySQL允许您限定NATURAL或USING 联接的公共（成对）列，而标准不允许这样做。

 UNION子句

SELECT ...
UNION [ALL | DISTINCT] SELECT ...
[UNION [ALL | DISTINCT] SELECT ...]

UNION用于将来自多个SELECT 语句的结果合并为一个结果集。

第一条 SELECT语句中的列名称用作返回结果的列名称。在每个SELECT语句的相应位置列出的所选列应具有相同的数据类型。（例如，第一条语句选择的第一列应与其他语句选择的第一列具有相同的类型。）

如果相应SELECT列的数据类型不匹配，则UNION结果中列的类型和长度将考虑所有SELECT语句检索到的值 。例如，考虑以下内容：

mysql> SELECT REPEAT('a',1) UNION SELECT REPEAT('b',10);
+---------------+
| REPEAT('a',1) |
+---------------+
| a             |
| bbbbbbbbbb    |
+---------------+

该SELECT语句是普通的select语句，但具有以下限制：

• 只有最后一个SELECT 语句可以使用INTO OUTFILE。（但是，会将整个UNION结果写入文件。）
• HIGH_PRIORITY不能与属于UNION的SELECT语句一起使用。如果为第一个 SELECT指定它，则无效。如果为任何后续的SELECTstatements 指定它，则会产生语法错误。

UNION的默认行为是从结果中删除重复的行。可选的DISTINCT关键字除了默认值之外没有任何效果，因为它还是指定了重复行删除。使用可选ALL关键字时，不会发生重复行删除，并且结果包括所有SELECT语句中的所有匹配行。

您可以在同一查询中混合使用UNION ALL和UNION DISTINCT。混合UNION类型的处理方式是，a DISTINCT union重写其左侧的ALL union，可以使用UNION DISTINCT显式生成DISTINCT union，或者使用UNION隐式生成DISTINCT union，而不使用DISTINCT或ALL关键字。

要将ORDER BY或LIMIT应用于单个 SELECT，请将该子句放在包含 SELECT的括号内：

(SELECT a FROM t1 WHERE a=10 AND B=1 ORDER BY a LIMIT 10)
UNION
(SELECT a FROM t2 WHERE a=11 AND B=2 ORDER BY a LIMIT 10);

注意
以前版本的 MySQL 可能会允许这样的语句没有括号。在 MySQL 5.7 中，强制要求使用括号。

对单个 SELECTstatements 使用ORDER BY意味着行在最终结果中出现的 order 没有任何意义，因为UNION默认情况下会产生一组无序的行。因此，ORDER BY在此上下文中，通常将与 LIMIT结合使用，以便将其用于确定要检索的 SELECT选定行的子集，即使它不一定影响最终UNION结果中这些行的顺序 。如果ORDER BY在中 SELECT中没有出现LIMIT，它会被优化掉，因为它无论如何都不会产生任何影响。

要使用ORDER BY或LIMIT子句对整个 UNION 结果进行排序或限制，用圆括号括住各个SELECT语句，并将ORDER BY或LIMIT放在最后一个语句之后。下面示例使用两个子句：

(SELECT a FROM t1 WHERE a=10 AND B=1)
UNION
(SELECT a FROM t2 WHERE a=11 AND B=2)
ORDER BY a LIMIT 10;

如前所述，不带括号的语句等效于一个带括号的语句。

这种ORDER BY不能使用包含表名的列的引用（即tbl_name.col_name格式的名称）。而是在第一个SELECT语句中提供列别名，并在ORDER BY中引用的别名。(或者，在ORDER BY中的列，使用其列位置.但是，使用列位置已经无效.)

此外，如果要排序的列是别名，则ORDER BY子句必须引用别名，而不是列 name。以下第一个 statements 将起作用，但第二个将失败并出现Unknown column 'a' in 'order clause'错误：

(SELECT a AS b FROM t) UNION (SELECT ...) ORDER BY b;
(SELECT a AS b FROM t) UNION (SELECT ...) ORDER BY a;

要使 UNION 结果中的行由每个 SELECT一个接一个地检索的行的集合组成，在每个 SELECT语句中选择一个额外的列作为排序列使用并在最后一个SELECT语句后添加一个ORDER BY：

(SELECT 1 AS sort_col, col1a, col1b, ... FROM t1)
UNION
(SELECT 2, col2a, col2b, ... FROM t2) ORDER BY sort_col;

要在单个选择结果中另外维护排序顺序，向ORDER BY子句添加第二列：

(SELECT 1 AS sort_col, col1a, col1b, ... FROM t1)
UNION
(SELECT 2, col2a, col2b, ... FROM t2) ORDER BY sort_col, col1a;

使用附加列还可以确定每一行来自哪个SELECT。额外列也可以提供其他标识信息，例如表示表名的字符串

ORDER BY子句中带有聚合 function 的UNION 查询将被拒绝，并出现ER_AGGREGATEORDER_FORUNION错误。 例：

SELECT 1 AS foo UNION SELECT 2 ORDER BY MAX(1);

子查询语法

子查询是SELECT另一个语句中的一条语句。

支持SQL标准要求的所有子查询形式和操作，以及一些特定于MySQL的功能。

这是子查询的示例：

SELECT * FROM t1 WHERE column1 = (SELECT column1 FROM t2);

在此示例中，SELECT * FROM t1 ...是 外部查询（或external statement），(SELECT column1 FROM t2)是subquery。我们说子查询嵌套在外部查询中，实际上，有可能将子查询嵌套到其他子查询中相当深的深度。子查询必须始终出现在括号内。

子查询的主要优点是：

• 它们允许结构化的查询，以便可以隔离语句的每个部分。

• 它们提供了执行操作的替代方法，否则将需要复杂的联接和联合。

• 许多人发现子查询比复杂的联接或联合更具可读性。确实，正是子查询的创新使人们有了将早期SQL称为“ 结构化查询语言 ”的初衷 。”

这是一个示例语句，该语句显示有关SQL标准指定并受MySQL支持的子查询语法的要点：

DELETE FROM t1
WHERE s11 > ANY
 (SELECT COUNT(*) /* no hint */ FROM t2
  WHERE NOT EXISTS
   (SELECT * FROM t3
    WHERE ROW(5*t2.s1,77)=
     (SELECT 50,11*s1 FROM t4 UNION SELECT 50,77 FROM
      (SELECT * FROM t5) AS t5)));

子查询可以返回标量（单个值），单行，单列或表（一个或多个列中的一个或多个行）。这些称为标量，列，行和表子查询。返回特定类型结果的子查询通常只能在某些上下文中使用，如以下各节所述。

可以在其中使用子查询的语句类型几乎没有限制。子查询可以包含很多的关键字或条款，一个普通的 SELECT可以包含： DISTINCT，GROUP BY， ORDER BY，LIMIT，连接，索引提示，UNION结构，注释，函数等等。

子查询的外部语句可以是任何一个： SELECT， INSERT， UPDATE， DELETE， SET，或 DO。

MySQL 中，你不能在子查询中修改表和从相同的表中选择，这适用于语句，例如 DELETE， INSERT， REPLACE， UPDATE，和（因为子查询可以在SET子句中使用） LOAD DATA。

有关优化程序如何处理子查询的信息，请参阅第 8.2.2 节，“优化子查询，派生表和查看 References”。有关子查询使用限制的讨论，包括某些形式的子查询语法的 performance 问题，请参阅第 13.2.10.12 节，“子查询的限制”。

子查询作为标量操作数

在最简单的形式中，子查询是返回单个值的标量子查询。标量子查询是一个简单的操作数，几乎可以在任何单列值或文字合法的地方使用它，并且可以期望它具有所有操作数都具有的那些特征：数据类型，长度，指示可以是NULL，依此类推。例如：

CREATE TABLE t1 (s1 INT, s2 CHAR(5) NOT NULL);
INSERT INTO t1 VALUES(100, 'abcde');
SELECT (SELECT s2 FROM t1);

此SELECT子查询返回一个单一值（'abcde'），其数据类型为CHAR，长度为5，字符集和排序规则等于CREATE TABLE时有效的默认值 ，并指示该列中的值可以为 NULL。标量子查询选择的值的可空性不会被复制，因为如果子查询结果为空，则结果为NULL。对于刚刚显示的子查询，如果t1是空的，其结果必然是NULL，即使 s2是NOT NULL。

在某些情况下，无法使用标量子查询。如果语句仅允许使用文字值，则不能使用子查询。例如，LIMIT需要文字整数参数，并且LOAD DATA需要文字字符串文件名。您不能使用子查询来提供这些值。

当您在以下各节中看到包含相当简单的(SELECT column1 FROM t1)构造的示例时，请想象您自己的代码包含了更多不同和复杂的构造。

假设我们制作了两个表：

CREATE TABLE t1 (s1 INT);
INSERT INTO t1 VALUES (1);
CREATE TABLE t2 (s1 INT);
INSERT INTO t2 VALUES (2);

然后执行SELECT：

SELECT (SELECT s1 FROM t2) FROM t1;

结果是2，因为t2中有一行包含了一个字段s1,其值为2。

使用子查询进行比较

子查询最常见的用法是：

non_subquery_operand comparison_operator (subquery)

comparison_operator是其中一个 operators：

=  >  <  >=  <=  <>  !=  <=>

例如：

... WHERE 'a' = (SELECT column1 FROM t1)

MySQL 也允许这种结构：

non_subquery_operand LIKE (subquery)

在一个时间点，子查询的唯一合法位置在比较的右侧，您可能仍然会发现一些坚持这一点的旧 DBMS。

这是一个普通形式的子查询比较示例，您无法使用联接进行此比较。它查找表T1中的所有行，其中 column1 值等于表T2中的最大值：

SELECT * FROM t1
  WHERE column1 = (SELECT MAX(column2) FROM t2);

这是另一个示例，再次使用联接是不可能的，因为它涉及到对一个表进行聚合。它查找t1表中包含在给定列中出现两次值的所有行：

SELECT * FROM t1 AS t
  WHERE 2 = (SELECT COUNT(*) FROM t1 WHERE t1.id = t.id);

为了将子查询与标量进行比较，子查询必须返回一个标量。为了将子查询和行构造函数进行比较，该子查询必须返回与行构造函数具有相同数量值的行。

包含 ANY，IN 或 SOME 的子查询

句法：

operand comparison_operator ANY (subquery)
operand IN (subquery)
operand comparison_operator SOME (subquery)

comparison_operator是其中一个 operators：

=  >  <  >=  <=  <>  !=

ANY关键字必须遵循比较 operator，意味着‘如果和子查询返回列中的任一个值比较为TRUE,则返回TRUE’。”例如：

SELECT s1 FROM t1 WHERE s1 > ANY (SELECT s1 FROM t2);

假设 table t1中有一行包含(10)。如果 table t2包含(21,14,7)，则表达式为TRUE，因为t2中的 value 7小于10。如果 table t2包含(20,10)，或者 table t2为空，则表达式为FALSE。如果 table t2包含(NULL,NULL,NULL)，则表达式未知(即NULL)。

与子查询一起使用时，该词IN是= ANY的别名。因此，这两个语句是相同的：

SELECT s1 FROM t1 WHERE s1 = ANY (SELECT s1 FROM t2);
SELECT s1 FROM t1 WHERE s1 IN    (SELECT s1 FROM t2);

与表达式列表一起使用时，IN和= ANY不是同义词。 IN可以采用表达式列表，但= ANY不能。

NOT IN不是<> ANY的别名，而是<> ALL

SOME这个词是ANY的别名。因此，这两个语句是相同的：

SELECT s1 FROM t1 WHERE s1 <> ANY  (SELECT s1 FROM t2);
SELECT s1 FROM t1 WHERE s1 <> SOME (SELECT s1 FROM t2);

使用SOME这个词很少见，但是这个例子说明了它可能有用的原因。对于大多数人来说，英语短语“a 不等于任何 b”意味着“没有 b 等于 a”，但这不是 SQL 语法的含义。语法意味着“有一些 b 与 a 不相等”。使用<> SOME可以帮助确保每个人都理解查询的 true 含义。

包含 ALL 的子查询

句法：

operand comparison_operator ALL (subquery)

这个词ALL，它必须遵循一个比较操作符，表示“ 如果子查询中返回的列的所有值与其比较都为TRUE则返回TRUE” 例如：

SELECT s1 FROM t1 WHERE s1 > ALL (SELECT s1 FROM t2);

假设 table t1中有一行包含(10)。如果 table t2包含(-5,0,+5)，则表达式为TRUE，因为10大于t2中的所有三个值。如果 table t2包含(12,6,NULL,-100)，则表达式为FALSE，因为 table t2中的单个 value 12大于10。如果 table t2包含(0,NULL,1)，则表达式未知(即NULL)。

最后，如果 table t2为空，则表达式为TRUE。因此，当 table t2为空时，以下表达式为TRUE：

SELECT * FROM t1 WHERE 1 > ALL (SELECT s1 FROM t2);

但是当 table t2为空时，此表达式为NULL：

SELECT * FROM t1 WHERE 1 > (SELECT s1 FROM t2);

此外，当 table t2为空时，以下表达式为NULL：

SELECT * FROM t1 WHERE 1 > ALL (SELECT MAX(s1) FROM t2);

 通常，包含NULL值和空表的表是“边缘情况”。在编写子查询时，请始终考虑是否考虑了这两种可能性。

NOT IN是<> ALL的别名。因此，这两个语句是相同的：

SELECT s1 FROM t1 WHERE s1 <> ALL (SELECT s1 FROM t2);
SELECT s1 FROM t1 WHERE s1 NOT IN (SELECT s1 FROM t2);

行子查询

标量或列子查询返回单个 value 或一列值。行子查询是一个子查询变量，它返回一行，因此可以返回多个列 value。行子查询比较的合法运算符是：

=  >  <  >=  <=  <>  !=  <=>

这是两个示例：

SELECT * FROM t1
  WHERE (col1,col2) = (SELECT col3, col4 FROM t2 WHERE id = 10);
SELECT * FROM t1
  WHERE ROW(col1,col2) = (SELECT col3, col4 FROM t2 WHERE id = 10);

对于两个查询，如果 table t2包含带id = 10的单行，则子查询返回单行。如果此行的col3和col4值等于t1中任何行的col1和col2值，则WHERE表达式为TRUE，并且每个查询都返回t1的行数。如果t2行col3和col4值不等于任何t1行的col1和col2值，则表达式为FALSE，并且查询返回空结果集。如果子查询不生成行，则表达式未知(即NULL)。如果子查询生成多行，则会发生错误，因为行子查询最多只能返回一行。

有关每个 operator 如何用于行比较的信息，请参阅第 12.3.2 节，“比较函数和 Operators”。

表达式(1,2)和ROW(1,2)有时称为行构造函数。两者是等价的。行构造函数和子查询返回的行必须包含相同数量的值。

行构造函数用于与返回两列或更多列的子查询进行比较。当子查询返回单个列时，这被视为标量 value 而不是行，因此行构造函数不能与不返回至少两列的子查询一起使用。因此，以下查询失败并出现语法错误：

SELECT * FROM t1 WHERE ROW(1) = (SELECT column1 FROM t2)

在其他情况下，行构造器是合法的。例如，以下两个语句在语义上是等效的（并且由优化器以相同的方式处理）：

SELECT * FROM t1 WHERE (column1,column2) = (1,1);
SELECT * FROM t1 WHERE column1 = 1 AND column2 = 1;

以下查询回答请求，“查找 table t1中也存在于 table t2中的所有行”：

SELECT column1,column2,column3
  FROM t1
  WHERE (column1,column2,column3) IN
         (SELECT column1,column2,column3 FROM t2);

有关优化程序和行构造函数的更多信息，请参阅Section 8.2.1.19，“行构造函数表达式优化”

具有 EXISTS 或 NOT EXISTS 的子查询

如果子查询返回任何行，EXISTS subquery是TRUE，NOT EXISTS subquery是FALSE。例如：

SELECT column1 FROM t1 WHERE EXISTS (SELECT * FROM t2);

传统上，EXISTS子查询以SELECT *开头，但它可以以SELECT 5或SELECT column1或任何东西开头。 MySQL 忽略了这样一个子查询中的 SELECT列表，所以没有区别。

对于前面的示例，如果t2包含任何行，甚至包含NULL值的行，则EXISTS条件为 TRUE。实际上这是一个不太可能的示例，因为[NOT] EXISTS子查询几乎总是包含相关性。以下是一些更实际的示例：

• 一个或多个城市设有哪种商店？

SELECT DISTINCT store_type FROM stores
  WHERE EXISTS (SELECT * FROM cities_stores
                WHERE cities_stores.store_type = stores.store_type);

• 没有城市有什么样的商店？

SELECT DISTINCT store_type FROM stores
  WHERE NOT EXISTS (SELECT * FROM cities_stores
                    WHERE cities_stores.store_type = stores.store_type);

• 所有城市都有什么样的商店？

SELECT DISTINCT store_type FROM stores s1
  WHERE NOT EXISTS (
    SELECT * FROM cities WHERE NOT EXISTS (
      SELECT * FROM cities_stores
       WHERE cities_stores.city = cities.city
       AND cities_stores.store_type = stores.store_type));

最后一个示例是双重嵌套NOT EXISTS查询。也就是说，它在NOT EXISTS子句中具有NOT EXISTS 子句。形式上，它回答了“一个城市是否存在一个商店不在商店类中”。但是更容易说嵌套的NOT EXISTS回答了问题 “is x TRUE for all y?”

相关子查询

SELECT * FROM t1
  WHERE column1 = ANY (SELECT column1 FROM t2
                       WHERE t2.column2 = t1.column2);

请注意，子查询包含t1列的 reference，即使子查询的FROM子句未提及 table t1。因此，MySQL 在子查询之外查找，并在外部查询中查找t1。

假设 table t1包含column1 = 5和column2 = 6的行;同时，table t2包含column1 = 5和column2 = 7的行。简单表达式... WHERE column1 = ANY (SELECT column1 FROM t2)将是TRUE，但在此例中，子查询中的WHERE子句是FALSE(因为(5,6)不等于(5,7))，因此整个表达式是FALSE。

范围规则： MySQL 从内到外进行评估。例如：

SELECT column1 FROM t1 AS x
  WHERE x.column1 = (SELECT column1 FROM t2 AS x
    WHERE x.column1 = (SELECT column1 FROM t3
      WHERE x.column2 = t3.column1));

在此语句中，x.column2必须是 table t2中的列，因为SELECT column1 FROM t2 AS x ...重命名t2。它不是 table t1中的列，因为SELECT column1 FROM t1 ...是一个更远的外部查询。

对于HAVING或ORDER BY子句中的子查询，MySQL 还会在外部 select 列表中查找列名。

对于某些情况，优化了相关子查询。例如：

val IN (SELECT key_val FROM tbl_name WHERE correlated_condition)

否则，它们效率低下并且可能会变慢。将查询重写为联接可能会提高性能。

相关子查询中的聚合函数可能包含外部引用，但前提是该函数只包含外部引用，并且该函数未包含在另一个函数或表达式中。

派生表

派生表是在查询FROM子句范围内生成表的表达式。例如，SELECT 语句FROM子句中的子查询是派生表：

SELECT ... FROM (subquery) [AS] tbl_name ...

[AS] tbl_name子句是必需的，因为FROM子句中的每个 table 都必须具有 name。派生的 table 中的任何列都必须具有唯一的名称。

为了便于说明，假设你有这个 table：

CREATE TABLE t1 (s1 INT, s2 CHAR(5), s3 FLOAT);

以下是使用示例表在FROM子句中使用子查询的方法：

INSERT INTO t1 VALUES (1,'1',1.0);
INSERT INTO t1 VALUES (2,'2',2.0);
SELECT sb1,sb2,sb3
  FROM (SELECT s1 AS sb1, s2 AS sb2, s3*2 AS sb3 FROM t1) AS sb
  WHERE sb1 > 1;

结果：

+------+------+------+
| sb1  | sb2  | sb3  |
+------+------+------+
|    2 | 2    |    4 |
+------+------+------+

这是另一个示例：假设您想知道分组表的一组总和的平均值。这不起作用：

SELECT AVG(SUM(column1)) FROM t1 GROUP BY column1;

但是，此查询提供了所需的信息：

SELECT AVG(sum_column1)
  FROM (SELECT SUM(column1) AS sum_column1
        FROM t1 GROUP BY column1) AS t1;

请注意，在子查询（sum_column1）中使用的列名在外部查询中被识别。

派生表可以返回标量，列，行或表。

派生表受以下限制：

• 派生表不能是相关子查询。

• 派生表不能包含对相同SELECT的其他表的引用。

• 派生表不能包含外部引用。这是MySQL的限制，不是SQL标准的限制。

优化程序以EXPLAIN不需要具体化派生表的方式来确定有关派生表的信息。

在某些情况下，使用EXPLAIN SELECT可能会修改 table 数据。如果外部查询访问任何表并且内部查询调用存储的 function 来更改 table 的一行或多行，则会发生这种情况。假设数据库d1中有两个表t1和t2，以及一个修改t2的存储 function f1，如下所示创建：

CREATE DATABASE d1;
USE d1;
CREATE TABLE t1 (c1 INT);
CREATE TABLE t2 (c1 INT);
CREATE FUNCTION f1(p1 INT) RETURNS INT
  BEGIN
    INSERT INTO t2 VALUES (p1);
    RETURN p1;
  END;

直接在EXPLAIN SELECT中引用 function 对t2没有影响，如下所示：

mysql> SELECT * FROM t2;
Empty set (0.02 sec)

mysql> EXPLAIN SELECT f1(5)\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: NULL
   partitions: NULL
         type: NULL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: NULL
     filtered: NULL
        Extra: No tables used
1 row in set (0.01 sec)

mysql> SELECT * FROM t2;
Empty set (0.01 sec)

这是因为该SELECT 语句未引用任何表，如在输出的 table和Extra列中所示。以下嵌套SELECT也是如此 ：

mysql> EXPLAIN SELECT NOW() AS a1, (SELECT f1(5)) AS a2\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: NULL
         type: NULL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: NULL
     filtered: NULL
        Extra: No tables used
1 row in set, 1 warning (0.00 sec)

mysql> SHOW WARNINGS;
+-------+------+------------------------------------------+
| Level | Code | Message                                  |
+-------+------+------------------------------------------+
| Note  | 1249 | Select 2 was reduced during optimization |
+-------+------+------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM t2;
Empty set (0.00 sec)

但是，如果外部SELECT 引用任何表，则优化器也会在子查询中执行该语句，其结果将t2 被修改：

mysql> EXPLAIN SELECT * FROM t1 AS a1, (SELECT f1(5)) AS a2\G
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: <derived2>
   partitions: NULL
         type: system
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra: NULL
*************************** 2. row ***************************
           id: 1
  select_type: PRIMARY
        table: a1
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra: NULL
*************************** 3. row ***************************
           id: 2
  select_type: DERIVED
        table: NULL
   partitions: NULL
         type: NULL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: NULL
     filtered: NULL
        Extra: No tables used
3 rows in set (0.00 sec)

mysql> SELECT * FROM t2;
+------+
| c1   |
+------+
|    5 |
+------+
1 row in set (0.00 sec)

这也意味着EXPLAIN SELECT语句(例如此处显示的语句)可能需要 long time 才能执行，因为t1 function 对t1中的每一行执行一次：

EXPLAIN SELECT * FROM t1 AS a1, (SELECT BENCHMARK(1000000, MD5(NOW())));

子查询错误

有些错误仅适用于子查询。本节介绍它们。

• 不支持的子查询语法：

ERROR 1235 (ER_NOT_SUPPORTED_YET)
SQLSTATE = 42000
Message = "This version of MySQL doesn't yet support
'LIMIT & IN/ALL/ANY/SOME subquery'"

这意味着 MySQL 不支持以下形式的 statements：

SELECT * FROM t1 WHERE s1 IN (SELECT s2 FROM t2 ORDER BY s1 LIMIT 1)

• 子查询中的列数不正确：

ERROR 1241 (ER_OPERAND_COL)
SQLSTATE = 21000
Message = "Operand should contain 1 column(s)"

在这种情况下会发生此错误：

SELECT (SELECT column1, column2 FROM t2) FROM t1;

如果目的是行比较，则可以使用返回多个列的子查询。在其他上下文中，子查询必须是标量操作数。见第 13.2.10.5 节，“行子查询”。

• 子查询中的行数不正确：

ERROR 1242 (ER_SUBSELECT_NO_1_ROW)
SQLSTATE = 21000
Message = "Subquery returns more than 1 row"

对于子查询必须返回最多一行但返回多行的语句，会发生此错误。考虑以下示例：

SELECT * FROM t1 WHERE column1 = (SELECT column1 FROM t2);

如果SELECT column1 FROM t2仅返回一行，则前一个查询将起作用。如果子查询返回多于一行，将发生错误1242。在这种情况下，查询应重写为：

SELECT * FROM t1 WHERE column1 = ANY (SELECT column1 FROM t2);

• 子查询中使用不正确的表：

Error 1093 (ER_UPDATE_TABLE_USED)
SQLSTATE = HY000
Message = "You can't specify target table 'x'
for update in FROM clause"

在以下情况下会发生此错误，这些情况会尝试修改表并从子查询中的同一表中进行选择：

UPDATE t1 SET column2 = (SELECT MAX(column1) FROM t1);

您可以使用子查询在UPDATE语句中进行分配， 因为在UPDATE和 DELETE语句中以及在SELECT语句中子查询都是合法的 。但是，您不能对子查询FROM子句和更新目标使用相同的 table(在本例中为 table t1)。

对于 transactional 存储引擎，子查询失败会导致整个语句失败。对于非事务性存储引擎，将保留在遇到错误之前进行的数据修改。

优化子查询

开发正在进行中，

• 使用影响子查询中行数或顺序的子查询子句。例如：

SELECT * FROM t1 WHERE t1.column1 IN
  (SELECT column1 FROM t2 ORDER BY column1);
SELECT * FROM t1 WHERE t1.column1 IN
  (SELECT DISTINCT column1 FROM t2);
SELECT * FROM t1 WHERE EXISTS
  (SELECT * FROM t2 LIMIT 1);

• 用子查询替换联接。例如，尝试以下操作：

SELECT DISTINCT column1 FROM t1 WHERE t1.column1 IN (
  SELECT column1 FROM t2);

而不是这个：

SELECT DISTINCT t1.column1 FROM t1, t2
  WHERE t1.column1 = t2.column1;

• 可以将某些子查询转换为联接，以与不支持子查询的旧版MySQL兼容。但是，在某些情况下，将子查询转换为联接可能会提高性能。
• 将子句从子查询的外部移到内部。例如，使用以下查询：

SELECT * FROM t1
  WHERE s1 IN (SELECT s1 FROM t1 UNION ALL SELECT s1 FROM t2);

而不是这个查询：

SELECT * FROM t1
  WHERE s1 IN (SELECT s1 FROM t1) OR s1 IN (SELECT s1 FROM t2);

对于另一个示例，请使用以下查询：

SELECT (SELECT column1 + 5 FROM t1) FROM t2;

而不是这个查询：

SELECT (SELECT column1 FROM t1) + 5 FROM t2;

• 使用行子查询而不是相关子查询。例如，使用以下查询：

SELECT * FROM t1
  WHERE (column1,column2) IN (SELECT column1,column2 FROM t2);

而不是这个查询：

SELECT * FROM t1
  WHERE EXISTS (SELECT * FROM t2 WHERE t2.column1=t1.column1
                AND t2.column2=t1.column2);

• 使用NOT (a = ANY (...))而不是a <> ALL (...)。

• 使用x = ANY (table containing (1,2))而不是x=1 OR x=2。

• 使用= ANY而不是EXISTS。

• 对于总是返回一行的不相关子查询，总是 IN比慢 =。例如，使用以下查询：

SELECT * FROM t1
  WHERE t1.col_name = (SELECT a FROM t2 WHERE b = some_const);

而不是这个查询：

SELECT * FROM t1
  WHERE t1.col_name IN (SELECT a FROM t2 WHERE b = some_const);

这些技巧可能会导致程序运行得更快或更慢。使用MySQL BENCHMARK()函数之类的 功能，您可以了解自己的情况有什么帮助。

MySQL 本身的一些优化是：

• MySQL仅执行一次不相关的子查询。用于 EXPLAIN确保给定的子查询确实不相关。
• MySQL的重写IN， ALL，ANY，和 SOME试图利用子查询中的 select-list 列被索引的可能性。
• MySQL将以下形式的子查询替换为索引查找功能，该功能 EXPLAIN描述为特殊的连接类型（unique_subquery或 index_subquery）：

... IN (SELECT indexed_column FROM single_table ...)

• MySQL使用包含MIN()或MAX() 的表达式来增强以下形式的表达式，除非包含 NULL值或空集：

value {ALL|ANY|SOME} {> | < | >= | <=} (uncorrelated subquery)

例如，此WHERE子句：

WHERE 5 > ALL (SELECT x FROM t)

化器可能会这样处理：

WHERE 5 > (SELECT MAX(x) FROM t)

将子查询重写为连接

有时，除了使用子查询之外，还有其他方法可以测试一组值中的成员资格。同样，在某些情况下，不仅可以在没有子查询的情况下重写查询，而且利用其中的某些技术而不是使用子查询可能更有效。其中之一是 IN()构造：

例如，此查询：

SELECT * FROM t1 WHERE id IN (SELECT id FROM t2);

可以改写为：

SELECT DISTINCT t1.* FROM t1, t2 WHERE t1.id=t2.id;

查询：

SELECT * FROM t1 WHERE id NOT IN (SELECT id FROM t2);
SELECT * FROM t1 WHERE NOT EXISTS (SELECT id FROM t2 WHERE t1.id=t2.id);

可以改写为：

SELECT table1.*
  FROM table1 LEFT JOIN table2 ON table1.id=table2.id
  WHERE table2.id IS NULL;

LEFT [OUTER] JOIN可以比等效子查询更快，因为服务器可能能够更好地优化它 - 这个事实并非仅针对 MySQL 服务器。在 SQL-92 之前，外连接不存在，因此子查询是执行某些操作的唯一方法。今天，MySQL Server 和许多其他现代数据库系统提供了广泛的外连接类型。

MySQL Server支持多表 DELETE语句，这些语句可用于基于一个表甚至是多个表的信息有效地删除行。UPDATE还支持多表 语句。

子查询的限制

• 通常，您不能修改表并在子查询中从同一表中选择。例如，此限制适用于以下形式的语句：

DELETE FROM t WHERE ... (SELECT ... FROM t ...);
UPDATE t ... WHERE col = (SELECT ... FROM t ...);
{INSERT|REPLACE} INTO t (SELECT ... FROM t ...);

例外：如果对于修改后的表，您正在使用派生表，并且该派生表已实现，而不是合并到外部查询中，则上述禁止条件不适用。

UPDATE t ... WHERE col = (SELECT * FROM (SELECT ... FROM t...) AS dt ...);

在这里，派生表的结果被实现为临时表，所以t中的相关行在t更新时已经被选中。

• 仅部分支持行比较操作：

1. For expr [NOT] IN subquery, expr can be an n-tuple (specified using row constructor syntax) and the subquery can return rows of n-tuples. The permitted syntax is therefore more specifically expressed as row_constructor [NOT] IN table_subquery

2. For expr op {ALL|ANY|SOME} subquery, expr must be a scalar value and the subquery must be a column subquery; it cannot return multiple-column rows.

换句话说，对于返回n-tuples 行的子查询， 支持以下操作：

(expr_1, ..., expr_n) [NOT] IN table_subquery

但这不被支持：

(expr_1, ..., expr_n) op {ALL|ANY|SOME} subquery

支持IN但不 支持其他行比较的原因 IN是通过将其重写为一系列= 比较和AND操作来实现的。这种方法不能用于ALL， ANY或SOME。

• FROM子句中的子查询不能是相关子查询。它们在查询执行期间整体实现（评估为生成结果集），因此无法对外部查询的每一行进行评估。优化程序将延迟实现，直到需要结果为止，这可以避免实现。
• MySQL 在某些子查询运算符的子查询中不支持LIMIT：

mysql> SELECT * FROM t1
       WHERE s1 IN (SELECT s2 FROM t2 ORDER BY s1 LIMIT 1);
ERROR 1235 (42000): This version of MySQL doesn't yet support
 'LIMIT & IN/ALL/ANY/SOME subquery'

• MySQL 允许子查询引用存储的 function，它具有 data-modifying 副作用，例如将行插入 table。例如，如果f()插入行，则以下查询可以修改数据：

SELECT ... WHERE x IN (SELECT f() ...);

此行为是 SQL 标准的扩展。在 MySQL 中，它可以产生不确定的结果，因为对于给定查询的不同执行，f()可能执行的次数不同，具体取决于优化程序选择如何处理它。

对于基于语句或混合格式的复制，这种不确定性的一个含义是，这样的查询可以在主服务器及其从属服务器上产生不同的结果。

UPDATE 语法

UPDATE是一个 DML 语句，用于修改 table 中的行。

Single-table 语法：

UPDATE [LOW_PRIORITY] [IGNORE] table_reference
    SET assignment_list
    [WHERE where_condition]
    [ORDER BY ...]
    [LIMIT row_count]

value:
    {expr | DEFAULT}

assignment:
    col_name = value

assignment_list:
    assignment [, assignment] ...

Multiple-table 语法：

UPDATE [LOW_PRIORITY] [IGNORE] table_references
    SET assignment_list
    [WHERE where_condition]

对于 single-table 语法，UPDATE语句使用新值更新名为 table 的现有行的列。 SET子句指示要修改的列以及应该给出的值。每个 value 可以作为表达式给出，或者关键字DEFAULT可以将列显式设置为其默认 value。如果给出WHERE子句，则指定标识要更新的行的条件。如果没有WHERE子句，则更新所有行。如果指定了ORDER BY子句，则在指定的 order 中更新行。 LIMIT子句限制了可以更新的行数。

对于多表语法， UPDATE更新table_references满足条件的每个表中的行。即使每个匹配行多次匹配条件，它也会更新一次。对于多表的语法， 不能使用ORDER BY和LIMIT。

对于分区表，此语句的 single-single 和 multiple-table 形式都支持使用PARTITION选项作为 table reference 的一部分。此选项采用一个或多个分区或子分区(或两者)的列表。仅检查列出的分区(或子分区)是否匹配，并且不更新任何这些分区或子分区中的行，无论它是否满足where_condition。

Note

不像这种情况当使用PARTITION语句跟上 an INSERT or REPLACE statement；另外 UPDATE ... PARTITION statement被认为是成功的，尽管分区（或子分区）列表中没有行匹配 the where_condition.

where_condition是一个表达式，对于要更新的每一行，求值为 true。

table_references和where_condition的指定如第 13.2.9 节，“ SELECT 语法”中所述。

您只需要对UPDATE 实际更新的列中引用的列具有UPDATE特权。对于读取但未修改的任何列，您只需要SELECT特权。

UPDATE语句支持以下修饰符：

• 使用LOW_PRIORITY修饰符，UPDATE延迟执行，直到没有其他客户端从表中读取。这会影响只使用表级锁只存储引擎（例如 MyISAM，MEMORY和 MERGE）。
• 使用IGNORE修饰符，即使更新期间发生错误，update语句也不会中止。在唯一键值上发生重复键冲突的行不会更新。更新为导致数据转换错误的值的行将更新为最接近的有效值。有

UPDATE IGNORE 语句（包括具有ORDER BY 子句的语句）被标记为对基于语句的复制不安全。 (This is because the order in which the rows are updated determines which rows are ignored.) Such statements produce a warning in the error log when using statement-based mode and are written to the binary log using the row-based format when using MIXED mode. (Bug #11758262, Bug #50439) See Section 16.2.1.3, “Determination of Safe and Unsafe Statements in Binary Logging”, for more information.

如果从 table 访问要在表达式中更新的列，UPDATE将使用列的当前 value。例如，以下语句设置 col1为比当前值大一：

UPDATE t1 SET col1 = col1 + 1;

以下语句中的第二个赋值将col2设置为当前(更新的)col1 value，而不是原始的col1 value。结果是col1和col2具有相同的 value。此行为与标准 SQL 不同。

UPDATE t1 SET col1 = col1 + 1, col2 = col1;

Single-table UPDATE作业通常从左到右进行评估。对于 multiple-table 更新，无法保证在任何特定的 order 中执行分配。

如果将列设置为其当前值，MySQL会注意到这一点，并且不会对其进行更新。

如果通过设置为NULL更新已声明为NOT NULL的列，则在启用严格 SQL 模式时会发生错误;否则，该列将设置为列数据类型的隐式默认值 value，并且警告计数会递增。隐式默认 value 对于数值类型是0，对于 string 类型是空 string('')，对于 date 和 time 类型是“零”value。

如果显式更新生成的列，则唯一允许的 value 为DEFAULT。

UPDATE返回实际更改的行数。 mysql_info() C API function 返回匹配和更新的行数以及UPDATE期间发生的警告数。

您可以使用LIMIT row_count来限制UPDATE的范围。 LIMIT子句是 rows-matched 限制。一旦找到满足WHERE子句的row_count行，该语句就会停止，无论它们是否实际被更改。

如果UPDATE语句包含ORDER BY子句，则行将在子句指定的 order 中更新。这在某些可能导致错误的情况下非常有用。假设 table t包含具有唯一索引的列id。以下语句可能会因 duplicate-key 错误而失败，具体取决于更新行的 order：

UPDATE t SET id = id + 1;

例如，如果表在id列中包含1和2， 并且在2更新为3之前将1更新为2，则会发生错误。为避免此问题，请添加一个 ORDER BY子句以使具有较大id值的行在具有较小值的行 之前进行更新：

UPDATE t SET id = id + 1 ORDER BY id DESC;

您还可以执行UPDATE 涵盖多个表的操作。但是，您不能使用 ORDER BY或LIMIT与multi-table一起使用UPDATE。table_references子句列出了连接中涉及的表。

UPDATE items,month SET items.price=month.price
WHERE items.id=month.id;

前面的示例显示了使用逗号运算符的内部联接，但是多表UPDATE 语句可以使用 SELECT语句中允许的任何类型的联接，例如 LEFT JOIN。

UPDATE items,month SET items.price=month.price
WHERE items.id=month.id;

如果使用涉及InnoDB表的 multiple-table UPDATE语句，并且存在外部 key 约束，则 MySQL 优化器可能会处理与 parent/child 关系不同的表。在这种情况下，语句失败并回滚。相反，更新单个 table 并依赖InnoDB提供的ON UPDATE功能，以便相应地修改其他表。

您不能更新表并直接在子查询中从同一表中选择。您可以使用多表更新来解决此问题，在该更新中，其中一个表是从您实际希望更新的表派生的，并使用别名引用派生表。假设您希望更新一个 items使用下面所示语句定义的表：

CREATE TABLE items (
    id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    wholesale DECIMAL(6,2) NOT NULL DEFAULT 0.00,
    retail DECIMAL(6,2) NOT NULL DEFAULT 0.00,
    quantity BIGINT NOT NULL DEFAULT 0
);

为了降低加价幅度为30％或以上且您的存货少于100的任何商品的零售价格，您可以尝试使用以下UPDATE语句（例如以下语句），该语句在WHERE子句中使用子查询 。如此处所示，此语句不起作用：

mysql> UPDATE items  
     > SET retail = retail * 0.9  
     > WHERE id IN 
     >     (SELECT id FROM items 
     >         WHERE retail / wholesale >= 1.3 AND quantity > 100);
ERROR 1093 (HY000): You can't specify target table 'items' for update in FROM clause

相反，您可以采用多表更新，其中将子查询移到要更新的表列表中，使用别名在最外层WHERE子句中引用它，如下所示：

UPDATE items, 
       (SELECT id FROM items 
        WHERE id IN 
            (SELECT id FROM items 
             WHERE retail / wholesale >= 1.3 AND quantity < 100)) 
        AS discounted 
SET items.retail = items.retail * 0.9 
WHERE items.id = discounted.id;

由于优化器默认会尝试将派生表合并 discounted到最外面的查询块中，因此仅当您强制实现派生表时，此方法才有效。您可以通过在运行更新之前derived_merge将optimizer_switch系统变量的标志设置为off或使用NO_MERGE优化程序提示来做到这一点，如下所示：

UPDATE /*+ NO_MERGE(discounted) */ items, 
       (SELECT id FROM items 
        WHERE retail / wholesale >= 1.3 AND quantity < 100) 
        AS discounted 
    SET items.retail = items.retail * 0.9 
    WHERE items.id = discounted.id;

 

在这种情况下使用优化器提示的优点在于，它仅适用于使用该提示的查询块，因此不必optimizer_switch在执行之后再 更改一次的值 UPDATE。

另一种可能性是重写子查询，使其不使用IN或EXISTS，如下所示：

UPDATE items, 
       (SELECT id, retail / wholesale AS markup, quantity FROM items) 
       AS discounted 
    SET items.retail = items.retail * 0.9                    
    WHERE discounted.markup >= 1.3 
    AND discounted.quantity < 100
    AND items.id = discounted.id;

在这种情况下，默认情况下将实现子查询而不是合并子查询，因此没有必要禁用派生表的合并。