[代码]数据库批量更新字符属性

[代码]数据库批量更新字符属性

以yii为例：

<?php

class TestController extends CommonController
{

    /**
     * 数据库批量更新字符属性
     */
    public function actionUpdateTableField() {
        $attributes = array(
            'create_user_id' => "`create_user_id` int(10) NULL DEFAULT '0' COMMENT '创建人'",
            'create_time' => "`create_time` timestamp NULL DEFAULT NULL COMMENT '创建时间'",
            'update_user_id' => "`update_user_id` int(10) NULL DEFAULT '0' COMMENT '修改人'",
            'update_time' => "`update_time` timestamp NULL DEFAULT NULL COMMENT '更新时间'"
        );
        $allTable = $this->getAllTable();
        foreach($allTable as $value){
            $table = $value['table_name'];
            foreach ($attributes as $field=>$attr) {
                $this->updateTableField($table, $field, $attr);
            }
        }
        echo 'success';
    }

    //获取数据库所有的表名
    private function getAllTable($db = ''){
        $db = $db ? $db : config('dbname');
        $sql = "SELECT table_name FROM information_schema. TABLES where TABLE_SCHEMA =  '{$db}';";
        $AllTable = $this->app_db->createCommand($sql)->queryAll();
        return $AllTable;
    }

    //更新表字段属性
    private function updateTableField($table, $field, $attr, $db = '') {
        $db = $db ? $db : config('dbname');
        $checkFieldExists = $this->checkTableFieldExists($table, $field, $db); //检查表字段是否存在
        if($checkFieldExists) {
            //有字段时
            $flag = 'MODIFY';
        } else {
            //没字段时
            $flag = 'ADD';
        } 
        $update_sql = "ALTER TABLE `{$table}` {$flag} COLUMN {$attr};";
        echo $update_sql."\n\r\n<br/>".PHP_EOL;
        $this->app_db->createCommand($update_sql)->execute();
    }

    //检查表字段是否存在
    private function checkTableFieldExists($table, $field, $db = '') {
        $db = $db ? $db : config('dbname');
        $sql = "SELECT TABLE_NAME, COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE column_name = '{$field}' and table_name = '{$table}' AND TABLE_SCHEMA = '{$db}'";
        $checkField = $this->app_db->createCommand($sql)->queryRow();
        return $checkField ? true : false;
    }

    //获取表所有字段
    private function getTableFields($table, $db = '') {
        $db = $db ? $db : config('dbname');
        $fields = array();
        $sql = "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = '{$table}' AND TABLE_SCHEMA = '{$db}'";
        $result = Yii::app()->db->createCommand($sql)->queryAll();
        if($result) {
            foreach($result as $row) {
                array_push($fields, $row['COLUMN_NAME']);
            }
        }
        return $fields;
    }
}