MySQL資料類型

標籤：

一、數實值型別

  1. 對於整型資料，Mysql支援在類型名稱後面的小括弧內指定顯示寬度，如果不指定則預設為int(11)，一般配合zerofill使用，數字位元不夠的空間

     使用字元 “0”填滿。

     欄位定義樣本：phone int zerofill;

  2. 如果一個列指定為zerofill，則Mysql自動為該列添加unsigned屬性。。

  3. 一個表最多隻能有一個auto_increment列。

  4. 定點數decimal在Mysql內部以字串形式存放，比浮點數更精確。（預設整數位為10，小數位為0）。浮點數儲存資料存在誤差問題，浮點數的比較也有

     誤差，應盡量避免，如果非要使用浮點數比較則最好使用範圍比較而不要使用“==”比較。

  5. BIT類型用於存放位欄位值。對於位欄位，直接使用select命令查詢將看不到結果，可以使用bin()或者hex()函數進行讀取，分別將其顯示為二進位和十六

     進位格式。若位元超出bit類型欄位長度，則無法插入。

  6. 浮點數當插入資料的精度超過欄位精度定義時，會將資料進行四捨五入後插入，該過程浮點數不會報錯，但是定點數可能會進行警告然後插入，或者直

     接報錯，無法插入，這取決於SQLMode。

 

 

二、日期時間類型

 

1. Mysql只給表中的第一個TIMESTAMP欄位設定預設值為系統時間current_timestamp，如果有第二個TIMESTAMP類型欄位，則預設值設定為0.

2. TIMESTAMP還和時區相關，當插入日期時，會先轉換為本地時區後存放，而從資料庫裡面取出時，也同樣需要將日期轉換為本地時區後顯示。這樣，兩

    個不同時區的使用者看到的同一個日期可能是不一樣的。

3. Mysql每種日期時間類型都有一個有效值範圍，如果超出這個範圍，在預設的SQLMode下，系統會提示錯誤，並將以零值來進行儲存。

4. TIMESTAMP的取值範圍為1970年到2038年的某一天，所以它不適合存放比較久遠的日期。

5. TIMESTAMP的屬性受Mysql版本和伺服器SQLMode的影響很大。

6. 日期時間類型的使用原則：選擇能夠滿足應用的最小儲存的日期類型。

注意：

    Mysql的時間類型都不能賦值時間戳記。。。而應該按YYYYMMDDHHMMSS這樣的時間格式（年月日時分秒之間可以加入多種不同分隔字元），或者使用Mysql的now()等函數賦值。

 

 

三、字串類型

 

1. 在檢索的時候，char列刪除了字串尾部的空格，而varchar則保留這些空格。binary和varbinary類似於char和varchar，不同的是他們包含二進位字串

   而不包含非二進位字串。當儲存binary值時，在值的最後通過填充零位元組以達到指定的欄位定義長度，如：在定義為binary(3)的欄位中插入‘a’，

 使用hex函數查看是610000.

2. 由於char是固定長度的，所以它的處理速度比varchar快得多，但其缺點是浪費儲存空間，程式需要對行尾空格進行處理。對於那些長度變化不大並且對

   查詢速度有較高要求的資料可以考慮使用char類型來儲存。

3. 在MySQL 中，不同的儲存引擎對CHAR 和VARCHAR 的使用原則有所不同：

（1）MyISAM 儲存引擎：建議使用固定長度的資料列代替可變長度的資料列。

（2）MEMORY 儲存引擎：目前都使用固定長度的資料行儲存，因此無論使用CHAR或VARCHAR 列都沒有關係。兩者都是作為CHAR 類型處理。

（3）InnoDB 儲存引擎：建議使用VARCHAR 類型。對於InnoDB 資料表，內部的行儲存格式沒有區分固定長度和可變長度列（所有資料行都使用指向資料

   列值的頭指標），因此在本質上，使用固定長度的CHAR 列不一定比使用可變長度VARCHAR 列效能要好。因而，主要的效能因素是資料行使用的儲存總

   量。由於CHAR 平均佔用的空間多於VARCHAR，因此使用VARCHAR 來最小化需要處理的資料行的儲存總量和磁碟I/O 是比較好的。

4. Text和blob的主要區別是blob能用來儲存位元據比如照片，而text只能儲存字元資料，比如一篇文章或日記。

5. Blob和text值會引起一些效能問題，特別是在執行了大量的刪除操作時，會在表中留下很大的“空洞”，以後填入這些“空洞”的記錄子插入的效能上會

   有影響。為了提高效能，建議定期使用optimize table功能對這類表進行片段整理，避免因為“空洞”導致效能問題。

   由此也可以看出：MySQL資料表刪除記錄之後，資料表檔案大小並不會減小，記錄刪除的地方會留下一些空白，以後插入記錄的時候就會先去填滿這些空

   白。當然，可以手動使用optimize table命令去除這些空白，使資料檔案變小。

6. 可以使用合成的索引來提高大文字欄位的查詢效能：

   簡單來說，合成索引就是根據大文字欄位的內容建立一個散列值（例如使用md5函數），並把這個散列值儲存在單獨的資料列中，接下來就可以通過檢索

   散列值找到資料行了。這在一定程度上減少I/O，從而提高查詢效率。但是這種技術只能用於精確匹配的查詢，對於類似<或>=等範圍搜尋操作符是沒有作

   用的。如果需要對大文字欄位進行模糊查詢，可以只為欄位的前n個字元創建索引，例如：

   Create index idx_blob on t(content(100))，其中content為text類型欄位。這樣對content的前100個字元進行模糊查詢就可以使用到首碼索引了，但

   是查詢條件中“%”不能放在最前面，否則索引將不會被使用。

7. ENUM類型是忽略大小寫。對於插入不存在ENUM指定範圍內的值時，並沒有返回警告（Mysql5.7插入失敗），而是插入了enum中的第一個值，enum

   類型只允許從值集合中選取單個值。

8. 相比ENUM，SET可以儲存的成員數量比較少，而且需要的位元組數比較多，但是set可以一次選取多個成員。對於超出允許值範圍的值不允許插入，對於

   包含重複成員的集合將只取一個。

 

MySQL資料類型