1. mysql中常見的數據類型
一：MySQL數據類型
MySQL中定義數據欄位的類型對你資料庫的優化是非常重要的
MySQL支持多種數據類型，大致可以分為三類：數值 日期/時間和字元串
二、數值類型
1.整數類型
2. 高性能MySQL：字元串類型（2）
字元串類型（ ）
與CHAR 和VARCHAR 類似的類型還有BINARY 和VARBINARY 它們存儲的是二進制字元串 二進制字元串跟常規字元串非常相似 但是二進制字元串存儲的是位元組碼而不是字元 填充也不一樣 MySQL 填充BINARY 採用的是 （零位元組）而不是空格 在檢索時也不會去掉填充值
當需要存儲二進制數據 並且希望MySQL 使用位元組碼而不是字元進行比較時 這些類型是非常有用的 二進制比較的優勢並不僅僅體現在大小寫敏感上 MySQL 比較BINARY 字元串時 每次按一個位元組 並且根據該位元組的數值進行比較 因此 二進制比較比字元比較簡單很多 所以也就更快
慷慨是不明智的
使用VARCHAR（ ） 和VARCHAR（ ） 存儲 hello 的空間開銷是一樣的 那麼使用更短的列有什麼優勢嗎？
事實證明有很大的優勢 更大的列會消耗更多的內存 因為MySQL 通常會分配固定大小的內存塊來保存內部值 尤其是使用內存臨時表進行排序或操作時會特別糟糕 在利用磁碟臨時表進行排序時也同樣糟糕
所以最好的策略是只分配真正需要的空間
BLOB 和TEXT 類型
BLOB 和TEXT 都是為存儲很大的數據而設計的字元串數據類型 分別採用二進制和字元方式存儲
實際上 它們分別屬於兩組不同的數據類型家族 字元類型是TINYTEXT SMALLTEXT TEXT MEDIUMTEXT LONGTEXT ;對應的二進制類型是TINYBLOB SMALLBLOB BLOB MEDIUMBLOB LONGBLOB BLOB 是SMALLBLOB 的同義詞 TEXT 是SMALLTEXT 的同義詞
與其他類型不同 MySQL 把每個BLOB 和TEXT 值當作一個獨立的對象處理 存儲引擎在存儲時通常會做特殊處理 當BLOB 和TEXT 值太大時 InnoDB 會使用專門的 外部
存儲區域來進行存儲 此時每個值在行內需要 ~ 個位元組存儲一個指針 然後在外部存儲區域存儲實際的值
BLOB 和TEXT 家族之間僅有的不同是BLOB 類型存儲的是二進制數據 沒有排序規則或字元集 而TEXT 類型有字元集和排序規則
MySQL 對BLOB 和TEXT 列進行排序與其他類型是不同的 它只對每個列的最前max_sort_length 位元組而不是整個字元串做排序 如果只需要排序前面一小部分字元 則可以減小max_sort_length 的配置 或者使用ORDER BY SUSTRING（column length）
MySQL 不能將BLOB 和TEXT 列全部長度的字元串進行索引 也不能使用這些索引消除排序 （關於這個主題下一章會有更多的信息 ）
磁碟臨時表和文件排序
因為Memory 引擎不支持BLOB 和TEXT 類型 所以 如果查詢使用了BLOB 或TEXT列並且需要使用隱式臨時表 將不得不使用MyISAM 磁碟臨時表 即使只有幾行數據也是如此（Percona Server 的Memory 引擎支持BLOB 和TEXT 類型 但直到本書寫作之際 同樣的場景下還是需要使用磁碟臨時表）
這會導致嚴重的性能開銷 即使配置MySQL 將臨時表存儲在內存塊設備上（RAMDisk） 依然需要許多昂貴的系統調用
最好的解決方案是盡量避免使用BLOB 和TEXT 類型 如果實在無法避免 有一個技巧是在所有用到BLOB 欄位的地方都使用SUBSTRING（column length） 將列值轉換為字元串（在ORDER BY 子句中也適用） 這樣就可以使用內存臨時表了 但是要確保截取的子字元串足夠短 不會使臨時表的大小超過max_heap_table_size 或tmp_table_size 超過以後MySQL 會將內存臨時表轉換為MyISAM 磁碟臨時表
最壞情況下的長度分配對於排序的時候也是一樣的 所以這一招對於內存中創建大臨時表和文件排序 以及在磁碟上創建大臨時表和文件排序這兩種情況都很有幫助 例如 假設有一個 萬行的表 佔用幾個GB 的磁碟空間 其中有一個utf 字元集的VARCHAR（ ） 列 每個字元最多使用 個位元組 最壞情況下需要 位元組的空間 如果在ORDER BY 中用到這個列 並且查詢掃描整個表 為了排序就需要超過 GB 的臨時表
這三行數據實際存儲為整數 而不是字元串 可以通過在數字上下文環境檢索看到這個雙重屬性
返回目錄 高性能MySQL
編輯推薦
ASP NET MVC 框架揭秘
Oracle索引技術
ASP NET開發培訓視頻教程
lishixin/Article/program/MySQL/201311/29686
3. MySql數據類型有哪些
1. MySQL數據類型
在MySQL中有如下幾種數據類型：
（1）數值型
數值是諸如32 或153.4 這樣的值。MySQL 支持科學表示法，科學表示法由整數或浮點數後跟「e」或「E」、一個符號（「+」或「-」）和一個整數指數來表示。1.24E+12 和23.47e-1 都是合法的科學表示法表示的數。而1.24E12 不是合法的，因為指數前的符號未給出。
浮點數由整數部分、一個小數點和小數部分組成。整數部分和小數部分可以分別為空，但不能同時為空。
數值前可放一個負號「-」以表示負值。
（2）字元（串）型
字元型（也叫字元串型，簡稱串）是諸如「Hello, world!」或「一個饅頭引起的血案」這樣的值，或者是電話號碼87398413這樣的值。既可用單引號也可用雙引號將串值括起來。
初學者往往分不清數值87398143和字元串87398143的區別。都是數字啊，怎麼一 個要用數值型，一個要用字元型呢？關鍵就在於：數值型的87398143是要參與計算的，比如它是金融中的一個貨款總額；而字元型的87398143是不 參與計算的，只是表示電話號碼，這樣的還有街道號碼、門牌號碼等等，它們都不參與計算。
（3）日期和時間型
日期和時間是一些諸如「2006-07-12」或「12:30:43」這樣的值。MySQL還支持日期/時間的組合，如「2006-07-12 12:30:43」。
（4）NULL值
NULL表示未知值。比如填寫表格中通訊地址不清楚留空不填寫，這就是NULL值。
我們用Create Table語句創建一個表（參看前面的章節），這個表中包含列的定義。例如我們在前面創建了一個joke表，這個表中有content和writer兩個列：
定義一個列的語法如下：
其中列名由col_name 給出。列名可最多包含64個字元，字元包括字母、數字、下劃線及美元符號。列名可以名字中合法的任何符號（包括數字）開頭。但列名不能完全由數字組成，因 為那樣可能使其與MySQL數據類型分不開。MySQL保留諸如SELECT、DELETE和CREATE這樣的詞，這些詞不能用做列名，但是函數名（如POS 和MIN）是可以使用的。
列類型col_type表示列可存儲的特定值。列類型說明符還能表示存放在列中的值的最大長 度。對於某些類型，可用一個數值明確地說明其長度。而另外一些值，其長度由類型名蘊含。例如，CHAR(10) 明確指定了10個字元的長度，而TINYBLOB值隱含最大長度為255個字元。
有的類型說明符允許指定最大的顯示寬度（即顯示值時使用多少個字元）。浮 點類型允許指定小數位數，所以能控制浮點數的精度值為多少。
可以在列類型之後指定可選的類型說明屬性，以及指定更多的常見屬性。屬性起修飾類型的作用，並更改其處理列值的方式，屬性有以下類型：
（1）專用屬性用於指定列。例如，UNSIGNED 屬性只針對整型，而BINARY屬性只用於CHAR 和VARCHAR。
（2）通用屬性除少數列之外可用於任意列。可以指定NULL 或NOT NULL 以表示某個列是否能夠存放NULL。還可以用DEFAULT，def_value 來表示在創建一個新行但未明確給出該列的值時，該列可賦予值def_value。def_value 必須為一個常量；它不能是表達式，也不能引用其他列。不能對BLOB 或TEXT 列指定預設值。
如果想給出多個列的專用屬性，可按任意順序指定它們，只要它們跟在列類型之後、通用屬性之前即可。類似地，如果需要給出多個通用屬性，也可按任意順序給出它們，只要將它們放在列類型和可能給出的列專用屬性之後即可。
2. MySQL的列（欄位）類型
資料庫中的每個表都是由一個或多個列（欄位）構成的。在用CREATE TABLE語句創建一個表時，要為每列（欄位）指定一個類型。列（欄位）的類型比MySQL數據類型更為細化，它精確地描述了給定表列（欄位）可能包含的值的種類，如是否帶小數、是否文字很多。
4. 高性能MySQL：字元串類型（1）
字元串類型（ ）
MySQL 支持多種字元串類型 每種類型還有很多變種 這些數據類型在 和 版本發生了很大的變化 使得情況更加復雜 從MySQL 開始 每個字元串列可以定義自己的字元集和排序規則 或者說校對規則（collation）（更多關於這個主題的信息請參考第 章） 這些東西會很大程度上影響性能
VARCHAR 和CHAR 類型
VARCHAR 和CHAR 是兩種最主要的字元串類型 不幸的是 很難精確地解釋這些值是怎麼存儲在磁碟和內存中的 因為這跟存儲引擎的具體實現有關 下面的描述假設使用的存儲引擎是InnoDB 和/ 或者MyISAM 如果使用的不是這兩種存儲引擎 請參考所使用的存儲引擎的文檔
先看看VARCHAR 和CHAR 值通常在磁碟上怎麼存儲 請注意 存儲引擎存儲CHAR 或者VARCHAR 值的方式在內存中和在磁碟上可能不一樣 所以MySQL 伺服器從存儲引擎讀出的值可能需要轉換為另一種存儲格式 下面是關於兩種類型的一些比較
VARCHAR
VARCHAR 類型用於存儲可變長字元串 是最常見的字元串數據類型 它比定長類型更節省空間 因為它僅使用必要的空間（例如 越短的字元串使用越少的空間） 有一種情況例外 如果MySQL 表使用ROW_FORMAT=FIXED 創建的話 每一行都會使用定長存儲 這會很浪費空間
VARCHAR 需要使用 或 個額外位元組記錄字元串的長度 如果列的最大長度小於或等於 位元組 則只使用 個位元組表示 否則使用 個位元組 假設採用latin 字元集 一個VARCHAR（ ） 的列需要 個位元組的存儲空間 VARCHAR（ ） 的列則需要 個位元組 因為需要 個位元組存儲長度信息
VARCHAR 節省了存儲空間 所以對性能也有幫助 但是 由於行是變長的 在UPDATE 時可能使行變得比原來更長 這就導致需要做額外的工作 如果一個行佔用的空間增長 並且在頁內沒有更多的空間可以存儲 在這種情況下 不同的存儲引擎的處理方式是不一樣的 例如 MyISAM 會將行拆成不同的片段存儲 InnoDB則需要分裂頁來使行可以放進頁內 其他一些存儲引擎也許從不在原數據位置更新數據
下面這些情況下使用VARCHAR 是合適的 字元串列的最大長度比平均長度大很多 列的更新很少 所以碎片不是問題 使用了像UTF 這樣復雜的字元集 每個字元都使用不同的位元組數進行存儲
在 或者更高版本 MySQL 在存儲和檢索時會保留末尾空格 但在 或更老的版本 MySQL 會剔除末尾空格
InnoDB 則更靈活 它可以把過長的VARCHAR 存儲為BLOB 我們稍後討論這個問題
CHAR
CHAR 類型是定長的 MySQL 總是根據定義的字元串長度分配足夠的空間 當存儲CHAR 值時 MySQL 會刪除所有的末尾空格（在MySQL 和更老版本中VARCHAR也是這樣實現的 也就是說這些版本中CHAR 和VARCHAR 在邏輯上是一樣的 區別只是在存儲格式上） CHAR 值會根據需要採用空格進行填充以方便比較
CHAR 適合存儲很短的字元串 或者所有值都接近同一個長度 例如 CHAR 非常適合存儲密碼的MD 值 因為這是一個定長的值 對於經常變更的數據 CHAR 也比VARCHAR 更好 因為定長的CHAR 類型不容易產生碎片 對於非常短的列 CHAR 比VARCHAR 在存儲空間上也更有效率 例如用CHAR（ ） 來存儲只有Y 和N 的值 如果採用單位元組字元集注 只需要一個位元組 但是VARCHAR（ ） 卻需要兩個位元組 因為還有一個記錄長度的額外位元組
CHAR 類型的這些行為可能有一點難以理解 下面通過一個具體的例子來說明 首先 我們創建一張只有一個CHAR（ ） 欄位的表並且往裡面插入一些值
當檢索這些值的時候 會發現string 末尾的空格被截斷了
如果用VARCHAR（ ） 欄位存儲相同的值 可以得到如下結果
數據如何存儲取決於存儲引擎 並非所有的存儲引擎都會按照相同的方式處理定長和變長的字元串 Memory 引擎只支持定長的行 即使有變長欄位也會根據最大長度分配最大空間 不過 填充和截取空格的行為在不同存儲引擎都是一樣的 因為這是在MySQL 伺服器層進行處理的
返回目錄 高性能MySQL
編輯推薦
ASP NET MVC 框架揭秘
Oracle索引技術
ASP NET開發培訓視頻教程
lishixin/Article/program/MySQL/201311/29687