Cara menggunakan mysql unhex

Question

I've got a table which contains a varchar(1024) field, which in that contains strings which has hex encoded strings. This table is filled automatically and I have to provide an SP to allow users to download this, therefore, I need to change the hex back into human readable form.

If I manually run this statement (taking the Hex data from the field), it works just fine:

SELECT X'5468697320697320612074657374206D6573736167652031323334353637383930';

But I cannot find a working example of getting this to work when calling the field/column name. I've found a few examples, but these just return a null or 0.

I've tried X and UnHex() and neither give me a result.

Where am I going wrong?

Thanks

EDIT:

Okay, after doing a bit more testing, it appears it must be the way it's being written to the database in the first place.

It's a Classic ASP page that calls an SP, which creates the database entry. In this method, the write to the DB works, and I can see the HEX content in the field. Copying the content of the field, and putting this into a Select X'123123' gives me the ASCII values, as I want.

If I try this as a Select, this fails, giving me a zero or Null return.

SELECT Message_Body_Hex, UNHEX(Message_Body_Hex) FROM messages_inbound

returns:

Message_Body_Hex......unhex(Message_Body_Hex)

417265612032........(NULL)

Still confused! :)

Menambah jawaban oleh ThaBadDawg, gunakan fungsi-fungsi praktis ini (terima kasih kepada kolega saya yang lebih bijak) untuk mendapatkan dari string 36 panjang kembali ke array byte 16.

DELIMITER $$

CREATE FUNCTION `GuidToBinary`(
    $Data VARCHAR(36)
) RETURNS binary(16)
DETERMINISTIC
NO SQL
BEGIN
    DECLARE $Result BINARY(16) DEFAULT NULL;
    IF $Data IS NOT NULL THEN
        SET $Data = REPLACE($Data,'-','');
        SET $Result =
            CONCAT( UNHEX(SUBSTRING($Data,7,2)), UNHEX(SUBSTRING($Data,5,2)),
                    UNHEX(SUBSTRING($Data,3,2)), UNHEX(SUBSTRING($Data,1,2)),
                    UNHEX(SUBSTRING($Data,11,2)),UNHEX(SUBSTRING($Data,9,2)),
                    UNHEX(SUBSTRING($Data,15,2)),UNHEX(SUBSTRING($Data,13,2)),
                    UNHEX(SUBSTRING($Data,17,16)));
    END IF;
    RETURN $Result;
END

$$

CREATE FUNCTION `ToGuid`(
    $Data BINARY(16)
) RETURNS char(36) CHARSET utf8
DETERMINISTIC
NO SQL
BEGIN
    DECLARE $Result CHAR(36) DEFAULT NULL;
    IF $Data IS NOT NULL THEN
        SET $Result =
            CONCAT(
                HEX(SUBSTRING($Data,4,1)), HEX(SUBSTRING($Data,3,1)),
                HEX(SUBSTRING($Data,2,1)), HEX(SUBSTRING($Data,1,1)), '-', 
                HEX(SUBSTRING($Data,6,1)), HEX(SUBSTRING($Data,5,1)), '-',
                HEX(SUBSTRING($Data,8,1)), HEX(SUBSTRING($Data,7,1)), '-',
                HEX(SUBSTRING($Data,9,2)), '-', HEX(SUBSTRING($Data,11,6)));
    END IF;
    RETURN $Result;
END
$$

CHAR(16) sebenarnya adalah BINARY(16), pilih rasa yang Anda sukai

Untuk mengikuti kode dengan lebih baik, ambil contoh yang diberikan angka yang dipesan GUID di bawah ini. (Karakter ilegal digunakan untuk tujuan ilustrasi - setiap tempat karakter unik.) Fungsi akan mengubah urutan byte untuk mencapai urutan bit untuk pengelompokan indeks superior. Panduan yang diperintahkan ditampilkan di bawah contoh.

12345678-9ABC-DEFG-HIJK-LMNOPQRSTUVW
78563412-BC9A-FGDE-HIJK-LMNOPQRSTUVW

Tanda hubung dihapus:

123456789ABCDEFGHIJKLMNOPQRSTUVW
78563412BC9AFGDEHIJKLMNOPQRSTUVW

In MySQL, you can unhex a string using the UNHEX() function. But you can’t unhex a number with that function.

To unhex a number in MySQL, use the CONV() function instead.

The CONV() function allows you to convert numeric values between different numbering systems. For example, you can convert between say decimal and binary, octal to decimal, or, more relevant to this article, between hexadecimal and decimal.

Syntax

The syntax goes like this:

CONV(N,from_base,to_base)

Where N is the number you want to convert, from_base is the base that the number is in, and to_base is the base you want to convert it to.

Example

Here’s an example to demonstrate:

SELECT CONV('F',16,10);

Result:

+-----------------+
| CONV('F',16,10) |
+-----------------+
| 15              |
+-----------------+

In this case, we convert the number F from base 16 (hexadecimal) to base 10 (decimal). So we can see that F in hexadecimal equals 15 in decimal.

Here’s another example using a larger number:

SELECT CONV('FCA3B',16,10);

Result:

+---------------------+
| CONV('FCA3B',16,10) |
+---------------------+
| 1034811             |
+---------------------+

kode mysql MySQL UNHEX online MySQL UNHEX UUID Unhex Python UNHEX Java CASE WHEN MySQL

Cara menggunakan mysql unhex

Syntax

Example

Pos Terkait

Periklanan

BERITA TERKINI

Toplist Popular

Periklanan

Terpopuler

Token Data

Periklanan

Tentang Kami

Legal

Dukungan

Social