MySql - 如果不存在则创建表，否则截断？

MySql - Create Table If Not Exists Else Truncate?

提问by shmuel613

Here is the updated question:

这是更新的问题：

the current query is doing something like:

当前查询正在执行以下操作：

$sql1 = "TRUNCATE TABLE fubar";
$sql2 = "CREATE TEMPORARY TABLE IF NOT EXISTS fubar SELECT id, name FROM barfu";

The first time the method containing this is run, it generates an error message on the truncate since the table doesn't exist yet.

第一次运行包含它的方法时，它会在截断时生成一条错误消息，因为该表尚不存在。

Is my only option to do the CREATE TABLE, run the TRUNCATE TABLE, and then fill the table? (3 separate queries)

我唯一的选择是执行CREATE TABLE，运行TRUNCATE TABLE，然后填表吗？（3 个单独的查询）

original question was:

原来的问题是：

I've been having a hard time trying to figure out if the following is possible in MySql without having to write block sql:

我一直很难弄清楚在 MySql 中是否可以执行以下操作，而不必编写块 sql：

CREATE TABLE fubar IF NOT EXISTS ELSE TRUNCATE TABLE fubar

If I run truncate separately before the create table, and the table doesn't exist, then I get an error message. I'm trying to eliminate that error message without having to add any more queries.

如果我在创建表之前单独运行 truncate，并且该表不存在，那么我会收到一条错误消息。我正在尝试消除该错误消息，而不必添加更多查询。

This code will be executed using PHP.

此代码将使用 PHP 执行。

回答by mdahlman

shmuel613, it would be better to update your original question rather than replying. It's best if there's a single place containing the complete question rather than having it spread out in a discussion.

shmuel613，最好更新您的原始问题而不是回复。最好有一个地方包含完整的问题，而不是在讨论中展开。

Ben's answer is reasonable, except he seems to have a 'not' where he doesn't want one. Dropping the table only if it doesn'texist isn't quite right.

本的回答是合理的，除了他似乎有一个“不”，他不想要一个。仅在表不存在时才删除表不太正确。

You will indeed need multiple statements. Either conditionally create then populate:

您确实需要多个语句。要么有条件地创建然后填充：

1. CREATE TEMPORARY TABLE IF NOT EXISTS fubar ( id int, name varchar(80) )
2. TRUNCATE TABLE fubar
3. INSERT INTO fubar SELECT * FROM barfu

1. 如果不存在，则创建临时表 fubar ( id int, name varchar(80) )
2. TRUNCATE TABLE fubar
3. INSERT INTO fubar SELECT * FROM barfu

or just drop and recreate

或者只是放下并重新创建

1. DROP TABLE IF EXISTS fubar
2. CREATE TEMPORARY TABLE fubar SELECT id, name FROM barfu

1. 删除表如果存在 fubar
2. 创建临时表 fubar SELECT id, name FROM barfu

With pure SQL those are your two real classes of solutions. I like the second better.

使用纯 SQL，这些是您真正的两类解决方案。我更喜欢第二个。

(With a stored procedure you could reduce it to a single statement. Something like: TruncateAndPopulate(fubar) But by the time you write the code for TruncateAndPopulate() you'll spend more time than just using the SQL above.)

（使用存储过程，您可以将其简化为单个语句。例如： TruncateAndPopulate(fubar) 但是当您为 TruncateAndPopulate() 编写代码时，您将花费更多的时间，而不仅仅是使用上面的 SQL。）

回答by Mark Janssen

You could do the truncate after the 'create if not exists'. That way it will always exist... and always be empty at that point.

您可以在“如果不存在则创建”之后进行截断。这样它就会一直存在……而且在那个时候总是空的。

CREATE TABLE fubar IF NOT EXISTS
TRUNCATE TABLE fubar

回答by Mark Janssen

execute any query if table exists.

如果表存在，则执行任何查询。

Usage: call Edit_table(database-name,table-name,query-string);

用法： call Edit_table(database-name,table-name,query-string);

• Procedure will check for existence of table-name under database-name and will execute query-string if it exists. Following is the stored procedure:

• 过程将检查数据库名下的表名是否存在，如果存在则执行查询字符串。下面是存储过程：

DELIMITER $$

DROP PROCEDURE IF EXISTS `Edit_table` $$
CREATE PROCEDURE `Edit_table` (in_db_nm varchar(20), in_tbl_nm varchar(20), in_your_query varchar(200))
DETERMINISTIC
BEGIN

DECLARE var_table_count INT;

select count(*) INTO @var_table_count from information_schema.TABLES where TABLE_NAME=in_tbl_nm and TABLE_SCHEMA=in_db_nm;
IF (@var_table_count > 0) THEN
  SET @in_your_query = in_your_query;
  #SELECT @in_your_query;
  PREPARE my_query FROM @in_your_query;
  EXECUTE my_query;

ELSE
  select "Table Not Found";
END IF;

END $$
DELIMITER ;

DELIMITER $$

DROP PROCEDURE IF EXISTS `Edit_table` $$
CREATE PROCEDURE `Edit_table` (in_db_nm varchar(20), in_tbl_nm varchar(20), in_your_query varchar(200))
DETERMINISTIC
BEGIN

DECLARE var_table_count INT;

select count(*) INTO @var_table_count from information_schema.TABLES where TABLE_NAME=in_tbl_nm and TABLE_SCHEMA=in_db_nm;
IF (@var_table_count > 0) THEN
  SET @in_your_query = in_your_query;
  #SELECT @in_your_query;
  PREPARE my_query FROM @in_your_query;
  EXECUTE my_query;

ELSE
  select "Table Not Found";
END IF;

END $$
DELIMITER ;

More on Mysql

更多关于 Mysql

回答by Ben

how about:

怎么样：

DROP TABLE IF EXISTS fubar;
CREATE TABLE fubar;

Or did you mean you just want to do it with a single query?

或者你的意思是你只想用一个查询来完成？

回答by shmuel613

OK then, not bad. To be more specific, the current query is doing something like:

好吧，还不错。更具体地说，当前查询正在执行以下操作：

$sql1 = "TRUNCATE TABLE fubar";
$sql2 = "CREATE TEMPORARY TABLE IF NOT EXISTS fubar SELECT id, name FROM barfu";

The first time the method containing this is run, it generates an error message on the truncate since the table doesn't exist yet.

第一次运行包含它的方法时，它会在截断时生成一条错误消息，因为该表尚不存在。

Is my only option to do the "CREATE TABLE", run the "TRUNCATE TABLE", and then fill the table? (3 separate queries)

我唯一的选择是执行“创建表”，运行“截断表”，然后填充表吗？（3 个单独的查询）

PS - thanks for responding so quickly!

PS - 感谢您这么快回复！

回答by Lastnico

If you're using PHP, use mysql_list_tablesto check that the table exists before TRUNCATE it.

如果您使用 PHP，请在 TRUNCATE 之前使用mysql_list_tables检查该表是否存在。