Select all columns except one in MySQL? ~ Tech Blog

Friday 2 November 2018

Select all columns except one in MySQL?

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)

Answers

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>

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;

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

While trying the solutions by @Mahomedalid and @Junaid I found a problem. So thought of sharing it. If the column name is having spaces or hyphens like check-in then the query will fail. The simple workaround is to use backtick around column names. The modified query is below

SET @SQL = CONCAT('SELECT ', (SELECT GROUP_CONCAT(CONCAT("`", COLUMN_NAME, "`")) FROM
INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'users' AND COLUMN_NAME NOT IN ('id')), ' FROM users');
PREPARE stmt1 FROM @SQL;
EXECUTE stmt1;

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

I liked the answer from @Mahomedalid besides this fact informed in comment from @Bill Karwin. The possible problem raised by @Jan Koritak is true I faced that but I have found a trick for that and just want to share it here for anyone facing the issue.

we can replace the REPLACE function with where clause in the sub-query of Prepared statement like this:

Using my table and column name

SET @SQL = CONCAT('SELECT ', (SELECT GROUP_CONCAT(COLUMN_NAME) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'users' AND COLUMN_NAME NOT IN ('id')), ' FROM users');
PREPARE stmt1 FROM @SQL;
EXECUTE stmt1;

So, this is going to exclude only the field id but not company_id

Hope this will help anyone looking for a solution.

Regards

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

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.

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");

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

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

Based on @Mahomedalid answer, I have done some improvements to support "select all columns except some in mysql"

SET @database    = 'database_name';
SET @tablename   = 'table_name';
SET @cols2delete = 'col1,col2,col3';

SET @sql = CONCAT(
'SELECT ', 
(
    SELECT GROUP_CONCAT( IF(FIND_IN_SET(COLUMN_NAME, @cols2delete), NULL, COLUMN_NAME ) )
    FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @tablename AND TABLE_SCHEMA = @database
), 
' FROM ',
@tablename);

SELECT @sql;

If you do have a lots of cols, use this sql to change group_concat_max_len

SET @@group_concat_max_len = 2048;

Agree on @Mahomedalid's answer. But I didn't wanted to do something like prepared statement and I didn't wanted to type all the fields. So What I had was a silly solution. Go to the table in phpmyadmin->sql->select, it dumps the query copy replace and done! :)

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.

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]);

Tech Blog

Friday 2 November 2018