在数据库中存储十六进制和十六进制字符的最佳数据类型 [英] Best datatype to store hexidecimal and hex characters in the database

问题描述

我正在使用以太坊api.我想将api中的信息存储到mysql表中.

地址数据如下:

0x3f5ce5fbfe3e9af3971dd833d26ba9b5c936f0be
0x1d80982502f3bb75654df13aa32bbd5ac9cab7d6
0xaf13bbdbe1ff53c2df7109a53c217320d2d76ee2
...

我一直在为这些字符使用varchar列.是否有更好的数据类型?我在想也许varbinary，但我不知道是否有任何优势.缺点是sql代码比较杂乱，就像我对HEX()和UNHEX()一样.

解决方案

对我而言，不存在特定类型，您需要在文本和二进制之间进行选择:

CHAR(40) :( 这里的字符集并不重要)

• 优点:简单
• 缺点:您需要更多的空间磁盘(〜+ 100％)
• 缺点:您可能会存储无效的non hexa数据

BINARY(20):

• 优点:减少磁盘空间
• 优点:您不能存储无效数据
• 缺点:您需要转换(如果需要，请参见hexa值)

对我来说数据一致性是最重要的一点:我更喜欢二进制选项.

• unhex('FF')== unhex('ff')但'FF'<>'ff'
• 尝试存储unhex('zz')会引发错误，文本不会引发错误

而且Hex/unHex是非常简单的功能

提示:您可以将数据存储在BINARY列中，并创建一个hexa视图以轻松查看六边值.

这里是在二进制区域 http://rextester.com/SEV11235 中存储数据的示例>

I'm using the Ethereum api. I want to store the information from the api into a mysql table.

The address data looks like:

0x3f5ce5fbfe3e9af3971dd833d26ba9b5c936f0be
0x1d80982502f3bb75654df13aa32bbd5ac9cab7d6
0xaf13bbdbe1ff53c2df7109a53c217320d2d76ee2
...

I've been using just varchar column for these characters. Are there a better data type? I'm thinking maybe varbinary but I don't know if there's any advantages. The disadvantage is that the sql code will be messier as I will have to HEX() and UNHEX().

解决方案

For me no specific type exists, you need to choose between text and binary :

CHAR(40) : (charset is not important here)

• pros : simplicity
• cons : you need more space disk (~+100%)
• cons : you risk to store invalid non hexa data

BINARY(20) :

• pros : reduce space disk
• pros : you can't store invalid data
• cons: you need to convert (if you need see hexa value)

For me data consistency is the most important point : I prefer binary option.

• unhex('FF') == unhex('ff') but 'FF' <> 'ff'
• trying to store unhex('zz') raises an error, with text no error raised)

Moreover Hex/unHex are very simple function

Hint : You can store your data in BINARY column and create an hexa view to see easily hexa value.

Here is an example to store data in binary zone http://rextester.com/SEV11235

这篇关于在数据库中存储十六进制和十六进制字符的最佳数据类型的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！