Link to home
Start Free TrialLog in
Avatar of mikha
mikhaFlag 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
ASKER CERTIFIED SOLUTION
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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
Avatar of 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?

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
Avatar of slightwv (䄆 Netminder)
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