使用 MySQL 将一个值与表中的多个列（在一个语句中）匹配
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1372860/
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
Matching a value to multiple columns (in one statement) from a table using MySQL
提问by Mr. Smith
I'm working with a table in MySQL that contains the following columns:
我正在使用 MySQL 中的一个表，其中包含以下列：
id, january, february, march, april, etc
The data in the table looks like this:
表中的数据如下所示：
aa, 0, 0, 1, 0
ab, 1, 0, 1, 0
ac, 1, 1, 0, 0
ad, 1, 1, 1, 0
To query it, I could easily do this:
要查询它，我可以轻松地做到这一点：
select * from table where january = 1 and february = 1
The result would be:
结果将是：
ac, 1, 1, 0, 0
ad, 1, 1, 1, 0
I want to know if there's a way to do it like this:
我想知道是否有办法做到这一点：
select * from table where table.columns = 1
I want to use table columns in expression without actually specifying the names manually (typing them out).
我想在表达式中使用表列而不实际手动指定名称（输入它们）。
Bonus (+1) question:
Could it be done using Match/Against like this:
奖励 (+1) 问题：
是否可以像这样使用 Match/Against 来完成：
select * from table
where
(
match (somehow,get,the,table,columns,I,need,here)
against (1 in boolean mode)
)
Thanks for your time! :)
谢谢你的时间！:)
采纳答案by OMG Ponies
You have to use a Prepared Statement, because what you want to do can only be done with dynamic SQL:
你必须使用准备好的语句，因为你想要做的只能用动态 SQL 来完成：
SET @stmt = 'SELECT * FROM YOUR_TABLE WHERE 1 = 1 '
SET @stmt = CONCAT(@stmt, (SELECT CONCAT_WS(' AND ', CONCAT(column_name, ' = 1 '))
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_name = 'YOUR_TABLE'
AND table_schema = 'db_name'
AND column_name NOT IN ('id')));
PREPARE stmt FROM @stmt;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
The first SET statement constructs a basic SELECT statement; the "1 = 1" portion is just there to make it easier to concatenate the "AND column = 1"
第一个 SET 语句构造了一个基本的 SELECT 语句；“1 = 1”部分只是为了更容易连接“ AND column = 1”
The second SET statement concatenates the contents of the query to get the list of columns based on the table name onto the end of the string in the first SET statement. The idea is that this:
第二个 SET 语句连接查询的内容以获取基于表名的列列表到第一个 SET 语句中字符串的末尾。这个想法是：
SELECT CONCAT_WS(' AND ', CONCAT(column_name, ' = 1 '))
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_name = 'YOUR_TABLE'
AND table_schema = 'db_name'
AND column_name NOT IN ('id')
... will return a row that resembles "AND january = 1 AND february = 1 ...". You'd have to update the NOT IN clause if there are other columns you don't want in the WHERE clause.
...将返回类似于“ AND january = 1 AND february = 1 ...”的行。如果 WHERE 子句中有您不想要的其他列，则必须更新 NOT IN 子句。
The rest is just standard prepared statement stuff, and this all would have to take place within a stored procedure.
其余的只是标准的准备好的语句内容，这一切都必须在存储过程中进行。
回答by Lukasz Lysik
Try to create view
尝试创建视图
CREATE VIEW v_months
AS
SELECT *, CONCAT( CAST(jan AS CHAR(1)),
CAST(feb AS CHAR(1)),
CAST(mar AS CHAR(1)),
...) AS all
FROM months
You will get something like this:
你会得到这样的东西：
aa, 0, 0, 1, 0, 0010
ab, 1, 0, 1, 0, 1010
ac, 1, 1, 0, 0, 1100
ad, 1, 1, 1, 0, 1110
And then you can query
然后你可以查询
SELECT * FROM v_months WHERE all = '100110010101'
Or if you want query "get all rows, where feb = 1", you can write like this:
或者，如果您想查询“获取所有行，其中 feb = 1”，您可以这样写：
SELECT * FROM v_months WHERE all LIKE '_1____________'
where '_' matches exactly one character.
其中“_”正好匹配一个字符。
回答by Jason246
I understand what Mr.Smith is trying to do.
我明白史密斯先生想做什么。
It's a question of 12 rows x 4 columns vs. 1 row x 12 columns.
这是一个 12 行 x 4 列与 1 行 x 12 列的问题。
The former table design would be something like:
以前的表格设计类似于：
id, someone's id, month, value x 12 per month
id，某人的 id，月份，价值 x 12 每月
1, 101, january, 1
2, 101, february, 1
3, 101, march, 0
etc..
The corresponding sql statement to this would be:
对应的 sql 语句为：
$sqlQuery = "SELECT month FROM my_month_table WHERE value = 1";
What I'm guessing Mr.Smith is trying:
我猜史密斯先生正在尝试的是：
id, someone's id, january, february, march ...
id，某人的id，一月，二月，三月...
$sqlQuery = "SELECT corrensponding_column_names_to_where_clause FROM my_month_table WHERE column_value = 1";
回答by Sadat
You can do it using match...against.
您可以使用 match...against 来做到这一点。
In that case your table must be MyISAM table and you must create FULLTEXT index including required columns.
在这种情况下，您的表必须是 MyISAM 表，并且您必须创建包含所需列的 FULLTEXT 索引。
回答by Blama
How about solving the problem with a VIEW?
用 VIEW 解决问题怎么样？
CREATE TABLE `d001ab05`.`months` (
`id` INT NOT NULL ,
`jan` BOOL NOT NULL ,
`feb` BOOL NOT NULL ,
`mar` BOOL NOT NULL ,
`apr` BOOL NOT NULL ,
`may` BOOL NOT NULL ,
`jun` BOOL NOT NULL ,
`jul` BOOL NOT NULL ,
`aug` BOOL NOT NULL ,
`sep` BOOL NOT NULL ,
`oct` BOOL NOT NULL ,
`nov` BOOL NOT NULL ,
`dec` BOOL NOT NULL ,
PRIMARY KEY ( `id` )
) ENGINE = InnoDB;
Insert data:
插入数据：
INSERT INTO `d001ab05`.`months` (
`id`, `jan`, `feb`, `mar`, `apr`, `may`, `jun`,
`jul`, `aug`, `sep`, `oct`, `nov`, `dec`
) VALUES (
'1', '1', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0'
), (
'2', '1', '1', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0'
), (
'3', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0'
), (
'4', '1', '1', '1', '1', '1', '1', '1', '1', '1', '1', '1', '1'
);
View with Select:
选择查看：
CREATE OR REPLACE VIEW `moncase` AS
SELECT id,
CASE WHEN `jan` =1 OR `feb` =1 OR `mar` =1 OR `apr` =1 OR `may` =1 OR
`jun` =1 OR `jul` =1 OR `aug` =1 OR `sep` =1 OR `oct` =1 OR `nov` =1 OR `dec` =1
THEN 1 ELSE 0 END AS or_over_months
FROM months;
Select:
选择：
SELECT * FROM `moncase` WHERE `or_over_months` = 1;
Result:
结果：
id | or_over_months
1 | 1
2 | 1
4 | 1
回答by Bill Karwin
What you need is an un-pivot operation. Microsoft SQL Server supports PIVOTand UNPIVOTas extensions to standard SQL. I don't know of any other brand of RDBMS that supports built-in pivot/unpivot functionality. Certainly MySQL does not support this.
您需要的是非枢轴操作。Microsoft SQL Server 支持PIVOT并UNPIVOT作为标准 SQL 的扩展。我不知道支持内置数据透视/逆透视功能的任何其他品牌的 RDBMS。当然 MySQL 不支持这个。
You can't use FULLTEXTsearch in this case, because as the docs say:
FULLTEXT在这种情况下您不能使用搜索，因为正如文档所说：
Full-text indexes can be used only with MyISAM tables, and can be created only for CHAR, VARCHAR, or TEXT columns.
全文索引只能用于 MyISAM 表，并且只能为 CHAR、VARCHAR 或 TEXT 列创建。
And it wouldn't relieve you from needing to specify the columns anyway, because the MATCH()predicate needs you to list allcolumns in the fulltext index.
无论如何，它不会让您无需指定列，因为MATCH()谓词需要您列出全文索引中的所有列。
If you can't restructure the table to store a row-per-month or a single column to encode all 12 months, then you do need to generate dynamic SQL.
如果您无法重构表以存储每月一行或单个列来编码所有 12 个月，那么您确实需要生成动态 SQL。
回答by pingw33n
You can use vectors in MySQL:
你可以在 MySQL 中使用向量：
select * from table where (january, february) = (1, 1)
Beware of different server and client collations for text columns, possibly you'll need to specify collation explicitly then.
当心文本列的不同服务器和客户端排序规则，然后您可能需要明确指定排序规则。
回答by Jay
In my opinion this is caused from what appears to be poor table design (I am guessing that is just an example to simply your problem, but bear with me, and I think this concept could be modeled better.
Here are the tables:
在我看来，这是由看起来很糟糕的表格设计引起的（我猜这只是您问题的一个例子，但请耐心等待，我认为这个概念可以更好地建模。
以下是表格：
Things Things_Months Months
thing_id thing_id Month_id
thing_info month_id Month_Name
thing_month_id order_field
and here would be the sql:
这将是 sql：
select thing_info, month_name
from things, things_months, months
where things.thing_id = things_months.thing_id
and things_months.month_id = months.month_id
order by things.things_info, months.order_field
The results would be
结果将是
thingy 1, January
thingy 1, February
thingy 2, April
thingy 3, November
thingy 3, December
回答by longneck
select * from table where 1 in (january, february)
