mysql field name from variable

Question

is it possible to select field which name is string?

SELECT 'fieldname' FROM table

i need this for trigger to have dynamic field names something like

SET fieldname = NEW.`name`; UPDATE table SET fieldname = 1 ;  

Answer 1

If the string is in your external application (like PHP), sure, just construct the MySQL statement.

If the string is inside a MySQL table, you can't. MySQL has no eval() or such function. The following is impossible:

Suppose you have a table queries with a field columnname that refers to one of the column names in the table mytable. There might be additional columns in queries that allow you to select the columnname you want.

INSERT INTO queries  (columname) VALUES ("name") SELECT (select columnname from queries) from mytable 

You can however work with PREPARED STATEMENTS. Be aware this is very hacky.

SELECT columnname from queries into @colname; SET @table = 'mytable'; SET @s = CONCAT('SELECT ',@colname,' FROM ', @table);  PREPARE stmt FROM @s; EXECUTE stmt; 

Answer 2

If you want to select more than one column:

SELECT GROUP_CONCAT(COLUMN_NAME) FROM information_schema.`COLUMNS` C  WHERE table_name = 'MyTb' AND COLUMN_NAME LIKE '%whatever%' INTO @COLUMNS;  SET @table = 'MyTb'; SET @s = CONCAT('SELECT ',@columns,' FROM ', @table);  PREPARE stmt FROM @s; EXECUTE stmt;