Rewrite the title as: Get MySQL field names using variables

Question

Is it possible to select a field with a string name?

Select "field name" from table

I need this trigger to have dynamic field names Something similar

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

Answer 1

As these correct answers suggest, you can also do this in a stored procedure, which works great for me in the MySQL 8x community:

CREATE DEFINER=`root`@`127.0.0.1` PROCEDURE `SP_LIST_COLLECTORS`(
    IN P_email VARCHAR(60),#Admin email
    IN P_password_hash VARCHAR(255),#Admin hash
    IN P_filter_field VARCHAR(80),
    IN P_filter_value VARCHAR(255)
)
BEGIN
DECLARE V_filter_field VARCHAR(80);
    SET V_filter_field = P_filter_field;
    BEGIN
        GET DIAGNOSTICS CONDITION 1 @ERRNO = MYSQL_ERRNO, @MESSAGE_TEXT = MESSAGE_TEXT;
        SELECT 'ERROR' AS STATUS, CONCAT('MySQL ERROR: ', @ERRNO, ': ', @MESSAGE_TEXT) AS MESSAGE;
    END;
    SET @statement = CONCAT('SELECT collector_id, email, address, post_code, phone, alt_phone, contact_name
    FROM collectors_table
    WHERE ',P_filter_field, '=\'', P_filter_value, '\';');
    #SELECT collector_id, email, address, post_code, phone, alt_phone, contact_name FROM collectors_table WHERE (V_filter_field) = P_filter_value;
    PREPARE stmnt FROM @statement;
    EXECUTE stmnt;
END

Answer 2

If the string is in your external application (such as PHP), of course, just construct the MySQL statement.

Not if the string is inside a MySQL table. MySQL does not have eval() or such functions. The following is not possible:

Suppose you have a table queries in which field columnname refers to one of the column names in table mytable. There may be additional columns in the query that allow you to select the desired column names.

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

However, you can use prepared statements < /a>. Note that 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;