mysql - 如果行很长，sed 无法进行替换

Question

sed 适用于短线，但不适用于长线。使用以下命令:

sed 's/DEFINER=\`[a-z]*\`@\`localhost\`//g' temp.sql

当 temp.sql 包含像这样的短行时它起作用:

CREATE ALGORITHM=UNDEFINED DEFINER=`factstvdus`@`localhost` SQL SECURITY DEFINER VIEW `caseinfo` AS select `files`.`fileNumber` AS `fileNumber`...

但是，对于非常长的行，替换不会发生。我的目标是删除以下的每个实例:

DEFINER=`factstvdus`@`localhost`

用户名(在本例中为 factstvdus)并不总是相同的。 mysqldump 在一长行中输出所有 View 定义，因此将它们包装成我所知道的多行并不容易。

如何让 sed 处理长行，或者我应该使用其他工具吗？

更新:这是在 CentOS 6 上，GNU sed 版本 4.2.1

失败的行和上面的完全一样，只是真的很长:

CREATE ALGORITHM=UNDEFINED DEFINER=`factstvdus`@`localhost` SQL SECURITY DEFINER VIEW `caseinfo` AS select `files`.`fileNumber` AS `fileNumber`,`files`.`fileTitle` AS `fileTitle`,`files`.`openDate` AS `openDate`,`files`.`applicantID` AS `applicantID`,`files`.`adjusterID` AS `adjusterID`,`files`.`adjusterType` AS `adjusterType`,`files`.`employerID` AS `employerID`,`files`.`insurerID` AS `insurerID`,`files`.`TPAid` AS `TPAid`,`files`.`employerContact` AS `employerContact`,`files`.`insurerContact` AS `insurerContact`,`files`.`TPAcontact` AS `TPAcontact`,`files`.`claimNumber` AS `claimNumber`,`files`.`AAid` AS `AAid`,`files`.`AAfirmID` AS `AAfirmID`,`files`.`GLDAid` AS `GLDAid`,`files`.`coverages` AS `coverages`,`files`.`policyNumber` AS `policyNumber`,`files`.`policyLimits` AS `policyLimits`,`files`.`DOH` AS `DOH`,`files`.`courtLocationID` AS `courtLocationID`,`files`.`NORfiled` AS `NORfiled`,`files`.`baseRate` AS `baseRate`,`files`.`recommendations` AS `recommendations`,`files`.`GAICID` AS `GAICID`,`files`.`fileStatus` AS `fileStatus`,`files`.`lastEventDate` AS `lastEventDate`,`files`.`flatFee` AS `flatFee`,`adjusters`.`adjusterFirstName` AS `adjusterFirstName`,`adjusters`.`adjusterMiddleName` AS `adjusterMiddleName`,`adjusters`.`adjusterLastName` AS `adjusterLastName`,`adjusters`.`adjusterOfficePhone` AS `adjusterOfficePhone`,`adjusters`.`adjusterCellPhone` AS `adjusterCellPhone`,`adjusters`.`adjusterFax` AS `adjusterFax`,`adjusters`.`adjusterPhoneExtension` AS `adjusterPhoneExtension`,`adjusters`.`adjusterEmail` AS `adjusterEmail`,`ADJ`.`companyID` AS `clientID`,concat(if((`ADJ`.`companyID` < 1000),_utf8'0',_utf8''),if((`ADJ`.`companyID` < 100),_utf8'0',_utf8''),if((`ADJ`.`companyID` < 10),_utf8'0',_utf8''),`ADJ`.`companyID`,_utf8'-',`files`.`fileNumber`) AS `fullFileNumber`,`ADJ`.`companyName` AS `clientName`,`ADJ`.`companyNameShort` AS `clientNameShort`,`ADJ`.`officePhone` AS `clientOfficePhone`,`ADJ`.`tollFreePhone` AS `clientTollFreePhone`,`EMP`.`companyName` AS `employerName`,`INSUR`.`companyName` AS `insurerName`,`TPA`.`companyName` AS `TPAname`,`AA`.`lawyerFirstName` AS `AAfirstName`,`AA`.`lawyerMiddleName` AS `AAmiddleName`,`AA`.`lawyerLastName` AS `AAlastName`,`AA`.`lawyerReceptionist` AS `AAreceptionist`,`AA`.`lawyerNotes` AS `AAnotes`,`AA`.`lawyerOfficePhone` AS `AAofficePhone`,`AA`.`lawyerCellPhone` AS `AAcellPhone`,`AA`.`lawyerFax` AS `AAfax`,`AA`.`lawyerEmail` AS `AAemail`,`AAF`.`officePhone` AS `AAfirmOfficePhone`,`AAF`.`tollFreePhone` AS `AAfirmTollFreePhone`,`AAF`.`faxNumber` AS `AAfirmFax`,`AAF`.`EAMSno` AS `AAfirmEAMSno`,`AAF`.`EAMSname` AS `AAfirmEAMSname`,`AA`.`lawyerPhoneExtension` AS `AAphoneExtension`,`AAF`.`firmName` AS `AAfirmName`,`GLDA`.`lawyerFirstName` AS `GLDAfirstName`,`GLDA`.`lawyerMiddleName` AS `GLDAmiddleName`,`GLDA`.`lawyerLastName` AS `GLDAlastName`,`GLDA`.`lawyerOfficePhone` AS `GLDAofficePhone`,`GLDA`.`lawyerCellPhone` AS `GLDAcellPhone`,`GLDA`.`lawyerFax` AS `GLDAfax`,`GLDA`.`lawyerEmail` AS `GLDAemail`,`GLDA`.`userID` AS `GLDAUserID`,`applicants`.`applicantFirstName` AS `applicantFirstName`,`applicants`.`applicantMiddleName` AS `applicantMiddleName`,`applicants`.`applicantLastName` AS `applicantLastName`,`applicants`.`applicantDOB` AS `applicantDOB`,`applicants`.`applicantSSN` AS `applicantSSN`,`applicants`.`applicantJobTitle` AS `applicantJobTitle`,`applicants`.`homePhone` AS `applicantHomePhone`,`applicants`.`cellPhone` AS `applicantCellPhone`,`applicants`.`applicantEmail` AS `applicantEmail`,`APPPHYSLOC`.`locationID` AS `applicantPhysicalLocationID`,`APPPHYSLOC`.`locationAddress` AS `applicantPhysicalAddress`,`APPPHYSLOC`.`locationCity` AS `applicantPhysicalCity`,`APPPHYSLOC`.`locationState` AS `applicantPhysicalState`,`APPPHYSLOC`.`locationZipCode` AS `applicantPhysicalZip`,`APPMAILLOC`.`locationID` AS `applicantMailLocationID`,`APPMAILLOC`.`locationAddress` AS `applicantMailAddress`,`APPMAILLOC`.`locationCity` AS `applicantMailCity`,`APPMAILLOC`.`locationState` AS `applicantMailState`,`APPMAILLOC`.`locationZipCode` AS `applicantMailZip`,`AAPHYSLOC`.`locationID` AS `AAphysicalLocationID`,`AAPHYSLOC`.`locationAddress` AS `AAphysicalAddress`,`AAPHYSLOC`.`locationCity` AS `AAphysicalCity`,`AAPHYSLOC`.`locationState` AS `AAphysicalState`,`AAPHYSLOC`.`locationZipCode` AS `AAphysicalZip`,`AAMAILLOC`.`locationID` AS `AAmailLocationID`,`AAMAILLOC`.`locationAddress` AS `AAmailAddress`,`AAMAILLOC`.`locationCity` AS `AAmailCity`,`AAMAILLOC`.`locationState` AS `AAmailState`,`AAMAILLOC`.`locationZipCode` AS `AAmailZip`,`AAFPL`.`locationID` AS `AAfirmPhysicalLocationID`,`AAFPL`.`locationAddress` AS `AAfirmPhysicalAddress`,`AAFPL`.`locationCity` AS `AAfirmPhysicalCity`,`AAFPL`.`locationState` AS `AAfirmPhysicalState`,`AAFPL`.`locationZipCode` AS `AAfirmPhysicalZip`,`AAFML`.`locationID` AS `AAfirmMailLocationID`,`AAFML`.`locationAddress` AS `AAfirmMailAddress`,`AAFML`.`locationCity` AS `AAfirmMailCity`,`AAFML`.`locationState` AS `AAfirmMailState`,`AAFML`.`locationZipCode` AS `AAfirmMailZip`,`ADJPHYSLOC`.`locationID` AS `adjusterPhysicalLocationID`,`ADJPHYSLOC`.`locationAddress` AS `adjusterPhysicalAddress`,`ADJPHYSLOC`.`locationCity` AS `adjusterPhysicalCity`,`ADJPHYSLOC`.`locationState` AS `adjusterPhysicalState`,`ADJPHYSLOC`.`locationZipCode` AS `adjusterPhysicalZip`,`ADJMAILLOC`.`locationID` AS `adjusterMailLocationID`,`ADJMAILLOC`.`locationAddress` AS `adjusterMailAddress`,`ADJMAILLOC`.`locationCity` AS `adjusterMailCity`,`ADJMAILLOC`.`locationState` AS `adjusterMailState`,`ADJMAILLOC`.`locationZipCode` AS `adjusterMailZip`,`ADJBILLLOC`.`locationID` AS `adjusterBillLocationID`,`ADJBILLLOC`.`locationAddress` AS `adjusterBillAddress`,`ADJBILLLOC`.`locationCity` AS `adjusterBillCity`,`ADJBILLLOC`.`locationState` AS `adjusterBillState`,`ADJBILLLOC`.`locationZipCode` AS `adjusterBillZip`,`EMPPHYSLOC`.`locationID` AS `employerPhysicalLocationID`,`EMPPHYSLOC`.`locationAddress` AS `employerPhysicalAddress`,`EMPPHYSLOC`.`locationCity` AS `employerPhysicalCity`,`EMPPHYSLOC`.`locationState` AS `employerPhysicalState`,`EMPPHYSLOC`.`locationZipCode` AS `employerPhysicalZip`,`EMPMAILLOC`.`locationID` AS `employerMailLocationID`,`EMPMAILLOC`.`locationAddress` AS `employerMailAddress`,`EMPMAILLOC`.`locationCity` AS `employerMailCity`,`EMPMAILLOC`.`locationState` AS `employerMailState`,`EMPMAILLOC`.`locationZipCode` AS `employerMailZip`,`INSURPHYSLOC`.`locationID` AS `insurerPhysicalLocationID`,`INSURPHYSLOC`.`locationAddress` AS `insurerPhysicalAddress`,`INSURPHYSLOC`.`locationCity` AS `insurerPhysicalCity`,`INSURPHYSLOC`.`locationState` AS `insurerPhysicalState`,`INSURPHYSLOC`.`locationZipCode` AS `insurerPhysicalZip`,`INSURMAILLOC`.`locationID` AS `insurerMailLocationID`,`INSURMAILLOC`.`locationAddress` AS `insurerMailAddress`,`INSURMAILLOC`.`locationCity` AS `insurerMailCity`,`INSURMAILLOC`.`locationState` AS `insurerMailState`,`INSURMAILLOC`.`locationZipCode` AS `insurerMailZip`,`TPAPHYSLOC`.`locationID` AS `TPAphysicalLocationID`,`TPAPHYSLOC`.`locationAddress` AS `TPAphysicalAddress`,`TPAPHYSLOC`.`locationCity` AS `TPAphysicalCity`,`TPAPHYSLOC`.`locationState` AS `TPAphysicalState`,`TPAPHYSLOC`.`locationZipCode` AS `TPAphysicalZip`,`TPAMAILLOC`.`locationID` AS `TPAmailLocationID`,`TPAMAILLOC`.`locationAddress` AS `TPAmailAddress`,`TPAMAILLOC`.`locationCity` AS `TPAmailCity`,`TPAMAILLOC`.`locationState` AS `TPAmailState`,`TPAMAILLOC`.`locationZipCode` AS `TPAmailZip`,`EMP`.`officePhone` AS `employerOfficePhone`,`EMP`.`tollFreePhone` AS `employerTollFreePhone`,`EMP`.`faxNumber` AS `employerFax`,`INSUR`.`officePhone` AS `insurerOfficePhone`,`INSUR`.`tollFreePhone` AS `insurerTollFreePhone`,`INSUR`.`faxNumber` AS `insurerFax`,`TPA`.`officePhone` AS `TPAofficePhone`,`TPA`.`tollFreePhone` AS `TPAtollFreePhone`,`TPA`.`faxNumber` AS `TPAfax` from ((((((((((((((((((((((((`files` left join `adjusters` on((`files`.`adjusterID` = `adjusters`.`adjusterID`))) left join `applicants` on((`files`.`applicantID` = `applicants`.`applicantID`))) left join `locations` `APPPHYSLOC` on((`applicants`.`physicalLocationID` = `APPPHYSLOC`.`locationID`))) left join `locations` `APPMAILLOC` on((`applicants`.`mailingLocationID` = `APPMAILLOC`.`locationID`))) left join `locations` `ADJPHYSLOC` on((`adjusters`.`physicalLocationID` = `ADJPHYSLOC`.`locationID`))) left join `locations` `ADJMAILLOC` on((`adjusters`.`mailingLocationID` = `ADJMAILLOC`.`locationID`))) left join `locations` `ADJBILLLOC` on((`adjusters`.`billingLocationID` = `ADJBILLLOC`.`locationID`))) left join `companies` `EMP` on((`files`.`employerID` = `EMP`.`companyID`))) left join `companies` `INSUR` on((`files`.`insurerID` = `INSUR`.`companyID`))) left join `companies` `TPA` on((`files`.`TPAid` = `TPA`.`companyID`))) left join `lawyers` `AA` on((`files`.`AAid` = `AA`.`lawyerID`))) left join `lawyers` `GLDA` on((`files`.`GLDAid` = `GLDA`.`lawyerID`))) left join `companies` `ADJ` on((`ADJ`.`companyID` = `adjusters`.`adjusterCompanyID`))) left join `locations` `EMPPHYSLOC` on((`EMP`.`physicalLocationID` = `EMPPHYSLOC`.`locationID`))) left join `locations` `EMPMAILLOC` on((`EMP`.`mailingLocationID` = `EMPMAILLOC`.`locationID`))) left join `locations` `INSURPHYSLOC` on((`INSUR`.`physicalLocationID` = `INSURPHYSLOC`.`locationID`))) left join `locations` `INSURMAILLOC` on((`INSUR`.`mailingLocationID` = `INSURMAILLOC`.`locationID`))) left join `locations` `TPAPHYSLOC` on((`TPA`.`physicalLocationID` = `TPAPHYSLOC`.`locationID`))) left join `locations` `TPAMAILLOC` on((`TPA`.`mailingLocationID` = `TPAMAILLOC`.`locationID`))) left join `locations` `AAPHYSLOC` on((`AA`.`physicalLocationID` = `AAPHYSLOC`.`locationID`))) left join `locations` `AAMAILLOC` on((`AA`.`mailingLocationID` = `AAMAILLOC`.`locationID`))) left join `firms` `AAF` on((`files`.`AAfirmID` = `AAF`.`firmID`))) left join `locations` `AAFPL` on((`AAFPL`.`locationID` = `AAF`.`physicalLocationID`))) left join `locations` `AAFML` on((`AAFML`.`locationID` = `AAF`.`mailingLocationID`)));

Answer 1

这两条线对我都适用:

sed 's/DEFINER=`[a-z]*`@`localhost`//g' temp.sql

唯一的变化是移除了转义符。作为 GNU sed 扩展，反斜杠-反引号匹配模式空间的开头。 (如果模式空间是单行，这与 ^ 相同。)然而，在这种情况下，我们需要匹配一个实际的反引号。