think php sql生成,thinkphp快速生成数据库迁移文件

此方法将数据库结构转为迁移工具的语法，快速的生成整个库的迁移文件，代码如下：

public function index()

{

$tables = Db::query("SHOW TABLE STATUS");

$str = '';

foreach ($tables as $table) {

$tableName = $table['Name'];

//表字段

$fields = Db::query("SHOW FULL FIELDS FROM {$tableName}");

$str .= '$this->table("' . $tableName . '")' . "

";

//处理表字段

foreach ($fields as $field) {

if ($field['Field'] == 'id') {

continue;

}

$fieldName = $field['Field'];

$rule = "/([a-z]+)(\((\d+\,?\d?)\)?)?\s?(unsigned)?/";

preg_match($rule, $field['Type'], $result);

$fieldType = $this->fieldType($result[1]);

//var_dump($result);

$option = []; //['limit' => 30, 'default' => 'index', 'null' => false, 'comment' => '模块标识'];

//字段注释

$option['comment'] = $field['Comment'];

//默认值

if ($field['Default'] !== null) {

$option['default'] = $field['Default'];

}

//是否为空

$option['null'] = $field['Null'] == 'YES' ? true : false;

//长度

if (isset($result[3])) {

if ($fieldType == 'decimal' || $fieldType == 'float') { //decimal

list($precision, $scale) = explode(',', $result[3]);

$option['precision'] = $precision;

$option['scale'] = $scale;

} else {

$option['limit'] = $result[3];

}

}

//timestamp 的自动更新

if ($fieldType == 'timestamp' && $field['Extra']) {

$option['update'] = 'CURRENT_TIMESTAMP';

}

//无符号

if (in_array('unsigned', $result)) {

$option['signed'] = false;

}

//enum

if ($fieldType == 'enum') {

$rule = "/([a-z]+)\((\'(\w+)\'\,?)+\)/";

preg_match($rule, $field['Type'], $result);

eval('$values = ' . str_replace('enum', 'array', $result['0']) . ';');

$option['values'] = $values;

}

$option = var_export($option, true);

$str .= "->addColumn('{$fieldName}', '{$fieldType}', {$option})" . "

";

}

//表索引

$indexs = Db::query("SHOW INDEX FROM {$tableName}");

$indexArr = [];

foreach ($indexs as $index) {

if ($index['Column_name'] == 'id') {

continue;

}

$indexArr[$index['Key_name']]['field'][] = $index['Column_name'];

if (!$index['Non_unique']) {

$indexArr[$index['Key_name']]['option'] = ['unique' => true];

} else {

$indexArr[$index['Key_name']]['option'] = [];

}

}

//处理索引

if (!empty($indexArr)) {

foreach ($indexArr as $_index) {

$indexField = var_export($_index['field'], true);

$indexOption = var_export($_index['option'], true);

$str .= "->addIndex($indexField, $indexOption)" . "

";

}

}

$str .= "->setEngine('{$table['Engine']}')->setComment('{$table['Comment']}')->create();

"; //表引擎 //表注释

}

return $str;

}

/**

* 字段类型

* @param type $type

* @return type

*/

protected function fieldType($type)

{

switch ($type) {

case 'int':

case 'smallint':

case 'tinyint':

case 'year':

case 'mediumint':

$t = 'integer';

break;

case 'bigint':

$t = 'biginteger';

break;

case 'char':

case 'varchar':

$t = 'string';

break;

case 'double':

$t = 'float';

break;

case 'longtext':

case 'mediumtext':

case 'text':

$t = 'text';

break;

default :

$t = $type;

}

return $t;

}