Avatar of mikha
mikha
Flag for United States of America asked on

check if table/record exists in mysql

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
SQL

Avatar of undefined
Last Comment
slightwv (䄆 Netminder)

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
slightwv (䄆 Netminder)

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Tomas Helgi Johannsson

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
mikha

ASKER

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 Johannsson

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
Your help has saved me hundreds of hours of internet surfing.
fblack61
slightwv (䄆 Netminder)

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