MySQL删除数据表前三个月数据

testrecord数据库中存储着玩家行为记录，每三个月需要清理掉。

删除数据前的准备

查看testrecord数据库的大小：

MySQL [(none)]> USE information_schema;
Database changed
MySQL [information_schema]> SELECT CONCAT(ROUND(SUM(DATA_LENGTH/1024/1024/1024),2),'GB')
    -> as DATA FROM TABLES WHERE
    -> table_schema='testrecord';
+---------+
| DATA    |
+---------+
| 56.70GB |
+---------+
1 row in set (0.00 sec)

查看testrecord数据库各数据表大小：

MySQL [information_schema]> SELECT
    -> table_schema as '数据库',
    -> table_name as '表名',
    -> table_rows as '记录数',
    -> TRUNCATE(data_length/1024/1024/1024, 2) as '数据容量(GB)',
    -> TRUNCATE(index_length/1024/1024/1024, 2) as '索引容量(GB)'
    -> FROM information_schema.tables
    -> WHERE table_schema='testrecord'
    -> ORDER BY data_length DESC, index_length DESC;
+----------- +-------------------+-----------+----------------+----------------+
| 数据库     | 表名               | 记录数    | 数据容量(GB)    | 索引容量(GB)   |
+----------- +-------------------+----------------------------+----------------+
| testrecord | playeritem        |  71206129 |           9.71 |           4.17 |
| testrecord | dropcord          | 102342337 |           7.92 |           6.22 |
| testrecord | ipcord            |  22256444 |           4.97 |           3.93 |
| testrecord | store             |  16583137 |           2.58 |           1.20 |
| testrecord | teip              |   6887136 |           2.43 |           1.28 |

以上得知：
需要清理数据的前三个数据表分别是：playeritem、dropcord、ipcord

查看创建数据表的SQL语句：

MySQL [(none)]> USE testrecord;
Database changed
MySQL [testrecord]> SHOW CREATE TABLE playeritem \G
*************************** 1. row ********