MySQL "exists" function in "where" clause-CodePudding

I would like to select a field only if the field exists on the table. If it does not exist, then I want to select a different field in the "where" clause.

The use case for this is that I have several similar tables, some of which have a "user_id" field and others do not, but rather than hard-coding all the tables that have this, I want to just check for the existence of the field, so that I can use the same sql for all. Those that do not have user_id use the "id" field instead for my application.

This code gives me the error

Unknown column 'user_id' in 'where clause'

for the table that does not have a user_id field.

I am using MySQL 5.7

select id from users 
    where 
        if ( 
            exists( select 1 from information_schema.columns where table_name = 'users' and column_name = 'user_id' ), 
                user_id > 10, 
                id > 10);

CodePudding user response：

I don't think you can achieve what you're looking to do in the way you're looking to do it.

The interpreter has no expectation that you might only want to use one of these fields, and it correctly points out that one of them is not valid for that query.

I think you'd have a far easier time having a common field name for everything you're searching on.

Consider creating views that provide all the columns of the base table and map the varying column as user_id.

e.g.

CREATE VIEW uniform_user 
AS
SELECT 
   id,
   id as user_id,
   ...

You can then exploit these views rather than the base tables and know that a user_id will definitely exist in each.

SELECT user_id FROM uniform_user

etc.