范例-生成数据库表大文件脚本
范例-生成数据库表大文件脚本-testlog.sql
代码
testlog.sql
sql
create table testlog (id int auto_increment primary key,name char(10),age int default 20);
delimiter $$
create procedure pro_testlog()
begin
declare i int;
set i = 1;
while i < 100000
do insert into testlog(name,age) values (concat('wang',i),i);
set i = i +1;
end while;
end$$
delimiter ;使用
如下是一个生成大文件表的sql脚本：
testlog.sql
sql
create table testlog (id int auto_increment primary key,name char(10),age int default 20);
delimiter $$
create procedure pro_testlog()
begin
declare i int;
set i = 1;
while i < 100000
do insert into testlog(name,age) values (concat('wang',i),i);
set i = i +1;
end while;
end$$
delimiter ;将这个代码拷贝到自己数据库里：
sql
[root@linux-test ~]#mysql -uroot -pxyy520 test
mysql> create table testlog (id int auto_increment primary key,name char(10),age int default 20);
Query OK, 0 rows affected (0.01 sec)
mysql>
mysql> delimiter $$
mysql>
mysql> create procedure pro_testlog()
-> begin
-> declare i int;
-> set i = 1;
-> while i < 100000
-> do insert into testlog(name,age) values (concat('wang',i),i);
-> set i = i +1;
-> end while;
-> end$$
Query OK, 0 rows affected (0.01 sec)
mysql>
mysql> delimiter ;
mysql>查看当前表信息：
sql
mysql> select *from testlog;
Empty set (0.00 sec)
[root@linux-test ~]#ll -h /data/mysql/test
total 1.1M
……
-rw-rw---- 1 mysql mysql 8.5K Apr 28 07:19 testlog.frm
-rw-rw---- 1 mysql mysql 96K Apr 28 07:19 testlog.ibd #这个文件是96K触发脚本：
sql
mysql> call pro_testlog;
Query OK, 1 row affected (16.77 sec)
[root@linux-test ~]#ll -h /data/mysql/test/testlog.ibd
-rw-rw---- 1 mysql mysql 11M Apr 28 07:21 /data/mysql/test/testlog.ibd
[root@linux-test ~]#ll -h /data/mysql/test/testlog.ibd
-rw-rw---- 1 mysql mysql 12M Apr 28 07:21 /data/mysql/test/testlog.ibd
#可以看到这个testlog.ibd文件已经增大到12M了。此时直接使用delete from testlog; 命令删除表后，那么这个/data/mysql/test/testlog.ibd文件大小是否会变小呢？
sql
mysql> delete from testlog;
Query OK, 99999 rows affected (0.20 sec)
mysql> select *from testlog;
Empty set (0.00 sec)
mysql>
[root@linux-test ~]#ll -h /data/mysql/test/testlog.ibd
-rw-rw---- 1 mysql mysql 12M Apr 28 07:24 /data/mysql/test/testlog.ibd
#可以发现这个文件大小依然没有变化。那么我们可以利用如下命令来清理这种空洞文件：
sql
mysql> OPTIMIZE TABLE testlog;
+--------------+----------+----------+-------------------------------------------------------------------+
| Table | Op | Msg_type | Msg_text |
+--------------+----------+----------+-------------------------------------------------------------------+
| test.testlog | optimize | note | Table does not support optimize, doing recreate + analyze instead |
| test.testlog | optimize | status | OK |
+--------------+----------+----------+-------------------------------------------------------------------+
2 rows in set (0.01 sec)
##再次验证(符合预期)
[root@linux-test ~]#ll -h /data/mysql/test/testlog.ibd
-rw-rw---- 1 mysql mysql 96K Apr 28 07:25 /data/mysql/test/testlog.ibd当然，truncate命令直接是可以会自动缩减数据文件的大小的。
sql
| 199997 | wang99998 | 99998 |
| 199998 | wang99999 | 99999 |
+--------+-----------+-------+
99999 rows in set (0.04 sec)
mysql> truncate table testlog;
Query OK, 0 rows affected (0.01 sec)
[root@linux-test ~]#ll -h /data/mysql/test/testlog.ibd
-rw-rw---- 1 mysql mysql 96K Apr 28 07:26 /data/mysql/test/testlog.ibd位置
链接：https://pan.baidu.com/s/1-c4nXQki7jMJYl2sCrer7Q?pwd=zt1p 提取码：zt1p testlog.sql
FAQ
- 直接调用
sql
MariaDB [hellodb]> call pro_testlog;
Query OK, 99999 rows affected (12.308 sec) #耗费12s
[root@mysql hellodb]# ll -h
-rw-rw----. 1 mysql mysql 999 Jun 13 06:52 testlog.frm
-rw-rw----. 1 mysql mysql 12M Jun 13 07:07 testlog.ibd- 启用事务后，执行会更快些
bash
MariaDB [hellodb]> begin;
Query OK, 0 rows affected (0.000 sec)
MariaDB [hellodb]> call pro_testlog;
Query OK, 99999 rows affected (1.086 sec) #只要1s
MariaDB [hellodb]> commit;
Query OK, 0 rows affected (0.001 sec)
[root@mysql hellodb]# ll -h testlog*
-rw-rw----. 1 mysql mysql 999 Jun 13 07:27 testlog.frm
-rw-rw----. 1 mysql mysql 12M Jun 13 07:28 testlog.ibd