1. 创建一张测试表:
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
`id` bigint(20) PRIMARY key not null AUTO_INCREMENT,
`username` varchar(50) DEFAULT NULL,
`email` varchar(30) DEFAULT NULL,
`password` varchar(32) DEFAULT NULL,
`status` tinyint(1) NULL DEFAULT 0
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
2. 使用存储过程插入1千万条数据:
create procedure myproc()
begin
declare num int;
set num=1;
while num <= 10000000 do
insert into user(username,email,password) values(CONCAT('username_',num), CONCAT(num ,'@qq.com'), MD5(num));
set num=num+1;
end while;
end
3.执行统计查询:注意这里使用了EXPLAIN
EXPLAIN select count(1) as total from user;
4.获取查询字段:rows 总行数,实际上,我的表里有8千万多条数据