Mysql 建庫建表技巧分享

2024-07-24 12:44:20

字體：大中小

供稿：網(wǎng)友

一、兩表之間若有關(guān)聯(lián)，你是否還在用主鍵進(jìn)行關(guān)聯(lián)？
　　比如現(xiàn)在有2張表，一張新聞欄目表，一張新聞表，現(xiàn)在兩張表需要進(jìn)行關(guān)聯(lián)，我想大多數(shù)人的做法肯定是在新聞表里建一個(gè)新聞欄目id，然后把新聞欄目表里的主鍵ID（自增）寫到這個(gè)字段里，通過這樣進(jìn)行兩表關(guān)聯(lián)。
　　如果你是這樣做的，趕緊改掉這個(gè)習(xí)慣吧。也許你會問為什么，欄目id是主鍵啊，又是自增的，為什么這樣操作不行？原因其實(shí)很簡單，欄目我們會增加，也會刪除，刪除就會造成主鍵id之間會有斷號的情況，由于主鍵設(shè)置為自增，也就是說你之前刪掉的欄目，再進(jìn)行添加，id是不會去補(bǔ)上哪個(gè)空缺的，而是一直遞增。這樣就會造成一種情況，如果那天對數(shù)據(jù)庫進(jìn)行優(yōu)化，把主鍵進(jìn)行了重新排序（暫時(shí)沒有找到mysql優(yōu)化軟件會優(yōu)化主鍵，但是可以通過代碼刪除主鍵，然后從新建立自增主鍵來實(shí)現(xiàn)主鍵重新排序），那就徹底杯具了，欄目和文章完全對不上號了。所以我建議兩表之間關(guān)聯(lián)不用主鍵，而是單獨(dú)建一個(gè)編號的字段，我們這里可以用mysql的uuid()函數(shù)做為編號，相關(guān)文獻(xiàn)可以參考《UUID做主鍵好還是不好》，只所以一張表要2個(gè)主鍵，一個(gè)物理主鍵（自增id），一個(gè)邏輯主鍵（UUID），原因是：對于InnoDB這種聚集主鍵類型的引擎來說，數(shù)據(jù)會按照主鍵進(jìn)行排序，由于UUID的無序性，InnoDB會產(chǎn)生巨大的IO壓力，此時(shí)不適合使用UUID做物理主鍵，可以把它作為邏輯主鍵，物理主鍵依然使用自增ID。至于性能，我本地測了下基本上沒差異，網(wǎng)上也有人做了10W條數(shù)據(jù)的測試——《實(shí)測MYSQL UUID性能》。
二、統(tǒng)一把主鍵類型設(shè)為bigint吧
　　bigint是從-2^63 (-9223372036854775808)到2^63-1 (9223372036854775807)的所有整型數(shù)據(jù)，存儲大小為8個(gè)字節(jié)。而int是從-2^31 (-2,147,483,648)到2^31-1 (2,147,483,647)的整型數(shù)據(jù)，存儲大小為4個(gè)字節(jié)。存儲空間擴(kuò)大一倍，而存儲數(shù)據(jù)卻擴(kuò)大N倍，再加上主鍵是一個(gè)自增的字段，我們根本無法控制它會自增到多少數(shù)值，所以我通常在建表的時(shí)候，主鍵類型都是設(shè)為bigint的，同樣，上面提到的編號字段類型也是bigint。
三、不要把varchar長度設(shè)太“死”
　　這也是我之前經(jīng)常犯得一個(gè)毛病，比如手機(jī)，我就設(shè)置為varchar(11)，郵編設(shè)置成varchar(6)，姓名設(shè)置成varchar(10)等等等等，看似每個(gè)字段都設(shè)置得很嚴(yán)謹(jǐn)，但是在項(xiàng)目實(shí)際進(jìn)行中，這完全就是自找苦吃，比如手機(jī)，用戶偏偏就要在手機(jī)號前輸個(gè)0，又比如郵編，如果用戶輸入的是全角的數(shù)字呢？姓名就更不用說了，萬一是個(gè)少數(shù)民族的人，名字七八個(gè)字。所以我建議，既然定義為varchar，就代表不會涉及到計(jì)算，何不干脆定義一個(gè)通用的長度，比如varchar(50)，如果真要限制長度，用程序去判斷，不要讓數(shù)據(jù)庫來限制，不然用戶輸了一長串，結(jié)果mysql就存了前幾個(gè)字符，讓人覺得這程序有問題。

上一篇：mysql5.5 master-slave(Replication)配置方法

下一篇：MYSQL建立外鍵失敗幾種情況記錄Can't create table不能創(chuàng)建表