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

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 2

If you are looking to exclude the value of a field, e.g. for security concerns / sensitive info, you can retrieve that column as null.

e.g.

SELECT *, NULL AS salary FROM users

Answer 3

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 4

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

Answer 5

I would like to add another point of view in order to solve this problem, specially if you have a small number of columns to remove.

You could use a DB tool like MySQL Workbench in order to generate the select statement for you, so you just have to manually remove those columns for the generated statement and copy it to your SQL script.

In MySQL Workbench the way to generate it is:

Right click on the table -> send to Sql Editor -> Select All Statement.

Answer 6

Im pretty late at throing out an answer for this, put this is the way i have always done it and frankly, its 100 times better and neater than the best answer, i only hope someone will see it. And find it useful

    //create an array, we will call it here. 
    $here = array();
    //create an SQL query in order to get all of the column names
    $SQL = "SHOW COLUMNS FROM Table";
        //put all of the column names in the array
        foreach($conn->query($SQL) as $row) {
            $here[] = $row[0];
        }
    //now search through the array containing the column names for the name of the column, in this case i used the common ID field as an example
    $key = array_search('ID', $here);
    //now delete the entry
    unset($here[$key]);