MySQL IF NOT EXISTS / IFNULL SELECT other column

Question

I want to check, if a column (mysql) exists. If not, mysql should take the value from a column that definitely exists. My Query looks like this, but it does not work:

IF NOT EXISTS (SELECT `en` FROM `HP_strings` 
               WHERE `name`='copyright' AND `group`='system')
THEN
    SELECT `default` FROM `HP_strings` 
    WHERE `name`='copyright' AND `group`='system';

I also tried

IFNULL((SELECT `en` FROM `HP_strings` 
        WHERE `name`='copyright' AND `group`='system'),
(SELECT `default` FROM `HP_strings` 
 WHERE `name`='copyright' AND `group`='system'));

Same result (error)

EDIT: 'en' does NOT EXIST. I want to know, if it exists.

Clarification needed: do you really want to check if the column exists, or if the column value is `NULL`? — bfavaretto, Jun 04 '12 at 19:00
@user1435792, you mean there is no column named `en` and you really want to check whether any column `en` exist in your table? — Rahul, Jun 04 '12 at 19:04
@user1435792, I have edited my post; see if that helps at all. — Rahul, Jun 04 '12 at 19:08

bfavaretto · Accepted Answer · 2012-06-06T05:46:16.797

1

To inspect a table structure in MySQL, you have to query the information_schema database. To do that, your db user must have access to that system database (which a regular user shouldn't have).

I don't know what you are trying to do, but you should probably rethink you database structure so it doesn't require checking if a column exists in a table.

edited Jun 06 '12 at 05:46

answered Jun 04 '12 at 19:06

bfavaretto

71,580
16
111
150

Rahul · Answer 2 · 2012-06-04T19:07:16.240

0

Try this:

SELECT `default` FROM `HP_strings` where isnull(`en`) and `name`='copyright' 
AND `group`='system'

EDIT: (Based on your comment)

You can check whether a column really exist or not in your table from DB schema like below ... but I don't think you can manipulate in your query like you are intending to.

SELECT *  FROM information_schema.COLUMNS  
WHERE TABLE_SCHEMA = 'db_name'  
AND TABLE_NAME = 'table_name'  
AND COLUMN_NAME = 'column_name'

edited Jun 04 '12 at 19:07

answered Jun 04 '12 at 18:55

Rahul

76,197
13
71
125

#1054 - Unknown column 'en' in 'where clause' – user1435792 Jun 04 '12 at 19:00
@user1435792, solution have been given based on your post data. As per your post `en` must be a column. So, what `en` actually? – Rahul Jun 04 '12 at 19:01
it is a column. But it does not exist. I want to check if this column really exists. Because someone could make a column 'en' and then the value of this column should be used. – user1435792 Jun 04 '12 at 19:03
@user1435792, you should clear that in post. See my edit ... if it helps you. – Rahul Jun 04 '12 at 19:05

MySQL IF NOT EXISTS / IFNULL SELECT other column

2 Answers2