We help IT Professionals succeed at work.

check if table/record exists in mysql

mikha
mikha asked
on
I want to create scripts to create a database/schema and seed data in mysql as such, pseudo code below

if the table doesn't exits is true
    then create table

Note - I don't want to drop table before  creating it , just want to check if it exits or not ,

another way i was thinking was, which is less code , more like a try and catch block in other languages , such that if there is already a table by that name, it throws an exception and
i don't do anything .

try {

         create table MyTable
      }

Catch {
              //do nothing
   }

Also, if i version these scripts , such that

script 1
script 2
script 3,

is there a way, I can run this and make changes to my database from whatever folder i am in  , instead of having to copy the content of the scripts to the query window in mysql workbench
Comment
Watch Question

Most Valuable Expert 2012
Distinguished Expert 2019
Commented:

The docs are your friend:


As far back as 5.6 MySQL supports create table if not exists:

https://dev.mysql.com/doc/refman/5.5/en/create-table-select.html


To execute SQL commands in a file:

mysql> source file_name
mysql> \. file_name


https://dev.mysql.com/doc/refman/8.0/en/mysql-batch-commands.html


Tomas Helgi JohannssonDatabase Administrator / Software Engineer

Commented:
Hi,

You can query the mysql information_schema.tables to list available tables and use that in your script.

https://dev.mysql.com/doc/refman/8.0/en/tables-table.html

Another way and easier is to use the create table syntax that Sligtwv mentions. :-)

Regards,
   Tomas Helgi

Author

Commented:

thank you both for your comments. I do have one more thing , I'm not sure about. If i create database locally and set up a login to connect to my app. can I also somehow have that user set up in scripts, such that I can create the same set up and tables , simply by running scripts in a prod server?

Tomas Helgi JohannssonDatabase Administrator / Software Engineer

Commented:
Hi,

Yes, you can create a database locally and setup a user that you use in your local version of your app and script for testing purposes.

It is common practice to have dev, test and production environment to develop and test applications, configurations and scripts before running it in production.

Regards,
   Tomas Helgi
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:

>> can I also somehow have that user set up in scripts, such that I can create the same set up and tables , simply by running scripts in a prod server?


Yes.  I already answered that in my first post:

To execute SQL commands in a file:

mysql> source file_name
mysql> \. file_name

https://dev.mysql.com/doc/refman/8.0/en/mysql-batch-commands.html