PHP转换SQLite数据库为MySQL

网上找了些工具，但都是单独导出一个数据表，且格式有些不一样，还要手动替换一些字符，才能适合mysql格式

所以我自己简单的弄了一个转换

不过呢，我这里转换的还是比较粗糙的，比如我只是将 TEXT 类型转成 varchar(255)，INTEGER 转换成了int(11)

但若是有同学需要用的话，可以自己修改下即可

<?php
class SQLiteToMySQL {
    private $db;
    private $pdo;
    private $master_table_array = array();
    private $replaceArray = array(
        '"'=>'`',
        'AUTOINCREMENT'=>'AUTO_INCREMENT',
        'INTEGER'=>'int(11)',
        'TEXT'=>'varchar(255)',
    );
    private $sqlDumpText;

    public function __construct($db) {
        $this->db = $db;
        $this->open();
        $this->getMasterData();
    }

    public function __toString() {
        $str = 'SQLite转换为MySQL。';
        return $str;
    }

    private function open() {
        if (!is_file($this->db)) {
            throw new Exception($this->db.' 数据库文件不存在。');
        }
        $this->pdo = new PDO('sqlite:'.$this->db);
    }

    public function getMasterData() {
        $res = $this->pdo->query('select * from sqlite_master');
        $list = $res->fetchAll(PDO::FETCH_ASSOC);
        $this->master_table_array = $list;
        return $this;
    }


    /**
     * 合并SQL字段语句
     * @param $arr
     * @return string
     */
    private function mergeSqlFields($arr) {
        if(!$arr) return;
        $result = '';
        foreach ($arr as $k => $v) {
            $result .= "'".$v."',";
        }
        return substr($result,0,strlen($result)-1);
    }



    /**
     * 取得插入SQL语句
     * @param string $tb
     * @return array
     */
    public function getInsertSql($tb='') {
        $resultArray = array();
        foreach ($this->master_table_array as $k => $v) {
            $table = $v['tbl_name'];
            if ($tb) {
                if ($tb != $table) continue;
            }
            $res = $this->pdo->query('select * from '.$table.' ');
            $list = $res->fetchAll(PDO::FETCH_ASSOC);
            foreach ($list as $k => $v) {
                $mergeSqlFields = $this->mergeSqlFields($v);
                $resultArray[$table][] = "INSERT INTO `".$table."` VALUES (".$mergeSqlFields.");";
            }
        }
        return $resultArray;
    }



    /**
     * 取得数据表机构创建语句
     * @param string $tb
     * @return array
     */
    public function getCreateSql($tb='') {
        $resultArray = array();
        foreach ($this->master_table_array as $k => $v) {
            $table = $v['tbl_name'];
            if ($tb) {
                if ($tb != $table) continue;
            }
            $resultArray[$table] = $v['sql'];
            foreach ($this->replaceArray as $k => $v) {
                $resultArray[$table] = str_replace($k,$v,$resultArray[$table]);
            }
            $resultArray[$table] = "DROP TABLE IF EXISTS `".$table."`;\n".$resultArray[$table].'ENGINE=MyISAM DEFAULT CHARSET=utf8;';
        }
        return $resultArray;
    }


    /**
     * 取得要导出的sql语句
     * @return $this
     */
    public function getDumpSql() {
        $this->sqlDumpText = '';
        foreach ($this->master_table_array as $k => $v) {
            $table = $v['tbl_name'];
            if ($table == 'sqlite_sequence') continue;
            $getCreateSql = $this->getCreateSql($table);
            $getCreateSql = $getCreateSql[$table];
            $getInsertSql = $this->getInsertSql($table);
            $getInsertSql = implode("\n",$getInsertSql[$table]);
            $this->sqlDumpText .="\n\n-- ----------------------------\n-- Table structure for {$table}\n-- ----------------------------\n\n";
            $this->sqlDumpText .=$getCreateSql;
            $this->sqlDumpText .="\n\n-- ----------------------------\n-- Records for {$table}\n-- ----------------------------\n\n";
            $this->sqlDumpText .=$getInsertSql;
        }
        return $this;
    }


    public function writeFile($savepath) {
        if (!is_dir($dir = dirname($savepath))) {
            mkdir($dir, 0777, true);
        }
        if ( !$hd = fopen($savepath, 'w')) {
            throw new Exception('打开文件：'.$savepath.' 失败。');
        }
        if (!fwrite($hd, $this->sqlDumpText)) {
            throw new Exception('写入内容失败。');
        }
        fclose($hd);
        return true;
    }

    public function printSql() {
        echo $this->sqlDumpText;
        return $this;
    }

}

try {
    //SQLite数据库位置
    $db = '/data/www/test/Database/data.db';
    //导出的新的sql存放位置
    $savepath = '/data/www/test/MySQL/data.sql';
    $SQLiteToMySQL = new SQLiteToMySQL($db);
    if ($SQLiteToMySQL->getDumpSql()->writeFile($savepath)) {
        echo PHP_EOL,'导出SQL成功！保存位置：'.$savepath;
    }
} catch (Exception $e) {
    echo $e->getMessage();
}

效果演示：

-- ----------------------------
-- Table structure for menu
-- ----------------------------

DROP TABLE IF EXISTS `menu`;
CREATE TABLE `menu` (
`id`  int(11) PRIMARY KEY AUTO_INCREMENT NOT NULL,
`type`  int(11),
`pid`  int(11),
`name`  varchar(255),
`url`  varchar(255),
`show`  int(11) DEFAULT 1,
`sort`  int(11)
)ENGINE=MyISAM DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records for menu
-- ----------------------------

INSERT INTO `menu` VALUES ('7','2','0','菜单管理','','0','0');
INSERT INTO `menu` VALUES ('8','2','7','添加菜单','g=Admin&a=Menu&m=add','1','2');
INSERT INTO `menu` VALUES ('9','2','7','菜单列表','g=Admin&a=Menu&m=index','1','1');
INSERT INTO `menu` VALUES ('10','2','0','图片管理','','0','0');
INSERT INTO `menu` VALUES ('11','2','10','添加图片','g=Admin&a=Game&m=add','1','0');
INSERT INTO `menu` VALUES ('12','2','10','图片列表','g=Admin&a=Game&m=index','1','0');




-- ----------------------------
-- Table structure for platform
-- ----------------------------

DROP TABLE IF EXISTS `platform`;
CREATE TABLE `platform` (
`id`  int(11) PRIMARY KEY AUTO_INCREMENT NOT NULL,
`pid`  int(11),
`code`  int(11),
`name`  varchar(255),
`sort`  int(11)
)ENGINE=MyISAM DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records for platform
-- ----------------------------

INSERT INTO `platform` VALUES ('1','16','qq','qq','');
INSERT INTO `platform` VALUES ('2','16','51','51','');
INSERT INTO `platform` VALUES ('4','16','baidu','baidu','');
INSERT INTO `platform` VALUES ('7','16','duowan','duowan','');