Skip to Main Content

MySQL Database

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

I have a script file which the sql command line . but it does not execute

Syed JMay 20 2021

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

Comments

Gurjas
You should create a bitmap or btree index on the action_id of the history table depending on the cardinality of the column.
Which will fasten the checking of action usage in the history table.

As per my understanding the isused idea is not good.

Why don't you go for the foreign key in the table? If you have already implement the foreign key in the table then this problem should not be there.. now you have to check the existense manually in the table. If possible you can also implement the foreign key now.
668031
Thanks,
after all they did create a index. The problem of having a foreign key without having a index by that key is that it takes very long to check if it is used.
1 - 2

Post Details

Added on May 20 2021
7 comments
625 views