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 ;
3 Answers
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;
3 Comments
mahulst
Probably a bit late at the party, but what the hell... You mention this behaviour is very hacky. But could you perhaps explain why this is? Because this solution helps me a lot, but I don't want to depend on an unstable fix.
dotancohen
The code is hacky because it is not using straightforward SQL features (because those features don't exist). But don't worry, it is stable and won't break on you. As of ten minutes ago, I'm using it in production!
italiansoda
Do you know if Postgresql has this functionality? Does it have an
eval() function that would work if the string is inside a table?Just as a heads up to these correct answers you can also do it inside a stored procedure this worked perfectly for me in 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
Comments
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;
SELECT `string` FROM `table`?