Database Administration
, ,

SQLdump IF NOT EXISTS Vs DROP

There must be a “correct” answer, or at least a logical reason for it. I’ve been working with RDBMS / relation database software for roughly 20 years now. While I have no formal training or degree in database administration, I certainly have studied a lot about database optimization, relationships, and theory such as normalization. This particular issue confounds me.

I’m mostly familiar with MySQL, but the SQL syntax is basic. MySQL allows for the IF NOT EXISTS phrase in data import operations. Using IF NOT EXISTS is a more non-destructive approach to importing data. E.g. CREATE TABLE IF NOT EXISTS allows for existing data to remain if it’s present, while DROP TABLE IF EXISTS is most certainly going to destroy any data which might be present.

Whey then does an sqldump / mysqldump instead use the latter in most instances? Please comment here if you have the answer.

In any case, I usually use the following search / replace regular expression to counter that prior to importing data. It works for me and eliminates some headache at least. Have at it if you wish!

Search expression:
^.*(DROP TABLE IF EXISTS )(.*)$

Replacement expression:
\n

Then, subsequently

Search expression:
^(.*)(CREATE TABLE )(.*)$

Replacement expression:
CREATE TABLE IF NOT EXISTS 

I also apply this same principal to the CREATE DATABASE syntax.

Whatchu do


Leave a Reply

Your email address will not be published. Required fields are marked *