将文本添加到MySQL列的名称

Supposing a query such as:

假设一个查询,例如:

SELECT * FROM tableA;

How can I prepend a_ to each columns' name? For example if there is a column "username" it would be accessed in the results as "a_username".

如何在每个列的名称前加上a_?例如,如果存在列“用户名”,则在结果中将其作为“a_username”进行访问。

EDIT: The SELECT username AS a_username format will not help as I need to continue using the * field selection. There is a JOIN and a potential conflict with a returned column from another table in the JOIN. I will be iterating over the returned columns (foreach) and only want to output the columns that came from a particular table (whose schema may change) to HTML input fields where a site admin could edit the fields' content directly. The SQL query in question looks like SELECT firstTable.*, anotherTable.someField, anotherTable.someOtherField and their exists the possibility that someField or someOtherField exists in firstTable.

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

Thanks.

5 个解决方案

#1

You can use the INFORMATION_SCHEMA.COLUMNS table to formulate the query and then use dynamic SQL to execute it.

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

First let's make a sample database called dotancohen and a table called mytable

首先让我们创建一个名为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>

Here is the metadata table called INFORMATION_SCHEMA.COLUMNS:

这是名为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>

What you need from this table are the following columns:

您需要从此表中获得以下列:

table_schema
table_name
column_name
ordinal_position

What you are asking for is to have the column_name and the column_name prepended with a_

你要的是让column_name和column_name前面加上a_

Here is the query and how to execute it:

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

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;

Let's execute it

我们来执行吧

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>

Give it a Try !!!

试试看 !!!

You mentioned in your question : The SELECT username AS a_username format will not help as I need to continue using the * field selection.

您在问题中提到:SELECT用户名AS a_username格式无效,因为我需要继续使用*字段选择。

All you have to do to implement my suggestion is run the query using tableA as follows:

要实现我的建议,您只需使用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;

When you retrieve that query result, just use it as the query to submit to mysql_query.

检索该查询结果时,只需将其用作提交到mysql_query的查询。

#2

You'll need to list the columns, e.g

您需要列出列,例如

SELECT username AS a_username FROM tableA;

alternatively, post-process in back-end, e.g. change the array keys in your code

或者,后端的后处理,例如更改代码中的数组键

#3

Create a view with renamed columns, e.g. -

使用重命名的列创建视图,例如 -

CREATE VIEW a_view AS SELECT username AS a_username FROM table;

Then refer to this view.

然后参考这个视图。

#4

As already mentioned, there is no standard way to mass-prefix column names in a regular query.

如前所述,没有标准方法在常规查询中对列名称进行批量前缀。

But if you really wanted to achieve it, you could write a stored procedure which would query information_schema to get a list of columns in a table, and then prefix them one by one. After that it's possible to concatenate a query as string, PREPARE and EXECUTE it.

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

A downside to this approach is the fact that you cannot join on a stored procedure's result. But of course, you could as well create a stored procedure for each type of query you issue. Prefixing the fields for any table could then be made a separate generic FUNCTION.

这种方法的缺点是您无法加入存储过程的结果。但是,当然,您也可以为您发出的每种查询创建存储过程。然后,可以将任何表的字段前缀作为单独的通用函数。

All of this stuff sounds to me like an overkill, though. I would recommend either renaming the actual columns, so that they are always prefixed, or just listing all the result fields with AS aliases, as Scibuff and Alister suggested.

不过,所有这些东西听起来都像是一种矫枉过正。我建议重命名实际列,以便它们始终作为前缀,或者只是列出所有带有AS别名的结果字段,如Scibuff和Alister所建议的那样。

#5

I don't believe it can be done for all columns automatically, but you can list as many columns as you like with AS

我不相信它可以自动为所有列完成,但您可以使用AS列出任意数量的列

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

I've only inserted newlines for some extra clarity.

我只是插入换行符以获得更多清晰度。

#1