In SQL, how do I exclude result from SELECT * FROM …?

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. :)

Answers


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");

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!


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.


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?


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.


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.


Need Your Help

How to insert URL into flash messages?

url notifications ruby-on-rails-3 hyperlink

I read this (1) and this (2) but I'd like to have a simple method to do that... and that works! Both ( (1) and (2) ) does not works for me.

Java XML Bean and XML xml parser

java xml-parsing xmlbeans

I have never used XMl perser directly.When i was going through xml beans i got a doubt which may be stupid(apologies for that) and here it goes

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.