delimiter $$ createfunction addRowToMillion(x int) returnsint begin declare i intdefault1; declare sum intdefault0; pjwhile:while i < x do insertintouser( phone, passwd, nickname, gender, home, status, birth, body_height, body_weight ) select phone, passwd, nickname, gender, home, status, birth, body_height, body_weight fromuser; set i = i +1; set sum = i; end while; return sum; end $$ delimiter ;
+-------------+-----------+------+-----+-------------------+-----------------------------+ | Field | Type |Null| Key |Default| Extra | +-------------+-----------+------+-----+-------------------+-----------------------------+ | id |int(11) |NO| PRI |NULL| auto_increment | | owner_id |int(11) |NO| PRI |NULL|| | content | text |NO||NULL|| | create_time |timestamp|NO||CURRENT_TIMESTAMP|| | update_time | datetime | YES ||CURRENT_TIMESTAMP|onupdateCURRENT_TIMESTAMP| +-------------+-----------+------+-----+-------------------+-----------------------------+
m_comment表,如下所示:
1 2 3 4 5 6 7 8 9 10
+-------------+-----------+------+-----+-------------------+-----------------------------+ | Field | Type |Null| Key |Default| Extra | +-------------+-----------+------+-----+-------------------+-----------------------------+ | id |int(11) |NO| PRI |NULL| auto_increment | | owner_id |int(11) |NO| PRI |NULL|| | artcle_id |int(11) |NO| PRI |NULL|| | content | text |NO||NULL|| | create_time |timestamp|NO||CURRENT_TIMESTAMP|| | update_time | datetime | YES ||CURRENT_TIMESTAMP|onupdateCURRENT_TIMESTAMP| +-------------+-----------+------+-----+-------------------+-----------------------------+
建立普通索引,使得加快查询某一用户所发表的所有文章数据速度:
1
altertable m_artcle add index(owner_id)
获取某一用户发布的所有文章,按时间降序排序
1 2 3
select*from m_artcle where owner_id =4 orderby create_time desc
获取某一用户被发表评论数最多的文章:
1 2 3 4 5
select id, content from m_artcle where id = (select artcle_id from m_comment groupby artcle_id orderbycount(artcle_id) desc limit 1 )
当注销一个用户后,也要把其发布的文章和评论都一一删除,编写一个触发器:
1 2 3 4 5 6 7 8
delimiter ;; createtrigger `tri_delete_user` after deleteon `user` foreachrow begin deletefrom m_artcle where owner_id = old.id; deletefrom m_comment where owner_id = old.id; end ;; DELIMITER ;
DELIMITER ;; create event `event_artcleCount_second` on schedule every10second starts now() on completion preserve enable do BEGIN insertinto c_artcle(artcle_count) selectcount(*) from _artcle where unix_timestamp(now()) - unix_timestamp(create_time) <10; END ;; DELIMITER ;