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)

Sounds like there's no good solution, thanks anyways guys.

Answer 1

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 2

You can do:

SELECT column1, column2, column4 FROM table WHERE whatever

without getting column3, though perhaps you were looking for a more general solution?

Answer 3

53 columns? I would stick with SELECT * as Thomas suggests in that case... unless that extra column has a huge amount of data that would be undesirable to retrieve...?

Answer 4

If the column that you didn't want to select had a massive amount of data in it, and you didn't want to include it due to speed issues and you select the other columns often, I would suggest that you create a new table with the one field that you don't usually select with a key to the original table and remove the field from the original table. Join the tables when that extra field is actually required.

Answer 5

You could use DESCRIBE my_table and use the results of that to generate the SELECT statement dynamically.

Answer 6

Would a View work better in this case?

CREATE VIEW vwTable
as  
SELECT  
    col1  
    , col2  
    , col3  
    , col..  
    , col53  
FROM table

Answer 7

I agree that it isn't sufficient to Select *, if that one you don't need, as mentioned elsewhere, is a BLOB, you don't want to have that overhead creep in.

I would create a view with the required data, then you can Select * in comfort --if the database software supports them. Else, put the huge data in another table.

Answer 8

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 9

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 10

At first I thought you could use regular expressions, but as I've been reading the MYSQL docs it seems you can't. If I were you I would use another language (such as PHP) to generate a list of columns you want to get, store it as a string and then use that to generate the SQL.

Answer 11

While I agree with Thomas' answer (+1 ;)), I'd like to add the caveat that I'll assume the column that you don't want contains hardly any data. If it contains enormous amounts of text, xml or binary blobs, then take the time to select each column individually. Your performance will suffer otherwise. Cheers!

Answer 12

The problem is this: I want to duplicate a row. But using SELECT * also includes the index, thus giving an error. Something to the effect of 'SELECT All columns except ID' would solve the problem. I think this is what people are asking.

Answer 13

You can use SQL to generate SQL if you like and evaluate the SQL it produces. This is a general solution as it extracts the column names from the information schema. Here is an example from the Unix command line.

substituting MYSQL with your mysql command TABLE with the table name EXCLUDEDFIELD with excluded field name

echo $(echo 'select concat("select ", group_concat(column_name) , " from TABLE") from information_schema.columns where table_name="TABLE" and column_name != "EXCLUDEDFIELD" group by "t"' | MYSQL | tail -n 1) | MYSQL

You will really only need to extract the column names in this way only once to construct the column list excluded that column, and then just use the query you have constructed.

So something like:

column_list=$(echo 'select group_concat(column_name) from information_schema.columns where table_name="TABLE" and column_name != "EXCLUDEDFIELD" group by "t"' | MYSQL | tail -n 1)

Now you can reuse the $column_list string in queries you construct.

Answer 14

Actually there is a way, you need to have permissions of course for doing this ...

SET @sql = CONCAT('SELECT ', (SELECT REPLACE(GROUP_CONCAT(COLUMN_NAME), '<columns_to_delete>,', '') FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '<table>' AND TABLE_SCHEMA = '<database>'), ' FROM <table>');

PREPARE stmt1 FROM @sql;
EXECUTE stmt1;

Replacing <table>, <database> and <columns_to_delete>

Answer 15

This is the simplest way and it works

SELECT * FROM TABLE WHERE COLUMN != 'NAME'

Answer 16

Just do

SELECT * FROM table WHERE whatever

Then drop the column in you favourite programming language: php

while (($data = mysql_fetch_array($result, MYSQL_ASSOC)) !== FALSE) {
   unset($data["id"]);
   foreach ($data as $k => $v) { 
      echo"$v,";
   }      
}

Answer 17

Yes, though it can be high I/O depending on the table here is a workaround I found for it.

Select *
into #temp
from table

alter table #temp drop column column_name

Select *
from #temp

Answer 18

Hello chaps,

And for Oracle, does anyone have some clues?

Thanks, Zo