table_exists

Description

Tests whether the table specified in in_db and in_table exists either as a regular table, or as a temporary table. The returned value corresponds to the table that will be used, so if there’s both a temporary and a permanent table with the given name, then ‘TEMPORARY’ will be returned.

Parameters

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

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

out_exists ENUM('', ‘BASE TABLE’, ‘VIEW’, ‘TEMPORARY’): The return value: whether the table exists. The value is one of: * '' - the table does not exist neither as a base table, view, nor temporary table. * ‘BASE TABLE’ - the table name exists as a permanent base table 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.07 sec)

mysql> use db1; Database changed mysql> CREATE TABLE t1 (id INT PRIMARY KEY); Query OK, 0 rows affected (0.08 sec)

mysql> CREATE TABLE t2 (id INT PRIMARY KEY); Query OK, 0 rows affected (0.08 sec)

mysql> CREATE view v_t1 AS SELECT * FROM t1; Query OK, 0 rows affected (0.00 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.00 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.00 sec)

+————+ | @exists | +————+ | BASE TABLE | +————+ 1 row in set (0.01 sec)

mysql> CALL sys.table_exists(‘db1’, ‘v_t1’, @exists); SELECT @exists; Query OK, 0 rows affected (0.00 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.01 sec)

+———+ | @exists | +———+ | | +———+ 1 row in set (0.00 sec)