MySQL Script issues

Question

The script creates the sql command to copy a table from one database to another provided the table does not exists in the target database. The query checks if the table does not exists in sakila1 and it exist in sakila then table should be created.

When run on mysql command prompt the below query

SELECT concat('CREATE TABLE if does not exists sakila1.', TABLE_NAME, ' like sakila.', TABLE_NAME, ';') 
FROM information_schema.`TABLES` 
WHERE TABLE_SCHEMA = 'sakila'

it just writes the following sql command

CREATE TABLE if does not exists sakila1.actor like sakila.actor;                      |

| CREATE TABLE if does not exists sakila1.actor_info like sakila.actor_info;                 |

| CREATE TABLE if does not exists sakila1.address like sakila.address;                    |

| CREATE TABLE if does not exists sakila1.category like sakila.category;                   |

| CREATE TABLE if does not exists sakila1.city like sakila.city;                       |

| CREATE TABLE if does not exists sakila1.country like sakila.country;                    |

| CREATE TABLE if does not exists sakila1.customer like sakila.customer;                   |

| CREATE TABLE if does not exists sakila1.customer_list like sakila.customer_list;              |

| CREATE TABLE if does not exists sakila1.film like sakila.film;                       |

| CREATE TABLE if does not exists sakila1.film_actor like sakila.film_actor;                 |

| CREATE TABLE if does not exists sakila1.film_category like sakila.film_category;              |

| CREATE TABLE if does not exists sakila1.film_list like sakila.film_list;                  |

| CREATE TABLE if does not exists sakila1.film_text like sakila.film_text;                  |

| CREATE TABLE if does not exists sakila1.inventory like sakila.inventory;                  |

| CREATE TABLE if does not exists sakila1.language like sakila.language;                   |

| CREATE TABLE if does not exists sakila1.nicer_but_slower_film_list like sakila.nicer_but_slower_film_list; |

| CREATE TABLE if does not exists sakila1.payment like sakila.payment;                    |

| CREATE TABLE if does not exists sakila1.rental like sakila.rental;                     |

| CREATE TABLE if does not exists sakila1.sales_by_film_category like sakila.sales_by_film_category;     |

| CREATE TABLE if does not exists sakila1.sales_by_store like sakila.sales_by_store;             |

| CREATE TABLE if does not exists sakila1.staff like sakila.staff;                      |

| CREATE TABLE if does not exists sakila1.staff_list like sakila.staff_list;                 |

| CREATE TABLE if does not exists sakila1.store like sakila.store;                      |

+------------------------------------------------------------------------------------------------------------+

23 rows in set (0.00 sec)

The SQL command does not get executed rather it just displays as the sql command.

I need to create the required tables in sakila1 by running the above mentioned query instead of just writing for the command to create table.

Could someone help me!

Flag

*CREATE TABLE if **does** not exists* - excess word, will cause an error. — Akina, May 20 '21 at 09:03
Does this answer your question? [Is it possible to execute a string in MySQL?](https://stackoverflow.com/questions/999200/is-it-possible-to-execute-a-string-in-mysql) — Progman, May 24 '21 at 08:44

Kasim Husaini · Answer 1 · 2021-05-20T09:18:20.707

CREATE FUNCTION fn_table_exists(dbName VARCHAR(255), tableName VARCHAR(255))
  RETURNS BOOLEAN
  BEGIN
    DECLARE totalTablesCount INT DEFAULT (
      SELECT COUNT(*)
      FROM information_schema.TABLES
      WHERE (TABLE_SCHEMA COLLATE utf8_general_ci = dbName COLLATE utf8_general_ci)
        AND (TABLE_NAME COLLATE utf8_general_ci = tableName COLLATE utf8_general_ci)
    );
    RETURN IF(
      totalTablesCount > 0,
      TRUE,
      FALSE
    );
END
;


Set @query:=''
SELECT @query:=@query+concat('IF fn_table_exists(sakila1,',TABLE_NAME,')=FALSE THEN CREATE TABLE sakila1.', TABLE_NAME, ';') FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'sakila'
PREPARE stmt FROM @query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

MySQL Script issues

1 Answers1