Can Column Names Be Concatenated Dynamically for MySQL Query Execution?

Concatenating Column Names Dynamically for Query Execution in MySQL

Problem Statement:

In MySQL, it is desired to concatenate column names in a query where the first part of the column name is a string and the second part is a number retrieved from another query.

Attempted Solution:

The following SQL statement attempts to achieve the concatenation:

<code class="sql">SELECT CONCAT('column', mytable.mycolumn) FROM table ...</code>

However, this approach does not yield the desired result, and concatenation seems ineffective.

Solution:

Contrary to an earlier belief, it is indeed possible to dynamically construct column names in MySQL using server-side prepared statements. Consider the following approach:

<code class="sql">set @query := (
  select concat(
    "select",
      group_concat(concat("\n  1 as ", column_name) separator ','),
    "\nfrom dual")
  from information_schema.columns
  where table_name = 'columns')
;

prepare s1 from @query
;
execute s1
;
deallocate prepare s1
;</code>

This code dynamically constructs a query string based on the contents of the columns table. The prepare statement is then used to compile the query string, which can subsequently be executed using the execute statement.

The above is the detailed content of Can Column Names Be Concatenated Dynamically for MySQL Query Execution?. For more information, please follow other related articles on the PHP Chinese website!