查询执行被中断，错误#1317 [英] Query execution was interrupted, error #1317

问题描述

我有一张桌子，桌子上有一堆产品(在这种情况下是书).我的销售点系统生成的报告包含ISBN(唯一产品编号)和永久销售.

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与另一个表中的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.

这大约需要处理30,000种产品.

This needs to be done for about 30,000 products.

这是我正在使用的SQL语句:

Here is the SQL statement that I am using:

UPDATE `inventory`,`sales` 
   SET `inventory`.`numbersold` = `sales`.`numbersold` 
 WHERE `inventory`.`isbn` = `sales`.`isbn`;

我收到MySQL错误:

I am getting MySQL Error:

#1317 SQLSTATE:70100(ER_QUERY_INTERRUPTED)查询执行被中断

#1317 SQLSTATE: 70100 (ER_QUERY_INTERRUPTED) Query execution was interrupted

我正在使用GoDaddy.com提供的phpMyAdmin

I am using phpMyAdmin provided by GoDaddy.com

推荐答案

我可能来得有点晚，但是...当然，查询似乎由于执行时间限制而中断.解决这个问题可能没有简单的方法，但是有两个想法:

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:

确保已索引inventory.isbn和sales.isbn.如果不是这样，添加索引将大大减少您的执行时间.

Make sure that inventory.isbn and sales.isbn are indexed. If they aren't, adding an index will reduce your execution time dramatically.

如果这不起作用，请将查询细分为多个块，然后运行几次:

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';

AND子句将搜索限制为以数字1开头的ISBN.对每个数字从"0"到"9"运行查询.对于ISBN，您可能会发现选择最后一个字符会带来更好的结果.使用substring(库存.sales，-1)`

The AND clause 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)`

这篇关于查询执行被中断，错误#1317的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！