mysql 统计表大小_mysql – 计算表的行大小和最大行大小

问题：

有没有办法计算表创建占用的字节数,我知道你可以从information_schema.tables获取一些信息,但这些信息不够准确.

实际需要的是仅根据innodb表的定义的字节数,并且collat​​ion也可以被认为是utf-8-general-ci

例如,表测试如下

create table test

(

col1 varchar(25),

col2 int,

col3 varchar(3),

col4 char(15),

col5 datetime

);

现在需要知道根据表中列的类型可以在一行中累积的总行大小.

在MSSQL中找到了某种类似的解决方案,但需要它的MySQL版本

任何帮助深表感谢.

解决方法:

经过大量思考和研究后发现了一个真正有助于实现所需要的答案.它是一个perl脚本,参考链接是

#!/usr/bin/perl

use strict;

$| = 1;

my %DataType = (

"TINYINT"=>1, "SMALLINT"=>2, "MEDIUMINT"=>3, "INT"=>4, "INTEGER"=>4, "BIGINT"=>8,

"FLOAT"=>'$M<=24?4:8', "DOUBLE"=>8,

"DECIMAL"=>'int(($M-$D)/9)*4+int(((($M-$D)%9)+1)/2)+int($D/9)*4+int((($D%9)+1)/2)',

"NUMERIC"=>'int(($M-$D)/9)*4+int(((($M-$D)%9)+1)/2)+int($D/9)*4+int((($D%9)+1)/2)',

"BIT"=>'($M+7)>>3',

"DATE"=>3, "TIME"=>3, "DATETIME"=>8, "TIMESTAMP"=>4, "YEAR"=>1,

"BINARY"=>'$M',"CHAR"=>'$M*$CL',

"VARBINARY"=>'$M+($M>255?2:1)', "VARCHAR"=>'$M*$CL+($M>255?2:1)',

"ENUM"=>'$M>255?2:1', "SET"=>'($M+7)>>3',

"TINYBLOB"=>9, "TINYTEXT"=>9,

"BLOB"=>10, "TEXT"=>10,

"MEDIUMBLOB"=>11, "MEDIUMTEXT"=>11,

"LONGBLOB"=>12, "LONGTEXT"=>12

);

my %DataTypeMin = (

"VARBINARY"=>'($M>255?2:1)', "VARCHAR"=>'($M>255?2:1)'

);

my ($D, $M, $S, $C, $L, $dt, $dp ,$bc, $CL);

my $fieldCount = 0;

my $byteCount = 0;

my $byteCountMin = 0;

my @fields = ();

my $fieldName;

my $tableName;

my $defaultDbCL = 1;

my $defaultTableCL = 1;

my %charsetMaxLen;

my %collationMaxLen;

open (CHARSETS, "mysql -B --skip-column-names information_schema -e 'select CHARACTER_SET_NAME,MAXLEN from CHARACTER_SETS;' |");

%charsetMaxLen = map ( ( /^(\w+)/ => /(\d+)$/ ), );

close CHARSETS;

open (COLLATIONS, "mysql -B --skip-column-names information_schema -e 'select COLLATION_NAME,MAXLEN from CHARACTER_SETS INNER JOIN COLLATIONS USING(CHARACTER_SET_NAME);' |");

%collationMaxLen = map ( ( /^(\w+)/ => /(\d+)$/ ), );

close COLLATIONS;

open (TABLEINFO, "mysqldump -d --compact ".join(" ",@ARGV)." |");

while () {

chomp;

if ( ($S,$C) = /create database.*?`([^`]+)`.*default\scharacter\sset\s+(\w+)/i ) {

$defaultDbCL = exists $charsetMaxLen{$C} ? $charsetMaxLen{$C} : 1;

print "Database: $S".($C?" DEFAULT":"").($C?" CHARSET $C":"")." (bytes per char: $defaultDbCL)\n\n";

next;

}

if ( /^create table\s+`([^`]+)`.*/i ) {

$tableName = $1;

@fields = ();

next;

}

if ( $tableName && (($C,$L) = /^\)(?:.*?default\scharset=(\w+))?(?:.*?collate=(\w+))?/i) ) {

$defaultTableCL = exists $charsetMaxLen{$C} ? $charsetMaxLen{$C} : (exists $collationMaxLen{$L} ? $collationMaxLen{$L} : $defaultDbCL);

print "Table: $tableName".($C||$L?" DEFAULT":"").($C?" CHARSET $C":"").($L?" COLLATION $L":"")." (bytes per char: $defaultTableCL)\n";

$tableName = "";

$fieldCount = 0;

$byteCount = 0;

$byteCountMin = 0;

while ($_ = shift @fields) {

if ( ($fieldName,$dt,$dp,$M,$D,$S,$C,$L) = /\s\s`([^`]+)`\s+([a-z]+)(\((\d+)(?:,(\d+))?\)|\((.*)\))?(?:.*?character\sset\s+(\w+))?(?:.*?collate\s+(\w+))?/i ) {

$dt = uc $dt;

if (exists $DataType{$dt}) {

if (length $S) {

$M = ($S =~ s/(\'.*?\'(?!\')(?=,|$))/$1/g);

$dp = "($M : $S)"

}

$D = 0 if !$D;

$CL = exists $charsetMaxLen{$C} ? $charsetMaxLen{$C} : (exists $collationMaxLen{$L} ? $collationMaxLen{$L} : $defaultTableCL);

$bc = eval($DataType{$dt});

$byteCount += $bc;

$byteCountMin += exists $DataTypeMin{$dt} ? $DataTypeMin{$dt} : $bc;

} else {

$bc = "??";

}

$fieldName.="\t" if length($fieldName) < 8;

print "bytes:\t".$bc."\t$fieldName\t$dt$dp".($C?" $C":"").($L?" COLL $L":"")."\n";

++$fieldCount;

}

}

print "total:\t$byteCount".($byteCountMin!=$byteCount?"\tleast: $byteCountMin":"\t\t")."\tcolumns: $fieldCount\n\n";

next;

}

push @fields, $_;

}

close TABLEINFO;

谢谢大家的帮助.

标签：information-schema,mysql,database-design,innodb

来源： https://codeday.me/bug/20190805/1589699.html