您必须使用Prepared Statement,因为您要执行的操作只能通过动态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
”的连接更容易
第二条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')
…将返回类似于“ AND january = 1 AND february = 1 ...
”的行。如果WHERE子句中不需要其他列,则必须更新NOT IN子句。
其余的只是标准的准备好的语句,所有这些都必须在存储过程中进行。