Specify fields I don’t want in Mysql

Asked

Viewed 1,446 times

4

In Mysql, when we will make a SELECT, generally we can specify each field we want to return.

SELECT id, nome FROM usuarios

But let’s imagine a scenario where I have 50 columns in a table and I didn’t want the fields to be displayed senha and email.

It would be easier, logically, I specify that I don’t want these 2 fields, than selecting the others 48 fields remaining that I want to be shown

Is there any way to do this in Mysql?

mysql sql database select-sql

3 answers

7


The solutions presented work, although strange, I will only give some alternatives to the problem. Not necessarily solutions as requested in the question (if there is a real solution to this):

  • Use the *. If it will take several columns, eliminating one or the other will probably not bring much advantage, in some cases it may even be disadvantageous.
  • If you’re going to use this kind of selection a lot, create a vision with the desired columns.
  • Create script code generator (scaffolding) which reads the database and manages at least part of the darlings for you. It is easier to delete some fields or the script may even know what to take.

The latter two are considered DRY and only different approaches to obtain the same result.

  • +1 by DRY(dont repeat yourself) of the second item applied in the creation of the view. For we would have a definition in one place only that would be used in several other places :)

  • On second thought, why is there not yet a simple solution (such as NOT SELECT <column>, <column> FROM <table>) in SQL ANSI?

  • It could even exist but is probably little used, rarely makes sense and can bring some obscure problems. I think it’s hardly the intention. Until you say you want everything is common, the * was made for this, it clearly says that you want up the columns that you do not know if it exists. To say that you want everything, even what may have been added after less such columns is quite rare to be necessary. Filtering one or the other column can cost more than getting them all, so it better be more explicit than you want.

5

Another solution would simply omit the value of these two columns:

SELECT *, NULL as senha, NULL as email FROM usuarios;

The NULL there can be replaced any value you want to show by default in these fields.

  • Also taken from the SOEN? I just saw this on the link of friend @Luishenrique. Very good!

  • True! But I didn’t take it from there. I always do this to omit the hash of passwords :)

3

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 fields <table>, <database> and <columns_to_omit>

Withdrawn from the SOEN: https://stackoverflow.com/questions/9122/select-all-columns-except-one-in-mysql

  • Our God! seems like a bit of a gambit, but it deserved a + 1 :)

  • It really is a trick, because you select the name of all columns internally and replace them for nothing, mounting the main query that in your example will have 48 columns.

Browser other questions tagged mysql sql database select-sql

You are not signed in. Login or sign up in order to post.