我能想到的最好的办法是与您分享我的脚本，它可以接受列定义列表并相应地更改数据库表。它可以添加、删除、修改（甚至重命名）列和更改主键。不幸的是，它是PHP编写的，所以需要重新编码，但也许您可以找到通用的思路有用。
我已经成功使用这个脚本几个月来升级我的CMS的各种安装。
该函数接受一个数组的数组作为第二个参数，其中每个后者都包含在位置处：
0 - Column name
1 - MySql column type (ex. "int" or "varchar(30)").
2 - whether columns is nullable (true for allow null, false for forbid)
3 - The default value for column (ie. "0").
4 - true, when column is part of primary key
5 - old name of a column (thus column of name in 5., if exists, is going to be renamed to column of name in 0.)
第一个参数是表名，第三个参数是函数是否应该删除在数据库表中存在但在提供的数组中被忽略的列。
对于这个恶心的协议我很抱歉，但是这个函数从来没有被设计成公共接口的一部分。:-)
下面是 CreateOrUpdateTable 函数体（稍后会解释引用）：
function CreateOrUpdateTable($tablename, array $columns, $allowdropcolumn = false)
{
foreach($columns as &$column)
{
if ((!isset($column[0])) || (!preg_match('/^[a-zA-Z0-9_\-]+$/', $column[0])))
$column[0] = 'TableColumn' . array_search($column, $columns);
if ((!isset($column[1])) || (!preg_match('/^(int|date|datetime|decimal\([0-9]+,[0-9]+\)|varchar\([0-9]+\)|char\([0-9]+\)|text|tinyint)$/', $column[1])))
$column[1] = 'int';
if ((!isset($column[2])) || (!is_bool($column[2])))
$column[2] = ALLOW_NULL;
if ((!isset($column[3])) || (!is_string($column[3])))
$column[3] = (($column[2] == ALLOW_NULL || $column[1] === 'text') ? 'NULL' : ($column[1] == 'int' ? "'0'" : ($column[1] == 'tinyint' ? "'0'" : ($column[1] == 'decimal' ? "'0.00'" : ($column[1] == 'date' ? "'1900-01-01'" : ($column[1] == 'datetime' ? "'1900-01-01 00:00:00'" : "''"))))));
else
$column[3] = "'" . Uti::Sql($column[3]) . "'";
if ((!isset($column[4])) || (!is_bool($column[4])))
$column[4] = false;
}
unset($column);
if (!$this->TableExists($tablename))
{
$statements = array();
foreach ($columns as $column)
{
$statement = $this->ColumnCreationStatement($column);
if ($statement !== '')
$statements[] = $statement;
}
$this->Query("create table " . $tablename . "(" . implode(',', $statements) . ") ENGINE=InnoDB DEFAULT CHARSET=latin2");
}
else
{
$this->Select("show columns in " . $tablename);
$existing = $this->AllRows(null, 'Field');
$oldkeys = array(); $newkeys = array();
foreach ($existing as $e)
if ($e['Key'] === 'PRI')
$oldkeys[] = $e['Field'];
sort($oldkeys);
$oldkeys = implode(',', $oldkeys);
$lastcolumn = '';
foreach ($columns as $column)
{
if ($column[4])
$newkeys[] = $column[0];
$newtype = $column[1] . ($column[1] === 'int' ? '(11)' : ($column[1] === 'tinyint' ? '(4)' : ''));
$newnull = ($column[2] === ALLOW_NULL ? 'YES' : 'NO');
$newdefault = $column[3];
if (isset($existing[$column[0]]))
{
$oldtype = $existing[$column[0]]['Type'];
$oldnull = $existing[$column[0]]['Null'];
$olddefault = isset($existing[$column[0]]['Default']) ? "'" . Uti::Sql($existing[$column[0]]['Default']) . "'" : "NULL";
if (($oldtype != $newtype) || ($oldnull != $newnull) || ($olddefault != $newdefault))
{
$this->SaveToLog("Altering table [" . $tablename . "], column [" . $column[0] . "], changing: type [" .
$oldtype . "] => [" . $newtype . "] nullability [" . $oldnull . "] => [" . $newnull . "] default [" . $olddefault . "] => [" . $newdefault . "]", true);
$statement = $this->ColumnCreationStatement($column, false);
if ($statement !== '')
$this->Query("alter table " . $tablename . " change " . $column[0] . " " . $statement);
}
unset($existing[$column[0]]);
}
else if (isset($column[5]) && (Uti::AnyExists(array_keys($existing), $column[5]) !== false))
{
$oldcolumn = Uti::AnyExists(array_keys($existing), $column[5]);
$this->SaveToLog("Altering table [" . $tablename . "], column [" . $column[0] . "], renaming: name [" . $oldcolumn . "] => [" . $column[0] . "] " .
" type [" . $newtype . "] nullability [" . $newnull . "] default [" . $newdefault . "]", true);
$statement = $this->ColumnCreationStatement($column, false);
if ($statement !== '')
$this->Query("alter table " . $tablename . " change " . $oldcolumn . " " . $statement);
unset($existing[$oldcolumn]);
}
else
{
$this->SaveToLog("Altering table [" . $tablename . "], column [" . $column[0] . "], adding: name [" . $column[0] . "] " .
" type [" . $newtype . "] nullability [" . $newnull . "] default [" . $newdefault . "]", true);
$statement = $this->ColumnCreationStatement($column, false);
if ($statement !== '')
$this->Query("alter table " . $tablename . " add " . $statement . " " . $lastcolumn);
}
$lastcolumn = 'AFTER ' . $column[0];
}
if ($allowdropcolumn)
{
foreach ($existing as $e)
{
$this->SaveToLog("Altering table [" . $tablename . "], column [" . $e['Field'] . "], dropping", true);
$this->Query("alter table " . $tablename . " drop " . $e['Field']);
}
}
sort($newkeys);
$newkeys = implode(',',$newkeys);
if ($oldkeys != $newkeys)
{
$this->SaveToLog("Altering table [" . $tablename . "], changing keys [" . $oldkeys . "] => [" . $newkeys . "]", true);
if ($oldkeys !== '')
$this->Query("alter table " . $tablename . " drop primary key");
if ($newkeys !== '')
$this->Query("alter table " . $tablename . " add primary key (" . $newkeys . ")");
}
}
}
以下外部函数需要解释:
ColumnCreationStatement提供了修改/创建表格部分的语句:
private function ColumnCreationStatement(array $columninfo, $includekey = true)
{
$r = '';
if ((count($columninfo) > 0) && (preg_match('/^[a-zA-Z0-9_\-]+$/', $columninfo[0])))
{
$r .= $columninfo[0];
if ((count($columninfo) > 1) && (preg_match('/^(int|date|datetime|decimal\([0-9]+,[0-9]+\)|varchar\([0-9]+\)|char\([0-9]+\)|text|tinyint)$/', $columninfo[1])))
$r .= ' ' . $columninfo[1];
else
$r .= ' int';
if ((count($columninfo) > 2) && is_bool($columninfo[2]))
$r .= ($columninfo[2] === NOT_NULL ? ' not null' : ' null');
if ((count($columninfo) > 3) && is_string($columninfo[3]) && ($columninfo[3] !== '') && ($columninfo[1] !== 'text'))
$r .= " default " . $columninfo[3];
if ((count($columninfo) > 4) && is_bool($columninfo[4]) && $includekey)
$r .= ($columninfo[4] === true ? ', primary key(' . $columninfo[0] . ')' : '');
}
return $r;
}
TableExists只是验证数据库中是否存在表（使用show tables like）。
Query执行MySql语句（是的：不返回结果;])
Select和AllRows是返回行作为哈希表集合的快捷方式。
SaveToLog很明显。 :-)
Uti :: AnyExists看起来像这样：
public static function AnyExists($haystack, $needles, $separator = ';')
{
if (!is_array($needles))
$needles = explode($separator, $needles);
foreach ($needles as $needle)
{
if (array_search($needle, $haystack) !== false)
return $needle;
}
return false;
}
我希望这些都能有所帮助。如果您有任何问题，请在评论中随时提问。:-)
