在MySQL列名称前添加文本

Question

假设有一个查询，例如：

SELECT * FROM tableA;

我该如何在每个列名前添加a_？例如，如果有一个名为“username”的列，则在结果中访问它时将使用“a_username”。请注意，SELECT username AS a_username格式不能帮助我，因为我需要继续使用*字段选择。由于存在JOIN和另一张表中返回的列可能会发生冲突，我将迭代返回的列（foreach），并且只想输出来自特定表的列（其模式可能会更改）以便网站管理员可以直接编辑字段内容的 HTML 输入字段。所涉及的SQL查询类似于SELECT firstTable.*, anotherTable.someField, anotherTable.someOtherField，并且存在某些字段或someOtherField也存在于firstTable中的可能性。谢谢。

- dotancohen

---

为什么需要 SELECT *？通常情况下这是不好的做法，除非你正在编写一个数据库管理程序。 - outis

@outis：实际上，这段代码确实为论坛管理员提供了直接更改自定义表中的值的能力，该表将来可能会添加其他字段。我也讨厌使用 SELECT *，但正如你所说，在这种情况下是可以证明并且更可取的。 - dotancohen

因为我需要 SELECT table_of_interest.*, anotherTable.someField, anotherTable.someOtherField，并且存在 someField 或者 someOtherField 可能存在于 table_of_interest 中。 - dotancohen

@dotancohen：我想你误解了我的意思；如果你的调用代码执行 SELECT anotherTable.someField, anotherTable.someOtherField, table_of_interest.* FROM ...，那么前两个返回字段的顺序是可靠的，并且之后的所有内容都来自于 table_of_interest。 - eggyal

@dotancohen: 在刚才回答另一个问题的时候，我注意到（至少在PDO这种情况下 - 参见getColumnMeta()），从中派生列的表的名称是可以在结果中获得的。我不知道你是如何连接MySQL的，但也许这可以消除你重命名列的需要？ - eggyal

Answer 1

你可以使用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>

你需要从这个表中获取以下列：

- table_schema - table_name - column_name - ordinal_position

你所要求的是在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。

- RolandoMySQLDBA

---

非常感谢Rolando。我从你的回答中学到了很多东西，其中最重要的是适合我的解决方案不存在这个事实。然而，你出色而详尽的例子告诉了我为什么，这才是真正的答案。我感激你的帮助和建议！ - dotancohen

Answer 2

您需要列出列，例如

SELECT username AS a_username FROM tableA;

或者，可以在后端进行后处理，例如在代码中更改数组键。

- scibuff

---

那只适用于单列。我在这个特定的表中有22列，并且有多个查询。因此，我需要保留SELECT *格式。 - dotancohen

运气不好，要么把它们全部列出来，要么在后端更改数组键。 - scibuff

你可以使用 "select ... as" 格式列出多个字段 --- 例如 select username as a_username, otherfield as a_otherfield, anotherfield as a_anotherfield ... - Kasapo

Answer 3

创建一个视图并重命名列，例如 -

CREATE VIEW a_view AS SELECT username AS a_username FROM table;

然后参考这个视图。

- Devart

---

谢谢Devart。对于我需要处理的特定情况，视图不够灵活。 - dotancohen

Answer 4

正如先前提到的，通常查询中没有标准的批量添加前缀列名的方法。

但是如果你真的想实现它，你可以编写一个存储过程，在其中查询信息模式以获取表中列的列表，然后逐个添加前缀。之后，可以将查询连接为字符串，PREPARE和EXECUTE它。

这种方法的缺点是不能在存储过程的结果上进行联接。但当然，您也可以为发出的每种类型的查询创建存储过程。为所有表添加前缀字段可以单独成为一个通用的FUNCTION。

尽管所有这些东西对我来说都有点过度，我建议要么重命名实际列，使它们始终带有前缀，要么像Scibuff和Alister建议的那样，仅列出所有结果字段与AS别名一起显示。

- Dmytro Shevchenko

---

谢谢Shedal。正如你所提到的，不能在存储过程上进行JOIN操作，而我确实需要进行JOIN操作。 - dotancohen

Answer 5

我不相信可以自动完成所有列的操作，但是您可以使用AS列出任意数量的列。

SELECT id    AS a_id, 
       name  AS a_name, 
       email AS a_email  /*, etc....*/
FROM tableA;

我只是为了更清晰一些插入了换行符。

- Alister Bulman

---

谢谢，阿利斯特。我已经澄清了问题，以阐明必须保留 * 字段选择。 - dotancohen