MySQL 语句：数据处理语句（DML）

CALL 语句

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

• CALL语句调用先前用“CREATE PROCEDURE”定义的存储过程。
• 可以不带括号地调用不带参数的存储过程。也就是说，“CALL p()”和“CALL p”是等效的。
• 【见：“CREATE PROCEDURE 和 CREATE FUNCTION 语句”】

CALL可以使用声明为OUT或INOUT参数的参数将值传递回其调用方。当过程返回时，Client 端程序也可以获得在例程中执行的最终语句所影响的行数：在 SQL 级别，调用“ROW_COUNT()”函数；从 C API 中，调用“mysql_affected_rows()”函数。

• 对于“INOUT”参数，在将其值传递给过程之前对其进行初始化。
• 在与“PREPARE”和“EXECUTE”一起使用的已准备好的 CALL 语句中，占位符可用于 IN 参数，OUT 和 INOUT 参数。

示例：以下过程具有一个 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 |
+------------------+------------+

示例：在与 PREPARE 和 EXECUTE 一起使用的已准备好的 CALL 语句中，占位符可用于 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 |
+------------------+------------+

• 关于“CLIENT_MULTI_RESULTS”：【？？？】
  1. 要编写使用CALL SQL 语句执行生成结果集的存储过程的 C 程序，必须启用“CLIENT_MULTI_RESULTS”标志。这是因为，除了过程中执行的语句可能返回的任何结果集之外，每个 CALL 还会返回指示呼叫状态的结果。如果将 CALL 用于执行任何包含准备好的语句的存储过程，则也必须启用“CLIENT_MULTI_RESULTS”。无法确定何时加载此类过程是否会产生这些语句，因此有必要假设它们会产生结果。
  2. 可以在调用“mysql_real_connect()”时启用“CLIENT_MULTI_RESULTS”，或者通过传递“CLIENT_MULTI_RESULTS”标志本身来显式，或者通过“CLIENT_MULTI_RESULTS”隐式传递(也将启用“CLIENT_MULTI_RESULTS”)。 “CLIENT_MULTI_RESULTS”默认启用。
• 要处理使用“mysql_query()”或“mysql_real_query()”执行的 CALL 语句的结果，请使用调用“mysql_next_result()”的循环来确定是否还有更多结果。【？？？】
• C 程序可以使用 Prepared-Statement 接口执行 CALL 语句并访问 OUT 和 INOUT 参数。这是通过使用调用“mysql_stmt_next_result()”的循环以确定是否还有更多结果来处理CALL语句的结果来完成的。【？？？】

DELETE 语句

1. 单表语法：

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

   • DELETE语句从 tbl_name 中删除行，并返回删除的行数。要检查已删除的行数，请调用“ROW_COUNT()”函数。
2. 主要子句：
   1. “WHERE”子句：where_condition 是一个表达式，对于要删除的每一行，其值为 true；
   2. “ORDER BY”子句，则按指定的 Sequences 删除行；
   3. “LIMIT”子句：限制了可以删除的行数。
   • （这些子句适用于单表删除，但不适用于多表删除）
3. 多表语法：

   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]
   

4. 权限：

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

5. 性能：
   • 当不需要知道已删除的行数时，与没有 WHERE 子句的 DELETE 语句相比，“TRUNCATE TABLE”语句是清空表的更快方法。【见：“TRUNCATE TABLE 语句”】
     • 与DELETE不同，TRUNCATE TABLE不能在事务内使用或在表上有锁时不能使用。
   • 为了确保给定的 DELETE 语句不会花费太多时间，针对 DELETE 的特定于 MySQL 的“LIMIT row_count”子句指定了要删除的最大行数。如果要删除的行数大于限制，请重复 DELETE 语句，直到受影响的行数小于 LIMIT 值。【？】
6. 子查询：

   您不能从 table 中删除，也不能从子查询的同一 table 中选择。

7. 分区表支持：

   DELETE 使用“PARTITION”选项支持显式分区选择，该选项采用一个或多个分区或子分区(或两者)的逗号分隔名称列表，从中选择要删除的行。【未包括在列 table 中的分区将被忽略】

   给定具有名为 p0 的分区的分区表 t，执行语句“DELETE FROM t PARTITION (p0)”与执行“ALTER TABLE t TRUNCATE PARTITION (p0)”具有相同的作用；在这两种情况下，都将删除分区 p0 中的所有行。

   • PARTITION 可以与 WHERE 条件一起使用，在这种情况下，仅在列出的分区中的行上测试该条件。
   • PARTITION 选项也可以在多表 DELETE 语句中使用。在 FROM 选项中命名的表中，最多可以使用一个这样的选项。
8. 自动递增列：
   1. 如果删除包含“AUTO_INCREMENT”列最大值的行，则该值不会再用于 MyISAM 或 InnoDB 表。
   2. 如果在 autocommit 模式下使用“DELETE FROM tbl_name”(不带WHERE子句)删除 table 中的所有行，则该序列将从 InnoDB 和 MyISAM 以外的所有存储引擎重新开始。
   3. 对于 MyISAM 表，可以在多列键中指定 AUTO_INCREMENT 个辅助列。在这种情况下，即使对于 MyISAM 表，也会重复使用从序列顶部删除的值。
9. 修饰符：

   DELETE语句支持以下修饰符：

   1. 如果指定“LOW_PRIORITY”修饰符，则服务器将延迟 DELETE 的执行，直到没有其他 Client 端从 table 中读取。
      • 这仅影响仅使用 table 级锁定（例如 MyISAM，MEMORY 和 MERGE）的存储引擎。
   2. 对于 MyISAM 表，如果使用“QUICK”修饰符，则存储引擎在删除期间不会合并索引叶，这可能会加快某些类型的删除操作。
   3. “IGNORE”修饰符使 MySQL 在删除行的过程中忽略错误。（在解析阶段遇到的错误将以通常的方式处理）由于使用 IGNORE 而被忽略的错误将作为警告返回。
10. 删除顺序：

    如果 DELETE 语句包含“ORDER BY”子句，则按该子句指定的 Sequences 删除行。【这主要与“LIMIT”结合使用】

    示例：查找与 WHERE 子句匹配的行，按 timestamp_column 对其进行排序，并删除第一个(最旧的)行：

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

    • ORDER BY 还有助于按照避免引用完整性违规的 Sequences 删除行。
11. InnoDB 表：

    如果要从大 table 中删除许多行，则可能会超出 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 执行时，没有其他会话可以访问涉及的表，因此重命名操作不会出现并发问题。
12. MyISAM 表：【？？？】

    在 MyISAM 表中，删除的行保留在链接列表中，随后的 INSERT 操作重用旧的行位置。要回收未使用的空间并减小文件大小，请使用“OPTIMIZE TABLE”语句或“myisamchk”Util 重新组织表。

    • 【OPTIMIZE TABLE更易于使用，但myisamchk更快】

    “QUICK”修饰符影响索引叶是否合并以进行删除操作。“DELETE QUICK”对于将删除行的索引值替换为以后插入的行的相似索引值的应用程序最有用。在这种情况下，删除值留下的漏洞将被重用。【？】

    当删除的值导致索引块填充不足，该索引块跨越索引值范围而又发生新插入时，“DELETE QUICK”无效。在这种情况下，使用“QUICK”会导致索引中浪费空间而无法回收。这是这种情况的示例：

    1. 创建一个包含索引的 AUTO_INCREMENT 列的表。
    2. 在表中插入许多行。每次插入都会产生一个索引值，该值将添加到索引的高端。
    3. 使用“DELETE QUICK”删除列范围低端的行块。

    在这种情况下，与已删除索引值关联的索引块将被填充不足，但由于使用QUICK而不会与其他索引块合并。当发生新的插入操作时，它们仍会填充不足，因为新行的索引值不在删除范围内。此外，即使您以后使用DELETE而不使用QUICK，它们仍会填充不足，除非某些已删除的索引值恰好位于填充不足的块内或邻近的索引块中。要在这种情况下回收未使用的索引空间，请使用OPTIMIZE TABLE。

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

    您可以在 DELETE 语句中指定多个表，以根据 WHERE 子句中的条件从一个或多个表中删除行。

    • 不能在多表 DELETE 中使用 ORDER BY 或 LIMIT。
    1. 仅删除表中 FROM 子句前列出的“列”的匹配行：

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

    2. 仅删除 FROM 子句中列出的“表”中匹配的行（在 USING 子句之前）：
       • （同时从许多 table 中删除行，并使其他 table 仅用于搜索）

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

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

      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 是“SELECT expr, ...”的简写，但具有以下优点：当您不关心结果时，它会稍快一些。
• DO 主要用于具有副作用的功能，例如“RELEASE_LOCK()”。
• DO 仅执行表达式。不能在所有可以使用 SELECT 的情况下使用。例如，“DO id FROM t1”无效，因为它引用了一个 table。

示例：此 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)

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

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 语句提供对表存储引擎接口的直接访问。【？？？】

• 它可用于InnoDB和MyISAM表。

1. “HANDLER ... OPEN”：

   打开一个 table，使其可以使用后续的“HANDLER ... READ”语句进行访问。

   • 该 table 对象未被其他会话共享，并且在会话调用“HANDLER ... CLOSE”或会话终止之前不会关闭。
   • 如果使用别名打开 table，则其他 HANDLER 语句对打开的 table 的进一步引用必须使用别名而不是 table 名。如果您不使用别名，而是使用数据库名称限定的 table 名称打开，则其他引用必须使用非限定的 table 名称。例如，对于使用 mydb.mytable 打开的 table，其他引用必须使用 mytable。
2. “HANDLER ... READ”：【？】
   1. 第一种：获取一行，其中指定的索引满足给定值，并且满足 WHERE 条件。如果您有多列索引，则将索引列值指定为以逗号分隔的列 table。为索引中的所有列指定值，或者为索引列的最左前缀指定值。

      假设索引 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`”：

      HANDLER tbl_name READ `PRIMARY` ...
      

   2. 第二种：以与 WHERE 条件匹配的索引 Sequences 从 table 中获取一行。
   3. 第三种：以与 WHERE 条件匹配的自然行 Sequences 从 table 中获取一行。

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

   • 如果没有 LIMIT 子句，则所有“HANDLER ... READ”形式都将获取一行。要返回特定数量的行，请包含 LIMIT 子句。它具有与 SELECT 语句相同的语法。
3. “HANDLER ... CLOSE”：关闭使用“HANDLER ... OPEN”打开的 table。

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

1. HANDLER 比 SELECT 快：
2. 为“HANDLER ... OPEN”分配了一个指定的存储引擎处理程序对象。该对象可用于该 table 的后续 HANDLER 语句；它不需要为每个重新初始化。
3. 涉及的解析较少。
4. 没有优化程序或查询检查开销。
5. 处理程序接口不必提供一致的数据外观(例如，允许dirty reads)，因此存储引擎可以使用 SELECT 通常不允许的优化。
6. HANDLER 使得移植到使用类似 ISAM 的底层接口的 MySQL 应用程序更加容易。
7. HANDLER 使您可以用 SELECT 难以(甚至不可能)遍历数据库。当使用为数据库提供交互式用户界面的应用程序时，HANDLER 界面是查看数据的更自然的方式。

HANDLER是一个较低级别的语句。例如，它不提供一致性。也就是说，“HANDLER ... OPEN”不会 为 table 创建快照，并且不会锁定 table。这意味着发出“HANDLER ... OPEN”语句后，可以修改 table 数据(通过当前会话或其他会话)，并且这些修改对于“HANDLER ... NEXT”或“HANDLER ... PREV”扫描可能仅部分可见。

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

1. 任何会话都在处理程序的 table 上执行“FLUSH TABLES”或 DDL 语句。
2. 打开处理程序的会话将执行使用 table 的非 HANDLER 语句。

表格的“TRUNCATE TABLE”关闭使用“HANDLER OPEN”打开的表格的所有处理程序。

• 如果用“FLUSH TABLES tbl_name WITH READ LOCK”刷新的 table 是用 HANDLER 打开的，则处理程序将隐式刷新，并丢失其位置。

【子查询】

• 见：“MySQL 语句：数据处理语句（DML）：子查询”