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

by

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

More Related Contents:

  1. how to delete data from table A besides the customer service and director data in table B?
  2. Get records with max value for each group of grouped SQL results
  3. How to store multiple options in a single table?
  4. How to populate a table with a range of dates?
  5. Get the new record primary key ID from MySQL insert query?
  6. brew install mysql on macOS
  7. TINYTEXT, TEXT, MEDIUMTEXT, and LONGTEXT maximum storage sizes
  8. MySQL: multiple tables or one table with many columns? [closed]
  9. How do I check to see if a value is an integer in MySQL?
  10. Add 2 hours to current time in MySQL?
  11. How can I Insert many rows into a MySQL table and return the new IDs?
  12. Store image to database blob; retrieve from db into Picturebox
  13. How to prepend a string to a column value in MySQL?
  14. MySQL trigger On Insert/Update events
  15. MySQL combine two columns into one column
  16. How to get first day of every corresponding month in mysql?
  17. How can I change MariaDB to MySQL in XAMPP?
  18. MySQL: LIMIT by a percentage of the amount of records?
  19. Dynamic table name at sql statement
  20. MySQL Inner Join Query Multiple Tables
  21. MySQL 1062 – Duplicate entry ‘0’ for key ‘PRIMARY’
  22. MySQL LEFT JOIN 3 tables
  23. Doctrine 2 mysql FIELD function in order by
  24. FIND_IN_SET with multiple value [duplicate]
  25. MySQL SELECT most frequent by group
  26. MySql using correct syntax for the over clause
  27. Accessing a mysql database from external host/ip? (ie: mysql workbench)
  28. MySQL table -> Can you return the same row multiple times, in the same query?
  29. Can’t connect to RDS instance from EC2 instance
  30. django cannot connect mysql in docker-compose

Leave a Comment