mysql 表格多个字段同时乘以某一个值,使用MySQL将值与表中的多个列（在一条语句中）匹配...

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

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:

select * from table

where

(

match (somehow,get,the,table,columns,I,need,here)

against (1 in boolean mode)

)

Thanks for your time! :)

解决方案

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;

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"

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

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