In MySQL, when we are going to make a SELECT , we can usually specify each field we want to return.
SELECT id, nome FROM usuarios
However, let's imagine a scenario where I have 50 columns in a table and I did not want the senha and email fields to be displayed.
It would be easier to logically specify that I do not want these 2 fields , than to select the remaining 48 fields I want to be displayed
Is there any way to do this in MySQL?
The solutions presented work, albeit strange, I will only give some alternatives to the problem. Not necessarily solutions as a request in the question (if there is a real solution to this):
* . If you are going to get multiple columns, deleting one or the other probably will not bring much advantage, in some cases it may even be disadvantageous. Another solution would simply be to omit the value of these two columns:
SELECT *, NULL as senha, NULL as email FROM usuarios;
The NULL there can be any value you want to display by default in these fields.
You can use the following select:
SET @sql = CONCAT('SELECT ', (SELECT REPLACE(GROUP_CONCAT(COLUMN_NAME), '<columns_to_omit>,', '') FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '<table>' AND TABLE_SCHEMA = '<database>'), ' FROM <table>');
PREPARE stmt1 FROM @sql;
EXECUTE stmt1;
Replace the <table> , <database> , and <columns_to_omit>
Retired from SOEN: link