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

The answer posted by Mahomedalid has a small problem:

Inside replace function code was replacing "<columns_to_delete>," by "", this replacement has a problem if the field to replace is the last one in the concat string due to the last one doesn't have the char comma "," and is not removed from the string.

My proposal:

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

Replacing <table>, <database> and `

The column removed is replaced by the string "FIELD_REMOVED" in my case this works because I was trying to safe memory. (The field I was removing is a BLOB of around 1MB)

Answer 2

Would a View work better in this case?

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

Answer 3

In mysql definitions (manual) there is no such thing. But if you have a really big number of columns col1, ..., col100, the following can be useful:

mysql> CREATE TEMPORARY TABLE temp_tb SELECT * FROM orig_tb;
mysql> ALTER TABLE temp_tb DROP col_x;
mysql> SELECT * FROM temp_tb;

Answer 4

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_omit>,', '') 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_omit>

Answer 5

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 6

I wanted this too so I created a function instead.

public function getColsExcept($table,$remove){
    $res =mysql_query("SHOW COLUMNS FROM $table");

    while($arr = mysql_fetch_assoc($res)){
        $cols[] = $arr['Field'];
    }
    if(is_array($remove)){
        $newCols = array_diff($cols,$remove);
        return "`".implode("`,`",$newCols)."`";
    }else{
        $length = count($cols);
        for($i=0;$i<$length;$i++){
            if($cols[$i] == $remove)
                unset($cols[$i]);
        }
        return "`".implode("`,`",$cols)."`";
    }
}

So how it works is that you enter the table, then a column you don't want or as in an array: array("id","name","whatevercolumn")

So in select you could use it like this:

mysql_query("SELECT ".$db->getColsExcept('table',array('id','bigtextcolumn'))." FROM table");

or

mysql_query("SELECT ".$db->getColsExcept('table','bigtextcolumn')." FROM table");