MySQL information_schema.COLUMNS表没有自动更新的解决办法

我部署的是MySQL 5.7版本，最近需要统计数据库中所有表的字段信息，发现统计的数据跟之前的没有变化，而实际上我最近对数据库做了一些改动，增加了一些字段。

我统计数据库中所有表的字段信息时，使用的是如下SQL脚本，从information_schema.COLUMNS来获取表字段的数量：

--统计数据库每个表的字段数
SELECT TABLE_NAME,COUNT( * ) FROM information_schema.COLUMNS WHERE table_schema = 'MYDATABASE' GROUP BY TABLE_NAME;

这样看来我这样获取到的字段信息是不准确的，MySQL的information_schema库中的COLUMNS表并没有随着数据库表字段的更新而同步刷新。在百度上搜索了很多办法发现都是错的，查阅了大量资料以及google了之后发现原来这个问题是MySQL的isolation level（隔离级别）的配置导致的。

information_schema是一个“in-memory”的数据库，它是从log里读取数据并且创建表的，我们需要将隔离级别（SESSION和GLOBAL）设置为REPEATABLE READ（可重复读），这个库才会对变更自动刷新。

关于隔离级别的设置，可以参考以下：

首先需要了解一些基本的概念

1.隔离级别分为SESSION和GLOBAL：
SESSION只是对当前会话有效；
GLOBAL对非当前会话的新会话有效；

2.SQL标准定义了4类隔离级别，分别是：

1）Read Uncommitted（读取未提交内容）

在该隔离级别，所有事务都可以看到其他未提交事务的执行结果。本隔离级别很少用于实际应用，因为它的性能也不比其他级别好多少。读取未提交的数据，也被称之为脏读（Dirty
Read）。

2）Read Committed（读取提交内容）

这是大多数数据库系统的默认隔离级别（但不是MySQL默认的）。它满足了隔离的简单定义：一个事务只能看见已经提交事务所做的改变。这种隔离级别 也支持所谓的不可重复读（Nonrepeatable
Read），因为同一事务的其他实例在该实例处理其间可能会有新的commit，所以同一select可能返回不同结果。

3）Repeatable Read（可重复读）

这是MySQL的默认事务隔离级别，它确保同一事务的多个实例在并发读取数据时，会看到同样的数据行。不过理论上，这会导致另一个棘手的问题：幻读（Phantom Read）。

4）Serializable（可串行化）

这是最高的隔离级别，它通过强制事务排序，使之不可能相互冲突，从而解决幻读问题。简言之，它是在每个读的数据行上加上共享锁。在这个级别，可能导致大量的超时现象和锁竞争。

2.查看当前数据库的隔离级别的SQL语句如下：

select @@session.tx_isolation;
select @@global.tx_isolation;
--或者：
elect @@session.transaction_isolation;
select @@global.transaction_isolation;

3.设置隔离级别的SQL语句如下：

SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE

SET GLOBAL TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED
SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ
SET GLOBAL TRANSACTION ISOLATION LEVEL SERIALIZABLE

另外，在特殊需求，不能设置隔离级别为可重复读时，可以尝试执行ANALYZE TABLE命令来手动刷新information_schema.COLUMNS表，使用时根据需要执行SQL语句：

ANALYZE TABLE 表名;

具体操作可以参考下图：

版权声明：除特殊说明，文章均为博主 去吐槽 原创文章，转载请注明原文出处。

原文链接: https://www.qutucao.com/863.html