选择除mysql中只有一个字段以外的所有字段[复制]

Possible duplicate:
Select all columns except one in MySQL?

可能重复：选择除MySQL中的所有列以外的所有列？

I want to know is there a way to select all fields except one field from a table in my database.

我想知道有没有办法从我的数据库中的表中选择除一个字段之外的所有字段。

I know I can describe the field names in the select query.
For example:

我知道我可以在select查询中描述字段名称。例如：

SELECT fieldname1, fieldname2, fieldname3, fieldname4 FROM tablename;

But my question is, is there any way to do it in a simple way... Like this

但我的问题是，有没有办法以简单的方式做到这一点......就像这样

SELECT * FROM tablename EXCEPT(fieldname3);

I am using MySQL and Zend framework.

我正在使用MySQL和Zend框架。

3 个解决方案

#1

you can do it easily like that

你可以轻松地做到这一点

lets say your field is an id = 5

假设你的字段是id = 5

then

然后

   select * from your_table where id !=5 

and if you mean columns

如果你的意思是列

lets say you dont want select column3

假设您不想选择column3

then

然后

   select column1,column2,column4 from tablename;

if you have many columns

如果你有很多列

    SET @sql = CONCAT('SELECT ', (SELECT REPLACE(GROUP_CONCAT(COLUMN_NAME),  '<columns_to_delete>,', '') 
    FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '<table>'   AND TABLE_SCHEMA = '<database>'), ' FROM <table>');

    PREPARE stmt1 FROM @sql;
   EXECUTE stmt1;

#2

Yes you can fetch from information_schema.columns

是的，您可以从information_schema.columns中获取

SET @sql = CONCAT('SELECT ', (SELECT GROUP_CONCAT(COLUMN_NAME) FROM 
information_schema.columns WHERE table_schema = 'dbo' AND table_name = 
'tablename' AND column_name NOT IN ('c1', 'c2')), 
' from dbo.tablename');  

PREPARE stmt1 FROM @sql;

EXECUTE stmt1;

#3

Try this -

尝试这个 -

SHOW FIELDS FROM `tablename` WHERE FIELD NOT IN ('f1','f2','f3');

Execute this query and fetch the specific field-names and put each field-name into an array. then implode the array with ',' inside the select query.

执行此查询并获取特定的字段名称，并将每个字段名称放入一个数组中。然后在select查询中使用'，'内爆数组。

$fields = implode(',',$fields_arr);
$sql = SELECT $fields FROM `tablename`;