今天无意中发现这个问题，以前用yii提供的例子的mysql导出在导入出错了

International Chinese
1

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'TABLE posttag

ADD CONSTRAINT FK_post FOREIGN KEY (postId) REFERENCES `p’ at line 3

这是啥原因？？

2

你清空数据库再导入吗？

3

是的,清空了在导入的！

需要我把数据库存贴出来？(6个表仅几行数据)

4

你的MySQL是哪个版本?

5

MySQL client version: 5.0.22

是否低了？

6

这是什么原因所造成的?

7

你可以先建索引 然后再建外键就可以了

8

己经建好的,直接导出载导入就会出错了！

9

– phpMyAdmin SQL Dump

– version 2.9.0

– http://www.phpmyadmin.net

–

– 主机: localhost

– 生成日期: 2010 年 04 月 19 日 22:05

– 服务器版本: 5.0.24

– PHP 版本: 5.2.0

–

– 数据库: yii

–

–

– 表的结构 comment

–

CREATE TABLE comment (

id int(11) NOT NULL auto_increment,

content text collate utf8_unicode_ci NOT NULL,

contentDisplay text collate utf8_unicode_ci,

status int(11) NOT NULL,

createTime int(11) default NULL,

author varchar(128) collate utf8_unicode_ci NOT NULL,

email varchar(128) collate utf8_unicode_ci NOT NULL,

url varchar(128) collate utf8_unicode_ci default NULL,

postId int(11) NOT NULL,

PRIMARY KEY (id),

KEY FK_comment_post (postId)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;

–

– 导出表中的数据 comment

–

–

– 表的结构 post

–

CREATE TABLE post (

id int(11) NOT NULL auto_increment,

title varchar(128) collate utf8_unicode_ci NOT NULL,

content text collate utf8_unicode_ci NOT NULL,

contentDisplay text collate utf8_unicode_ci,

tags text collate utf8_unicode_ci,

status int(11) NOT NULL,

createTime int(11) default NULL,

updateTime int(11) default NULL,

commentCount int(11) default ‘0’,

authorId int(11) NOT NULL,

PRIMARY KEY (id),

KEY FK_post_author (authorId)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=2 ;

–

– 导出表中的数据 post

–

INSERT INTO post (id, title, content, contentDisplay, tags, status, createTime, updateTime, commentCount, authorId) VALUES

(1, ‘Welcome to Yii Blog’, ‘This blog system is developed using Yii. It is meant to demonstrate how to use Yii to build a complete real-world application. Complete source code may be found in the Yii releases.\r\n\r\nFeel free to try this system by writing new posts and posting comments.’, ‘<p>This blog system is developed using Yii. It is meant to demonstrate how to use Yii to build a complete real-world application. Complete source code may be found in the Yii releases.</p>\r\n\r\n<p>Feel free to try this system by writing new posts and posting comments.</p>’, ‘yii, blog’, 1, 1230952187, 1230952187, 0, 1);

–

– 表的结构 posttag

–

CREATE TABLE posttag (

postId int(11) NOT NULL,

tagId int(11) NOT NULL,

PRIMARY KEY (postId,tagId),

KEY FK_tag (tagId)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

–

– 导出表中的数据 posttag

–

INSERT INTO posttag (postId, tagId) VALUES

(1, 1),

(1, 2);

–

– 表的结构 tag

–

CREATE TABLE tag (

id int(11) NOT NULL auto_increment,

name varchar(128) collate utf8_unicode_ci NOT NULL,

PRIMARY KEY (id)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=3 ;

–

– 导出表中的数据 tag

–

INSERT INTO tag (id, name) VALUES

(1, ‘yii’),

(2, ‘blog’);

–

– 表的结构 user

–

CREATE TABLE user (

id int(11) NOT NULL auto_increment,

username varchar(128) collate utf8_unicode_ci NOT NULL,

password varchar(128) collate utf8_unicode_ci NOT NULL,

email varchar(128) collate utf8_unicode_ci NOT NULL,

profile text collate utf8_unicode_ci,

PRIMARY KEY (id)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=2 ;

–

– 导出表中的数据 user

–

INSERT INTO user (id, username, password, email, profile) VALUES

(1, ‘demo’, ‘fe01ce2a7fbac8fafaed7c982a04e229’, ‘webmaster@example.com’, NULL);

–

– 表的结构 yiilog

–

CREATE TABLE yiilog (

id int(11) NOT NULL auto_increment,

level varchar(128) collate utf8_unicode_ci default NULL,

category varchar(128) collate utf8_unicode_ci default NULL,

logtime int(11) default NULL,

message text collate utf8_unicode_ci,

PRIMARY KEY (id)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=2 ;

–

– 导出表中的数据 yiilog

–

INSERT INTO yiilog (id, level, category, logtime, message) VALUES

(1, ‘error’, ‘system.db.CDbCommand’, 1271485345, ‘Error in executing SQL: DELETE FROM YiiLog WHERE 0=1’);

–

– 限制导出的表

–

–

– 限制表 comment

–

ALTER TABLE comment

ADD CONSTRAINT FK_comment_post FOREIGN KEY (postId) REFERENCES post (id) ON DELETE CASCADE;

–

– 限制表 post

–

ALTER TABLE post

ADD CONSTRAINT FK_post_author FOREIGN KEY (authorId) REFERENCES user (id) ON DELETE CASCADE;

–

– 限制表 posttag

–

ALTER TABLE posttag

ADD CONSTRAINT FK_post FOREIGN KEY (postId) REFERENCES post (id) ON DELETE CASCADE,

ALTER TABLE posttag

ADD CONSTRAINT FK_post FOREIGN KEY (postId) REFERENCES post (id) ON DELETE CASCADE, ADD CONSTRAINT FK_tag FOREIGN KEY (tagId) REFERENCES tag (id) ON DELETE CASCADE;

//导出就是这样子 在导入就出错了！

10

我也遇到了同样的问题，这是怎么回事？求高手指点！