Can MySQL Fields Be Accessed Dynamically Using String Variables?

Dynamically Accessing MySQL Fields by Variable

Problem:

Can one programmatically access a MySQL field by name stored in a string variable, particularly for dynamically updating field values?

Example Use Case:

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

Answer:

The ability to dynamically access MySQL fields by variable depends on the context.

External Variables:

If the field name is stored as a variable in an external application (e.g., PHP), constructing a valid MySQL statement is possible.

Internal Variables:

However, if the field name is stored as a value within a MySQL table, accessing it using standard SQL syntax is not feasible. This is due to MySQL's lack of an evaluation (eval) function.

Prepared Statement Technique:

As a workaround, one can utilize prepared statements, though this approach is considered a hacky solution. Here's how it can be implemented:

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

PREPARE stmt FROM @s;
EXECUTE stmt;

This technique involves storing a table's column name in a user variable (@colname), concatenating a dynamic SQL statement, preparing the statement, and executing it.

The above is the detailed content of Can MySQL Fields Be Accessed Dynamically Using String Variables?. For more information, please follow other related articles on the PHP Chinese website!