0

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.

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
6
  • 1
    Check the EXPLAIN EXTENDED of both methods - it wouldn't surprise me if the optimiser removed any bloat from the executed code which is what counts! – Vérace Jan 17 '20 at 0:01
  • 1
    I simple had do repeat the table name No. You just didn't understand what helped solve the problem. Compare your queries. In the question you have used alias id which is equal to a field name, i.e. you have tried to obtain two fields with the same name id - aliased expression and from table structure. In the answer you have used alias uuid which does not interfere, so no error occures. You would simply read the error message carefully... PS. You may freely remove tablename from asterisk moving it in front of output list: SELECT *, bin_to_uuid(id) as uuid FROM table_name. – Akina Jan 17 '20 at 4:51
  • 1
    @Akina axctually they did solve the syntax error. That's a MySQL bug/limitation: dbfiddle.uk/… Moving the * first in the SELECT list is one way to solve it. Using SELECT ..., t.* instead of SELECT ..., * is another. – ypercubeᵀᴹ Jan 17 '20 at 12:00
  • 1
    Multiple columns with same alias or the same column multiple times has no issue: dbfiddle.uk/… – ypercubeᵀᴹ Jan 17 '20 at 12:04
  • Thanks @ypercubeᵀᴹ, you gone right into the point ^^ – Michael Wallace Jan 17 '20 at 15:28
0

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.

2
  • I agree with the ideia that you should keep the column names in the query. I was aware of this, but naming the columns in the queries would add more SQL code on my backend, winch i really do not want to do. But i can refactor this later if this become a problem. – Michael Wallace Jan 17 '20 at 15:02
  • "Correct me if I'm wrong here, but that suggests to me that you have 16 tables with the same structure": No, they are well separated by subject, there is no data overlap. When i say i will have to repeat i mean that i will have to write similar queries for the other tables, but the data is not the same. – Michael Wallace Jan 17 '20 at 15:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.