-- 參考敝人之前的資料
https://ithelp.ithome.com.tw/articles/10101484

-- 或直接看這次類似的

CREATE TABLE innodb1(
id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
data CHAR(100) NOT NULL) ENGINE=InnoDB;

CREATE TABLE innodb2(
id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
data CHAR(100) NOT NULL) ENGINE=InnoDB ROW_FORMAT=COMPRESSED;

CREATE TABLE myisam1(
id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
data CHAR(100) NOT NULL) ENGINE=MyISAM;

-- 載入資料
LOAD DATA INFILE '/usr/share/dict/words' INTO TABLE innodb1(data);
LOAD DATA INFILE '/usr/share/dict/words' INTO TABLE innodb2(data);
LOAD DATA INFILE '/usr/share/dict/words' INTO TABLE myisam1(data);

----
(miku1) [miku1]> LOAD DATA INFILE '/usr/share/dict/words' INTO TABLE innodb1(data);
Query OK, 235886 rows affected (3.09 sec)
Records: 235886  Deleted: 0  Skipped: 0  Warnings: 0

(miku1) [miku1]> LOAD DATA INFILE '/usr/share/dict/words' INTO TABLE innodb2(data);
Query OK, 235886 rows affected (4.21 sec)
Records: 235886  Deleted: 0  Skipped: 0  Warnings: 0

(miku1) [miku1]> LOAD DATA INFILE '/usr/share/dict/words' INTO TABLE myisam1(data);
Query OK, 235886 rows affected (3.27 sec)
Records: 235886  Deleted: 0  Skipped: 0  Warnings: 0
----
-- 察看空間
SELECT table_name, table_rows, row_format,
        data_length / 1024 AS 'data(K)',
        index_length / 1024 AS 'index(K)'
FROM INFORMATION_SCHEMA.TABLES
WHERE table_name IN ('innodb1', 'innodb2', 'myisam1');

+------------+------------+------------+------------+-----------+
| TABLE_NAME | TABLE_ROWS | ROW_FORMAT | data(K)    | index(K)  |
+------------+------------+------------+------------+-----------+
| innodb1    |     234118 | Dynamic    | 31296.0000 |    0.0000 |
| innodb2    |     234260 | Compressed | 14624.0000 |    0.0000 |
| myisam1    |     235886 | Fixed      | 93294.7559 | 2365.0000 |
+------------+------------+------------+------------+-----------+
3 rows in set (0.04 sec)

-- 官方文件說明 : https://dev.mysql.com/doc/refman/8.0/en/tables-table.html
-- For InnoDB tables, the row count is only a rough estimate used in SQL optimization. 
-- (This is also true if the InnoDB table is partitioned.) 
-- InnoDB 的 table_rows 是評估值.

---------
DELIMITER $$
CREATE PROCEDURE dbtblrowcnt(IN schema_name varchar(64))
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE tblname varchar(64);
    
    DECLARE cur1 CURSOR FOR 
        SELECT TABLE_NAME
          FROM information_schema.tables
         WHERE TABLE_SCHEMA = schema_name
         ORDER BY TABLE_NAME;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    
    OPEN cur1;
    
    read_loop:LOOP
        FETCH cur1 INTO tblname;
        IF done THEN
            LEAVE read_loop;
        END IF;
        
        SET @SQLText = CONCAT('SELECT COUNT(*) INTO @cnt FROM ', schema_name, '.', tblname);
        PREPARE stmt FROM @SQLText;
        EXECUTE stmt;
        SELECT tblname, @cnt;
    END LOOP;
    
    CLOSE cur1;
END $$
DELIMITER ;
---
CALL dbtblrowcnt('miku1');

+---------+--------+
| tblname | @cnt   |
+---------+--------+
| innodb1 | 235886 |
+---------+--------+
1 row in set (0.02 sec)

+---------+--------+
| tblname | @cnt   |
+---------+--------+
| innodb2 | 235886 |
+---------+--------+
1 row in set (0.08 sec)

+---------+--------+
| tblname | @cnt   |
+---------+--------+
| myisam1 | 235886 |
+---------+--------+
1 row in set (0.08 sec)

Query OK, 0 rows affected (0.09 sec)

informationschema.TABLES 不準確,
那什麼準確?

SELECT count(1) AS num FROM dbname

可以用如上的命令來取得單表的總筆數。
好像還有一種是用常數的方式，但那不是真正計算。而是取用schema資料。
要真正計算的還是要用上面的語法

一般我也是會直接用informationschema或是mysql內的系統mysql資料庫中的innodb_table_stats表來處理。
表不多的話還無所謂啦。表一多會很可怕喔!!