How do I cast a type to a bigint in MySQL?

Question

CAST() seems to only work for BINARY,CHAR,DATE;DATETIME,DECIMAL,TIME,SIGNED,UNSIGNED.

I need to convert a hex string to a bigint, that is, I'd want:

SELECT CAST(CONV("55244A5562C5566354',16,10) AS BIGINT)

CONV() returns a string, so that's why I'm trying the convert it. I have 2 uses for this

• Inserting data, e.g. INSERT INTO a(foo) SELECT CONV(bar,16,10) FROM ... Here foo is a bigint column, bar a varchar. Perhaps I could get away with the select statement being a string and let MySQL take care of it (?)

• Returning data where the client will dynamically learn the data type of the column, SELECT CONV(bar,16,10) is no good as the client will handle it as a string.

Answer 1

What seems to be the problem? I've tested this conversion both on 64-bit and 32-bit system. Works fine. Note, that instead of doing hex to bin conversion, you can just treat the number as hexadecimal.

mysql> SELECT CAST(X'55244A5562C5566354' AS UNSIGNED);
+-----------------------------------------+
| CAST(X'55244A5562C5566354' AS UNSIGNED) |
+-----------------------------------------+
|                     2614996416347923284 |
+-----------------------------------------+
1 row in set (0.00 sec)

Answer 2

SELECT CAST(CONV('55244A5562C5566354',16,10) AS UNSIGNED INTEGER);