使用Java和JDBC将无符号的64位数字插入到BigInt MySQL列中 [英] Inserting unsigned 64-bit number into BigInt MySQL column using Java and JDBC

问题描述

我搜索了该问题的答案，但仅找到模糊的答案.这是问题所在: 我在MySQL中有一个示例表: mytable 时间时间戳 数据BigInt(20)unsigned

I searched for answer to this question and only found vague answers. Here is the problem: I have a sample table in MySQL: mytable time Timestamp data BigInt(20) unsigned

选择数据字段，使其可以采用2 ^ 64的最大值:18446744073709551616(20位数字，因此是BigInt(20)).

Data field is chosen so that it can take in 2^64 max value: 18446744073709551616 (20 digits and hence BigInt(20)).

我有一个csv文件，其中包含无符号的64位整数.

I have a csv file which contains unsigned 64-bit integers.

现在我正在使用Java和JDBC插入此数据并遇到问题，因为Java的unsigned long并不长.因此，我尝试仅传递字符串-但失败.

Now I am using Java and JDBC to insert this data and running into issues because Java does not have unsigned long. So I tried just passing string - but it fails.

st = conn.prepareStatement("INSERT INTO pawan_table (mytime, data) VALUES(?, ?)");
st.setTimestamp(1, new Timestamp(86400000+i*1000));
st.setString(2, "18446744073709551616");
st.execute();

我还尝试了Java中的BigInteger类-但JDBC没有采用该类型的方法.我只能将BigInteger转换为带符号的"long"，这不是我想要的.例如:

I also tried BigInteger class in Java - but JDBC does not have a method which takes that type. I can only convert BigInteger to "long" which is signed and that is not what I want. For example:

BigInteger bi = new BigInteger(string_from_csv_file);
st.setLong(2, bi.longValue());

有趣的是，MySQL Workbench可以插入此数据-但它是用c#编写的！而且我需要用Java编写代码！

Interesting thing is that MySQL Workbench can insert this data - but it is written in c#! and I need to write my code in Java!

Java程序员如何在MySQL BigInt(20)列中插入无符号的64位数字?

How do Java programmers insert unsigned 64-bit number in MySQL BigInt(20) column?

推荐答案

这是解决方案:

如果您不使用prepare语句，而只是使用字符串SQL语句进行插入，则无需担心，因为JDBC只是将您的语句移至MySQL服务器，而MySQL服务器可以将字符串正确解析为无符号64号(BigInt- 20).

If you do not use prepare statement and are just inserting using a string SQL statement, you have nothing to worry about because JDBC simply moves your statement to MySQL server and MySQL server can correctly parse the string to unsigned 64 number (BigInt-20).

如果使用prepare语句，则可能会遇到麻烦.如果您这样做:

If you use a prepare statement, then you are in trouble. If you do:

BigInteger bi = new BigInteger("18446744073709551615"); // max unsigned 64-bit number
statement.setObject(2, bi, Types.BIGINT);
statement.execute();

您将获得MySqlDataTruncation异常，因为JDBC希望将其截短以进行长时间签名.

you will get an MySqlDataTruncation exception because JDBC wants to truncate this to signed long.

如果您这样做:

BigInteger bi = new BigInteger(new Long(Long.MAX_VALUE).toString());
statement.setObject(2, bi, Types.BIGINT);
statement.execute();

这将起作用，因为该值在Java的有符号长度之内.

This will work because the value is within Java's signed long.

因此始终可行的解决方法是:

So workaround which always works is:

BigInteger bi = new BigInteger("18446744073709551615"); // max unsigned 64-bit number
statement.setString(2, bi.toString());
statement.execute();

您最终将数据作为字符串传递，然后让MySQL服务器正确解析它.

You end up passing the data as a string and let MySQL server parse it properly.

有人应该修复JDBC驱动程序，因为许多统计应用程序中都会出现无符号的64位数字.

Someone should fix the JDBC driver because unsigned 64-bit numbers keep coming up in a lot of statistical applications.

这篇关于使用Java和JDBC将无符号的64位数字插入到BigInt MySQL列中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！