如何在java中将UUID保存为二进制（16） [英] How to save a UUID as binary(16) in java

问题描述

我有一个表TestTable，列ID为二进制（16），名称为varchar（50）

I have a table TestTable with columns ID as binary(16) and name as varchar(50)

我一直在尝试将有序的UUID存储为PK比如本文中的以优化的方式存储UUID

I've been trying to store an ordered UUID as PK like in this article Store UUID in an optimized way

我看到UUID作为HEX（blob）保存在数据库中

I see the UUID is saved in database as HEX (blob)

所以我想要从java保存此ID但我收到此错误

So I want to save this ID from java but I am getting this error

数据截断：第1行的列'ID'的数据太长

Data truncation: Data too long for column 'ID' at row 1

我目前正在使用库sql2o与mysql进行交互

I am currently using the library sql2o to interact with mysql

所以基本上这是我的代码

So basically this is my code

String suuid = UUID.randomUUID().toString();
String partial_id = suuid.substring(14,18) + suuid.substring(9, 13) + suuid.substring(0, 8) + suuid.substring(19, 23) + suuid.substring(24)
String final_id = String.format("%040x", new BigInteger(1, partial_id.getBytes()));
con.createQuery("INSERT INTO TestTable(ID, Name) VALUES(:id, :name)")
        .addParameter("id", final_id)
        .addParameter("name", "test1").executeUpdate();

部分ID应该是这样的 11d8eebc58e0a7d796690800200c9a66

The partial id should be something like this 11d8eebc58e0a7d796690800200c9a66

我在没有问题的情况下在mysql中尝试了这个语句

I tried this statement in mysql without issue

insert into testtable(id, name) values(UNHEX(CONCAT(SUBSTR(uuid(), 15, 4),SUBSTR(uuid(), 10, 4),SUBSTR(uuid(), 1, 8),SUBSTR(uuid(), 20, 4),SUBSTR(uuid(), 25))), 'Test2');

但是当我删除unhex函数时出现了同样的错误。那么如何将正确的ID从Java发送到mysql？

But I got the same error when I remove the unhex function. So how can I send the correct ID from Java to mysql?

更新

我根据 David Ehrmann 的答案解决了我的问题。但在我的情况下，我使用tomcat中的HexUtils将我的已排序的UUID字符串转换为bytes []：

I solved my problem inspired on the answer of David Ehrmann. But in my case I used the HexUtils from tomcat to transform my sorted UUID string into bytes[]:

byte[] final_id = HexUtils.fromHexString(partial_id);

推荐答案

尝试将其存储为字节：

UUID uuid = UUID.randomUUID();
byte[] uuidBytes = new byte[16];
ByteBuffer.wrap(uuidBytes)
        .order(ByteOrder.BIG_ENDIAN)
        .putLong(uuid.getMostSignificantBits())
        .putLong(uuid.getLeastSignificantBits());

con.createQuery("INSERT INTO TestTable(ID, Name) VALUES(:id, :name)")
    .addParameter("id", uuidBytes)
    .addParameter("name", "test1").executeUpdate();

一点解释：你的表正在使用BINARY（16），所以将UUID序列化为原始bytes是一种非常简单的方法。 UUID本质上是128位整数，带有一些保留位，因此该代码将其写为big-endian 128位int。 ByteBuffer只是将两个long转换为字节数组的简单方法。

A bit of an explanation: your table is using BINARY(16), so serializing UUID as its raw bytes is a really straightforward approach. UUIDs are essentially 128-bit ints with a few reserved bits, so this code writes it out as a big-endian 128-bit int. The ByteBuffer is just an easy way to turn two longs into a byte array.

现在实际上，所有转换工作和头痛都不值20字节你每行保存。

Now in practice, all the conversion effort and headaches won't be worth the 20 bytes you save per row.

这篇关于如何在java中将UUID保存为二进制（16）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！