PowerDesigner逆向生成表结构
software
2024-04-19 10:59
导入SQL生成
从数据库工具中导出的 sql 需要去除 各个字段和表的字符集设置
# 删除以下相关内容
## SET utf8mb4 COLLATE utf8mb4_unicode_ci
## ENGINE=InnoDB
-- 源SQL --
CREATE TABLE `table_name` (
`id` bigint unsigned NOT NULL AUTO_INCREMENT,
`content` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci COMMENT '内容'
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=DYNAMIC COMMENT='评论';
-- 删除字符集设置后 --
CREATE TABLE `table_name` (
`id` bigint unsigned NOT NULL AUTO_INCREMENT,
`content` text COMMENT '内容'
PRIMARY KEY (`id`)
) COMMENT='评论';
方法一:
1、File > Reverse Engineer > Database
1.1 确定后在 Using script files 中添加 sql 文件
方法二:
1、选中左侧 Workspace 后 右键,然后 new > Physical Data Model
2、在上面菜单中点 Database > Update Model From Database
3、在弹窗中 Using script files 中添加 sql 文件(同方法一 1.1)
导入完成后 name 字段跟 code 一样都表字段名称,需要转成注释,提供两个脚本
在上面菜单中点 Tools > Execute Commands > Edit/Run Script,将下面需要实现的脚本放入后执行
1、将Name数据替换为Comment数据
Option Explicit
ValidationMode = True
InteractiveMode = im_Batch
Dim mdl ' the current model
' get the current active model
Set mdl = ActiveModel
If (mdl Is Nothing) Then
MsgBox "There is no current Model "
ElseIf Not mdl.IsKindOf(PdPDM.cls_Model) Then
MsgBox "The current model is not an Physical Data model. "
Else
ProcessFolder mdl
End If
Private sub ProcessFolder(folder)
On Error Resume Next
Dim Tab 'running table
for each Tab in folder.tables
if not tab.isShortcut then
tab.name = tab.comment
Dim col ' running column
for each col in tab.columns
if col.comment="" then
else
col.name= col.comment
end if
next
end if
next
Dim view 'running view
for each view in folder.Views
if not view.isShortcut then
view.name = view.comment
end if
next
' go into the sub-packages
Dim f ' running folder
For Each f In folder.Packages
if not f.IsShortcut then
ProcessFolder f
end if
Next
end sub
2、将Comment数据替换为Name中数据:
Option Explicit
ValidationMode = True
InteractiveMode = im_Batch
Dim mdl ' the current model
' get the current active model
Set mdl = ActiveModel
If (mdl Is Nothing) Then
MsgBox "There is no current Model "
ElseIf Not mdl.IsKindOf(PdPDM.cls_Model) Then
MsgBox "The current model is not an Physical Data model. "
Else
ProcessFolder mdl
End If
' This routine copy name into comment for each table, each column and each view
' of the current folder
Private sub ProcessFolder(folder)
Dim Tab 'running table
for each Tab in folder.tables
if not tab.isShortcut then
tab.comment = tab.name
Dim col ' running column
for each col in tab.columns
col.comment= col.name
next
end if
next
Dim view 'running view
for each view in folder.Views
if not view.isShortcut then
view.comment = view.name
end if
next
' go into the sub-packages
Dim f ' running folder
For Each f In folder.Packages
if not f.IsShortcut then
ProcessFolder f
end if
Next
end sub