Mysql中有哪些建表規(guī)范

今天就跟大家聊聊有關(guān)Mysql中有哪些建表規(guī)范，可能很多人都不太了解，為了讓大家更加了解，小編給大家總結(jié)了以下內(nèi)容，希望大家根據(jù)這篇文章可以有所收獲。

一、庫名、表名、字段名必須使用小寫字母，“_”分割；庫名、表名、字段名必須不超過12個字符；庫名、表名、字段名見名知意,建議使用名詞而不是動詞。

二、建議使用InnoDB存儲引擎。

存儲引擎：innoDb支持事物，myisam不支持事物，建議使用innoDb， 5.5以后的默認(rèn)引擘，支持事務(wù)，行級鎖，更好的恢復(fù)性，高并發(fā)下性能更好，對多核，大內(nèi)存，ssd等硬件支持更好。

（1） MyISAM表是獨立于操作系統(tǒng)的，這說明可以輕松地將其從Windows服務(wù)器移植到Linux服務(wù)器；每當(dāng)我們建立一個MyISAM引擎的表時，就會在本地磁盤上建立三個文件，文件名就是表明。例如，我建立了一個MyISAM引擎的tb_Demo表，那么就會生成以下三個文件：

1.tb_demo.frm，存儲表定義；

2.tb_demo.MYD，存儲數(shù)據(jù)；

3.tb_demo.MYI，存儲索引。

MyISAM表無法處理事務(wù)，這就意味著有事務(wù)處理需求的表，不能使用MyISAM存儲引擎。MyISAM存儲引擎特別適合在以下幾種情況下使用：

1.選擇密集型的表。MyISAM存儲引擎在篩選大量數(shù)據(jù)時非常迅速，這是它最突出的優(yōu)點。

2.插入密集型的表。MyISAM的并發(fā)插入特性允許同時選擇和插入數(shù)據(jù)。例如：MyISAM存儲引擎很適合管理郵件或Web服務(wù)器日志數(shù)據(jù)。

（2） InnoDB是一個健壯的事務(wù)型存儲引擎，這種存儲引擎已經(jīng)被很多互聯(lián)網(wǎng)公司使用，為用戶操作非常大的數(shù)據(jù)存儲提供了一個強大的解決方案。我的電腦上安裝的MySQL 5.6.13版，InnoDB就是作為默認(rèn)的存儲引擎。InnoDB還引入了行級鎖定和外鍵約束，在以下場合下，使用InnoDB是最理想的選擇：

1.更新密集的表。InnoDB存儲引擎特別適合處理多重并發(fā)的更新請求。
2.事務(wù)。InnoDB存儲引擎是支持事務(wù)的標(biāo)準(zhǔn)MySQL存儲引擎。
3.自動災(zāi)難恢復(fù)。與其它存儲引擎不同，InnoDB表能夠自動從災(zāi)難中恢復(fù)。 4.外鍵約束。MySQL支持外鍵的存儲引擎只有InnoDB。 5.支持自動增加列AUTO_INCREMENT屬性。

一般來說，如果需要事務(wù)支持，并且有較高的并發(fā)讀取頻率，InnoDB是不錯的選擇。

（3）  MEMORY   ：使用MySQL Memory存儲引擎的出發(fā)點是速度。

1.目標(biāo)數(shù)據(jù)較小，而且被非常頻繁地訪問。在內(nèi)存中存放數(shù)據(jù)，所以會造成內(nèi)存的使用，可以通過參數(shù)max_heap_table_size控制Memory表的大小，設(shè)置此參數(shù)，就可以限制Memory表的最大大小。

2.如果數(shù)據(jù)是臨時的，而且要求必須立即可用，那么就可以存放在內(nèi)存表中。

3.存儲在Memory表中的數(shù)據(jù)如果突然丟失，不會對應(yīng)用服務(wù)產(chǎn)生實質(zhì)的負(fù)面影響。

（2） MERGE

MERGE存儲引擎是一組MyISAM表的組合，這些MyISAM表結(jié)構(gòu)必須完全相同，盡管其使用不如其它引擎突出，但是在某些情況下非常有用。說白了，Merge表就是幾個相同MyISAM表的聚合器；Merge表中并沒有數(shù)據(jù)，對Merge類型的表可以進行查詢、更新、刪除操作，這些操作實際上是對內(nèi)部的MyISAM表進行操作。Merge存儲引擎的使用場景

三、存儲精確浮點數(shù)必須使用DECIMAL替代FLOAT和DOUBLE。

（1） decimal 類型可以精確地表示非常大或非常精確的小數(shù)。大至 1028（正或負(fù)）以及有效位數(shù)多達 28 位的數(shù)字可以作為 decimal類型存儲而不失其精確性。該類型對于必須避免舍入錯誤的應(yīng)用程序（如記賬）很有用。

1、   decimal   類型是適合財務(wù)和貨幣計算的   128   位數(shù)據(jù)類型。

2、   decimal不是浮點型、decimal不存在精度損失；

3、   decimal所能儲存的數(shù)比double大，從double到decimal的類型轉(zhuǎn)換不會出現(xiàn)任何問題。

4、

float 單精度浮點 32bit，

double 雙精度浮點64bit，

decimal是高精度 128bit，浮點型。

float double 是 基本類型（primitive type），decimal不是。

5、  浮點數(shù)運算會有精度損失問題，有精度損失時程序不會報告，要程序員自己注意。

（2)   mysql中的數(shù)值類型（不包括整型）：

IEEE754浮點數(shù)： float  （單精度） ， double  或 real  （雙精度）
    定點數(shù)： decimal 或 numeric
    單精度浮點數(shù)的有效數(shù)字二進制是24位，按十進制來說，是8位；雙精度浮點數(shù)的有效數(shù)字二進制是53位，按十進制來說，是16 位

一個實數(shù)的有效數(shù)字超過8位，用單精度浮點數(shù)來表示的話，就會產(chǎn)生誤差！同樣，如果一個實數(shù)的有效數(shù)字超過16位，用雙精度浮點數(shù)來表示，也會產(chǎn)生誤差

（3)    IEEE754標(biāo)準(zhǔn)的計算機浮點數(shù)，在內(nèi)部是用二進制表示的，但在將一個十進制數(shù)轉(zhuǎn)換為二進制浮點數(shù)時，也會造成誤差，原因是不是所有的數(shù)都能轉(zhuǎn)換成有限長度的二進制數(shù)。

即一個二進制可以準(zhǔn)確轉(zhuǎn)換成十進制，但一個帶小數(shù)的十進制不一定能夠準(zhǔn)確地用二進制來表示。

四、建議使用INT UNSIGNED存儲IPV4。

（1） 用UNSINGED INT存儲IP地址占用4字節(jié)，CHAR(15)則占用15字節(jié)。另外，計算機處理整數(shù)類型比字符串類型快。使用INT UNSIGNED而不是CHAR(15)來存儲IPV4地址，通過MySQL函數(shù)inet_ntoa和inet_aton來進行轉(zhuǎn)化。IPv6地址目前沒有轉(zhuǎn)化函數(shù)，需要使用DECIMAL或兩個BIGINT來存儲。

例如：

SELECT INET_ATON('209.207.224.40');

3520061480

SELECT INET_NTOA(3520061480);

209.207.224.40

五、 整形定義中不添加長度，比如使用INT，而不是INT(4)。

（1） mysql中int數(shù)據(jù)類型長度最大為11位,最少為4位,不夠在前面補空格。

（2） 而mysql中int本身就是4個字節(jié) bigint是8個字節(jié) 所以說int(X)的含義就是 int決定數(shù)據(jù)存儲的字節(jié) X表示期望數(shù)據(jù)的列寬度

在SQL語句中int代表你要創(chuàng)建字段的類型，int代表整型，11代表字段的長度。

整數(shù)列的顯示寬度與mysql需要用多少個字符來顯示該列數(shù)值，與該整數(shù)需要的存儲空間的大小都沒有關(guān)系，比如，不管設(shè)定了顯示寬度是多少個字符，bigint都要占用8個字節(jié)。

六、短數(shù)據(jù)類型，使用TINYINT。

（1） 同樣的字節(jié)數(shù)，非負(fù)存儲的數(shù)值范圍更大。如TINYINT有符號為 -128-127，無符號為0-255。

1、TINYINT ，字段類型，如果設(shè)置為UNSIGNED類型，只能存儲從0到255的整數(shù),不能用來儲存[負(fù)數(shù)]。

2、TINYINT 型的字段如果不設(shè)置UNSIGNED類型,存儲-128到127的整數(shù)。

提示：  通常，為了節(jié)省空間，應(yīng)該盡可能的使用最小的 [整型數(shù)據(jù)]。一個TINYINT型數(shù)據(jù)只占用一個字節(jié),一個INT型數(shù)據(jù)占用四個字節(jié)。這看起來似乎差別不大，但是在比較大的表中，字節(jié)數(shù)的增長是很快的。另一方面，一旦你已經(jīng)創(chuàng)建了一個字段，要修改它是很困難的。因此，為安全起見，你應(yīng)該預(yù)測一下，一個字段所需要存儲的數(shù)值最大有可能是多大，然后選擇適當(dāng)?shù)臄?shù)據(jù)類型。

（2）

tinyint 1字節(jié) （-128,127）

smallint 2字節(jié) （-32768,32767）

int 無符號 0-65535

mediumint 3字節(jié) （-8388608,8388607）

int或integer 4字節(jié) （-2147483648,2147483647）

七、不建議使用ENUM類型，使用TINYINT來代替。

詳細(xì)講解：

說起這個ENUM， 經(jīng)查閱各大技術(shù)社區(qū)的網(wǎng)絡(luò)文摘，ENUM確實是mysql里的一個特色字段，印象里模糊記得在以前看到一些比較知名的商城系統(tǒng)如shopnc里面在用它，但也沒細(xì)究，可能是因為他可以設(shè)置字段的區(qū)間范圍，會讓值可以被數(shù)據(jù)庫所控制，有枚舉約束的功能（比如，字段只想有0和1，如果用 TINYINT（1），結(jié)果就可能出現(xiàn)2，那2就是贓數(shù)據(jù)了）

但ENUM也有一些比較棘手的問題，比如數(shù)據(jù)遷移的時候，他幾乎不可能被其他數(shù)據(jù)庫所支持，如果enum里面是字符串，對于其他數(shù)據(jù)庫來說就更郁悶了，還不能設(shè)為tinyint等類型的字段（enum雖然可以存儲字符串，但對于內(nèi)部來說，還是以順序進行索引，比如'a','b','c'，我們也可以用索引值來獲取值select * from tbl_name whre enum = 2，這與select * from tbl_name where enum = 'b'等義）如果你看明白了這兩句SQL為什么等義，那么你也就可以了解為什么不主張用enum字段了。

也就是說，假如一個設(shè)計不合理的ENUM字段，給程序員帶來的就完全是夢魘了，比如一個enum字段的范圍是('0','1','2','3','4','5')，而enum的枚舉值對應(yīng)的索引是從1開始的，因此，insert into table (enum)values(1)，插入的并不是1，而是0。

另外假如你在設(shè)計好enum的枚舉字段范圍并使用了一段時間后，再到字段范圍中加一個枚舉值，并且不是加在最后，那么也就相當(dāng)于把原來的范圍都改變了索引值，也就是當(dāng)你在查詢的時候直接查詢值（并加上單引號），將不會使用enum自身隱藏的索引值來獲取結(jié)果了。

如果是純數(shù)值型，還是建議采用tinyint字段吧，畢竟它也只占一個字節(jié)，即使出現(xiàn)贓數(shù)據(jù)，也可以被接受，不象enum，如果純數(shù)字型范圍，更改了索引，你就不知道你查詢的值是否正確了）

八、盡可能不使用TEXT、BLOB類型。

1. TEXT、BLOB類型會使查詢變慢，如果需要保存超長字符集，建議用varchar（n）類型或?qū)⑦^大字段拆分到其他表中；

2. 使用VARBINARY存儲變長字符串，binary儲存定長字符串。因為二進制字節(jié)流，不存在編碼問題

    binary(n) ：固定長度為 n 字節(jié)，其中 n 值從 1 到 8,000 ，存儲空間為 n 字節(jié)；
   
    varbinary( n | max)：可變長度，n 的取值范圍為 1 至 8,000，max 是指最大存儲空間是 2^31-1 個字節(jié)，即最大4GB；
   
    n：在表列定義或變量聲明語句中沒有指定 n，則默認(rèn)長度為 1；在CAST 函數(shù)中沒有指定 n，則默認(rèn)長度為 30；
   
    詳情：：：      [http://www.cnblogs.com/ljhdo/p/4530293.html](http://www.cnblogs.com/ljhdo/p/4530293.html)

   
   

對比

1、 BLOB是一個二進制大對象，可以容納可變數(shù)量的數(shù)據(jù)。有4種 BLOB類型：TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB。它們只是可容納值的最大長度不同。

有4種TEXT類型：TINYTEXT、TEXT、MEDIUMTEXT和LONGTEXT。這些對應(yīng)4種BLOB類型，有相同的最大長度和存儲需求。

2、 BLOB 列被視為二進制字符串(字節(jié)字符串)。TEXT列被視為非二進制字符串(字符字符串)。BLOB列沒有字符集，并且排序和比較基于列值字節(jié)的數(shù)值值。TEXT列有一個字符集，并且根據(jù)字符集的 校對規(guī)則對值進行排序和比較。

在TEXT或BLOB列的存儲或檢索過程中，不存在大小寫轉(zhuǎn)換。

3、 在大多數(shù)方面，可以將BLOB列視為能夠足夠大的VARBINARY列。同樣，可以將TEXT列視為VARCHAR列。BLOB和TEXT在以下幾個方面不同于VARBINARY和VARCHA

注意事項：

由于BLOB和TEXT值可能會非常長，使用它們時可能遇到一些約束：

當(dāng)排序時只使用該列的前max_sort_length個字節(jié)。max_sort_length的 默認(rèn)值是1024；該值可以在啟動d服務(wù)器時使用--max_sort_length選項進行更改。

運行時增加max_sort_length的值可以在排序或組合時使更多的字節(jié)有意義。任何客戶端可以更改其會話max_sort_length變量的值：

久、禁止在數(shù)據(jù)庫中使用VARBINARY、BLOB存儲圖片、文件等。

如果要存儲圖片、文件等 采用分布式文件系統(tǒng)更高效

十、VARCHAR(N)，N表示的是字符數(shù)不是字節(jié)數(shù)，比如VARCHAR(255)，可以最大可存儲255個漢字，需要根據(jù)實際的寬度來選擇N。

區(qū)別：

1、char的總結(jié)：

char最大長度是255字符，注意是字符數(shù)和字符集沒關(guān)系?？梢杂心J(rèn)值，尾部有空格會被截斷。

2、varchar的總結(jié)：

varchar的最大長度65535是指能存儲的字節(jié)數(shù)，其實最多只能存儲65532個字節(jié)，還有3個字節(jié)用于存儲長度。注意是字節(jié)數(shù)這個和字符集有關(guān)系。一個漢字字符用utf8占用3字節(jié)，用gbk占用2字節(jié)。存儲的最大字符數(shù)因編碼不同而不同通常是n=65532/3或n=65532/2個字符。可以有默認(rèn)值，尾部有空格不會截斷。

二 理論知識

先說明一下 MySQL 歷來版本對 varchar 的定義:

4.0版本以下，varchar(50)，指的是50字節(jié)，如果存放UTF8漢字時，只能存16個（每個中文3字節(jié)）

5.0版本以上，varchar(50)，指的是50字符，無論存放的是數(shù)字、字母還是UTF8中文（每個中文3字節(jié)），都可以存放50個

存儲限制

需要額外占用字節(jié)存放字符的長度:小于255為1個字節(jié)，大于255則要2個字節(jié)

編碼限制

gbk :每個字符最多占用2個字節(jié)

utf8:每個字符最多占用3個字節(jié)

utf8mb4 每個字符最多占用4個字節(jié),中文占3個字節(jié)，emoji表情符號 占用4個字節(jié)

列長度限制

MySQL定義行的長度不能超過65535，該數(shù)值限制了列的數(shù)目。比如所有列為char(128) utf8字符集,最多有65535/(128*3)=170個列。

一、表字符集選擇UTF8。

（1） 使用utf8字符集，如果是漢字，占3個字節(jié)，但ASCII碼字符還是1個字節(jié)。

（2） 統(tǒng)一，不會有轉(zhuǎn)換產(chǎn)生亂碼風(fēng)險

（3） 其他地區(qū)的用戶（美國、印度、臺灣）無需安裝簡體中文支持，就能正?？茨奈淖?，并且不會出現(xiàn)亂碼

（4） ISO-8859-1編碼(latin1)使用了單字節(jié)內(nèi)的所有空間，在支持ISO-8859-1的系統(tǒng)中傳輸和存儲其他任何編碼的字節(jié)流都不會被拋棄。即把其他任何編碼的字節(jié)流當(dāng)作ISO-8859-1編碼看待都沒有問題，保存的是原封不動的字節(jié)流。

十二、存儲年使用YEAR類型。

**    重點：： mysql的日期與時間類型：分為time、date、datetime、timestamp、year，**

（1）、類型支持：year 與 year（4）,注意無year（2）的定義方式，否則報錯“[Err] 1818 - Supports only YEAR or YEAR(4) column.”

create table if  not exists time(
atime  YEAR                         #year的定義，可寫成year或者year(4)

) engine =innodb charset = utf8;

（2）、插入值,支持整數(shù)和字符串，支持 2位數(shù) 或者 4位數(shù)

00~69  將轉(zhuǎn)換為2000~2069之間

70~99  將轉(zhuǎn)換為1970~1999之間

#測試year類型insert into time values( 78);    #數(shù)據(jù)庫中顯示：1978
insert into time values('78'); #數(shù)據(jù)庫中顯示：1978

insert into time values('1978'); #數(shù)據(jù)庫中顯示：1978

（3）、注意點

1、 支持插入 數(shù)字0 或者 字符串0，實際顯示的數(shù)值不同

insert into time  values( 0);  #數(shù)據(jù)庫中顯示：0
insert into time values('0'); #數(shù)據(jù)庫中顯示：2000

2、year只保存年份，占用空間小

3、其他和日期有關(guān)的可以通過整型保存

時間初  ： 存9位

十三、存儲日期使用DATE類型。

MySQL日期類型、日期格式、存儲空間、日期范圍比較。

日期類型        存儲空間       日期格式                 日期范圍

------------ ---------   --------------------- -----------------------------------------

datetime       8 bytes   YYYY-MM-DD HH:MM:SS   1000-01-01 00:00:00 ~ 9999-12-31 23:59:59

timestamp      4 bytes   YYYY-MM-DD HH:MM:SS   1970-01-01 00:00:01 ~ 2038

date           3 bytes   YYYY-MM-DD            1000-01-01          ~ 9999-12-31

year           1 bytes   YYYY                  1901                ~ 2155

十四、存儲時間（精確到秒）建議使用int/bigint類型，int使用4字節(jié)，bigint使用8個字節(jié)。

1）int (1) 4個字節(jié)存儲，INT的長度是4個字節(jié)，存儲空間上比datatime少，int索引存儲空間也相對較小，排序和查詢效率相對較高一點點 （2）可讀性極差，無法直觀的看到數(shù)據(jù)，可能讓你很惱火

可以略微注意2038年問題的陷阱。對于MySQL而言，如果存時間戳請使用timestamp或bigint，而不要使用int。 2)TIMESTAMP

（1）4個字節(jié)儲存 （2）值以UTC格式保存 （3）時區(qū)轉(zhuǎn)化 ，存儲時對當(dāng)前的時區(qū)進行轉(zhuǎn)換，檢索時再轉(zhuǎn)換回當(dāng)前的時區(qū)。 （4）TIMESTAMP值不能早于1970或晚于2037

3)datetime （1）8個字節(jié)儲存 （2）與時區(qū)無關(guān) （3）以'YYYY-MM-DD HH:MM:SS'格式檢索和顯示DATETIME值。支持的范圍為'1000-01-01 00:00:00'到'9999-12-31 23:59:59'a

十五、建議字段定義為NOT NULL。

**   （1）**空值是不占用空間的， not null的效率比null高

** （2） ** MySQL中的NULL其實是占用空間的  ： 打個比方來說，你有一個杯子，空值代表杯子是真空的，NULL代表杯子中裝滿了空氣，雖然杯子看起來都是空的，但是區(qū)別是很大的。

十六、表結(jié)構(gòu)變更需要通知DBA審核。

數(shù)據(jù)庫管理員 DBA ：：：Database Administrator

**    （1）** 每次變更不能說變就變了，不然，別人不知道，肯定也是按照原來的來，報錯的話，也就不好往下進行了，

看完上述內(nèi)容，你們對Mysql中有哪些建表規(guī)范有進一步的了解嗎？如果還想了解更多知識或者相關(guān)內(nèi)容，請關(guān)注億速云行業(yè)資訊頻道，感謝大家的支持。