MySQL“ CREATE TABLE IF NOT EXISTS”-> Error 1050

使用以下命令:

CREATE TABLE IF NOT EXISTS `test`.`t1` (
`col` VARCHAR(16) NOT NULL
) ENGINE=MEMORY;

在 MySQL 查询浏览器中运行两次会得到:

表‘ t1’已经存在错误1050

我认为创建表“ IF NOT EXISTS”不会抛出错误。我漏掉了什么吗，还是这是个窃听器？我正在运行 5.1版本。谢谢。

333779 次浏览
小开
最佳答案

Works fine for me in 5.0.27

I just get a warning (not an error) that the table exists;

小开

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
小开

I have a solution to a problem that may also apply to you. My database was in a state where a DROP TABLE failed because it couldn't find the table... but a CREATE TABLE also failed because MySQL thought the table existed. (This state could easily mess with your IF NOT EXISTS clause).

I eventually found this solution:

sudo mysqladmin flush-tables

For me, without the sudo, I got the following error:

mysqladmin: refresh failed; error: 'Access denied; you need the RELOAD privilege for this operation'

(Running on OS X 10.6)

小开

Create mysql connection with following parameter. "'raise_on_warnings': False". It will ignore the warning. e.g.

config = {'user': 'user','password': 'passwd','host': 'localhost','database': 'db',   'raise_on_warnings': False,}
cnx = mysql.connector.connect(**config)
小开

You can use the following query to create a table to a particular database in 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`)


);
小开

I had a similar Problem as @CraigWalker on debian: My database was in a state where a DROP TABLE failed because it couldn't find the table, but a CREATE TABLE also 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.

I created this state by just copying the whole folder that contained a database with some MyISAM and some InnoDB tables

cp -a /var/lib/mysql/sometable /var/lib/mysql/test

(this is not recommended!)

All InnoDB tables where not visible in the new database test in phpmyadmin.

sudo mysqladmin flush-tables didn't help either.

My solution: I had to delete the new test database with drop database test and copy it with mysqldump instead:

mysqldump somedatabase -u username -p -r export.sql
mysql test -u username -p < export.sql
小开 
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`)
);***
小开

If anyone is getting this error after a Phpmyadmin export, using the custom options and adding the "drop tables" statements cleared this right up.

小开

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 SQL or Oracle.


提交答案