Выбрать все столбцы, кроме одного в MySQL?

Насколько мне известно, нет. Вы можете сделать что-то вроде:

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

без получения column3, хотя, возможно, вы искали более общее решение?

Если в столбце, который вы не хотели выбирать, было огромное количество данных, и вы не хотели включать его из-за проблем со скоростью, и вы часто выбираете другие столбцы, я бы посоветовал вам создать новую таблицу. с одним полем, которое вы обычно не выбираете ключом к исходной таблице, и удалите это поле из исходной таблицы. Присоединяйтесь к таблицам, когда это дополнительное поле действительно необходимо.

Вы можете использовать ОПИСАТЬ 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.

Хотя я согласен с ответом Томаса (+1;)), я хотел бы добавить предостережение, что я предполагаю, что столбец, который вы хотите не, почти не содержит данных. Если он содержит огромное количество текста, XML или двоичных двоичных объектов, найдите время, чтобы выбрать каждый столбец по отдельности. В противном случае ваша производительность пострадает. Ваше здоровье!

Вы можете использовать SQL для генерации SQL, если хотите, и оценить SQL, который он производит. Это общее решение, поскольку оно извлекает имена столбцов из информационной схемы. Вот пример из командной строки Unix.

Подставляя

• MYSQL с вашей командой mysql
• ТАБЛИЦА с названием таблицы
• 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,";
   }      
}

Да, хотя это может быть много операций ввода-вывода в зависимости от таблицы, вот обходной путь, который я нашел для этого.

SELECT *
INTO #temp
FROM table

ALTER TABLE #temp DROP COlUMN column_name

SELECT *
FROM #temp

В определениях mysql (руководство) такого нет. Но если у вас действительно большое количество столбцов col1, ..., col100, вам может пригодиться следующее:

DROP TABLE IF EXISTS temp_tb;
CREATE TEMPORARY TABLE ENGINE=MEMORY temp_tb SELECT * FROM orig_tb;
ALTER TABLE temp_tb DROP col_x;
   #// ALTER TABLE temp_tb DROP col_a, ... , DROP col_z; #// for a few columns to drop
SELECT * FROM temp_tb;

Я согласен с «простым» решением перечислить все столбцы, но это может быть обременительным, а опечатки могут привести к потере времени. Я использую функцию 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') )

Результатом являются все столбцы из таблицы пользователей и два дополнительных столбца, которые были объединены из метатаблицы.

Ответ, отправленный Магомедалидом, содержит небольшую проблему:

Код функции замены внутри заменял "<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», в моем случае это работает, потому что я пытался сохранить память. (Поле, которое я удалял, представляет собой большой двоичный объект размером около 1 МБ)

Ответ от @Mahomedalid мне понравился, к тому же об этом сообщается в комментарии @Bill Karwin. Возможная проблема, поднятая @Jan Koritak, верна. Я столкнулся с этим, но я нашел уловку для этого и просто хочу поделиться им здесь для всех, кто сталкивается с проблемой.

мы можем заменить функцию REPLACE предложением where в подзапросе подготовленного оператора следующим образом:

Используя имя моей таблицы и столбца

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;

Если у вас много столбцов, используйте этот 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", "anycolumn")

Итак, в 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, я обнаружил проблему. Так что подумал о том, чтобы поделиться им. Если в имени столбца есть пробелы или дефисы, такие как отметка, запрос завершится ошибкой. Простое решение - использовать обратную кавычку вокруг имен столбцов. Измененный запрос ниже

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;

Может у меня есть решение, чтобы Яна Коритака указал на несоответствие

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 Workbench, чтобы сгенерировать для вас оператор выбора, поэтому вам просто нужно вручную удалить эти столбцы для сгенерированного оператора и скопировать его в свой сценарий SQL.

В MySQL Workbench способ его создания:

Щелкните правой кнопкой мыши по таблице -> отправить в редактор Sql -> Выбрать все инструкции.

Select * - это антипаттерн SQL. Его не следует использовать в производственном коде по многим причинам, в том числе:

Обработка занимает немного больше времени. Когда что-то запускается миллионы раз, эти крошечные биты могут иметь значение. Медленная база данных, в которой медлительность вызвана этим типом неаккуратного кодирования, является самым сложным для настройки производительности.

Это означает, что вы, вероятно, отправляете больше данных, чем вам нужно, что вызывает узкие места как на сервере, так и в сети. Если у вас есть внутреннее соединение, шансы отправить больше данных, чем вам нужно, составляют 100%.

Это вызывает проблемы с обслуживанием, особенно когда вы добавляете новые столбцы, которые не хотите видеть повсюду. Кроме того, если у вас есть новый столбец, вам может потребоваться что-то сделать с интерфейсом, чтобы определить, что делать с этим столбцом.

Это может нарушить представления (я знаю, что это правда на сервере SQl, это может быть или не быть правдой в mysql).

Если кому-то хватит глупости перестроить таблицы со столбцами в другом порядке (чего не следует делать, но это происходит постоянно), любой код может сломаться. Специально код для вставки, например, когда вы внезапно помещаете город в поле address_3, потому что без указания, база данных может идти только в порядке столбцов. Это достаточно плохо, когда типы данных меняются, но хуже, когда замененные столбцы имеют один и тот же тип данных, потому что вы можете иногда вставлять неверные данные, которые нужно очистить беспорядком. Вам нужно позаботиться о целостности данных.

Если он используется во вставке, он сломает вставку, если новый столбец добавлен в одну таблицу, но не в другую.

Это может сломать триггеры. Проблемы с триггером бывает сложно диагностировать.

Сложите все это против времени, необходимого для добавления имен столбцов (черт возьми, у вас даже может быть интерфейс, который позволяет вам перетаскивать имена столбцов (я знаю, что делаю это в SQL Server, держу пари, что есть какой-то способ do this - это какой-то инструмент, который вы используете для написания запросов mysql.) Давайте посмотрим: «Я могу вызвать проблемы с обслуживанием, я могу вызвать проблемы с производительностью и я могу вызвать проблемы с целостностью данных, но эй, я сэкономил пять минут времени разработки». в нужных столбцах.

Я также предлагаю вам прочитать эту книгу: 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

Я согласен с ответом @Mahomedalid, но я не хотел делать что-то вроде подготовленного оператора, и я не хотел вводить все поля, поэтому то, что у меня было, было глупым решением.

Перейдите в таблицу в phpmyadmin-> sql-> select, он выдает запрос: скопировать, заменить и готово! :)

У принятого ответа есть несколько недостатков.

• Не работает там, где для имен таблиц или столбцов требуются обратные кавычки.
• Это не сработает, если столбец, который вы хотите пропустить, находится последним в списке.
• Требуется дважды указать имя таблицы (один раз для выбора, а другой - для текста запроса), что является избыточным и ненужным.
• Он потенциально может возвращать имена столбцов в неправильный порядок

Все эти проблемы можно решить, просто включив обратные кавычки в SEPARATOR для вашего GROUP_CONCAT и используя условие WHERE вместо REPLACE(). Для моих целей (и я представляю себе многие другие) я хотел, чтобы имена столбцов возвращались в том же порядке, в котором они появляются в самой таблице. Для этого мы используем явное предложение ORDER BY внутри функции GROUP_CONCAT():

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 Workbench, вы можете щелкнуть правой кнопкой мыши свою таблицу и выбрать 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 workbench и построителя запросов -

1. Откройте представление столбцов
2. Shift выберите все столбцы, которые вы хотите в своем запросе (в вашем случае все, кроме одного, что я делаю)
3. Щелкните правой кнопкой мыши и выберите отправить в редактор SQL-> краткое имя.
4. Теперь у вас есть список, и вы можете скопировать и вставить запрос куда угодно.