Wybrać wszystkie kolumny oprócz jednej w MySQL?

Czy Widok byłby lepszy w tym przypadku?

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

W mysql definitions (manual) nie ma czegoś takiego. Ale jeśli masz naprawdę dużą liczbę kolumn col1,..., col100, przydatne mogą być:

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

Możesz zrobić:

SELECT column1, column2, column4 FROM table WHERE whatever

Bez uzyskania column3, choć może szukałeś bardziej ogólnego rozwiązania?

Jeśli chcesz wykluczyć wartość pola, np. dla kwestii bezpieczeństwa / poufnych informacji, możesz pobrać tę kolumnę jako null.

Np.

SELECT *, NULL AS salary FROM users

Z tego co wiem, nie ma. możesz zrobić coś w stylu:

SELECT col1, col2, col3, col4 FROM tbl

I ręcznie wybrać kolumny, które chcesz. Jednak, jeśli chcesz dużo kolumn, możesz po prostu zrobić:

SELECT * FROM tbl 

W twoim konkretnym przypadku, proponuję:

SELECT * FROM tbl

Chyba, że chcesz tylko kilka kolumn. Jeśli chcesz tylko cztery kolumny, to:

SELECT col3, col6, col45, col 52 FROM tbl

Byłoby dobrze, ale jeśli chcesz 50 kolumn, to każdy kod, który sprawia, że zapytanie stanie się (też?) trudne do odczytania.

Podczas próby rozwiązań @Mahomedalid i @ Junaid znalazłem problem. Więc postanowiłem się tym podzielić. Jeśli nazwa kolumny ma spacje lub myślniki, takie jak check-in, zapytanie nie powiedzie się. Prostym obejściem jest użycie backtick wokół nazw kolumn. Zmodyfikowane zapytanie znajduje się poniżej

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;

Jeśli kolumna, której nie chcesz wybrać, zawiera ogromną ilość danych, a nie chcesz jej dołączać ze względu na problemy z prędkością i często zaznaczasz inne kolumny, sugerowałbym utworzenie nowej tabeli z jednym polem, którego zwykle nie wybierasz za pomocą klucza do oryginalnej tabeli i usunięcie pola z oryginalnej tabeli. Dołącz do tabel, gdy to Dodatkowe pole jest rzeczywiście wymagane.

Możesz użyć opisz my_table i użyć tego wyniku do dynamicznego generowania instrukcji SELECT.

Moim głównym problemem jest wiele kolumn, które dostaję podczas łączenia tabel. Chociaż nie jest to odpowiedź na twoje pytanie (Jak wybrać wszystkie kolumny z jednej tabeli), myślę, że warto wspomnieć, że możesz określić table., Aby uzyskać wszystkie kolumny z konkretnej tabeli, zamiast tylko określać .

Oto przykład, jak to może być bardzo przydatne:

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

Wynikiem są wszystkie kolumny z tabeli users oraz dwie dodatkowe kolumny, które zostały dołączył z tabeli meta.

Podobała mi się odpowiedź z @Mahomedalid poza tym fakt poinformowany w komentarzu z @Bill Karwin. Ewentualny problem podniesiony przez @Jan Koritak jest prawdą, że stanąłem przed tym, ale znalazłem na to sztuczkę i po prostu chcę podzielić się nim tutaj dla każdego, kto ma do czynienia z problemem.

Możemy zastąpić funkcję REPLACE klauzulą where w zapytaniu podrzędnym przygotowanej instrukcji w następujący sposób:

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;

Więc to wyklucza tylko pole id, ale nie company_id

Nadzieja pomoże to każdemu, kto szuka rozwiązania.

Pozdrawiam

Dobrą praktyką jest określanie kolumn, które odpytywasz, nawet jeśli odpytywasz wszystkie kolumny.

Proponuję więc wpisać nazwę każdej kolumny w oświadczeniu (z wyłączeniem tej, której nie chcesz).

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

FROM table

Zgadzam się z" prostym " rozwiązaniem polegającym na wypisaniu wszystkich kolumn, ale może to być uciążliwe, a literówki mogą powodować dużo zmarnowanego czasu. Używam funkcji "getTableColumns", aby pobrać nazwy moich kolumn odpowiednie do wklejenia do zapytania. Więc wszystko, co muszę zrobić, to usunąć te, których nie chcę.

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;

Twój wynik zwraca na przykład łańcuch rozdzielany przecinkami...

Col1,col2,col3,col4,...col53

Zgadzam się, że nie wystarczy Select *, jeśli ten, którego nie potrzebujesz, jak wspomniano gdzie indziej, jest BLOBEM, nie chcesz mieć tego nad głową.

Stworzyłbym widok z wymaganymi danymi, wtedy możesz Select * wygodnie --jeśli oprogramowanie bazy danych je obsługuje. W przeciwnym razie umieść ogromne dane w innej tabeli.

Na początku myślałem, że możesz używać wyrażeń regularnych, ale ponieważ czytałem dokumenty MYSQL wydaje się, że nie możesz. Gdybym był tobą, użyłbym innego języka (np. PHP), aby wygenerować listę kolumn, które chcesz uzyskać, zapisać ją jako ciąg znaków, a następnie użyć go do wygenerowania SQL.

Po prostu zrób

SELECT * FROM table WHERE whatever

Następnie wrzuć kolumnę do swojego ulubionego języka programowania: php

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

Ja też tego chciałem, więc zamiast tego stworzyłem funkcję.

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

Więc jak to działa jest to, że wpisujesz tabelę, a następnie kolumnę, której nie chcesz lub jak w tablicy: array("id","name","whatevercolumn")

Więc w select możesz go używać tak:

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

Lub

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

Chociaż zgadzam się z odpowiedzią Tomasza (+1;)), chciałbym dodać zastrzeżenie, że zakładam, że kolumna, której nie chcesz , zawiera prawie żadne dane. Jeśli zawiera ogromne ilości blobów tekstowych, xml lub binarnych, poświęć trochę czasu, aby wybrać każdą kolumnę z osobna. Inaczej twoja wydajność ucierpi. Zdrowie!

Tak, choć może być wysoki I / O w zależności od tabeli tutaj jest obejście znalazłem dla niego.

Select *
into #temp
from table

alter table #temp drop column column_name

Select *
from #temp

Odpowiedź wysłana przez Mahomedalid ma mały problem:

Wewnątrz kodu funkcji replace zastępowano "<columns_to_delete>, "przez"", ten zamiennik ma problem, jeśli pole do zastąpienia jest ostatnim w łańcuchu concat, ponieważ ostatnie nie ma przecinka znakowego", " i nie jest usuwane z łańcucha.

Moja propozycja:

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

Zastępowanie <table>, <database> oraz "

Usunięta kolumna zostaje zastąpiona ciągiem "FIELD_REMOVED" w moim przypadku to działa, ponieważ próbowałem bezpieczna pamięć. (Pole, które usuwałem to BLOB około 1MB)

Na podstawie odpowiedzi @ Mahomedalid, zrobiłem kilka ulepszeń do obsługi "wybierz wszystkie kolumny z wyjątkiem Niektórych w 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;

Jeśli masz dużo cols, użyj tego sql do zmiany group_concat_max_len

SET @@group_concat_max_len = 2048;

Być może mam rozwiązanie Jana Koritaka wskazał rozbieżność

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

Tabela:

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

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

Wynik Zapytania:

'SELECT name_eid,sal FROM employee'

Zgadzam się z odpowiedzią @ Mahomedalid. Ale nie chciałem robić czegoś takiego jak prepared statement i nie chciałem wpisywać wszystkich pól. Więc miałem głupie rozwiązanie. Przejdź do tabeli w phpMyAdmin- > SQL - > wybierz, wyrzuca kopię zapytania Zastąp i gotowe! :)

Jeśli jest to zawsze ta sama kolumna, możesz utworzyć widok, który go nie zawiera.

W Przeciwnym Razie nie sądzę.

Możesz użyć SQL do generowania SQL, jeśli chcesz i ocenić SQL, który tworzy. Jest to ogólne rozwiązanie, ponieważ wyodrębnia nazwy kolumn ze schematu informacyjnego. Oto przykład z uniksowej linii poleceń.

Zastępowanie

• MYSQL z poleceniem mysql
• Tabela z nazwą tabeli
• EXCLUDEDFIELD z wyłączoną nazwą pola

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

Naprawdę wystarczy wyodrębnić nazwy kolumn w ten sposób tylko raz, aby skonstruować lista kolumn wykluczyła tę kolumnę, a następnie po prostu użyj zapytania, które stworzyłeś.

Więc coś w stylu:

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)

Teraz możesz ponownie użyć $column_list w zapytaniach, które konstruujesz.

Chciałbym dodać inny punkt widzenia w celu rozwiązania tego problemu, szczególnie jeśli masz niewielką liczbę kolumn do usunięcia.

Możesz użyć narzędzia DB, takiego jak MySQL Workbench, Aby wygenerować instrukcję select, więc musisz ręcznie usunąć te kolumny dla Wygenerowanej instrukcji i skopiować ją do skryptu SQL.

W MySQL Workbench sposób jego wygenerowania to:

Kliknij prawym przyciskiem myszy na tabeli - > Wyślij do edytora Sql - > wybierz Wszystkie Oświadczenie.

Jestem dość późno w throwing odpowiedzi na to, umieścić to jest sposób zawsze to zrobić i szczerze, Jego 100 razy lepiej i schludniej niż najlepsza odpowiedź, mam tylko nadzieję, że ktoś go zobaczyć. And find it useful

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

Select * jest antypaternem SQL. Nie powinien być stosowany w kodzie produkcyjnym z wielu powodów, w tym:

To trwa trochę dłużej, aby przetworzyć. Kiedy rzeczy są uruchamiane miliony razy, te małe kawałki mogą mieć znaczenie. Powolna baza danych, w której powolność jest spowodowana tego typu niechlujnym kodowaniem, jest najtrudniejsza do dostrojenia wydajności.

Oznacza to, że prawdopodobnie wysyłasz więcej danych niż potrzebujesz, co powoduje zarówno wąskie gardła serwera, jak i sieci. Jeśli masz wewnętrzny join, szanse na wysłanie większej ilości danych niż potrzebujesz są 100%.

Powoduje problemy z konserwacją, zwłaszcza gdy dodałeś nowe kolumny, których nie chcesz widzieć wszędzie. Ponadto, jeśli masz nową kolumnę, być może będziesz musiał zrobić coś z interfejsem, aby określić, co zrobić z tą kolumną.

Może łamać widoki(wiem, że to prawda w SQL serverze, może, ale nie musi być prawdą w mysql).

Jeśli ktoś jest na tyle głupi, aby przebudować tabele z kolumnami w różny sposób porządek (czego nie powinieneś robić, ale zdarza się to cały czas), każdy rodzaj kodu może się zepsuć. Espcially kod dla wstawki np. gdy nagle wstawiasz miasto do pola adres_3, ponieważ bez podania, baza danych może iść tylko w kolejności kolumn. Jest to wystarczająco złe, gdy typy danych zmieniają się, ale gorzej, gdy zamienione kolumny mają ten sam typ danych, ponieważ możesz przejść do pewnego wstawiania złych danych, które są bałaganem do czyszczenia. Musisz dbać o dane uczciwość.

Jeśli jest ona użyta w wstawce, spowoduje przerwanie wstawki, jeśli w jednej tabeli zostanie dodana nowa kolumna, ale nie w drugiej.

Może złamać wyzwalacze. Problemy z wyzwalaniem mogą być trudne do zdiagnozowania.

Dodać to wszystko w stosunku do czasu potrzebnego do dodania w nazwach kolumn (heck może nawet mieć interfejs, który pozwala przeciągać nazwy kolumn (wiem, że robię w SQL Server, założę się, że jest jakiś sposób, aby to zrobić jest jakieś narzędzie, którego używasz do pisania zapytań mysql.) Let ' s widzisz, " mogę powodować problemy z konserwacją, mogę powodować problemy z wydajnością i mogę powodować problemy z integralnością danych, ale hej, zaoszczędziłem pięć minut czasu programistycznego."Naprawdę po prostu umieść konkretne kolumny, które chcesz.

Proponuję również przeczytać tę książkę: 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