SELECT count(*)
FROM information_schema.TABLES
WHERE (TABLE_SCHEMA = 'your_db_name') AND (TABLE_NAME = 'name_of_table')

如果得到非零计数，则表存在。

如果想要正确，请使用INFORMATION_SCHEMA。

SELECT * 
FROM information_schema.tables
WHERE table_schema = 'yourdb' 
    AND table_name = 'testtable'
LIMIT 1;

或者，您也可以使用SHOW TABLES

SHOW TABLES LIKE 'yourtable';

如果结果集中有行，则表存在。

扩展这个答案，可以进一步编写一个函数，根据表是否存在返回TRUE/FALSE:

CREATE FUNCTION fn_table_exists(dbName VARCHAR(255), tableName VARCHAR(255))
  RETURNS BOOLEAN
  BEGIN
    DECLARE totalTablesCount INT DEFAULT (
      SELECT COUNT(*)
      FROM information_schema.TABLES
      WHERE (TABLE_SCHEMA COLLATE utf8_general_ci = dbName COLLATE utf8_general_ci)
        AND (TABLE_NAME COLLATE utf8_general_ci = tableName COLLATE utf8_general_ci)
    );
    RETURN IF(
      totalTablesCount > 0,
      TRUE,
      FALSE
    );
END
;


SELECT fn_table_exists('development', 'user');

这里的答案有几个问题需要注意:

1) INFORMATION_SCHEMA。TABLES不包括TEMPORARY表。

2)使用任何类型的SHOW查询，即SHOW TABLES LIKE 'test_table'，将强制返回一个结果集到客户端，这是检查表是否存在服务器端的不希望的行为，从存储过程中也返回一个结果集。

3)正如一些用户提到的，你必须小心如何使用SELECT 1 FROM test_table LIMIT 1。

如果你这样做:

SET @table_exists = 0;
SET @table_exists = (SELECT 1 FROM test_table LIMIT 1);

如果表中没有行，则不会得到预期的结果。

下面是一个适用于所有表(甚至是TEMPORARY表)的存储过程。

它可以这样使用:

SET @test_table = 'test_table';
SET @test_db = NULL;
SET @does_table_exist = NULL;

CALL DoesTableExist(@test_table, @test_db, @does_table_exist);

SELECT @does_table_exist;

代码:

/*
    p_table_name is required
    p_database_name is optional
        if NULL is given for p_database_name, then it defaults to the currently selected database
    p_does_table_exist
        The @variable to save the result to

    This procedure attempts to
        SELECT NULL FROM `p_database_name`.`p_table_name` LIMIT 0;

    If [SQLSTATE '42S02'] is raised, then
        SET p_does_table_exist = 0
    Else
        SET p_does_table_exist = 1

    Info on SQLSTATE '42S02' at:
        https://dev.mysql.com/doc/refman/5.7/en/server-error-reference.html#error_er_no_such_table
*/

DELIMITER $$

DROP PROCEDURE IF EXISTS DoesTableExist
$$

CREATE PROCEDURE         DoesTableExist (
    IN p_table_name VARCHAR(64),
    IN p_database_name VARCHAR(64),
    OUT p_does_table_exist TINYINT(1) UNSIGNED
)
BEGIN
    /* 793441 is used in this procedure for ensuring that user variables have unique names */

    DECLARE EXIT HANDLER FOR SQLSTATE '42S02'
    BEGIN
        SET p_does_table_exist = 0
        ;
    END
    ;


    IF p_table_name IS NULL THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'DoesTableExist received NULL for p_table_name.';
    END IF;


    /* redirect resultset to a dummy variable */

    SET @test_select_sql_793441 = CONCAT(
        "SET @dummy_var_793441 = ("
            " SELECT"
                " NULL"
            " FROM ",
                IF(
                    p_database_name IS NULL,
                    "",
                    CONCAT(
                        "`",
                        REPLACE(p_database_name, "`", "``"),
                        "`."
                    )
                ),
                "`",
                REPLACE(p_table_name, "`", "``"),
                "`"
            " LIMIT 0"
        ")"
    )
    ;

    PREPARE _sql_statement FROM @test_select_sql_793441
    ;
    SET @test_select_sql_793441 = NULL
    ;
    EXECUTE _sql_statement
    ;
    DEALLOCATE PREPARE _sql_statement
    ;

    SET p_does_table_exist = 1
    ;
END
$$

DELIMITER ;

在阅读了以上所有内容后，我更喜欢以下说法:

SELECT EXISTS(
       SELECT * FROM information_schema.tables 
       WHERE table_schema = 'db' 
       AND table_name = 'table'
);

它确切地指出你想做什么，它实际上返回一个'布尔'

如果在2019年之后阅读这篇文章，请注意MySQL 5.7添加了一个table_exists过程，它将确定一个表是否存在，包括临时表。

用法: 设置@exist为"，'BASE TABLE'， 'VIEW'， 'TEMPORARY'中的一个

CALL sys.table_exists('db1', 't3', @exists);

参考:

https://dev.mysql.com/doc/refman/5.7/en/sys-table-exists.html