java mysql 注入,Java / Mysql如何将整个SQL文件注入mysql服务器？

How would you go about loading a .sql file into a mysql database from a java program? I've tried extracting a sql resource file from the self containted jar, I've try processBuilder to locate and run external shell commands. I've tried about everything and none of which seem like the correct way about doing this.

So to see the problem I'm having simply do this:

on any database do a mysqldump: mysqldump -uroot --routines myDB > ~/Desktop/myDB.sql

Now lets try to recreate this entire database from java/jdbc api. How does one properly and correctly do this with our mydB.sql file?

It might be nice to think you create a nice jdbc statement to loop around your databse file but then it gets ugly and bunk when you do get a section of your dump like this

Problem one will run into below as example:

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;

/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;

/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;

/*!40101 SET NAMES utf8 */;

/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;

/*!40103 SET TIME_ZONE='+00:00' */;

/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;

/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;

/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;

/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--

-- Table structure for table `DOG`

--

DROP TABLE IF EXISTS `DOG`;

/*!40101 SET @saved_cs_client = @@character_set_client */;

/*!40101 SET character_set_client = utf8 */;

CREATE TABLE `DOG` (

`DOG_ID` mediumint(9) NOT NULL,

`OWNER_ID` mediumint(9) NOT NULL,

PRIMARY KEY (`DOG_ID`),

KEY `CHANNEL_FK1` (`OWNER_ID`)

) ENGINE=MyISAM DEFAULT CHARSET=latin1;

/*!40101 SET character_set_client = @saved_cs_client */;

Is this the limitations of the jdbc? Is there some nice api that I'm missing which I can call like mysql.createDatabase(myDB.sql).

解决方案

Flyway has an advanced SQL parser and can deal with mysql dumps out of the box. Configuring it for your usecase should be trivial.