blocks|key|1740339|text|如果您想要正确，请使用INFORMATION_SCHEMA。|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|1740340|SELECT+*+
FROM+information_schema.tables
WHERE+table_schema+=+'yourdb'+
++++AND+table_name+=+'testtable'
LIMIT+1;|code-block|syntax|javascript|1740341|或者，您可以使用SHOW+TABLES|style|CODE|1740342|SHOW+TABLES+LIKE+'yourtable';|1740343|如果结果集中有行，则表存在。|1740344|entityMap|0|LINK|mutability|MUTABLE|url|http://dev.mysql.com/doc/refman/5.0/en/tables-table.html^0|B|I|0|0|0|8|B|0|0|0^^$0|@$1|2|3|4|5|6|7|Y|8|@]|9|@$A|Z|B|10|1|11]]|C|$]]|$1|D|3|E|5|F|7|12|8|@]|9|@]|C|$G|H]]|$1|I|3|J|5|6|7|13|8|@$A|14|B|15|K|L]]|9|@]|C|$]]|$1|M|3|N|5|F|7|16|8|@]|9|@]|C|$G|H]]|$1|O|3|P|5|6|7|17|8|@]|9|@]|C|$]]|$1|Q|3|-4|5|6|7|18|8|@]|9|@]|C|$]]]|R|$S|$5|T|U|V|C|$W|X]]]]

If you want to be correct, use <a href="http://dev.mysql.com/doc/refman/5.0/en/tables-table.html" rel="noreferrer">INFORMATION_SCHEMA</a>.

<pre><code>SELECT * 
FROM information_schema.tables
WHERE table_schema = 'yourdb' 
 AND table_name = 'testtable'
LIMIT 1;
</code></pre>

Alternatively, you can use <code>SHOW TABLES</code>

<pre><code>SHOW TABLES LIKE 'yourtable';
</code></pre>

If there is a row in the resultset, table exists.

blocks|key|1953775|text|SELECT+count(*)
FROM+information_schema.TABLES
WHERE+(TABLE_SCHEMA+=+'your_db_name')+AND+(TABLE_NAME+=+'name_of_table')|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|1953776|如果您得到一个非零计数，则该表存在。|unstyled|1953777|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$B|C]]|$1|D|3|E|5|F|7|J|8|@]|9|@]|A|$]]|$1|G|3|-4|5|F|7|K|8|@]|9|@]|A|$]]]|H|$]]

<pre><code>SELECT count(*)
FROM information_schema.TABLES
WHERE (TABLE_SCHEMA = 'your_db_name') AND (TABLE_NAME = 'name_of_table')
</code></pre>

if you get a non-zero count, the table exists.

blocks|key|1740717|text|您可以查询INFORMATION_SCHEMA+tables系统视图：|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1740718|SELECT+table_name
FROM+information_schema.tables
WHERE+table_schema+=+'databasename'
AND+table_name+=+'testtable';|code-block|syntax|javascript|1740719|如果没有返回任何行，则该表不存在。|1740720|entityMap^0|O|6|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@$9|P|A|Q|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|R|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|S|8|@]|D|@]|E|$]]|$1|M|3|-4|5|6|7|T|8|@]|D|@]|E|$]]]|N|$]]

You can query the INFORMATION_SCHEMA <code>tables</code> system view:

<pre><code>SELECT table_name
FROM information_schema.tables
WHERE table_schema = 'databasename'
AND table_name = 'testtable';
</code></pre>

If no rows returned, then the table doesn't exist.

blocks|key|1954120|text|您可以查询INFORMATION_SCHEMA.TABLES来查看表是否存在，而不是依赖于错误。如果有记录，它就是存在的。如果没有记录，它就不存在。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1954121|entityMap|0|LINK|mutability|MUTABLE|url|http://dev.mysql.com/doc/refman/5.0/en/tables-table.html^0|5|P|5|P|0|0^^$0|@$1|2|3|4|5|6|7|N|8|@$9|O|A|P|B|C]]|D|@$9|Q|A|R|1|S]]|E|$]]|$1|F|3|-4|5|6|7|T|8|@]|D|@]|E|$]]]|G|$H|$5|I|J|K|E|$L|M]]]]

Rather than relying on errors, you can query <a href="http://dev.mysql.com/doc/refman/5.0/en/tables-table.html" rel="noreferrer"><code>INFORMATION_SCHEMA.TABLES</code></a> to see if the table exists. If there's a record, it exists. If there's no record, it doesn't exist.

blocks|key|1954150|text|除了SELECT之外，所有的选项都不允许使用SELECT中使用的数据库名称，所以我写了以下代码：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1954151|SELECT+COUNT(*)+AS+cnt+FROM+information_schema.TABLES+
WHERE+CONCAT(table_schema,".",table_name)="db_name.table_name";|code-block|syntax|javascript|1954152|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

None of the options except SELECT doesn't allow database name as used in SELECT, so I wrote this:

<pre><code>SELECT COUNT(*) AS cnt FROM information_schema.TABLES 
WHERE CONCAT(table_schema,".",table_name)="db_name.table_name";
</code></pre>

blocks|key|1954195|text|下面是一个不是SELECT+*+FROM的表|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1954196|SHOW+TABLES+FROM+`db`+LIKE+'tablename';+//zero+rows+=+not+exist|code-block|syntax|javascript|1954197|这是从一个数据库专家那里得到的，这是我被告知的：|1954198|select+1+from+`tablename`;+//avoids+a+function+call
select+*+from+INFORMATION_SCHEMA.tables+where+schema+=+'db'+and+table+=+'table'+//+slow.+Field+names+not+accurate
SHOW+TABLES+FROM+`db`+LIKE+'tablename';+//zero+rows+=+does+not+exist|1954199|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|N|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|O|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

Here is a table that is not a SELECT * FROM
<pre><code>SHOW TABLES FROM `db` LIKE 'tablename'; //zero rows = not exist
</code></pre>
Got this from a database pro, here is what I was told:
<pre><code>select 1 from `tablename`; //avoids a function call
select * from INFORMATION_SCHEMA.tables where schema = 'db' and table = 'table' // slow. Field names not accurate
SHOW TABLES FROM `db` LIKE 'tablename'; //zero rows = does not exist
</code></pre>

blocks|key|1954205|text|您可以执行如下操作：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1954206|++++++++++++string+strCheck+=+"SHOW+TABLES+LIKE+\'tableName\'";
++++++++++++cmd+=+new+MySqlCommand(strCheck,+connection);
++++++++++++if+(connection.State+==+ConnectionState.Closed)
++++++++++++{
++++++++++++++++connection.Open();
++++++++++++}
++++++++++++cmd.Prepare();
++++++++++++var+reader+=+cmd.ExecuteReader();
++++++++++++if+(reader.HasRows)
++++++++++++{+++++++++++++++++++++++++++++
++++++++++++++Console.WriteLine("Table+Exist!");
++++++++++++}
++++++++++++else
++++++++++++{+++++++++++++++++++++++++++++
++++++++++++++Console.WriteLine("Table+does+not+Exist!");
++++++++++++}|code-block|syntax|javascript|1954207|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

You can do something like below:

<pre><code> string strCheck = "SHOW TABLES LIKE \'tableName\'";
 cmd = new MySqlCommand(strCheck, connection);
 if (connection.State == ConnectionState.Closed)
 {
 connection.Open();
 }
 cmd.Prepare();
 var reader = cmd.ExecuteReader();
 if (reader.HasRows)
 { 
 Console.WriteLine("Table Exist!");
 }
 else
 { 
 Console.WriteLine("Table does not Exist!");
 }
</code></pre>

blocks|key|1740873|text|我在php中用到了这个。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1740874|private+static+function+ifTableExists(string+$database,+string+$table):+bool
++++{
++++++++$query+=+DB::select("
++++++++++++SELECT+
++++++++++++++++IF(+EXISTS+
++++++++++++++++++++(SELECT+*+FROM+information_schema.COLUMNS
++++++++++++++++++++++++WHERE+TABLE_SCHEMA+=+'$database'
++++++++++++++++++++++++AND+TABLE_NAME+=+'$table'
++++++++++++++++++++++++LIMIT+1),
++++++++++++++++1,+0)
++++++++++++++++AS+if_exists
++++++++");

++++++++return+$query[0]->if_exists+==+1;
++++}|code-block|syntax|javascript|1740875|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

I use this in php.

<pre><code>private static function ifTableExists(string $database, string $table): bool
 {
 $query = DB::select("
 SELECT 
 IF( EXISTS 
 (SELECT * FROM information_schema.COLUMNS
 WHERE TABLE_SCHEMA = '$database'
 AND TABLE_NAME = '$table'
 LIMIT 1),
 1, 0)
 AS if_exists
 ");

 return $query[0]-&gt;if_exists == 1;
 }
</code></pre>

blocks|key|1954344|text|显示类似'table_name‘的表|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1954345|如果返回rows+>0，则表存在|1954346|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|F|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|G|8|@]|9|@]|A|$]]|$1|D|3|-4|5|6|7|H|8|@]|9|@]|A|$]]]|E|$]]

show tables like 'table_name'

if this returns rows > 0 the table exists

blocks|key|1954385|text|上述修改后的解决方案不需要明确了解当前数据库。这样就更灵活了。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1954386|SELECT+count(*)+FROM+information_schema.TABLES+WHERE+TABLE_NAME+=+'yourtable'+
AND+TABLE_SCHEMA+in+(SELECT+DATABASE());|code-block|syntax|javascript|1954387|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

This modified solution from above does not require explicit knowledge of the current database. It is then more flexible.

<pre><code>SELECT count(*) FROM information_schema.TABLES WHERE TABLE_NAME = 'yourtable' 
AND TABLE_SCHEMA in (SELECT DATABASE());
</code></pre>

blocks|key|1954443|text|扩展此answer，可以进一步编写一个函数，根据表是否存在来返回TRUE/FALSE：|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|1954444|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');|code-block|syntax|javascript|1954445|entityMap|0|LINK|mutability|MUTABLE|url|https://stackoverflow.com/a/8829122/7032856^0|3|6|0|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@$A|R|B|S|1|T]]|C|$]]|$1|D|3|E|5|F|7|U|8|@]|9|@]|C|$G|H]]|$1|I|3|-4|5|6|7|V|8|@]|9|@]|C|$]]]|J|$K|$5|L|M|N|C|$O|P]]]]

Expanding this <a href="https://stackoverflow.com/a/8829122/7032856">answer</a>, one could further write a function that returns TRUE/FALSE based on whether or not a table exists:

<pre><code>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 &gt; 0,
 TRUE,
 FALSE
 );
END
;


SELECT fn_table_exists('development', 'user');
</code></pre>

blocks|key|1954487|text|在读完以上所有内容后，我更喜欢下面的陈述：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1954488|SELECT+EXISTS(
+++++++SELECT+*+FROM+information_schema.tables+
+++++++WHERE+table_schema+=+'db'+
+++++++AND+table_name+=+'table'
);|code-block|syntax|javascript|1954489|它准确地指出了你想要做什么，并且它实际上返回了一个'boolean‘。|1954490|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

After reading all of the above, I prefer the following statement:

<pre><code>SELECT EXISTS(
 SELECT * FROM information_schema.tables 
 WHERE table_schema = 'db' 
 AND table_name = 'table'
);
</code></pre>

It indicates exactly what you want to do and it actually returns a 'boolean'.

blocks|key|1954782|text|这里的答案有几个问题需要注意：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1954783|1)+INFORMATION_SCHEMA.TABLES不包含临时表。|offset|length|style|CODE|1954784|2)使用任何类型的SHOW查询，例如SHOW+TABLES+LIKE+'test_table'，都会强制从也返回结果集的存储过程中将结果集返回给客户端，这对于在服务器端检查表是否存在来说是不受欢迎的行为。|1954785|3)正如一些用户提到的，您必须小心使用SELECT+1+FROM+test_table+LIMIT+1。|1954786|如果你这样做：|1954787|SET+@table_exists+=+0;
SET+@table_exists+=+(SELECT+1+FROM+test_table+LIMIT+1);|code-block|syntax|javascript|1954788|如果表的行数为零，则不会得到预期的结果。|1954789|下面是一个适用于所有表的存储过程(即使是临时的)。|1954790|它的用法如下：|1954791|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;|1954792|代码：|1954793|/*
++++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+;|1954794|entityMap^0|0|3|P|0|9|4|I|T|0|J|W|0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|16|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|17|8|@$D|18|E|19|F|G]]|9|@]|A|$]]|$1|H|3|I|5|6|7|1A|8|@$D|1B|E|1C|F|G]|$D|1D|E|1E|F|G]]|9|@]|A|$]]|$1|J|3|K|5|6|7|1F|8|@$D|1G|E|1H|F|G]]|9|@]|A|$]]|$1|L|3|M|5|6|7|1I|8|@]|9|@]|A|$]]|$1|N|3|O|5|P|7|1J|8|@]|9|@]|A|$Q|R]]|$1|S|3|T|5|6|7|1K|8|@]|9|@]|A|$]]|$1|U|3|V|5|6|7|1L|8|@]|9|@]|A|$]]|$1|W|3|X|5|6|7|1M|8|@]|9|@]|A|$]]|$1|Y|3|Z|5|P|7|1N|8|@]|9|@]|A|$Q|R]]|$1|10|3|11|5|6|7|1O|8|@]|9|@]|A|$]]|$1|12|3|13|5|P|7|1P|8|@]|9|@]|A|$Q|R]]|$1|14|3|-4|5|6|7|1Q|8|@]|9|@]|A|$]]]|15|$]]

There are several issues to note with the answers here:

1) <code>INFORMATION_SCHEMA.TABLES</code> does not include TEMPORARY tables.

2) Using any type of <code>SHOW</code> query, i.e. <code>SHOW TABLES LIKE 'test_table'</code>, will force the return of a resultset to the client, which is undesired behavior for checking if a table exists server-side, from within a stored procedure that also returns a resultset.

3) As some users mentioned, you have to be careful with how you use <code>SELECT 1 FROM test_table LIMIT 1</code>.

If you do something like:

<pre><code>SET @table_exists = 0;
SET @table_exists = (SELECT 1 FROM test_table LIMIT 1);
</code></pre>

You will not get the expected result if the table has zero rows.

Below is a stored procedure that will work for all tables (even TEMPORARY).

It can be used like:

<pre><code>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;
</code></pre>

The code:

<pre><code>/*
 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 ;
</code></pre>

blocks|key|1954848|text|这是我检查临时表和普通表的“go-to”EXISTS过程。此过程适用于MySQL+5.6版及更高版本。@DEBUG参数是可选的。默认模式是假定的，但可以连接到@s语句中的表。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1954849|drop+procedure+if+exists+`prcDoesTableExist`;
delimiter+#
CREATE+PROCEDURE+`prcDoesTableExist`(IN+pin_Table+varchar(100),+OUT+pout_TableExists+BOOL)
BEGIN
++++DECLARE+`boolTableExists`+TINYINT(1)+DEFAULT+1;
++++DECLARE+CONTINUE+HANDLER+FOR+1243,+SQLSTATE+VALUE+'42S02'+SET+`boolTableExists`+:=+0;
++++++++SET+@s+=+concat('SELECT+null+FROM+`',+pin_Table,+'`+LIMIT+0+INTO+@resultNm');
++++PREPARE+stmt1+FROM+@s;
++++EXECUTE+stmt1;
++++DEALLOCATE+PREPARE+stmt1;
++++set+pout_TableExists+=+`boolTableExists`;+--+Set+output+variable
++++IF+@DEBUG+then
++++++++select+IF(`boolTableExists`
++++++++++++,+CONCAT('TABLE+`',+pin_Table,+'`+exists:+',+pout_TableExists)
++++++++++++,+CONCAT('TABLE+`',+pin_Table,+'`+does+not+exist:+',+pout_TableExists)
++++++++)+as+result;
++++END+IF;
END+#
delimiter+;|code-block|syntax|javascript|1954850|以下是@debug为on的示例call语句：|1954851|set+@DEBUG+=+true;
call+prcDoesTableExist('tempTable',+@tblExists);
select+@tblExists+as+'@tblExists';|1954852|变量@tblExists返回一个布尔值。|1954853|entityMap^0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|Q|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|R|8|@]|9|@]|A|$E|F]]|$1|K|3|L|5|6|7|S|8|@]|9|@]|A|$]]|$1|M|3|-4|5|6|7|T|8|@]|9|@]|A|$]]]|N|$]]

This has been my 'go-to' EXISTS procedure that checks both temp and normal tables. This procedure works in MySQL version 5.6 and above. The @DEBUG parameter is optional. The default schema is assumed, but can be concatenated to the table in the @s statement.

<pre><code>drop procedure if exists `prcDoesTableExist`;
delimiter #
CREATE PROCEDURE `prcDoesTableExist`(IN pin_Table varchar(100), OUT pout_TableExists BOOL)
BEGIN
 DECLARE `boolTableExists` TINYINT(1) DEFAULT 1;
 DECLARE CONTINUE HANDLER FOR 1243, SQLSTATE VALUE '42S02' SET `boolTableExists` := 0;
 SET @s = concat('SELECT null FROM `', pin_Table, '` LIMIT 0 INTO @resultNm');
 PREPARE stmt1 FROM @s;
 EXECUTE stmt1;
 DEALLOCATE PREPARE stmt1;
 set pout_TableExists = `boolTableExists`; -- Set output variable
 IF @DEBUG then
 select IF(`boolTableExists`
 , CONCAT('TABLE `', pin_Table, '` exists: ', pout_TableExists)
 , CONCAT('TABLE `', pin_Table, '` does not exist: ', pout_TableExists)
 ) as result;
 END IF;
END #
delimiter ;
</code></pre>

Here is the example call statement with @debug on:

<pre><code>set @DEBUG = true;
call prcDoesTableExist('tempTable', @tblExists);
select @tblExists as '@tblExists';
</code></pre>

The variable @tblExists returns a boolean.

blocks|key|1741615|text|如果不存在，则此紧凑方法返回1。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1741616|set+@ret+=+0;+
SELECT+1+INTO+@ret+FROM+information_schema.TABLES+
+++++++++WHERE+TABLE_SCHEMA+=+DATABASE()+AND+TABLE_NAME+=+'my_table';+
SELECT+@ret;|code-block|syntax|javascript|1741617|您可以在mysql函数中输入|1741618|DELIMITER+$$
CREATE+FUNCTION+ExistTable+(_tableName+varchar(255))
RETURNS+tinyint(4)
SQL+SECURITY+INVOKER
BEGIN
++DECLARE+_ret+tinyint;
++SET+_ret+=+0;
++SELECT
++++1+INTO+_ret
++FROM+information_schema.TABLES
++WHERE+TABLE_SCHEMA+=+DATABASE()
++AND+TABLE_NAME+=+_tablename+LIMIT+1;
++RETURN+_ret;
END
$$
DELIMITER+;|1741619|并将其称为|1741620|Select+ExistTable('my_table');|1741621|如果存在，则返回1；如果不存在，则返回0。|1741622|entityMap^0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|T|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|U|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|V|8|@]|9|@]|A|$E|F]]|$1|K|3|L|5|6|7|W|8|@]|9|@]|A|$]]|$1|M|3|N|5|D|7|X|8|@]|9|@]|A|$E|F]]|$1|O|3|P|5|6|7|Y|8|@]|9|@]|A|$]]|$1|Q|3|-4|5|6|7|Z|8|@]|9|@]|A|$]]]|R|$]]

This compact method return 1 if exist 0 if not exist. 

<pre><code>set @ret = 0; 
SELECT 1 INTO @ret FROM information_schema.TABLES 
 WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'my_table'; 
SELECT @ret;
</code></pre>

You can put in into a mysql function

<pre><code>DELIMITER $$
CREATE FUNCTION ExistTable (_tableName varchar(255))
RETURNS tinyint(4)
SQL SECURITY INVOKER
BEGIN
 DECLARE _ret tinyint;
 SET _ret = 0;
 SELECT
 1 INTO _ret
 FROM information_schema.TABLES
 WHERE TABLE_SCHEMA = DATABASE()
 AND TABLE_NAME = _tablename LIMIT 1;
 RETURN _ret;
END
$$
DELIMITER ;
</code></pre>

and call it

<pre><code>Select ExistTable('my_table');
</code></pre>

return 1 if exist 0 if not exist.

blocks|key|1741661|text|如果在2019年之后阅读这篇文章，请注意MySQL+5.7添加了一个table_exists过程，该过程将确定表是否存在，包括临时表。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1741662|用法:将@exist设置为'‘、'BASE+TABLE’、'VIEW‘、'TEMPORARY’之一|1741663|CALL+sys.table_exists('db1',+'t3',+@exists);|code-block|syntax|javascript|1741664|参考资料：|1741665|https://dev.mysql.com/doc/refman/5.7/en/sys-table-exists.html|1741666|entityMap|0|LINK|mutability|MUTABLE|url^0|Y|C|0|0|0|0|0|1P|0|0^^$0|@$1|2|3|4|5|6|7|X|8|@$9|Y|A|Z|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|10|8|@]|D|@]|E|$]]|$1|H|3|I|5|J|7|11|8|@]|D|@]|E|$K|L]]|$1|M|3|N|5|6|7|12|8|@]|D|@]|E|$]]|$1|O|3|P|5|6|7|13|8|@]|D|@$9|14|A|15|1|16]]|E|$]]|$1|Q|3|-4|5|6|7|17|8|@]|D|@]|E|$]]]|R|$S|$5|T|U|V|E|$W|P]]]]

If reading this after 2019, note that MySQL 5.7 added a <code>table_exists</code> procedure that will determine if a table exists, including TEMPORARY TABLES.
Usage:
Sets @exist to one of '', 'BASE TABLE', 'VIEW', 'TEMPORARY'
<pre><code>CALL sys.table_exists('db1', 't3', @exists);
</code></pre>
Reference:
<a href="https://dev.mysql.com/doc/refman/5.7/en/sys-table-exists.html" rel="nofollow noreferrer">https://dev.mysql.com/doc/refman/5.7/en/sys-table-exists.html</a>

blocks|key|1955065|text|在创建表之前，建议您检查SQL+Server数据库中是否存在该表。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1955066|USE+[DB_NAME]
GO
IF+OBJECT_ID('table_name',+'U')+IS+NOT+NULL
BEGIN
PRINT+'Table+exists.'
END
ELSE
BEGIN
PRINT+'Table+does+not+exist.'
END|code-block|syntax|javascript|1955067|或者，使用sys.Objects检查SQL+Server中是否存在表。|1955068|USE+[DB_NAME]
GO
IF+EXISTS(SELECT+1+FROM+sys.Objects
WHERE+Object_id+=+OBJECT_ID(N'table_name')
AND+Type+=+N'U')
BEGIN
PRINT+'Table+exists.'
END
ELSE
BEGIN
PRINT+'Table+does+not+exist.'
END|1955069|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|N|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|O|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

Before creating a TABLE, it is always advisable to check whether the table exists in SQL Server database or not.
<pre><code>USE [DB_NAME]
GO
IF OBJECT_ID('table_name', 'U') IS NOT NULL
BEGIN
PRINT 'Table exists.'
END
ELSE
BEGIN
PRINT 'Table does not exist.'
END
</code></pre>
Alternatively
Using the sys.Objects to check whether a table exists in SQL Server or not.
<pre><code>USE [DB_NAME]
GO
IF EXISTS(SELECT 1 FROM sys.Objects
WHERE Object_id = OBJECT_ID(N'table_name')
AND Type = N'U')
BEGIN
PRINT 'Table exists.'
END
ELSE
BEGIN
PRINT 'Table does not exist.'
END
</code></pre>

Is there a way to check if a table exists without selecting and checking values from it?

That is, I know I can go <code>SELECT testcol FROM testtable</code> and check the count of fields returned, but it seems there must be a more direct / elegant way to do it.

Check if MySQL table exists without using "select from" syntax?

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

有没有一种方法可以在不选择和检查表中的值的情况下检查表是否存在？也就是说，我知道我可以使用SELECT testcol FROM testtable来检查返回的字段的数量，但似乎必须有一种更直接、更优雅的方法来完成这项工作。

问不使用select from语法检查MySQL表是否存在？
EN

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问不使用select from语法检查MySQL表是否存在？EN

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问不使用select from语法检查MySQL表是否存在？
EN