I'm trying to remove constraints from a mysqldump before piping it into another SQL database. Mysqldump generates tables that look something like this with 1 or more constraints:
CREATE TABLE `SOME_TBL` (
`ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'blah',
/* ... */
PRIMARY KEY (`ID`) USING BTREE,
CONSTRAINT `SOME_TBL_FC1` FOREIGN KEY (`SOME_FIELD`) REFERENCES `SOME_OTHER_TBL` (`ID`),
CONSTRAINT `SOME_TBL_FC2` FOREIGN KEY (`ANOTHER_FIELD`) REFERENCES `ANOTHER_TBL` (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=3845453 DEFAULT CHARSET=utf8 COMMENT='data lives here';
I can delete the constraint lines like so:
mysqldump --source-database \
| sed -E '/^ *CONSTRAINT/d' \
| mysql --result-database
But then I'm left with trailing commas, for example:
CREATE TABLE `SOME_TBL` (
`ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'blah',
/* ... */
PRIMARY KEY (`ID`) USING BTREE,
) ENGINE=InnoDB AUTO_INCREMENT=3845453 DEFAULT CHARSET=utf8 COMMENT='data lives here';
...which SQL doesn't like. (note the comma after "BTREE" above). Note I do no not want to remove all trailing commas, only those trailing commas that appear before a newline and close paren (something like ,\n)
)
What's the easiest way to delete these trailing commas and the constraint lines mid-stream? Note that the dumps might be several thousand megabytes, therefore I can't simply slurp the whole file into perl or something; I want to be able to do this as part of a pipeline.
I could probably run some SQL afterwards that uses INFORMATION_SCHEMA to delete the constraints, but I'm wondering if there's a more elegant way to do this using only text-processing tools?