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

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

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

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

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

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

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

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

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

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

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     gt  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 gt  call fieldExists   exists   jos vm product    child option   NULL     Query OK  0 rows affected  0 01 sec   mysql gt  select   exists     ----------      exists    ----------           0    ----------  1 row in set  0 00 sec   mysql gt  call fieldExists   exists   jos vm product    child options    etrophies      Query OK  0 rows affected  0 01 sec   mysql gt  select   exists     ----------      exists    ----------           1    ----------

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

Examples related to mysql