Select all columns except one in MySQL?

Question

I'm trying to use a select statement to get all of the columns from a certain MySQL table except one. Is there a simple way to do this?

EDIT: There are 53 columns in this table (NOT MY DESIGN)

Answer 1

Select * is a SQL antipattern. It should not be used in production code for many reasons including:

It takes a tiny bit longer to process. When things are run millions of times, those tiny bits can matter. A slow database where the slowness is caused by this type of sloppy coding throughout is the hardest kind to performance tune.

It means you are probably sending more data than you need which causes both server and network bottlenecks. If you have an inner join, the chances of sending more data than you need are 100%.

It causes maintenance problems especially when you have added new columns that you do not want seen everywhere. Further if you have a new column, you may need to do something to the interface to determine what to do with that column.

It can break views (I know this is true in SQl server, it may or may not be true in mysql).

If someone is silly enough to rebuild the tables with the columns in a differnt order (which you shouldn't do but it happens all teh time), all sorts of code can break. Espcially code for an insert for example where suddenly you are putting the city into the address_3 field becasue without specifying, the database can only go on the order of the columns. This is bad enough when the data types change but worse when the swapped columns have the same datatype becasue you can go for sometime inserting bad data that is a mess to clean up. You need to care about data integrity.

If it is used in an insert, it will break the insert if a new column is added in one table but not the other.

It might break triggers. Trigger problems can be difficult to diagnose.

Add up all this against the time it take to add in the column names (heck you may even have an interface that allows you to drag over the columns names (I know I do in SQL Server, I'd bet there is some way to do this is some tool you use to write mysql queries.) Let's see, "I can cause maintenance problems, I can cause performance problems and I can cause data integrity problems, but hey I saved five minutes of dev time." Really just put in the specific columns you want.

I also suggest you read this book: http://www.amazon.com/SQL-Antipatterns-Programming-Pragmatic-Programmers-ebook/dp/B00A376BB2/ref=sr_1_1?s=digital-text&ie=UTF8&qid=1389896688&sr=1-1&keywords=sql+antipatterns

Answer 2

It is good practice to specify the columns that you are querying even if you query all the columns.

So I would suggest you write the name of each column in the statement (excluding the one you don't want).

SELECT
    col1
    , col2
    , col3
    , col..
    , col53

FROM table

Answer 3

If it's always the same one column, then you can create a view that doesn't have it in it.

Otherwise, no I don't think so.

Answer 4

I agree with the "simple" solution of listing all the columns, but this can be burdensome, and typos can cause lots of wasted time. I use a function "getTableColumns" to retrieve the names of my columns suitable for pasting into a query. Then all I need to do is to delete those I don't want.

CREATE FUNCTION `getTableColumns`(tablename varchar(100)) 
          RETURNS varchar(5000) CHARSET latin1
BEGIN
  DECLARE done INT DEFAULT 0;
  DECLARE res  VARCHAR(5000) DEFAULT "";

  DECLARE col  VARCHAR(200);
  DECLARE cur1 CURSOR FOR 
    select COLUMN_NAME from information_schema.columns 
    where TABLE_NAME=@table AND TABLE_SCHEMA="yourdatabase" ORDER BY ORDINAL_POSITION;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
  OPEN cur1;
  REPEAT
       FETCH cur1 INTO col;
       IF NOT done THEN 
          set res = CONCAT(res,IF(LENGTH(res)>0,",",""),col);
       END IF;
    UNTIL done END REPEAT;
  CLOSE cur1;
  RETURN res;

Your result returns a comma delimited string, for example...

col1,col2,col3,col4,...col53

Answer 5

To the best of my knowledge, there isn't. You can do something like:

SELECT col1, col2, col3, col4 FROM tbl

and manually choose the columns you want. However, if you want a lot of columns, then you might just want to do a:

SELECT * FROM tbl 

and just ignore what you don't want.

In your particular case, I would suggest:

SELECT * FROM tbl

unless you only want a few columns. If you only want four columns, then:

SELECT col3, col6, col45, col 52 FROM tbl

would be fine, but if you want 50 columns, then any code that makes the query would become (too?) difficult to read.

Answer 6

My main problem is the many columns I get when joining tables. While this is not the answer to your question (how to select all but certain columns from one table), I think it is worth mentioning that you can specify table. to get all columns from a particular table, instead of just specifying .

Here is an example of how this could be very useful:

select users.*, phone.meta_value as phone, zipcode.meta_value as zipcode

from users

left join user_meta as phone
on ( (users.user_id = phone.user_id) AND (phone.meta_key = 'phone') )

left join user_meta as zipcode
on ( (users.user_id = zipcode.user_id) AND (zipcode.meta_key = 'zipcode') )

The result is all the columns from the users table, and two additional columns which were joined from the meta table.