php导入sql文件_PDO导入SQL文件

php导入sql文件

一般而言，用navicat软件导出的sql文件如下，文件名 install.sql，这里我用PDO连接数据库并导入的

/*
 Navicat Premium Data Transfer
 Source Server         : 老流氓
 Source Server Type    : MySQL
 Source Server Version : 50649
 Source Host           : localhost:3306
 Source Schema         : test
 Target Server Type    : MySQL
 Target Server Version : 50649
 File Encoding         : 65001
 Date: 05/01/2021 21:52:43
*/
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for lotus_auth_group
-- ----------------------------
DROP TABLE IF EXISTS lotus_auth_group;
CREATE TABLE lotus_auth_group  (
  id mediumint(8) UNSIGNED NOT NULL AUTO_INCREMENT,
  title char(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '',
  status tinyint(1) NOT NULL DEFAULT 1,
  rules varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '权限规则ID',
  remarks varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '',
  PRIMARY KEY (id) USING BTREE
) ENGINE = MyISAM AUTO_INCREMENT = 265 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '权限组表' ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of lotus_auth_group
-- ----------------------------
INSERT INTO lotus_auth_group VALUES (227, '普通用户', 1, '1,2,253,251,256,252,259,262', '');
INSERT INTO lotus_auth_group VALUES (229, '超级管理员', 1, '1,2,253,254,255,251,252,262,263', '');

但是如何用代码方式导入到mysql呢，网上很难找到教程，于是尝试自己写了下，特此分享

//导入sql文件
//测试数据库连接
$dbms='mysql';
$host = $param['host'];
$username = $param['username'];
$password = $param['password'];
$database = $param['database'];
$port     = $param['port'];
$dsn="$dbms:host=$host;port:$port;dbname=$database";
$code = $param['code'];
$pdo = new PDO($dsn, $username, $password);
//读取文件
$sql = file_get_contents('/install.sql');
$_arr = explode(';', $sql);
//没有数据库先要创建数据库
$pdo->query("CREATE DATABASE IF NOT EXISTS $database DEFAULT CHARACTER SET utf8mb4 COLLATE utf8_general_ci;");
$pdo->query("USE $database");
foreach ($_arr as $_value) {
    $pdo->query($_value.';');
}
echo '导入成功！';