how to check if database exist in mysql procedure

Question

I have a procedure in mysql which is doing something with DB, but it needs to throw exception in case that it exist. How do I do that?

I need something like

if exist <db name> then
  SIGNAL SQLSTATE '45002'
  SET MESSAGE_TEXT = 'This database already exist';
end if;

Has been asked here before: stackoverflow.com/questions/838978/… — Bart Friederichs
– Bart Friederichs, Commented Mar 6, 2013 at 10:54
that question is not about implementation as if statement in a procedure — Petr
– Petr, Commented Mar 6, 2013 at 12:58

Bart Friederichs · Accepted Answer · 2013-03-06 13:27:00Z

1

You could use this SQL:

SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = 'DBName'

In a procedure it would then be something like this:

DECLARE name TEXT;
SELECT SCHEMA_NAME INTO name FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = 'DBName'
IF name = 'DBName' THEN
   SIGNAL SQLSTATE '45002'
   SET MESSAGE_TEXT = 'This database already exist';
END IF;

(Disclaimer: I haven't written MySQL procedures before and I haven't tested this one. It is only mentioned to give the way of a possible implementation. Also, this example isn't complete.)

edited Mar 6, 2013 at 13:27

answered Mar 6, 2013 at 10:52

Bart Friederichs

33.8k16 gold badges112 silver badges219 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Petr Over a year ago

how do I implement it in procedure as if statement?

Bart Friederichs Over a year ago

I updated my answer. I hope the syntax is correct. If anyone sees errors, please update the answer.

Petr Over a year ago

I suppose it's if name = 'DBName' then but this answers what I needed

Collectives™ on Stack Overflow

how to check if database exist in mysql procedure

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related