MySQL concat() 创建要在查询中使用的列名？

要创建要在查询中使用的列名称，您需要通过 set 命令使用用户定义的变量。语法如下 -

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

PREPARE anyVariableName from @anyVariableName;登录后复制

EXECUTE anyVariableName;登录后复制

DEALLOCATE PREPARE anyVariableName; 登录后复制

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

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)登录后复制

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

mysql> execute stmt;登录后复制

+----+---------+ | Id | User_Id | +----+---------+ |  1 | 1 | +----+---------+ 1 row in set (0.00 sec)登录后复制

mysql> deallocate prepare stmt; Query OK, 0 rows affected (0.00 sec)登录后复制