Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

The MySQL add if not exists for a new field.

Posted on 2014-01-29
6
Medium Priority
?
4,573 Views
Last Modified: 2014-02-13
Right now we try to add a new field to a MYSQL table but we want to check if the same field in the target table exists before we add it to avoid error message, I check from some web site (http://stackoverflow.com/questions/5069611/add-column-if-none-exists-in-mysql) it suggest:


alter table <table name> ADD IF NOT EXISTS NEW_CITY_ID bigint(19)       ;

it use if not exists but it seems MysQL see syntax error.

What should it be?
or MysQL can't do it.
0
Comment
Question by:marrowyung
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 6

Assisted Solution

by:Patrick Tallarico
Patrick Tallarico earned 920 total points
ID: 39820041
It looks like Mysql does not currently support the IF NOT EXISTS functionality with the ALTER TABLE syntax.

One alternate method is to run a check on the Information_Schema.COLUMNS table.  This should list all columns in the database.
Something like
SELECT COLUMN_NAME
from information_schema.COLUMNS
where TABLE_NAME = 'yourTable'
and COLUMN_NAME = 'yourNewColumnName';

If that does not return a record of your Column, then you should be alright to just issue a standard ALTER TABLE command like
ALTER TABLE `yourTable` ADD COLUMN `yourNewColumnName` bigint(19);
0
 
LVL 59

Accepted Solution

by:
Julian Hansen earned 1080 total points
ID: 39820166
There are a number of options here

1. Catch the error and ignore it
2. Write a stored procedure to do the work - something like this (source: http://stackoverflow.com/questions/14381895/mysql-add-column-if-not-exist)
CREATE PROCEDURE Alter_Table()
BEGIN
    DECLARE _count INT;
    SET _count = (  SELECT COUNT(*) 
                    FROM INFORMATION_SCHEMA.COLUMNS
                    WHERE   TABLE_NAME = 'email_subscription' AND 
                            COLUMN_NAME = 'subscribe_all');
    IF _count = 0 THEN
        ALTER TABLE email_subscription
            ADD COLUMN subscribe_all TINYINT(1) DEFAULT 1,
            ADD COLUMN subscribe_category varchar(512) DEFAULT NULL;
    END IF;
END

Open in new window

3. Run a script to do what the stored procedure does and only run the query if the query against the information_schema table returns no results.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 39832626
so you both agree that MySQL don't accept the "IF NOT EXISTS " stagble for Alter table, right?
0
Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.

 
LVL 1

Author Comment

by:marrowyung
ID: 39846770
julianH,


What is the code to Catch the error and ignore it ? this might be ok for me as currently what I am experiencing is, I have another check that if the field has been removed and what I will do is to rename the table and clone the existing table once.

So if this process run first and the check runs later,  I will run into the sam problem as the talbe already cloned. so the same field exsiting over there.

the result can be incorrect !
0
 
LVL 59

Expert Comment

by:Julian Hansen
ID: 39846950
Personally I would go with the check to see if the column exists before hand - either in a stored procedure as shown above or executing the query to check before hand.
0
 
LVL 1

Author Closing Comment

by:marrowyung
ID: 39858106
I am still testing it .
0

Featured Post

Fill in the form and get your FREE NFR key NOW!

Veeam® is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
Backups and Disaster RecoveryIn this post, we’ll look at strategies for backups and disaster recovery.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

722 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question