講義開發(fā)常用mysql v2

1、版本修改歷史類別：A增加 M修改 D刪除版本號(hào)日期類別描述編寫審核V0.12013-07-20A新建文檔V0.12013-07-23M修改文檔V1.02013-07-26M修改文檔V1.02013-07-28M修改文檔V1.02013-08-05M修改文檔目錄V1.01開發(fā)實(shí)用技術(shù)4自增字段定義4字符串函數(shù)4CHAR(N)或 VARCHAR(N)中的 N 解釋4日期操作函數(shù)5類型轉(zhuǎn)換函數(shù)5開發(fā)規(guī)范6字段定義規(guī)范6綁定變量和替代變量使用規(guī)范7數(shù)據(jù)類型轉(zhuǎn)換規(guī)范8SELECT * 的使用規(guī)范8字段上添加函數(shù)使用規(guī)范9表連接規(guī)范9分頁查詢規(guī)范101.2.變更管理10開發(fā)服務(wù)器說明10使用建議及命名規(guī)范

2、11使用建議115.5.1數(shù)據(jù)庫涉及字符規(guī)范11數(shù)據(jù)庫表命名規(guī)范12字段命名規(guī)范12外鍵命名規(guī)范12字段類型規(guī)范13索引使用原則13其他設(shè)計(jì)技巧131.開發(fā)實(shí)用技術(shù)自增字段定義字符串函數(shù)1.3 CHAR(N)或 VARCHAR(N)中的 N 解釋1.4 日期操作函數(shù)1.5 類型轉(zhuǎn)換函數(shù)1.1 自增字段定義類型為 或者 BIG自增字段類型必須是整型， 類型。并且自增字段必須是主鍵或1.2 字符串函數(shù)1.3 CHAR(N)或 VARCHAR(N)中的 N 解釋 所有數(shù)據(jù)庫的字符集都為 UTF8，字符集校對(duì)規(guī)則為 UTF8_general_ci。對(duì)于中文漢字，實(shí)際的時(shí)候占三個(gè)字節(jié)，而數(shù)據(jù)或字母，則只

3、占一個(gè)字節(jié)。例如： 中此兩類字符串定義時(shí)候填寫的長度N，不是字節(jié)數(shù)的意思 ，而是字符數(shù)的意思。中字符串連接方法，使用CONCAT() 或 CONCAT_ WS()函數(shù)，語法如下：CONCAT(string1,string2,.)CONCAT_ WS(separator,string1,string2,.)字符串長度統(tǒng)計(jì)：LENGTH(string)#返回 string 所占的字節(jié)數(shù)CHAR_LENGTH(string)#返回 string 中的字符個(gè)數(shù)統(tǒng)計(jì)字符個(gè)數(shù)，就不區(qū)分是漢字還是字母或數(shù)字，也跟字符集沒有關(guān)系，若統(tǒng)計(jì)的是字節(jié)數(shù)，則由字符是漢字、字母或數(shù)字類型，以及字符集共同決定。請(qǐng)各位牢記

4、所有的數(shù)據(jù)庫都將會(huì)采用 UTF8 編碼，所以一個(gè)漢字占 3 個(gè)字節(jié)，一個(gè)字母或數(shù)字占一個(gè)字節(jié)。主鍵的一部分。1.4日期操作函數(shù)1.5類型轉(zhuǎn)換函數(shù)字符串轉(zhuǎn)換成日期方式，DATE_FORMAT()或 STR_TO_DATE()，兩個(gè)函數(shù)的格式如下： DATE_FORMAT(expr1,format) STR_TO_DATE(expr1, format)常用的日期格式 YYYY-MM-DD HH:MM:SS 對(duì)應(yīng)的 format 為%Y-%m-%d %H:%i:%S通用的類型轉(zhuǎn)換函數(shù)： CAST(expr AS type) CONVERT(expr,type)CONVERT(expr USING t

5、ranscoding_name)獲取當(dāng)前時(shí)間：NOW()，CURDATE()、CURTIME()其中NOW() 函數(shù)精確到秒，格式：YYYY-MM-DD HH:MM:SSCURDATE() 函數(shù)精確到天，格式：YYYY-MM-DDCURTIME() 函數(shù)精確到秒，格式：HH:MM:SS日期數(shù)值的加減函數(shù)：DATE_ADD(date,ERVAL expr type) DATE_ SUB(date,ERVAL expr type)常用的幾種 type 類型：YEAR、MONTH、DAY、HOUR、MINUTE，其中 expr 可以為正數(shù)或負(fù)數(shù)，在開過程中，一般使用DATE_ADD()函數(shù)，若要作日

6、期減去一個(gè)數(shù)字的方式，就使用負(fù)數(shù)。DATEDIFF(expr1,expr2)，是返回 開始日期expr1 與 結(jié)束日期expr2 之間，相差的天數(shù) ，返回值為正數(shù)或負(fù)數(shù)。返回日期某部分信息的函數(shù)：YEAR(expr1) 返回日期expr1 部分的年份；MONTH(expr1) 返回日期expr1 部分的月份；DAY(expr1)返回 expr1 部分的天數(shù)；KDAY(expr1) 返回expr1 對(duì)應(yīng)的數(shù)字CREATE TABEL gl_user(username VARCHAR(40);則 username 最多能40 個(gè)字符 。開發(fā)規(guī)范2.字段定義規(guī)范綁定變量和替代變量使用規(guī)范數(shù)據(jù)類型轉(zhuǎn)換

7、規(guī)范SELECT * 的使用規(guī)范字段上添加函數(shù)使用規(guī)范表連接規(guī)范分頁查詢規(guī)范2.1 字段定義規(guī)范中用到的相關(guān)列數(shù)據(jù)類型需求與范圍描述信息如下表列類型表達(dá)的范圍需求TINY(M) UNSIGNED ZEROFILL-128 到 127 或 0 到 2551 個(gè)字節(jié)SMALL(M) UNSIGNED ZEROFILL-32768 到 32767 或 0 到 655352 個(gè)字節(jié)(M) UNSIGNED ZEROFILL-2147483648 到 2147483647 或 0 到4 個(gè)字節(jié)-到8 到157 或 08 個(gè)字節(jié)BIG(M) UNSIGNED ZEROFILLDECIMAL(M,D) UN

8、SIGNEDZEROFILL整數(shù)最大位數(shù)（ M ）為 65 ，小數(shù)位數(shù)最大（ D ）為 30變長DATEYYYY-MM-DD3 個(gè)字節(jié)DATETIMEYYYY-MM-DD HH:MM:SS(1001 年到 9999 年的范圍 )8 個(gè)字節(jié)TIMESTYYYY-MM-DD HH:MM:SS （ 1970 年到 2037 年的范圍）4 個(gè)字節(jié)0M=255（ 建議 CHAR(1) 外，超過此長度的用VARCHAR）M 個(gè)字符（所占空間跟字符集等有關(guān)系）CHAR(M)M 個(gè)字符（ N 大小由字符集，以及VARCHAR(M)0M65532/N是否為中文還是字母數(shù)字等 有關(guān)系）2.2 綁定變量和替代變量使

9、用規(guī)范基本原則：1. 所有 Query 的 Where 條件中的變量，都需要使用綁定變量來實(shí)現(xiàn)，此要求并不完全是基于性能的考慮，是基于安全方面的考慮，如若有任何不使用綁定變量的需求，都必須通過安全部門的審核并征得同意。詳細(xì)說明：2. 在 iBatis 的 SqlMap 文件中綁定變量使用 #var_name#表示，替代變量使用$var_name$；所有需要?jiǎng)討B(tài) Order By 條件的 Query，在使用替代變量過程中，需要將可能傳入的內(nèi)容以枚舉類寫死在代碼中接受任何外部傳入內(nèi)容；對(duì)于不變的常量條件，請(qǐng)使用常量而不是變量；3. IN 子句，使用Iterate + 數(shù)組類型變量的方式實(shí)現(xiàn)綁定變量

10、而不是通過代碼拼接 Query語句，例如：#userIds#TEXT64K 個(gè)字符所占空間跟字符集等有關(guān)系詳細(xì)說明：1. 所有動(dòng)態(tài)長度字符串全部使用 VARCHAR 類型，類似于狀態(tài)，有限類別的字段， 也使用可以比較明顯表示出實(shí)際意義的字符串，而不應(yīng)該使用之類的數(shù)字來代替；2. 固定長度的字符串使用 CHAR 類型，所有單個(gè)字符的全部使用 CHAR 類型，而不應(yīng)該使用 VARCHAR 類型；3. 僅僅當(dāng)字符數(shù)量可能超過 20000 個(gè)的時(shí)候，可以使用 TEXT 類型來存放字符類數(shù)據(jù)。所有使用 TEXT 類型的字段必須和原表進(jìn)行分拆，與原表主鍵單獨(dú)組成另外一個(gè)表進(jìn)行存放；4. 需要精確到時(shí)間（年

11、月日時(shí)分秒）的字段可以使用 DATETIME 或 TIMEST，但請(qǐng)注意各自能表達(dá)的范圍，以及是否需要用到 TIMEST的特性；5. 所有只需要精確到天的字段全部使用 DATE 類型，而不應(yīng)該使用 TIMEST或者DATETIME 類型；6. 自增序列類型的字段只能使用或者 BIG，且明確標(biāo)識(shí)出為無符號(hào)型(UNSIGNED)，除非確實(shí)會(huì)出現(xiàn)負(fù)數(shù)，僅當(dāng)該字段數(shù)字取值會(huì)超過 42 億，才使用 BIG類型；2.3數(shù)據(jù)類型轉(zhuǎn)換規(guī)范2.4 SELECT * 的使用規(guī)范基本原則：在不必要查詢中使用*列出所有字段，且需存在 GROUP BY 或 ORDER BY 的時(shí)候，使用 SELECT * 一次取出所有

12、的字段。對(duì)于表連接的 JOIN 語句，使用 SELECT * 來進(jìn)行查詢，除非明確獲得 DBA 允許。含有 text 字段的表，當(dāng)不需要取出 TEXT 字段的時(shí)候，也使用 SELECT * 進(jìn)行查詢.詳細(xì)說明：進(jìn)行 GROUP BY 或 ORDER BY 的時(shí)候不允許使用 SELECT * 是為了確保能夠使用的優(yōu)化排序算法.JOIN 語句不允許使用 SELECT * 是為了防止僅僅只需要索引即可完成的查詢需要回表取數(shù).存有 TEXT 字段表,在不需要取出 TEXT 字段的時(shí)候，不允許使用 SELECT * ,因?yàn)?TEXT 字段是存放在和普通不一樣的物理位置，會(huì)造成大量的 io 操作.避免因增

13、刪字段而沒有修改相關(guān)SQL 及相關(guān)程序代碼導(dǎo)致程序 BUG，而禁用 SELECT *.基本原則：在所有 Query 的 Where 條件中必須使用和過濾字段完全一致的數(shù)據(jù)類型，杜絕任何隱式類型轉(zhuǎn)換，避免造成因?yàn)閿?shù)據(jù)類型不匹配而導(dǎo)致 Query 執(zhí)行計(jì)劃的出錯(cuò)，造 能問題.詳細(xì)說明：所有 Where 條件的字段上不允許使用函數(shù)做類型轉(zhuǎn)換， 需要轉(zhuǎn)換類型，只能轉(zhuǎn)換過濾值,而不是轉(zhuǎn)換字段.最為常見的隱式類型轉(zhuǎn)換常見于時(shí)間類型與字符串類型之間，建議所有時(shí)間類型字段在 iBatis 中均以時(shí)間類型傳入，或者以字符串傳入然后通過時(shí)間函數(shù)轉(zhuǎn)換字符串為合法的時(shí)間格式 ，如下：SELECT * FROM mem

14、ber WHERE gmt_create=DATE_FORMATE(2009010101:02:03,%Y-%m-%d %H:%i:%s);在表連接 Query 中，如果連接條件兩端的數(shù)據(jù)類型不一致，必須保證將驅(qū)動(dòng)表的連接條件數(shù)據(jù)類型轉(zhuǎn)換為與被驅(qū)動(dòng)表一致的數(shù)據(jù)類型.iBatis 會(huì)生成 t.user_id in (1,2,3,4,5 .)的語句2.5字段上添加函數(shù)使用規(guī)范2.6表連接規(guī)范過濾條件寫到WHERE 字句中。出于代碼的可讀性原因，所有外連接 SQL 語句中，請(qǐng)一律使用LEFT JOIN，禁用 RIGHT JOIN。另外，請(qǐng)注意 LEFT JOIN 字句中，右邊位置表的條件書寫位置不同

15、的影響：SELECT A.rolename,A.gmt_create,B.nickname FROM gl_role A LEFT JOIN gl_roledetail B ON A.ID=B.roleid AND B.roleID=2;+-+-+| rolename| gmt_create| nickname |+-+-+| 0000-00-00 00:00:00 | test2| 0000-00-00 00:00:00 | NULL| 0000-00-00 00:00:00 | NULL| 2009-08-20 18:20:18 | NULL|+-+-+SELECT A.rolename,

16、A.gmt_create,B.nickname FROM gl_role A LEFT JOIN gl_roledetail B ON A.ID=B.roleid WHERE B.roleID=2;+-+-+| rolename | gmt_create| nickname |基本原則：所有非外連接 SQL（即 INNER JOIN），請(qǐng)把關(guān)聯(lián)表寫到 FROM 字句中，關(guān)聯(lián)條件與基本原則：在 WHERE 條件中出現(xiàn)的過濾字段上，使用任何函數(shù)進(jìn)行類型或格式的轉(zhuǎn)換；正確的做法是把傳入比較的值轉(zhuǎn)換為列類型所需要的。錯(cuò)誤的寫法：SELECT username FROM gl_user WHERE DA

17、TE_FORMAT(gmt_create,%Y%m%d%H%i%s)=20090501022300;正確的寫法：SELECT username FROM gl_user WHERE gmt_create=DATE_FORMAT(20090501022300, %Y-%m-%d %H:%i:s);5) WHERE 條件查詢，如果有條件中有日期類型的字段，使用大于”和小于”符號(hào)，使用 betn 和 and。2.7 分頁查詢規(guī)范基本原則：分頁查詢語句全部都需要帶有排序條件，除非商業(yè)方明確要求不要使用任何排序來隨機(jī)展示數(shù)據(jù)。詳細(xì)說明：1) 常規(guī)分頁語句寫法(start：起始數(shù)，page_offset：

18、每頁數(shù))：SELECT ID,username FROM gl_user WHERE username like ORDERBY M.gmt_creaIMIT start, page_offset;2) 多表 Join 的分頁語句，如果過濾條件在單個(gè)表上，需要先分頁，再 Join：低性能寫法：SELECT M.username,P.rolename FROM gl_user M INNER JOIN gl_role P ONM.ID=P.userid WHERE username like LIMIT start, page_offset;高性能寫法： ORDER BYM.gmt_create

19、SELECT M.username,P.rolename FROM (SELECT ID,usernameFROM gl_userIMIT start,WHERE username like page_offset) M,gl_role這樣寫的前提是關(guān)聯(lián)的表之間page_offset 的值。 ORDER BY M.gmt_creaP WHERE M.ID=P.userid;一一對(duì)應(yīng)，否則可能會(huì)返回的數(shù)目少于或多于3.變更管理3.1 有非小表（不超過 10W 條）結(jié)構(gòu)變更的發(fā)布至少提前 1通知發(fā)布具體時(shí)間，超過 10w 條的結(jié)構(gòu)變更必須提前 2通知3.2 每天的 09:00 12:00 與 14

20、:00 16:00 之間一般不對(duì)產(chǎn)品數(shù)據(jù)庫做任何變更（備注：數(shù)據(jù)庫結(jié)構(gòu)變更）操作+-+-+| 0000-00-00 00:00:00 | test2|+-+-+4.開發(fā)服務(wù)器說明Server = MYDatabase = db_992f63_mydb Uid = 992f63_mydbPwd = mydb_123使用建議及命名規(guī)范5.5.1使用建議5.2數(shù)據(jù)庫涉及字符規(guī)范采用26 個(gè)英文字母(區(qū)分大小寫)和 0-9 這十個(gè)自然數(shù),加上下劃線_組成,共 63 個(gè)字符.不能出現(xiàn)其他字符(注釋除外).注意事項(xiàng):1) 進(jìn)行數(shù)據(jù)庫結(jié)構(gòu)設(shè)計(jì)的時(shí)候，考慮適當(dāng)?shù)娜哂?，盡量確保應(yīng)用讀寫數(shù)據(jù)的 SQL 簡潔.2)

21、 所有字符集為utf8，校對(duì)規(guī)則為 utf8_general_ci ，默認(rèn)是不區(qū)分英文字母大小寫，若有需求區(qū)分大小寫，請(qǐng)跟 DBA 特別，或者表定義語句指定 COLLATE utf8_bin.3) 盡量不需要使用子查詢，特別是 IN 的方式，可考慮轉(zhuǎn)化為 EXISTSSELECT * FROM A WHRE A.ColName1 IN (SELECT DISTINCT ID FROM B WHERE .);建議改寫為：SELECT * FROM A WHRE EXISTS (SELECT 1 FROM B WHERE B.ID=A.ColName1.);要返回自增序列的 ID 值，可以考慮使用

22、函數(shù) LAST_INSERT_ID()，此函數(shù)只能返回同一個(gè) SES最近一次對(duì)有 AUTO_INCREMENT 屬性表 INSERT 的 ID 值.所有的時(shí)間字，請(qǐng)以數(shù)據(jù)庫的時(shí)鐘為準(zhǔn)，除用戶輸入的時(shí)間值外.對(duì)于項(xiàng)目的數(shù)據(jù)量、PV 等合理評(píng)估，DBA TEAM 相關(guān)，會(huì)給大家合理成數(shù)據(jù)存取架構(gòu)，增強(qiáng)系統(tǒng)的擴(kuò)展性與用戶體驗(yàn)，以及高可用性等.開發(fā)服務(wù)器相關(guān)信息：5.3數(shù)據(jù)庫表命名規(guī)范5.4字段命名規(guī)范5.5外鍵命名規(guī)范字段名稱全部使用大寫；.不需要加表名的前綴；如果需要有多個(gè)單詞組合的，用下劃線”_” 連接；如 用戶名：USER_NAME用戶 ID：USER_ID表與表之間的相關(guān)聯(lián)字段要用名稱,如

23、info_user 表里面的 userId 和 group 表里面的 userId 相對(duì)應(yīng)；業(yè)務(wù)流水號(hào)采用：表名_seq；數(shù)據(jù)表名全部使用小寫英文以及下劃線組成比如:info_usersystem_destination信息類采用：info_文件類采用：file_關(guān)聯(lián)類采用： er_備份數(shù)據(jù)表名使用正式表名加上備份時(shí)間組成,如: info_user_20130720 system_destination_20130720以上命名都不得超過 30 個(gè)字符的系統(tǒng)限制.變量名的長度限制為 29(不包括標(biāo)識(shí)字符 ).數(shù)據(jù)對(duì)象、 變量名都采用英文字符,使用中文命名.絕對(duì)不要在對(duì)象名的字符之間留空格.保留詞,要保證你的字段名沒有和保留詞、數(shù)據(jù)庫系統(tǒng)或者常用方法5) 保持字段名和類型的一致性,在命名字段并為其指定數(shù)據(jù)類型的時(shí)候一定要保證一致性.假如數(shù)據(jù)類型在一個(gè)表里是整數(shù),那在另一個(gè)表里可就別變成字符型了.5.6 字段類型規(guī)范5.7 索引使用原則邏輯主鍵使用唯一的成組索引,對(duì)系統(tǒng)鍵(作為過程)采用唯一的非成組索引,對(duì)任何外鍵列采用非成組索引.考慮數(shù)據(jù)庫的