0

I have a huge MySQL Dump with lots of insert statement using the multiple rows syntax. I need to search for specific content in each row, therefore I want to split the statement to have each row in a extra string.

INSERT INTO tbl_name (a,b,c)
VALUES (1,2,'test1'),
       (4,5,'test2 (test2a),
       (test2b)'),(7,8,'test3');

Result should be a list of the 3 blocks

[
    "1,2,'test1'",
    "4,5,'test2 (test2a),(test2b)'",
    "7,8,'test3'"
]

Question, what is the most efficient way to achieve this. Is it regular expression with some magic? Language does not matter for me. Can do with Java, PHP, Perl ...

Tim Biegeleisen
  • 387,723
  • 20
  • 200
  • 263
borehack
  • 87
  • 1
  • 5
  • 1
    Are the column numbers fixed throughout the dump? Or should you be considering a possibility of multiple rows? Also, did you consider loading this into a mysql database and querying for the values? That seems more straight forward compared to parsing a free format text file using Regular expressions. – Kashyap Apr 01 '16 at 06:43
  • Why do you want/need to do this? You could use a `UNION` query but perhaps this would be better handled in your app layer. – Tim Biegeleisen Apr 01 '16 at 06:43
  • the dump is very large and I need this to verify history data within multiple dumps. lets say I have 10 dumps each > 3GB or so (with gz) and I want to search for keywords in each of the dumps to see history of a few rows (with the key words). the column numbers are fix yes someone deleted rows and I need to verify when that happend. so i want to search the dumps on what date the row was still present – borehack Apr 01 '16 at 06:46
  • `regex` will be a bad choice for this because of `nesting` structure.. See here :- http://stackoverflow.com/questions/546433/regular-expression-to-match-outer-brackets – rock321987 Apr 01 '16 at 06:48
  • Please tell us more details around your problem. Maybe you are on the wrong track. – etalon11 Apr 01 '16 at 06:48
  • as described I have a dump of the database every day. but the dump is very large > 3GB (with gz) to load each dump search for the row and dump the database again would be a very disk / mysql intensive process. i just want to search through each dump for a certain row to tell a customer by when it was deleted (no longer present) – borehack Apr 01 '16 at 06:51
  • I question whether any of the regexps (in the 'dup' question) will work _in mysql_. – Rick James Apr 03 '16 at 00:17

0 Answers0