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

User · Answer

If you use MySQL Workbench you can right-click your table and click Send to sql editor and then Select All Statement This will create an statement where all fields are listed, like this:

SELECT `purchase_history`.`id`,
    `purchase_history`.`user_id`,
    `purchase_history`.`deleted_at`
FROM `fs_normal_run_2`.`purchase_history`;
SELECT * FROM fs_normal_run_2.purchase_history;

Now you can just remove those that you dont want.

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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- gt 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

User · Answer

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  DROP TABLE IF EXISTS temp tb  CREATE TEMPORARY TABLE ENGINE MEMORY temp tb SELECT   FROM orig tb  ALTER TABLE temp tb DROP col x         ALTER TABLE temp tb DROP col a        DROP col z      for a few columns to drop SELECT   FROM temp tb

User · Answer

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

User · Answer

The accepted answer has several shortcomings    It fails where the table or column names requires backticks It fails if the column you want to omit is last in the list It requires listing the table name twice  once for the select and another for the query text  which is redundant and unnecessary It can potentially return column names in the wrong order   All of these issues can be overcome by simply including backticks in the SEPARATOR for your GROUP CONCAT and using a WHERE condition instead of REPLACE     For my purposes  and I imagine many others   I wanted the column names returned in the same order that they appear in the table itself   To achieve this  here we use an explicit ORDER BY clause inside of the GROUP CONCAT   function   SELECT CONCAT       SELECT         GROUP CONCAT COLUMN NAME ORDER BY  ORDINAL POSITION  SEPARATOR                FROM          TABLE SCHEMA                  TABLE NAME             FROM INFORMATION SCHEMA COLUMNS WHERE  TABLE SCHEMA     my database      AND  TABLE NAME     my table      AND  COLUMN NAME      column to omit

User · Answer

I use this work around although it may be  quot Off topic quot  - using mysql workbench and the query builder -  Open the columns view Shift select all the columns you want in your query  in your case all but one which is what i do  Right click and select send to SQL Editor- gt  name short  Now you have the list and you can then copy paste the query to where ever

User · Answer

I have a suggestion but not a solution  If some of your columns have a larger data sets then you should try with following  SELECT    LEFT col1  0  AS col1  LEFT col2  0  as col2 FROM table

User · Answer

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   gt   v           echo  v

User · Answer

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     lt columns to omit gt         FROM INFORMATION SCHEMA COLUMNS WHERE TABLE NAME     lt table gt   AND TABLE SCHEMA     lt database gt       FROM  lt table gt      PREPARE stmt1 FROM  sql  EXECUTE stmt1    Replacing  lt table gt    lt database gt  and  lt columns to omit gt

User · Answer

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

User · Answer

My main problem is the many columns I get when joining tables  While this is not the answer to your question  how to select all but certain columns from one table   I think it is worth mentioning that you can specify table  to get all columns from a particular table  instead of just specifying     Here is an example of how this could be very useful    select users    phone meta value as phone  zipcode meta value as zipcode  from users  left join user meta as phone on    users user id   phone user id  AND  phone meta key    phone      left join user meta as zipcode on    users user id   zipcode user id  AND  zipcode meta key    zipcode        The result is all the columns from the users table  and two additional columns which were joined from the meta table

User · Answer

Would a View work better in this case    CREATE VIEW vwTable as   SELECT       col1         col2         col3         col           col53   FROM table

User · Answer

The answer posted by Mahomedalid has a small problem   Inside replace function code was replacing   lt columns to delete gt    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                       lt columns to delete gt       FIELD REMOVED                FROM INFORMATION SCHEMA COLUMNS            WHERE TABLE NAME     lt table gt                AND TABLE SCHEMA     lt database gt       FROM  lt table gt       Replacing  lt table gt    lt database gt  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

User · Answer

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 lt  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- gt getColsExcept  table  array  id   bigtextcolumn      FROM table      or  mysql query  SELECT    db- gt getColsExcept  table   bigtextcolumn     FROM table

User · Answer

I agree with  Mahomedalid s answer  but I didn t want to do something like a prepared statement and I didn t want 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

User · Answer

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

User · Answer

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

User · Answer

You can do   SELECT column1  column2  column4 FROM table WHERE whatever   without getting column3  though perhaps you were looking for a more general solution

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

May be I have a solution to Jan Koritak s pointed out discrepancy  SELECT CONCAT  SELECT      SELECT GROUP CONCAT t col  FROM       SELECT CASE     WHEN COLUMN NAME    eid  THEN NULL     ELSE COLUMN NAME     END AS col      FROM INFORMATION SCHEMA COLUMNS      WHERE TABLE NAME    employee  AND TABLE SCHEMA    test    t WHERE t col IS NOT NULL      FROM employee       Table     SELECT table name column name  FROM INFORMATION SCHEMA COLUMNS  WHERE TABLE NAME    employee  AND TABLE SCHEMA    test                                      table name  column name employee    eid employee    name eid employee    sal                                     Query Result    SELECT name eid sal FROM employee

User · Answer

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

User · Answer

Select   is a SQL antipattern  It should not be used in production code for many reasons including   It takes a tiny bit longer to process  When things are run millions of times  those tiny bits can matter  A slow database where the slowness is caused by this type of sloppy coding throughout is the hardest kind to performance tune   It means you are probably sending more data than you need which causes both server and network bottlenecks  If you have an inner join  the chances of sending more data than you need are 100     It causes maintenance problems especially when you have added new columns that you do not want seen everywhere  Further if you have a new column  you may need to do something to the interface to determine what to do with that column    It can break views  I know this is true in SQl server  it may or may not be true in mysql     If someone is silly enough to rebuild the tables with the columns in a differnt order  which you shouldn t do but it happens all teh time   all sorts of code can break  Espcially code for an insert for example where suddenly you are putting the city into the address 3 field becasue without specifying  the database can only go on the order of the columns   This is bad enough when the data types change but worse when the swapped columns have the same datatype becasue you can go for sometime inserting bad data that is a mess to clean up   You need to care about data integrity    If it is used in an insert  it will break the insert if a new column is added in one table but not the other    It might break triggers  Trigger problems can be difficult to diagnose    Add up all this against the time it take to add in the column names  heck you may even have an interface that allows you to drag over the columns names  I know I do in SQL Server  I d bet there is some way to do this is some tool you use to write mysql queries   Let s see   I can cause maintenance problems  I can cause performance problems and I can cause data integrity problems  but hey I saved five minutes of dev time   Really just put in the specific columns you want   I also suggest you read this book  http   www amazon com SQL-Antipatterns-Programming-Pragmatic-Programmers-ebook dp B00A376BB2 ref sr 1 1 s digital-text amp ie UTF8 amp qid 1389896688 amp sr 1-1 amp keywords sql antipatterns

User · Answer

You could use DESCRIBE my table and use the results of that to generate the SELECT statement dynamically

User · Answer

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  gt 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

User · Answer

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

[mysql] Select all columns except one in MySQL?

Examples related to mysql

Examples related to select

Examples related to wildcard