MySQL Check if a column exists in a table with SQL

Question

I am trying to write a query that will check if a specific table in MySQL has a specific column  and if not      create it  Otherwise do nothing  This is really an easy procedure in any enterprise-class database  yet MySQL seems to be an exception   I thought something like  IF EXISTS  SELECT   FROM INFORMATION SCHEMA COLUMNS             WHERE TABLE NAME  prefix topic  AND column name  topic last update    BEGIN  ALTER TABLE  prefix topic  ADD  topic last update  DATETIME NOT NULL  UPDATE  prefix topic  SET  topic last update     topic date add   END    would work  but it fails badly  Is there a way

User · Answer

I threw this stored procedure together with a start from @lain's comments above, kind of nice if you need to call it more than a few times (and not needing php):

delimiter //
-- ------------------------------------------------------------
-- Use the inforamtion_schema to tell if a field exists.
-- Optional param dbName, defaults to current database
-- ------------------------------------------------------------
CREATE PROCEDURE fieldExists (
OUT _exists BOOLEAN,      -- return value
IN tableName CHAR(255),   -- name of table to look for
IN columnName CHAR(255),  -- name of column to look for
IN dbName CHAR(255)       -- optional specific db
) BEGIN
-- try to lookup db if none provided
SET @_dbName := IF(dbName IS NULL, database(), dbName);

IF CHAR_LENGTH(@_dbName) = 0
THEN -- no specific or current db to check against
  SELECT FALSE INTO _exists;
ELSE -- we have a db to work with
  SELECT IF(count(*) > 0, TRUE, FALSE) INTO _exists
  FROM information_schema.COLUMNS c
  WHERE 
  c.TABLE_SCHEMA    = @_dbName
  AND c.TABLE_NAME  = tableName
  AND c.COLUMN_NAME = columnName;
END IF;
END //
delimiter ;

Working with fieldExists

mysql> call fieldExists(@_exists, 'jos_vm_product', 'child_option', NULL) //
Query OK, 0 rows affected (0.01 sec)

mysql> select @_exists //
+----------+
| @_exists |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

mysql> call fieldExists(@_exists, 'jos_vm_product', 'child_options', 'etrophies') //
Query OK, 0 rows affected (0.01 sec)

mysql> select @_exists //
+----------+
| @_exists |
+----------+
|        1 |
+----------+

User · Answer

Select just column name from information schema and put the result of this query into variable  Then test the variable to decide if table needs alteration or not   P S  Don t foget to specify TABLE SCHEMA for COLUMNS table as well

User · Answer

Following is another way of doing it using plain PHP without the information schema database    chkcol   mysql query  SELECT   FROM  my table name  LIMIT 1     mycol   mysql fetch array  chkcol   if  isset  mycol  my new column       mysql query  ALTER TABLE  my table name  ADD  my new column  BOOL NOT NULL DEFAULT  0

User · Answer

Just to help anyone who is looking for a concrete example of what  Mchl was describing  try something like  SELECT   FROM information schema COLUMNS   WHERE TABLE SCHEMA    my schema  AND TABLE NAME    my table    AND COLUMN NAME    my column    If it returns false  zero results  then you know the column doesn t exist

User · Answer

DO NOT put ALTER TABLE MODIFY COLS or any other such table mod operations inside a TRANSACTION   Transactions are for being able to roll back a QUERY failure not for ALTERations   it will error out every time in a transaction   Just run a SELECT   query on the table and check if the column is there

User · Answer

This works well for me   SHOW COLUMNS FROM  table  LIKE  fieldname     With PHP it would be something like      result   mysql query  SHOW COLUMNS FROM  table  LIKE  fieldname      exists    mysql num rows  result   TRUE FALSE

User · Answer

Many thanks to Mfoo who has put the really nice script for adding columns dynamically if not exists in the table  I have improved his answer with PHP  The script additionally helps you find how many tables actually needed  Add column  mysql comand  Just taste the recipe  Works like charm     lt  php ini set  max execution time   0     host    localhost    username    root    password        database    books     con   mysqli connect  host   username   password   if   con    echo  Cannot connect to the database   die     mysqli select db  con   database    result mysqli query  con   show tables     tableArray   array    while  tables   mysqli fetch row  result            tableArray      tables 0           already   0   new   0  for  rs   0   rs  lt  count  tableArray    rs           exists   FALSE        result   mysqli query  con   SHOW COLUMNS FROM    tableArray  rs    LIKE  tags          exists    mysqli num rows  result   TRUE FALSE       if  exists    FALSE                mysqli query  con   ALTER TABLE    tableArray  rs    ADD COLUMN tags VARCHAR 500  CHARACTER SET utf8 COLLATE utf8 unicode ci NULL               new          echo      new   Table DONE  lt br  gt              else                  already          echo      already   Field defined alrady  lt br  gt                  echo   lt br  gt        gt

User · Answer

julio  Thanks for the SQL example  I tried the query and I think it needs a small alteration to get it working properly   SELECT    FROM information schema COLUMNS  WHERE      TABLE SCHEMA    db name   AND TABLE NAME    table name   AND COLUMN NAME    column name    That worked for me   Thanks

User · Answer

I am using this simple script   mysql query  select  column from  table   or mysql query  alter table  table add  column varchar  20       It works if you are already connected to the database

User · Answer

This work for me with sample PDO    public function GetTableColumn            query     this- gt db- gt prepare  SHOW COLUMNS FROM  what table  LIKE  what column       try                   query- gt execute                                                  if  query- gt fetchColumn      return 1   else  return 0         catch PDOException  e  die  e- gt getMessage

[mysql] MySQL, Check if a column exists in a table with SQL

Examples related to mysql