ChatGPT解决这个技术问题 Extra ChatGPT

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)

53 columns? I would stick with SELECT * as Thomas suggests in that case... unless that extra column has a huge amount of data that would be undesirable to retrieve...?
Except one colum... I supose you know which one should be ignored, hence INFORMATION_SCHEMA.columns is the way.
A common use for this is to exclude the auto-increment ID column. For example, to select data to be inserted into a different table, which has its own ID.
A common use for this is to exclude the password hash when retrieving the user info
honestly it's rather ridiculous there isn't simple way to do it ffs

h
haim770

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>


caveat: INFORMATION_SCHEMA queries have pretty poor performance, so be careful this type of query isn't in the critical path for anything.
Like @Jan Koritak said below, this answer doesn't actually work if the title's columns that you want to remove are also a sub-string of the title for any columns you wish to keep. There is a better answer that is similar to this that can be found here.
This is way worse than just specifying the columns which is a known best practice.
This doesnt work if there are spaces in the column names. It should be updated to always surround the names in backticks <column_name>
This also won't work if the column to be ignored is the last one listed in the table structure (because the replace will not match the trailing coma).
j
jfg956

(Do not try this on a big table, the result might be... surprising !)

TEMPORARY TABLE

DROP TABLE IF EXISTS temp_tb;
CREATE TEMPORARY TABLE ENGINE=MEMORY temp_tb SELECT * FROM orig_tb;
ALTER TABLE temp_tb DROP col_a, DROP col_f,DROP col_z;    #// MySQL
SELECT * FROM temp_tb;

DROP syntax may vary for databases @Denis Rozhnev


Gave me error message on 3rd step: "Column count doesn't match value count at row 1". So I changed step 2 to "UPDATE temp_tb SET id = NULL" and then it worked.
Ok, this works. But when I run this query again, it gives me an error, that, temp_tb already exists. For how much time the temp_tb is in the memory? Apologies if it's a stupid question. P.S. upvoted your answer.
@Karan To run this query repeatedly, add another command to the beginning: DROP TABLE IF EXISTS temp_tb;
@Karan To specify memory engine, use the command: CREATE TEMPORARY TABLE temp_tb ENGINE=MEMORY (SELECT * FROM orig_tb); , otherwise it's saved to disk by default, and survives a server restart. (thanks)
Excellent answer. For dropping multiple columns, refer to this answer.
B
Brian Childress

Would a View work better in this case?

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

HAHA! Yeah, sure. Now how do you construct the view to include all BUT one of the columns. I think you see how this begs the original question. In fact, I found this thread specifically because I wanted to create a view that excluded certain columns without being forced to list all the remaining columns explicitly in the view definition.
@ChrisNadovich but with View you just list once.
M
Mike Stone

You can do:

SELECT column1, column2, column4 FROM table WHERE whatever

without getting column3, though perhaps you were looking for a more general solution?


Most of the higher rated answers are mostly just finding ways to generate this exact query without typing by hand
Actually, for maintenance reasons, it is useful to have an "everything except" kind of query. Otherwise explicit field lists must be updated if new fields are added later.
@lev, that's correct and THEY SHOULD BE!!! BEcasue you don't know if you want to have any future columns (they may be meta data columns or ones that don't apply to a particular screen). You dont; want to harm performance by returning more than you need (which is true 100% of the time when you have an inner join) I suggest you do some reading on why select * is a SQL antipattern.
The OP states there are >50 columns so this is rather impractical.
@HLGEM Not really, I wanted to select all columns without maintaining the query every time I add a new column, except one of them, and this wouldn't simply work in my case. But anyways I applied Sean O's solution for myself
S
Sean O

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

Why? It doesn't work. If you have a column salary, this query will just end up with the results having two columns named salary, one full of nulls and one with the actual salaries.
@Myforwik This query does indeed add a second salary column. But since it's retrieved after the *, it overwrites the original. It's not pretty, but it does work.
SQL has always allowed duplicate column names in a result-set. If you want this to work you need to run it with a client application that doesn't support dupes and gives priority to the last dupe. The official command-line client supports dupes. HeidiSQL supports them as well. SQL Fiddle doesn't, but displays first dupe, not last. To sum up: this does not work.
Of course it doesn't work. This is great example of why you should test your answers in mysql itself, instead of through libraries that talk to mysql.
@SeanO, @ Alastair, @ Everyone, Doesn't Override. The server still returns sensitive data.
A
Arion

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.


Select * is a poor choice always. Do not recommend it. Read up on why it is a SQl Antipattern.
S
Suraj

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;

S
Stacey Richards

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.


j
jammycakes

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


c
cwd

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.


thank you ,i need to select all of columns of first table and only one field from second table in joining and your answer helped me.
D
Dharman

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


V
Valerian Pereira

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

This will be terribly slow for any moderately sized table.
m
mbillard

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

It acts like a contract with the code and when looking at the query, you know exactly what data you can extract from the it without looking at the schema of the table.
@kodecraft: It's good practice for the same reason that it's good practice to alway return the same type from a function (even if you work in a language where that's not enforced). Basically just the Principle of Least Surprise.
D
David Poor

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


n
nlucaroni

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.


i
icco

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.


h
hahakubile

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;

S
Shark Lasers

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! :)


O
OJ.

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!


S
Soth

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 => $v) { 
      echo"$v,";
   }      
}

Unless the column you want to exclude is a huge BLOB or something.
This is bad if you're trying to avoid the wasted data.
SElect * is a SQL antipattern and shouldl never be used in production code.
I don't agree that SELECT * is some kind of anti-pattern that should "never" be used in production code. It's far clearer that you've retrieved all your columns - as compared to cross-referencing a long list of columns that may, or not, in fact be all the fields. It's also, very obviously, quicker to code, by far. And there are many, many cases where the fields of the table will correspond exactly to the fields in a view or form.
Perfect solution. Out of all the weird long and unclear solutions which I have seen so far, this one is straightforward and clear, and works perfectly. While it may not be efficient for some people with humongous databases, but for most of us this is just perfect. In my case SELECT * or SELECT individual columns doesn't make any difference, all I needed was to exclude two timestamp columns from a list which will have hardly 20-30 records per user. So no efficiency hit for me.
M
Mat

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)


K
Kilise

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

B
Bhavik Shah

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'

G
Gareth Simpson

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.


s
sth

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.


H
Héctor Paúl Cervera-García

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.


E
Eaten by a Grue

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';

D
DropHit

I use this work around although it may be "Off topic" - 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-> name short. Now you have the list and you can then copy paste the query to where ever.

https://i.stack.imgur.com/NNnAY.png


This is awesome. Thanks
M
MR_AMDEV

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

A
Adam

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.