SQL分析——常见问题七

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

要获取特定数据库mysqldemo中所有表的行数，请使用以下步骤：

1. 首先，获取数据库中的所有表名
2. 其次，构造一个SQL语句，其中包含SELECT COUNT(*) FROM table_name以分隔的所有表的所有语句UNION。
3. 第三，使用预处理语句执行SQL 语句。

首先，要获取数据库的所有表名，请information_schema按以下方式从数据库中查询：

SELECT
	table_name 
FROM
	information_schema.TABLES 
WHERE
	table_schema = 'mysqldemo' 
	AND table_type = 'BASE TABLE'; 

其次，要构造SQL语句，我们使用GROUP_CONCAT 和CONCAT 函数，如下所示：

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语句。

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 = 'classicmodels'
            AND table_type = 'BASE TABLE') table_list 

如果您使用的是MySQL 8.0+，则可以使用MySQL CTE（公用表表达式）代替派生表：

WITH table_list AS (
SELECT
    table_name
  FROM information_schema.tables
  WHERE table_schema = 'classicmodels' 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
FROMtable_list; 

第三，@sql使用 准备好的语句执行该语句，如下所示：

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

通过一个查询获取数据库中所有表的MySQL行计数

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

SELECT
    table_name,
    table_rows
FROM
    information_schema.tables
WHERE
    table_schema = 'mysqldemo'
ORDER BY table_name; 

此方法有时不准确，因为的行数information_schema与表中的实际行数不同步。为了避免这种情况，必须ANALYZE TABLE在从information_schema数据库查询行数之前运行该语句。

 ANALYZE TABLE table_name, ...; 

MySQL 比较两个表

在数据迁移中，我们经常必须比较两个表以标识一个表中的一条记录，而另一表中没有相应的记录。

例如，我们有一个新数据库，其架构与旧数据库不同。我们的任务是将所有数据从旧数据库迁移到新数据库，并验证数据是否已正确迁移。

要检查数据，我们必须比较两个表，一个在新数据库中，一个在旧数据库中，并识别不匹配的记录。

假设我们有两个表：t1 和 t2。以下步骤比较两个表并标识不匹配的记录：

首先，使用UNION语句合并两个表中的行；仅包括需要比较的列。返回的结果集用于比较。

SELECT t1.pk, t1.c1
FROM t1
UNION ALL
SELECT t2.pk, t2.c1
FROM t2 

其次，根据需要比较的主键和列将记录分组。如果需要比较的列中的值相同，则COUNT()返回2，否则 COUNT()返回1。

请参阅以下查询：

SELECT pk, c1
FROM
 (
   SELECT t1.pk, t1.c1
   FROM t1
   UNION ALL
   SELECT t2.pk, t2.c1
   FROM t2
)  t
GROUP BY pk, c1
HAVING COUNT(*) = 1
ORDER BY pk 

如果比较中涉及的列中的值相同，则不返回任何行。

MySQL 比较两个表的例子:
首先，创建2个具有相似结构的表：

CREATE TABLE t1(
    id int auto_increment primary key,
    title varchar(255)    
);
 
CREATE TABLE t2(
    id int auto_increment primary key,
    title varchar(255),
    note varchar(255)
); 

其次，在t1和t2表中插入一些数据：

INSERT INTO t1(title)
VALUES('row 1'),('row 2'),('row 3');
 
INSERT INTO t2(title,note)
SELECT title, 'data migration'
FROM t1; 

第三，比较两个表的id和title列的值：

SELECT id,title
FROM (
SELECT id, title FROM t1
UNION ALL
SELECT id,title FROM t2
) tbl
GROUP BY id, title
HAVING count(*) = 1
ORDER BY id; 

没有行返回，因为没有不匹配的记录。

第四，在t2表中插入新行：

INSERT INTO t2(title,note)
VALUES('new row 4','new'); 

第五，再次执行查询以比较两个表中标题列的值。新记录中未匹配的则被返回。

Mysql Like 优化

要提高Mysql 的查询效率最有效的办法是让所有的查询走索引字段，但是在Mysql中 Like 关键字只有对前缀查询(“keyword%”)走索引。

select * from table where name like "keyword%"

我们常常需要模糊查询（“%keyword%”）或后缀查询(“%keyword”)。

解决办法的思路是想办法让模糊查询和后缀查询都能走索引就可以达到目的。

后缀查询解决方案：使用新建字段反转索引然后关键字段反转变成前缀查询：

select * from table where rename like "drowyey%"

目前无法使用模糊查询，如果量大可以全文索引。