Mysql monthly partitions:
PARTITION s201612 VALUES LESS THAN (TO_DAYS (' 2017-01-01 ')),
PARTITION s201701 VALUES LESS THAN (TO_DAYS (' 2017-02-01 ')),
PARTITION s201702 VALUES LESS THAN (TO_DAYS (' 2017-03-01 ')),
PARTITION s201703 VALUES LESS THAN (TO_DAYS (' 2017-04-01 ')),
PARTITION s201704 VALUES LESS THAN (TO_DAYS (' 2017-05-01 ')),
PARTITION s201705 VALUES LESS THAN (TO_DAYS (' 2017-06-01 ')),
PARTITION s201706 VALUES LESS THAN (TO_DAYS (' 2017-07-01 ')),
PARTITION s201707 VALUES LESS THAN (TO_DAYS (' 2017-08-01 ')),
PARTITION s201708 VALUES LESS THAN (TO_DAYS (' 2017-09-01 ')),
PARTITION s201709 VALUES LESS THAN (TO_DAYS (' 2017-10-01 ')),
PARTITION s201710 VALUES LESS THAN (TO_DAYS (' 2017-11-01 ')),
PARTITION s201711 VALUES LESS THAN (TO_DAYS (' 2017-12-01 ')),
PARTITION s201712 VALUES LESS THAN (TO_DAYS (' 2018-01-01 ')),
PARTITION p2 VALUES LESS THAN MAXVALUE

Through INFORMATION_SCHEMA. Partitions data query partition table rows, check the s201701 line number 65,
SELECT
Partition_name part,
Partition_expression expr,
Partition_description descr,
Table_rows
The FROM
INFORMATION_SCHEMA. Partitions
WHERE
TABLE_SCHEMA=schema ()
AND TABLE_NAME='table_ym';

In fact the data of January 77, delete s2017 this partition, found in January all the data has been deleted, how to make the lookup table data partition number and the actual data is consistent? Ps: just found 12 gap is January 1, 2017 the day's data,

CodePudding user response:

Check mysql official documentation, found INFORMATION_SCHEMA. Partitions TABLE_ROWS fields provide is actually a probably numerical, attach the official documentation

TABLE_ROWS: The number of table rows in The partition.

For partitioned InnoDB tables, the row count is given in the TABLE_ROWS column is only an estimated value, informs in SQL optimization, and may not always be exact.