如何使用PHP从.frm文件中获取表结构? [英] How to get table structures from a .frm file using PHP?

问题描述

让我通过phpMyAdmin创建了一个以pb00k命名的表，该SQL如下所示:

Let I have created a table named after pb00k via phpMyAdmin which SQL is as bellow:

    CREATE TABLE `pb00k` (
  `k3y` int(255) NOT NULL AUTO_INCREMENT,
  `n4m3` text NOT NULL,
  `numb3r` text NOT NULL,
  PRIMARY KEY (`k3y`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

因此它将在mysql/data/中生成一个包含此结构的pb00k.frm文件.现在，我想使用PHP从此文件中获取此结构.

So it generates a pb00k.frm file in mysql/data/ which contains this structure. Now I want to get this structure from this file using PHP.

有可能吗?

推荐答案

是的，有可能恢复至少部分信息. (为了使其他读者受益，问题的提出者已经意识到，有更简单的方法来获取列元数据.)

Yes it's possible to recover at least part of information. (For the benefit of other readers the poser of the question is already aware that there are easier ways to get the column metadata).

面临的挑战是，.frm文件的文档记录不充分，因为一般社区很少需要解密这些文件.此外，文件的格式可能会因操作系统而异.

The challenge is that .frm files are not so well documented because any need to decipher them by the general community is pretty rare. Also the format of the files may vary with the operating system.

但是，通过使用hexdump或类似的实用程序查看文件，您可以部分查看正在发生的情况.然后，您可以更好地了解如何在PHP程序中读取文件并解码原始二进制数据.

However, by viewing the files with hexdump or a similar utility you can see partly what is going on. Then you better informed to read the files in a PHP program and decode the raw binary data.

早些时候，我作为练习来进行此操作，并且能够恢复列数，列名和列类型.

I did this as an exercise some time back, and I was able to recover number of columns, column names and column types.

以下是显示如何提取列名称的示例.我的.frm用于一个名为"stops"的表，但是您可以替换自己的.frm.

Below is a sample to show how to extract column names. My .frm was for a table names "stops", but you can substitute your own .frm.

<?php
$fileName = "stops.frm";

// read file into an array of char
//---------------------------------
$handle = fopen($fileName, "rb");
$contents = fread($handle, filesize($fileName));
fclose($handle);
$fileSize=strlen($contents);  // save the filesize fot later printing

// locate the column data near the end of the file
//-------------------------------------------------
$index = 6;    // location of io_size
$io_size_lo = ord($contents[$index]);  
$io_size_hi = ord($contents[$index+1]);
$io_size = $io_size_hi *0x100 + $io_size_lo; // read IO_SIZE

$index = 10;  // location of record length
$rec_len_lo = ord($contents[$index]);
$rec_len_hi = ord($contents[$index+1]);
$rec_len = $rec_len_hi * 0x100 + $rec_len_lo; // read rec_length

// this formula uses io_size and rec_length to get to column data
$colIndex = ( (  (($io_size + $rec_len)/$io_size)   + 1) * $io_size ) + 258;
$colIndex -= 0x3000;   // this is not documented but seems to work!

// find number of columns in the table
//------------------------------------------------- 
echo PHP_EOL."Col data at 0x".dechex($colIndex).PHP_EOL;
$numCols = ord($contents[$colIndex]);

//Extract the column names
//--------------------------------------
$colNameIndex = $colIndex+0x50;   //0X50 by inspection
echo "Col names at 0x".dechex($colNameIndex).PHP_EOL;
$cols=array();
for ($col = 0; $col < $numCols; $col++){
    $nameLen = ord($contents[$colNameIndex++]);          // name length is at ist posn
    $cols[]['ColumnName']= substr($contents,$colNameIndex,$nameLen-1); // read the name
    $colNameIndex+=$nameLen+2;        // skip ahead to next name (2 byte gap after \0)
}
print_r($cols);

这应该可以帮助您入门.如果您认为往正确的方向前进，我会在接下来的几天有时间时补充说明.

This should get you started. I will add to this when I have time in the coming days if you think is heading in the right direction.

编辑..我更新了代码，以便它适用于任何.frm文件(来自Table).确保可以在 https://上找到免费的工具来恢复mySQL(基于innoDB引擎). github.com/twindb/undrop-for-innodb .阅读了代码和相关的博客之后，他们没有使用.FRM文件进行恢复.相同的表信息也存储在innoDB词典中，他们正在使用它来恢复表格式等.

EDIT. I updated the code so it should work for any .frm file (from Table). For sure there is a free tool to recover mySQL (based on innoDB engine) available at https://github.com/twindb/undrop-for-innodb. Having read through the code and the associated blogs, they are not using the .FRM files for recovery. The same table information is also stored in the innoDB dictionary and they are using this to recover table formats etc.

还有一种读取.FRM文件内容的方法.此处 https://twindb.com/如何从frm-files-online/恢复表结构.但是，他们正在使用mySQL读取.frm文件并从那里重新创建表.

There is also a way to read the .FRM files content. This described here https://twindb.com/how-to-recover-table-structure-from-frm-files-online/. However, they are using mySQL to read the .frm files and recreating tables from there.

这里还有一个实用程序，其中包含一组实用程序 https://www.mysql.com/why-mysql/presentations/mysql-utilities/包含一个.frm阅读器.这是由Oracle制作的，Oracle是唯一知道.frm文件格式的人！该实用程序是免费的，因此您可以下载它.

There is also a utility a package of utilities found here https://www.mysql.com/why-mysql/presentations/mysql-utilities/ that contains a .frm reader. This was made by Oracle, who are the only people who know the format of the .frm files! The utility is free so you can download it.

Oracle发布有关.frm文件格式的一些信息 https://dev.mysql.com/doc/internals/zh-CN/frm-file-format.html ，但这既不完整，也不正确！请参阅前面的堆栈问题. https://dba.stackexchange.com/questions/208198/mysql-frm-file-format-h​​ow-to-extract-column-info

Oracle publish some information on the format of .frm files https://dev.mysql.com/doc/internals/en/frm-file-format.html, but it is both incomplete and incorrect! See this previous Stack question. https://dba.stackexchange.com/questions/208198/mysql-frm-file-format-how-to-extract-column-info

现在，毕竟，如果您仍然想尝试自己分析.frm文件是为了娱乐还是学习，那么您需要耐心并花一些时间来整理一个非常复杂的结构.如果您想继续尝试就可以了，但是请将您的.FRM文件(发送至[e-mail])发送给我，这样我就可以检查出来，几天后我将向您发送一些PHP代码，该代码将提取一些其他信息，例如数据类型和显示尺寸.

Now after all that if you still want to try to parse the .frm files yourself for fun or for learning, then you need to be patient and spend time unravelling quite a complicated structure. If you want to keep trying that is OK but send me your .FRM file ( to [e-mail]) so I can check it out and I will send you some PHP code in a few days that will extract some additional information like datatype and display sizes.

这篇关于如何使用PHP从.frm文件中获取表结构?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！