MySQL 如果表存在删除表然后创建它，如果它不存在就创建它
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20155989/
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
If table exists drop table then create it, if it does not exist just create it
提问by George
I'm stumped, I don't know how to go about doing this.
我很难过，我不知道该怎么做。
Basically I just want to create a table, but if it exists it needs to be dropped and re-created, not truncated, but if it doesn't exist just create it.
基本上我只想创建一个表，但如果它存在，则需要删除并重新创建，而不是截断，但如果它不存在，则创建它。
Would anyone be able to help?
有人可以帮忙吗？
Thanks, George
谢谢，乔治
回答by G-Nugget
Just put DROP TABLE IF EXISTS `tablename`;before your CREATE TABLEstatement.
放在DROP TABLE IF EXISTS `tablename`;你的CREATE TABLE陈述之前。
That statement drops the table if it exists but will not throw an error if it does not.
如果表存在，该语句将删除该表，但如果不存在则不会抛出错误。
回答by r3mainer
Just use DROP TABLE IF EXISTS:
只需使用DROP TABLE IF EXISTS：
DROP TABLE IF EXISTS `foo`;
CREATE TABLE `foo` ( ... );
Try searching the MySQL documentationfirst if you have any other problems.
如果您有任何其他问题，请先尝试搜索MySQL 文档。
回答by Alex Offshore
Well... Huh. For years nobody mentioned one subtle thing.
嗯……嗯。多年来，没有人提到一件微妙的事情。
Despite DROP TABLE IF EXISTS `bla`; CREATE TABLE `bla` ( ... );seems reasonable, it leads to a situation when old table is already gone and new one has not been yet created: some client may try to access subject table right at this moment.
尽管DROP TABLE IF EXISTS `bla`; CREATE TABLE `bla` ( ... );看起来很合理，但它会导致旧表已经消失而新表尚未创建的情况：此时某些客户端可能会尝试访问主题表。
The better way is to create brand new table and swap it with an old one (table contents are lost):
更好的方法是创建全新的表并与旧表交换（表内容丢失）：
CREATE TABLE `bla__new` (id int); /* if not ok: terminate, report error */
RENAME TABLE `bla__new` to `bla`; /* if ok: terminate, report success */
RENAME TABLE `bla` to `bla__old`, `bla__new` to `bla`;
DROP TABLE IF EXISTS `bla__old`;
- You should check the result of
CREATE ...and do not continue in case of error, because failure means that other thread didn't finish the same script: either because it crashed in the middle or just didn't finish yet -- it's a good idea to inspect things by yourself. - Then, you should check the result of first
RENAME ...and do not continue in case of success: whole operation is successfully completed; even more, running nextRENAME ...can (and will) be unsafe if another thread has already started same sequence (it's better to cover this case than not to cover, see locking note below). - Second
RENAME ...atomically replaces table definition, refer to MySQL manualfor details. - At last,
DROP ...just cleans up the old table, obviously.
- 您应该检查 的结果
CREATE ...并且在出现错误时不要继续，因为失败意味着其他线程没有完成相同的脚本：要么是因为它在中间崩溃了，要么只是还没有完成——这是一个好主意自己检查东西。 - 然后，您应该检查 first 的结果，如果成功
RENAME ...则不要继续：整个操作已成功完成；更重要的是，如果另一个线程已经启动了相同的序列，那么运行 next可能（并且将会）是不安全的（覆盖这种情况比不覆盖更好，请参阅下面的锁定说明）。RENAME ... - 第二个
RENAME ...原子地替换表定义，详见 MySQL手册。 - 最后
DROP ...，显然只是清理了旧桌子。
Wrapping all statements with something like SELECT GET_LOCK('__upgrade', -1); ... DO RELEASE_LOCK('__upgrade');allows to just invoke all statements sequentially without error checking, but I don't think it's a good idea: complexity increases and locking functions in MySQL aren't safe for statement-based replication.
用类似的东西包装所有语句SELECT GET_LOCK('__upgrade', -1); ... DO RELEASE_LOCK('__upgrade');允许只按顺序调用所有语句而不进行错误检查，但我认为这不是一个好主意：MySQL 中的复杂性增加和锁定功能对于基于语句的复制是不安全的。
If the table data should survive table definition upgrade... For general case it's far more complex story about comparing table definitions to find out differences and produce proper ALTER ...statement, which is not always possible automatically, e.g. when columns are renamed.
如果表数据应该在表定义升级后仍然存在...对于一般情况，比较表定义以找出差异并生成正确的ALTER ...语句要复杂得多，这并不总是自动实现的，例如当列被重命名时。
Side note 1:You can deal with views using the same approach, in this case CREATE/DROP TABLEmerely transforms to CREATE/DROP VIEWwhile RENAME TABLEremains unchanged. In fact you can even turn table into view and vice versa.
旁注 1：您可以使用相同的方法处理视图，在这种情况下CREATE/DROP TABLE仅转换为CREATE/DROP VIEWwhileRENAME TABLE保持不变。事实上，您甚至可以将表格变成视图，反之亦然。
CREATE VIEW `foo__new` as ...; /* if not ok: terminate, report error */
RENAME TABLE `foo__new` to `foo`; /* if ok: terminate, report success */
RENAME TABLE `foo` to `foo__old`, `foo__new` to `foo`;
DROP VIEW IF EXISTS `foo__old`;
Side note 2:MariaDB users should be happy with CREATE OR REPLACE TABLE/VIEW, which already cares about subject problem and it's fine points.
旁注 2：MariaDB 用户应该对 感到满意CREATE OR REPLACE TABLE/VIEW，它已经关心主题问题，这是很好的点。
回答by sirskoy
I needed to drop a table and re-create with a data from a view. I was creating a table out of a view and this is what I did:
我需要删除一个表并使用视图中的数据重新创建。我正在从视图中创建一个表，这就是我所做的：
DROP TABLE <table_name>;
CREATE TABLE <table_name> AS SELECT * FROM <view>;
The above worked for me using MySQL MariaDb.
以上使用 MySQL MariaDb 对我有用。
