SQL - How do you compare a CLOB

问题:

in a DB2 trigger, I need to compare the value of a CLOB field. Something like:

IF OLD_ROW.CLOB_FIELD != UPDATED_ROW.CLOB_FIELD 

but "!=" does not work for comparing CLOBs.

What is the way to compare it?

Edited to add:

My trigger needs to do some action if the Clob field was changed during an update. This is the reason I need to compare the 2 CLOBs in the trigger code. I'm looking for some detailed information on how this can be done

回答1:

In Oracle 10g you can use DBMS_LOB.compare() API.

Example:

select * from table t where dbms_lob.compare(t.clob1, t.clob2) != 0

Full API:

DBMS_LOB.COMPARE (
   lob_1            IN BLOB,
   lob_2            IN BLOB,
   amount           IN INTEGER := 4294967295,
   offset_1         IN INTEGER := 1,
   offset_2         IN INTEGER := 1)
  RETURN INTEGER;

DBMS_LOB.COMPARE (
   lob_1            IN CLOB  CHARACTER SET ANY_CS,
   lob_2            IN CLOB  CHARACTER SET lob_1%CHARSET,
   amount           IN INTEGER := 4294967295,
   offset_1         IN INTEGER := 1,
   offset_2         IN INTEGER := 1)
  RETURN INTEGER; 

DBMS_LOB.COMPARE (
   lob_1            IN BFILE,
   lob_2            IN BFILE,
   amount           IN INTEGER,
   offset_1         IN INTEGER := 1,
   offset_2         IN INTEGER := 1)
  RETURN INTEGER;

回答2:

Calculate the md5 (or other) hash of the clobs and then compare these. Initial calculation will be slow but comparison is fast and easy. This could be a good method if the bulk of your data doesn't change very often.

One way to calculate md5 is through a java statement in your trigger. Save these in the same table (if possible) or build a simple auxiliary table.

回答3:

Iglekott's idea is a good one, with a caveat:

Be careful with compare-by-hash if your data is likely to get attacked. It is not currently computationally feasible to generate a hash collision for a specific MD5 value, but it is possible to generate two different inputs that will produce the same MD5 (therefore not triggering your code). It is also possible to generate two different strings with the same prefix that hash to the same value.

If that kind of attack can lead to the integrity of your system being compromised, and that's a concern, you want to explore other options. The easiest would be simply switching the hash functions, SHA-2 does not have currently known vulnerabilities.

If this isn't a concern -- hell, go with CRC. You aren't going for cryptographic security here. Just don't go with a cryptographically weak function if this stuff is getting installed on a smartbomb, 'mkay? :-)

回答4:

If the CLOBs are 32K or less, you can cast them as VARCHAR, which allows comparison, LIKE, and various SQL string functions.

Otherwise, you may want to consider adding a column to contain the hash of the CLOB and change the application(s) to keep that hash up to date whenever the CLOB is updated.

回答5:

The md5 idea is probably the best, but another alternative is to create a special trigger that only fires when your CLOB field is updated.

According to the syntax diagram, you would define the trigger as:

CREATE TRIGGER trig_name AFTER UPDATE OF CLOB_FIELD 
//trigger body goes here

This is assuming that your application (or whoever is updating the table) is smart enough to update the CLOB field ONLY WHEN there has been a change made to the clob field, and not every time your table is updated.

回答6:

I believe it's not possible to use these kind of operators on CLOB fields, because of the way they're stored.

回答7:

Just declare the trigger to fire if that particular column is updated.

create trigger T_TRIG on T 
before update of CLOB_COL
...

回答8:

Generating a hash value and comparing them is the best way IMHO.

Here is the untested code:

...
declare leftClobHash integer;
declare rightClobHash integer;
set leftClobHash = (
    SELECT DBMS_UTILITY.GET_HASH_VALUE(OLD_ROW.CLOB_FIELD,100,1024) AS HASH_VALUE 
    FROM SYSIBM.SYSDUMMY1);
set rightClobHash = (
    SELECT DBMS_UTILITY.GET_HASH_VALUE(UPDATED_ROW.CLOB_FIELD,100,1024) AS HASH_VALUE 
    FROM SYSIBM.SYSDUMMY1);

IF leftClobHash != rightClobHash
...

Note that you need EXECUTE privilege on the DBMS_UTILITY module. You can find more information about the provided SQL PL code in the following links.

• Declaring variables: http://www.sqlpl-guide.com/DECLARE
• Setting values for variables: http://www.sqlpl-guide.com/SET
• Generating Hash: http://pic.dhe.ibm.com/infocenter/db2luw/v9r7/index.jsp?topic=%2Fcom.ibm.db2.luw.sql.rtn.doc%2Fdoc%2Fr0055167.html
• More on DBMS_UTILITY module: http://pic.dhe.ibm.com/infocenter/db2luw/v9r7/index.jsp?topic=%2Fcom.ibm.db2.luw.sql.rtn.doc%2Fdoc%2Fr0055155.html

回答9:

Does DB2 use != for not equals? The ANSI SQL Standard uses <> for not equals.