How to Use ALTER Table query with IF Condtion in MySQL

Question

My requirement is to modify the column size of a table in MySQL table. I will use the ALTER statement to achieve this. But Before that I need to check whether the table is exists or not. If the table exists then only I will use ALTER statement.

I tried so many ways. But not able to achieve this. Is there a way to achieve this in MySQL.

Please help me to achieve this.

You need to run query like select 1 from tablename limit 1 , to check if table exists. Also check https://stackoverflow.com/questions/8829102/mysql-check-if-table-exists-without-using-select-from — Love-Kesh, Nov 07 '17 at 12:57
Look at this one: https://stackoverflow.com/questions/972922/add-column-to-mysql-table-if-it-does-not-exist see the answer with 71 upvotes (second answer). — Nic3500, Nov 07 '17 at 13:04

score 0 · Answer 1 · answered Nov 07 '17 at 13:17

MySQL doesn't have a built in way to do this.

One option is simply to run the ALTER TABLE statement, and ignore the Table ... doesn't exist error if the table does not exist.

Another option (if you are willing to install common_schema) is to use QueryScript to wrap the ALTER TABLE command in a try catch and ignore the error if the table does not exist.

Something like this would work:

  call common_schema.run("
    try 
    {
      alter table sakila.film modify column title varchar(100) not null;
    }
    catch {}
  ");

How to Use ALTER Table query with IF Condtion in MySQL

1 Answers1