范例-生成数据库表大文件脚本

 1create table testlog (id int auto_increment primary key,name char(10),age int default 20);
 2
 3delimiter $$
 4
 5create procedure  pro_testlog() 
 6begin  
 7declare i int;
 8set i = 1; 
 9while i < 100000 
10do  insert into testlog(name,age) values (concat('wang',i),i); 
11set i = i +1; 
12end while; 
13end$$
14
15delimiter ;

 1create table testlog (id int auto_increment primary key,name char(10),age int default 20);
 2
 3delimiter $$
 4
 5create procedure  pro_testlog() 
 6begin  
 7declare i int;
 8set i = 1; 
 9while i < 100000 
10do  insert into testlog(name,age) values (concat('wang',i),i); 
11set i = i +1; 
12end while; 
13end$$
14
15delimiter ;

 1[root@linux-test ~]#mysql -uroot -pxyy520 test
 2
 3mysql> create table testlog (id int auto_increment primary key,name char(10),age int default 20);
 4Query OK, 0 rows affected (0.01 sec)
 5
 6mysql> 
 7mysql> delimiter $$
 8mysql> 
 9mysql> create procedure  pro_testlog() 
10    -> begin  
11    -> declare i int;
12    -> set i = 1; 
13    -> while i < 100000 
14    -> do  insert into testlog(name,age) values (concat('wang',i),i); 
15    -> set i = i +1; 
16    -> end while; 
17    -> end$$
18Query OK, 0 rows affected (0.01 sec)
19
20mysql> 
21mysql> delimiter ;
22mysql> 

1mysql> select *from testlog;
2Empty set (0.00 sec)
3
4[root@linux-test ~]#ll -h /data/mysql/test
5total 1.1M
6……
7-rw-rw---- 1 mysql mysql 8.5K Apr 28 07:19 testlog.frm
8-rw-rw---- 1 mysql mysql  96K Apr 28 07:19 testlog.ibd #这个文件是96K

1mysql> call pro_testlog;
2Query OK, 1 row affected (16.77 sec)
3
4[root@linux-test ~]#ll -h /data/mysql/test/testlog.ibd
5-rw-rw---- 1 mysql mysql 11M Apr 28 07:21 /data/mysql/test/testlog.ibd
6[root@linux-test ~]#ll -h /data/mysql/test/testlog.ibd
7-rw-rw---- 1 mysql mysql 12M Apr 28 07:21 /data/mysql/test/testlog.ibd
8#可以看到这个testlog.ibd文件已经增大到12M了。

 1mysql> delete from testlog;
 2Query OK, 99999 rows affected (0.20 sec)
 3
 4mysql> select *from testlog;
 5Empty set (0.00 sec)
 6
 7mysql> 
 8
 9
10[root@linux-test ~]#ll -h /data/mysql/test/testlog.ibd
11-rw-rw---- 1 mysql mysql 12M Apr 28 07:24 /data/mysql/test/testlog.ibd
12#可以发现这个文件大小依然没有变化。

 1mysql> OPTIMIZE TABLE testlog;
 2+--------------+----------+----------+-------------------------------------------------------------------+
 3| Table        | Op       | Msg_type | Msg_text                                                          |
 4+--------------+----------+----------+-------------------------------------------------------------------+
 5| test.testlog | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
 6| test.testlog | optimize | status   | OK                                                                |
 7+--------------+----------+----------+-------------------------------------------------------------------+
 82 rows in set (0.01 sec)
 9
10##再次验证(符合预期)
11[root@linux-test ~]#ll -h /data/mysql/test/testlog.ibd
12-rw-rw---- 1 mysql mysql 96K Apr 28 07:25 /data/mysql/test/testlog.ibd

 1| 199997 | wang99998 | 99998 |
 2| 199998 | wang99999 | 99999 |
 3+--------+-----------+-------+
 499999 rows in set (0.04 sec)
 5
 6mysql> truncate table testlog;
 7Query OK, 0 rows affected (0.01 sec)
 8
 9[root@linux-test ~]#ll -h /data/mysql/test/testlog.ibd
10-rw-rw---- 1 mysql mysql 96K Apr 28 07:26 /data/mysql/test/testlog.ibd

1MariaDB [hellodb]> call pro_testlog;
2Query OK, 99999 rows affected (12.308 sec) #耗费12s
3
4
5[root@mysql hellodb]# ll -h
6-rw-rw----. 1 mysql mysql  999 Jun 13 06:52 testlog.frm
7-rw-rw----. 1 mysql mysql  12M Jun 13 07:07 testlog.ibd

 1MariaDB [hellodb]> begin;
 2Query OK, 0 rows affected (0.000 sec)
 3
 4MariaDB [hellodb]> call pro_testlog;
 5Query OK, 99999 rows affected (1.086 sec) #只要1s
 6
 7MariaDB [hellodb]> commit;
 8Query OK, 0 rows affected (0.001 sec)
 9
10
11[root@mysql hellodb]# ll -h testlog*
12-rw-rw----. 1 mysql mysql 999 Jun 13 07:27 testlog.frm
13-rw-rw----. 1 mysql mysql 12M Jun 13 07:28 testlog.ibd