In a mysql data backup and recovery operation, a problem was encountered: after using sqlyog for data backup, and then recovering the data on another server, the "'" separator was devoured due to the varbinary field in one of the tables (due to the problem of binary encoding, the quotation mark separator was treated as part of the data), so that the data could not be imported normally.
Some text tools were used for processing, but they were unsuccessful, some of which could be recognized, but automatically modified the binary encoded data, and some would insert other binary data, resulting in still unreadable.
So consider other methods: read the data and spell the SQL statement, and then import it. The specific methods are:
(1) Use the HEX function to read the data when exporting, and convert the binary data into a hexadecimal string;
select HEX(binField) from testTable;
(2) Use the UNHEX function to convert the hexadecimal string into the binary data import database during import;
insert into testTable binField values(UNHEX(@hexstr));
The following code demonstrates the functionality of HEX and UNHEX:
SELECT HEX('this is a test str') and the result of the query is: 746869732069732061207465737420737472
SELECT UNHEX('746869732069732061207465737420737472'), and the result of the query is: this is a test str
You can also read the hexadecimal character directly, adding a 0x prefix to the string:
SELECT 0x746869732069732061207465737420737472, the query result is: this is a test str
In addition, you can also use binary import and export methods to back up and restore data. There is no discussion here.
|
Posted on 6/13/2016 10:27:47 PM
|
|
|
