荆轲刺秦王
sql server数据库转mysql之后,遇到:
CREATE TABLE `q_bk_date` (
`daid` int(11) NOT NULL AUTO_INCREMENT,
`partno` varchar(200) CHARACTER SET utf8 NOT NULL DEFAULT '''''',
`Bdate` date DEFAULT NULL,
`bkno` varchar(25) CHARACTER SET utf8 NOT NULL DEFAULT '''''',
`bvar` varchar(6) CHARACTER SET utf8 NOT NULL DEFAULT '''''',
`bkdate` date DEFAULT NULL,
`bz` varchar(100) CHARACTER SET utf8 DEFAULT NULL,
PRIMARY KEY (`daid`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=71 DEFAULT CHARSET=utf8mb4;
varchar字段的 default值变成了""";
写个小脚本来批量生成DDL语句,例如:
ALTER TABLE q_bk_date ALTER COLUMN partno SET DEFAULT '';
上代码:
public function generateSql()
{
// 获取所有表名
$tables = Db::getTables();
$sqlRes = '';
foreach ($tables as $key=>$value){
// 获取表信息,包括字段类型
$info = Db::getConnection()->getFieldsType($value);
$varcharFields = array();
foreach ($info as $index=>$string){
$prefix = 'varchar';
if (substr($string, 0, strlen($prefix)) === $prefix) {
//echo "字符串以 '{$prefix}' 开头";
array_push($varcharFields, $index);
}
}
//print_r($varcharFields);exit();
$databaseName = 'chip_sql2000'; // 数据库名
$tableName = $value; // 表名
// $columnName = 'your_column_name'; // 列名
foreach ($varcharFields as $ind=>$col){
$sql = "SELECT COLUMN_DEFAULT
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = ?
AND TABLE_NAME = ?
AND COLUMN_NAME = ?";
// 执行查询
$result = Db::query($sql, [$databaseName, $tableName, $col]);
//var_dump($result);exit();
// 检查结果
if($result){
if ($result[0]['COLUMN_DEFAULT'] === "''") {
$sqlRes .= "ALTER TABLE $value ALTER COLUMN $col SET DEFAULT '';";
//echo "字段{$columnName}是NOT NULL";
}
}
}
}
echo $sqlRes;exit();
}
生成结果:
ALTER TABLE q_bk_date ALTER COLUMN partno SET DEFAULT '';
ALTER TABLE q_bk_date ALTER COLUMN bkno SET DEFAULT '';
ALTER TABLE q_bk_date ALTER COLUMN bvar SET DEFAULT '';
...
然后就可以拿去执行。