0

I deal with tables with many columns - recently I realised I can generate a list of column names excluding one column (maybe PK), that I don't want.

Like so:

mysql> SET @notColumns = 'primary_key,another_column';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT GROUP_CONCAT(COLUMN_NAME)   FROM INFORMATION_SCHEMA.COLUMNS   WHERE TABLE_SCHEMA = 'our_dev' AND TABLE_NAME = 'subscriptions' AND FIND_IN_SET(COLUMN_NAME, @notColumns) = 0  GROUP BY TABLE_NAME;
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GROUP_CONCAT(COLUMN_NAME)                                                                                                                                                                                                                                                                                        |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| lots,of,columns,excluding,those,specified,comma,separated,in,the,varchar,variable |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> 

I want to make this into a function with 2 parameters, table name and a string of comma-separated values, but I couldn't get the table parameter to work, so with a fixed table name:

mysql> CREATE FUNCTION notColumns(notColumns VARCHAR(255)) 
RETURNS VARCHAR(1000) 
RETURN (SELECT GROUP_CONCAT(COLUMN_NAME)   FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'our_db' 
AND TABLE_NAME = 'subscriptions' 
AND FIND_IN_SET(COLUMN_NAME, @notColumns) = 0  
GROUP BY TABLE_NAME);

This outputs what I want, but if I try and interpolate with it, like so:

SELECT (SELECT notColumns('primary_key,other_column)) FROM subscriptions;

I just get the column names again, instead of the corresponding values from the 1st record in the table that I want lots,of,columns,excluding,those,specified,comma,separated,in,the,varchar,variable

Is this column interpolation even possible? If so what can I do to make the SQL query language read the output of my function as raw text?

1 Answer 1

1

For that you need dynamic sql

like so

CREATE FUNCTION notColumns(notColumns VARCHAR(255)) 
RETURNS VARCHAR(1000) 
RETURN (SELECT GROUP_CONCAT(COLUMN_NAME)   FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'our_db' 
AND TABLE_NAME = 'subscriptions' 
AND FIND_IN_SET(COLUMN_NAME, @notColumns) = 0  
GROUP BY TABLE_NAME);
SELECT notColumns("primary_key,other_column")
| notColumns("primary_key,other_column") |
| :------------------------------------- |
| null                                   |
SET @sql = CONCAT('SELECT ',notColumns("primary_key,other_column"),' FROM subscriptions;');
SELECT @sql;
PREPARE stmt FROM @sql;
EXECUTE stmt;
✓

| @sql |
| :--- |
| null |

db<>fiddle here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.