1

I am trying to determine if a column already exists in a MySQL table before altering the table to add it. I need an all in one query I can run since I am executing this through an old PHP mysql library that only executes a single statement in one call. This the query I was attempting to use. 

SELECT COUNT(*) as col_count
FROM information_schema.COLUMNS
WHERE
    TABLE_SCHEMA = DATABASE()
AND TABLE_NAME = 'mytable'
AND COLUMN_NAME = 'status';

Unfortunately the DATABASE() function returns information_schema instead of current connection DB at least in PHPMyAdmin. It may be different through the PHP mysql driver. Therefore, it returns zero for the column count.

SELECT DATABASE(), COUNT(*) as col_count
FROM information_schema.COLUMNS
WHERE
    TABLE_SCHEMA = DATABASE()
AND TABLE_NAME = 'mytable'
AND COLUMN_NAME = 'status';

This returns: "Information_schema", "0" despite the fact that the original connection was to a different database.

I have come up with a workaround in PHP but would still like to know if this is possible using only SQL.

Improve this question

3 Answers 3

Reset to default
1

You were on the right track. Add one thing before your code:

USE YourDb;  -- You need to be in _your_ db to make `DATABASE()` work right

SELECT COUNT(*) as col_count
    FROM information_schema.COLUMNS
    WHERE  TABLE_SCHEMA = DATABASE()
      AND  TABLE_NAME = 'mytable'
      AND  COLUMN_NAME = 'status';
Improve this answer
3
  • I have updated my question to clarify that I need an all in one query I can run since I am executing this through an old PHP mysql library that only executes a single statement in one call. I can't run two statements together. The PHP mysql connection is NOT to information_schema, it is to "YourDb" say.
    – lolcode
    Mar 8, 2016 at 6:09
  • If the default db is already YourDb, then only the SELECT is needed. I added the USE because you were obviously having some kind of trouble in that area when you got back information_schema, 0.
    – Rick James
    Mar 8, 2016 at 6:18
  • Not sure why it wasn't working through that old PHP library back then but the single query in my post works fine in MySQL Workbench now. Marking as the accepted answer since the "USE yourdb" part could be useful to other people looking for the same thing.
    – lolcode
    Sep 13, 2018 at 13:08
0

You may use DESCRIBE table_name to print the table definition

Improve this answer
2
  • 3
    Can you more fully explain how this solves the OP's problem? I'm no MySQL expert but it seems like this is going to result in a lot of string parsing in PHP. I believe the OP would rather have SQL that will detect the column and conditionally create the column if it doesn't exist.
    – Erik
    Mar 7, 2016 at 23:27
  • Actually, this isn't a terrible option. The result set from DESCRIBE table_name can simply be looped through in PHP to look for the column.
    – lolcode
    Mar 8, 2016 at 6:16
0

Or you can just ALTER TABLE and catch the error related to a existing column already there.

Improve this answer
2
  • 1
    This does not provide an answer to the question. To critique or request clarification from an author, leave a comment below their post. - From Review
    – Marco
    Sep 13, 2018 at 6:31
  • Apologies, the question half implied a check before doing an alter. Was a 2 year old post so was just leaving as an alternate strategy for migrations for those that search this question.
    – danblack
    Sep 13, 2018 at 6:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.