To verify that a table with the value name exists in mysql

Asked 2 years ago, Updated 2 years ago, 47 views

DB : MySQL 5.7
TABLE : my_table
DB NAME : test
TABLE NAME : my_table 
    id INT
    table_name VARCHAR(32)
    PK (table_name)

TARGET DB NAME : test2

Like the schema information mentioned above, The table my_table in the test DB has the table name that exists in the test2 DB.

I want to check my_table to see if there is a table in test2 for each row. My attempt is as below, but I can't do it because I removed limit 1. (It's not an error, but it's going on indefinitely... It doesn't work even with limit 2.)

Is there a way?

# try
SELECT 
    id, 
    table_name, 
    (SELECT count(0) FROM information_schema.tables WHERE table_schema = 'test2' AND table_name = table_name)
FROM test.my_table;

mysql database sql

2022-09-20 15:37

1 Answers

SELECT
    t1.table_name,
    if(t.TABLE_NAME IS NOT NULL, 1, 0) AS table_exists
FROM test.my_table t1
LEFT JOIN information_schema.TABLES t ON t.TABLE_NAME = t1.table_name


2022-09-20 15:37

If you have any answers or tips


© 2024 OneMinuteCode. All rights reserved.