Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

The MySQL add if not exists for a new field.

Posted on 2014-01-29
6
Medium Priority
?
4,866 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
  • 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 60

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
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
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 60

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

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

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 …
This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
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

782 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