MySQL 语法错误消息“操作数应包含 1 列”
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/456644/
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 Syntax error message "Operand should contain 1 column(s)"
提问by Elie
I tried running the following statement:
我尝试运行以下语句：
INSERT INTO VOUCHER (VOUCHER_NUMBER, BOOK_ID, DENOMINATION)
SELECT (a.number, b.ID, b.DENOMINATION)
FROM temp_cheques a, BOOK b
WHERE a.number BETWEEN b.START_NUMBER AND b.START_NUMBER+b.UNITS-1;
which, as I understand it, should insert into VOUCHER each record from temp_cheques with the ID and DENOMINATION fields corresponding to entries in the BOOK table (temp_cheques comes from a database backup, which I'm trying to recreate in a different format). However, when I run it, I get an error:
据我了解，它应该将 temp_cheques 中的每条记录插入到凭证中，ID 和 DENOMINATION 字段对应于 BOOK 表中的条目（temp_cheques 来自数据库备份，我试图以不同的格式重新创建）。但是，当我运行它时，出现错误：
Error: Operand should contain 1 column(s)
SQLState: 21000
ErrorCode: 1241
I'm running this in SQuirrel and have not had issues with any other queries. Is there something wrong with the syntax of my query?
我在 SQuirrel 中运行它并且没有遇到任何其他查询的问题。我的查询语法有问题吗？
EDIT:
编辑：
The structure of BOOK is:
书的结构是：
ID int(11)
START_NUMBER int(11)
UNITS int(11)
DENOMINATION double(5,2)
The structure of temp_cheques is:
temp_cheques 的结构是：
ID int(11)
number varchar(20)
回答by lc.
Try removing the parenthesis from the SELECT clause. From Microsoft TechNet, the correct syntax for an INSERT statement using a SELECT clause is the following.
尝试从 SELECT 子句中删除括号。在Microsoft TechNet 中，使用 SELECT 子句的 INSERT 语句的正确语法如下。
INSERT INTO MyTable (PriKey, Description)
SELECT ForeignKey, Description
FROM SomeView
The error you're getting, "The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET SQL_MAX_JOIN_SIZE=# if the SELECT is okay.", is actually correct, assuming you have many rows in both BOOK and temp_cheques. You are trying to query all rows from both tables and make a cross-reference, resulting in an m*n size query. SQL Server is trying to warn you of this, before performing a potentially long operation.
你得到的错误，“SELECT 会检查超过 MAX_JOIN_SIZE 的行；检查你的 WHERE 并使用 SET SQL_BIG_SELECTS=1 或 SET SQL_MAX_JOIN_SIZE=# 如果 SELECT 没问题。”，实际上是正确的，假设你有很多行BOOK 和 temp_cheques。您正在尝试从两个表中查询所有行并进行交叉引用，从而产生 m*n 大小的查询。在执行可能较长的操作之前，SQL Server 会尝试警告您这一点。
Set SQL_BIG_SELECTS= 1 before running this statement, and try again. It should work, but note that this operation may take a long time.
SQL_BIG_SELECTS在运行此语句之前设置= 1，然后再试一次。它应该可以工作，但请注意，此操作可能需要很长时间。
回答by shahkalpesh
Does B contain the UNITS column?
B 是否包含 UNITS 列？
What is the table structure for temp_cheques and Book?
temp_cheques 和 Book 的表结构是什么？
EDIT: As I said in comments, all the columns should be numeric when doing +/- and when comparing.
Does the following simple SELECT work?
编辑：正如我在评论中所说，在执行 +/- 和比较时，所有列都应该是数字。
以下简单的 SELECT 有效吗？
SELECT b.START_NUMBER+b.UNITS-1 FROM Books B
SELECT b.START_NUMBER+b.UNITS-1 FROM Books B
回答by RJHunter
I don't have a MySQL instance handy, but my first guess is the WHERE clause:
我手边没有 MySQL 实例，但我的第一个猜测是 WHERE 子句：
WHERE a.number BETWEEN b.START_NUMBER AND b.START_NUMBER+b.UNITS-1;
I imagine that the MySQL parser may be interpreting that as:
我想 MySQL 解析器可能会将其解释为：
WHERE number
(BETWEEN start_number AND start_number) + units - 1
Try wrapping everything in parentheses, ie:
尝试将所有内容都括在括号中，即：
WHERE a.number BETWEEN b.START_NUMBER AND (b.START_NUMBER + b.UNITS - 1);
回答by Elie
The final version of the query is as follows:
查询的最终版本如下：
Set SQL_BIG_SELECTS = 1;
INSERT INTO VOUCHER (VOUCHER_NUMBER, BOOK_ID, DENOMINATION)
SELECT a.number, b.ID, b.DENOMINATION
FROM temp_cheques a, BOOK b
WHERE a.number BETWEEN b.START_NUMBER AND (b.START_NUMBER+b.UNITS-1);
The parsing of the BETWEEN statement required parentheses, the SELECT did not, and because of the size of the two tables (215000 records in temp_cheques, 8000 in BOOK) I was breaking a limit on the select size, requiring me to set SQL_BIG_SELECTS = 1.
BETWEEN 语句的解析需要括号，SELECT 不需要，并且由于两个表的大小（temp_cheques 中有 215000 条记录，BOOK 中有 8000 条记录）我打破了对选择大小的限制，需要我设置 SQL_BIG_SELECTS = 1 .
回答by Pim Hazebroek
I ran into the same error when using Spring Repositories.
我在使用 Spring Repositories 时遇到了同样的错误。
My repository contained a method like:
我的存储库包含一个方法，如：
List<SomeEntity> findAllBySomeId(List<String> ids);
This is working fine when running integration tests against an in-memory database (h2). However against a stand alone database like MySql is was failing with the same error.
在针对内存数据库 (h2) 运行集成测试时，这工作正常。然而，对于像 MySql 这样的独立数据库，失败并出现同样的错误。
I've solved it by changing the method interface to:
我已经通过将方法接口更改为：
List<someEntity findBySomeIdIn(List<String> ids);
Note: there is no difference between findand findAll. As described here: Spring Data JPA difference between findBy / findAllBy
注意：find和之间没有区别findAll。如此处所述： Spring Data JPA difference between findBy / findAllBy
