MySQL - 获取大约的通用方式.行大小(以字节为单位) [英] MySQL - universal way of getting approx. row size in bytes

问题描述

好的，如果没有人想帮我解决这些废话，我不会生气，但我想有一种方法可以在 MySQL 控制台中逐行获取表的近似字节大小(好吧，我正在使用phpMyAdmins SQL 窗口).

OK, I won't be mad if nobody wants to help me with this nonsense, but I wanted to have a way to get the approximate row by row byte size of a table in a MySQL console (well I'm using phpMyAdmins SQL window).

内部查询从架构中读取所需的表列并将它们分组连接到一个语句，同时将 char_length() 函数添加到每个列名.我没有找到比使用 SEPARATOR 更好的方法，但是由于分隔符在第一个元素之后出现，我添加了两个无意义的元素:开头的empty"和结尾的zzempty".那些被剥离后替换.

The inner query reads the desired table columns from the schema and group_concats them to a to a statement, while adding the char_length() function to every column name. I did not find a better way, than using SEPARATOR for this, but as the separator comes first after the first element, I add two nonsense elements : "empty" at the beginning and "zzempty" at the end. Thoses are stripped of with replace later.

最好的部分是，我认为这段代码做了我想要的，但问题是，结果被获取但没有显示.

The best part is, I think this code does what I want, but the problem is, the results get fetched but are not displayed.

获得准备好的语句获取的结果的最简单方法是什么?是将结果放入表格的唯一方法吗?

What is the easiest way to get the results fetched by the prepared statement? Is the only way to get the results to put them into a table?

SET    @query = CONCAT('SELECT ',
 (select
   REPLACE(group_concat( column_name 
 ORDER BY
   CASE 
      WHEN column_name="empty" THEN 0 
      WHEN column_name = 'zzempty'THEN 99 
      ELSE 1 
   END,
   column_name   separator '`) + char_length(`'),
   'empty`) + ',
   '')     
 FROM
   (SELECT
      'empty' as column_name,
      '1' as id 
   UNION
   select
      column_name,
      1 as id     
   from
      information_schema.columns     
   where
      table_schema = 'DB_NAME'        
      and table_name = 'TABLE_NAME' 
   UNION
   SELECT
      'zzempty' as column_name,
      '1' as id ) t1      
group by
   id),' FROM `TABLE_NAME`');   
SET
@query2 = REPLACE( @query, '+ char_length(`zzempty',  '')  ;  
PREPARE stmt FROM @query2;  
Execute stmt ;

事实上，我认为必须有更简单的方法来获取行大小，但我没有找到任何方法.

In fact, I think there must be much easier way to get the row size, but I did not find any.

推荐答案

使用 SHOW TABLE STATUS 语句:

It's easier to ask MySQL about it, using the SHOW TABLE STATUS statement:

mysql> SHOW TABLE STATUS WHERE name='ApiRequest'\G
*************************** 1. row ***************************
           Name: ApiRequest
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 105
 Avg_row_length: 1872
    Data_length: 196608
Max_data_length: 0
   Index_length: 16384
      Data_free: 2520776704
 Auto_increment: 114
    Create_time: 2015-05-29 13:00:13
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_unicode_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.00 sec)

这篇关于MySQL - 获取大约的通用方式.行大小(以字节为单位)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！