seaBlueApi/Controller/Common/Upgrade.Class.php

<?php
/**
 * 系统升级脚本
 * Created by PhpStorm.
 * User: phperstar
 * Date: 2021/4/28
 * Time: 3:54 PM
 */

namespace Jindouyun\Controller\Common;

use Mall\Framework\Factory;

use JinDouYun\Cache\AllTableNameCache;

use Jindouyun\Dao\Customer\DCustomer;


class Upgrade
{
    private $serviceDB = 'default';

    /**
     * 升级sql执行
     */
    public function updateSql()
    {
        $sql = "
           ALTER TABLE `www.qianniao.vip`.`qianniao_goods_1` ADD COLUMN `showMinUnitPrice` tinyint(3) NOT NULL DEFAULT 4 COMMENT '属性最小单位价格是否展示  默认 4 不展示' AFTER `realSalesNum`;
         ";

        // 提取库名和表名
        preg_match("/(`[\S]+`)/ius", $sql, $matches);
        if(empty($matches)){
            echo 'sql错误，未匹配到库名与表名'.PHP_EOL;
            exit();
        }

        $matches = explode('`.`',$matches[0]);
        if(empty($matches)){
            echo 'sql错误，库名或表名错误'.PHP_EOL;
            exit();
        }

        foreach ($matches as $k => $v){
            $matches[$k] = str_replace('`','',$v);
        }

        $databaseName = $matches[0];
        $tableName = $matches[1];

        // 表名做处理
        $tmptableName = explode('_', $tableName);
        $tablePrefix = '';
        $sqlTableNumPoint = 1; // 出现数字的点位
        foreach ($tmptableName as $k => $v){
            if(!is_numeric($v)){
                $tablePrefix .= $v.'_';
                $sqlTableNumPoint +=1;
            }
        }

        $allTableName = AllTableNameCache::getAllTableNameByDatabaseName($databaseName);

        // 开始执行sql
        $objDCustomer = new DCustomer($this->serviceDB);
        foreach ($allTableName as $key => $value){
            if(strpos($value, $tablePrefix) !== false){

                $execTableName = explode('_', $value);
                $execTableNumPoint = 1; // 出现数字的点位
                foreach ($execTableName as $k => $v){
                    if(!is_numeric($v)){
                        $execTableNumPoint +=1;
                    }
                }
                if($execTableNumPoint != $sqlTableNumPoint){
                    continue;
                }

                $newsql = str_replace("`{$databaseName}`.`{$tableName}`","`{$databaseName}`.`{$value}`", $sql);
                $dbResult = $objDCustomer->query($newsql);
                if($dbResult == false){
                    echo $objDCustomer->error().PHP_EOL;
                    //exit();
                    continue;
                }
            }
        }

        echo "当前sql执行执行完成了".PHP_EOL;
    }
}