1

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".

HaroldFinch
  • 762
  • 1
  • 6
  • 17
  • 1
    I think the only way to do this is with a stored procedure that performs a query against `INFORMATION_SCHEMA` and then decides whether to create the view. – Barmar Jul 15 '15 at 21:43
  • Are you attempting to read from the database from an application? This could be better treated on the scripting/backend side. If just in database @Barmar has the right idea – FirebladeDan Jul 15 '15 at 21:43
  • @Bharmar: The code I am writing could be executed on other people's databases, which I have no prior control over, so I cannot count on a stored procedure being there. Sure, I could create a stored procedure too, but that has the same issues as the view (I don't want to modify something if it already exists, only add something that never existed; that is much safer). – HaroldFinch Jul 15 '15 at 21:52
  • I don't think there's any way to do this directly in SQL, you'll need to do it in an application language outside the database. – Barmar Jul 15 '15 at 21:56
  • @FirebadeDan: Yes, I ultimately want to read a view from an application. In my case, from an R script. What would your suggested code be on the application script side? Right now, my script simply always calls "CREATE OR REPLACE VIEW" which is OK when testing on my local database, but this could be dangerous once my code is used by others. – HaroldFinch Jul 15 '15 at 21:57
  • Actually, does anyone know what MySQL's "CREATE VIEW" command does if the view already exists? I read [their documentation](https://dev.mysql.com/doc/refman/5.0/en/create-view.html), but I do not see an explicit statement of what happens in this case. (Their documentation only explicitly describes what happens for the "CREATE OR REPLACE" command.) – HaroldFinch Jul 15 '15 at 22:44

1 Answers1

1

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.

RandomSeed
  • 29,301
  • 6
  • 52
  • 87
  • you are right that if I change my syntax from "create or replace view" to simply "create view" then I at least avoid clobbering an existing view. Howerer, if the view exists, the resulting stderr output is annoying, and much worse is the error exit code (MySQL returns 1 in this case). I would like to avoid that, if possible. – HaroldFinch Jul 17 '15 at 21:22