Ideas for select all columns but one mysql stored procedure
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!!
Solutions / Ideas to above problem are:
- Ignorance is bliss. Select all(*) and ignore the column.
- Manually type column names or manage it with copy paste!
- Create a view from original table and drop the unnecessary column!
- MySQL Database command / syntax that allows us to do this; which is not there unfortunately.
- Use my stored procedure to select all but one columns:
No need to explain why all such hacks are initiated with the help of information_schema!! 🙂
The idea:
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.
Here you go with the code: [Download selectAllButOne.sql at the end of the page.]
DELIMITER $$
DROP PROCEDURE IF EXISTS `selectAllButOne` $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `selectAllButOne`(in_db_nm varchar(20),in_tbl_nm varchar(20),in_colm_nm varchar(20))
BEGIN
SET @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,’;’);
PREPARE stmnt FROM @stmnt;
EXECUTE stmnt;
END $$
DELIMITER ;
As one of the ideas, #4 says about MySQL syntax come to our rescue.
I wish if MySQL can ease out us with syntax as follows:
SELECT * FROM TABLENAME EXCEPT COLUMN COLUMNNAME[,COLUMNNAME…]
But…
Download Select all but one mysql stored procedure.
Any other ideas?
