Comparing data to table in the database
I receive raw data in CSVs, and upload it to a table in a MySQL database (upon which my website functions). I want to compare a newer CSV to the data I uploaded from an older CSV, and I want to see the differences between the two (basically I want to diff the raw data with the table).
I have PHP, MySQL, and my desktop apps 开发者_如何学JAVA(e.g. Excel) at my disposal. What's the best way to go about this? Possible ways I can think of:
- Inserting the newer data into a Table_Copy, then somehow diffing the two tables in mysql.
- Somehow querying the database in comparison to the rawdata without having to upload it.
- Downloading the data from the database into raw CSV format, and then comparing the two raw CSV's using a desktop program
Why don't you use the where clause to pull only the data that is new? For instance
select * from table where dateadded > '1-1-2011 18:18'
This depends on your table having a dateadded column and populating that with the date and time the data is added.
diff <(mysqldump test old_csv --skip-extended-insert) <(mysqldump test new_csv --skip-extended-insert) --side-by-side --suppress-common-lines --width=690 | more
You can use the following approaches
1) Database Table comparison - create a copy of the table and then compare data.
You can use propriety tools to do it easily (Eg : EMS Data comparer).
You can also write some simple queries to achieve this (Eg : select id from table_copy not in (select id in table) )
2) Use a file comparer like winmerge Take the dump of both the tables with exact method, and them compare it.
I use both the approaches depending on my data size. For smaller data 2nd approach is good.
精彩评论