sql语句crud数据库

整理下之前写的批量更新数据库的业务
业务场景如下:
我们做的是一个商城平台,每一家接入平台的商户都有自己的库(用来存放自己的商品数据).
现在碰到的问题主要有2个

1. 当商户新接入的时候,需要从特定的库复制表结构过来
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;
    }
}