Solved

The MySQL add if not exists for a new field.

Posted on 2014-01-29
6
4,234 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 230 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 58

Accepted Solution

by:
Julian Hansen earned 270 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
Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

 
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 58

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

Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

Question has a verified solution.

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

As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
I have been using r1soft Continuous Data Protection (http://www.r1soft.com/linux-cdp/) for many years now with the mySQL Addon and wanted to share a trick I have used several times. For those of us that don't have the luxury of using all transact…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…

617 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