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

Answer 3

you can't do that, sorry. Actually you shouln't have done it if you could - specifying these things explicitly is always better, assume other developer adds new field and your application will fail

Answer 4

You are too advanced.

The only data language that I have seen that supports your syntax is the D language with its "...ALL BUT ..." construct:

Wikipedia - D Language Specification

There are some reference implementations available, but mostly for teaching purposes.

Answer 5

SET @sql = CONCAT('SELECT ',
    (SELECT REPLACE(GROUP_CONCAT(COLUMN_NAME), '<columns_to_delete>,', '')
       FROM INFORMATION_SCHEMA.COLUMNS
       WHERE TABLE_NAME = '<table>'
         AND TABLE_SCHEMA = '<database>'),
     ' FROM <table>');

PREPARE stmt1 FROM @sql;
EXECUTE stmt1;

Answer 6

Unless there's some special extension in MySql you cannot do that. You either get all, or have to explicitly state what you want. It is best practice to always name columns, as this will not alter the query behaviour even if the underlying table changes.

Answer 7

There is no SQL syntax to support:

select * from table but not select id,active

If you want all but one or more columns, you have to explicitly define the list of columns you want.

Answer 8

You should not be using select * anyway. Enumerate the columns you want and only the columns you want, that is the best practice.

Answer 9

I'm fairly certain you can't. Probably the best way I can think of is to create SELECT name, age, surname, lastname, category FROM table as a view, then just SELECT * FROM view. I prefer to always select from a view anyway.

However, as others have pointed out, if another column gets added to the view your application could fail. On some systems as well (PostgreSQL is a candidate) you cannot alter the table without first dropping the view so it becomes a bit cumbersome.

Answer 10

If the reason is to avoid column duplication error without having to specify a long list of columns:

1. temporarily change the name of column that is a duplicate to enable the view to be created.

2. delete the duplicate column from the select and save view

3. rename the changed column name

If the reason is simply to omit a one or more columns:

1. create view and delete column/s from select