CODEWITHSUNDEEP
mysqlinnodbmyisam
zozo6015
zozo6015

Reputation: 577

change mysql table storage engine upon import

We have some customers sending us mysql database sql files where some times the tables engines are myISAM and when or before importing it into our database we would need to convert the tables to InnoDB. Is this possible?

cheers, Peter

Upvotes: 2

Views: 995

Answers (1)

e4c5
e4c5

Reputation: 53774

If you are fortunate enough to be on linux

sed -i 's/engine=myisam/engine=innodb/gi' userfile.sql

This does assume that the data itself does not contain the exact text engine=myisam you can make it a bit more rigorous like this:

sed -i 's/) ENGINE=InnoDB/) ENGINE=MyISAM/g' junk.sql

A table creation statement looks like this:

CREATE TABLE `Table1` (
  `user_id` int(11) DEFAULT NULL,
  `user_name` varchar(5) DEFAULT NULL,
  `user_rating` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

And in the second usage of sed we have made it case sensitive and it also looks out for the ) and ' ' characters that preceded the ENGINE keyword

Upvotes: 4

Related Questions