Prepare text for MySQL column names

Question

Suppose the query:

SELECT * FROM tableA; 

How can I add a_ to the name of each column? For example, if there is a column "username", it will be available in the results as "a_username".

EDIT: The SELECT username AS a_username will not help, since I need to continue selecting the * field. There is a JOIN and a potential conflict with the returned column from another table in the JOIN. I will iterate over the returned columns (foreach) and only want to output the columns that came from a particular table (the layout of which may change) into HTML input fields, where the site administrator can directly edit the contents of the fields. The proposed SQL query looks like SELECT firstTable.*, anotherTable.someField, anotherTable.someOtherField , and it is likely that someField or someOtherField exists in firstTable.

Thanks.

Answer 1

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

First give a sample database called dotancohen and a table called 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 a metadata table named 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> 

In this table, you will need the following columns:

• table_schema
• table_name
• column_name
• ORDINAL_POSITION

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

Here is the request 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 it run

 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 format of the SELECT AS username a_username will not help, since I need to continue selecting the * field.

All you have to do to implement my proposal is to start the query using table A as follows:

 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 this query result, just use it as a query to send mysql_query .

Answer 2

You will need to specify columns, e.g.

 SELECT username AS a_username FROM tableA; 

alternatively, a post process in the background, for example. change array keys in code

Answer 3

Create a view with renamed columns, for example. -

 CREATE VIEW a_view AS SELECT username AS a_username FROM table; 

Then refer to this view.

Answer 4

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

But if you really wanted to, you could write a stored procedure that would query calculus information to get a list of columns in a table, and then prefix them one by one. After that, you can bind the query as a string, PREPARE and EXECUTE .

The disadvantage of this approach is the fact that you cannot join the result of a stored procedure. But, of course, you could create a stored procedure for each type of request that you issue. The field prefix for any table can then be made a separate shared FUNCTION .

All this sounds to me like a bust. I would recommend renaming the actual columns so that they are always prefixed or simply list all result fields using AS aliases, as suggested by Scibuff and Alister.

Answer 5

I don’t think it can be done for all columns automatically, but you can specify as many columns as you like using AS

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

I just inserted new lines for some extra clarity.