mysqlsql-serverhashsha1hashbytes

How do I compare a TEXT field between two different databases (MySQL, SQL SERVER) with a hash?


I'm attempting to quickly compare large data in two separate applications (one on MySQL, the other on SQL SERVER).

the myData field is a TEXT field in both databases, and I want to see if the value of this field has changed between them (myData can be hundreds of thousands or millions of characters long)

in MySQL:

select sha1(myData) from myTable where mypk=1;

in SQL SERVER:

select right(convert([varchar](45), hashbytes('SHA1', cast(myData as nvarchar(max))), 1),40) from myTable where mypk=1;

My hash output will not match for the same TEXT field value in both DBs. How can I get it to do so?

Here are my assumptions to date:

  1. MySQL will hash TEXT, but SQL-SERVER will not (hence the cast to nvarchar).
  2. MySQL will not allow casting to nvarchar.
  3. If the input types to the hash are different, the output will be different (in my case where one is TEXT and the other is nvarchar).

Additionally, what if instead of SQL Server it was DB2 or Oracle? Is there some simple approach to this (sorry if this part of the q is too vague)?


Solution

  • SQL Server text is a deprecated type that has been replaced by varchar(max) for storing long non-unicode strings encoded. So 'varchar(max)' is more likely to be binary-compatible with MySql text than `nvarchar(max)' which uses two bytes per character.