Documentation Home
MySQL 8.4 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 40.2Mb
PDF (A4) - 40.3Mb
Man Pages (TGZ) - 262.0Kb
Man Pages (Zip) - 367.6Kb
Info (Gzip) - 4.0Mb
Info (Zip) - 4.0Mb


30.4.4.26 The table_exists() Procedure

Tests whether a given table exists as a regular table, aTEMPORARY table, or a view. The procedure returns the table type in anOUT parameter. If both a temporary and a permanent table exist with the given name,TEMPORARY is returned.

Parameters

  • in_db VARCHAR(64): The name of the database in which to check for table existence.

  • in_table VARCHAR(64): The name of the table to check the existence of.

  • out_exists ENUM('', 'BASE TABLE', 'VIEW', 'TEMPORARY'): The return value. This is anOUT parameter, so it must be a variable into which the table type can be stored. When the procedure returns, the variable has one of the following values to indicate whether the table exists:

    • '': The table name does not exist as a base table,TEMPORARY table, or view.

    • BASE TABLE: The table name exists as a base (permanent) table.

    • VIEW: The table name exists as a view.

    • TEMPORARY: The table name exists as aTEMPORARY table.

Example
mysql> CREATE DATABASE db1;Query OK, 1 row affected (0.01 sec)mysql> USE db1;Database changedmysql> CREATE TABLE t1 (id INT PRIMARY KEY);Query OK, 0 rows affected (0.03 sec)mysql> CREATE TABLE t2 (id INT PRIMARY KEY);Query OK, 0 rows affected (0.20 sec)mysql> CREATE view v_t1 AS SELECT * FROM t1;Query OK, 0 rows affected (0.02 sec)mysql> CREATE TEMPORARY TABLE t1 (id INT PRIMARY KEY);Query OK, 0 rows affected (0.00 sec)mysql> CALL sys.table_exists('db1', 't1', @exists); SELECT @exists;Query OK, 0 rows affected (0.01 sec)+-----------+| @exists   |+-----------+| TEMPORARY |+-----------+1 row in set (0.00 sec)mysql> CALL sys.table_exists('db1', 't2', @exists); SELECT @exists;Query OK, 0 rows affected (0.02 sec)+------------+| @exists    |+------------+| BASE TABLE |+------------+1 row in set (0.00 sec)mysql> CALL sys.table_exists('db1', 'v_t1', @exists); SELECT @exists;Query OK, 0 rows affected (0.02 sec)+---------+| @exists |+---------+| VIEW    |+---------+1 row in set (0.00 sec)mysql> CALL sys.table_exists('db1', 't3', @exists); SELECT @exists;Query OK, 0 rows affected (0.00 sec)+---------+| @exists |+---------+|         |+---------+1 row in set (0.00 sec)