选择MySQL中除一列外的所有列?

我试图使用一个选择语句从某个MySQL表中获得除一个以外的所有列。有什么简单的方法吗?

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

444307 次浏览
小开

据我所知，没有。你可以这样做:

SELECT col1, col2, col3, col4 FROM tbl

并手动选择所需的列。然而，如果你想要很多列，那么你可能只需要做一个:

SELECT * FROM tbl

忽略你不想要的。

针对你的特殊情况，我建议:

SELECT * FROM tbl

除非你只想要几列。如果你只想要四列，那么:

SELECT col3, col6, col45, col 52 FROM tbl

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

小开

你可以:

SELECT column1, column2, column4 FROM table WHERE whatever

没有得到列3，尽管您可能在寻找一个更一般的解?

小开

如果您不想选择的列中有大量数据，并且由于速度问题而不想包括它，并且您经常选择其他列，那么我建议您使用一个通常不选择的字段创建一个新表，并从原始表中删除该字段。当实际需要额外字段时，将表连接起来。

小开

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

小开

视图在这种情况下工作得更好吗?

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

我同意Select *是不够的，如果你不需要它，正如在其他地方提到的，是一个BLOB，你不想让这个开销蔓延进来。

我会用所需的数据创建一个视图，然后你可以舒服地Select *——如果数据库软件支持它们的话。否则，将大量数据放到另一个表中。

小开

如果它总是相同的一列，那么你可以创建一个不包含它的视图。

否则，不，我不这么认为。

小开

即使要查询所有列，也最好指定要查询的列。

因此，我建议您在语句中写下每一列的名称(不包括您不想要的列)。

SELECT
col1
, col2
, col3
, col..
, col53


FROM table
小开

起初，我认为你可以使用正则表达式，但因为我一直在阅读MYSQL文档，似乎你不能。如果我是你，我会使用另一种语言(如PHP)来生成您想要获取的列的列表，将其存储为字符串，然后使用它来生成SQL。

小开

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

小开

如果愿意，可以使用SQL生成SQL，并对生成的SQL进行评估。这是一种通用的解决方案，因为它从信息模式中提取列名。下面是一个Unix命令行的示例。

替换

  • MYSQL的MYSQL命令
  • 带有表名的TABLE
  • 包含排除字段名的EXCLUDEDFIELD
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

实际上，您只需要以这种方式提取列名一次，就可以构造排除该列的列列表，然后只需使用已构造的查询。

比如:

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)

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

小开
最佳答案

实际上有一种方法，当然你需要有权限才能这样做…

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;

取代<table>, <database> and <columns_to_omit>

小开

只做

SELECT * FROM table WHERE whatever

然后用您最喜欢的编程语言php删除该列

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

是的，尽管根据表的不同，I/O可能会很高，但我找到了一个解决方案。

SELECT *
INTO #temp
FROM table


ALTER TABLE #temp DROP COlUMN column_name


SELECT *
FROM #temp
小开

(不要在大桌子上尝试，结果可能是……令人惊讶的!)

临时表

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语法可能因数据库@Denis Rozhnev而异

小开

我同意列出所有列的“简单”解决方案，但这可能会造成负担，而且打字错误可能会浪费大量时间。我使用“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;

您的结果返回一个以逗号分隔的字符串，例如…

col1、col2 col3、col4…col53

小开

我的主要问题是在连接表时获得了许多列。虽然这不是你问题的答案(如何从一个表中选择除某些列外的所有列)，但我认为值得一提的是，你可以指定table.来获取特定表中的所有列，而不仅仅是指定。

下面是一个很有用的例子:

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

结果是用户表中的所有列，以及从元表中连接的两个附加列。

小开

Mahomedalid发布的答案有一个小问题:

在replace函数代码中，将“<columns_to_delete>,”替换为“”，如果要替换的字段是concat字符串中的最后一个字段，则此替换会出现问题，因为最后一个字段没有字符逗号“，”并且没有从字符串中移除。

我的建议:

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

替换<table>， <database>和'

在我的情况下，被删除的列被字符串“FIELD_REMOVED”所取代，这是因为我试图安全内存。(我正在删除的字段是一个大约1MB的BLOB)

小开

我喜欢来自@Mahomedalid的答案，除了@Bill Karwin的评论中告知的事实。@Jan Koritak提出的可能的问题是真的，我面临过这个问题，但我已经找到了一个技巧，只是想在这里为任何面临这个问题的人分享。

我们可以在Prepared语句的子查询中用where子句替换replace函数，如下所示:

使用我的表和列名

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;

因此，这将只排除id字段，而不排除company_id字段

小开

基于@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;

如果确实有很多cols，则使用此sql语句更改group_concat_max_len

SET @@group_concat_max_len = 2048;
小开

我很晚才想出一个答案，坦率地说，这是我一直在做的事情，它比最好的答案要好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]);
小开

我也想要这个，所以我创建了一个函数。

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)."`";
}
}

所以它的工作原理是，你输入表格，然后是你不想要的列或在数组中:array("id"，"name"，"whatevercolumn")

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

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

或

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

如果你想排除一个字段的值，例如安全问题/敏感信息，你可以检索该列为空。

如。

SELECT *, NULL AS salary FROM users
小开

在尝试@Mahomedalid和@Junaid的解决方案时，我发现了一个问题。所以我想分享一下。如果列名中有空格或连字符(如check-in)，则查询将失败。简单的解决方法是在列名周围使用反标记。修改后的查询如下

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;
小开

可能我有一个解决方案Jan Koritak的指出的不符

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

表:

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

================================

查询结果:

'SELECT name_eid,sal FROM employee'
小开

我想添加另一个观点来解决这个问题，特别是如果你有少量的列要删除。

你可以使用像MySQL工作台这样的DB工具来为你生成选择语句，所以你只需要手动删除生成语句的那些列，并将其复制到SQL脚本中。

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

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

小开

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

它需要更长的时间来处理。当程序运行数百万次时，这些微小的部分就会产生影响。在缓慢的数据库中，这种缓慢是由这种类型的草率编码引起的，是最难进行性能调优的类型。

这意味着你发送的数据可能比你需要的多，这会导致服务器和网络瓶颈。如果您有一个内部连接，那么发送超过所需数据的可能性是100%。

这会导致维护问题，特别是当您添加了不想到处都看到的新列时。此外，如果您有一个新列，您可能需要对接口做一些事情，以确定对该列做什么。

它可以打破视图(我知道这在SQl server中是真的，在mysql中可能是真的，也可能不是真的)。

如果有人傻到用不同的列顺序重新构建表(你不应该这样做，但这种情况经常发生)，各种代码都可能被破坏。特别是插入代码，例如，突然将城市放到address_3字段中，因为没有指定，数据库只能按照列的顺序运行。当数据类型发生变化时，这已经够糟糕的了，但当交换的列具有相同的数据类型时，情况就更糟了，因为有时您可能会插入需要清理的糟糕数据。你需要关心数据的完整性。

如果在插入中使用它，如果在一个表中添加了新列，而在另一个表中没有添加，那么它将中断插入。

它可能会破坏触发器。触发问题可能很难诊断。

将所有这些与添加列名所花费的时间加起来(哎呀，你甚至可能有一个允许你拖拽列名的界面(我知道我在SQL Server中这样做，我打赌有一些方法可以做到这一点，你用一些工具来编写mysql查询)。让我们看看，“我可以引起维护问题，我可以引起性能问题，我可以引起数据完整性问题，但是嘿，我节省了5分钟的开发时间。”只需要填入你想要的具体列。

我也建议你读这本书: 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 < / p >

小开

我同意@Mahomedalid的回答，但我不想做一些准备好的语句，我不想输入所有的字段，所以我有一个愚蠢的解决方案。

去phpmyadmin->sql->select表，它转储查询:复制，替换和完成!：）

小开

公认的答案有几个缺点。

  • 当表或列名需要反引号时，该方法失败
  • 如果要省略的列位于列表的最后，则会失败
  • 它需要列出两次表名(一次用于选择文本，另一次用于查询文本)，这是多余和不必要的
  • 它可能返回错误的订单 . xml文件中的列名

所有这些问题都可以通过简单地在你的GROUP_CONCAT的SEPARATOR中包含反刻度，并使用WHERE条件而不是REPLACE()来克服。出于我的目的(我想很多人也是如此)，我希望列名按照它们在表中出现的顺序返回。为此，我们在GROUP_CONCAT()函数中使用了显式的ORDER BY子句:

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';
小开
我有一个建议，但不是解决办法。 如果您的一些列有较大的数据集，那么您应该尝试以下

SELECT *, LEFT(col1, 0) AS col1, LEFT(col2, 0) as col2 FROM table
小开

如果你使用MySQL工作台，你可以右键单击你的表，然后单击Send to sql editor，然后单击Select All Statement，这将创建一个列出所有字段的语句，如下所示:

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;

现在你可以删除那些你不想要的。

小开

我在周围使用这种工作，尽管它可能“跑题”;-使用mysql工作台和查询生成器-

  1. 打开列视图
  2. Shift选择所有你想在你的查询列(在你的情况下，所有但这是我所做的)
  3. 右键单击并选择发送到SQL编辑器->短名称。
  4. 现在你有了列表，然后你可以复制粘贴查询到任何地方。

enter image description here

小开

这个问题是关于MySQL的，但我仍然认为至少值得一提的是谷歌BigQuery和H2原生支持* EXCEPT语法，例如。

SELECT * FROM actor

生产:

|actor_id|first_name|last_name   |last_update            |
|--------|----------|------------|-----------------------|
|1       |PENELOPE  |GUINESS     |2006-02-15 04:34:33.000|
|2       |NICK      |WAHLBERG    |2006-02-15 04:34:33.000|
|3       |ED        |CHASE       |2006-02-15 04:34:33.000|

而

SELECT * EXCEPT (last_update) FROM actor

生产:

|actor_id|first_name|last_name   |
|--------|----------|------------|
|1       |PENELOPE  |GUINESS     |
|2       |NICK      |WAHLBERG    |
|3       |ED        |CHASE       |

也许，MySQL的未来版本也会支持这种语法?


提交答案