Snippet Name: MySQL_Checksum_Comparison

Tags:

Created Date: 2025-06-16 13:12:42
Last Modified Date: 2025-05-31 10:32:20

Dependencies:

DBA Notes:

Sample Output:

Title: MySQL Checksum Comparison
Author: user
in mysql, is there an equivalent of SQL servers BINARY_CHECKSUM, so i can compare say, the primary two servers with he same tables? in SQL server, i would export out the PK and a calculated checksum and put it in a CTE, and run my code on the target server like this:   ;WITH MySource([AdminUserID],[ChkSum])
AS
(
SELECT '352','911713655' UNION ALL
SELECT '353','1304086180' UNION ALL
SELECT '354','-1564818900' UNION ALL
SELECT '355','-1835023452' UNION ALL
SELECT '356','671848729' UNION ALL
SELECT '357','-2059810566' UNION ALL
SELECT '358','-79954919' UNION ALL
SELECT '359','-1100942532' UNION ALL
SELECT '360','-773769137' UNION ALL
SELECT '361','-1579589891' UNION ALL
SELECT '362','-1834928773' 
)
SELECT * FROM MySource
 
EXCEPT 
SELECT [AdminUserID], BINARY_CHECKSUM(
--all columns
[AdminUserID],[Email],[Password],[Description],[Created],[Modified],[IsActive],[AccountType],[Permissions],[NumberOfInvalidLogins],[Lockout],[MustChangePassword],[LastPasswordChange])
AS ChkSum FROM [GYMT_ODB].[GYMT_Admin_User] MyTarget

Author: ChatGPT
MySQL does **not** have a direct equivalent of SQL Server???s `BINARY_CHECKSUM()`. However, you can achieve similar functionality using **hash functions** like `MD5()` or `SHA1()` to create a **row-level checksum** for comparison purposes between source and target tables across servers.

---

### ??? MySQL Equivalent Strategy:

You can create a **checksum per row** by **concatenating all column values**, and hashing the result:

```sql
SELECT 
  AdminUserID,
  MD5(CONCAT_WS('||',
    AdminUserID, Email, Password, Description, Created, Modified, IsActive,
    AccountType, Permissions, NumberOfInvalidLogins, Lockout,
    MustChangePassword, LastPasswordChange
  )) AS ChkSum
FROM GYMT_Admin_User;
```

- `CONCAT_WS()` is used to safely join column values with a delimiter (`'||'`) so that you reduce hash collision risks from adjacent values.
- `MD5()` can be swapped with `SHA1()` or `SHA2()` if you want stronger hashes (e.g., `SHA2(..., 256)`).

---

### ???? Your Full Comparison Approach in MySQL

Assuming you exported the following CSV-style hash set from Server A:

```sql
-- On Server B, simulate a CTE-like structure using a derived table
SELECT *
FROM (
  SELECT '352' AS AdminUserID, 'a0f1ab23...' AS ChkSum UNION ALL
  SELECT '353', 'e9273bc2...' UNION ALL
  ...
) AS MySource
LEFT JOIN (
  SELECT 
    AdminUserID,
    MD5(CONCAT_WS('||',
      AdminUserID, Email, Password, Description, Created, Modified, IsActive,
      AccountType, Permissions, NumberOfInvalidLogins, Lockout,
      MustChangePassword, LastPasswordChange
    )) AS ChkSum
  FROM GYMT_Admin_User
) AS MyTarget
ON MySource.AdminUserID = MyTarget.AdminUserID
WHERE MySource.ChkSum != MyTarget.ChkSum OR MyTarget.ChkSum IS NULL;
```

- This returns rows where the row checksum **differs** or is **missing** from the target.

---

### Notes:
- MySQL doesn???t support CTEs before version 8.0. If you're using MySQL 8.0+, you **can use CTEs** just like in SQL Server.
- If rows are large or frequent updates happen, consider using `SHA2(..., 256)` for better hash integrity.
- If you want to export the `AdminUserID` and `ChkSum` from one server and compare on another, you can write it to a flat file or temporary table.

Would you like a script to **generate the insertable values from MySQL Server A** to paste into Server B like your SQL Server example?