Mysql中有哪些建表規范

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

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

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

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

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

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

2.tb_demo.MYD，存儲數據；

3.tb_demo.MYI，存儲索引。

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

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

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

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

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

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

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

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

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

3.存儲在Memory表中的數據如果突然丟失，不會對應用服務產生實質的負面影響。

（2） MERGE

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

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

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

1、   decimal   類型是適合財務和貨幣計算的   128   位數據類型。

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

3、   decimal所能儲存的數比double大，從double到decimal的類型轉換不會出現任何問題。

4、

float 單精度浮點 32bit，

double 雙精度浮點64bit，

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

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

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

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

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

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

（3)    IEEE754標準的計算機浮點數，在內部是用二進制表示的，但在將一個十進制數轉換為二進制浮點數時，也會造成誤差，原因是不是所有的數都能轉換成有限長度的二進制數。

即一個二進制可以準確轉換成十進制，但一個帶小數的十進制不一定能夠準確地用二進制來表示。

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

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

例如：

SELECT INET_ATON('209.207.224.40');

3520061480

SELECT INET_NTOA(3520061480);

209.207.224.40

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

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

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

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

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

六、短數據類型，使用TINYINT。

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

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

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

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

（2）

tinyint 1字節 （-128,127）

smallint 2字節 （-32768,32767）

int 無符號 0-65535

mediumint 3字節 （-8388608,8388607）

int或integer 4字節 （-2147483648,2147483647）

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

詳細講解：

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

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

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

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

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

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

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

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

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

   
   

對比

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

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

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

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

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

注意事項：

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

當排序時只使用該列的前max_sort_length個字節。max_sort_length的 默認值是1024；該值可以在啟動d服務器時使用--max_sort_length選項進行更改。

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

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

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

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

區別：

1、char的總結：

char最大長度是255字符，注意是字符數和字符集沒關系。可以有默認值，尾部有空格會被截斷。

2、varchar的總結：

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

二 理論知識

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

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

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

存儲限制

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

編碼限制

gbk :每個字符最多占用2個字節

utf8:每個字符最多占用3個字節

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

列長度限制

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

一、表字符集選擇UTF8。

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

（2） 統一，不會有轉換產生亂碼風險

（3） 其他地區的用戶（美國、印度、臺灣）無需安裝簡體中文支持，就能正常看您的文字，并且不會出現亂碼

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

十二、存儲年使用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）、插入值,支持整數和字符串，支持 2位數 或者 4位數

00~69  將轉換為2000~2069之間

70~99  將轉換為1970~1999之間

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

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

（3）、注意點

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

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

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

3、其他和日期有關的可以通過整型保存

時間初  ： 存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字節，bigint使用8個字節。

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

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

（1）4個字節儲存 （2）值以UTC格式保存 （3）時區轉化 ，存儲時對當前的時區進行轉換，檢索時再轉換回當前的時區。 （4）TIMESTAMP值不能早于1970或晚于2037

3)datetime （1）8個字節儲存 （2）與時區無關 （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代表杯子中裝滿了空氣，雖然杯子看起來都是空的，但是區別是很大的。

十六、表結構變更需要通知DBA審核。

數據庫管理員 DBA ：：：Database Administrator

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

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