選擇MYSQL列的類型

2024-07-24 12:55:35

字體：大中小

來源：轉載

供稿：網友

2.3 選擇列的類型
上一節描述了各種可供選擇的mysql的列類型及其屬性，以及它們可存儲的各種值，所占用的存儲空間等等。但是在實際創建一個表時怎樣決定用哪些類型呢？本節討論在做出決定前應考慮的各種因素。最“常用”的列類型是串類型。可將任何數據存儲為串，因為數和日期都可以串的形式表示。但是為什么不將所有列都定義為串從而結束這里的討論呢？讓我們來看一個簡單的例子。假定有一些看起來像數的值。可將它們表示為串，但應該這樣做嗎？這樣做會發生什么事？
有一樁事不可避免，那就是可能要使用更多的空間，因為較串來說，數的存儲更為有效。我們可能已經注意到，由于數和串處理方式的不同，查詢結果也有所不同。例如，數的排序與串的排序就有所不同。數2 小于數11，但串“2”按字典順序大于“ 11”。可用如下數值內容的列來搞清這個問題：

將零加到該列強制得出一個數值，但是這樣合理嗎？一般可能不合理。將該列作為數而不是串具有幾個重要的含義。它對每個列值實施串到數的轉換，這是低效的。而且將該列的值轉換為計算結果妨礙mysql使用該列上的索引，降低了以后的查詢速度。如果這些值一開始就是作為數值存儲的，那么這些性能上的降低都不會出現。采用一種表示而不用另一種的簡單選擇實際上并不簡單，它在存儲需求、查詢效率以及處理性能等方面都會產生重要的影響。
前面的例子說明，在選擇列類型時，有以下幾個問題需要考慮：
■ 列中存儲何種類型的值？這是一個顯而易見的問題，但必須確定。可將任何類型的值表示為串，尤其當對數值使用更為合適的類型可能得到更好的性能時（日期和時間值也是這樣）。可見，對要處理的值的類型進行評估不一定是件微不足道的事，特別在數據是別人的數據時更是如此。如果正在為其他人建立一個表，搞清列中要存儲的值的類型極為重要，必須提足夠多的問題以便得到作出決定的充足的信息。
■ 列值有特定的取值范圍嗎？如果它們是整數，它們總是非負值嗎？如果這樣，可采用unsigned 類型。如果它們是串，總能從定長值集中選出它們嗎？如果這樣， enum或set 是很合適的類型。在類型的取值范圍與所用的存儲量之間存在折衷。需有一個多“大”的類型？對于數，如果其取值范圍有限，可以選擇較小的類型，對取值范圍幾乎無限的數，應該選擇較大的類型。對于串，可以使它們短也可以使它們長，但如果希望存儲的值只含不到10 個字符，就不應該選用char( 2 5 5 )。
■ 性能與效率問題是什么？有些類型比另外一些類型的處理效率高。數值運算一般比串的運算快。短串比長串運行更快，而且磁盤消耗更小。定長類型比可變長類型的性能更好。
■ 希望對值進行什么樣的比較？對于串，其比較可以是區分大小寫的，也可以不區分大小寫。其選擇也會影響排序，因為它是基于比較的。
■ 計劃對列進行索引嗎？如果計劃對列進行索引，那么將會影響您對列類型的選擇，因為有的mysql版本不允許對某些類型進行索引，例如不能對blob 和text 類型進行索引。而且有的mysql版本要求定義索引列為not null 的，這使您不能使用null 值。
現在讓我們來更詳細地考慮這些問題。這里要指出的是：在創建表時，希望作出盡可能好的列類型選擇，但如果所作的選擇其實際并不是最佳的，這也不會帶來多大的問題。可用alter table 將原來選擇的類型轉換為更好的類型。在發現數據所含的值比原設想的大時，可像將smallint 更換成mediumint 那樣簡單地對類型進行更換。有時這種更換也可能很復雜，例如將char 類型更換成具有特定值集的enum 類型。在mysql3.23 及以后的版本中，可使用procedure analyse( ) 來獲得表列的信息，諸如最小值和最大值以及推薦的覆蓋列中值的取值范圍的最佳類型。這有助于確定使用更小的類型，從而改進涉及該表的查詢的性能，并減少存儲該表所需的空間量。
2.3.1列中存儲何種類型的值
在決定列的類型時，首先應該考慮該列的值類型，因為這對于所選擇的類型來說具有最為明顯的意義。通常，在數值列中存儲數，在串列中存儲串，在日期和時間列中存儲日期和時間。如果數值有小數部分，那么應該用浮點列類型而不是整數類型，如此等等。有時也存
在例外，不可一概而論。主要是為了有意義地選擇類型，應該理解所用數據的特性。如果您打算存儲自己的數據，大概對如何存儲它們會有自己很好的想法。但是，如果其他人請您為
他們建一個表，決定列類型有時會很困難。這不像處理自己的數據那么容易。應該充分地提問，搞清表實際應該包含何種類型的值。
如果有人告訴您，某列需要記錄“降雨量”。那是一個數嗎？或者它“主要”是一個數值，即，一般是但不總是編碼成一個數嗎？例如，在看電視新聞時，氣象預報一般包括降雨量。有時是一個數（如“ 0 . 2 5”英寸的雨量），但是有時是“微量( t r a c e )”降雨，意思是“雨根本就不大”。這對氣象預報很合適，但在數據庫中怎樣存儲？有可能需要將“微量”量化為一個數，以便能用數值列類型來記錄降雨量，或許需要使用串，以便可以記錄“微量”這個詞。或者可以提出某種更為復雜的安排，使用一個數值列和一個串列，如果填充一個列就讓另一個列為null。很明顯，可能的話，應該避免最后這種選擇；最后這種選擇使表難于理解，使查詢更為困難。我們一般盡量以數值形式存儲所有的行，而且只為了顯示的需要才對它們進行轉換。例如，如果小于0.01英寸的非零降雨量被視為微量，那么可以如下選擇列值：

對于金錢的計算，需要處理元和分部分。這似乎像浮點值，但float和double 容易出現舍入錯誤，除了只需要大致精確的記錄外，這些類型可能不適合。因為人們對自己的錢都是很敏感的，最好是用一種能提供完善的精確性的類型，例如：
■ 將錢表示為decimal(m, 2) 類型，選擇m 為適合于所需取值范圍的最大寬度。這給出具有兩位小數精度的浮點值。decimal 的優點是將值表示為一個串，而且不容易出現舍入錯誤。不利之處是串運算比內部存儲為數的值上的運算效率差。
■ 可在內部用整數類型來表示所有的錢值。其優點是內部用整數來計算，這樣會非常快。不利之處是在輸入或輸出時需要利用乘或除100 對值進行轉換。有些數據顯然是數值的，但必須決定是使用浮點類型還是使用整數類型。應該搞清楚所用的單位是什么以及需要什么樣的精度。整個單元的精度都夠嗎？或者需要表示小數的單元嗎？這將有助于您在整數列和浮點數列之間進行區分。例如，如果您正表示權重，那么如果記錄的值為英磅，可以使用一個整形列。如果希望記錄小數部分，就應該使用浮點列。在有的情況下，甚至會使用多個字段，例如：如果希望根據磅和盎司記錄權重，則可以使用多個列。
高度（h e i g h t）是另外一種數值類型，有如下幾種表示方法：
■ 諸如“6 英尺2 英寸”可表示為“ 6 - 2”這樣一個串。這種形式具有容易察看和理解的優點（當然比“ 74 英寸更好理解”），但是這種值很難用于數學運算，如求和或取平均值。
■ 一個數值字段表示英尺，另一個數值字段表示英寸。這樣的表示進行數值運算相對容易，但兩個字段比一個字段難于使用。
■ 只用一個表示英寸的數值段。這是數據庫最容易處理的方式，但是這種方式意義最不明確。不過要記住，不一定要用與您慣常使用的那種格式來表示值。可以用mysql的函數將值轉換為看上去意義明顯的值。因此，最后這種表示方法可能是表示高度的最好方法。
如果需要存儲日期信息，需要包括時間嗎？即，它們永遠都需要包括時間嗎？ mysql不提供具有可選時間部分的日期類型： date 可不包含時間，而datetime 必須包含時間。如果時間確實是可選的，那么可用一個date 列記錄日期，一個time 列記錄時間。允許time 列為null 并解釋為“無時間”：

在用基于日期信息的主-細目關系連接兩個表時，決定是否需要時間值特別重要。假如您正在進行一項研究，包括一些對進入您的辦公室的人進行測試的題目。在一個標準的初步測試集之后，您可能會在同一天進行幾個額外的測試，測試的選擇視初步測試結果而定。您可能會利用一個主-細目關系來表示這些信息，其中題目的標識信息和標準的初步測試存儲在一個主記錄中，而其他測試保存為輔助細目表的行。然后基于題目id 與進行測試的日期將這兩個表連接到一起。
在這種情況下必須回答的問題是，是否可以只用日期，或者是否需要既使用日期又使用時間。這個問題依賴于一個題目是否可以在同一天投入測試過程不止一次。如果是這樣，那么應該記錄時間（比方說，記錄測試過程開始的時間），或者用datetime 列，或者分別用
date 和time 列（兩者都必須填寫）。如果一個題目一天測試了兩次，沒有時間值就不能將該題目的細目記錄與適當的主記錄進行關聯。
我曾經聽過有人聲稱“我不需要時間；我從不在同一天把一道題測試兩次”。有時他們是對的，但是我也看到過這些人后來在錄入同一天測試多次的題目的數據后，反過來考慮怎樣防止細目記錄與錯誤的主記錄相混。很抱歉，這時已經太遲了！有時可以在表中增加time 列來處理這個問題，不幸的是，除非有某些獨立的數據源，如原書面記錄，否則很難整理現有記錄。此外，沒辦法消除細目記錄的歧義，以便將它們關聯到合適的主記錄上。即使有獨立的信息源，這樣做也是非常亂的，很可能使已經編寫來利用表的應用程序出問題。最好是向表的擁有者說明問題并保證在創建他們的表之前進行很好的描述。
有時具有一些不完整的數據，這會干擾列類型的選擇。如果進行家譜研究，需要記錄出生日期和死亡日期，有時會發現所能搜集到的數據中只是某人出生或死亡的年份，但沒有確切的日期。如果使用date 列，除非有完整的日期值，否則不能輸入日期。如果希望能夠記
錄所具有的任何信息，即使不完整也保存，那么可能必須保存獨立的年、月、日字段。這樣就可以輸入所具有的日期成員并將沒有的部分設為null。在mysql3.23 及以后的版本中，還允許date 的日為0 或者月和日部分為0。這樣“模糊”的日期可用來表示不完整的日期值。
2.3.2 列值有特定的取值范圍嗎
如果已經決定從通用類別上選擇一種列類型，那么考慮想要表示的值的取值范圍會有助于將您的選擇縮減到該類別中特定的類型上。假如希望存儲整數值。這些整數值的取值范圍為0 到10 0 0，那么可以使用從smallint 到bigint 的所有類型。如果這些整數值的取值
范圍最多為2 000 000，則不能使用s m a l l i n t，其選擇范圍從mediumint 到b i g i n t。需要從這個可能的選擇范圍中選取一種類型。當然，可以簡單地為想要存儲的值選擇最大的類型（如上述例子中選擇b i g i n t）。但是，一般應該為所要存儲的值選擇足以存儲它的最小的類型。這樣做，可以最小化表占用的存儲量，得到最好的性能，因為通常較小列的處理比較大列的快。如果不知道所要表示的值的取值范圍，那么必須進行猜測或使用bigint 以應付最壞的情況。（請注意，如果進行猜測時使用了一個太小的類型，工作不會白做；以后可以利用alter table 來將此列改為更大一些的類型。）
在第1章中，我們為學分保存方案創建了一個score 表，它有一個記錄測驗和測試學分的score 列。為了討論簡單起見，創建該表時使用了int 類型，但現在可以看出，如果學分在0到100 的取值范圍內，更好的選擇應該是tinyint unsigned，因為所用的存儲空間較小。數據的取值范圍還影響列類型的屬性。如果該數據從不為負，可使用unsigned 屬性；否則就不能用它。
串類型沒有數值列那樣的“取值范圍”，但它們有長度，需要知道該串可使用的列最大長度。如果串短于2 5 6個字符，可使用char、varchar、tinytext 或tinyblob 等類型。如果想要更長的串，可使用text 或blob 類型，而char 和varchar 不再是
選項。對于用來表示某個固定值集合的串列，可以考慮使用enum 或set 列類型。它們可能是很好的選項，因為它們在內部是用數來表示的。這兩個類型上的運算是數值化的，因此，比其他的串類型效率更高。它們還比其他串類型緊湊、節省空間。在描述必須處理的值的范圍時，最好的術語是“總是”和“決不”（如“總是小于10 0 0”或“決不為負”），因為它們能更準確地約束列類型的選擇。但在未確證之前，要慎用這兩個術語。特別是與其他人談他們的數據，而他們開始亂用這兩個術語時要注意。在有人說“總是”或“決不”時，一定要搞清他們說的確實是這個含義。有時人們說自己的數據總是有某種特定的性質，而其真正的含義是“幾乎總是”。
例如，假如您為某些人設計一個表，而他們告訴您，“我們的測試學分總是0 到10 0”。根據這個描述，您選擇了tinyint 類型并使它為unsigned 的，因為值總是非負的。然而，您發現編碼錄入數據庫的人有時用－ 1來表示“學生因病缺席”。呀，他們沒告訴您這事。可能可以用null 來表示－1，但如果不能，必須記錄－ 1，這樣就不能用unsigned 列了（只好用alter table 來補救！）。有時關于這些情形的討論可通過提一些簡單的問題來簡化，如問：曾經有過例外嗎？如果曾經有過例外情況，即使是只有一次，也必須考慮。您會發現，和您討論數據庫設計的人總是認為，如果例外不經常發生，那么就沒什么關系。然而在創建數據庫時，就不能這樣想了。需要提的問題并不是例外出現有多頻繁，而是有沒有例外？如果有，必須考慮進去。
2.3.3 性能與效率問題
列類型的選擇會在幾個方面影響查詢性能。如果記住下幾節討論的一般準則，將能夠選出有助于mysql有效處理表的列類型。
1. 數值與串的運算
數值運算一般比串運算更快。例如比較運算，可在單一運算中對數進行比較。而串運算涉及幾個逐字節的比較，如果串更長的話，這種比較還要多。如果串列的值數目有限，應該利用enum 或set 類型來獲得數值運算的優越性。這兩種類型在內部是用數表示的，可更為有效地進行處理。例如替換串的表示。有時可用數來表示串值以改進其性能。例如，為了用點分四位數（d o t t e d - q ua d）表示法來表示ip 號，如19 2 . 16 8 . 0 . 4，可以使用串。但是也可以通過用四字節的unsigned 類型的每個字節存儲四位數的每個部分，將ip 號轉換為整數形式。這即可以節省空間又可加快查找速度。但另一方面，將ip 號表示為int 值會使諸如查找某個子網的號碼這樣的模式匹配難于完成。因此，不能只考慮空間問題；必須根據利用這些值做什么來決定哪種表示更適合。
2. 更小的類型與更大的類型
更小的類型比更大的類型處理要快得多。首先，它們占用的空間較小，且涉及的磁盤活動開銷也少。對于串，其處理時間與串長度直接相關。一般情況下，較小的表處理更快，因為查詢處理需要的磁盤i/o 少。對于定長類型的列，應該選擇最小的類型，只要能存儲所需范圍的值即可。例如，如果mediumint 夠用，就不要選擇b i g i n t。如果只需要float精度，就不應該選擇d o u b l e。對于可變長類型，也仍然能夠節省空間。一個blob 類型的值用2 字節記錄值的長度，而一個longblob 則用4 字節記錄其值的長度。如果存儲的值長度永遠不會超過6 4 k b，使用blob 將使每個值節省2 字節（當然，對于text 類型也可以做類似的考慮）。
3. 定長與可變長類型
定長類型一般比可變長類型處理得更快：
■ 對于可變長列，由于記錄大小不同，在其上進行許多刪除和更改將會使表中的碎片更多。需要定期運行optimize table 以保持性能。而定長列就沒有這個問題。
■ 在出現表崩潰時，定長列的表易于重新構造，因為每個記錄的開始位置是確定的。可變長列就沒有這種便利。這不是一個與查詢處理有關的性能問題，但它必定能加快表的修復過程。如果表中有可變長的列，將它們轉換為定長列能夠改進性能，因為定長記錄易于處理。在試圖這樣做之前，應該考慮下列問題：
■ 使用定長列涉及某種折衷。它們更快，但占用的空間更多。char(n) 類型列的每個值總要占用n 個字節（即使空串也是如此），因為在表中存儲時，值的長度不夠將在右邊補空格。而varchar(n) 類型的列所占空間較少，因為只給它們分配存儲每個值所需要的空間，每個值再加一個字節用于記錄其長度。因此，如果在char 和varchar列之間進行選擇，需要對時間與空間作出折衷。如果速度是主要關心的因素，則利用char 列來取得定長列的性能優勢。如果空間是關鍵，應該使用varchar 列。
■ 不能只轉換一個可變長列；必須對它們全部進行轉換。而且必須使用一個alte rtable 語句同時全部轉換，否則轉換將不起作用。
■ 有時不能使用定長類型，即使想這樣做也不行。例如對于比255 字符長的串，沒有定長類型。
4. 可索引類型
索引能加快查詢速度，因此，應該選擇可索引的類型。
5. null 與not null 類型
如果定義一列為not null，其處理更快，因為mysql在查詢處理中不必檢查該列的值弄清它是否為null，表中每行還能節省一位。避免列中有null 可以使查詢更簡單，因為不需要將null 作為一種特殊情形來考慮。通常，查詢越簡單，處理就越快。所給出的性能準則有時是互相矛盾的。例如，根據mysql能對行定位這一方面來說，包含char 列的定長行比包含varchar 列的可變長行處理快。但另一方面，它也將占用更多的空間，因此，會導致更多的磁盤活動。從這個觀點來看， varchar 可能會更快。作為一個經驗規則，可假定定長列能改善性能，即使它占用更多的空間也如此。對于某個特殊的關鍵應用，可能會希望以定長和可變長兩種方式實現一個表，并進行某些測試以決定哪種方式對您的特定應用來說更快。
2.3.4 希望對值進行什么樣的比較
根據定義串的方式，可以使串類型以區分大小寫或不區分大小寫的方式進行比較和排序。表2-14 示出不區分大小寫的每個類型及其等價的區分大小寫類型。根據列定義中給不給出關鍵字b i n a ry，有的類型（char、varchar）是二進制編碼或非二進制編碼的。其他類型（b l o b、t e x t）的“二進制化”隱含在類型名中。

請注意，二進制（區分大小寫）類型僅在比較和排序行為上不同于相應的非二進制（不區分大小寫）類型。任意串類型都可以包含任意種類的數據。特別是， text 類型盡管在列類型名中稱為“t e x t（文本）”，但它可以很好地存儲二進制數據。如果希望使用一個在比較時既區分大小寫，又可不區分大小寫的列。可在希望進行區分大小寫的比較時，利用b i n a ry 關鍵字強制串作為二進制串值。例如，如果my_col 為一個char 列，可按不同的方式對其進行比較：
my_col = “a b c” 不區分大小寫
binary my_col =“a b c” 區分大小寫
my_col = binary“a b c” 區分大小寫
如果有一個希望以非字典順序存儲的串值，可考慮使用enum 列。enum 值的排序是根據列定義中所列出枚舉值的順序進行的，因此可以使這些值以任意想要的次序排序。
2.3.5 計劃對列進行索引嗎
使用索引可更有效地處理查詢。索引的選擇是第4 章中的一個主題，但一般原則是將where 子句中用來選擇行的列用于索引。如果您要對某列進行索引或將該列包含在多列索引中，則在類型的選擇上可能會有限定。在早于3.23.2 版的mysql發行版中，索引列必須定義為not null，并且不能對blob 或text 類型進行索引。這些限制在mysql3.23.2 版中都撤消了，但如果您正使用一個更早的版本，不能或不愿升級，那么必須遵從這些約束。不過在下列情形中可以繞過它們：
■ 如果可以指定某個值作為專用的值，那么能夠將其作為與null 相同的東西對待。對于date 列，可以指定“0000 - 00 - 00”表示“無日期”。在串列中，可以指定空串代表“缺值”。在數值列中，如果該列一般只存儲非負值，則可使用- 1。
■ 不能對blob 或text 類型進行索引，但如果串不超過255 它符，可使用等價的varchar 列類型并對其進行索引。可varchar(255) binary 用于blob 值，將varchar(255) 用于text 值。
2.3.6 列類型選擇問題的相互關聯程度
不要以為列類型的選擇是相互獨立的。例如，數值的取值范圍與存儲大小有關；在增大取值的范圍時，需要更多的存儲空間，這會影響性能。另外，考慮選擇使用auto _increment 來創建一個存放唯一序列號的列有何含義。這個選擇有幾個結果，它們涉及列
的類型、索引和null 的使用，現列出如下：
■ auto_increment 是一個應該只用于整數類型的列屬性。它將您的選擇限定在tinyint 到bigint 之上。
■ auto_increment 列應該進行索引，從而當前最大的序列號可以很快就確定，不用對表進行全部掃描。此外，為了防止序列號被重用，索引號必須是唯一的。這表示必須將列定義為primary key 或定義為unique 索引。
■ 如果所用的mysql版本早于3 . 2 3 . 2，則索引列不能包含null 值，因此，必須定義列為not null。所有這一切表示，不能像如下這樣只定義一個auto_increment 列：