Assume we’ve a table with 100 rows and we need to select all columns but one.
The problem is headache of actually typing out all 99 required columns!! In this blog we will see how can we select all columns but one from the table.<\/p>\n\n\n\n
No need to explain why all such hacks are initiated with the help of information_schema!! \ud83d\ude42<\/p>\n\n\n\n
Prepare column list to be selected from COLUMNS table of information_schema database for specified database name and table name. Thus we can select all columns but one and prepare sql statement and execute.
It can be further extended for select all but SOME; instead of “column_name<>in_colm_nm” one can use “column_name not in …” to prepare the column list to be selected.<\/p>\n\n\n\n
Here you go with the code: [Download selectAllButOne.sql\u00a0at the end of the page.]<\/p>\n\n\n\n
DELIMITER $$\nDROP PROCEDURE IF EXISTS `selectAllButOne` $$\nCREATE DEFINER=`root`@`localhost` PROCEDURE `selectAllButOne`(in_db_nm varchar(20),in_tbl_nm varchar(20),in_colm_nm varchar(20))\nBEGIN<\/em>\nSET @stmnt= CONCAT('SELECT ',(SELECT GROUP_CONCAT(column_name) FROM information_schema.columns WHERE table_name=in_tbl_nm AND table_schema=in_db_nm AND column_name<>in_colm_nm), ' FROM ',in_db_nm,'.',in_tbl_nm,';');\nPREPARE stmnt FROM @stmnt;\nEXECUTE stmnt;\nEND $$\nDELIMITER ;<\/code><\/pre>\n\n\n\n<\/p>\n\n\n\n
As one of the ideas, #4 says about MySQL syntax come to our rescue.<\/p>\n\n\n\n
I wish if MySQL can ease out us with syntax as follows:<\/p>\n\n\n\n
SELECT * FROM TABLENAME EXCEPT COLUMN COLUMNNAME[,COLUMNNAME...]<\/code><\/pre>\n\n\n\nBut until then we have this quick work around<\/p>\n\n\n\n