比较InnoDb和MyIsam MySql表的平等性

Question

我试图从两个不同的数据库中比较两个MySql表，一个使用MyIsam引擎，另一个使用InnoDb引擎。

我需要能够快速确定表是否存储相同的值。首先，我使用了MD5哈希比较，但失败了；我认为这是因为db引擎不同。

有人对我如何比较这两张表格有什么建议吗？

备注

这是一个正在用C#.NET编写的应用程序，所以我更喜欢C#实现，而不是MySql引擎实现。通常，每个表总是有210列和0到100行之间的任何位置。

Answer 1

当然，对于大型表和不同的自动增量，不是一个解决方案。您可以使用具有特定事件的cron作业自动化这一点，或者如果您说在cron作业看到的某个表中填充一个值，则清除并开始它。

CREATE TABLE charlie1
(   billybob INT NOT NULL,
    birthdate DATETIME NOT NULL,
    funny_num FLOAT NOT NULL
)ENGINE=INNODB;

INSERT INTO charlie1 (billybob,birthdate,funny_num) VALUES (1,'2006-01-02',28832.123);
INSERT INTO charlie1 (billybob,birthdate,funny_num) VALUES (2,'2004-09-02',18832.888);
INSERT INTO charlie1 (billybob,birthdate,funny_num) VALUES (3,'2006-07-03',28332.123);
INSERT INTO charlie1 (billybob,birthdate,funny_num) VALUES (4,'2006-01-02',28852.777);


SELECT billybob,birthdate,funny_num
FROM charlie1
ORDER BY billybob
INTO OUTFILE '/tmp/charlie1.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'

CREATE TABLE charlie2
(   billybob INT NOT NULL,
    birthdate DATETIME NOT NULL,
    funny_num FLOAT NOT NULL
)ENGINE=MYISAM;

INSERT INTO charlie2 (billybob,birthdate,funny_num) VALUES (1,'2006-01-02',28832.123);
INSERT INTO charlie2 (billybob,birthdate,funny_num) VALUES (2,'2004-09-02',18832.888);
INSERT INTO charlie2 (billybob,birthdate,funny_num) VALUES (3,'2006-07-03',28332.123);
INSERT INTO charlie2 (billybob,birthdate,funny_num) VALUES (4,'2006-01-02',28852.777);


SELECT billybob,birthdate,funny_num
FROM charlie2
ORDER BY billybob
INTO OUTFILE '/tmp/charlie2.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'

i changed data in the file to show diff picks it up:

root@hp:/tmp# diff /tmp/charlie1.csv /tmp/charlie2.csv
2c2
< "2","2004-09-02 00:00:00","18832.9"
---
> "2","2004-09-02 00:00:00","18832.8"