后台数据库规范
修订记录
日期
版本
描述
作者
2015-11-30
0.1
初稿
一 表、字段、索引设计规范
数据中表示最基础的元素，表中的每个字段名，在字段上的索引建立都需要规范化和统一化，具体的措施如下：
1.表名、字段名必须使用小写字母，多个单词以“_”分割；
2.表名、字段名必须不超过35个字符；
3.表名、字段名使用有意义的单词，禁止使用MYSQL关键字，不超长不使用简写；
4.表名、字段名必须有注释，尤其是状态字段要详细说明每个状态的意义；
5.不同的系统表名以系统名简称开头；
6.表一律使用InnoDB存储引擎，面向事务处理，特殊情况需知会；
7.存储浮点类型使用DECLIMAL代替FLOAT和DOUBLE；
8.尽可能控制字段长度，字段长度以2的幂次方长度为标准；
9.索引字段不允许为空，设置默认值；
10.建表的时候不允许设定字符集；
11.尽可能不使用TEXT、BOLB类型
12.存储日期用DATETIME类型，不用VARCHAR类型存储时间类型
13.唯一索引必须按照“uidx_表名简写_字段名1_字段名2[_字段名]”进行命名
14.非唯一索引必须按照“idx_表名简写_字段名1_字段名2[_字段名]”进行命名
15.索引名不超过35个字符，若字段过多，使用简写
16.视图必须以“vw_”开头，例：TEST表上建一个视图：vw _test
17.学会用explain判断语句的执行效率
18.SQL语句不允许全表扫（类型表除外），后续会监控全表扫，如发现必须整改
二 储过程、函数、事件命名规范
在编写存储过程，函数，事件是按下列规则命名：
1.存储过程名必须以“pro_[系统名]_”开头，函数以“fun_[系统名]_”开头，事件以“e_[系统名]_”开头，新增前先看看是否已经存在同名
2.存储过程、函数、事件名必须大写，入参以“i_”开头，出参以“o_”开头，出入参以“io_”开头，其他定义参数以“v_”开头，游标以“cur_”开头
3.存储过程、函数、事件中表的前缀命名规范：
基础表：com_table_name,  common，用户信息包含在内
本地生活：lbs_table_name  基于位置的应用
应用商城：app_table_name  推广app
个人中心：pcs_table_name  person center system
设备相关：eqm_table_name  equipment
DB框架：event_table_name 定时任务框架
三 SQL脚本文件规范
1.新建一个db开发流 ，根据版本创建不同的文件夹
2.脚本命名：项目名称_ id_序号_文件内容类型_创建人名全拼
序号规范：01、10 
同一个任务下的sql最好通过序号标示出执行顺序，也可以给部署人员特殊部署说明
文件内容类型
描述
tab
对表相关操作，新建表时索引可以放在此文件中
idx
对索引相关操作
pro
对存储过程相关操作
fun
对函数相关操作
job
对定时任务相关操作
dml
批量处理数据操作，insert、delete、update
grant
授权语句
fix
修复脚本，不通过版本管理器移交部署
例： zjcs-commons -_01_tab_tag.sql
tag是标记号，每个开发人员的sql脚本需要加入注释和表的字段说明，所用开发人员的脚本在同一个sql文件中。
3.SQL脚本文件请在开发环境执行通过后在移交
4.移交脚本并且部署成功后，请尽量不要对已部署成功脚本做修改，如需修改，请先给DBA或有权限的测试人员fix脚本，清理掉会影响该脚本执行的内容
5.脚本用UTF-8无BOM格式保存，保证数据库脚本编码的一致性，避免出现乱码。
四 表、字段、索引语法规范
/*删除表*/
DROP TABLE test_user_info;
/*创建表*/
CREATE TABLE test_user_info (
created_user  INT(32) NOT NULL COMMENT '创建人' ,
date_created  DATETIME NOT NULL COMMENT '创建时间' ,
updated_user  INT(32) NOT NULL COMMENT '修改人' ,
date_updated  DATETIME NOT NULL COMMENT '修改时间' ,
id_test_user_info  drop删除表INT(32) NOT NULL AUTO_INCREMENT COMMENT '主键id ' ,
user_name  VARCHAR(20) NOT NULL COMMENT '用户姓名' ,
login_name  VARCHAR(20) NOT NULL COMMENT '用户登录名' ,
PRIMARY KEY (id_user_info)
)
ENGINE=InnoDB
COMMENT='用户信息表'
;
/*新建索引*/
CREATE INDEX idx_ui_user_name ON test_user_info (user_name);
CREATE INDEX idx_ui_id_username ON idx_ui_user_name (id_user_info, user_name);
CREATE UNIQUE INDEX uidx_ui_login_name ON test_user_info (login_name);
/*删除索引*/
DROP INDEX idx_ui_user_name ON test_user_info;
/*新增字段*/
ALTER TABLE test_user_info ADD SEX CHAR(1) NOT NULL DEFAULT 0 COMMENT '性别（0：女；1：男）';
/*修改字段*/
ALTER TABLE test_user_info CHANGE user_name user_name VARCHAR(30);
/*删除字段*/
ALTER TABLE test_user_info DROP COLUMN sex;
五 存储过程、函数SQL脚本规范
DROP PROCEDURE IF EXISTS pro_test;
DELIMITER //
CREATE PROCEDURE pro_test ()
BEGIN
-- 记录异常信息
        CALL pro_proc_error_info('pro_test','NOT FOUND',            CONCAT_WS(';',v_parent_id, v_customer_id));
-- 存储过程内容部分
END;
//
DELIMITER ;
例：
DROP PROCEDURE IF EXISTS pro_test;
DELIMITER //
CREATE PROCEDURE pro_test ()
BEGIN
DECLARE v_customer_id INT;
DECLARE v_customer_id_end INT;
DECLARE v_level VARCHAR(100);
DECLARE v_parent_id INT;
DECLARE v_i INT;
DECLARE done INT DEFAULT FALSE;
DECLARE cur_stats_list CURSOR FOR SELECT c.customer_id,c.customer_id FROM CUSTOMER c WHERE c.customer_id<>117;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
-- 开启事务
START TRANSACTION;
open cur_stats_list;
read_loop: LOOP
FETCH cur_stats_list INTO v_customer_id, v_customer_id_end;
IF done THEN
-- 记录异常信息
CALL pro_proc_error_info('PRO_TEST','NOT FOUND', CONCAT_WS(';', v_parent_id, v_customer_id)); -- 异常处理
LEAVE read_loop;
END IF;
SET v_parent_id = v_customer_id;
SET V_LEVEL = '';
SET V_I = 0;
WHILE v_parent_id <> 117 && V_I <= 5 DO
SELECT parent_id INTO v_parent_id FROM CUSTOMER C WHERE c.customer_id = v_parent_id;
SET v_level = CONCAT(v_parent_id,',', v_level);
SET V_I = V_I + 1 ;
END WHILE;
UPDATE CUSTOMER c SET c.level = substring(v_level,1,(length(v_level)-1)) WHERE C.customer_id = v_customer_id_end;
-- 提交事务
COMMIT;
END LOOP;
-- 关闭游标
CLOSE cur_stars_list;
END;
/
DELIMITER ;
六 DB开发部署流程说明
1.开发人员按照SQL脚本文件规范在开发环境测试通过，SOURCE命名执行.SQL文件
2.测试通过后上传脚本至：
svn://...... /系统设计/数据库设计/版本号/*.sql
3.填写DB移交部署文档
4.邮件发送DB移交部署文档给DBA执行
七 DB生产问题处理流程说明
1.生产问题处理人写好处理问题的SQL语句及处理失败的回滚方案，邮件发送给小组负责人，抄送，项目负责人邮件回复确认可修改，由DBA或运维人员执行SQL后邮件通知问题处理人处理结果；
2.生产问题处理人验证是否处理成功，不成功：通知运维执行回滚方案；成功：通知用户。
八 表结构设计、规范心得
1.互联网项目的表结构应尽量的往快速查询方面考虑
2.只要有利于查询效率，适当的冗余数据是可以接受的
3.表、字段的注释很重要，对开发效率有很大的提升，修改了字段描述应即使更新，尤其是状态字段。