PHP转换SQLite数据库为MySQL

来源：互联网发布：七区独立袋装弹簧知乎编辑：程序博客网时间：2024/05/21 10:34

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

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

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

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

<?phpclass 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','');

1 0