mysql_coldiff

Examples

Included with this distribution is a file named create_coldiff_tests.sql, it contains some CREATE TABLE and INSERT statements to make some fake tables to experiment with.

Here are the contents of those tables:

mysql> select * from COLDIFF_A;
+----+-------+------+----------+
| ID | COUNT | X    | CRON     |
+----+-------+------+----------+
|  1 |    10 | x    | 01:01:01 |
|  2 |    20 | x    | 02:02:02 |
|  3 |    30 | x    | 03:03:03 |
|  4 |    40 | x    | 04:04:04 |
|  5 |    50 | x    | 05:05:05 |
+----+-------+------+----------+

mysql> select * from COLDIFF_B;         mysql> select * from COLDIFF_C;
+----+-------+------+----------+        +-------+---------+------+----------+
| ID | COUNT | X    | CRON     |        | IDENT | HOWMANY | XVAL | AT       |
+----+-------+------+----------+        +-------+---------+------+----------+
|  2 |    20 | y    | 02:02:02 |        |     2 |      20 | y    | 02:02:02 |
|  3 |    35 | y    | 03:03:03 |        |     3 |      35 | y    | 03:03:03 |
|  5 |    50 | x    | 17:05:05 |        |     5 |      50 | x    | 17:05:05 |
|  6 |    60 | x    | 06:06:06 |        |     6 |      60 | x    | 06:06:06 |
+----+-------+------+----------+        +-------+---------+------+----------+

Supposing that you had put the example tables into your test database, here are some examples of what you should get:

prompt > mysql_coldiff -d test -i ID -n COLDIFF_A COLDIFF_B
We're comparing the test.COLDIFF_A using the ID column and the test.COLDIFF_B using the ID column.

The following are values of test.COLDIFF_B.ID that are missing from test.COLDIFF_A.ID :
6
The following are values of test.COLDIFF_A.ID that are missing from test.COLDIFF_B.ID :
1 4
We are comparing the following columns:
ID to ID
X to X
COUNT to COUNT

+----------------+----+---+
|                | ID | X |
| test.COLDIFF_A +----+---+
|________________|  2 | x |
|                |  2 | y |
| test.COLDIFF_B +----+---+
|                | ID | X |
+----------------+----+---+

+----------------+----+---+-------+
|                | ID | X | COUNT |
| test.COLDIFF_A +----+---+-------+
|________________|  3 | x |    30 |
|                |  3 | y |    35 |
| test.COLDIFF_B +----+---+-------+
|                | ID | X | COUNT |
+----------------+----+---+-------+



prompt > mysql_coldiff -d test -c COUNT=HOWMANY:X=XVAL:CRON=AT COLDIFF_A.ID COLDIFF_C.IDENT
We're comparing the test.COLDIFF_A using the ID column and the test.COLDIFF_C using the IDENT column.

The following are values of test.COLDIFF_C.IDENT that are missing from test.COLDIFF_A.ID :
6
The following are values of test.COLDIFF_A.ID that are missing from test.COLDIFF_C.IDENT :
1 4
We are comparing the following columns:
X to XVAL
CRON to AT
COUNT to HOWMANY

+----------------+-------+------+
|                |    ID |    X |
| test.COLDIFF_A +-------+------+
|________________|     2 |    x |
|                |     2 |    y |
| test.COLDIFF_C +-------+------+
|                | IDENT | XVAL |
+----------------+-------+------+

+----------------+-------+------+---------+
|                |    ID |    X |   COUNT |
| test.COLDIFF_A +-------+------+---------+
|________________|     3 |    x |      30 |
|                |     3 |    y |      35 |
| test.COLDIFF_C +-------+------+---------+
|                | IDENT | XVAL | HOWMANY |
+----------------+-------+------+---------+

+----------------+-------+----------+
|                |    ID |     CRON |
| test.COLDIFF_A +-------+----------+
|________________|     5 | 05:05:05 |
|                |     5 | 17:05:05 |
| test.COLDIFF_C +-------+----------+
|                | IDENT |       AT |
+----------------+-------+----------+