iT邦幫忙

0

MYSQL,如何寫一個SCRIPT查詢一個DB下所有TABLE的筆數

MYSQL,如何寫一個SCRIPT查詢兩個DB下所有TABLE的筆數

不可以利用informationschema.TABLES 因為主管說不準確
所以要使用select count() 但是我不太會寫,有高手可以幫幫忙嗎?

假設DB:AAA TABLE:10個 10個TABLE各別筆數
假設DB:CCC TABLE:10個 10個TABLE各別筆數

可以單查或是秀成一個表格都可以!

2
一級屠豬士
iT邦高手 1 級 ‧ 2019-03-29 16:29:18
最佳解答
-- 參考敝人之前的資料
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)

https://ithelp.ithome.com.tw/upload/images/20190329/20050647abTGCnmQ7f.png

0
小魚
iT邦高手 1 級 ‧ 2019-03-28 14:36:27

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

看更多先前的回應...收起先前的回應...
dadou1211 iT邦新手 5 級 ‧ 2019-03-28 14:38:31 檢舉

他是說在動態的情形下,資料會增加,所以用informationschema.TABLES會有落差

小魚 iT邦高手 1 級 ‧ 2019-03-28 14:39:26 檢舉

這種情況不多吧,
有比這更準確的嗎?

資料表應該不大會增加,
如果要抓資料筆數或容量大小,
就會有些誤差,
不過就是一直更新資料吧...

動態如果資料為微秒的情況下。用count()也不會準確吧。
說真的,實在不了解其主管的想法是啥。

除非你們有交易及更新事件會超過3秒的。

小魚 iT邦高手 1 級 ‧ 2019-03-28 14:45:30 檢舉

另外在抓資料前可以強制更新informationschema表的資料,
就會比較準確,
當然如果你資料每秒都在變動,
那怎麼樣都不會準確.

dadou1211 iT邦新手 5 級 ‧ 2019-03-28 14:51:06 檢舉

其實他就是希望我能用COUNT(*)做出來吧,所以才來求救

小魚 iT邦高手 1 級 ‧ 2019-03-28 14:53:58 檢舉

那不是一樣不準確嗎?
而且我剛剛試,
不能直接count啊...

那就用count吧。
用你的後端程式來跑吧。

dadou1211 iT邦新手 5 級 ‧ 2019-03-29 17:52:08 檢舉

0
浩瀚星空
iT邦高手 1 級 ‧ 2019-03-28 14:40:06
SELECT count(1) AS num FROM dbname

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

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

看更多先前的回應...收起先前的回應...
小魚 iT邦高手 1 級 ‧ 2019-03-28 14:42:50 檢舉

不過 innodb_table_stats 是不是有可能不存在?

dadou1211 iT邦新手 5 級 ‧ 2019-03-28 14:48:14 檢舉
#!/bin/sh

USER="root"
PASS="1234"

databases="AAA"
tables=(`/usr/local/mysql/bin/mysql -u${USER} -p${PASS} -N -e "show tables from ${databases};"`)

for ((i=0; i<${#tables[@]}; i++))
do
    /usr/local/mysql/bin/mysql -u${USER} -p${PASS} -N -e "select count(*) as "${tables[$i]}"  from $databases."${tables[$i]}";"
    ```

其實我有試著寫這樣也只能單獨秀出來一個DB,而且只有數量沒有TABLE名

因為你沒將名字給帶入顯示。

@小魚
是的,有可能不存在,這招是只能給有root權限的人用。
畢竟系統表一般很多是沒權限能讀取的。

小魚 iT邦高手 1 級 ‧ 2019-03-28 15:08:50 檢舉

dadou1211
請把程式碼用兩個 ```包起來
詳見Markdown語法,
才不會有些特殊字元會消失,
類似這樣,

"程式碼"
dadou1211 iT邦新手 5 級 ‧ 2019-03-29 17:51:52 檢舉

謝謝各位高手的回應~這問題我解決了

我要發表回答

立即登入回答