英文:
How to update a row without any data to uniquely identify the row?
问题
我有一张表，只有一个文本列（还有一些其他列，但在这里不相关）。该列中的值可能会有重复。该表没有任何主键或其他唯一数据来逐个标识每一行。
之前使用Blowfish算法对文本进行了加密。但现在我们计划改用AES加密文本。
因此，我们从表中获取一批行，然后将其传递给我们的Java代码，该代码将从Blowfish转换为AES。但是，由于我们没有唯一的键来标识行，我如何确保我们正在更新正确的列呢？
我能想到两种方法来解决这个问题。
1）在数据库中添加自增的主键/唯一键。虽然这是理想的做法，但会导致我们的应用程序需要进行大量的代码更改。
2）以某种奇怪的方式使用rownum进行更新。我担心这种方法会影响数据完整性。
是否有其他方法可以解决这个问题？或者什么是最佳做法？
我们正在使用Oracle SQL数据库和JDBCTemplate。
英文:
I have a table with one single text column(there are some other columns. But they are irrelevant here). The values in this column can contain duplicates. The table doesn't have any primary key or other unique data to individually identify each row.
The text was previously encrypted using Blowfish algorithm. But we are now planning to encrypt the text using AES.
So we take a batch of rows from the table and then run it through our java code, which does the conversion from Blowfish to AES. But, since we don't have a unique key to identify the row, how do I make sure that we are updating the correct column.
I can think of two ways to do this.
- Add a auto-incrementing primary key/unique key to the DB. Which although is ideal, will result in lots of code changes in our application.
- Do the update using rownum, in some weird way. I am concerned about the data-integrity with this approach.
Is there any other way to do this? or What the best approach?
We are using an Oracle SQL DB and JDBCTemplate.
答案1
得分: 0
在您描述的情况下，我不确定您为什么关心；只需下载该行，解密，删除，重新加密并插入新值。如果多行发生更改，那就这样吧；反正您无法知道它们之前是什么样的，而且您还说行上没有其他唯一标识它的内容，所以系统必须容忍信息丢失。
我的意思是，假设您有两个文本值：
你好
再见
并且您对它们进行了加密，它们变成了相同的内容：
1235
1235
要么其中一个现在丢失了，解密1235将始终产生“你好”，要么解密知道1235将产生“你好”或“再见” - 您可以通过新的算法运行这两个“你好”/“再见”，产生唯一的值111和222，因此您可以安全地从表中删除所有1235（delete from t where enctext = 1235）并插入111和222。
在表中重复使用1235没有任何帮助（您不能说“这个1235解密为你好，但那个1235解密为再见”，因为那么其他某些知识必须已经被添加了- 这些知识也可以唯一标识行。如果情况如您所说，您不需要担心哪个是哪个；不管怎样，您都可以检索到原始数据进行重新加密，或者您就无法检索到。
英文:
In the scenario you've described I'm not sure why you care; just download the row, decrypt it, delete it, reencrypt it and insert the new values. If multiple rows change as a result then so be it; you can't tell what they were before anyway and you say there is nothing else on the row that uniquely identifies it so the system must be tolerant of losing information
What I mean by this is, say you had two text values:
hello
goodbye
And you encrypted them and they became the same:
1235
1235
It's either that one of them is now lost, and decrypting 1235 will always produce "hello" or its that the decrypted knows that 1235 will either produce "hello" or "goodbye" - you can run both of these "hello"/"goodbye" through your new algorithm that does produce unique values 111 and 222, so you can safely erase all the 1235 from the table (delete from t where enctext = 1235) and insert 111 and 222..
Having duplicated 1235 in the table wasn't helpful in any way( you couldn't say "this 1235 decrypts to hello, but that 1235 decrypts to goodbye" because then some other piece of knowledge must have been added- knowledge which can also uniquely identify the row. If things are as you say you don't need to be concerned about which was what; either was both and you can either retrieve to original data for reencryption or you can't.
专注分享java语言的经验与见解，让所有开发者获益！
评论