delphi - 如何将MySQL Blob字段转换为插入语句的字符串

Question

我正在为我的应用程序创建“备份/恢复”功能，但遇到问题将 blob 转换为 sql 语句。我有这个代码:

function GetDatasetValues(dataSet: TDataSet): string;
var
  i: Integer;
  ms: TStream;
  ss: TStringStream;
  sTemp : string;
begin
  Result := '';

  for i := 0 to Pred(dataSet.FieldCount) do
  begin

    if not dataSet.Fields[i].IsNull then
    begin

      case dataSet.Fields[i].DataType of
        TFieldType.ftGraphic, TFieldType.ftBlob, TFieldType.ftStream:
        begin
          ms := TMemoryStream.Create;
          try
            TBlobField(dataSet.Fields[I]).SaveToStream(ms);
            ms.Position := 0;
            ss := TStringStream.Create;
            try
              TNetEncoding.Base64.Encode(ms, ss);

              Result := Result + ss.DataString + ',';
            finally
              ss.Free;
            end;
          finally
            ms.Free;
          end;
        end;
// more codes...

结果是:

insert into `tb1`(f_int,f_varchar,f_blob) values 
(1,'1',e1xydGYxXGFuc2lcZGVmZjB7XGZvbnR0Ymx7XGYwXGZuaWwgXCdjZVwnYTJcJ2M4XCdlZFwnZDFc
J2M1XCdiYVwnZGE7fXtcZjFcZm5pbFxmY2hhcnNldDEzNCBcJ2NlXCdhMlwnYzhcJ2VkXCdkMVwn
YzVcJ2JhXCdkYTt9fQ0Ke1xjb2xvcnRibCA7XHJlZDMyXGdyZWVuMzFcYmx1ZTUzO30NCntcKlxn
ZW5lcmF0b3IgTXNmdGVkaXQgNS40MS4yMS4yNTEwO31cdmlld2tpbmQ0XHVjMVxwYXJkXGNmMVxs
YW5nMjA1MlxmMFxmczE4IFQwMzE3MCB4eSBTaW1vbiBcZjFcJ2I2XCdhM1wnYjVcJ2IxXCdjM1wn
YTggXCdiN1wnYzVcJ2NhXCdkNlwnYzhcJ2E1XCdkN1wnZjZcJ2EzXCdhY1wnYzFcJ2VkXCdjZFwn
ZTJcJ2QwXCdjMlwnY2FcJ2MwXCdiZFwnZTdcJ2IyXCdiYlwnZDNcJ2MzXCdiNVwnYzhcJ2I1XCdi
ZFwnYzhcJ2FiXCdiMlwnYmZcJ2JiXCdkOFwnYjFcJ2JlXCdkNFwnZDlcJ2I3XCdkNlwnYmFcJ2Vj
XHBhcg0KXHBhcg0KVDAzMTcwIFwnYzFcJ2JkXCdiOFwnZjZcJ2NhXCdjMlwnYzdcJ2U5XHBhcg0K
XHBhcg0KMVwnYTFcJ2EyXCdkMFwnYzJcJ2NhXCdjMFwnYmRcJ2U3XCdiMlwnYmJcJ2QzXCdjM1wn
YjVcJ2M4XCdiNVwnYmRcJ2M4XCdhYlwnYjJcJ2JmXCdiYlwnZDhcJ2IxXCdiZVwnZDRcJ2Q5XCdi
N1wnZDZcJ2JhXCdlY1wnYjBcJ2M5XCdhM1wnYWNcJ2JmXCdjOVwnZDJcJ2Q0XCdiZlwnYWFcJ2Nh
XCdiY1wnYzJcJ2JkXCdkMFwnZjhcJ2M0XCdjM1wnYjNcJ2Y2XCdkMlwnYmJcJ2IyXCdiZlwnYjdc
J2Q2XCdkN1wnZjZcJ2I3XCdkNlwnYmFcJ2VjXCdhM1wnYWNcJ2QyXCdiYlwnYjJcJ2JmXCdiN1wn
ZDZcJ2JiXCdiOVwnY2RcJ2I2XCdkN1wnY2FcJ2JmXCdlZVwnYTNcJ2FjXCdjNFwnZTNcJ2M4XCdh
NVwnY2ZcJ2ViXCdjZlwnZWJcJ2I3XCdiZFwnYjBcJ2I4XCdhMVwnYTNccGFyDQpccGFyDQoyXCdh
MVwnYTJ4eVwnY2ZcJ2VlXCdjNFwnYmZcJ2EzXCdhY1wnYjdcJ2M1XCdjYVwnZDZcJ2JkXCdiYlwn
YjhcJ2Y4XCdjNFwnZTNcJ2JhXCdjZFwnYjZcJ2EzXCdiNVwnYjFcJ2MzXCdhOFwnYzhcJ2E1XCdk
N1wnZjZcJ2EzXCdhY1wnYmZcJ2M5XCdkMlwnZDRcJ2I0XCdmM1wnZDZcJ2MyXCdkMFwnYjRcJ2I4
XCdmNlwnYjdcJ2EyXCdkNVwnYjlcJ2I3XCdiZFwnYjBcJ2I4XCdiOFwnZjhcJ2NlXCdkMlwnYTFc
J2EzXHBhcg0KXGYwXHBhcg0KfQ0KAA==);

转换后的blob字段在mysql浏览器中执行失败。它说语法错误。当尝试使用 heidsql 备份数据库时备份功能，它产生的sql是:

insert INTO `tb1` (`f_int`, `f_varchar`, `f_blob`) VALUES
    (1, '1', _binary 0x7B5C727466315C616E73695C64656666307B5C666F6E7474626C7B5C66305C666E696C205C2763655C2761325C2763385C2765645C2764315C2763355C2762615C2764613B7D7B5C66315C666E696C5C6663686172736574313334205C2763655C2761325C2763385C2765645C2764315C2763355C2762615C2764613B7D7D0D0A7B5C636F6C6F7274626C203B5C72656433325C677265656E33315C626C756535333B7D0D0A7B5C2A5C67656E657261746F72204D7366746564697420352E34312E32312E323531303B7D5C766965776B696E64345C7563315C706172645C6366315C6C616E67323035325C66305C66733138205430333137302078792053696D6F6E205C66315C2762365C2761335C2762355C2762315C2763335C276138205C2762375C2763355C2763615C2764365C2763385C2761355C2764375C2766365C2761335C2761635C2763315C2765645C2763645C2765325C2764305C2763325C2763615C2763305C2762645C2765375C2762325C2762625C2764335C2763335C2762355C2763385C2762355C2762645C2763385C2761625C2762325C2762665C2762625C2764385C2762315C2762655C2764345C2764395C2762375C2764365C2762615C2765635C7061720D0A5C7061720D0A543033313730205C2763315C2762645C2762385C2766365C2763615C2763325C2763375C2765395C7061720D0A5C7061720D0A315C2761315C2761325C2764305C2763325C2763615C2763305C2762645C2765375C2762325C2762625C2764335C2763335C2762355C2763385C2762355C2762645C2763385C2761625C2762325C2762665C2762625C2764385C2762315C2762655C2764345C2764395C2762375C2764365C2762615C2765635C2762305C2763395C2761335C2761635C2762665C2763395C2764325C2764345C2762665C2761615C2763615C2762635C2763325C2762645C2764305C2766385C2763345C2763335C2762335C2766365C2764325C2762625C2762325C2762665C2762375C2764365C2764375C2766365C2762375C2764365C2762615C2765635C2761335C2761635C2764325C2762625C2762325C2762665C2762375C2764365C2762625C2762395C2763645C2762365C2764375C2763615C2762665C2765655C2761335C2761635C2763345C2765335C2763385C2761355C2763665C2765625C2763665C2765625C2762375C2762645C2762305C2762385C2761315C2761335C7061720D0A5C7061720D0A325C2761315C27613278795C2763665C2765655C2763345C2762665C2761335C2761635C2762375C2763355C2763615C2764365C2762645C2762625C2762385C2766385C2763345C2765335C2762615C2763645C2762365C2761335C2762355C2762315C2763335C2761385C2763385C2761355C2764375C2766365C2761335C2761635C2762665C2763395C2764325C2764345C2762345C2766335C2764365C2763325C2764305C2762345C2762385C2766365C2762375C2761325C2764355C2762395C2762375C2762645C2762305C2762385C2762385C2766385C2763655C2764325C2761315C2761335C7061720D0A5C66305C7061720D0A7D0D0A00);

并且可以在mysql broser中执行。那么如何在mysql浏览器中将BLOB转换为可执行的sql字符串呢？或者如何实现 heidisql 方式将 blob 转换为字符串？

Answer 1

这应该会给你一个好的开始。编码不是base64，只是将字节转换为十六进制字符串。

function BinToHex(Buffer: array of BYTE; BufSize: Integer):string;
var
  I: Integer;
  const Digits:array[0..15] of char='0123456789abcdef';
begin
  for I := 0 to BufSize - 1 do
    begin
      Result := Result + digits[Buffer[i] shr 4]+digits[Buffer[i] and $0F];
   end;
end;


function GetDatasetValues(dataSet: TDataSet): ansistring;
var
  i: Integer;
  ms: TStream;
  ss: TStringStream;
  sTemp : string;
  bytes : TBYTES;
begin
  Result := '';

  for i := 0 to Pred(dataSet.FieldCount) do
  begin

    if not dataSet.Fields[i].IsNull then
    begin

      case dataSet.Fields[i].DataType of
        TFieldType.ftGraphic, TFieldType.ftBlob, TFieldType.ftStream:
        begin
          ms := TMemoryStream.Create;
          try
            TBlobField(dataSet.Fields[I]).SaveToStream(ms);
            ms.Position := 0;
            ss := TStringStream.Create;
            try
              ms.Read(bytes,ms.Size);
              Result := BinToHex(bytes,ms.Size);
            finally
              ss.Free;
            end;
          finally
            ms.Free;
          end;
        end;
      end;
    end;
  end;
end;