Search code examples
mysqlinnodbmyisam

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

Solution

  • 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