CODEWITHSUNDEEP
mysqlview
HaroldFinch
HaroldFinch

Reputation: 911

MySQL: create view if it doesn't exist, but don't alter it if it does

Is there an elegant canonical way to create a view in a MySQL database ONLY if it does not exist, but don't do anything if the view already does exist?

There is a similar question here, whose answer is to use the "CREATE OR REPLACE VIEW" syntax.

That does not work for me, since I do NOT want to replace the view if it exists.

Ideal would a syntax like "CREATE IF DOES NOT EXIST VIEW".

Upvotes: 1

Views: 1482

Answers (1)

RandomSeed
RandomSeed

Reputation: 29769

If you attempt to create a view that already exists, then the existing view remains untouched.

Of course, there is an unpleasant side effect: it raises an error :) But perhaps you can live with it...

MySQL does not provide such a feature. As Barmar suggests, use a stored procedure (perhaps a disposable one) or an external script, I see no other option.

You commented:

[replacing an existing view without consideration] could be dangerous once my code is used by others

but it is equally dangerous for your code to rely on a view created by others. Your database is a intrinsic component of your application and should be treated as such. Nobody is supposed to mess with it. If you want to let your users create their own views, instruct them to create it in a separate database.

In case you target systems where only one database is available (as in most shared hosting solutions), the usual approach is to prefix your own structures names.

Upvotes: 1

Related Questions