MySql中的动态列名称选择 [英] dynamic column name selection in MySql

问题描述

我今天在Mysql中陷入困境，在mysql select语句中需要使用动态列名.让我解释一下:

I am stuck in Mysql today with the dynamic column name need in mysql select statement. Let me explain:

sql> select name1 from namescollection. 
sql> select name2 from namescollection. 
sql> select name3 from namescollection. 

因此，namescollection表具有三列，分别为name1，name2，name3 我想在我的存储过程中以1,2,3作为动态查询此表，并将其作为变量传递，但是当我查询时也在简单的sql上进行查询:

So namescollection table has three columns having name1, name2, name3 I would like to query this table in my stored procedure being 1,2,3 as dynamic and would be passed as a variable, but on the simple sql too when i query:

SELECT concat('name','1') FROM `namescollection` 

name1 ----- name1 rather fetching name1 field's value.

能对此提出任何建议吗，尽管我知道调用concat时输出name1的权利，但我想使用它作为列名，但是我需要使用concat的正确函数.

Can any suggest on this, the right function i need to use rather concat though I know its right to output name1 when I am calling concat but I want to use it as column name.

推荐答案

您可以做的是在存储过程中使用准备好的语句，该语句将允许您执行字符串查询:

What you can do is use a prepared statement within your stored procedure which will allow you to execute a string query:

作为一个简单的例子:

DELIMITER //
CREATE PROCEDURE selname (IN col VARCHAR(20))
BEGIN
  SET @sql = CONCAT('SELECT ', col, ' FROM tbl');
  PREPARE stmt FROM @sql;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;
END//
DELIMITER ;

---

使用此 SQLFiddle演示进行测试

这篇关于MySql中的动态列名称选择的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！