在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?

我尝试使用一个select语句来从一个MySQL表中获取所有的列，除了一个。有没有一种简单的方法?

EDIT: There are 53 columns in this table (NOT MY DESIGN)

编辑:这个表中有53个列(不是我的设计)

28 个解决方案

#1

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>

替换

<数据库> 和< columns_to_忽略>。

，

#2

Would a View work better in this case?

在这种情况下，视图效果会更好吗?

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

#3

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定义(手册)中没有这样的东西。但是如果你有一个很大的列，col1，…，col100，以下是有用的:

mysql> CREATE TEMPORARY TABLE temp_tb SELECT * FROM orig_tb;
mysql> ALTER TABLE temp_tb DROP col_x;
mysql> SELECT * FROM temp_tb;

#4

You can do:

你能做什么:

SELECT column1, column2, column4 FROM table WHERE whatever

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

如果没有得到column3，也许您正在寻找一个更通用的解决方案?

#5

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

#6

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.

很好，但是如果您想要50个列，那么任何使查询变得(太)难以阅读的代码。

#7

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

通过@Mahomedalid和@Junaid的解决方案，我发现了一个问题。所以想要分享它。如果列名有空格或连字符，那么查询将会失败。简单的解决方法是在列名周围使用backtick。修改后的查询如下。

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;

#8

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.

如果你不想选择的列有大量的数据,而你不想把它由于速度问题,你经常选择其他列,我建议你创建一个新表的一个字段,你通常不会选择关键原始表和删除字段来自原始表。当需要额外的字段时，加入表格。

#9

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

您可以使用描述my_table并使用它的结果动态生成SELECT语句。

#10

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.

结果是来自users表的所有列，以及来自元表的另外两个列。

#11

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.

我喜欢@Mahomedalid的答案，除了@Bill Karwin的评论。@Jan Koritak提出的可能的问题是我面对的，但是我已经找到了一个技巧，并且想要在这里分享给任何面对这个问题的人。

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

我们可以将替换函数替换为已准备语句的子查询中的where子句:

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

因此，这将只包含字段id，而不包括company_id。

Hope this will help anyone looking for a solution.

希望这能帮助任何寻求解决方案的人。

Regards

问候

#12

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

#13

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.

我同意列出所有列的“简单”解决方案，但这可能是繁重的工作，而拼写错误会导致大量的浪费时间。我使用一个函数“getTableColumns”来检索适合粘贴到查询中的列的名称。然后我要做的就是删除那些我不想要的。

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

col1、col2 col3、col4…col53

#14

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.

我同意，选择*是不够的，如果您不需要的，如其他地方所提到的，是一个BLOB，您不希望有这种开销。

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.

我将用所需的数据创建一个视图，然后您可以在舒适中选择*，如果数据库软件支持它们的话。另外，将巨大的数据放在另一个表中。

#15

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.

一开始我以为你可以使用正则表达式，但我一直在阅读MYSQL文档，看起来你做不到。如果我是您，我将使用另一种语言(如PHP)生成您想要获取的列的列表，将其存储为字符串，然后使用该字符串生成SQL。

#16

Just do

只做

SELECT * FROM table WHERE whatever

Then drop the column in you favourite programming language: php

然后在您最喜欢的编程语言:php中删除列。

while (($data = mysql_fetch_array($result, MYSQL_ASSOC)) !== FALSE) {
   unset($data["id"]);
   foreach ($data as $k => $v) { 
      echo"$v,";
   }      
}

#17

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

它是如何工作的，你输入表格，然后是你不想要的列或者数组里的数组:数组("id"，"name"，"whatevercolumn")

So in select you could use it like this:

所以在select中你可以这样使用:

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

or

或

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

#18

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!

虽然我同意Thomas的回答(+1;))，但我想补充一点，我将假设您不需要的列几乎不包含任何数据。如果它包含大量的文本、xml或二进制blobs，那么就花时间逐个选择每一列。否则你的表现将会受到影响。干杯!

#19

Yes, though it can be high I/O depending on the table here is a workaround I found for it.

是的，虽然它可以是高的I/O，取决于这张桌子是我为它找到的一个变通方法。

Select *
into #temp
from table

alter table #temp drop column column_name

Select *
from #temp

#20

The answer posted by Mahomedalid has a small problem:

Mahomedalid的回答有一个小问题:

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.

内部替换函数代码替换了“ ”，“”，这个替换有一个问题，如果要替换的字段是concat字符串中的最后一个，因为最后一个没有char逗号“，”并且没有从字符串中删除。

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)

在我的例子中，删除的列被字符串“field_remove”替换，因为我正在尝试安全内存。(我删除的字段是一个大约1MB的BLOB)

#21

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

基于@Mahomedalid的回答，我已经做了一些改进来支持“除了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

如果您有很多cols，可以使用此sql来更改group_concat_max_len。

SET @@group_concat_max_len = 2048;

#22

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'

#23

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

@Mahomedalid达成一致的答案。但是我不想做一些准备好的语句，我不想输入所有的字段。所以我得到的是一个愚蠢的解决方案。进入phpmyadmin->sql->select的表格，它转储查询副本并完成!:)

#24

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.

否则，我不这么认为。

#25

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.

如果您喜欢并评估它所生成的SQL，您可以使用SQL来生成SQL。这是一个通用的解决方案，它从信息模式中提取列名。下面是Unix命令行中的一个示例。

Substituting

替换

• MYSQL with your mysql command
• MYSQL和MYSQL命令。
• 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.

现在可以在构造的查询中重用$column_list字符串。

#26

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.

您可以使用像MySQL Workbench这样的DB工具来为您生成select语句，因此您只需手动删除生成语句的这些列并将其复制到您的SQL脚本中。

In MySQL Workbench the way to generate it is:

在MySQL Workbench中生成它的方法是:

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

右击表->发送到Sql编辑->选择所有语句。

#27

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

我很晚才对这个问题给出一个答案，这是我一贯的做法，坦率地说，它比最好的答案要好100倍，我只希望有人能看到它。并觉得它很有用

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

#28

Select * is a SQL antipattern. It should not be used in production code for many reasons including:

Select *是一个SQL反模式。它不应该在生产代码中使用，原因有很多，包括:

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%.

这意味着您可能会发送比您需要的更多的数据，从而导致服务器和网络瓶颈。如果您有一个内部连接，发送超过所需数据的概率是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).

它可以破坏视图(我知道这在SQl server中是正确的，它可能在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.

如果某人愚蠢到用一个不同的顺序(你不应该这样做，但是会发生所有的时间)来重新构建表，那么所有的代码都可以中断。例如，当您突然将城市放入address_3字段中，而没有指定的时候，数据库只能执行列的顺序。当数据类型发生变化时，这已经足够糟糕了，但当交换的列具有相同的数据类型时，情况就更糟了，因为您可以在某个时候插入糟糕的数据，从而清理混乱。您需要关心数据完整性。

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.

这对的时间加起来要添加的列名称(见鬼你甚至可能有一个接口,允许你拖过的列名称(我知道我在SQL Server,我打赌有一些方法是使用一些工具编写mysql查询)。让我们看看，“我可能会导致维护问题，我可能会导致性能问题，我可能会导致数据完整性问题，但是，嘿，我节省了5分钟的开发时间。”只需要输入特定的列。

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&ie=UTF8&qid=1389896688&sr=1-1&keywords=sql+antipatterns

我还建议你阅读这本书:http://www.amazon.com/sql - antipatterns-programing- programmers-ebook/dp/b00a376bb2/ref=sr _1_ 1?s=digital-text&ie=UTF8&qid=1389896688&sr=1-1&关键字=sql+反模式。