我正在尝试使用select语句获取某个MySQL表中除了某一列之外的所有列。有没有简单的方法可以做到这一点?
编辑:该表中有53个列(不是我设计的)。
我正在尝试使用select语句获取某个MySQL表中除了某一列之外的所有列。有没有简单的方法可以做到这一点?
编辑:该表中有53个列(不是我设计的)。
Mahomedalid的答案有一个小问题:
在replace函数中,代码替换了"<columns_to_delete>,
"为"",如果要替换的字段是连接字符串中的最后一个,则这种替换会存在问题,因为最后一个字段没有逗号“,”而且不会从字符串中删除。
我的建议:
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)
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'
虽然我同意Thomas的答案(+1; ),但是我想要补充一点,假设你不想要的那一列几乎没有数据。如果它包含大量文本、xml或二进制对象,则需要花费时间逐个选择每个列。否则,您的性能会受到影响。干杯!
我也希望如此,因此我创建了一个函数。
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");
被接受的答案存在几个缺点。
所有这些问题都可以通过简单地在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
* 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 的未来版本也会支持这种语法吧?