Create database if db not exist
I want to make SQL Server scritp for creating database if not exist.
From code above i getting this error:
Msg 911, Level 16, State 1, Line 5 Database ‘DataBase’ does not exist. Make sure that the name is entered correctly.
How to make database with tables and procedures when database not exist? I want to make it in one query
3 Answers 3
Could you check the following script :
Obviously you have to start with (and mind the GO here):
But you have to do it like this:
Mind the GO again. If you don’t use GO SSMS (or any other client) will still think your batch is not completed, your database is still not created and therefore not available for further use, and you get the error message you posted.
Now you can start using your just created database with:
Again, mind the GO statement. In this case it is inadmissible because it is not possible to combine CREATE DATABASE and CREATE TABLE statements in one batch. So after the GO continue with:
As already mentioned by others it is good practice to check if every table exists and do a create action if it doesn’t and alter action if it does (or just do nothing). But if you really don’t want to check if each table exists, for instance when you are sure you need to start from scratch, you could start with dropping the database if it exists:
How to check if mysql database exists
Is it possible to check if a (MySQL) database exists after having made a connection.
I know how to check if a table exists in a DB, but I need to check if the DB exists. If not I have to call another piece of code to create it and populate it.
22 Answers 22
If you just need to know if a db exists so you won’t get an error when you try to create it, simply use (From here):
A simple way to check if a database exists is:
If database with the name ‘dbname’ doesn’t exist, you get an empty set. If it does exist, you get one row.
From the shell like bash
If you are looking for a php script see below.
Here is a bash function for checking if a database exists:
Another alternative is to just try to use the database. Note that this checks permission as well:
A very simple BASH-one-liner:
A great way to check if a database exists in PHP is:
That is the method that I always use.
For those who use php with mysqli then this is my solution. I know the answer has already been answered, but I thought it would be helpful to have the answer as a mysqli prepared statement too.
With this Script you can get Yes or No database exists, in case it does not exist it does not throw Exception.
Long winded and convoluted (but bear with me!), here is a class system I made to check if a DB exists and also to create the tables required:
In this you can replace the database name en with any database name you like and also change the creator script to anything at all and (hopefully!) it won’t break it. If anyone can improve this, let me know!
Note
If you don’t use Visual Studio with PHP tools, don’t worry about the regions, they are they for code folding 😛
Here’s my way of doing it inside a bash script:
If you are using MSSQL instead of MySQL, see this answer from a similar thread.
I am using simply the following query:
Then check if the result is FALSE. Otherwise, there might be an access denied error, but I cannot know that. So, in case of privileges involved, one can use:
as already mentioned earlier.
Be careful when checking for existence with a like statement!
If in a series of unfortunate events your variable ends up being empty, and you end up executing this:
It will return ALL databases, thus telling the calling script that it exists since some rows were returned.
It’s much safer and better practice to use an «=» equal sign to test for existence.
The correct and safe way to test for existence should be:
Note that you have to wrap the column name database with backticks, it can’t use relaxed syntax in this case.
This way, if the code creating the variable ‘xxxxx’ returned blank, then SHOW DATABASES will not return ALL databases, but will return an empty set.
Несколько заметок о MySQL
За время работы с MySQL набралось некоторое количество нюансов и приемов, из которых я составил эту статью в виде набора заметок. Все это не секрет и, разумеется, можно найти в документации.
Буду использовать дефолтные настройки MySQL. Некоторые заметки связаны с PHP, поэтому для примеров буду использовать расширение mysqli.
Для запуска sql-запросов из статьи можно инициализировать таблицы так:
Проверить текущие автоинкременты можно так:
У обоих таблиц они равны 4. При этом имеется такое подключение к БД:
Вставка по уникальному ключу и автоинкремент
Если в таблице есть уникальный ключ, то для вставки и обновления есть три способа переложить проверку уникальности на MySQL: INSERT IGNORE, INSERT… ON DUPLICATE KEY UPDATE, REPLACE. Каждый тип запросов по разному ведет себя с автоинкрементом на разных типах таблиц:
Автоинкремент стал 5, хотя вставки не было. А что будет с MyISAM:
Там автоинкремент остался 4. Аналогичная ситуация будет с ON DUPLICATE KEY UPDATE:
В user_innodb автоинкремент стал 6, а у user_myisam остался 4.
REPLACE работает иначе: в случае нахождения совпадений в уникальном ключе, он удалит старую запись и добавт новую.
Увеличит автоинкремент до 7, теперь у Петрова >
У MyISAM аналогично:
Автоинкремент стал 5, а Петров получил >
Итак, REPLACE работает на обоих движках таблиц одинаково, а INSERT IGNORE и ON DUPLICATE KEY UPDATE изменяют автоинкремент на InnoDB.
Получение id изменяемой записи после обновления
Выведет 7 и 7, первый раз запись была добавлена под второй раз изменена.
С INSERT IGNORE такой трюк не выйдет. Код ниже выведет 9 и 0
Реализация SEQUENCE
У MySQL, в отличие от других СУРБД, нет такой штуки как SEQUENCE. Есть автоинкремент, но он не позвоялет решить все задачи, с которыми может помочь SEQUENCE. Например, шардинг.
В общем случае, если надо раскладывать записи по различным таблицам или даже базам, нужна будет мастер-таблица с автоинкрементным полем, в котором централизованно генерировался бы ID новой записи.
Решить эту задачу можно так:
В одной таблице получается сразу несколько последовательностей, в этом примере три.
Далее можно получать следующий ID из нужной последовательности с помощью функции
last_insert_id():
Для автоинкремента есть возможность указать шаг приращения опцией конфигурации auto_increment_increment. В этом примере такую функцию можно реализовать примерно так:
Немного о беззнаковых целых
С аккуратностью используйте беззнаковые целые как типы полей MySQL, если обращаетесь к БД из PHP.
История моего «озарения» по этому поводу. Для поля id всегда использовал беззнаковый целый тип, все равно классический id не бывает отрицательным. Однажды, генерируя модель с помощью Gii (скаффолдинг Yii), я обратил внимание, на то что правила валидации в модели для моих id и других беззнаковых целых полей генерируются как для строк. “WTF?” — подумал я и полез в код фреймворка, где обнаружил, что при разборе типов полей есть такой “хардкод” проверки на наличие unsigned:
Я посчитал это ошибкой, обрадовался, что сейчас у меня есть шанс внести свою лепту в исправление багов Yii. Но радость быстро сменилась мыслью “это ж-ж-ж-ж, неспроста”.
Действительно, в PHP нет беззнаковых целых, а в общем случае целые в PHP 32-х разрядные (под 32-bit Linux и под Windows). Если целочисленное значение превышает PHP_INT_MAX, то оно приводится к float, и тут самое место для возникновения магии со странными багами. Так что господин Qiang Xue все правильно сделал.
Simulate CREATE DATABASE IF NOT EXISTS for PostgreSQL?
I want to create a database which does not exist through JDBC. Unlike MySQL, PostgreSQL does not support create if not exists syntax. What is the best way to accomplish this?
The application does not know if the database exists or not. It should check and if the database exists it should be used. So it makes sense to connect to the desired database and if connection fails due to non-existence of database it should create new database (by connecting to the default postgres database). I checked the error code returned by Postgres but I could not find any relevant code that species the same.
Another method to achieve this would be to connect to the postgres database and check if the desired database exists and take action accordingly. The second one is a bit tedious to work out.
Is there any way to achieve this functionality in Postgres?
11 Answers 11
Restrictions
CREATE DATABASE cannot be executed inside a transaction block.
So it cannot be run directly inside a function or DO statement, where it would be inside a transaction block implicitly. SQL procedures, introduced with Postgres 11, cannot help with this either.
Workaround from within psql
You can work around it from within psql by executing the DDL statement conditionally:
Sends the current query buffer to the server, then treats each column of each row of the query’s output (if any) as a SQL statement to be executed.
Workaround from the shell
With \gexec you only need to call psql once:
Workaround from within Postgres transaction
You could use a dblink connection back to the current database, which runs outside of the transaction block. Effects can therefore also not be rolled back.
Install the additional module dblink for this (once per database):
Again, you may need more psql options for the connection. See Ortwin’s added answer:
Detailed explanation for dblink:
You can make this a function for repeated use.
another alternative, just in case you want to have a shell script which creates the database if it does not exist and otherwise just keeps it as it is:
I found this to be helpful in devops provisioning scripts, which you might want to run multiple times over the same instance.
For those of you who would like an explanation:
If you don’t care about the data, you can drop database first and then recreate it:
When CREATE SCHEMA IF NOT EXISTS is issued and schema already exists then notice (not error) with duplicate object information is raised.
To solve these problems you need to use dblink extension which opens a new connection to database server and execute query without entering into transaction. You can reuse connection parameters with supplying empty string.
This solution is without any race condition like in other answers, where database can be created by external process (or other instance of same script) between checking if database exists and its own creation.
Moreover when CREATE DATABASE fails with other error than database already exists then this error is propagated as error and not silently discarded. There is only catch for duplicate_database error. So it really behaves as IF NOT EXISTS should.
You can put this code into own function, call it directly or from transaction. Just rollback (restore dropped database) would not work.
Testing output (called two times via DO and then directly):
Here is the updated question:
the current query is doing something like:
The first time the method containing this is run, it generates an error message on the truncate since the table doesn’t exist yet.
original question was:
I’ve been having a hard time trying to figure out if the following is possible in MySql without having to write block sql:
If I run truncate separately before the create table, and the table doesn’t exist, then I get an error message. I’m trying to eliminate that error message without having to add any more queries.
This code will be executed using PHP.
6 Answers 6
shmuel613, it would be better to update your original question rather than replying. It’s best if there’s a single place containing the complete question rather than having it spread out in a discussion.
Ben’s answer is reasonable, except he seems to have a ‘not’ where he doesn’t want one. Dropping the table only if it doesn’t exist isn’t quite right.
You will indeed need multiple statements. Either conditionally create then populate:
or just drop and recreate
With pure SQL those are your two real classes of solutions. I like the second better.
(With a stored procedure you could reduce it to a single statement. Something like: TruncateAndPopulate(fubar) But by the time you write the code for TruncateAndPopulate() you’ll spend more time than just using the SQL above.)