背景
比如,我们做的是一套定制化ERP系统。某一个客户他们的商品编码或sku,与咱们最初设计的不一致,新客户的编码很长。此时,商品编码分布在各个业务库、业务表中。
上脚本
SELECT
concat(
"ALTER TABLE ",
"`",
col.TABLE_NAME,
"`", " modify ", "`",
col.COLUMN_NAME,
"`", " ", ' varchar(128) ', " ",
IF ( col.IS_NULLABLE = 'NO', " NOT NULL ", "" ),
IF ( col.COLUMN_DEFAULT IS NULL,
IF ( col.EXTRA = 'auto_increment' OR col.IS_NULLABLE = 'NO', " ", " DEFAULT null " ),
concat( " DEFAULT ", IF ( col.DATA_TYPE = 'timestamp' OR col.DATA_TYPE = 'bit', col.COLUMN_DEFAULT, concat( "'", col.COLUMN_DEFAULT, "'" ) ))
),
IF
( col.EXTRA IS NULL, " ", concat( " ", col.EXTRA, " " ) ),
" COMMENT ",
" ",
"'",
col.COLUMN_COMMENT,
"'",
";"
) change_column_type
FROM
information_schema.COLUMNS col
JOIN information_schema.TABLES tbl ON col.TABLE_SCHEMA = tbl.TABLE_SCHEMA
AND col.TABLE_NAME = tbl.TABLE_NAME
WHERE
col.TABLE_SCHEMA = 'mdmbase'
AND col.column_name = 'item_code'
AND tbl.TABLE_TYPE = 'BASE TABLE';