MySQL 错误：在简单的 INSERT 语句上查询为空 (1065)
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1325348/
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
MySQL Error: Query was empty (1065) on simple INSERT statement
提问by pkaeding
I am trying to run a large script that creates a table, and then inserts almost 15,000 rows into it. The table gets created just fine, and then at the 833 INSERT, I get an error:
我正在尝试运行一个创建表的大型脚本，然后向其中插入近 15,000 行。该表创建得很好，然后在 833 INSERT 处，我收到一个错误：
Error: Query was empty (1065)
Here is my 833rd INSERT statement (the one that is failing):
这是我的第 833 条 INSERT 语句（失败的语句）：
INSERT INTO CLASSCODE (CLASS_CODE, CLASS_CODE_NAME, RATE_GROUP, PROGRAM_NM, ST_CODE, EFF_DT, EXP_DT) VALUES (10255, "Funeral Directors - incl PL other than Crematory - 10255", 3, "Service", "AZ", 19980801, NULL);
I can't see any syntax errors or differences between this line, and one that works. FOr reference, here is an example of an INSERT statement that works just fine:
我看不到这一行与有效行之间的任何语法错误或差异。作为参考，这里是一个工作正常的 INSERT 语句示例：
INSERT INTO CLASSCODE (CLASS_CODE, CLASS_CODE_NAME, RATE_GROUP, PROGRAM_NM, ST_CODE, EFF_DT, EXP_DT) VALUES (10425, "Frame Shop - Picture/Posters - 10425", 2, "Retail", "AZ", 19980801, NULL);
The part that puzzles me is that the error sounds like something that would happen if I was populating the new row using data from another SELECT statement, which was coming up empty. That is not the case, though, as my INSERT statements are all using static data.
令我困惑的部分是，如果我使用来自另一个 SELECT 语句的数据填充新行，该错误听起来会发生什么，该语句为空。但是，情况并非如此，因为我的 INSERT 语句都使用静态数据。
My table definition looks like this:
我的表定义如下所示：
CREATE TABLE CLASSCODE (
CLASS_CODE INTEGER NOT NULL,
CLASS_CODE_NAME VARCHAR(60) NOT NULL,
RATE_GROUP SMALLINT NOT NULL,
PROGRAM_NM VARCHAR(20) NOT NULL,
ST_CODE CHAR(2),
EFF_DT DATE,
EXP_DT DATE)
I am running this script in the GUI MySQL Query Browser.
我在 GUI MySQL 查询浏览器中运行这个脚本。
Could it be something to do with the number of rows I'm trying to insert? Do I need to periodically commit? Is there something simple that I am just overlooking?
这可能与我尝试插入的行数有关吗？我需要定期提交吗？有什么简单的东西我只是忽略了吗？
Thanks!
谢谢！
回答by zombat
The most common scenario to run into these in a script file is when you have a double semi-colon somewhere:
在脚本文件中遇到这些的最常见情况是当您在某处有双分号时：
INSERT INTO CLASSCODE
(CLASS_CODE, CLASS_CODE_NAME, RATE_GROUP, PROGRAM_NM, ST_CODE, EFF_DT, EXP_DT)
VALUES
(10255, "Funeral Directors - incl PL other than Crematory - 10255", 3, "Service", "AZ", 19980801, NULL)
;;
I'd do a quick search through the script and see if there's a ;;in there around line 833.
我会快速搜索脚本，看看;;第 833 行附近是否有一个。
回答by Scott
I would suggest running the sql from the command line in a terminal or SSH session. The gui interface may be interfering with or limiting your ability to run the large number of inserts.
我建议在终端或 SSH 会话中从命令行运行 sql。gui 界面可能会干扰或限制您运行大量插入的能力。
回答by paxdiablo
Since the other inserts are working okay and there doesn't appear to be a difference in the structure of them, my first thought is that you're running out of some resource at insert number 833.
由于其他插入工作正常，并且它们的结构似乎没有区别，我的第一个想法是您在插入编号 833 处的某些资源用完了。
What happens if you do a commit after each insert (your transaction buffer may be running out of space)? Does it work then? Actually, a better test would be a single commit at transaction number 800. If you then get beyond 833, then that was your problem.
如果您在每次插入后进行提交（您的事务缓冲区可能空间不足）会发生什么？那它有效吗？实际上，更好的测试是在事务号 800 处进行单次提交。如果超过 833，那就是你的问题。
If so, simply modify your script (or the script that creates your script id it's auto-generated) to commit every 100 or so inserts (committing after every insert will probably make it unbearably slow).
如果是这样，只需修改您的脚本（或创建脚本 ID 的脚本，它是自动生成的）以每 100 个左右的插入提交一次（在每次插入后提交可能会使其慢得难以忍受）。
回答by Brent Writes Code
In case you're worried about your actual query syntax, it seems fine. I created your table and ran the insert and it worked. When you say you're running a script, do you just have a file full of raw SQL that you're running through? The times I've run into the "Empty Query" type error before have been when people are using JDBC-type stuff and have their looping logic messed up.
如果您担心您的实际查询语法，这似乎没问题。我创建了你的表并运行了插入，它工作正常。当您说您正在运行一个脚本时，您是否只有一个充满您正在运行的原始 SQL 的文件？我之前遇到过“空查询”类型错误的时候是当人们使用 JDBC 类型的东西并且他们的循环逻辑混乱时。
I think we can safely say it's not that particular insert statement that you pasted in, it's got to be something server related or syntax around that query in the script. The rows should be getting commited automatically unless you've reconfigured something. You can try running "show variables;" from the mysql command line and appending that here. We can then take a look at your setup and see if anything looks weird.
我认为我们可以有把握地说这不是您粘贴的那个特定的插入语句，它必须是与服务器相关的内容或脚本中该查询的语法。除非您重新配置了某些内容，否则这些行应该自动提交。您可以尝试运行“show variables;” 从 mysql 命令行并将其附加到此处。然后我们可以查看您的设置，看看是否有任何奇怪的地方。
Have you tried re-ordering some of your inserts in the file? That will at least tell us if it's failing on a particular query or a particular spot in the file. Try reordering your inserts just to see if it still fails about 833 in or if it fails in a different place. If it falls in the same place, it sounds like a memory or buffer-related thing. If it changes places when you move it, I'd say you've got a stray character (a ; or a . maybe) laying around somewhere like @zombat said.
您是否尝试过重新排序文件中的某些插入内容？这至少会告诉我们它是否在特定查询或文件中的特定位置失败。尝试重新排序您的插入，看看它是否仍然在大约 833 处失败，或者是否在不同的地方失败。如果落在同一个地方，听起来像是内存或缓冲区相关的东西。如果它在你移动它时改变了位置，我会说你有一个流浪角色（一个 ; 或一个 . 也许）像@zombat 所说的那样躺在某处。
Hope that helps. Let us know what you can.
希望有帮助。让我们知道你能做什么。
回答by acdarekar
I encountered the same error today. Apparently, when I was using prepared statements for PDO:MySql, the table was having null data while querying.
我今天遇到了同样的错误。显然，当我为 PDO:MySql 使用准备好的语句时，该表在查询时有空数据。
I had to rectify all the missing data from the table.
我不得不纠正表中所有缺失的数据。
