2

I know my title is not very descriptive... let me explain in details here.

Let say, if a table has 26 fields. e.g. field_a ... field_z. and I only want a select query to return me 15 fields only.

So, normally, I will do SELECT field_a, field_b ... field_o FROM myTable.

Which is tedious. Is there a way in MYSQL that I can do a SELECT * and tell it not to return certain fields?

e.g. soemthing like SELECT * exclude (field_p, field_q .. field_z) FROM myTable?

Thanks all for the answers. :)

murvinlai
  • 48,919
  • 52
  • 129
  • 177

6 Answers6

4

SELECT * is evil.

You should never use it in production code.

You should always specify the columns you want returned, like so:

SELECT `column1`, `column2`, `someothercolumn`
FROM `myTable`

As always, the documentation can help with the nitty gritty!

TehShrike
  • 9,855
  • 2
  • 33
  • 28
3

SQL itself does not support the functionality you are asking for. (If it did, it would have the same problems as select *. See other's comments)

This is what I do when I'm using MySQL:

  1. Fire up mysql command line client
  2. Perform a describe my_table
  3. Copy the data in Field column (mouse select)
  4. Paste data in my editor (TextPad)
  5. Manually remove the columns I don't need
  6. Run a macro that substitutes new line for a comma (and insert a table alias)

All in all, it takes around 20-30 seconds to create a select list regardless of the number of columns. This way ensures that I don't misspell or forget any columns.

Ronnis
  • 12,593
  • 2
  • 32
  • 52
2
delimiter //
DROP PROCEDURE IF EXISTS `getColumnNames`//
CREATE PROCEDURE `getColumnNames` (db_name CHAR(255), t_name CHAR(255), ex_name CHAR(255))
BEGIN
SELECT group_concat(column_name) FROM `information_schema`.`COLUMNS` C 
WHERE
table_schema = db_name
AND
table_name = t_name
AND 
column_name not in (ex_name)
GROUP BY table_schema,table_name;
END

//
delimiter ;


call getColumnNames("Db_name", "tbl_name", "col_to_exclude");
shantanuo
  • 31,689
  • 78
  • 245
  • 403
1

No use the fields you want (select * shouold not appear in production code even if you want allthe fields). I don't know about mySQL but in SQL Server I can drag and drop the columns which makes it easy to specify, is there a way to make this less tedious by dragging and dropping?

HLGEM
  • 94,695
  • 15
  • 113
  • 186
1

There's no way to do this because 'SELECT *' is a bad idea in a production environment anyhow. Just think of all the extra error-catching that would need to be done if something like this existed -- what if the excluded field didn't exist in the table? Does this create an error? A warning?

Enumerating all your fields is indeed tedious, but it's the correct way to do it. It makes your code (a little bit more) self-documenting, and helps stop errors early in the cycle. For one example, if 'user_name' is eventually renamed to 'username' for whatever reason, the SQL statement will fail and you won't have a strange data lolling around in your code waiting to be traced down.

anschauung
  • 3,697
  • 3
  • 24
  • 34
  • These arguments against are a bit bogus IMO. Sometimes it would indeed be more self documenting to be able to say "I want to select all columns except column X.". The need for me most often arises when doing an admin script that does an `Insert .. Select` from the same table. I think I read somewhere on this site that [language D](http://en.wikipedia.org/wiki/D_%28data_language_specification%29) implements this. – Martin Smith Dec 30 '10 at 01:11
0

I hope that there's none way to do this. Even if there is one, I'd suggest not to use it.

Kindof solution is to create a view that excludes the one row you want to have excluded, and you can select * from the view.

Mirek Kratochvil
  • 742
  • 6
  • 3