Selecting all except some columns in MySQL

The MySQL SELECT is a ubiquitous statement. You can select rows using the ‘*’ operator or by listing the individual column names. But many times you may require using all the columns from a table except a couple of them. For example you may have a table containing twelve columns from which you require only eleven columns. Most of us will use the ‘*’ operator in the SELECT statement rather than explicitly list all the eleven column names. But what if the column we do not require contains a large amount of BLOB data. It is inefficient to include the BLOB column in the query as it will unnecessarily increase the query load. What we want is to select all the columns except the BLOB column. We can construct the required statement using the ‘SHOW COLUMNS’ statement.

The following php function will return all the column names for a given table.

Now we create a function that returns a comma separated list of column names which we can then pass to the SELECT statement. The function also takes a array of column names we want to exclude.

Now we create a list of columns and pass it to the SELECT statement.

We want to exclude columns 'post_author' and
'comment_count' from the table wp_posts
$exclude = array("post_author", "comment_count");

$stmt_list = create_statement($connection, "wp_posts", $exclude);

$query = "SELECT {$stmt_list} FROM wp_posts";

9 thoughts to “Selecting all except some columns in MySQL”

  1. There are many cases when this might be useful. Take a typical example. You are designing a prototype Ajax/PHP application or a Web Service. There are many instances in the application where you have to shuffle data from the server to the client quickly. The extra overhead of a single query on the server is negligible in comparison to the amount of data transfer saved by dropping unrequested columns. It also saves the labor of typing a long list of column names in the SELECT query.

  2. One other thing to point out. I’ve seen this type of code done in various places (the select to discover column names). One thing you can do, is cache that data aggressively, since it’s rare your table definitions change.

    Which means that you don’t really have the overhead on every query, you do it once and keep it cached forever, and manually flush the cache if you change the table columns.

  3. This is a really bad idea. When you would add columns to the table definition they transparently get inserted into a query like this. This way you aren’t tracking which columns are used and which are not. So there’s a big risk that eventually there will be columns that won’t be used.

    About the aggressively caching idea: It’s not a bad idea: I would suggest to manually put the result directly in the query.

  4. Thanks for all the comments guys! I completely agree with Onno. But it still is a lot easy for me to use the above code while developing a quick prototype php application as I already said in my previous comment.

  5. Just found your site – enjoying it quite a bit so far!!

    Just curious – if you know which columns you DON’T want, would it make just as much sense to use the columns as keys, unset them, and implode the results? I haven’t tried and I’m probably just barely NOT a noobie… just figured I’d ask if you can see a reason to use one method over the other.

    Great site!

  6. Not sure what you really mean. But if you are saying that we should ‘SELECT’ all the columns and then unset the ones we do not want and then display the result; then it destroys the main purpose of the above code. The idea is to not select the columns we want so that we can reduce the query load.

  7. I think David’s idea is not bad too… If said before that reducid query load was not your goal (as you are executing an extra query to fetch the table definition).

    You could probably do a SELECT * and then unset the unused fields.

Comments are closed.