DB课设的MySQL记录

放出很早以前的记录,不太想修饰了,就这样吧。
——
root用户登录
mysql -u root -p

创建数据库feedback
create database feedback

创建用户并赋feedback数据库所有权限
grant all on feedback.* to ‘dbcourse’@’localhost’ identified by ‘123456’ with grant option;

换dbcourse用户登录
mysql -u dbcourse -p123456

可以用以下命令切换到feedback数据库里面
use feedback
——–
出现的问题:
重复的约束名:
不同表中对某表同一行的外键约束不能用重名,后来我改的约束名规范为[table2]_[table1]_[keyname]_fk

timestamp问题:
问题描述为ERROR 1293 (HY000) at line 11: Incorrect table definition; there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause。
这个我当时是想做一条记录的create time和modified time的,一个设的default current_timestamp一个是default current_timestamp on update timestamp,mysql不允许。
各种资料显示,最好的workaround方法是另一个需要更新的timestamp用trigger实现。

触发器问题:
直接create trigger语句创建触发器时会报错,花了很长时间google发现是delimiter问题,调用语句内部如果有分号需要重设一个delimiter以保证不会让解释器混淆,如下所示:

DELIMITER |
create trigger `setsolved_trigger`
after insert on `solutions`
for each row 
begin
	update mails set mails.solved=1
	where mails.fid=new.fid;
end;
|

DELIMITER ;

另外trigger里面mails.fid,new.fid等还不能加“这种引号,加了会报错…
——–
初始化的sql代码如下:

create table if not exists mails (
	fid int not null auto_increment,
	mcontent text not null default '',
	sndemail varchar(X128) not null,
	rcvtime varchar(128) not null,
	ctime timestamp not null default current_timestamp,
	solved tinyint(5) not null default 0,
	primary key (fid)
) engine=InnoDB default charset=utf8 auto_increment=1;

create table if not exists solutions (
	sid int not null auto_increment,
	fid int not null,
	scontent text not null default '',
	mtime timestamp not null,
	ctime timestamp null,
	constraint mails_solutions_fid_fk foreign key (fid) references mails(fid) on delete cascade on update cascade,
	primary key (sid)
) engine=InnoDB default charset=utf8 auto_increment=1;

create table if not exists users (
	uid int not null auto_increment,
	uname varchar(128) not null default 'Anonymous',
	homepage varchar(128),
	uemail varchar(128) not null default '',
	ctime timestamp not null default current_timestamp,
	primary key (uid)
) engine=InnoDB default charset=utf8 auto_increment=1;

-- solvers 这个表暂时不用了。
-- create table if not exists solvers (
-- 	suid int not null auto_increment,
-- 	sid int not null,
-- 	uid int not null,
-- 	constraint solutions_solvers_sid_fk foreign key (sid) references solutions(sid) on delete cascade on update cascade,
-- 	constraint users_solvers_uid_fk foreign key (uid) references users(uid) on delete cascade on update cascade,
-- 	primary key (suid)
-- ) engine=InnoDB default charset=utf8 auto_increment=1;

create table if not exists comments (
	cid int not null auto_increment,
	fid int not null,
	uid int not null,
	ccontent text not null default '',
	posttime timestamp not null default current_timestamp,
	constraint mails_comments_fid_fk foreign key (fid) references mails(fid) on delete cascade on update cascade,
	constraint users_comments_uid_fk foreign key (uid) references users(uid) on delete cascade on update cascade,
	primary key (cid)
) engine=InnoDB default charset=utf8 auto_increment=1;

create table if not exists recommanders (
	rid int not null auto_increment,
	fid int not null,
	fromuid int not null,
	remail varchar(128) not null,
	constraint users_recommanders_uid_fk foreign key (fromuid) references users(uid) on delete cascade on update cascade,
	constraint mails_recommanders_fid_fk foreign key (fid) references mails(fid) on delete cascade on update cascade,
	primary key (rid)
) engine=InnoDB default charset=utf8 auto_increment=1;

DELIMITER |
create trigger setsolved_trigger
after insert on solutions
for each row 
begin
	update mails set mails.solved=1
	where mails.fid=new.fid;
end;|

DELIMITER |
create trigger setsolmtime_trigger
before insert on solutions
for each row
begin
	set new.ctime=now();
end;
|

DELIMITER ;
Tags : ,

0 thoughts on “DB课设的MySQL记录”

发表评论

电子邮件地址不会被公开。 必填项已用*标注

Click the right image To submit your comment: