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

注意:我的经验主要是 SQL Server，所以我知道一点，但不是专门的 MySQL。

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

我开始使用简单的方法遍历我的所有键并针对每个表提交每个键的语句，例如:

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 () 语句？ SELECT 123, 456 UNION ALL SELECT 124, 457?我刚刚测试过 MySql 允许这种不针对表发出的 SELECT。但是如果列表太长，SQL Server 最终会崩溃，那么这在 MySQL 中是不是一个好方法？我是否应该一次将列表保持在几百个以内？

  --CREATE Temp Table ( I do not know the syntax in MySql yet)
  
  INSERT INTO TempTable
  SELECT 123, 456
  UNION ALL SELECT 124, 457
  UNION ALL SELECT 125, 458
  
  DELETE T
  FROM
     Table 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 中，我可以编写一些自定义代码来解析字符串并将其转换为行集:

  CREATE PROCEDURE DoStuff @KeyString varchar(max)
  AS
  DECLARE @Keys TABLE (
     Key1 int,
     Key2 int,
     PRIMARY KEY CLUSTERED (Key1, Key2)
  )
  DECLARE @Pos int
  WHILE @Pos < Len(@KeyString) BEGIN
     -- loop to search for delimiting commas in @KeyString
     -- and insert pairs of parsed tokens to table variable @Keys
  END
  
  DELETE T
  FROM
     Table T
     INNER JOIN @Keys K ON T.Key1 = K.Key1 AND T.Key2 = K.Key2
  

由于我不熟悉 MySQL，我真的不知道应该先调查哪种可能性，我希望能得到一些帮助，以免我做出错误的决定和/或艰难地学习。

最佳答案

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

• CREATE TEMPORARY TABLE

  CREATE TEMPORARY TABLE Keys (
      Key1 INT UNSIGNED NOT NULL, 
      Key2 INT UNSIGNED NOT NULL, 
      PRIMARY KEY(Key1, Key2)
  );
  

• 使用 LOAD DATA LOCAL INFILE 将制表符分隔的数据文件加载到临时表中

  LOAD DATA LOCAL INFILE 'C:/path/to/datafile' INTO TABLE Keys;
  

• 利用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 文档说明如下:

A TEMPORARY table is visible only to the current connection, and is dropped automatically when the connection is closed. This means that two different connections can use the same temporary table name without conflicting with each other or with an existing non-TEMPORARY table of the same name. (The existing table is hidden until the temporary table is dropped.)

这很清楚!

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

关于MySql:使用复合 PK 的长列表对多行进行操作，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/2248516/