MySQL 查询执行被中断，错误 #1317
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17354229/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Query execution was interrupted, error #1317
提问by PTC
What I have is a table with a bunch of products (books, in this case). My point-of-sale system generates me a report that has the ISBN (unique product number) and perpetual sales.
我有一张桌子，上面放着一堆产品（在这种情况下是书）。我的销售点系统为我生成一份报告，其中包含 ISBN（唯一产品编号）和永久销售。
I basically need to do an update that matches the ISBN from one table with the ISBN from the other and then add the sales from the one table to the other.
我基本上需要进行更新，将一张表中的 ISBN 与另一张表中的 ISBN 匹配，然后将一张表中的销售额添加到另一张表中。
This needs to be done for about 30,000 products.
这需要为大约 30,000 种产品完成。
Here is the SQL statement that I am using:
这是我正在使用的 SQL 语句：
UPDATE `inventory`,`sales`
SET `inventory`.`numbersold` = `sales`.`numbersold`
WHERE `inventory`.`isbn` = `sales`.`isbn`;
I am getting MySQL Error:
我收到 MySQL 错误：
#1317 SQLSTATE: 70100 (ER_QUERY_INTERRUPTED) Query execution was interrupted
#1317 SQLSTATE: 70100 (ER_QUERY_INTERRUPTED) 查询执行被中断
I am using phpMyAdmin provided by GoDaddy.com
我正在使用 GoDaddy.com 提供的 phpMyAdmin
回答by PTC
I've probably come to this a bit late, but... It certainly looks like the query is being interrupted by an execution time limit. There may be no easy way around this, but here's a couple of ideas:
我可能有点晚了，但是......看起来查询确实被执行时间限制中断了。可能没有简单的方法来解决这个问题，但这里有几个想法：
Make sure that inventory.isbnand sales.isbnare indexed. If they aren't, adding an index will reduce your execution time dramatically.
确保inventory.isbn和sales.isbn已编入索引。如果不是，添加索引将大大减少您的执行时间。
if that doesn't work, break the query down into blocks and run it several times:
如果这不起作用，请将查询分解为块并运行几次：
UPDATE `inventory`,`sales`
SET `inventory`.`numbersold` = `sales`.`numbersold`
WHERE `inventory`.`isbn` = `sales`.`isbn`
AND substring(`inventory`.sales`,1,1) = '1';
The ANDclause restricts the search to ISBNs starting with the digit 1. Run the query for each digit from '0' to '9'. For ISBNs you might find selecting on the last character gives better results. Use substring(inventory.sales,-1)`
该AND子句将搜索限制为以数字 1 开头的 ISBN。对从“0”到“9”的每个数字运行查询。对于 ISBN，您可能会发现选择最后一个字符会得到更好的结果。使用substring(库存.sales，-1)`
回答by Макс Ляпцев
UPDATE inventory,salesSET inventory.numbersold= sales.numbersoldWHERE inventory.isbn= sales.isbnAND inventory.id< 5000
更新inventory，sales设置inventory。numbersold= sales. numbersold哪里inventory。isbn= sales. isbn和inventory。id< 5000
UPDATE inventory,salesSET inventory.numbersold= sales.numbersoldWHERE inventory.isbn= sales.isbnAND inventory.id> 5000 inventory.id< 10000
更新inventory，sales设置inventory。numbersold= sales. numbersold哪里inventory。isbn= sales. isbn和inventory。id> 5000 inventory。id< 10000
...
...
If the error, you can try to reduce the number to 1000, for example
如果报错，可以尝试将数量减少到1000，例如
回答by echo_Me
try to use INNER JOIN in the two tables like that
尝试在这样的两个表中使用 INNER JOIN
UPDATE `inventory`
INNER JOIN `sales`
ON `inventory`.`isbn` = `sales`.`isbn`
SET `inventory`.`numbersold` = `sales`.`numbersold`
