Как выводить в запросе все столбцы кроме одного, не перечисляя их?
Другими словами, как исключить столбец с известным именем из результата запроса select * from Table?
Перечислять столбцы также приходится в операторе INSERT, исключая из списка автоинкрементируемые столбцы или столбцы, которым нужно присвоить значение по умолчанию.
Допустим, требуется выбрать все столбцы из таблицы Laptop кроме столбца code. Чтобы вывести все столбцы таблицы, достаточно написать
Но чтобы исключить из списка столбец code, мы вынуждены перечислить все остальные столбцы:
Было бы неплохо, если мы могли написать что-то типа
Подобная возможность позволила бы нам избежать рутинной работы с вероятностью ошибиться при наборе и пригодилась бы при динамическом формировании запроса, когда заранее не известна ни таблица, ни число столбцов в ней.
Но, увы, такой возможности у нас нет. Зато мы можем сформировать список столбцов с помощью вспомогательного скрипта, чтобы в дальнейшем использовать его результаты в запросах. Это мы можем сделать, используя стандартное представление метаданных, которое называется информационной схемой:
Предикат NOT IN мы используем для того, чтобы можно было в перспективе исключать не один, а несколько столбцов. Теперь нам нужно получить результат в виде строки символов, представляющей собой разделенный запятыми перечень столбцов. Для этого нам уже придется использовать нестандартные средства.
SQL Server
В частности, для SQL Server мы можем это сделать при помощи конструкции FOR XML PATH, заменив попутно с помощью функции REPLACE пробел между именами столбцов на запятые:
В принципе, мы в состоянии сформировать требуемый оператор целиком с тем, чтобы его можно было динамически использовать в коде приложения:
MySQL
В случае MySQL мы можем воспользоваться специальной агрегатной функцией GROUP_CONCAT:
Следует отметить, что в MySQL информационная схема относится не к отдельной базе данных, а ко всему серверу. Поэтому на тот случай, если в разных базах сервера имеются таблицы с одинаковыми именами, в условия отбора нужно добавить предикат с указанием схемы (базы данных): TABLE_SCHEMA=’computers’.
Конкатенация в MySQL выполняется с помощью функции CONCAT. В итоге окончательное решение нашей задачи можно написать так:
PostgreSQL
В PostgreSQL задачу представления значений столбца в виде текстового списка можно решить при помощи двух встроенных функций: ARRAY и ARRAY_TO_STRING. Первая из них преобразует выборку в массив значений, а вторая преобразует массив в список. При этом вторым параметром функции ARRAY_TO_STRING задается символ, который будет являться разделителем элементов списка. Теперь решение нашей задачи можно записать в виде:
Источник
Выберите все столбцы кроме одного в MySQL?
Я пытаюсь использовать инструкцию select для получения всех столбцов из определенной таблицы MySQL, кроме одного. Есть ли простой способ сделать это?
EDIT: в этой таблице 53 столбца (не мой дизайн)
28 ответов
На самом деле есть способ, вам нужно иметь разрешения, конечно, для этого .
замена
, and
посмотреть бы работать лучше в этом случае?
в MySQL definitions (manual) нет такой вещи. Но если у вас действительно большое количество столбцов col1 , . col100 , следующее Может быть полезным:
вы можете сделать:
не получая column3, хотя, возможно, вы искали более общее решение?
Если вы хотите исключить значение поля, например, для проблем безопасности / конфиденциальной информации, вы можете получить этот столбец как null.
насколько мне известно, нет. Вы можете сделать что-то вроде:
и вручную выберите столбцы, которые вы хотите. Однако, если вам нужно много столбцов, вы можете просто сделать:
и просто игнорировать то, что вы не хотите.
в вашем конкретном случае, я бы предложил:
если вы не хотите только несколько столбцов. Если вы хотите только четыре столбца, то:
было бы хорошо, но если вы хотите 50 столбцов, тогда любой код, который делает запрос, станет (тоже? трудно читать.
при попытке решения @Mahomedalid и @Junaid я нашел проблему. Так думал делиться этим. Если имя столбца имеет пробелы или дефисы, такие как регистрация, запрос завершится ошибкой. Простым обходным путем является использование backtick вокруг имен столбцов. Измененный запрос находится ниже
Если в столбце, который вы не хотели выбирать, было огромное количество данных, и вы не хотели включать его из-за проблем со скоростью, и вы часто выбираете другие столбцы, я бы предложил вам создать новую таблицу с одним полем, которое вы обычно не выбираете с ключом к исходной таблице, и удалить поле из исходной таблицы. Присоединяйтесь к таблицам, когда это дополнительное поле действительно требуется.
можно использовать описать таблицы my_table и используйте результаты этого для динамического создания оператора SELECT.
моя главная проблема-это много столбцов, которые я получаю при объединении таблиц. Хотя это не ответ на ваш вопрос (Как выбрать все, кроме определенных столбцов из один таблица), я думаю, стоит упомянуть, что вы можете указать table. чтобы получить все столбцы из таблицы, а не просто задание .
вот пример, как это может быть очень полезно:
в результате все столбцы из таблицы users, и два дополнительные столбцы, которые были объединены в мета таблице.
мне понравился ответ от @Mahomedalid кроме этого факта сообщается в комментарии от @Bill Karwin . Возможные проблемы @Jan Koritak Это правда, я столкнулся с этим, но я нашел трюк для этого и просто хочу поделиться им здесь для тех, кто сталкивается с проблемой.
мы можем заменить функцию REPLACE предложением where в подзапросе подготовленного оператора следующим образом:
используя мою таблицу и имя столбца
таким образом, это будет исключать только поле id а не company_id
надеюсь, что это поможет всем, кто ищет решение.
рекомендуется указывать столбцы, которые вы запрашиваете, даже если вы запрашиваете все столбцы.
поэтому я бы предложил вам написать имя каждого столбца в инструкции (исключая тот, который вы не хотите).
Я согласен с» простым » решением перечисления всех столбцов, но это может быть обременительно, и опечатки могут вызвать много потерянного времени. Я использую функцию «getTableColumns» для получения имен моих столбцов, подходящих для вставки в запрос. Тогда все, что мне нужно сделать, это удалить те, которые я не хочу.
ваш результат возвращает строку с разделителями-запятыми, например.
столбец col1,и col2,с col3,col4. col53
Я согласен, что этого недостаточно для Select * , если тот, который вам не нужен, как упоминалось в другом месте, является каплей, вы не хотите, чтобы этот накладной полз.
Я бы создал посмотреть с необходимыми данными, то вы можете Select * в комфорт-если СУБД их поддерживает. В противном случае, поместите огромные данные в другую таблицу.
сначала я думал, что вы можете использовать регулярные выражения, но так как я читал MYSQL docs Кажется, вы не можете. На вашем месте я бы использовал другой язык (например, PHP) для создания списка столбцов, которые вы хотите получить, сохраните его как строку, а затем используйте ее для создания SQL.
затем отбросьте столбец на вашем любимом языке программирования: php
Я тоже этого хотел, поэтому вместо этого создал функцию.
Итак, как это работает, вы вводите таблицу, а затем столбец, который вы не хотите или как в массиве: array («id»,»name»,»whatevercolumn»)
поэтому в select вы можете использовать его следующим образом:
хотя я согласен с ответом Томаса (+1 ;)), я хотел бы добавить оговорку, что я буду считать столбец, что вы не want содержит едва ли какие-либо данные. Если он содержит огромное количество текста, xml или двоичных двоичных объектов, выделите каждый столбец по отдельности. Иначе пострадает ваше выступление. Ура!
да, хотя он может быть высоким I / O в зависимости от таблицы, вот обходной путь, который я нашел для него.
ответ написал Mahomedalid есть небольшая проблема:
внутри заменить код функции заменял» , «by»», эта замена имеет проблему, если поле для замены является последним в строке concat из-за того, что последнее не имеет запятой char», » и не удаляется из строки.
замена
, и
удаленный столбец заменяется строкой «FIELD_REMOVED» в моем дело в том, что я пытался сохранить память. (Поле, которое я удалял, является BLOB около 1 МБ)
основываясь на ответе @Mahomedalid, я сделал некоторые улучшения для поддержки «выберите все столбцы, кроме некоторых в mysql»
Если у вас есть много cols, используйте этот sql для изменения group_concat_max_len
может быть, у меня есть решение января Koritak это указал на несоответствие
стол :
Результат Запроса:
согласен с ответом @Mahomedalid. Но я не хотел делать что-то вроде подготовленного заявления, и я не хотел печатать все поля. Так что у меня было глупое решение. Перейдите в таблицу в phpmyadmin — >sql — >select, она сбрасывает копию запроса replace и готово! 🙂
Если это всегда один и тот же столбец, то вы можете создать представление, в котором его нет.
в противном случае, нет я так не думаю.
вы можете использовать SQL для создания SQL, если хотите, и оценить SQL, который он производит. Это общее решение, поскольку оно извлекает имена столбцов из информационной схемы. Вот пример из командной строки Unix.
MYSQL с вашей командой mysql
таблица с именем таблица
EXCLUDEDFIELD с исключенным именем Поля
вам действительно нужно будет только извлечь имена столбцов таким образом, только один раз для создания списка столбцов исключается этот столбец, а затем просто используйте созданный запрос.
Теперь вы можете использовать $column_list строка в запросах, которые вы создаете.
Я хотел бы добавить еще одну точку зрения для того, чтобы решить эту проблему, особенно если у вас есть небольшое количество столбцов, чтобы удалить.
вы можете использовать инструмент DB, как MySQL Workbench чтобы сгенерировать оператор select для вас, вам просто нужно вручную удалить эти столбцы для сгенерированного оператора и скопировать его в сценарий SQL.
в MySQL Workbench способ его генерации:
щелкните правой кнопкой мыши по таблице — > отправить Редактор Sql — > Выбрать Все Инструкции.
Я довольно поздно на throing ответ для этого, положить это так, как я всегда это делал, и, честно говоря, его в 100 раз лучше и аккуратнее, чем лучший ответ, я только надеюсь, что кто-то это увидит. И найти его полезным
Select * — это SQL-антипаттерн. Он не должен использоваться в производственном коде по многим причинам, включая:
Это займет немного больше времени. Когда вещи запускаются миллионы раз, эти крошечные кусочки могут иметь значение. Медленная база данных, где медленность вызвана этим типом небрежного кодирования, является самым сложным для настройки производительности.
Это означает, что вы, вероятно, отправляете больше данных, чем вам нужно, что вызывает узкие места сервера и сети. Если у вас есть внутреннее соединение, шансы отправки большего количества данных, чем вам нужно, составляют 100%.
Это вызывает проблемы в обслуживании особенно, когда вы добавили новые столбцы, которые вы не хотите видеть везде. Далее, Если у вас есть новый столбец, вам может потребоваться что-то сделать с интерфейсом, чтобы определить, что делать с этим столбцом.
Он может разбивать представления (я знаю, что это правда в SQl server, это может быть или не быть правдой в mysql).
Если кто-то достаточно глуп, чтобы восстановить таблицы со столбцами в другом порядке (что вы не должны делать, но это происходит все время), все виды кода могут сломаться. Особенно кода для вставки, например, если вдруг вы ставите город в адрес_3 поле, потому что без указания, на базе может идти только на порядок столбцов. Это достаточно плохо, когда типы данных меняются, но хуже, когда замененные столбцы имеют один и тот же тип данных, потому что вы можете иногда вставлять плохие данные, которые беспорядок для очистки. Вы должны заботиться о целостности данных.
Если он используется во вставке, он сломает вставку, если новый столбец будет добавлен в одну таблицу, но не в другую.
Это может разрушить триггеры. Проблемы триггера может быть трудно диагностировать.
сложите все это против времени, необходимого для добавления имен столбцов (черт возьми, у вас даже может быть интерфейс, который позволяет перетаскивать имена столбцов (я знаю, что я делаю в SQL Server, я бы поспорил, что есть какой — то способ сделать это какой инструмент вы используете для написания запросов MySQL.) Давайте посмотрим: «я могу вызвать проблемы с обслуживанием, я могу вызвать проблемы с производительностью, и я могу вызвать проблемы с целостностью данных, но эй, я сэкономил пять минут времени разработки.»На самом деле просто поместите конкретные столбцы, которые вы хотите.
Источник
SQL: SELECT все столбцы, кроме некоторых
Есть ли способ для SELECT всех столбцов в таблице, кроме определенных? Это было бы очень удобно для выбора всех неблобных или негеометрических столбцов из таблицы.
Однажды я слышал, что эта функциональность была преднамеренно исключена из стандарта SQL, поскольку изменение добавления столбцов в таблицу изменит результаты запроса. Это правда? Является ли аргумент действительным?
Есть ли обходной путь, особенно в PostgreSQL?
Такая функция не существует ни в Postgres, ни в стандарте SQL (AFAIK). Я думаю, что это довольно интересный вопрос, поэтому я немного погуглил и наткнулся на интересную статью на postgresonline.com .
Они показывают подход, который выбирает столбцы непосредственно из схемы:
Вы можете создать функцию, которая делает что-то подобное. Такие темы также обсуждались в списках рассылки, но общий консенсус был почти таким же: запросить схему.
Я уверен, что есть другие решения, но я думаю, что все они будут включать в себя какую-то магическую схему-запрос-foo.
Кстати: будьте осторожны с SELECT * . этим, поскольку это может иметь потери производительности
Реальный ответ заключается в том, что вы просто не можете практически. Эта функция была востребована на протяжении десятилетий, и разработчики отказались от ее реализации.
Популярный ответ, предлагающий запрос таблиц схемы, не сможет работать эффективно, потому что оптимизатор Postgres считает динамические функции черным ящиком (см. Контрольный пример ниже). Это означает, что индексы не будут использоваться и соединения не будут выполняться интеллектуально. Вам было бы намного лучше с какой-нибудь макро-системой, такой как m4. По крайней мере, это не смущает оптимизатор (но может все еще смущать вас). Без разветвления кода и написания функции самостоятельно или использования интерфейса языка программирования, который вы застряли.
Ниже я написал простое доказательство концепции, показывающее, насколько плохой будет производительность при очень простом динамическом выполнении в plpgsql. Также обратите внимание, что ниже я должен привести функцию, возвращающую общую запись к определенному типу строки, и перечислить столбцы. Таким образом, этот метод не будет работать для «выберите все, кроме», если вы не хотите переделать эту функцию для всех ваших таблиц.
Как вы можете видеть, вызов функции сканировал всю таблицу, в то время как прямой запрос использовал индекс ( 95,46 мс против 00,07 мс .). Эти типы функций будут включать любой сложный запрос, который должен использовать индексы или объединять таблицы в правильном порядке. ,
На самом деле это возможно в PostgreSQL, начиная с 9.4, где был представлен JSONB. Я размышлял о подобном вопросе о том, как показать все доступные атрибуты в Google Map (через GeoJSON).
johto на канале irc предлагает попробовать удалить элемент из JSONB.
Хотя вы получаете JSON вместо отдельных столбцов, это было именно то, что я хотел. Возможно, JSON можно расширить обратно в отдельные столбцы.
Единственный способ, которым вы можете (не говорите, что должны), это использовать динамические операторы SQL. Легко (как писал DrColossos) запрашивать системные представления, находить структуру таблицы и строить правильные операторы.
PS: Почему вы хотите выбрать все / некоторые столбцы, не зная / не записав точно структуру вашей таблицы?
Динамически, как указано выше, является единственным ответом, но я не буду рекомендовать его. Что, если вы добавите больше столбцов в долгосрочной перспективе, но они не обязательно требуются для этого запроса?
Вы бы начали тянуть больше столбца, чем вам нужно.
Что делать, если выбор является частью вставки, как в
Вставить в таблицу A (col1, col2, col3 .. coln) Выбрать все, кроме 2 столбцов ИЗ таблицыB
Соответствие столбцов будет неправильным, и ваша вставка не удастся.
Это возможно, но я все же рекомендую писать каждый необходимый столбец для каждого написанного выбора, даже если требуется почти каждый столбец.
Если ваша цель состоит в том, чтобы убрать помехи с экрана во время отладки, не отображая столбцы с большими значениями данных, то вы можете использовать следующий прием:
(установите пакет «hstore» contrib, если у вас его еще нет: » CREATE EXTENSION hstore; «)
Для таблицы «test» с col1, col2, col3 вы можете установить значение «col2» в null перед отображением:
Или установите два столбца в null перед отображением:
предостережение в том, что «test» должен быть таблицей (псевдоним или подвыбор не будет работать), так как должен быть определен тип записи, передаваемый в hstore.
Я только что обнаружил обходной путь, но он требует отправки SQL-запросов из R. Он может быть полезен для пользователей R.
По сути, dplyr пакет отправляет запросы SQL (и особенно PostgreSQL) и принимает -(column_name) аргумент.
Итак, ваш пример может быть записан следующим образом:
В комментарии вы объясняете, что ваш мотив состоит в том, чтобы удобнее не отображать содержимое столбцов с длинным содержимым, а не отображать сам столбец:
… Иногда я хочу запросить таблицу с геометрическим столбцом, не отображая очень длинную геометрическую строку, которая искажает вывод. Я не хочу указывать все столбцы, потому что может быть несколько десятков.
Это возможно с помощью вспомогательной функции, которая заменяет длинное содержимое на null (любой text столбец в моем примере, но вы бы изменили его для типов, которые вы хотите подавить):
С точки зрения приложения это ленивое решение. Приложение вряд ли автоматически узнает, что делать с новыми столбцами.
Приложения браузера данных могут запрашивать метаданные для данных и исключать столбцы из выполняемых запросов или выбирать подмножество данных столбца. Новые BLOB-объекты могут быть исключены при добавлении. Данные BLOB для определенных строк могут быть выбраны по запросу.
В любом варианте SQL, который поддерживает динамические запросы, запрос может быть построен с использованием запроса к метаданным таблиц. Для вашего намерения я бы исключил столбцы по типу, а не по имени.
Вы никогда не видите * в SQL-VIEWS . проверьте \d any_view на свой psql . Существует (интроспективная) предварительная обработка для внутреннего представления.
Все обсуждение здесь показывает , что предложение вопроса (неявное в вопросе и дискуссиях) является синтаксис для программистов, а не реальный «оптимизация SQL вопроса» . Ну, я думаю, это на 80% программист.
Так что может быть реализовано как « предварительный анализ с самоанализом» . Посмотрите, что делает PostgreSQL, когда вы объявляете SQL-VIEW с помощью SELECT * : конструктор VIEW превращается * в список всех столбцов (посредством самоанализа и в тот момент, когда вы запускаете СОЗДАТЬ ВИД исходного кода).
Реализация для CREATE VIEW и PREPARE
Это жизнеспособная реализация. Предположим, таблица t с полями (id serial, name text, the_geom geom) .
То же самое для ПОДГОТОВКИ .
. так, это возможно, и это то, что нужно 80% программистов, синтаксический сахар для ПОДГОТОВКИ и ПРОСМОТРОВ!
Примечание: конечно жизнеспособный синтаксис возможно не — column_name , если есть какой — то конфликт в PostgreSQL, так что мы можем предложить EXCEPT column_name , EXCEPT (column_name1, column_name2, . column_nameN) или другие.
