如何从我的查询MySQL计算大小? [英] How to calculate size from my query MySQL?

问题描述

我尝试查找以MB为单位的查询大小，但我不知道该怎么做. 我想以这种方式提取查询的大小:

I try to find size in MB of my query, but I don't understand how to do it. I would like to extract the size of my query in this way:

SELECT size_query FROM my_query

其中的"my_query"可能为:SELECT * FROM MyTable WHERE idFactory = 1

Where 'my_query' it could be: SELECT * FROM MyTable WHERE idFactory = 1

推荐答案

如果我正确理解了您的问题，则 ajreal 具有已经在

If I understand your question correctly, ajreal has already provided a solution on this StackOverflow question. Quoted:

select sum(row_size) 
from (
  select 
    char_length(column1)+
    char_length(column2)+
    char_length(column3)+
    char_length(column4) ... <-- repeat for all columns
  as row_size 
  from your_table
) as tbl1;

这将为您提供查询的大小(以字节为单位)，除以1024除以千字节，再除以兆字节.

This will give you the size of your query in bytes, divide by 1024 for kilobytes and again for megabytes.

如果您还是要将完整的结果集拉回PHP，并且想知道它的大小，则可以使用以下方法在PHP中对其进行计算:

If you're pulling the full result set back to PHP anyway and want to know the size of it, you could calculate it in PHP using something like this:

<?php

$data = [
    [
        'item' => 'Apple',
        'type' => 'fruit',
        'in_stock' => true
    ],
    [
        'item' => 'Biscuits',
        'type' => 'confectionery',
        'in_stock' => false
    ],
    [
        'item' => 'Milk',
        'type' => 'dairy',
        'in_stock' => true
    ],
];


function get_array_size(&$array)
{
    $size = 0;

    foreach ($array as $key => $value) {
        if (is_array($value)) {
            $size += get_array_size($value);
        } else if (is_string($value)) {
            $size += strlen($value);
        } else if (is_bool($value)) {
            $size += 1;
        } /* else if ( some other type ) {

        } */
    }

    return $size;
}

echo get_array_size($data); // Outputs 43

根据您的用例，您可能会接受或可能不接受.如果您要测量线路上的物理字节，这可能不够准确.

This may or may not be acceptable to you depending on your use case. If you're looking to measure the physical bytes on the wire, this probably won't be accurate enough.

这篇关于如何从我的查询MySQL计算大小?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！