荆轲刺秦王

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_DEFAULTFROM INFORMATION_SCHEMA.COLUMNSWHERE 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 '';
...

然后就可以拿去执行。