Check if a field exists and it's not null

Question

I need to fetch a value from a table with three possibilities: 1) The field exists and it's not null, so it returns the value of the field 2) the field exists but it's null, so it returns is-null-string 3) The field doesn't exists, so it returns not-existing-string

I am trying to run this query but I get this error message #1054 - Unknown column 'm.my_field' in 'field list'

SELECT if (exists(SELECT *
                  FROM information_schema.COLUMNS
                  WHERE TABLE_NAME = 'my_table' AND COLUMN_NAME = 'my_field'
                 ), IFNULL(m.my_field, 'is-null-string'), 'not-existing-string'
       ) AS my_field,
       m.*
FROM my_table m

Any idea how can I get it done in mysql 5.6?

Something is highly suspicious about your application/data model if you don't know if a column is in a table. — Gordon Linoff, Jun 24 '19 at 12:49
@GordonLinoff Yes, I agree with you. But I was wondering if there is a solution to get this result without having to change the tables. — Mohammad Mehdi Habibi, Jun 24 '19 at 13:36
@MohammadMehdiHabibi . . . In general, you need to use dynamic SQL. — Gordon Linoff, Jun 24 '19 at 14:24

score 0 · Answer 1 · answered Dec 22 '19 at 00:01

What you are looking for is DESCRIBE:

DESCRIBE my_table;

Look at the result to see if your field exists. Note, you don't want this code to be in your application. You need to know your table fields to run queries. You might only use this to generate some boilerplate code.

Check if a field exists and it's not null

1 Answers1