Выбрать все столбцы, кроме одного в MySQL?
297654 просмотра
29 ответа
Я пытаюсь использовать оператор выбора, чтобы получить все столбцы из определенной таблицы MySQL, кроме одного. Есть ли простой способ сделать это?
РЕДАКТИРОВАТЬ: в этой таблице 53 столбца (НЕ МОЙ ДИЗАЙН)
Автор: Tom Grochowicz Источник Размещён: 25.06.2019 10:52Ответы (29)
206 плюса
На самом деле есть способ, вам нужно иметь разрешения, конечно, для этого ...
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>
46 плюса
В определениях mysql (руководство) такой вещи нет. Но если у вас действительно большое количество столбцов col1
, ... col100
, может быть полезно следующее:
mysql> CREATE TEMPORARY TABLE temp_tb SELECT * FROM orig_tb;
mysql> ALTER TABLE temp_tb DROP col_x;
mysql> SELECT * FROM temp_tb;
Автор: ghionoiu
Размещён: 16.12.2010 01:15
42 плюса
Будет ли лучше работать View в этом случае?
CREATE VIEW vwTable
as
SELECT
col1
, col2
, col3
, col..
, col53
FROM table
Автор: Brian Childress
Размещён: 12.08.2008 07:11
33 плюса
Ты можешь сделать:
SELECT column1, column2, column4 FROM table WHERE whatever
без получения column3, хотя, возможно, вы искали более общее решение?
Автор: Mike Stone Размещён: 12.08.2008 06:4831 плюса
Если вы хотите исключить значение поля, например, из соображений безопасности / конфиденциальной информации, вы можете получить этот столбец как нулевой.
например
SELECT *, NULL AS salary FROM users
Автор: Sean O
Размещён: 13.06.2013 05:24
22 плюса
Насколько я знаю, нет. Вы можете сделать что-то вроде:
SELECT col1, col2, col3, col4 FROM tbl
и вручную выберите нужные столбцы. Однако, если вам нужно много столбцов, вы можете просто сделать:
SELECT * FROM tbl
и просто игнорируйте то, что вы не хотите.
В вашем конкретном случае я бы предложил:
SELECT * FROM tbl
если вы не хотите только несколько столбцов. Если вы хотите только четыре столбца, то:
SELECT col3, col6, col45, col 52 FROM tbl
было бы хорошо, но если вы хотите 50 столбцов, то любой код, который делает запрос, станет (слишком?) трудным для чтения.
Автор: Thomas Owens Размещён: 12.08.2008 06:4714 плюса
Пробуя решения @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;
Автор: Suraj
Размещён: 18.07.2013 09:01
10 плюса
Если в столбце, который вы не хотели выбирать, содержалось огромное количество данных, и вы не хотели включать его из-за проблем со скоростью, а вы часто выбирали другие столбцы, я бы предложил создать новую таблицу. с одним полем, которое вы обычно не выбираете, с ключом к исходной таблице и удалите поле из исходной таблицы. Присоединяйтесь к таблицам, когда это дополнительное поле действительно требуется.
Автор: Stacey Richards Размещён: 12.08.2008 06:569 плюса
Вы можете использовать DESCRIBE my_table и использовать результаты этого для динамического генерирования оператора SELECT.
Автор: jammycakes Размещён: 12.08.2008 07:017 плюса
Моя главная проблема - много столбцов, которые я получаю при соединении таблиц. Хотя это не ответ на ваш вопрос (как выбрать все столбцы, кроме определенных, из одной таблицы), я думаю, стоит упомянуть, что вы можете указать, чтобы получить все столбцы из определенной таблицы, а не просто указывать .table.<em></em>
Вот пример того, как это может быть очень полезно:
выберите пользователей. *, phone.meta_value в качестве телефона, zipcode.meta_value в качестве почтового индекса от пользователей оставь присоединиться к user_meta как телефон включено ((users.user_id = phone.user_id) И (phone.meta_key = 'phone')) оставьте присоединиться user_meta как почтовый индекс на ((users.user_id = zipcode.user_id) И (zipcode.meta_key = 'zipcode'))
Результатом являются все столбцы из таблицы пользователей и два дополнительных столбца, которые были объединены из мета таблицы.
Автор: cwd Размещён: 08.07.2011 12:265 плюса
Мне понравился ответ @Mahomedalid
помимо этого факта, сообщенного в комментарии от @Bill Karwin
. Возможная проблема, возникшая у @Jan Koritak
меня, - это правда, я сталкивался с этим, но я нашел для этого хитрость и просто хочу поделиться ею здесь для всех, кто столкнулся с проблемой.
мы можем заменить функцию REPLACE предложением where в подзапросе оператора Prepared следующим образом:
Используя мою таблицу и имя столбца
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
Надеюсь, что это поможет любому, кто ищет решение.
С уважением
Автор: Junaid Размещён: 15.05.2012 09:564 плюса
Рекомендуется указывать запрашиваемые столбцы, даже если вы запрашиваете все столбцы.
Поэтому я бы предложил вам написать название каждого столбца в заявлении (исключая тот, который вам не нужен).
SELECT
col1
, col2
, col3
, col..
, col53
FROM table
Автор: mbillard
Размещён: 12.08.2008 07:20
4 плюса
Просто делать
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,";
}
}
Автор: Soth
Размещён: 07.01.2010 10:02
4 плюса
Я согласен с «простым» решением перечисления всех столбцов, но это может быть обременительным, а опечатки могут привести к потере большого количества времени. Я использую функцию «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;
Ваш результат возвращает строку с запятой, например ...
Автор: David Poor Размещён: 07.01.2011 03:20col1, col2, col3, COL4, ... col53
3 плюса
Я согласен с тем, что недостаточно Select *
, если тот, который вам не нужен, как упоминалось в другом месте, является BLOB, вы не хотите, чтобы эти накладные расходы закрались.
Я хотел бы создать представление с необходимыми данными, а затем вы можете Select *
с комфортом - если программное обеспечение базы данных поддерживает их. Иначе, поместите огромные данные в другую таблицу.
3 плюса
Сначала я подумал, что вы можете использовать регулярные выражения, но, поскольку я читал документы MYSQL, кажется, вы не можете. На вашем месте я бы использовал другой язык (например, PHP) для генерации списка столбцов, которые вы хотите получить, сохранить его в виде строки и затем использовать его для генерации SQL.
Автор: icco Размещён: 12.08.2008 08:273 плюса
Я тоже этого хотел, поэтому вместо этого создал функцию.
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", "whatcolumn")
Так что в select вы можете использовать это так:
mysql_query("SELECT ".$db->getColsExcept('table',array('id','bigtextcolumn'))." FROM table");
или же
mysql_query("SELECT ".$db->getColsExcept('table','bigtextcolumn')." FROM table");
Автор: Kilise
Размещён: 22.03.2013 01:27
3 плюса
Согласитесь с ответом @ Mahomedalid. Но я не хотел делать что-то вроде подготовленного утверждения и не хотел вводить все поля. Так что у меня было глупое решение. Перейдите к таблице в phpmyadmin-> sql-> select, она выдаст копию запроса на замену и готово! :)
Автор: neelabh Размещён: 07.08.2015 06:282 плюса
Хотя я согласен с ответом Томаса (+1;)), я хотел бы добавить предостережение о том, что столбец, который вам не нужен, содержит едва ли какие-либо данные. Если он содержит огромное количество текста, xml или двоичных двоичных объектов, найдите время, чтобы выбрать каждый столбец отдельно. Ваше выступление будет страдать иначе. Ура!
Автор: OJ. Размещён: 12.08.2008 08:432 плюса
Ответ, опубликованный Махомедалидом, имеет небольшую проблему:
Внутри кода функции замены была замена " <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», в моем случае это работает, потому что я пытался сохранить память. (Поле, которое я удаляю, является BLOB-файлом размером около 1 МБ)
Автор: neurotico79 Размещён: 07.11.2011 10:522 плюса
Основываясь на ответе @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;
Автор: hahakubile
Размещён: 16.06.2012 04:36
2 плюса
Может быть, у меня есть решение указанного несоответствия Яна Коритака
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'
Автор: Bhavik Shah
Размещён: 19.07.2013 01:27
2 плюса
Да, хотя это может быть высокий уровень ввода-вывода в зависимости от таблицы, здесь я нашел обходной путь.
SELECT *
INTO #temp
FROM table
ALTER TABLE #temp DROP COlUMN column_name
SELECT *
FROM #temp
Автор: Nathan A
Размещён: 23.02.2010 10:04
1 плюс
Если это всегда один и тот же столбец, вы можете создать представление, в котором его нет.
Иначе нет, я так не думаю.
Автор: Gareth Simpson Размещён: 12.08.2008 07:181 плюс
Вы можете использовать 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
строку в построенных вами запросах.
1 плюс
Я хотел бы добавить другую точку зрения для решения этой проблемы, особенно если у вас есть небольшое количество столбцов для удаления.
Вы можете использовать инструмент БД, такой как MySQL Workbench , чтобы сгенерировать оператор выбора для вас, так что вам просто нужно вручную удалить эти столбцы для сгенерированного оператора и скопировать его в ваш скрипт SQL.
В MySQL Workbench способ его создания:
Щелкните правой кнопкой мыши по таблице -> отправить в Sql Editor -> Выбрать все выписки.
Автор: H. Paúl Cervera García Размещён: 05.12.2013 09:510 плюса
Принятый ответ имеет несколько недостатков.
- Сбой, когда имена таблиц или столбцов требуют обратных кавычек
- Сбой, если столбец, который вы хотите опустить, является последним в списке
- Это требует перечисления имени таблицы дважды (один раз для выбора и другого для текста запроса), что является избыточным и ненужным
- Потенциально может возвращать имена столбцов в неправильном порядке
Все эти проблемы могут быть преодолены путем простого включения обратных галочек в SEPARATOR
for for 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';
Автор: billynoah
Размещён: 03.06.2019 04:50
-1 плюса
Я довольно опаздываю с ответом на этот вопрос, говоря так, я всегда делал это, и, честно говоря, это в 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]);
Автор: Nick
Размещён: 21.02.2013 09:25
-4 плюса
Select * является антипаттерном SQL. Он не должен использоваться в производственном коде по многим причинам, включая:
Это займет чуть больше времени для обработки. Когда вещи запускаются миллионы раз, эти крошечные кусочки могут иметь значение. Медленная база данных, где медлительность вызвана этим типом небрежного кодирования повсюду, является самым трудным для настройки производительности.
Это означает, что вы, вероятно, отправляете больше данных, чем вам нужно, что вызывает узкие места как на сервере, так и в сети. Если у вас есть внутреннее объединение, шансы отправить больше данных, чем вам нужно, составляют 100%.
Это вызывает проблемы обслуживания, особенно если вы добавили новые столбцы, которые вы не хотите видеть повсюду. Кроме того, если у вас есть новый столбец, вам может потребоваться сделать что-то с интерфейсом, чтобы определить, что делать с этим столбцом.
Это может нарушить представления (я знаю, что это верно для сервера SQl, это может или не может быть правдой в MySQL).
Если кто-то достаточно глуп, чтобы перестроить таблицы со столбцами в другом порядке (что не следует делать, но это происходит постоянно), любой код может сломаться. Особенно код для вставки, например, когда вы неожиданно вводите город в поле address_3, так как без указания, база данных может идти только по порядку столбцов. Это достаточно плохо, когда типы данных изменяются, но хуже, когда поменяемые столбцы имеют один и тот же тип данных, потому что вы можете пойти на какое-то время, вставляя неверные данные, которые бесполезны для очистки. Вы должны заботиться о целостности данных.
Если он используется во вставке, он прервет вставку, если в одну таблицу будет добавлен новый столбец, но не во другую.
Это может сломать триггеры. Проблемы запуска могут быть сложными для диагностики.
Прибавьте все это к тому времени, которое требуется для добавления в имена столбцов (черт возьми, у вас может даже быть интерфейс, который позволяет перетаскивать имена столбцов (я знаю, что в SQL Server я уверен, что могу поспорить, что есть какой-то способ Это инструмент, который вы используете для написания запросов 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 & ключевые слова = SQL + antipatterns
Автор: HLGEM Размещён: 16.01.2014 06:25Вопросы из категории :
- mysql Двоичные данные в MySQL
- mysql Насколько большой может быть база данных MySQL до того, как производительность начнет снижаться
- mysql Выбрать все столбцы, кроме одного в MySQL?
- mysql MySQL или PDO - каковы плюсы и минусы?
- mysql Как выбрать n-ую строку в таблице базы данных SQL?
- mysql MyISAM против InnoDB
- select Как мне найти записи, которые не объединены?
- select Что "select ((select (s), $ | = 1) [0])" делает в Perl?
- select Лучший способ сделать вложенную логику оператора в SQL Server
- select Execute a Stored Procedure in a SELECT statement
- select Как выбрать элемент в jQuery, используя переменную для идентификатора?
- wildcard Почему плохо использовать подстановочный знак с оператором импорта Java?
- wildcard Поиск по шаблону для LINQ
- wildcard Получить отфильтрованный список файлов в каталоге
- wildcard Распакуйте все файлы в каталоге
- wildcard Исключить строку из поиска по шаблону в оболочке