0

I am stuck at what seemed to be a very simple task in MySQL.

I have a table A with fields A_Id and A_Name.

MySQL query - SELECT * FROM A;

Obviously, in the output, the column names are A_Id and A_Name. But I want them to be aid and aname, that is, eliminating all underscores and converting to lowercase only the column names.

I think this should be possible. Any help/suggestion is appreciated.

EDIT: Why do I need to do this?

I have indexed all these fields in ElasticSearch, and then querying using Spring Data Elastic, using named queries becomes difficult when there are underscores in the field names

I did look around for some answers, but all of them are either ALTER statements or manipulating the field values using REPLACE, none of which suit my usecase.

Thanks :)

Anusha
  • 647
  • 11
  • 29

1 Answers1

1

You need to use the AS keyword to change the column names in the output:

SELECT A_Id as aid, A_Name as aname
FROM A;

There's no simple way to do this automatically for all columns, you need to list each column specifically. The only way to automate it would be to write a stored procedure that created the query dynamically by querying INFORMATION_SCHEMA.COLUMNS.

The query to get the columns would include something like:

SELECT GROUP_CONCAT(CONCAT(column_name), ' AS ', REPLACE(LOWER(column_name), '_', '')) AS select_list
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_name = 'A';
Barmar
  • 741,623
  • 53
  • 500
  • 612
  • I was trying to avoid `AS` because I have tens of columns in my table. I haven't written a stored procedure before, can you point to some useful links?. Thank you very much :) – Anusha Oct 21 '17 at 02:15
  • Why do you need to do this? Why can't you just use the column names as they are? – Barmar Oct 21 '17 at 02:16
  • Google "mysql stored procedure tutorial" and you'll find some sites. – Barmar Oct 21 '17 at 02:17
  • I have indexed all these fields in ElasticSearch, and then querying using Spring Data Elastic, using named queries becomes difficult when there are underscores in the field names – Anusha Oct 21 '17 at 02:18
  • See https://stackoverflow.com/questions/7674786/mysql-pivot-table for examples of stored procedures that prepare queries dynamically. – Barmar Oct 21 '17 at 02:20