How to select all columns plus one alias column in MySQL

Question

It is a simple question, but i could not find the answer.

I want to select all the columns in my table with a SELECT * statement, but i also need to alias a UUID column this way: BIN_TO_UUID(ID) as ID.

I need something like this:

SELECT BIN_TO_UUID(ID) AS ID, * FROM TABLE_NAME

Also would be nice to remove the original column that was aliased, to do not have the same column twice with different names, but just if this do not add more text to the query, because i will have to repeat this for all my 16 tables.

Answer 1

I figured out the solution. I simple had do repeat the table name, as follows:

SELECT bin_to_uuid(id) as uuid, table_name.* FROM table_name

But if you have a solution that does not require to repeat the table name i will upvote your answer, since repeating all the table names in all the queries will bloat my the code a little.

EDIT:

As suggested in the comments by @ypercubeᵀᴹ and @Akina, looks like the * selector position is limited in MySQL. If you move the * to the first position everything works fine:

SELECT *, bin_to_uuid(id) as uuid FROM table_name

Answer 2

I want to select all the columns in my table with a SELECT * statement

This is a Bad Idea.

Databases are intrinsically shared resources and, typically, are maintained over time, by more than one person. You write a query today, using "select *" that pulls back all three columns in a table and everything is right with the World. Your Application performs well and everyone is happy.
Some time later, somebody [else] adds a dozen, blob fields into the same table and populates them with Lord only knows what. Suddenly, "your" Application starts performing really badly, because it's retrieving all those data values in which it has precisely zero interest. Bug Reports start to fly.

Or, alternatively, someone [else] rearranges the table (for whatever reason), putting the fields back in a different sequence. Now your "select *" query starts getting its data back in the "wrong" order. Bug Reports. Bug Reports.

[Almost] Always explicitly state the columns that you want to retrieve.

... i also need to alias a UUID column this way: BIN_TO_UUID(ID) as ID ...

That not "aliasing". That's performing a calculation on one of the fields and returning the value of that calculation.

You might consider creating a View that does this for you ...

create view View1 as 
select BIN_TO_UUID( field1 ) as ID
, field2 
, field3 
. . . 
from table1 ; 

... i will have to repeat this for all my 16 tables ...

Correct me if I'm wrong here, but that suggests to me that you have 16 tables with the same structure.

This can also be a Bad Idea.

The "Table per ..." model [dynamically] creates tables for each instance of an Entity. For instance, each Customer's Orders could be held in a table specifically for that Customer. Yes, it gives you data segregation (assuming your table permissions are right) but it comes with nightmarish maintenance overheads, having to make structural changes to "each" table many times over and handling any errors that you get along the way, and potentially has significant problems if you need to aggregate data across all of these Customer tables (I think MySQL is still limited to 61 tables in any one query).

If properly structured and indexed, MySQL can easily cope with significant numbers of rows in each Table.