How can I "select *" from a table in MySQL but omit certain columns?

Question

I have a table with the following columns:

id,name,age,surname,lastname,catgory,active

Instead of: SELECT name,age,surname,lastname,catgory FROM table

How can I make something like this: SELECT * FROM table [but not select id,active]

Answer 1

While many say it is best practice to explicitly list every column you want returned, there are situations where you might want to save time and omit certain columns from the results (e.g. testing). Below I have given two options that solve this problem.

1. Create a Function that retrieves all of the desired column names: ( I created a schema called functions to hold this function)

DELIMITER $$

CREATE DEFINER=`root`@`%` FUNCTION `getTableColumns`(_schemaName varchar(100), _tableName varchar(100), _omitColumns varchar(200)) RETURNS varchar(5000) CHARSET latin1
BEGIN
    SELECT GROUP_CONCAT(COLUMN_NAME) FROM information_schema.columns 
    WHERE table_schema = _schemaName AND table_name = _tableName AND FIND_IN_SET(COLUMN_NAME,_omitColumns) = 0 ORDER BY ORDINAL_POSITION;
END

Create and execute select statement:

SET @sql = concat('SELECT ', (SELECT 
functions.getTableColumns('test', 'employees', 'age,dateOfHire')), ' FROM test.employees'); 
PREPARE stmt1 FROM @sql;
EXECUTE stmt1;

2. OR without writing a function you could:

SET @sql = CONCAT('SELECT ', (SELECT GROUP_CONCAT(COLUMN_NAME) FROM 
information_schema.columns WHERE table_schema = 'test' AND table_name = 
'employees' AND column_name NOT IN ('age', 'dateOfHire')), 
' from test.eployees');  
PREPARE stmt1 FROM @sql;
EXECUTE stmt1;

*Replace test with your own schema name

**Replace employees with your own table name

***Replace age,dateOfHire with the columns you want to omit (you can leave it blank to return all columns or just enter one column name to omit)

** **You can adjust the lengths of the varchars in the function to meet your needs

Answer 2

The only way to do that that I know if is to enumerate each column you do want... no negative filters that I'm aware of.

select name, age, surname, lastname, category from table