如何在MySQL中将类型转换为bigint？

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

CAST()似乎只适用于BINARY,CHAR,DATE; DATETIME,DECIMAL,TIME,SIGNED,UNSIGNED。

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

我需要将十六进制字符串转换为bigint,也就是说,我想要:

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

CONV()返回一个字符串,这就是我尝试转换它的原因。我有2个用途

• 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 (?)

  插入数据,例如INSERT INTO(foo)SELECT CONV(bar,16,10)FROM ...这里foo是一个bigint列,bar为varchar。也许我可以将select语句作为一个字符串,并让MySQL处理它(?)

• 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.

  返回数据,客户端将动态学习列的数据类型,SELECT CONV(bar,16,10)是不好的,因为客户端将它作为字符串处理。

2 个解决方案

#1

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

#2

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.

什么似乎是问题?我已经在64位和32位系统上测试了这种转换。工作正常。请注意,您可以将数字视为十六进制,而不是进行十六进制到二进制转换。

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