数据库相关工作流程与工具

分享下，工作过程中数据库相关工作的流程： 

1.接到产品需求，根据需求进行领域模型设计

　　主要识别有哪些实体及关系、相关方及角色。例如：A既是服务提供方也可以是业务提供方甚至同时是接入方。他们在模型上是要考虑的。

2.在navicat -> 模型工具栏中，利用图形化页面进行拖拽表进行物理模型的设计

3.可以全选-复制形式将图形转化为建表sql,也可以通过navicat直接同步到目标数据库

4.通过工具将表的外键和索引名字按照公司既定的规范进行命名

5.进行代码开发

6.将数据库设计导成word文档及数据库sql脚本用于基线和交付。

 ps1.为了提高设计效率，外键或者建立索引的时候，可以随便取名字,而且外键自动建立同名索引，命名也不符合规范，后续用rename_db工具按照约定的规范重新命名外键和索引的名字。

 设计完成物理模型如图所示，可以直接导入数据库。（为方便沟通和其他人员理解模型，建议设置实体外键，后续上线可去除）

 ps2: 将数据库中外键和索引的名字按照规范进行统一重命名。可以使用如下存储过程

1 -- 文中sql 支持MySql
2
3 -- powered by wanglifeng https://www.cnblogs.com/wanglifeng717
4 -- 调用方式
5 -- CALL rename_db(\'tbl_name_test\',\'tbl_bat_\');
6
7
8 -- --------------------------------------------------------------------
9 -- ----从此往下，数据库外键、索引、唯一键重命名的相关内容--------------
10 -- -- powered by wanglifeng https://www.cnblogs.com/wanglifeng717
11 -- --------------------------------------------------------------------
12 DROP PROCEDURE IF EXISTS rename_db;
13 DELIMITER $
14 -- 将数据库对象重命名存储过程。
15 -- 参数说明：db_name 数据库名字 prefix_tbl_name表的前缀名字，例如：tbl_bat_face_info 则prefix_tbl_name=\'tbl_bat_\'
16 CREATE PROCEDURE rename_db(db_name VARCHAR(255),prefix_tbl_name VARCHAR(255))
17 BEGIN
18 SET FOREIGN_KEY_CHECKS = 0;
19 CALL rename_idx (db_name,prefix_tbl_name);
20
21 CALL rename_fk (db_name,prefix_tbl_name);
22
23 SET FOREIGN_KEY_CHECKS = 1;
24 END$
25 DELIMITER ;
26
27
28
29 -- ------------------------------------------
30 -- ------------------------------------------
31 -- 外键重命名存储过程
32 -- powered by wanglifeng https://www.cnblogs.com/wanglifeng717
33
34 DROP PROCEDURE IF EXISTS `rename_fk`;
35 DELIMITER $
36 CREATE PROCEDURE `rename_fk`(db_name VARCHAR(255),prefix_tbl_name VARCHAR(255))
37 BEGIN
38 DECLARE tb_name VARCHAR (200) ;
39 DECLARE cons_name VARCHAR (200) ;
40 DECLARE col_name VARCHAR (200) ;
41 DECLARE ref_tbl_name VARCHAR (200) ;
42 DECLARE ref_col VARCHAR (200) ;
43 -- 表名后缀，去除tbl_bat_之后的内容
44 DECLARE suffix_tbl_name VARCHAR(200);
45
46
47
48 DECLARE no_more_record INT DEFAULT 0 ;
49 -- 查询每个表下的约束。（外键和唯一键）
50 DECLARE cons CURSOR FOR
51 SELECT
52 t.TABLE_NAME,
53 t.CONSTRAINT_NAME,
54 k.COLUMN_NAME,
55 k.REFERENCED_TABLE_NAME,
56 k.REFERENCED_COLUMN_NAME
57 FROM
58 information_schema.TABLE_CONSTRAINTS t
59 JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE k
60 ON t.CONSTRAINT_NAME = k.CONSTRAINT_NAME
61 AND t.TABLE_NAME = k.TABLE_NAME
62 AND t.CONSTRAINT_SCHEMA=k.CONSTRAINT_SCHEMA
63 WHERE t.CONSTRAINT_TYPE=\'FOREIGN KEY\' AND t.table_schema = db_name ;
64 -- 游标游到底，找不到的时候标志位。注：如果select into var时候，如果找不到，游标的while循环也结束了，no_more_record =1
65 DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_more_record = 1 ;
66 -- 有多少外键名字已经是合法的了
67 SET @var_fk_rename_count=0;
68 SET @var_fk_valid_count=0;
69
70 -- 禁用外键
71 SET FOREIGN_KEY_CHECKS=0;
72 -- 开游标
73 OPEN cons;
74 -- 第一个要预先取得，之后在循环里面取
75 FETCH cons INTO tb_name,cons_name,col_name,ref_tbl_name,ref_col;
76 WHILE no_more_record !=1 DO
77 -- 截取表名后半部分。去除tbl_bat_字样
78 SET suffix_tbl_name =REPLACE(tb_name,prefix_tbl_name,\'\');
79 -- 统计一共重命名了多少外键名称。
80 SET @var_fk_rename_count=@var_fk_rename_count+1;
81
82 -- 先删除以前的外键
83 SET @drop_sql=CONCAT(\'alter table \',tb_name,\' drop foreign key `\',cons_name,\'`;\');
84
85 -- 拼出外键名字
86 SET @var_new_fk_name=CONCAT(\'fk_\',suffix_tbl_name,\'_\',col_name);
87
88 IF LENGTH(@var_new_fk_name)>64 THEN
89 SELECT t.ordinal_position INTO @var_ordinal_position FROM information_schema.COLUMNS t WHERE t.column_name=col_name AND t.table_name=tb_name;
90 SET @var_new_fk_name=CONCAT(\'fk_\',suffix_tbl_name,\'_\',@var_ordinal_position);
91 END IF;
92
93 -- 新建新的外键
94 SET @cre_sql=CONCAT(\'alter table \',tb_name,\' add constraint \',@var_new_fk_name,\' foreign key (\',col_name,\') references \',ref_tbl_name,\'(\',ref_col,\');\');
95
96 -- 动态执行拼接出来的sql
97 PREPARE stmt FROM @drop_sql;
98 EXECUTE stmt;
99 DEALLOCATE PREPARE stmt;
100
101 -- 动态执行拼接出来的sql
102 PREPARE stmt FROM @cre_sql;
103 EXECUTE stmt;
104 DEALLOCATE PREPARE stmt;
105
106 -- 游标继续向下走，类似于i=i+1
107 FETCH cons INTO tb_name,cons_name,col_name,ref_tbl_name,ref_col;
108 END WHILE;
109
110 -- 开启外键
111 SET FOREIGN_KEY_CHECKS=1;
112 SELECT @var_fk_rename_count AS \'重命名的外键数量\';
113 END$
114 DELIMITER ;
115
116
117
118 -- ------------------------------------------
119 -- ------------------------------------------
120
121 DROP PROCEDURE IF EXISTS rename_idx ;
122 DELIMITER $$
123 -- 给索引重命名
124 CREATE PROCEDURE `rename_idx`(db_name VARCHAR(255),prefix_tbl_name VARCHAR(255))
125 BEGIN
126 DECLARE is_uni VARCHAR (200) ;
127 DECLARE tbl_name VARCHAR (200) ;
128 DECLARE idx_name VARCHAR (200) ;
129 DECLARE idx_col VARCHAR (200) ;
130 DECLARE pre_idx VARCHAR(200);
131 DECLARE flag VARCHAR(200);
132 DECLARE var_count INT;
133
134 -- 表名后缀，去除tbl_bat_之后的内容
135 DECLARE suffix_tbl_name VARCHAR(200);
136
137 DECLARE no_more_record INT DEFAULT 0 ;
138
139 -- 查看数据库中所有表的所有索引，索引字段以（id，name）形式列出来
140 DECLARE idxs CURSOR FOR
141 SELECT
142 non_unique ,
143 TABLE_NAME ,
144 index_name ,
145 GROUP_CONCAT(column_name ORDER BY seq_in_index) -- 列合并
146 FROM
147 information_schema.statistics -- 提供了关于表索引的信息。是show index from schemaname.tablename的结果取之此表。
148 WHERE table_schema = db_name AND table_name IN
149 (SELECT table_name
150 FROM information_schema.TABLES -- 提供了关于数据库中的表的信息（包括视图）。是show tables from schemaname的结果取之此表。
151 WHERE TABLE_SCHEMA = db_name)
152 GROUP BY TABLE_NAME,INDEX_NAME ;
153
154
155 -- 查询要循环多少次
156 SELECT COUNT(*) INTO var_count FROM (SELECT
157 non_unique ,
158 TABLE_NAME ,
159 index_name ,
160 GROUP_CONCAT(column_name ORDER BY seq_in_index) -- 列合并
161 FROM
162 information_schema.statistics -- 提供了关于表索引的信息。是show index from schemaname.tablename的结果取之此表。
163 WHERE table_schema = db_name AND table_name IN
164 (SELECT table_name
165 FROM information_schema.TABLES -- 提供了关于数据库中的表的信息（包括视图）。是show tables from schemaname的结果取之此表。
166 WHERE TABLE_SCHEMA = db_name)
167 GROUP BY TABLE_NAME,INDEX_NAME ) tt;
168
169
170 -- 取消外键约束校验
171 SET FOREIGN_KEY_CHECKS = 0;
172
173
174
175 -- 计数到底有多少的索引被重命名，必须要赋初始值
176 SET @idx_rename_count=0;
177 SET @pk_count=0;
178 SET @uni_idx_rename_count=0;
179
180 -- ==================整个循环体开始部分=================
181 OPEN idxs;
182 loop_idxs:LOOP
183 IF var_count = 0 THEN
184 LEAVE loop_idxs;
185 END IF;
186 FETCH idxs INTO is_uni,tbl_name,idx_name,idx_col;
187
188
189 -- 开始业务-------------------------------------------------
190
191 SET suffix_tbl_name =REPLACE(tbl_name,prefix_tbl_name,\'\');
192
193 -- 如果索引类型是主键，无需重命名。
194 IF idx_name =\'PRIMARY\' THEN
195 SET @pk_count=@pk_count+1;
196 ELSE
197
198 -- 判断该索引的类型
199 SET flag=\'no\';-- 这一步必须要有，因为如果搜出来是空，flag还是上次的值
200 SELECT
201 t.CONSTRAINT_TYPE INTO flag
202 FROM
203 information_schema.TABLE_CONSTRAINTS t
204 WHERE t.table_schema = db_name
205 AND t.table_name=tbl_name
206 AND t.CONSTRAINT_NAME=idx_name;
207
208 -- 判断是否唯一索引,决定新建索引是否有Unique前缀名字
209 IF flag=\'UNIQUE\' THEN
210 SET @uni_idx_rename_count=@uni_idx_rename_count+1;
211
212 SET pre_idx=\' unique index uni_idx_\';
213
214 SET @sub_name=CONCAT(\'uni_idx_\',suffix_tbl_name,\'_\',REPLACE(idx_col,\',\',\'_\'));
215 -- 查看索引名字是否超长
216 IF LENGTH(@sub_name)>64 THEN
217 SELECT t.ordinal_position INTO @var_ordinal_position FROM information_schema.COLUMNS t WHERE t.column_name=idx_col AND t.table_name=tbl_name;
218 SET @sub_name=CONCAT(pre_idx,suffix_tbl_name,\'_\',@var_ordinal_position);
219 ELSE
220 SET @sub_name=CONCAT(pre_idx,suffix_tbl_name,\'_\',REPLACE(idx_col,\',\',\'_\'));
221 END IF;
222
223 ELSE
224 -- 计数器加1
225 SET @idx_rename_count=@idx_rename_count+1;
226
227 SET pre_idx=\' index idx_\';
228
229 SET @sub_name=CONCAT(\'idx_\',suffix_tbl_name,\'_\',REPLACE(idx_col,\',\',\'_\'));
230 -- 查看索引名字是否超长
231 IF LENGTH(@sub_name)>64 THEN
232 SELECT t.ordinal_position INTO @var_ordinal_position FROM information_schema.COLUMNS t WHERE t.column_name=idx_col AND t.table_name=tbl_name;
233 SET @sub_name=CONCAT(pre_idx,suffix_tbl_name,\'_\',@var_ordinal_position);
234 ELSE
235 SET @sub_name=CONCAT(pre_idx,suffix_tbl_name,\'_\',REPLACE(idx_col,\',\',\'_\'));
236
237 END IF;
238
239 END IF;
240
241 SET @drop_sql=CONCAT(\'alter table \',tbl_name,\' drop index `\',idx_name,\'`;\');
242 SET @cre_sql=CONCAT(\'alter table \',tbl_name,\' add \',@sub_name,\' (`\',REPLACE(idx_col,\',\',\'`,`\'),\'`);\');
243
244
245 -- 动态执行sql
246 PREPARE stmt FROM @drop_sql;
247 EXECUTE stmt;
248 DEALLOCATE PREPARE stmt;
249
250 PREPARE stmt FROM @cre_sql;
251 EXECUTE stmt;
252 DEALLOCATE PREPARE stmt;
253
254 END IF;
255
256 -- 结束业务-------------------------------------------------
257 SET var_count=var_count -1;
258 END LOOP;
259 CLOSE idxs;
260
261 -- ================================循环体业务结束的地方====================================
262
263
264 -- 开启外键约束校验
265 SET FOREIGN_KEY_CHECKS = 1;
266
267 SELECT @pk_count AS \'主键的数量\';
268 SELECT @idx_rename_count AS \'重命名的索引数量\';
269 SELECT @uni_idx_rename_count AS \'重命名唯一索引数量\';
270
271 END$$
272
273 DELIMITER ;

来源：https://www.cnblogs.com/wanglifeng717/p/15826673.html
图文来源于网络，如有侵权请联系删除。