Can MySQL Select a Field Using a Dynamically Generated Field Name?

Dynamic Field Name Selection in MySQL

In MySQL, is it possible to select a field based on a dynamic field name stored as a string?

Original 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:

Whether it's possible to select a field dynamically based on a string depends on where the string is located.

String in External Application:

If the field name string is in your external application, constructing the MySQL statement is straightforward.

String in MySQL Table:

However, if the field name string is stored in a MySQL table, directly selecting it is impossible. MySQL doesn't have an evaluation function like eval(). This operation is not possible using the following approach:

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

Using Prepared Statements:

As a workaround, you can use prepared statements, but be cautious as it's a hacky approach.

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

PREPARE stmt FROM @s;
EXECUTE stmt;

The above is the detailed content of Can MySQL Select a Field Using a Dynamically Generated Field Name?. For more information, please follow other related articles on the PHP Chinese website!