使用MySQL将值与表中的多个列(在一条语句中)匹配 [英] Matching a value to multiple columns (in one statement) from a table using MySQL

问题描述

我正在使用包含以下列的MySQL表:

I'm working with a table in MySQL that contains the following columns:

id, january, february, march, april, etc

表中的数据如下所示:

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

结果将是:

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).

奖金(+1)问题:
可以使用Match/Against这样来完成此操作吗:

Bonus (+1) question:
Could it be done using Match/Against like this:

select * from table
where
(
    match (somehow,get,the,table,columns,I,need,here)
    against (1 in boolean mode)
)

感谢您的宝贵时间！ :)

Thanks for your time! :)

推荐答案

您必须使用预处理语句，因为您要执行的操作只能通过动态SQL来完成:

You have to use a Prepared Statement, because what you want to do can only be done with dynamic 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;

第一个SET语句构造一个基本的SELECT语句； "1 = 1"部分正好在其中，以便于连接"AND column = 1"

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语句将查询的内容连接起来，以根据表名将列的列表获取到第一条SET语句中字符串的末尾.这个想法是这样的:

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:

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

...将返回类似于"AND january = 1 AND february = 1 ..."的行.如果WHERE子句中不需要其他列，则必须更新NOT IN子句.

... 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.

其余只是标准的预准备语句，而所有这些都必须在存储过程中进行.

The rest is just standard prepared statement stuff, and this all would have to take place within a stored procedure.

这篇关于使用MySQL将值与表中的多个列(在一条语句中)匹配的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！