mysql - tabla - select todas las columnas menos una
¿Seleccionar todas las columnas excepto una en MySQL? (28)
¿Funcionaría mejor una vista en este caso?
CREATE VIEW vwTable
as
SELECT
col1
, col2
, col3
, col..
, col53
FROM table
Estoy tratando de usar una declaración 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 MI DISEÑO)
Al principio pensé que podrías usar expresiones regulares, pero como he estado leyendo los documentos de MYSQL parece que no puedes. Si fuera usted, usaría otro idioma (como PHP) para generar una lista de columnas que desea obtener, almacenarla como una cadena y luego usarla para generar el SQL.
Aunque estoy de acuerdo con la respuesta de Thomas (+1;)), me gustaría agregar la advertencia de que asumiré que la columna que no desea no contiene casi ningún dato. Si contiene enormes cantidades de texto, xml o blobs binarios, tómese el tiempo para seleccionar cada columna individualmente. Su rendimiento sufrirá lo contrario. ¡Aclamaciones!
Basado en la respuesta de @Mahomedalid, he hecho 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 tienes muchos cols, usa este sql para cambiar group_concat_max_len
SET @@group_concat_max_len = 2048;
De acuerdo en la respuesta de @Mahomedalid. Pero no quería hacer algo como una declaración preparada y no quería escribir todos los campos. Así que lo que tuve fue una solución tonta. Vaya a la tabla en phpmyadmin-> sql-> select, se vuelca la consulta, reemplace y listo! :)
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;
En realidad, hay una manera, necesita tener permisos, por supuesto, para hacer esto ...
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;
Reemplazando <table>, <database> and <columns_to_omit>
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 declaración (excluyendo la que no desea).
SELECT
col1
, col2
, col3
, col..
, col53
FROM table
Estoy de acuerdo con la solución "simple" de enumerar todas las columnas, pero esto puede ser una carga y los errores tipográficos pueden causar una gran cantidad de tiempo perdido. Utilizo una función "getTableColumns" para recuperar los nombres de mis columnas adecuados para pegar en una consulta. Entonces todo lo que necesito hacer es eliminar aquellos 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 en que no es suficiente con Select * , si ese que no necesita, como se mencionó en otra parte, es un BLOB, no quiere que se introduzca esa sobrecarga.
Me gustaría crear una vista con los datos requeridos, luego puede Select * con comodidad, si el software de la base de datos los admite. De lo contrario, poner los datos enormes en otra tabla.
La respuesta publicada por Mahomedalid tiene un pequeño problema:
El código de la función de reemplazo interno reemplazó " <columns_to_delete>, " por "", este reemplazo tiene un problema si el campo a reemplazar es el último en la cadena de concat debido a que el último no tiene la coma coma "," y no se elimina de la cadena.
Mi propuesta:
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>'');
Reemplazando <table> , <database> y `
La columna eliminada se reemplaza por la cadena "FIELD_REMOVED" en mi caso, esto funciona porque estaba intentando una memoria segura. (El campo que estaba eliminando es un BLOB de alrededor de 1 MB)
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 cualquiera que enfrente el problema.
podemos reemplazar la función REEMPLAZAR con la cláusula where en la subconsulta de la declaración 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 id
Espero que esto ayude a cualquiera que busque una solución.
Saludos
Me gustaría agregar otro punto de vista para resolver este problema, especialmente si tiene un pequeño número de columnas para eliminar.
Podría usar una herramienta de base de datos como MySQL Workbench para generar la declaración de selección para usted, así que solo tiene que eliminar manualmente esas columnas para la declaración generada y copiarla en su script SQL.
En MySQL Workbench la forma de generarlo es:
Haga clic derecho en la tabla -> enviar a Sql Editor -> Seleccionar todas las declaraciones.
Mi problema principal son las muchas columnas que obtengo al unir tablas. Si bien esta no es la respuesta a su pregunta (cómo seleccionar todas las columnas excepto una de ciertas tablas), creo que vale la pena mencionar que puede especificar la table. para obtener todas las columnas de una tabla en particular, en lugar de simplemente especificar.
Aquí hay un ejemplo de cómo esto podría ser muy útil:
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'') )
El resultado son todas las columnas de la tabla de usuarios y dos columnas adicionales que se unieron desde la tabla meta.
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 check-in, la consulta fallará. La solución simple es utilizar la marca de retroceso alrededor de los nombres de columna. La consulta modificada está abajo.
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;
Podría usar DESCRIBE my_table y usar los resultados para generar la instrucción SELECT dinámicamente.
Puede ser que tenga una solución a la discrepancia señalada por 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''
Puede usar SQL para generar SQL si lo desea y evaluar el SQL que produce. Esta es una solución general, ya que extrae los nombres de columna 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
Realmente 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 usar la consulta que ha construido.
Así que algo 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 construya.
Que yo sepa, no lo hay. Puedes hacer algo como:
SELECT col1, col2, col3, col4 FROM tbl
y elija manualmente las columnas que desee. Sin embargo, si desea muchas columnas, puede que desee hacer un:
SELECT * FROM tbl
Y solo ignora lo que no quieres.
En su caso particular, sugeriría:
SELECT * FROM tbl
A menos que solo quieras unas pocas columnas. Si solo quieres cuatro columnas, entonces:
SELECT col3, col6, col45, col 52 FROM tbl
estaría bien, pero si desea 50 columnas, entonces cualquier código que haga que la consulta sea (¿también?) difícil de leer.
Quería esto también, 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 usted ingresa a la tabla, luego a una columna que no quiere o como en una matriz: array ("id", "name", "whatevercolumn")
Así que en selecto podrías usarlo así:
mysql_query("SELECT ".$db->getColsExcept(''table'',array(''id'',''bigtextcolumn''))." FROM table");
o
mysql_query("SELECT ".$db->getColsExcept(''table'',''bigtextcolumn'')." FROM table");
Sí, aunque puede ser una E / S alta dependiendo de la tabla aquí hay una solución que encontré para ella.
Select *
into #temp
from table
alter table #temp drop column column_name
Select *
from #temp
Select * es un antipattern de SQL. No debe utilizarse en el código de producción por muchas razones, entre ellas:
Se tarda un poco más en procesarse. Cuando las cosas se ejecutan millones de veces, esos pequeños bits pueden importar. Una base de datos lenta donde la lentitud es causada por este tipo de codificación descuidada es el tipo más difícil de ajustar el rendimiento.
Esto significa que probablemente esté enviando más datos de los que necesita, lo que causa cuellos de botella en el servidor y en la red. Si tiene una combinació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 que se vean 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 puede ser cierto en mysql).
Si alguien es lo suficientemente tonto como para reconstruir las tablas con las columnas en un orden diferente (lo que no debería hacer, pero sucede todo el tiempo), todo tipo de código puede romperse. Especifique el código para una inserción, por ejemplo, donde de repente está poniendo la ciudad en el campo dirección3 porque, sin especificar, la base de datos solo puede ir en el orden de las columnas. Esto es lo suficientemente malo cuando los tipos de datos cambian, pero peor aún cuando las columnas intercambiadas tienen el mismo tipo de datos, ya que puede ir insertando en algún momento datos erróneos que son un desastre para limpiar. Necesitas preocuparte 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 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 (puede que incluso tenga una interfaz que le permita arrastrar los nombres de las columnas) (Sé que lo hago en SQL Server, apuesto a que hay alguna forma de hacerlo). esta es una herramienta que usa para escribir consultas de mysql. Veamos: "Puedo causar problemas de mantenimiento, puedo causar problemas de rendimiento y problemas de integridad de datos, pero he ahorrado cinco minutos de tiempo de desarrollo". En las columnas específicas que quieras.
También le 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
Si desea excluir el valor de un campo, por ejemplo, por inquietudes de seguridad / información confidencial, puede recuperar esa columna como nula.
p.ej
SELECT *, NULL AS salary FROM users
Si la columna que no quiso seleccionar tenía una gran cantidad de datos y no quiso incluirla debido a problemas de velocidad y selecciona las otras columnas a menudo, le sugiero que cree una nueva tabla. con el campo que normalmente no selecciona con una clave para la tabla original y elimine el campo de la tabla original. Únase a las tablas cuando ese campo adicional sea realmente requerido.
Si siempre es la misma columna, puede crear una vista que no la tenga.
De lo contrario, no, no lo creo.
Solo haz
SELECT * FROM table WHERE whatever
Luego suelta la columna en tu lenguaje de programación favorito: php
while (($data = mysql_fetch_array($result, MYSQL_ASSOC)) !== FALSE) {
unset($data["id"]);
foreach ($data as $k => $v) {
echo"$v,";
}
}
Soy bastante tarde para dar una respuesta a esta pregunta, 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 la vea. Y encontrarlo ú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]);
Tu puedes hacer:
SELECT column1, column2, column4 FROM table WHERE whatever
sin obtener column3, aunque tal vez estabas buscando una solución más general?