一：數據庫的設計

數據庫命名：數據庫名的命名一般和項目的名稱保持一致，不要隨意的起名字。

數據庫編碼: 採用utf8mb4而不使用utf8

MySQL 的“utf8”實際上不是真正的UTF-8，真正的UTF-8是每個字符最多四個字節，而MySQL的“utf8”只支持每個字符最多三個字節。MySQL一直沒有修復這個 bug，他們在 2010 年發佈了一個叫作“utf8mb4”的字符集，繞過了這個問題。MySQL的“utf8mb4”才是真正的“UTF-8”。所有在使用“utf8”的 MySQL和MariaDB用戶都應該改用“utf8mb4”，永遠都不要再使用“utf8”。第三方像微信和QQ的暱稱一般包含一些表情符號等，這些符號是屬於UTF-8的，但是如果數據庫中使用utf-8將不能保存，只能將數據庫編碼改為utf8mb4才能保存第三方的暱稱這種數據

utf8轉utf8mb4方法

二：表的設計

數據庫表的好壞是數據庫設計的基礎，而且一旦數據庫表設計完畢並投入使用，將來再進行修改就比較麻煩，因此在進行數據庫設計的時候一定要儘可能的考慮周到。

1. 表名

• 表的命名一般遵守 “業務名稱 _ 表名“或者是“項目名_ 表名“的格式，對於業務名稱一般都是簡寫，不全拼，全拼表名會太長，如sysuser(系統模塊對應的用戶表)，對於一些公用的可以使用tbl(table)作為模塊名，如字典表 tbldictionary
• 表名不使用複數形式，表名應該僅僅表示表裡面的實體內容，不應該表示實體數量為什麼要使用前綴？
• 如果多個項目都使用同一個數據庫的話，可以防止命名衝突，例如用戶表，如果沒有前綴，只能有一個叫user的，其它項目也想使用這個名字就沒法用了，為了解決這種問題，可以在表名上增加一個前綴，前綴為項目名稱，如xxxuser, yyyuser; 在公司中可以經常看到有時候數據庫中的所有表都用項目的簡稱做前綴，很可能所有表只有這一種前綴，也沒有分多個前綴。 不同項目一般都會創建自己的數據庫，但是不能保證萬一會使用同一個數據庫的情況，如兩個項目關聯很大可能會使用同一個數據庫，這樣使用前綴就能解決命名衝突的問題。
• 在比較複雜的系統中，通過表名前綴可以大概瞭解到表所在的模塊和分類，這樣做日常開發和運維的時候看起來比較方便，新人瞭解系統數據結構的時候也有章可循，這種方式就是使用多種前綴的方式

2. 字段名

• MySQL 在 Windows 下不區分大小寫，但在 Linux 下默認是區分大小寫。因此，數據庫名、 表名、字段名，都不允許出現任何大寫字母，避免節外生枝。
• 一般所有表都要有id, id必為主鍵，類型為bigint unsigned,單表時自增、步長為1; 有些特殊場景下(如在高併發的情況下該字段的自增可能對效率有比價大的影響)id是通過程序計算出來的一個唯一值而不是通過數據庫自增長來實現的。
• 一般情況下主鍵id和業務沒關係的，例如訂單號不是主鍵id，一般是訂單表中的其他字段，一般訂單號order_code為字符類型
• 一般情況下每張表都有著四個字段createid,createtime,updateid,updatetime, 其中createid表示創建者id，createtime表示創建時間，updateid表示更新者id，updatetime表示更是時間，這四個字段的作用是為了能夠追蹤數據的來源和修改
• 最好不要使用備用字段(個人觀點), 禁用保留字，如 desc、range、match、delayed 等
• 表達是與否概念的字段，必須使用 isxxx 的方式命名，數據類型是 unsigned tinyint (1 表示是，0 表示否), 任何字段如果為非負數，必須是unsigned。表達邏輯刪除的字段名 isdeleted，1 表示刪除，0 表示未刪除
• 如果某個值能通過其他字段能計算出來就不需要用個字段來存儲，減少存儲的數據
• 為了提高查詢效率，可以適當的數據冗餘，注意是適當
• 強烈建議不使用外鍵, 數據的完整性靠程序來保證
• 單條記錄大小禁止超過8k， 一方面字段不要太多，有的都能上百，甚至幾百個，另一方面字段的內容不易過大像文章內容等這種超長內容的需要單獨存到另一張表

3. 字段的數據類型

不同的數據類型搜索的方式不同，所以說要選擇合適的數據類型。

用盡量少的存儲空間來存數一個字段的數據, 縮小存儲空間換取查詢時間,能用int的就不用char或者varchar,能用tinyint的就不用int,使用UNSIGNED存儲非負數值，其中無符號值可以避免誤存負數，且擴大了表示範圍。合適的字符存儲長度，不但節約數據庫表的存儲空間、節約索引存儲，更重要的是提升檢索速度。

儘量使用數字型字段，提高數據比對效率。

①：字符類型

• char是固定長度的字符類型，它的處理速度比varchar快，缺點是浪費存儲空間，當實際存儲的值小於指定的長度時會以空格來填充，對於長度變化不大並且對查詢速度有較高的要求可以選擇char。適合存儲用戶密碼的MD5哈希值，手機號，性別，因為它的長度總是一樣的。對於經常改變的值，char也好於varchar,因為固定長度的行不容易產生碎片，對於很短的列，char的效率也高於varchar。char(1)字符串對於單字節字符集只會佔用一個字節，但是varchar(1)則會佔用2個字節，因為1個字節用來存儲長度信息 。如果存儲的字符串長度幾乎相等，使用char定長字符串類型。
• varchar是可變長字符串，不預先分配存儲空間，長度不要超過 5000，如果存儲長度大於此值，定義字段類型為 text，獨立出來一張表，用主鍵來對應，避免影響其它字段索引效率。varchar的長度是字符長度，而不是字節長度。varchar還會使用額外的存儲空間來記錄可變字符串的長度列的最大長度小於255則只需要額外佔用一個字節來記錄字符串的長度列的最大長度大於255則需要額外佔用兩個字節來記錄字符串的長度
• 不同存儲引擎對char和varchar的使用原則不同，myisam:建議使用國定長度的數據列代替可變長度。innodb：建議使用varchar，大部分表都是使用innodb，所以varchar的使用頻率更高

②：數值類型

選用合適的長度非常重要，能用tinyint就不用integer

• 金額類型的字段儘量使用long用分表示，儘量不要使用bigdecimal，嚴謹使用float和double因為計算時會丟失經度
• 如果需要使用小數嚴謹使用float，double，使用定點數decimal，decimal實際上是以字符串的形式存儲的，所以更加精確，java中與之對應的數據類型為BigDecimal
• 如果值為非負數，一定要使用unsigned，無符號不僅能防止負數非法數據的保存，而且還能增大存儲的範圍
• 不建議使用ENUM、SET類型，使用TINYINT來代替

decimal不會經度丟失，但是會佔用更多的存儲空間，佔用空間越大就需要越多的磁盤IO，磁盤IO是影響MySQL性能的一個最重要的因素，所以還需慎用，對於金額直接使用bigint存儲分

誤區： 創建表時我們經常看到長度這一列，例如 tinyint(2)，對於整型來說小括號中的2不是指的存儲長度，而是指的零填充，對於字符串指的是長度，zerofill零填充，當數據的長度小於指定的長度時，會使用0來填充缺失的長度。零填充在mysql客戶度中看不出來，使用命令行可以看出來。

如果tinyint(2)中的2代表長度，那麼102就插入不成功，事實上是插入成功的。

使用命令行可以看到1和2因不到兩位長度，差一位，需要用0來填充

③：日期類型

根據實際需要選擇能夠滿足應用的最小存儲日期類型。

• 如果應用只需要記錄年份，那麼僅用一個字節的year類型。
• 如果記錄年月日用date類型, date佔用4個字節，存儲範圍10000-01-01到9999-12-31
• 如果記錄時間時分秒使用它time類型
• 如果記錄年月日並且記錄的年份比較久遠選擇datetime，而不要使用timestamp,因為timestamp表示的日期範圍要比datetime短很多
• 如果記錄的日期需要讓不同時區的用戶使用，那麼最好使用timestamp, 因為日期類型值只有它能夠和實際時區相對應
• datetime默認存儲年月日時分秒不存儲毫秒fraction，如果需要存儲毫秒需要定義它的寬度datetime(6)

timestamp與datetime

• 兩者都可用來表示YYYY-MM-DD HH:MM:SS[.fraction]類型的日期。
• 都可以使用自動更新CURRENT_TIMESTAMP
• 對於TIMESTAMP，它把客戶端插入的時間從當前時區轉化為UTC（世界標準時間）進行存儲。查詢時，將其又轉化為客戶端當前時區進行返回。而對於DATETIME，不做任何改變，基本上是原樣輸入和輸出。
• timestamp佔用4個字節：timestamp所能存儲的時間範圍為：'1970-01-01 00:00:01.000000' 到 '2038-01-19 03:14:07.999999' datetime佔用8個字節 ：datetime所能存儲的時間範圍為：'1000-01-01 00:00:00.000000' 到 '9999-12-31 23:59:59.999999'

總結：TIMESTAMP和DATETIME除了存儲範圍和存儲方式不一樣，沒有太大區別。如果需要使用到時區就必須使用timestamp，如果不使用時區就使用datetime因為datetime存儲的時間範圍更大

注意：

• 禁止使用字符串存儲日期，一般來說日期類型比字符串類型佔用的空間小，日期時間類型在進行查找過濾是可以利用日期進行對比，這比字符串對比高效多了，日期時間類型有豐富的處理函數，可以方便的對日期類型進行日期的計算
• 也儘量不要使用int來存儲時間戳

PROCEDURE analyse(): 用於分析表的數據類型

<code>SELECT * FROM tbl_user PROCEDURE analyse();
/<code>

3. 是否為null

MySQL字段屬性應該儘量設置為NOT NULL，除非你有一個很特別的原因去使用 NULL 值，你應該總是讓你的字段保持 NOT NULL 。

• 在MySql中NULL其實是佔用空間的，“可空列需要更多的存儲空間”：需要一個額外字節作為判斷是否為NULL的標誌位“需要mysql內部進行特殊處理”， 而空值""是不佔用空間的。
• 含有空值的列很難進行查詢優化，而且對錶索引時不會存儲NULL值的，所以如果索引的字段可以為NULL，索引的效率會下降很多。因為它們使得索引、索引的統計信息以及比較運算更加複雜。你應該用0、一個特殊的值或者一個空串代替null。
• 聯表查詢的時候，例如SELECT user.username, info.introduction FROM tbluser user LEFT JOIN tbluserinfo info ON user.id = info.userid; 如果tbluserinfo.introduction設置的可以為null, 假如這條sql查詢出了對應的記錄，但是username有值，introduction沒有值，那麼就不是很清楚這個introduction是沒有關聯到對應的記錄，還是關聯上了而這個值為null，null意思表示不明確，有歧義

注意：NULL在數據庫裡是非常特殊的，任何數跟NULL進行運算都是NULL, 判斷值是否等於NULL，不能簡單用=，而要用IS NULL關鍵字。使用 ISNULL()來判斷是否為 NULL 值，NULL 與任何值的直接比較都為 NULL。

• 1) NULL<>NULL的返回結果是NULL，而不是false。
• 2) NULL=NULL的返回結果是NULL，而不是true。
• 3) NULL<>1的返回結果是NULL，而不是true。

4. 存儲引擎

常用的存儲引擎的選擇有MYISAM、InnoDB、MEMORY，不同的存儲引擎支持的功能不一樣，MySQL5.5之後默認的是InnoDB。絕大部分場景都是使用InnoDB引擎。

• MYISAM 不支持事務, 不支持外鍵，其優勢是訪問速度快，對事務完整性沒有要求或者以select、insert為主的應用程序可以選擇這個引擎，支持全文索引，表鎖，注意：MYISAM 在刪除數據時好像類似於邏輯刪除，需要定時物理刪除，清理碎片：optimize table 名稱；
• InnoDB 支持事務，不支持全文索引，標鎖，支持外鍵
• MEMORY：查詢速度極快，數據在內存中不持久化，數據庫重啟數據就消失，類似於緩存的作用memcache

表引擎取決於實際應用場景；日誌及報表類表建議用myisam，只讀的表；與交易，審核，金額相關的表建議用innodb引擎。

建議：不要混合使用存儲引擎，實際場景中會有MyISAM和InnoDB混合使用的情況，但是這樣有問題，比如一個事務同時操作了myisam引擎的表和innodb引擎的表，而myisam是不支持事務的，就會造成myisam表沒有回滾。現在開發中絕大部分都是使用InnoDB，也不經常見到myisam，至少我工作中沒見到過。

mysql5.0之後默認為InnoDB創建表的時候可以指定engine，也可以通過alter table語句來修改存儲引擎。

<code>create table tbl_user (

)engine=InnoDB default charset=utf-8;

alter table tbl_user engine = innodb;/<code>

閱讀更多 Java實用技術 的文章

關鍵字: 數據結構 設計 Windows