MySQL concat() creates column names to be used in queries?

To create column names to be used in queries, you need to use user-defined variables via the set command. The syntax is as follows -

SET @anyVariableName :=
(
   SELECT CONCAT
   (
      "SELECT",
   GROUP_CONCAT(CONCAT(" 1 as ", COLUMN_NAME) SEPARATOR ','), " FROM DUAL")
   FROM INFORMATION_SCHEMA_COLUMNS
   WHERE TABLE_NAME= ‘yourTableName’
);

Now use the PREPARE command to prepare the statement. The syntax is as follows -

PREPARE anyVariableName from @anyVariableName;

Use the EXECUTE command to execute the statement. The syntax is as follows -

EXECUTE anyVariableName;

Use the DEALLOCATE command to deallocate the prepared statement. The syntax is as follows -

DEALLOCATE PREPARE anyVariableName;

We will now create a table with two columns and this column will be used in the join query. The query to create the table is as follows -

mysql> create table ConcatenationWithUserDefinedVariable
   -> (
   -> Id int NOT NULL AUTO_INCREMENT,
   -> User_Id int,
   -> PRIMARY KEY(Id)
   -> );
Query OK, 0 rows affected (1.14 sec)

Use the above syntax for the column names to be used in the query. The query is as follows -

mysql> set @q := (
   -> select concat(
   -> "select",
   -> group_concat(concat(" 1 as ", column_name) separator ','),
   -> "from dual")
   -> from information_schema.columns
   -> where table_name = 'ConcatenationWithUserDefinedVariable');
Query OK, 0 rows affected (0.01 sec)

The query to prepare the above user-defined variables is as follows -

mysql> prepare stmt from @q;
Query OK, 0 rows affected (0.00 sec)
Statement prepared

Now execute the statement prepared above. The query is as follows -

mysql> execute stmt;

The following is the output -

+----+---------+
| Id | User_Id |
+----+---------+
|  1 |       1 |
+----+---------+
1 row in set (0.00 sec)

Finally, you need to release the prepared statement. The query is as follows -

mysql> deallocate prepare stmt;
Query OK, 0 rows affected (0.00 sec)

The above is the detailed content of MySQL concat() creates column names to be used in queries?. For more information, please follow other related articles on the PHP Chinese website!