30.4.4.27 The table_exists() Procedure

Tests whether a given table exists as a regular table, a TEMPORARY table, or a view. The procedure returns the table type in an OUT 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 an OUT 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 a TEMPORARY table.

Example

mysql> CREATE DATABASE db1;
Query OK, 1 row affected (0.01 sec)

mysql> USE db1;
Database changed

mysql> 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)