Seleccione todas las columnas excepto una en MySQL?

Estoy tratando de usar una statement de selección para obtener todas las columnas de una determinada tabla de MySQL excepto una. ¿Hay una manera simple de hacer esto?

EDITAR: Hay 53 columnas en esta tabla (NO ES MI DISEÑO)

En realidad, hay una manera, debes tener permisos, por supuesto, para hacer esto …

SET @sql = CONCAT('SELECT ', (SELECT REPLACE(GROUP_CONCAT(COLUMN_NAME), ',', '') FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '' AND TABLE_SCHEMA = ''), ' FROM 
'); PREPARE stmt1 FROM @sql; EXECUTE stmt1;

Reemplazando

, and

¿Una vista funcionaría mejor en este caso?

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

En las definiciones de mysql (manual) no existe tal cosa. Pero si tiene una gran cantidad de columnas col1 , …, col100 , lo siguiente puede ser útil:

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

Tu puedes hacer:

 SELECT column1, column2, column4 FROM table WHERE whatever 

sin obtener la columna 3, aunque tal vez estabas buscando una solución más general?

Si está buscando excluir el valor de un campo, por ejemplo, por cuestiones de seguridad / información confidencial, puede recuperar esa columna como nula.

p.ej

 SELECT *, NULL AS salary FROM users 

Que yo sepa, no lo es. Puedes hacer algo como:

 SELECT col1, col2, col3, col4 FROM tbl 

y elija manualmente las columnas que desee. Sin embargo, si quiere una gran cantidad de columnas, entonces quizás quiera hacer una:

 SELECT * FROM tbl 

y simplemente ignora lo que no quieres.

En su caso particular, sugeriría:

 SELECT * FROM tbl 

a menos que solo desee unas pocas columnas. Si solo quieres cuatro columnas, entonces:

 SELECT col3, col6, col45, col 52 FROM tbl 

estaría bien, pero si quiere 50 columnas, entonces cualquier código que haga la consulta se volvería (¿demasiado?) difícil de leer.

Mientras probaba las soluciones de @Mahomedalid y @Junaid, encontré un problema. Así que pensé en compartirlo. Si el nombre de la columna tiene espacios o guiones como el check-in, la consulta fallará. La solución simple es usar el control invertido alrededor de los nombres de las columnas. La consulta modificada está debajo

 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; 

Si la columna que no deseaba seleccionar tenía una gran cantidad de datos y no deseaba incluirla debido a problemas de velocidad y seleccionaba las otras columnas a menudo, le sugiero que cree una nueva tabla con el único campo que generalmente no selecciona con una clave de la tabla original y elimina el campo de la tabla original. Únete a las tablas cuando ese campo extra realmente se requiera.

Puede usar DESCRIBIR my_table y usar los resultados de eso para generar la instrucción SELECT de forma dinámica.

Mi problema principal es la cantidad de columnas que obtengo al unir mesas. Si bien esta no es la respuesta a su pregunta (cómo seleccionar todas las columnas salvo algunas de una tabla), creo que vale la pena mencionar que puede especificar la table. para obtener todas las columnas de una tabla en particular, en lugar de solo especificarlas.

Aquí hay un ejemplo de cómo esto podría ser muy útil:

 seleccione usuarios. *, phone.meta_value como teléfono, zipcode.meta_value como código postal

 de los usuarios

 left join usuario_meta como teléfono
 on ((users.user_id = phone.user_id) AND (phone.meta_key = 'teléfono'))

 left join usuario_meta como código postal
 on ((users.user_id = zipcode.user_id) AND (zipcode.meta_key = 'zipcode'))

El resultado es todas las columnas de la tabla de usuarios y dos columnas adicionales que se unieron desde la tabla meta.

Me gustó la respuesta de @Mahomedalid además de este hecho informado en el comentario de @Bill Karwin . El posible problema planteado por @Jan Koritak es cierto, me enfrenté a eso, pero he encontrado un truco para eso y solo quiero compartirlo aquí para cualquier persona que se enfrente al problema.

podemos reemplazar la función REEMPLAZAR por la cláusula where en la subconsulta de la statement preparada como esta:

Usando mi tabla y nombre de columna

 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; 

Por lo tanto, esto excluirá solo el id campo pero no el company_id

Espero que esto ayude a cualquiera que busque una solución.

Saludos

Es una buena práctica especificar las columnas que está consultando incluso si consulta todas las columnas.

Por lo tanto, le sugiero que escriba el nombre de cada columna en la statement (excluyendo la que no desea).

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

Estoy de acuerdo con la solución “simple” de listar todas las columnas, pero esto puede ser engorroso y los errores tipográficos pueden causar mucho tiempo perdido. Utilizo una función “getTableColumns” para recuperar los nombres de mis columnas adecuados para pegar en una consulta. Entonces, todo lo que tengo que hacer es eliminar los que no quiero.

 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; 

Su resultado devuelve una cadena delimitada por comas, por ejemplo …

col1, col2, col3, col4, … col53

Estoy de acuerdo con que no es suficiente con Select * , si ese no es necesario, como se mencionó en otro lugar, es un BLOB, no querrás tener ese exceso de control.

Crearía una vista con los datos requeridos, luego puede Select * con comodidad, si el software de la base de datos los admite. De lo contrario, coloque los datos enormes en otra tabla.

Al principio pensé que podrías usar expresiones regulares, pero como he estado leyendo los documentos de MYSQL parece que no puedes. Si yo fuera usted, usaría otro idioma (como PHP) para generar una lista de columnas que desea obtener, almacenarla como una cadena y luego usar eso para generar el SQL.

Solo haz

 SELECT * FROM table WHERE whatever 

A continuación, suelte la columna en su lenguaje de progtwigción favorito: php

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

Yo también quería esto, así que creé una función en su lugar.

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

Entonces, ¿cómo funciona? Es que ingresas a la tabla, luego una columna que no quieres o como en una matriz: array ("id", "name", "whatevercolumn")

Entonces en select puedes usarlo así:

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

o

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

Si bien estoy de acuerdo con la respuesta de Thomas (+1;)), me gustaría añadir la advertencia de que asumiré que la columna que no desea contiene apenas datos. Si contiene cantidades enormes de texto, xml o blobs binarios, tómese el tiempo para seleccionar cada columna individualmente. Su rendimiento sufrirá lo contrario. ¡Aclamaciones!

Sí, aunque puede ser alta E / S dependiendo de la tabla aquí hay una solución que encontré para ello.

 Select * into #temp from table alter table #temp drop column column_name Select * from #temp 

La respuesta publicada por Mahomedalid tiene un pequeño problema:

El código de función reemplazar interior reemplazó a ” , ” por “”, este reemplazo tiene un problema si el campo para reemplazar es el último en la cadena concat porque el último no tiene la coma “,” y no se elimina de la cadena.

Mi propuesta:

 SET @sql = CONCAT('SELECT ', (SELECT REPLACE(GROUP_CONCAT(COLUMN_NAME), '', '\'FIELD_REMOVED\'') FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '' AND TABLE_SCHEMA = ''), ' FROM 
');

Reemplazando

, y `

La columna eliminada se reemplaza por la cadena “FIELD_REMOVED” en mi caso, esto funciona porque estaba intentando proteger la memoria. (El campo que estaba eliminando es un BLOB de alrededor de 1 MB)

Basado en la respuesta de @Mahomedalid, realicé algunas mejoras para admitir “seleccionar todas las columnas excepto algunas en 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; 

Si tiene muchos cols, use este sql para cambiar group_concat_max_len

 SET @@group_concat_max_len = 2048; 

Puede ser que tenga una solución a la discrepancia señalada de Jan Koritak

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

Mesa :

 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 

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

Resultado de la consulta:

 'SELECT name_eid,sal FROM employee' 

Acepta la respuesta de @Mahomedalid. Pero no quería hacer algo como una statement preparada y no quería escribir todos los campos. Entonces, lo que tenía era una solución tonta. Dirígete a la tabla en phpmyadmin-> sql-> select, ¡vuelca la copia de consulta replace y listo! 🙂

Si siempre es la misma columna, puede crear una vista que no la tenga.

De lo contrario, no, no lo creo.

Puede usar SQL para generar SQL si desea y evalúa el SQL que produce. Esta es una solución general, ya que extrae los nombres de las columnas del esquema de información. Aquí hay un ejemplo de la línea de comandos de Unix.

Sustituyendo

  • MYSQL con tu comando mysql
  • TABLA con el nombre de la tabla
  • EXCLUDEDFIELD con nombre de campo excluido
 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 

En realidad, solo necesitará extraer los nombres de las columnas de esta manera solo una vez para construir la lista de columnas excluidas de esa columna, y luego simplemente use la consulta que ha creado.

Entonces algo así como:

 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) 

Ahora puede reutilizar la cadena $column_list en las consultas que construye.

Me gustaría agregar otro punto de vista para resolver este problema, especialmente si tiene un pequeño número de columnas para eliminar.

Puede usar una herramienta de BD como MySQL Workbench para generar la statement de selección para usted, por lo que solo tiene que eliminar manualmente esas columnas para la statement generada y copiarla en su secuencia de comandos SQL.

En MySQL Workbench, la forma de generarlo es:

Haga clic derecho en la tabla -> enviar a Sql Editor -> Seleccionar todos los estados.

Es bastante tarde para dar una respuesta, pongo esta es la forma en que siempre lo he hecho y francamente, es 100 veces mejor y más ordenada que la mejor respuesta, solo espero que alguien lo vea. Y lo encuentra útil

  //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 * es un antipattern SQL. No debe usarse en el código de producción por muchas razones, entre ellas:

Tarda un poco más en procesarse. Cuando las cosas se ejecutan millones de veces, esos pequeños bits pueden ser importantes. Una base de datos lenta donde la lentitud es causada por este tipo de encoding descuidada en todas partes es la más difícil de interpretar.

Significa que probablemente esté enviando más datos de los que necesita, lo que causa cuellos de botella tanto en el servidor como en la red. Si tiene una unión interna, las posibilidades de enviar más datos de los que necesita son del 100%.

Causa problemas de mantenimiento, especialmente cuando ha agregado nuevas columnas que no desea ver en todas partes. Además, si tiene una nueva columna, es posible que deba hacer algo en la interfaz para determinar qué hacer con esa columna.

Puede romper vistas (sé que esto es cierto en el servidor SQl, puede o no ser cierto en mysql).

Si alguien es tan tonto como para reconstruir las tablas con las columnas en un orden diferente (lo cual no debe hacer, pero sucede todo el tiempo), todo tipo de código puede romperse. Especialmente código para una inserción, por ejemplo, donde repentinamente está poniendo la ciudad en el campo dirección_3 porque sin especificar, la base de datos solo puede seguir el orden de las columnas. Esto es suficientemente malo cuando los tipos de datos cambian, pero empeora cuando las columnas intercambiadas tienen el mismo tipo de datos porque puede ir por algún tiempo insertando datos incorrectos que es un desastre para limpiar. Debe preocuparse por la integridad de los datos.

Si se usa en una inserción, romperá la inserción si se agrega una nueva columna en una tabla pero no en la otra.

Podría romper los factores desencadenantes. Los problemas de activación pueden ser difíciles de diagnosticar.

Sume todo esto contra el tiempo que lleva agregar los nombres de las columnas (diablos, incluso puede tener una interfaz que le permita arrastrar los nombres de las columnas (sé que lo hago en SQL Server, apuesto a que hay alguna manera de haz esto es alguna herramienta que utilizas para escribir consultas mysql.) Veamos, “Puedo causar problemas de mantenimiento, puedo causar problemas de rendimiento y puedo causar problemas de integridad de datos, pero he ahorrado cinco minutos de tiempo de desarrollo”. Realmente solo puse en las columnas específicas que quieras.

También sugiero que lea este libro: 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