MySQL: суммировать все подсчет строк таблицы в одном запросе

При просмотре базы данных очень полезно получить обзор всех таблиц, включая их количество строк:

TableName    Count
t1           1234
t2             37 
...           ...

Таблица MySQL TABLES в базе данных information_schema предоставляет поле table_rows:

SELECT table_name, table_rows
    FROM INFORMATION_SCHEMA.TABLES
    WHERE TABLE_SCHEMA = '<your db>';

Но table_rows действителен только для некоторых движков базы данных, тогда как для INNODB это либо NULL, либо неточно.

Следовательно, необходимо составить метод, который делает явное значение SELECT Count (*)... для каждой таблицы.

Во многих повторениях этого вопроса о stackoverflow существует множество ответов, которые включают двухэтапный процесс. Один запрос для создания набора результатов с строками, содержащими операторы select select (*), за которыми следует процедура текстового кодирования, чтобы превратить это в фактический оператор, который может создать желаемый результат.

Я не видел, чтобы это превратилось в один шаг, поэтому ниже я отправляю этот ответ. Это не ракетная наука, но это удобно, если бы это было прописано.

Ответы

Ответ 1

Первый примерный код здесь - хранимая процедура, которая выполняет весь процесс за один шаг, насколько это касается пользователя.

BEGIN

# zgwp_tables_rowcounts
# TableName RowCount
# Outputs a result set listing all tables and their row counts 
# for the current database

SET SESSION group_concat_max_len = 1000000;

SET @sql = NULL;
SET @dbname = DATABASE();

SELECT
  GROUP_CONCAT( 
    CONCAT (
      'SELECT ''',table_name,''' as TableName, COUNT(*) as RowCount FROM ', 
       table_name, ' '
    ) 
    SEPARATOR 'UNION '  
  ) AS Qry
FROM
  information_schema.`TABLES` AS t
WHERE
  t.TABLE_SCHEMA = @dbname AND
  t.TABLE_TYPE = "BASE TABLE"
ORDER BY
  t.TABLE_NAME ASC

INTO @sql
;

PREPARE stmt FROM @sql;

EXECUTE stmt;

END

Примечания:

  • SELECT..INTO @sql создает необходимый запрос, а PREPARE... EXECUTE запускает его.

  • Устанавливает переменную group_concat_max_len, чтобы разрешить длинную строку результата из GROUP_CONCAT.

Вышеупомянутая процедура полезна для быстрого поиска в среде администратора, такой как Navicat, или в командной строке. Однако, несмотря на возвращение набора результатов, насколько мне известно, на него нельзя ссылаться в другом представлении или запросе, по-видимому, потому, что MySQL не может определить, перед запуском его, какие результаты наборы он производит, пусть вдоль каких столбцов они имеют.

Таким образом, по-прежнему полезно быстро создавать без ручного редактирования отдельный оператор SELECT... UNION, который можно использовать как представление. Это полезно, если вы хотите объединить количество строк в другую информацию из одной таблицы из другой таблицы. При этом еще одна хранимая процедура:

BEGIN

# zgwp_tables_rowcounts_view_statement
# Output: SelectStatement
# Outputs a single row and column, containing a (possibly lengthy)
# SELECT...UNION statement that, if used as a View, will output
# TableName RowCount for all tables in the current database.

SET SESSION group_concat_max_len = 1000000;
SET @dbname = DATABASE();

SELECT
  GROUP_CONCAT( 
    CONCAT (
      'SELECT ''',table_name,''' as TableName, COUNT(*) as RowCount FROM ', 
      table_name, ' ', CHAR(10)) 
    SEPARATOR 'UNION '  
  ) AS SelectStatement
FROM
  information_schema.`TABLES` AS t
WHERE
  t.TABLE_SCHEMA = @dbname AND
  t.TABLE_TYPE = "BASE TABLE"
ORDER BY
  t.TABLE_NAME ASC
;
END

Примечания

  • Очень похож на первую процедуру в концепции. Я добавил строку (CHAR (10)) для каждого вспомогательного оператора SELECT... UNION для удобства просмотра или редактирования инструкции.

  • Вы можете создать это как функцию и вернуть SelectStatement, если это более удобно для вашей среды.

Надеюсь, что это поможет.