# 获取行数

在本教程中，您将学习在数据库中获取MySQL行计数的各种方法。

## 获取单个表的MySQL行计数

要获取单个表的行计数，可以在[SELECT](http://www.yiibai.com/mysql/select-statement-query-data.html)语句中使用[COUNT(\*)](http://www.yiibai.com/mysql/count.html)，如下所示：

```sql
SELECT 
    COUNT(*)
FROM
    table_name;
```

例如，要获取[示例数据库(yiibaidb)](http://www.yiibai.com/mysql/sample-database.html)中的`customers`表中的行数，可以使用以下语句：

```sql
SELECT 
    COUNT(*)
FROM
    customers;
```

执行上面查询语句，得到以下结果 -

```sql
+----------+
| COUNT(*) |
+----------+
|      122 |
+----------+
1 row in set (0.01 sec)
```

**获取MySQL两个或多个表的行计数**

要获取多个表的行数，可以使用[UNION](http://www.yiibai.com/sql-union-mysql.html)运算符组合每个`SELECT`语句返回的结果集。

例如，要在单个查询中获取`customers`和`orders`表的行数，请使用以下语句。

```sql
SELECT 
    'customers' tablename, 
     COUNT(*) rows
FROM
    customers 
UNION 
SELECT 
    'orders' tablename, 
     COUNT(*) rows
FROM
    orders;
```

**获取特定数据库中所有表的MySQL行计数**

要获取行计数特定数据库中的所有表，例如`yiibaidb`数据，请按照以下步骤：

* 首先，获取数据库中的所有表名
* 第二步，构造一个SQL语句，其中包含由`UNION`分隔的所有表的所有`SELECT COUNT(*)FROM table_name`语句。
* 第三步，使用[准备语句](http://www.yiibai.com/mysql/prepared-statement.html)执行SQL语句。

**第一步**，要获取数据库的所有表名，请从`information_schema`数据库中查询如下：

```sql
SELECT 
    table_name
FROM
    information_schema.tables
WHERE
    table_schema = 'yiibaidb'
        AND table_type = 'BASE TABLE';
```

执行上面查询，得到以下结果 -

```sql
+--------------+
| TABLE_NAME   |
+--------------+
| customers    |
| employees    |
| offices      |
| orderdetails |
| orders       |
| payments     |
| productlines |
| products     |
+--------------+
8 rows in set (0.02 sec)
```

**第二步**，构造SQL语句，我们使用[GROUP\_CONCAT](http://www.yiibai.com/mysql/group_concat.html)和[CONCAT](http://www.yiibai.com/sql-concat-in-mysql.html)函数如下：

```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;
```

在此查询中，`table_list`是第一步中查询结果的表名列表。

以下查询使用第一个查询作为派生表，并以字符串形式返回SQL语句。

```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
    (SELECT 
        table_name
    FROM
        information_schema.tables
    WHERE
        table_schema = 'yiibaidb'
            AND table_type = 'BASE TABLE') table_list;
```

如果您使用*MySQL 8.0+*，则可以使用[MySQL CTE](http://www.yiibai.com/mysql/cte.html)(通用表表达式)而不是派生表：

```sql
WITH table_list AS (
SELECT
    table_name
  FROM information_schema.tables 
  WHERE table_schema = 'yiibaidb' AND
        table_type = 'BASE TABLE'
) 
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;
```

**第三步**，使用[prepare语句](http://www.yiibai.com/mysql/prepared-statement.html)执行`@sql`语句，如下所示：

```sql
USE yiibaidb;
PREPARE s FROM  @sql;
EXECUTE s;
DEALLOCATE PREPARE s;
```

执行上面查询统计语句，得到以下结果 -

```sql
+--------------+------+
| table_name   | rows |
+--------------+------+
| customers    |  122 |
| departments  |    0 |
| employees    |   23 |
| items        |    9 |
| offices      |    7 |
| orderdetails | 2998 |
| orders       |  327 |
| payments     |  273 |
| productlines |    7 |
| products     |  110 |
| tasks        |    0 |
| tokens       |    1 |
+--------------+------+
12 rows in set
```

**使用一个查询获取数据库中所有表的MySQL行计数**

获取数据库中所有表的行计数的快速方法是直接从`information_schema`数据库中查询数据：

```sql
SELECT 
    table_name, 
    table_rows
FROM
    information_schema.tables
WHERE
    table_schema = 'yiibaidb'
ORDER BY table_rows desc;
```

执行上面查询，得到以下结果 -

```sql
mysql> SELECT 
    table_name, 
    table_rows
FROM
    information_schema.tables
WHERE
    table_schema = 'yiibaidb'
ORDER BY table_rows desc;
+--------------+------------+
| table_name   | table_rows |
+--------------+------------+
| orderdetails |       2731 |
| orders       |        326 |
| payments     |        256 |
| customers    |        122 |
| products     |        110 |
| employees    |         23 |
| items        |          9 |
| productlines |          7 |
| offices      |          7 |
| tokens       |          0 |
| tasks        |          0 |
| departments  |          0 |
+--------------+------------+
12 rows in set
```

此方法有时不准确，因为表中的`information_schema`中的行计数和实际行计数不同步。 为避免这种情况，您必须在从`information_schema`数据库查询行计数之前运行[ANALYZE TABLE](http://www.yiibai.com/mysql/database-table-maintenance-statements.html)语句。

```sql
ANALYZE TABLE table_name, ...;
```

在本教程中，您已经学习了各种方法来获取MySQL数据库中一个或多个表的行数。


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://hezhiqiang-book.gitbook.io/mysql/di-er-zhang/huo-qu-hang-shu.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.