How do i detect if a table exist? MySql

Question

-edit- OMG Ponies answered this correctly. I cant accept his answer because for now it is just a comment.

I was suggested to do this

SELECT table_schema, table_name FROM information_schema.tables
WHERE table_schema = 'mydb' AND table_name='ApprovePost';

However it is not reliable and cause me errors on several versions of mysql on windows and linux.

Maybe there is another way. Does anyone know?

This issue is i can do create table if not exists but i do a second pass to add the FK constraint. In my sql dump i see > 130 contains on a single table. The table only has 6 columns, only two of these need constrains. The constrains keep building and building everytime i restart the apache server or whenever mono feels the need to call my global init method in my webapp.

Answer 1

If your only actual problem now is recreating the foreign key constantly (aside from a possibly broken MySQL install considering your other troubles), why not:

1) give it a constraint symbol (should be unique in database) and let the adding fail silently / catch 'em?

ALTER TABLE tbl ADD CONSTRAINT only_one_please FOREIGN KEY (columnname) ...

2) or better yet, add the foreign key clause in your create table in the first place?

As for the original question: I know no reason why this should happen, and I cannot recreate it. Selecting from information_schema is afaik quite the preferred way of checking this, and hasn't failed me yet. Aside from a brute force check like SELECT * FROM tablename LIMIT 0; and checking for errors, you first might want to check for any other caching mechanisms besides MySQL's query cache, and if they're not there / not the problem, perhaps try a SELECT SQL_NO_CACHE table_schema, table_name FROM information_schema.tables.

Answer 2

SELECT table_name FROM information_schema.tables WHERE table_schema = 'databasename' AND table_name = 'tablename';

If this query returns no results then the table does not exist. (replace 'databasename' and 'tablename' with the name of your database and table) :d

Answer 3

Looks like you need to use the FLUSH TABLES command for the INFORMATION_SCHEMA.TABLES to reflect existing tables.

Reference:

• TABLE CACHE