MySQL通过SHOW TABLE STATUS查看库中所有表的具体信息

有时候我们想看下指定库下所有表的使用情况，比如，查询表的Table大小，什么时候创建的，数据最近被更新的时间（即最近一笔insert/update/delete的时间）。这些信息对我们进行库表维护很有作用。

1.查询语法

SHOW TABLE STATUS
    [{FROM | IN} db_name]
    [LIKE 'pattern' | WHERE expr]

2.查询语句说明

{FROM | IN} db_name， 可选项，表示查询哪个数据库下面的表信息。
LIKE 'pattern' | WHERE expr ，可选项，可指定符合条件的表；LIKE 'pattern' 可指定表名； WHERE expr，可通过 返回输出字段like来指定符合条件的表名。

不带任何参数，表示查询的是当前库中，所以表的具体信息。
例如,以下语句为查询当前库中所以表大小为16384的表信息。

SHOW TABLE STATUS  where Data_length like 16384;

此语句返回信息如下图：

3.查询结果中各列的说明

4.通过系统数据库查询

其实我们也可以通过information_schema数据库下面的tables表去查询表的具体信息。查询结果和上面的信息差不多。例如我们查询 dbtest 数据库下面的表信息

  显示结果如下：

---------------------------------------------------------------------------------------------------------------------------------

SHOW TABLE STATUS语句

SHOW TABLE STATUS
    [{FROM | IN} db_name]
    [LIKE 'pattern' | WHERE expr]

SHOW TABLE STATUS像一样工作SHOW TABLES，但是提供了有关每个非TEMPORARY表的很多信息。您也可以使用mysqlshow --statusdb_name命令获得此列表。该LIKE子句（如果存在）指示要匹配的表名。的WHERE条款可以给出使用更一般的条件来选择行，如在讨论第25.48，“扩展到SHOW语句”。

该语句还显示有关视图的信息。

SHOW TABLE STATUS输出包含以下列：

• Name

  表的名称。

• Engine

  表的存储引擎。请参阅InnoDB存储引擎和备用存储引擎。

  对于分区表，Engine显示所有分区使用的存储引擎的名称。

• Version

  该列未使用。删除.frmMySQL 8.0中的文件后，此列现在报告的硬编码值10，这是.frmMySQL 5.7中使用的最后一个文件版本。

• Row_format

  该行的存储格式（Fixed，Dynamic，Compressed，Redundant，Compact）。对于MyISAM表，Dynamic对应于myisamchk -dvv报告为的内容Packed。

• Rows

  行数。一些存储引擎（例如MyISAM）存储准确的计数。对于其他存储引擎，例如InnoDB，该值是一个近似值，可能与实际值相差40％至50％。在这种情况下，请使用SELECT COUNT(*)以获得准确的计数。

  该Rows值NULL用于INFORMATION_SCHEMA表。

  对于InnoDB表，行数只是SQL优化中使用的粗略估计。（如果InnoDB表已分区，则也是如此。）

• Avg_row_length

  平均行长。

• Data_length

  对于MyISAM，Data_length是数据文件的长度（以字节为单位）。

  对于InnoDB，Data_length是为聚簇索引分配的大约空间量（以字节为单位）。具体来说，它是聚集索引大小（以页为单位）乘以InnoDB页面大小。

  有关其他存储引擎的信息，请参阅本节末尾的注释。

• Max_data_length

  对于MyISAM，Max_data_length是数据文件的最大长度。给定使用的数据指针大小，这是可以存储在表中的数据字节总数。

  未使用InnoDB。

  有关其他存储引擎的信息，请参阅本节末尾的注释。

• Index_length

  对于MyISAM，Index_length是索引文件的长度（以字节为单位）。

  对于InnoDB，Index_length是为非聚簇索引分配的大约空间量（以字节为单位）。具体来说，它是非聚集索引大小（以页为单位）的总和乘以InnoDB页面大小。

  有关其他存储引擎的信息，请参阅本节末尾的注释。

• Data_free

  已分配但未使用的字节数。

  InnoDB表报告表所属表空间的可用空间。对于位于共享表空间中的表，这是共享表空间的可用空间。如果您使用多个表空间，并且表具有自己的表空间，则可用空间仅用于该表。可用空间是指完全可用范围中的字节数减去安全裕量。即使可用空间显示为0，只要不需要分配新的盘区，也可以插入行。

  对于NDB群集，Data_free显示磁盘上为磁盘数据表或磁盘上的碎片分配但未被磁盘空间使用的空间。（该Data_length列中报告了内存中数据资源的使用情况。）

  对于分区表，此值仅是估计值，可能不是绝对正确。在这种情况下，获取此信息的一种更准确的方法是查询INFORMATION_SCHEMAPARTITIONS表，如本示例所示：

  SELECT SUM(DATA_FREE)
      FROM  INFORMATION_SCHEMA.PARTITIONS
      WHERE TABLE_SCHEMA = 'mydb'
      AND   TABLE_NAME   = 'mytable';
  

  有关更多信息，请参见“ INFORMATION_SCHEMA分区表”。

• Auto_increment

  下一个AUTO_INCREMENT值。

• Create_time

  创建表的时间。

• Update_time

  数据文件的最后更新时间。对于某些存储引擎，此值为NULL。例如，InnoDB将多个表存储在其系统表空间中，并且数据文件时间戳不适用。即使文件每次表模式与每个InnoDB在单独的表.ibd文件，改变缓冲可以延缓写入到数据文件，因此，文件的修改时间是从最后插入，更新或删除的时间不同。对于MyISAM，使用数据文件时间戳；但是，在Windows上，时间戳不会通过更新进行更新，因此该值不准确。

  Update_time显示last UPDATE，INSERT或DELETE对InnoDB未分区表执行的时间戳记值。对于MVCC，时间戳记值反映了该COMMIT时间，该时间被视为最后更新时间。重新启动服务器或从InnoDB数据字典缓存中删除表时，时间戳记不会保留。

• Check_time

  上次检查表的时间。并非所有存储引擎这次都更新，在这种情况下，该值始终为NULL。

  对于分区InnoDB表，Check_time始终为NULL。

• Collation

  该表的默认排序规则。输出未显式列出表默认字符集，但排序规则名称以字符集名称开头。

• Checksum

  实时校验和值（如果有）。

• Create_options

  与一起使用的额外选项CREATE TABLE。CREATE TABLE执行时的原始选项将保留，此处报告的选项可能与活动表的设置和选项不同。

  对于InnoDB表，将报告实际值ROW_FORMAT和KEY_BLOCK_SIZE选项。在MySQL 8.0之前，Create_options报告最初提供的ROW_FORMAT和KEY_BLOCK_SIZE。有关更多信息，请参见“ CREATE TABLE语句”。

  Create_options显示partitioned表是否已分区。它还显示ENCRYPTION在创建或更改每表文件表空间时指定的选项。该列不显示在创建或更改常规表空间时指定的加密选项。该表的ENCRYPTION列INNODB_TABLESPACES适用于每表文件空间和常规表空间。

• Comment

  创建表时使用的注释（或有关MySQL为什么无法访问表信息的信息）。

注意

• 对于InnoDB表，SHOW TABLE STATUS除了表保留的物理大小外，不提供准确的统计信息。行数只是SQL优化中使用的粗略估计。
• 对于NDB表，此语句的输出显示Avg_row_length和Data_length列的适当值，但BLOB不考虑列。
• 对于NDB表，仅Data_length包括存储在主存储器中的数据；在Max_data_length与Data_free列应用到磁盘的数据。
• 对于NDB群集磁盘数据表，Max_data_length显示为磁盘数据表或片段的磁盘部分分配的空间。（该Data_length列中报告了内存中数据资源的使用情况。）
• 对于MEMORY表，Data_length，Max_data_length，和Index_length值近似分配内存的实际数量。分配算法会大量保留内存，以减少分配操作的数量。
• 对于视图，大部分列中显示的SHOW TABLE STATUS是0或者NULL只是Name表示视图名称，Create_time表示创建时间，和Comment说VIEW。

表信息也可从INFORMATION_SCHEMATABLES表中获得。请参见“ INFORMATION_SCHEMA TABLES表”。

--------------------------------------------------------------------------------------------------------------------

修改数据库字符集：

　　代码如下:

　　ALTER DATABASE db_name DEFAULT CHARACTER SET character_name [COLLATE ...];  

把表默认的字符集和所有字符列（CHAR,VARCHAR,TEXT）改为新的字符集：

　　代码如下:

　　ALTER TABLE tbl_name CONVERT TO CHARACTER SET character_name [COLLATE ...] 

　　如：ALTER TABLE logtest CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci; 

只是修改表的默认字符集：

　　代码如下:

　　ALTER TABLE tbl_name DEFAULT CHARACTER SET character_name [COLLATE...]; 

　　如：ALTER TABLE logtest DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci; 

修改字段的字符集：

　　代码如下:

　　ALTER TABLE tbl_name CHANGE c_name c_name CHARACTER SET character_name [COLLATE ...]; 

　　如：ALTER TABLE logtest CHANGE title title VARCHAR(100) CHARACTER SET utf8 COLLATE utf8_general_ci; 

查看数据库编码：

　　代码如下:

　　SHOW CREATE DATABASE db_name;  

查看表编码：

　　代码如下:

　　SHOW CREATE TABLE tbl_name;  

查看字段编码：

　　代码如下:

　　SHOW FULL COLUMNS FROM tbl_name;