整理下之前写的批量更新数据库的业务
业务场景如下:
我们做的是一个商城平台,每一家接入平台的商户都有自己的库(用来存放自己的商品数据).
现在碰到的问题主要有2个
- 当商户新接入的时候,需要从特定的库复制表结构过来
- 当特定库的表有更改的时候,需要同步到其他商户的库
后来想了下,在项目中新建了一个文件夹用来存储sql,每次有表更新都把sql语句写到这个文件下.手动操作后,程序会从这个文件读取sql语句更新库
下面就是对以上业务写的处理类
class Database
{
//商户库前缀
const DEFAULT_DB_PREFIX = 'mall_';
//表前缀
const DEFAULT_TABLE_PREFIX = 'jl_';
//创业库并新建表
public static function create($config)
{
try {
//创建数据库
$DB = '';
$DB .= $config['db_prefix'] ?: self::DEFAULT_DB_PREFIX;
if (!$config['db_name']) {
throw new \Exception('缺少数据库名称');
}
$DB .= $config['db_name'];
$createDbSql = 'CREATE DATABASE IF NOT EXISTS ' . $DB . ' DEFAULT CHARACTER SET utf8;';
$ret = M()->execute($createDbSql);
if ($ret === false) {
throw new Exception('创建数据库失败');
}
//切换数据库
M()->link($DB,true);
M()->startTrans();
// 导入sql数据并创建表
$tablePrefix = $config['table_prefix'] ?: self::DEFAULT_TABLE_PREFIX;
$SQL_SCRIPT = file_get_contents(dirname(getcwd()) . '/SQL/base/db.sql');
$scriptArray = preg_split("/;[\r\n]+/", str_replace(self::DEFAULT_TABLE_PREFIX, $tablePrefix, $SQL_SCRIPT));
foreach ($scriptArray as $item) {
if (!$item) {
continue;
}
$res = M()->execute($item);
if ($res === false) {
M()->rollback();
throw new Exception('操作失败');
}
}
M()->link();
return true;
} catch (Exception $e) {
return false;
};
}
//更新库
public static function update($config)
{
$ret = true;
try {
//创建数据库
$DB = '';
$DB .= $config['db_prefix'] ?: self::DEFAULT_DB_PREFIX;
if (!$config['db_name']) {
throw new \Exception('缺少数据库名称');
}
$DB .= $config['db_name'];
//切换数据库
M()->link($DB,true);
M()->startTrans();
// 导入sql数据并创建表
$tablePrefix = $config['table_prefix'] ?: self::DEFAULT_TABLE_PREFIX;
$SQL_SCRIPT = file_get_contents(dirname(getcwd()) . '/SQL/ext/db.sql');
$scriptArray = preg_split("/;[\r\n]+/", str_replace(self::DEFAULT_TABLE_PREFIX, $tablePrefix, $SQL_SCRIPT));
foreach ($scriptArray as $item) {
if (!$item) {
continue;
}
if (preg_match('/DROP\s+TABLE\s+/', $item)) {
continue;
}
try {
$res = M()->execute($item);
} catch (Exception $e) {
continue;
}
if ($res === false) {
throw new Exception('操作失败');
}
}
M()->commit();
M()->link();
} catch (Exception $e) {
echo $e->getMessage();
$ret = false;
};
return $ret;
}
//更改表
public static function alertTable($data)
{
try {
if (!$data['table_name'] || !$data['action'] || !$data['column_name']) {
throw new \Exception('参数异常');
}
$tableName = $data['table_name'];
$action = $data['action'];
$columnName = $data['column_name'];
$definition = $data['definition'];
$alertTableSql = 'ALTER TABLE ' . $tableName . ' ' . $action . ' ' . $columnName;
if ($action == 'change') {
$alertTableSql .= ' ' . $data['new_column_name'];
}
$alertTableSql .= ' ' . $definition;
$company = M()->query("SELECT id FROM jl_company");
foreach ($company as $item) {
$DB_NAME = self::DEFAULT_DB_PREFIX . $item['id'];
M()->link($DB_NAME)->execute($alertTableSql);
}
} catch (\Exception $e) {
echo $e->getMessage();
}
}
//新建库
public static function newDB($config)
{
$newDB = self::DEFAULT_DB_PREFIX . $config['target'];
$ret = M()->execute("CREATE DATABASE `$newDB`");
if (!$ret) {
return false;
}
return true;
}
//复制库
public static function copyDB($config, $filter = [], $choose = [])
{
$originalDB = self::DEFAULT_DB_PREFIX . $config['original'];
$newDB = self::DEFAULT_DB_PREFIX . $config['target'];
try {
self::validateDB($originalDB);
self::validateDB($newDB);
$originalTabs = self::validateTab($originalDB);
M()->execute("USE $newDB");
foreach ($originalTabs as $tab) {
if (in_array($tab, $filter)) {
continue;
}
if (!$choose) {
M()->execute("DROP TABLE IF EXISTS $tab");
M()->execute("CREATE TABLE $tab LIKE " . $originalDB . "." . $tab);
M()->execute("INSERT INTO $tab SELECT * FROM " . $originalDB . "." . $tab);
} else {
if (in_array($tab, $choose)) {
M()->execute("DROP TABLE IF EXISTS $tab");
M()->execute("CREATE TABLE $tab LIKE " . $originalDB . "." . $tab);
M()->execute("INSERT INTO $tab SELECT * FROM " . $originalDB . "." . $tab);
}
}
}
} catch (Exception $e) {
echo $e->getMessage();
return false;
}
return true;
}
//flush表
public static function flushTab($config, $selectTabs = [])
{
$targetDB = self::DEFAULT_DB_PREFIX . $config['target'];
try {
self::validateDB($targetDB);
$tabs = self::validateTab($targetDB);
M()->execute("USE $targetDB");
foreach ($tabs as $tab) {
if (in_array($tab, $selectTabs)) {
M()->execute("TRUNCATE $tab");
}
}
} catch (Exception $e) {
echo $e->getMessage();
return false;
}
return true;
}
//更新表
public static function updateTab($config, $data = [])
{
$targetDB = self::DEFAULT_DB_PREFIX . $config['target'];
try {
if (!$data) {
throw new Exception('缺少参数');
}
self::validateDB($targetDB);
$tabs = self::validateTab($targetDB);
M()->execute("USE $targetDB");
foreach ($tabs as $tab) {
if ($data[$tab]) {
$up = $data[$tab];
M()->execute("update $tab set $up");
}
}
} catch (Exception $e) {
echo $e->getMessage();
return false;
}
return true;
}
//新增表
public static function insertTab($config, $data = [])
{
$originalDB = self::DEFAULT_DB_PREFIX . $config['original'];
$targetDB = self::DEFAULT_DB_PREFIX . $config['target'];
try {
if (!$data) {
throw new Exception('缺少参数');
}
self::validateDB($originalDB);
self::validateDB($targetDB);
$tabs = self::validateTab($targetDB);
M()->execute("USE $targetDB");
foreach ($tabs as $tab) {
if ($data[$tab]) {
$w = $data[$tab];
M()->execute("INSERT INTO $tab SELECT * FROM " . $originalDB . "." . $tab . " WHERE " . $w);
}
}
} catch (Exception $e) {
echo $e->getMessage();
return false;
}
return true;
}
//判断是否有效库
protected static function validateDB($dbname = '')
{
$dbSql = "SELECT * FROM information_schema.`SCHEMATA` WHERE SCHEMA_NAME = '$dbname'";
$ret = M()->query($dbSql);
if (!$ret) {
throw new Exception('目标数据库不存在');
}
}
//判断是否有效表
protected static function validateTab($dbname = '')
{
$tabs = M()->query("SELECT `TABLE_NAME` FROM information_schema.`TABLES` WHERE TABLE_SCHEMA = '$dbname'");
$tabs = array_column($tabs, 'table_name');;
if (!$tabs) {
throw new Exception('目标数据库数据表不存在');
}
return $tabs;
}
}
网友评论