MySQL“如果不存在则创建表”-> 错误 1050
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1650946/
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 "CREATE TABLE IF NOT EXISTS" -> Error 1050
提问by user199559
Using the command:
使用命令：
CREATE TABLE IF NOT EXISTS `test`.`t1` (
`col` VARCHAR(16) NOT NULL
) ENGINE=MEMORY;
Running this twice in the MySQL Query Browser results in:
在 MySQL 查询浏览器中运行两次会导致：
Table 't1' already exists Error 1050
表 't1' 已经存在错误 1050
I would have thought that creating the table "IF NOT EXISTS" would not throw errors. Am I missing something or is this a bug? I am running version 5.1. Thanks.
我原以为创建表“IF NOT EXISTS”不会引发错误。我错过了什么还是这是一个错误？我正在运行5.1版。谢谢。
采纳答案by Eli
Works fine for me in 5.0.27
在 5.0.27 对我来说很好用
I just get a warning (not an error) that the table exists;
我只是收到表存在的警告（不是错误）；
回答by gdt
As already stated, it's a warning not an error, but (if like me) you want things to run without warnings, you can disable that warning, then re-enable it again when you're done.
如前所述，这是一个警告而不是错误，但是（如果像我一样）您希望事情在没有警告的情况下运行，您可以禁用该警告，然后在完成后再次重新启用它。
SET sql_notes = 0; -- Temporarily disable the "Table already exists" warning
CREATE TABLE IF NOT EXISTS ...
SET sql_notes = 1; -- And then re-enable the warning again
回答by Sachin Parse
You can use the following query to create a table to a particular database in MySql.
您可以使用以下查询为 MySql 中的特定数据库创建表。
create database if not exists `test`;
USE `test`;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
/*Table structure for table `test` */
CREATE TABLE IF NOT EXISTS `tblsample` (
`id` int(11) NOT NULL auto_increment,
`recid` int(11) NOT NULL default '0',
`cvfilename` varchar(250) NOT NULL default '',
`cvpagenumber` int(11) NULL,
`cilineno` int(11) NULL,
`batchname` varchar(100) NOT NULL default '',
`type` varchar(20) NOT NULL default '',
`data` varchar(100) NOT NULL default '',
PRIMARY KEY (`id`)
);
回答by Balkishan
create database if not exists `test`;
USE `test`;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
/*Table structure for table `test` */
***CREATE TABLE IF NOT EXISTS `tblsample` (
`id` int(11) NOT NULL auto_increment,
`recid` int(11) NOT NULL default '0',
`cvfilename` varchar(250) NOT NULL default '',
`cvpagenumber` int(11) NULL,
`cilineno` int(11) NULL,
`batchname` varchar(100) NOT NULL default '',
`type` varchar(20) NOT NULL default '',
`data` varchar(100) NOT NULL default '',
PRIMARY KEY (`id`)
);***
回答by Craig Walker
I have a solution to a problem that may also apply to you. My database was in a state where a DROP TABLEfailed because it couldn't find the table... but a CREATE TABLEalso failed because MySQL thought the table existed. (This state could easily mess with your IF NOT EXISTS clause).
我有一个可能也适用于您的问题的解决方案。我的数据库处于DROP TABLE失败状态，因为它找不到表……但CREATE TABLE也失败了，因为 MySQL 认为该表存在。（此状态很容易与您的 IF NOT EXISTS 子句混淆）。
I eventually found this solution:
我最终找到了这个解决方案：
sudo mysqladmin flush-tables
For me, without the sudo, I got the following error:
对我来说，没有sudo，我收到以下错误：
mysqladmin: refresh failed; error: 'Access denied; you need the RELOAD privilege for this operation'
(Running on OS X 10.6)
（在 OS X 10.6 上运行）
回答by Vinay
Create mysql connection with following parameter. "'raise_on_warnings': False". It will ignore the warning. e.g.
使用以下参数创建 mysql 连接。“'raise_on_warnings'：错误”。它将忽略警告。例如
config = {'user': 'user','password': 'passwd','host': 'localhost','database': 'db', 'raise_on_warnings': False,}
cnx = mysql.connector.connect(**config)
回答by nizz0k
If anyone is getting this error after a Phpmyadmin export, using the the custom options and adding the "drop tables" statements cleared this right up.
如果有人在 Phpmyadmin 导出后收到此错误，请使用自定义选项并添加“删除表”语句将其清除。
回答by Kunal Vohra
Well there are lot of answeres already provided and lot are making sense too.
好吧，已经提供了很多答案，而且很多也很有意义。
Some mentioned it is just warning and some giving a temp way to disable warnings. All that will work but add risk when number of transactions in your DB is high.
有些人提到它只是警告，有些人提供了一种禁用警告的临时方法。当您的数据库中的事务数量很高时，所有这些都会起作用，但会增加风险。
I came across similar situation today and here is the query I came up with...
我今天遇到了类似的情况，这是我想出的查询...
declare
begin
execute immediate '
create table "TBL" ("ID" number not null)';
exception when others then
if SQLCODE = -955 then null; else raise; end if;
end;
/
This is simple, if exception come while running query it will be suppressed. and you can use same for SQLor Oracle.
这很简单，如果在运行查询时出现异常，它将被抑制。并且您可以将相同的用于SQLor Oracle。
回答by rubo77
I had a similar Problem as @CraigWalker on debian: My database was in a state where a DROP TABLEfailed because it couldn't find the table, but a CREATE TABLEalso failed because MySQL thought the table still existed. So the broken table still existed somewhere although it wasn't there when I looked in phpmyadmin.
我在 debian 上遇到了与 @CraigWalker 类似的问题：我的数据库处于DROP TABLE失败状态，因为它找不到表，但CREATE TABLE也失败了，因为 MySQL 认为该表仍然存在。因此，尽管在我查看 phpmyadmin 时它并不存在，但损坏的桌子仍然存在于某处。
I created this state by just copying the whole folder that contained a database with some MyISAMand some InnoDBtables
我通过复制包含MyISAM一些InnoDB表的数据库的整个文件夹来创建此状态
cp -a /var/lib/mysql/sometable /var/lib/mysql/test
(this is not recommended!)
（不推荐这样做！）
All InnoDB tables where not visible in the new database testin phpmyadmin.
所有 InnoDB 表test在 phpmyadmin的新数据库中不可见。
sudo mysqladmin flush-tablesdidn't help either.
sudo mysqladmin flush-tables也没有帮助。
My solution:I had to delete the new test database with drop database testand copy it with mysqldumpinstead:
我的解决方案：我不得不删除新的测试数据库并用drop database test它来mysqldump代替：
mysqldump somedatabase -u username -p -r export.sql
mysql test -u username -p < export.sql
