当列值匹配约束时，mysql获取列名 [英] mysql get column name when column value matches constraint

问题描述

我正在尝试从表中获取列名.我想提供行 id，我只想要特定行的该列的值(由 id 标识)为true"(我的表有一堆布尔字段)的列名.

I'm trying to get column names from a table. I want to supply the row id and I want only the column names for which the value of that column for the specific row (identified by the id) is "true" (my table has a bunch of boolean fields).

我想要类似的东西:

SELECT COLUMN_NAME 
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE COLUMN_NAME.value = true
        AND TABLE_THE_COLUMN_IS_FROM.id = "some_id"

其中 .value 是可变的，基本上检查每一列以查看它是否为真.

Where .value would be variable, basically checking each column to see if it were true.

我知道我可以获取行的值并进行迭代，只返回那些值为 true 的值，但我想看看是否有一种方法可以一步完成所有操作.在此先感谢知道的人！

I know I can just get the row's values and iterate through, returning only those with value true, but I wanted to see if there is a way to do it all in one step. Thanks in advance to anyone who knows!

推荐答案

没有办法在一个查询中动态扫描表的架构并检查其值.实现您想要的最佳方法是您建议的方法:查询行客户端，然后循环搜索您要查找的值的列.另一种替代方法是使用客户端的 INFORMATION SCHEMA 视图查询表模式，使用 where 子句构建 SQL 语句，在所有布尔列中查找 True 值，执行该语句并检查结果.

There is no means in one query to dynamically scan through the table's schema and inspect its values. The best way to achieve what you want is the one you suggested: query for the row client-side and then cycle through the columns searching for the values you seek. The other alternative is to query for the table schema using the INFORMATION SCHEMA views client-side, build a SQL statement with a where clause that looks for a True value in all the boolean columns, execute that and inspect the results.

这篇关于当列值匹配约束时，mysql获取列名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！