Choosing a dynamic column in MySql

Question

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

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

So the name mapping table has three columns with the names1, name2, name3 I would like to query this table in my stored procedure as 1,2,3 as dynamic and will be passed as a variable, but also in plain sql when I query:

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

name1 ----- name1 rather fetching name1 field value.

Is it possible to assume that the correct function I need to use is pretty concat, although I know its right to output name1 when I call concat, but I want to use it as the column name.

Answer 1

, , :

:

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 Demo

Answer 2

:

DELIMITER //
CREATE DEFINER=`root`@`localhost` PROCEDURE `selname`()
BEGIN
    declare cl int ;
    declare clvalue int ;

    set cl=1;
    SET clvalue=1;
    while cl < 4 DO
        SET clvalue=clvalue*cl;
        SET @sql = CONCAT('update test set col',cl, '=',clvalue,' where id=1');
        PREPARE stmt FROM @sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
        set cl=cl+1;
   end while;

END //
DELIMITER ;