带有可变列名的 MySQL 选择查询 [英] MySQL select query with variable column name

问题描述

我正在寻找一种最简单的解决方案来根据另一个查询的结果查询一个表.

I am looking for a simplest solution to query one table based on results from another query.

Table 1:
id    rslt  
-----------
1      r1  
2      r2   
3      r3 

Table 2:
  r1  r2  r3  
 ------------------
  a1  b1  c1
  a2  b2  c2
  a2  b3  c3

query: `SELECT rslt FROM table1 WHERE id = 1` will return r1

我希望能够动态地使用它来查询 table2 中的数据，即

I want to be able to use this dynamically to query data from table2 i.e.

SELECT r1 FROM table2

我试过了:

select (SELECT rslt from table1 where id = 1) from table2

即返回 'r1' 而不是 r1 列值 (a1, a2, a3) 中的值

That is returning 'r1' instead of values in r1 column values (a1, a2, a3)

提前致谢

推荐答案

最简单的解决方案是使用两个单独的查询.

The simplest solution is to use two separate queries.

我们使用第一个查询的结果为第二个查询动态生成 SQL 文本.

We use the result from the first query to dynamically generate the SQL text for the second query.

mysql> SET @colname := '' ; 
mysql> SELECT t.rslt FROM table1 t WHERE t.id = 1 ORDER BY t.rslt LIMIT 1 INTO @colname ;

mysql> SET @sql := CONCAT('SELECT `',@colname,'` FROM table2 ORDER BY 1') ;
mysql> PREPARE stmt FROM @sql ; 
mysql> EXECUTE stmt ;

mysql> DEALLOCATE PREPARE stmt ;

注意包括 @colname 作为 SQL 文本的一部分，动态准备的 SQL，是一个潜在的 SQL 注入漏洞.

N.B. including @colname as part of the SQL text, dynamically prepared SQL, is a potential SQL Injection vulnerability.

如果要求是在单个 SQL 语句的上下文中完成类似的操作，则该语句需要预测从 table1 查询返回的可能值，并包含显式引用到 table2 中可能的列.例如，像这样:

If the requirement is to get something similar done in the context of a single SQL statement, then the statement needs to anticipate the possible values to be returned for the query from table1, and include explicit references to the possible columns from table2. For example, something like this:

  SELECT CASE ( SELECT t.rslt FROM table1 t WHERE t.id = 1 LIMIT 1 )
           WHEN 'r1' THEN r.r1 
           WHEN 'r2' THEN r.r2 
           WHEN 'r3' THEN r.r3 
           ELSE NULL
         END AS c2
    FROM table2 r
   ORDER BY ...

这不一定是编写查询的最有效方式，但它演示了模式.

This isn't necessarily the most efficient way to write the query, but it demonstrates the pattern.

在 SQL 语句中，必须明确指定标识符(表名、列名、函数名)；这些不能在运行时动态派生.这是因为 SQL 语句的处理方式...解析 SQL 文本的语法，然后解析语义(有效引用和权限)，评估可用访问路径的相对成本，选择执行计划，然后执行该计划.

Within a SQL statement, the identifiers (table names, column names, function names) must be specified explicitly; these cannot be derived dynamically at run time. This is because of how SQL statements are processed... parsing the SQL text for syntax, then parsing for semantics (valid references and privileges), evaluating relative cost for available access paths, selecting an execution plan, and then executing that plan.

也就是说，使用此 SQL 观察到的行为是我们所期望的:

That is, the behavior observed with this SQL is what we expect:

 SELECT (SELECT rslt FROM table1 WHERE id = 1) FROM table2

SQL 文本已准备好，在执行时，对于table2 中的每一行，都会执行SELECT 列表中的子查询.如果子查询返回标量值，则标量值作为外部查询的列返回.子查询返回的值是一个值，它不是(也不能)被评估为列名.

The SQL text is prepared, and at execution time, for each row in table2, the subquery in the SELECT list is executed. If the subquery returns a scalar value, the scalar value is returned as a column of the outer query. The value returned by the subquery is a value, it is not (and cannot be) evaluated as a column name.

这篇关于带有可变列名的 MySQL 选择查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！