16

Possible duplicate:
Select all columns except one in 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 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.

JustCarty
  • 3,839
  • 5
  • 31
  • 51
웃웃웃웃웃
  • 11,829
  • 15
  • 59
  • 91
  • 6
    @sirwilliam If you are going to increment a number by 5, are you going to write a += 5; because you are to lazy to type out a = a+1+1+1+1+1? Writing code is often times about readability. If in your SQL query you want to select all columns except for one, the closest form to what you are willing to accomplish would indeed be something like `SELECT * EXCEPT lame_field FROM ...` – Jules Colle Apr 11 '13 at 16:36
  • 1
    this stupid that they dont have a simpler way of doing this, like SELET * , ~id ...etc when there is Create table with SELECT FROM most frequently we want to omit the auto id, – mahen3d May 21 '14 at 06:54
  • I totally agree with mahen3d , wtf? I have 2 tables in SQL that share the same structure. If in the future I wish to modify / add / delete columns I don't want to have to also add these columns in some part of the code. It's less mantainable AND A BAD IDEA that WILL CAUSE YOUR APP TO BUG. – Juan Vilar Feb 23 '15 at 12:16

3 Answers3

12

you can do it easily like that

lets say your field is an id = 5

then

   select * from your_table where id !=5

and if you mean columns

lets say you dont want select 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;
echo_Me
  • 37,078
  • 5
  • 58
  • 78
7

Yes you can fetch from 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;
Hary
  • 5,690
  • 7
  • 42
  • 79
6

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.

$fields = implode(',',$fields_arr);
$sql = SELECT $fields FROM `tablename`;
Mithun Sen
  • 523
  • 5
  • 19