|
使用Navicat导入同学的.sql文件时报错
'USING?BTREE,?CONSTRAINT?`fk1`?FOREIGN?KEY?(`Mid`)?REFERENCES?`member`?(`Mid`)'?at?line?14
在网上找了好久，终于找到原来是数据库版本不一致，出现原因是mysql 5.1和mysql 5.0在处理到索引语句时有所区别。参考以下链接
修改（或者颠倒过来）：
KEY `fk11` (`Mrank`) USING BTREE改为KEY `fk11` USING BTREE(`Mrank`)
报错原文：[Err]?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?'USING?BTREE
)?ENGINE=InnoDB?DEFAULT?CHARSET=utf8;
--?-----------------------'?at?line?7
[Err]?--?----------------------------
--?Table?structure?for?join
--?----------------------------
DROP?TABLE?IF?EXISTS?`join`;
CREATE?TABLE?`join`?(
??`Mrank`?varchar(11)?NOT?NULL?COMMENT?'会员等级',
??`condition`?int(11)?NOT?NULL?COMMENT?'成为会员条件',
??`prepolicy`?double(10,2)?NOT?NULL?COMMENT?'优惠政策',
??`pretime`?varchar(20)?NOT?NULL?COMMENT?'优惠时间',
??PRIMARY?KEY?(`Mrank`),
??KEY?`fk11`?(`Mrank`)?USING?BTREE
)?ENGINE=InnoDB?DEFAULT?CHARSET=utf8;
--?----------------------------
--?Table?structure?for?member
--?----------------------------
DROP?TABLE?IF?EXISTS?`member`;
CREATE?TABLE?`member`?(
??`Mid`?int(11)?NOT?NULL?AUTO_INCREMENT?COMMENT?'会员id',
??`Mrank`?varchar(11)?NOT?NULL?COMMENT?'会员等级',
??`name`?varchar(30)?NOT?NULL,
??`age`?smallint(6)?NOT?NULL,
??`work`?varchar(30)?DEFAULT?NULL?COMMENT?'工作单位',
??`telephone`?varchar(11)?NOT?NULL,
??`piont`?int(11)?NOT?NULL?COMMENT?'会员积分',
??PRIMARY?KEY?(`Mid`),
??KEY?`fk_Member_Join`?(`Mrank`),
??CONSTRAINT?`fk`?FOREIGN?KEY?(`Mrank`)?REFERENCES?`join`?(`Mrank`)
)?ENGINE=InnoDB?AUTO_INCREMENT=8?DEFAULT?CHARSET=utf8;
--?----------------------------
--?Table?structure?for?shopping
--?----------------------------
DROP?TABLE?IF?EXISTS?`shopping`;
[Err]?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?'USING?BTREE,
??CONSTRAINT?`fk1`?FOREIGN?KEY?(`Mid`)?REFERENCES?`member`?(`Mid`)'?at?line?14
[Err]?CREATE?TABLE?`shopping`?(
??`id`?int(11)?NOT?NULL?AUTO_INCREMENT,
??`Oid`?varchar(40)?NOT?NULL?COMMENT?'订单编号',
??`Mid`?int(11)?NOT?NULL,
??`Oname`?varchar(30)?NOT?NULL?COMMENT?'物品名称',
??`type`?varchar(30)?NOT?NULL?COMMENT?'物品分类',
??`quantity`?int(11)?NOT?NULL?COMMENT?'数量',
??`price`?float(2,0)?NOT?NULL?COMMENT?'标价',
??`derate`?float(2,0)?NOT?NULL?COMMENT?'减免',
??`pay`?float(2,0)?NOT?NULL?COMMENT?'应付款',
??`gain`?int(11)?NOT?NULL?COMMENT?'获得积分',
??`time`?timestamp?NOT?NULL?DEFAULT?CURRENT_TIMESTAMP?ON?UPDATE?CURRENT_TIMESTAMP?COMMENT?'购买时间',
??PRIMARY?KEY?(`id`),
??KEY?`f`?(`Mid`)?USING?BTREE,
??CONSTRAINT?`fk1`?FOREIGN?KEY?(`Mid`)?REFERENCES?`member`?(`Mid`)
)?ENGINE=InnoDB?AUTO_INCREMENT=7?DEFAULT?CHARSET=utf8;
--?----------------------------
--?Table?structure?for?user
--?----------------------------
DROP?TABLE?IF?EXISTS?`user`;
CREATE?TABLE?`user`?(
??`name`?varchar(30)?NOT?NULL,
??`pwd`?varchar(30)?NOT?NULL,
??PRIMARY?KEY?(`name`),
??KEY?`fk`?(`name`)?USING?BTREE
)?ENGINE=InnoDB?DEFAULT?CHARSET=utf8;
[Msg]?Finished?-?Unsuccessfully
--------------------------------------------------
参考链接mysql导入数据时报错：USING BTREE ) ENGINE=MyISAM DEFAULT CHARSET=utf8解决方法_amaoatao的博客-CSDN博客
|