假设一个查询:

SELECT * FROM tableA;

如何在每个列的名称前添加 a_？例如，如果有一列“用户名”，它将在结果中作为“a_username”访问。

编辑: SELECT username AS a_username 格式无济于事，因为我需要继续使用 * 字段选择。存在 JOIN 并且可能与 JOIN 中另一个表的返回列发生冲突。我将迭代返回的列 (foreach)，并且只想将来自特定表(其模式可能会更改)的列输出到 HTML 输入字段，站点管理员可以在其中直接编辑字段的内容。有问题的 SQL 查询看起来像 SELECT firstTable.*, anotherTable.someField, anotherTable.someOtherField 并且它们存在 firstTable 中存在 someField 或 someOtherField 的可能性。

谢谢。

最佳答案

您可以使用 INFORMATION_SCHEMA.COLUMNS 表来制定查询，然后使用动态 SQL 来执行它。

首先让我们创建一个名为 dotancohen 的示例数据库和一个名为 mytable 的表

mysql> drop database if exists dotancohen;
Query OK, 1 row affected (0.03 sec)

mysql> create database dotancohen;
Query OK, 1 row affected (0.00 sec)

mysql> use dotancohen
Database changed
mysql> create table mytable
    -> (
    ->     id int not null auto_increment,
    ->     username varchar(30),
    ->     realname varchar(30),
    ->     primary key (id)
    -> );
Query OK, 0 rows affected (0.06 sec)

mysql> insert into mytable (realname,username) values
    -> ('rolando','odnalor'),('pamela','alemap'),
    -> ('dominique','euqinimod'),('diamond','dnomaid');
Query OK, 4 rows affected (0.05 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from mytable;
+----+-----------+-----------+
| id | username  | realname  |
+----+-----------+-----------+
|  1 | odnalor   | rolando   |
|  2 | alemap    | pamela    |
|  3 | euqinimod | dominique |
|  4 | dnomaid   | diamond   |
+----+-----------+-----------+
4 rows in set (0.00 sec)

mysql>

这是名为 INFORMATION_SCHEMA.COLUMNS 的元数据表:

mysql> desc INFORMATION_SCHEMA.COLUMNS;
+--------------------------+---------------------+------+-----+---------+-------+
| Field                    | Type                | Null | Key | Default | Extra |
+--------------------------+---------------------+------+-----+---------+-------+
| TABLE_CATALOG            | varchar(512)        | NO   |     |         |       |
| TABLE_SCHEMA             | varchar(64)         | NO   |     |         |       |
| TABLE_NAME               | varchar(64)         | NO   |     |         |       |
| COLUMN_NAME              | varchar(64)         | NO   |     |         |       |
| ORDINAL_POSITION         | bigint(21) unsigned | NO   |     | 0       |       |
| COLUMN_DEFAULT           | longtext            | YES  |     | NULL    |       |
| IS_NULLABLE              | varchar(3)          | NO   |     |         |       |
| DATA_TYPE                | varchar(64)         | NO   |     |         |       |
| CHARACTER_MAXIMUM_LENGTH | bigint(21) unsigned | YES  |     | NULL    |       |
| CHARACTER_OCTET_LENGTH   | bigint(21) unsigned | YES  |     | NULL    |       |
| NUMERIC_PRECISION        | bigint(21) unsigned | YES  |     | NULL    |       |
| NUMERIC_SCALE            | bigint(21) unsigned | YES  |     | NULL    |       |
| CHARACTER_SET_NAME       | varchar(32)         | YES  |     | NULL    |       |
| COLLATION_NAME           | varchar(32)         | YES  |     | NULL    |       |
| COLUMN_TYPE              | longtext            | NO   |     | NULL    |       |
| COLUMN_KEY               | varchar(3)          | NO   |     |         |       |
| EXTRA                    | varchar(27)         | NO   |     |         |       |
| PRIVILEGES               | varchar(80)         | NO   |     |         |       |
| COLUMN_COMMENT           | varchar(1024)       | NO   |     |         |       |
+--------------------------+---------------------+------+-----+---------+-------+
19 rows in set (0.02 sec)

mysql>

你需要从这个表中得到的是以下列:

• 表模式
• 表名
• 专栏名称
• 顺序位置

您要求的是在 column_name 和 column_name 前加上 a_

这里是查询以及如何执行它:

select concat('select ',column_list,' from ',dbtb) into @newsql
from (select group_concat(concat(column_name,' a_',column_name)) column_list,
concat(table_schema,'.',table_name) dbtb from information_schema.columns
where table_schema = 'dotancohen' and table_name = 'mytable'
order by ordinal_position) A;
select @newsql;
prepare stmt from @newsql;
execute stmt;
deallocate prepare stmt;

让我们执行它

mysql> select concat('select ',column_list,' from ',dbtb) into @newsql
    -> from (select group_concat(concat(column_name,' a_',column_name)) column_list,
    -> concat(table_schema,'.',table_name) dbtb from information_schema.columns
    -> where table_schema = 'dotancohen' and table_name = 'mytable'
    -> order by ordinal_position) A;
Query OK, 1 row affected (0.01 sec)

mysql> select @newsql;
+--------------------------------------------------------------------------------+
| @newsql                                                                        |
+--------------------------------------------------------------------------------+
| select id a_id,username a_username,realname a_realname from dotancohen.mytable |
+--------------------------------------------------------------------------------+
1 row in set (0.00 sec)

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

mysql> execute stmt;
+------+------------+------------+
| a_id | a_username | a_realname |
+------+------------+------------+
|    1 | odnalor    | rolando    |
|    2 | alemap     | pamela     |
|    3 | euqinimod  | dominique  |
|    4 | dnomaid    | diamond    |
+------+------------+------------+
4 rows in set (0.01 sec)

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

mysql>

试一试!!!

您在问题中提到:SELECT username AS a_username 格式无济于事，因为我需要继续使用 * 字段选择。

要实现我的建议，您只需按如下所示使用 tableA 运行查询:

select concat('select ',column_list,' from ',dbtb) into @newsql
from (select group_concat(concat(column_name,' a_',column_name)) column_list,
concat(table_schema,'.',table_name) dbtb from information_schema.columns
where table_schema = DATABASE() and table_name = 'tableA'
order by ordinal_position) A;

当您检索该查询结果时，只需将其用作提交给 mysql_query 的查询即可.

关于mysql - 将文本添加到 MySQL 列的名称，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/10178018/