1 編寫目的

本手冊(cè)是為指導(dǎo)開發(fā)人員按照文檔中的規(guī)范進(jìn)行MYSQL數(shù)據(jù)庫設(shè)計(jì)及SQL編碼。

2 數(shù)據(jù)庫對(duì)象定義規(guī)范

2.1 表的定義規(guī)則

2.1.1 表名的規(guī)定

1. 使用英語

命名應(yīng)該使用英文單詞塌计，避免使用拼音栗恩，特別不應(yīng)該使用拼音簡寫;

命名不允許使用中文或者特殊字符男窟，因?yàn)椴皇撬袛?shù)據(jù)庫都支持;

只使用字母(A-Za-z),數(shù)字(0-9)以及下劃線()势决，不使用其他字符;

應(yīng)該以字母開頭;

2. 采用單數(shù)

表名使用的單詞步淹，應(yīng)是單數(shù)从隆，而不是復(fù)數(shù); 如工人表，選擇WORKER,

而不是WORKERS;

除非一些約定俗成的單詞贤旷，如SALES;

3. 采用大寫

Mysql 大小寫轉(zhuǎn)換需要一定的開銷广料，要求數(shù)據(jù)庫配置為大小寫敏感，所以要求數(shù)據(jù)庫對(duì)象名稱建議一律大寫幼驶，也方便不同數(shù)據(jù)庫間的移植;

禁止使用大小寫混合的方式

4. 單詞分隔

命名的各單詞之間可以使用下劃線(_) 進(jìn)行分隔;

一般不要超過30個(gè)字符

5. 表名的前綴

一個(gè)項(xiàng)目或一個(gè)模塊的表名定義建議前綴最好統(tǒng)一艾杏，方便管理

2.1.2 表的設(shè)計(jì)規(guī)則

1. 如果沒有特殊要求，表的缺省引擎采用Innodb,支持事務(wù)盅藻，鎖方式為行鎖购桑，并發(fā)性能高畅铭。

2. 每個(gè)表要求定義主鍵

3. 平衡范式與冗余關(guān)系，效率優(yōu)先勃蜘；原則上表設(shè)計(jì)必須遵守第三范式,如果因?yàn)楹侠淼娜哂嘧侄螌?huì)給我們減少join的查詢除外;

4. 單行記錄禁止超過8K

5. 禁止使用外鍵

6. 單表列數(shù)目建議小于30

7. 如果有可能硕噩，把表設(shè)計(jì)成固定長度的，可以提高性能缭贡。

8. 索引并不是越多越好炉擅，索引固然可以提高相應(yīng)的 select 的效率，但同時(shí)也降低了 insert 及 update 的效率阳惹，索引最好不要超過5個(gè)

2.1.3 列的規(guī)定

1. 列名建議大寫

2. 單詞采用單數(shù)

3. 是否采用下劃線要統(tǒng)一

4. 列名應(yīng)避免二義性

關(guān)聯(lián)表中的同義字段列名應(yīng)相同谍失，一個(gè)應(yīng)用程序中的多個(gè)關(guān)聯(lián)表，含有同樣含義的字段莹汤，建議采用相同的列名快鱼，同時(shí)字段名的數(shù)據(jù)類型也要求相同。

如客戶ID 一張表定義為CUSTOMER_ID 類型varchar
另一張表定義為CUSTOMER_NUM 類型int

如果兩張表依賴客戶ID字段關(guān)聯(lián)查詢時(shí)纲岭，可能因?yàn)殡[式轉(zhuǎn)換導(dǎo)致索引失效抹竹。

5. 主鍵

一定要顯式定義主鍵

采用與業(yè)務(wù)無關(guān)的單獨(dú)列

建議采用自增列

數(shù)據(jù)類型采用int，并盡可能小止潮，能用tinyint就不用int窃判，能用int就不用bigint

將主鍵放在表的第一列

禁止使用varchar類型作為主鍵語句設(shè)計(jì)

不建議使用UUID類型設(shè)計(jì)主鍵，

首先UUID長度過長沽翔，另外InnoDB為聚集主鍵類型的引擎兢孝，數(shù)據(jù)會(huì)按照主鍵進(jìn)行排序，由于UUID的無序性仅偎，InnoDB會(huì)產(chǎn)生巨大的IO壓力

6. 索引列

索引列必須定義為not null跨蟹，并設(shè)置default值

復(fù)合索引，選擇性高的字段排在前面橘沥。

7. 字段

l 長度設(shè)計(jì)需要根據(jù)業(yè)務(wù)實(shí)際需要進(jìn)行長度控制窗轩，禁止預(yù)留過長空間。例如status使用varchar(128)進(jìn)行存儲(chǔ)

l 用盡量少的存儲(chǔ)空間來存數(shù)一個(gè)字段的數(shù)據(jù);

例如：能使用int就不要使用varchar座咆、char,能用varchar(16)就不要varchar(256);

l IP地址最好使用int類型;

l 固定長度的類型最好使用char,例如：郵編;

l 能使用tinyint就不要使用smallint,int;

l 盡可能不用TEXT痢艺、BLOB類型。

l 存儲(chǔ)年使用YEAR類型介陶，存儲(chǔ)日期使用DATE類型堤舒，存儲(chǔ)時(shí)間（精確到秒）建議使用TIMESTAMP類型，因?yàn)門IMESTAMP使用4字節(jié)哺呜，DATETIME使用8個(gè)字節(jié)舌缤。

總之：Mysql的字段選擇原則，數(shù)據(jù)越小，性能越高国撵，請(qǐng)選擇合適字段類型,mysql數(shù)據(jù)類型及范圍介紹見附錄4.2

2.1.4 其它數(shù)據(jù)庫對(duì)象定義規(guī)則

除表外陵吸，其他對(duì)象命名也建議使用不同的前綴來區(qū)別。建議如下：

視圖 v_

序列 seq_

簇 c_

觸發(fā)器 trg_

存儲(chǔ)過程 sp_/p_

函數(shù) f_/fn_

主鍵 pk_

外鍵 fk_

唯一索引 uk_

普通索引 idx_

說明：

1）為了避免不必要的沖突和麻煩介牙，數(shù)據(jù)庫對(duì)象中不使用數(shù)據(jù)庫關(guān)鍵字和保留字壮虫，具體可閱附錄4.1

2）用戶自定義數(shù)據(jù)庫對(duì)象：表，視圖环础，主外鍵囚似，索引，觸發(fā)器喳整，函數(shù)谆构，存儲(chǔ)過程等風(fēng)格要保持一致。

2.1.5 建表格式規(guī)范

l 建表格式示范如下圖：

建表格式示范.png

l 修改及查看表相關(guān)注釋語句如下：

1）修改表的注釋

alter table tablename comment '修改后的表的注釋';

如：alter table TB_EXAMPLE comment '示例表1';

修改表的注釋.png

2）修改字段的注釋

alter table tablename modify column field_name int comment '修改后的字段注釋';

注意：字段名和字段類型照寫就行

如：alter table TB_EXAMPLE modify APP_NAME varchar(255) not null default '' comment '應(yīng)用名1';

字段名和字段類型修改.png

3） 查看表注釋的方法

show create table tablename;

如：show create table TB_EXAMPLE;

查看表注釋.png

4） 查看字段注釋的方法

show full columns from tablename;

如：show full columns from TB_EXAMPLE;

查看字段注釋.png

3 MYSQL SQL開發(fā)規(guī)范

3.1 SQL書寫規(guī)范

1框都、sql編寫時(shí)，大小寫一致

2呵晨、關(guān)鍵字單占一行魏保，如select、from摸屠、where谓罗、and、group by季二、order by等

3檩咱、注意行縮進(jìn)和對(duì)齊，建議語句中的關(guān)鍵字右對(duì)齊

4胯舷、使用空格刻蚯，SQL語句內(nèi)的算術(shù)運(yùn)算符、邏輯運(yùn)算符（AND桑嘶、OR炊汹、NOT）、 比較運(yùn)算符（=逃顶、<=讨便、>=、>以政、<霸褒、<>、BETWEEN AND）盈蛮、IN废菱、LIKE等運(yùn)算符前后都應(yīng)加一空格。

5、對(duì)較為復(fù)雜的sql語句加上注釋昙啄，說明算法穆役、功能。

注釋風(fēng)格：注釋單獨(dú)成行梳凛、放在語句前面耿币。

單行注釋：--

多行注釋：/* */

6、select后面的每一列（列數(shù)目大于1）單獨(dú)占一行韧拒，where后面的每個(gè)條件（條件數(shù)大于1）單獨(dú)占一行淹接。

7、update set子句內(nèi)容每一項(xiàng)單獨(dú)占一行叛溢，無縮進(jìn)塑悼。

8****、insert子句內(nèi)容每個(gè)表字段單獨(dú)占一行楷掉，無縮進(jìn)厢蒜；values每一項(xiàng)單獨(dú)占一行，無縮進(jìn) 烹植。

3.2 SQL開發(fā)技巧規(guī)范

1. Sql 語句盡可能得簡單

2. select * 盡量少用斑鸦， 僅select出需要的字段，只要一行數(shù)據(jù)時(shí)盡量使用limit 1

3. 盡量避免在where子句中使用in,not in或者h(yuǎn)aving草雕，使用exists,not exists代替

4. 盡量避免兩端模糊匹配 like %***%

5. 盡量用union all代替union

6. 拒絕大sql語句巷屿，大事物，大批量墩虹，避免鎖表嘱巾， 拆分大的 DELETE 或 INSERT 語句

如果你有一個(gè)大的處理，一定把其拆分诫钓，使用 LIMIT 條件是一個(gè)好的方法旬昭。參考下面是一個(gè)示例：

代碼如下:

while (1) {

//每次只做1000條

mysql_query("DELETE FROM logs WHERE log_date <= '2009-11-01' LIMIT 1000");

if (mysql_affected_rows() == 0) {

// 沒得可刪了，退出尖坤！

break;

}

// 每次都要休息一會(huì)兒

usleep(50000);

}

7. 數(shù)據(jù)表最好起別名稳懒，便于sql優(yōu)化器快速分析。

8. 除非業(yè)務(wù)需要否則不允許在sql語句中使用order by 子句慢味，禁止使用order by rand()

9. 多表連接查詢

1. 關(guān)聯(lián)表個(gè)數(shù)限制的基本原則

在報(bào)表數(shù)據(jù)庫或批處理數(shù)據(jù)庫中經(jīng)常會(huì)有需要關(guān)聯(lián)多張表做查詢的操作场梆，而這些表有的可能會(huì)是大表。過多的表做關(guān)聯(lián)可能給性能帶來嚴(yán)重的影響纯路，請(qǐng)慎用關(guān)聯(lián)查詢或油。

如果不能滿足這個(gè)限定條件，可以考慮如下的兩種處理方式:

第一驰唬，增加冗余字段顶岸，對(duì)于經(jīng)常被關(guān)聯(lián)使用的個(gè)別字段腔彰，可以考慮在一邊增加冗余字段的方式來減少關(guān)聯(lián)，這是一種反范式化的處理方式辖佣，但經(jīng)常被用于報(bào)表查詢類型的系統(tǒng)中霹抛。

第二，采用中間結(jié)果表方式卷谈。這種方式就是將原來一個(gè)SQL完成的操作拆開成多個(gè)SQL進(jìn)行杯拐，將某兩張或三張表的關(guān)聯(lián)結(jié)果先取出，然后再拿結(jié)果集與剩下的表繼續(xù)做關(guān)聯(lián)世蔗，得到最終完整的結(jié)果端逼。

2）連接語法

表連接分以下幾種方式

?等價(jià)連接(兩邊的表嚴(yán)格相等才返回)

A join B on 條件

?左外連接(左邊的表全返回，右邊表關(guān)聯(lián)不上就用null返回)

A left join B on條件

?右外連接(右邊的表全返回污淋，左邊表關(guān)聯(lián)不上就用null返回)

A right join B on　條件

?全外連接(左右兩表都不加限制顶滩，全部返回)

Afull join B and 條件

3）連接查詢的表字段前必須用表的別名標(biāo)識(shí)。

如　select a.col1,b.col2….

4）避免笛卡爾出現(xiàn)

產(chǎn)生笛卡爾連接的原因就是在多張表進(jìn)行關(guān)聯(lián)的操作中缺少了表間的連接條件寸爆。由于笛卡爾積產(chǎn)生的結(jié)果集將是多表記錄的乘積關(guān)系礁鲁，因此當(dāng)哪怕只有一張表的記錄數(shù)比較大時(shí)，其結(jié)果集都將被數(shù)倍以上地放大而昨，這勢必給數(shù)據(jù)庫性能帶來嚴(yán)重影響救氯。所以在寫關(guān)聯(lián)語句時(shí)要嚴(yán)格檢查連接條件是否有遺漏。

5）join連接的字段歌憨，字段類型必須相同，防止隱式轉(zhuǎn)換導(dǎo)致的索引失效墩衙。

10. 避免嵌套查詢

如：select a.name,(select b.address from table2 b where a.id=b.id) from a table1

用join代替：

Select a.name ,b.address

From table1 a

left join table2 b on a.id = b.id

11. 能用連接查詢實(shí)現(xiàn)的子查詢务嫡，應(yīng)避免使用子查詢

如：Select b.address from table1 b where a.id in(select id from table2 a where sex=1)

應(yīng)改為：select b.address from table1 b , table2 a where b.id=a.id and a.sex=1

或者：

Select b.address

From table1 b

Inner join table2 a on b.id = a.id

Where a.sex=1

12. Insert語句也要寫出字段名稱，避免因表結(jié)構(gòu)發(fā)生改變時(shí)發(fā)生編譯錯(cuò)誤

如：insert into table (col1,col2,col3…) values(?,?,?...)

13. 更新語句

查詢漆改、更新語句中嚴(yán)格選擇所操作的字段心铃。

盡量減少表被訪問的次數(shù)，看下面這個(gè)SQL:

UPDATE tb_target a

SET a.col1=

(SELECT b.col1FROM tb_source b WHERE b.id = a.id),

a.col2 =

(SELECT b.col2 FROM tb_source b WHERE b.id = a.id),

a.col3 =

(SELECT b.col3 FROM tb_source b WHERE bid = a.id),

a.col4 =

(SELECT b.col4 FROM tb_source b WHERE b.id = a.id)

WHERE a.id IN (SELECT b.id FROM tb_source b)

該語句作用其實(shí)就是用更新一個(gè)表的多個(gè)列挫剑，但這樣寫會(huì)多次掃描源表去扣。

如果改寫成如下的方式，則只需要掃描一次了:

UPDATE tb_target a

SET (a.col1,a.col2,a.col3,a.col4) =

(SELECT b.col1,b.col2,b.col3,b.col4

FROM tb_ source b

WHERE b.id = a.id)

WHERE EXISTS (SELECT 1 FROM tb_source b WHERE b.id = a.id)

14. 減少控制語句的檢查次數(shù)

如在if…else語句中樊破，盡可能最常用的愉棱，數(shù)量多的符合條件前置以被先檢查到，減少條件篩選的次數(shù)哲戚。

比如在人口清洗中奔滑，需要按民族分批處理時(shí)，最大數(shù)量符合條件的就是漢族顺少，第一個(gè)處理

4 附錄

4.1 常用保留字列表

A

ACTION ADD ALL ALTER ANALYZE AND AS ASC ASENSITIVE

B

BEFORE BETWEEN BIGINT BINARY BIT BLOB BOTH BY

C

CALL CASCADE CASE CHANGE CHARCHARACTER COLLATE COLUMN CONDITION CONNECTION CONSTRAINT CONTINUE CONVERT CREATE CROSS CURRENT_DATE CURRENT_TIME CURRENT_TIMESTAMP CURRENT_USER CURSOR CHECK

D

DATABASE DATABASES DATE DAY_HOUR DAY_MICROSECOND DAY_MINUTE DAY_SECOND DEC DECIMAL DECLARE DEFAULT DELAYED DELETE DESC DESCRIBE DETERMINISTIC DISTINCT DISTINCTROW DIV DOUBLE DROP DUAL

E

EACH ELSE ELSEIF ENCLOSED ENUM ESCAPED EXISTS EXIT EXPLAIN

F

FALSE FETCH FLOAT FOR FORCE FOREIGN FORM FULLTEXT

G

GOTO GRANTGROUP

H

HAVING HIGH_PRIORITY HOUR_MICROSECOND HOUR_MINUTE HOUR_SECOND

I

IF IGNORE

IN

INDEX INFILE INNER INOUT INSENSITIVE INSERT INT INTEGER INTERVAL INTO IS ITERATE

J

JOIN

K

KEY KYES KILL

L

LEADING LEAVE LEFT LIKE LIMIT LINES LOAD LOCALTIME LOCALTIMESTAMP LOCK

LONG LONGBLOB LONGTEXT LOOP LOW_PRIORITY

M

MATCH MEDIUMBLOB MEDIUMINT MEDIUMTEXT MIDDLEINT MINUTE_MICROSECOND MINUTE_SECOND MOD MODIFIES

N

NATURAL NO NO_WRITE_TO_BINLOG NOT NULL NUMERIC

O

ON OPTIMIZE OPTION OPTIONALLY OR ORDER OUT OUTER OUTFILE

P

PRECISION PRIMARY PROCEDURE PURGE

R

READ READS REFERENCES REGEXP RELEASE RENAME REPEAT REPLACE REQUIRE RESTRICT

RETURN REVOKE RIGHT RLIKE REAL

S

SCHEMA SCHEMAS SECOND_MICROSECOND SELECT SENSITIVESEPARATOR SET SHOW SMALLINT SONAME SPATIAL SPECIFIC SQL SQL_BIG_RESULT SQL_CALC_FOUND_ROWS

SQL_SMALL_RESULT SQLEXCEPTION SQLSTATE SQLWARNING SSL STARTING STRAIGHT_JOIN

T

TABLE TERMINATED TEXT THEN TIME TIMESTAMP TINYBLOB TINYINT TINYTEXT TO

TRAILING TRIGGER TRUE

U

UNDO UNION UNIQUE UNLOCK UNSIGNED UPDATE USAGE USE USING UTC_DATE UTC_TIME UTC_TIMESTAMP

V

VALUES VARBINARY VARCHAR VARCHARACTER VARYING

W

WHEN WHERE WHILE WITH WRITE

X

XOR

Y

YEAR_MONTH

Z

ZEROFILL

4.2 Mysql數(shù)據(jù)類型介紹

4.2.1 數(shù)字類型列表

數(shù)字類型列表.png

4.2.2 日期類型列表

日期類型列表.png

4.2.3 字符類型列表

字符類型列表.png