[MySQL] DB 내 모든 테이블 ROW 수 확인하기

개요

단일 테이블에 대한 ROW 수는 특정 테이블에 count 함수를 실행하여 간단하게 구할 수 있다.

SELECT 
    COUNT(*)
FROM
    table_name;

 

실행 결과

| count(1) |
| -------- |
| 2        |

 

그렇다면 데이터베이스 내 모든 테이블을 대상으로 행 수를 얻을 때는 어떻게 해야 할까? 방법을 적어둔다.

 

예시 DB

아래의 DDL로 생성한 예시 데이터베이스를 이용해 테스트해본다.

CREATE TABLE test (
  id INT
);
INSERT INTO test (id) VALUES (1);
INSERT INTO test (id) VALUES (2);

CREATE TABLE IF NOT EXISTS `docs` (
  `id` int(6) unsigned NOT NULL,
  `rev` int(3) unsigned NOT NULL,
  `content` varchar(200) NOT NULL,
  PRIMARY KEY (`id`,`rev`)
) DEFAULT CHARSET=utf8;
INSERT INTO `docs` (`id`, `rev`, `content`) VALUES
  ('1', '1', 'The earth is flat'),
  ('2', '1', 'One hundred angels can dance on the head of a pin'),
  ('1', '2', 'The earth is flat and rests on a bull\'s horn'),
  ('1', '3', 'The earth is like a ball.');

 

 

information_schema에서 조회

데이터베이스 내 저장된 테이블에 대한 정보는 information_schema.tables에 저장되어 있다. tables에는 table_rows라는 컬럼으로 해당 테이블이 저장하고 있는 데이터의 행 수를 저장하고 있다.

다만 information_schema에 저장된 정보와 실제 테이블 수가 동기화되지 않아 정확하지 않을 수 있다. 정확한 데이터를 얻고 싶다면 ANALYZE TABLE 명령어를 사용한 후, 테이블 행 수를 쿼리해야 한다.

ANALYZE TABLE table_name, ...;

SELECT 
    table_name, 
    table_rows
FROM
    information_schema.tables
WHERE
    table_schema = '<DB_NAME>'
ORDER BY table_name;

 

실행 결과

| TABLE_NAME | TABLE_ROWS |
| ---------- | ---------- |
| docs       | 4          |
| test       | 2          |

 

 

모든 테이블에서 직접 조회

MySQL Prepared Statement를 이용해 직접 조회해 본다.

 

1. 데이터베이스 내 모든 테이블 목록을 가져온다.

SELECT 
    table_name
FROM
    information_schema.tables
WHERE 1=1
	AND table_schema = '<DB_NAME>'
    AND table_type = 'BASE TABLE'
;

 

실행 결과

| TABLE_NAME |
| ---------- |
| docs       |
| test       |

 

 

2. SQL을 구성한다.

테이블 목록 조회 쿼리 결과를 table_list라고 할 때, 아래와 같이 SQL문을 구성할 수 있다.

SELECT 
    CONCAT(GROUP_CONCAT(CONCAT('SELECT \'',
                        table_name,
                        '\' table_name,COUNT(*) rows FROM ',
                        table_name)
                SEPARATOR ' UNION '),
            ' ORDER BY table_name')
INTO @sql 
FROM
    table_list
;

서브쿼리 또는 CTE (MySQL 8.0 이상 지원)을 통해 SQL을 생성한다. concat 명령어로 생성한 쿼리문을 @sql에 저장한다.

 

서브쿼리 사용 시

SELECT 
    CONCAT(GROUP_CONCAT(CONCAT('SELECT \'',
                        table_name,
                        '\' table_name, COUNT(*) row_cnt FROM ',
                        table_name)
                SEPARATOR ' UNION '),
            ' ORDER BY table_name')
INTO @sql 
FROM
    (SELECT 
        table_name
    FROM
        information_schema.tables
    WHERE 1=1
        AND table_schema = '<DB_NAME>'
        AND table_type = 'BASE TABLE') table_list
;

 

CTE 사용 시

WITH table_list AS (
SELECT
    table_name
  FROM information_schema.tables 
  WHERE table_schema = '<DB_NAME>' AND
        table_type = 'BASE TABLE'
) 
SELECT CONCAT(
            GROUP_CONCAT(CONCAT("SELECT '", table_name, "' table_name, COUNT(*) row_cnt FROM ", table_name) SEPARATOR " UNION "),
            ' ORDER BY table_name'
        )
INTO @sql
FROM table_list;

 

실행 결과

SELECT @sql;

-- 결과
| @sql                                                                                                                                |
| ----------------------------------------------------------------------------------------------------------------------------------- |
| SELECT 'docs' table_name, COUNT(*) row_cnt FROM docs UNION SELECT 'test' table_name, COUNT(*) row_cnt FROM test ORDER BY table_name |

 

3. @sql에 저장한 명령을 수행한다.

PREPARE s FROM @sql;
EXECUTE s;
DEALLOCATE PREPARE s;

 

실행 결과

| table_name | row_cnt |
| ---------- | ------- |
| docs       | 4       |
| test       | 2       |

 

 

참고 문서

https://www.mysqltutorial.org/mysql-row-count/