MySql：使用长组合 PK 列表对多行进行操作

Question

考虑到我在与 ODBC 连接的客户端应用程序中有一长串键，那么在 MySql 中处理多行的好方法是什么？

注意：我的经验主要是 SQL Server，所以我了解一点，但不是专门了解 MySQL。

任务是从 9 个表中删除一些行，但我可能有超过 5,000 个密钥对。

我从简单的方法开始，循环遍历所有键并针对每个表为每个键提交一条语句，例如：

DELETE FROM Table WHERE Key1 = 123 AND Key2 = 567 -- and 8 more tables
DELETE FROM Table WHERE Key1 = 124 AND Key2 = 568 -- and 8 more tables
DELETE FROM Table WHERE Key1 = 125 AND Key2 = 569 -- and 8 more tables
...

除了，这会产生 45,000 个单独的语句，您可以想象这有点慢。

那么，不用担心我在前端使用的编程语言，有什么好方法可以提交列表，以便我可以加入并立即或至少批量执行操作？到目前为止，我的想法如下：

• 创建一个临时表并插入其中，然后加入。我很乐意查找 MySQL 的语法来创建临时表，但是这是一个好的方法吗？

• 假设我确实使用临时表，填充临时表的最佳方法是什么？ 5000 个 INSERT Table VALUES () 语句？ 选择 123, 456 联合所有选择 124, 457？我刚刚测试了 MySql 允许这种不针对表发出的 SELECT。但是如果列表太长，SQL Server 最终会崩溃，那么这在 MySQL 中是一个好方法吗？我应该一次将列表保持在几百个吗？

  --CREATE Temp Table（我还不知道MySql中的语法）
  
  插入临时表
  选择 123, 456
  联合所有选择 124, 457
  联合所有选择 125, 458
  
  删除T
  从
     药片
     INNER JOIN TempTable X ON T.Key1 = X.Key1 AND T.Key2 = X.Key2
  

• XML。我发现 MySQL 5.1 有一些 XML 函数，但从粗略搜索来看，它似乎不支持将 XML 文本块转换为行集以进行连接。这是真的吗？对我来说，将值转换为 XML 非常容易。

• 虚拟分割操作。我认为在 MySql 中可能存在某种过程语言。在 SQL Server 中，我可以编写一些自定义代码来解析字符串并将其转换为行集：

  创建过程 DoStuff @KeyString varchar(max)
  作为
  声明 @Keys 表 (
     键 1 整数，
     键2整数，
     主键聚集（Key1、Key2）
  ）
  声明@Pos int
  而@Pos < Len(@KeyString) 开始
     -- 循环搜索@KeyString 中的分隔逗号
     -- 并将已解析的标记对插入表变量@Keys
  结尾
  
  删除T
  从
     药片
     内连接 @Keys K ON T.Key1 = K.Key1 AND T.Key2 = K.Key2
  

由于我不熟悉 MySQL，我真的不知道首先要调查哪种可能性，并且我希望得到一些帮助来帮助我做出错误的决定和/或以艰难的方式学习。

Answer 1

我将使用临时表解决方案，并将其连接到 DELETE 语句中的每个主表。因此，您只需执行九次删除，每个表一次。

• 创建临时表< /a>

  创建临时表键（
      Key1 INT 无符号非空， 
      Key2 INT 无符号非空， 
      主键（键1，键2）
  ）；
  

• 使用 加载数据本地INFILE

  将数据本地 INFILE 'C:/path/to/datafile' 加载到表键中；
  

• 使用MySQL的多表删除DELETE 语法。

  DELETE t FROM Table1 t JOIN Keys USING (Key1, Key2);
  DELETE t FROM Table2 t JOIN Keys USING (Key1, Key2);
  DELETE t FROM Table3 t JOIN Keys USING (Key1, Key2);
  DELETE t FROM Table4 t JOIN Keys USING (Key1, Key2);
  DELETE t FROM Table5 t JOIN Keys USING (Key1, Key2);
  DELETE t FROM Table6 t JOIN Keys USING (Key1, Key2);
  DELETE t FROM Table7 t JOIN Keys USING (Key1, Key2);
  DELETE t FROM Table8 t JOIN Keys USING (Key1, Key2);
  DELETE t FROM Table9 t JOIN Keys USING (Key1, Key2);
  

---

回复您的评论：

CREATE TABLE 上的 MySQL 文档说明如下：

临时表仅对当前连接可见，并在连接关闭时自动删除。这意味着两个不同的连接可以使用相同的临时表名称，而不会相互冲突或与现有的同名非 TEMPORARY 表发生冲突。 （现有表将被隐藏，直到临时表被删除。）

这非常清楚！

关于加载数据，您可以使用 INSERT 来完成。 5000 行没什么大不了的。我使用 PHP 脚本将数百万行（例如 StackOverflow XML 数据转储）加载到 MySQL 中，这只需要大约 20 分钟。我使用准备好的语句，然后使用参数执行它。