MySQL：如果表存在，截断并插入 ELSE 创建
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21801869/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL: IF table exists, truncate and insert ELSE create
提问by John C
Working only with MySQL (I have essentially noPHP knowledge), I need to have a table that's essentially a subset from a much larger table. The source table changes from time to time, losing some entries, gaining other new ones, and values changing for existing ones. I can describe what I want to happen, but can't seem to figure out a syntax of commands to make it work. I also know I can have two separate queries and just run whichever one I need, and I have that worked out, but I'd like to combine them if possible. Here's what I want:
只使用 MySQL（我基本上没有PHP 知识），我需要一个表，它本质上是一个更大表的子集。源表不时更改，丢失一些条目，获得其他新条目，并且现有条目的值发生变化。我可以描述我想要发生的事情，但似乎无法弄清楚使其工作的命令语法。我也知道我可以有两个单独的查询，然后运行我需要的任何一个，我已经解决了，但如果可能的话，我想将它们组合起来。这是我想要的：
IF the subset_table DOES NOT EXIST, create it as [select query], ELSE truncate the subset_table and insert [select query]
如果subset_table不存在，将其创建为[select query]，否则截断subset_table并插入[select query]
Like I said, I know there are other ways to do this - I could drop if exists/create, or I could just have two different sql files to run. I just want to know if I can do this as specified above.
就像我说的，我知道还有其他方法可以做到这一点 - 如果存在/创建，我可以删除，或者我可以只运行两个不同的 sql 文件。我只是想知道我是否可以按照上面的说明执行此操作。
Thoughts?
想法？
回答by Gordon Linoff
You can do this:
你可以这样做：
create table if not exists <tablename> . . .;
truncate table <tablename>;
insert into <tablename>(cols)
select blah blahblah . . .;
You don't need any ifstatements at all.
你根本不需要任何if陈述。
回答by Jeffery Allan
This can also be done through an SP (stored procedure)... makes it more readable and safe
这也可以通过 SP（存储过程）来完成......使其更具可读性和安全性
DELIMITER $$
DROP PROCEDURE IF EXISTS `create_table_sp`$$
CREATE PROCEDURE `create_table_sp`()
BEGIN
IF NOT EXISTS (SELECT 1 FROM information_schema.TABLES WHERE table_name = '<table_name>'
AND table_schema = DATABASE() AND table_type = 'BASE TABLE') THEN
CREATE TABLE <subset_table_name>
AS SELECT * FROM <main_table_name>;
ELSE
TRUNCATE TABLE <subset_table_name>;
INSERT INTO <subset_table_name>
AS SELECT * FROM <main_table_name>;
END IF;
END$$
DELIMITER ;
CALL `create_table_sp`;
DROP PROCEDURE IF EXISTS `create_table_sp`;
There is also another way,
还有一种方式，
- You could pass the table names as arguments to the SP, in this case sub_table_name and main_table_name
- Make the above DML statements to a string using CONCAT()
- Create a prepared statement out of it and execute
- 您可以将表名作为参数传递给 SP，在本例中为 sub_table_name 和 main_table_name
- 使用 CONCAT() 将上述 DML 语句转换为字符串
- 从中创建一个准备好的语句并执行
Hope this helped....
希望这有帮助....
