MySQL check if a table exists without throwing an exception
Querying the information_schema database using prepared statement looks like the most reliable and secure solution.
$sql = "SELECT 1 FROM information_schema.tables
WHERE table_schema = database() AND table_name = ?";
$stmt = $pdo->prepare($sql);
$stmt->execute([$tableName]);
$exists = (bool)$stmt->fetchColumn();
Checking that a table exists on MySQL
Try using the information_schema
to ask if the table exists. Something like
SELECT
*
FROM
information_schema
WHERE TABLE_NAME = "$table_array"
Take a look through everything the information_schema
holds, you will be pleasantly surprised by the information it has stored about your databases :)
How to check a condition whether table exist or not in RMysql
Here's what I do. Maybe there's something else that's more robust or generalizable?
Just "show tables" as a query and check for the presence of your table name in the result.
loadData <- function() {
db <- dbConnect(
MySQL(),
dbname = databaseName,
host = host,
port = port,
user = user,
password = password
)
rs <- dbSendQuery(con, "show tables")
table.frame <- fetch(rs, n = -1)
if ("some_table" %in% table.frame[, 1]) {
res <- dbSendQuery(db, "SELECT * FROM some_table")
final_data <- dbFetch(res)
dbDisconnect(db)
return(final_data)
} else {
return(NULL)
}
}
How to check if a table exists in MySQL using PHP PDO?
So i understand you will create the table, if it does not exist and insert data. So call first
$pdo->query("CREATE TABLE $TABLE IF NOT EXISTS;");
It will do nothing, when table exists.
And then insert your data.
$pdo->query("INSERT INTO $TABLE ... ");
No 'if then else' in PHP!
How to check if a table exists in a given schema
It depends on what you want to test exactly.
Information schema?
To find "whether the table exists" (no matter who's asking), querying the information schema (information_schema.tables
) is incorrect, strictly speaking, because (per documentation):
Only those tables and views are shown that the current user has access
to (by way of being the owner or having some privilege).
The query provided by @kong can return FALSE
, but the table can still exist. It answers the question:
How to check whether a table (or view) exists, and the current user has access to it?
SELECT EXISTS (
SELECT FROM information_schema.tables
WHERE table_schema = 'schema_name'
AND table_name = 'table_name'
);
The information schema is mainly useful to stay portable across major versions and across different RDBMS. But the implementation is slow, because Postgres has to use sophisticated views to comply to the standard (information_schema.tables
is a rather simple example). And some information (like OIDs) gets lost in translation from the system catalogs - which actually carry all information.
System catalogs
Your question was:
How to check whether a table exists?
SELECT EXISTS (
SELECT FROM pg_catalog.pg_class c
JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE n.nspname = 'schema_name'
AND c.relname = 'table_name'
AND c.relkind = 'r' -- only tables
);
Use the system catalogs pg_class
and pg_namespace
directly, which is also considerably faster. However, per documentation on pg_class
:
The catalog
pg_class
catalogs tables and most everything else that has
columns or is otherwise similar to a table. This includes indexes (but
see alsopg_index
), sequences, views, materialized views, composite
types, and TOAST tables;
For this particular question you can also use the system view pg_tables
. A bit simpler and more portable across major Postgres versions (which is hardly of concern for this basic query):
SELECT EXISTS (
SELECT FROM pg_tables
WHERE schemaname = 'schema_name'
AND tablename = 'table_name'
);
Identifiers have to be unique among all objects mentioned above. If you want to ask:
How to check whether a name for a table or similar object in a given schema is taken?
SELECT EXISTS (
SELECT FROM pg_catalog.pg_class c
JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE n.nspname = 'schema_name'
AND c.relname = 'table_name'
);
- Related answer on dba.SE discussing "Information schema vs. system catalogs"
Alternative: cast to regclass
SELECT 'schema_name.table_name'::regclass;
This raises an exception if the (optionally schema-qualified) table (or other object occupying that name) does not exist.
If you do not schema-qualify the table name, a cast to regclass
defaults to the search_path
and returns the OID for the first table found - or an exception if the table is in none of the listed schemas. Note that the system schemas pg_catalog
and pg_temp
(the schema for temporary objects of the current session) are automatically part of the search_path
.
You can use that and catch a possible exception in a function. Example:
- Check if sequence exists in Postgres (plpgsql)
A query like above avoids possible exceptions and is therefore slightly faster.
Note that the each component of the name is treated as identifier here - as opposed to above queries where names are given as literal strings. Identifiers are cast to lower case unless double-quoted. If you have forced otherwise illegal identifiers with double-quotes, those need to be included. Like:
SELECT '"Dumb_SchName"."FoolishTbl"'::regclass;
See:
- Are PostgreSQL column names case-sensitive?
to_regclass(rel_name)
in Postgres 9.4+
Much simpler now:
SELECT to_regclass('schema_name.table_name');
Same as the cast, but it returns ...
... null rather than throwing an error if the name is not found
Check if MySQL table exists or not
Updated mysqli version:
if ($result = $mysqli->query("SHOW TABLES LIKE '".$table."'")) {
if($result->num_rows == 1) {
echo "Table exists";
}
}
else {
echo "Table does not exist";
}
Original mysql version:
if(mysql_num_rows(mysql_query("SHOW TABLES LIKE '".$table."'"))==1)
echo "Table exists";
else echo "Table does not exist";
Referenced from the PHP docs.
Vb.net check if table exist in DB Mysql without Try Catch
UPDATE:
Mysql provide a method, i think is the best way excute this line
SELECT COUNT( * )
FROM information_schema.tables
WHERE table_schema = 'Database'
AND table_name = 'Table'
Related Topics
How to Validate Array in Laravel
PHP Replacing Multiple Spaces With a Single Space
Run a PHP File in a Cron Job Using Cpanel
Convert/Cast an Stdclass Object to Another Class
PHP Output Showing Little Black Diamonds With a Question Mark
JavaScript Equivalent of PHP'S In_Array()
How to Create and Download a CSV File from PHP Script
How to Show Ajax Requests in Url
How to Get Const'S Defined on a PHP Class
Why Does PHP 5.2+ Disallow Abstract Static Class Methods
MySQL: Select Random Entry, But Weight Towards Certain Entries
Ping Site and Return Result in PHP
PHP List of Specific Files in a Directory