腳本之家服務(wù)器常用軟件

快捷導(dǎo)航

mysql中怎樣使用合適的字段和字段長度

更新時(shí)間：2023年11月03日 10:12:02 作者：銘記T

這篇文章主要介紹了mysql中怎樣使用合適的字段和字段長度問題,具有很好的參考價(jià)值,希望對(duì)大家有所幫助,如有錯(cuò)誤或未考慮完全的地方,望不吝賜教

下面是mysql一個(gè)字段中含有哪些信息

一般重要的字段為類型，長度，屬性，非空，索引，自增等等。

注：A_I：auto_increment，代表自增。

字段的類型有好多種，先說下字段中比較常用的類型和長度

整數(shù)型

1、整數(shù)型的數(shù)值類型已經(jīng)限制了取值范圍，有符號(hào)整型和無符號(hào)整型都有，而M值并不代表可以存儲(chǔ)的數(shù)值字符長度，它代表的是數(shù)據(jù)在顯示時(shí)顯示的最小長度；

2、當(dāng)存儲(chǔ)的字符長度超過M值時(shí)，沒有任何的影響，只要不超過數(shù)值類型限制的范圍；

3、當(dāng)存儲(chǔ)的字符長度小于M值時(shí)，只有在設(shè)置了zerofill用0來填充，才能夠看到效果，換句話就是說，沒有zerofill，M值就是無用的。

舉例子，如果你設(shè)置int(11)，那么有個(gè)字段值是123，那么這個(gè)值在顯示寬度上是3位，而設(shè)計(jì)的是顯示的是11位，所以這時(shí)候，你如果在字段設(shè)計(jì)的時(shí)候，選擇zerofill就可以發(fā)現(xiàn)，123變成了00000000123，也就是剩下的8位用0補(bǔ)足了。

所以我們?cè)谠O(shè)計(jì)mysql數(shù)據(jù)庫時(shí)，建表時(shí)，mysql會(huì)自動(dòng)分配長度：int(11)、tinyint(4)、smallint(6)、mediumint(9)、bigint(20)。

所以，就用這些默認(rèn)的顯示長度就可以了。不用再去自己填長度，比如搞個(gè)int(10)、tinyint(1)之類的，基本沒用。而且導(dǎo)致表的字段類型多樣化。

字符串型

CHAR(M), VARCHAR(M)

CHAR(M)定義的列的長度為固定的，M取值可以為0～255之間，當(dāng)保存CHAR值時(shí)，在它們的右邊填充空格以達(dá)到指定的長度。當(dāng)檢索到CHAR值時(shí)，尾部的空格被刪除掉。在存儲(chǔ)或檢索過程中不進(jìn)行大小寫轉(zhuǎn)換。CHAR存儲(chǔ)定長數(shù)據(jù)很方便，CHAR字段上的索引效率級(jí)高，比如定義char(10)，那么不論你存儲(chǔ)的數(shù)據(jù)是否達(dá)到了10個(gè)字節(jié)，都要占去10個(gè)字節(jié)的空間,不足的自動(dòng)用空格填充。

VARCHAR(M)定義的列的長度為可變長字符串，M取值可以為0~65535之間，(VARCHAR的最大有效長度由最大行大小和使用的字符集確定。整體最大長度是65,532字節(jié)）。VARCHAR值保存時(shí)只保存需要的字符數(shù)，另加一個(gè)字節(jié)來記錄長度(如果列聲明的長度超過255，則使用兩個(gè)字節(jié))。VARCHAR值保存時(shí)不進(jìn)行填充。當(dāng)值保存和檢索時(shí)尾部的空格仍保留，符合標(biāo)準(zhǔn)SQL。varchar存儲(chǔ)變長數(shù)據(jù)，但存儲(chǔ)效率沒有CHAR高。如果一個(gè)字段可能的值是不固定長度的，我們只知道它不可能超過10個(gè)字符，把它定義為 VARCHAR(10)是最合算的。

CHAR和VARCHAR最大的不同就是一個(gè)是固定長度，一個(gè)是可變長度。

總結(jié)一下，從空間上考慮，用varchar合適；從效率上考慮，用char合適，關(guān)鍵是根據(jù)實(shí)際情況找到權(quán)衡點(diǎn)，所有當(dāng)需要大量查詢需求的時(shí)候，用char。當(dāng)對(duì)于保存數(shù)據(jù)量過大的需求時(shí)，為了節(jié)省儲(chǔ)存空間用varchar。

TEXT

最大長度為65,535(2的16次方–1)字符的TEXT列。

Text主要是用來存放非二進(jìn)制的文本，如論壇帖子,題目，或者百度知道的問題和回答之類。TEXT列不能有默認(rèn)值，存儲(chǔ)或檢索過程中，不存在大小寫轉(zhuǎn)換，后面如果指定長度，不會(huì)報(bào)錯(cuò)誤，但是這個(gè)長度是不起作用的，意思就是你插入數(shù)據(jù)的時(shí)候，超過你指定的長度還是可以正常插入。其實(shí)可以總結(jié)為用來儲(chǔ)存大批量的文本信息的時(shí)候，使用TEXT。

總結(jié)

1，長度的區(qū)別，char范圍是0～255，varchar最長是64k，如果遇到了大文本，考慮使用text，最大能到4G。

2，效率來說基本是char>varchar>text。

3，char和varchar可以有默認(rèn)值，text不能指定默認(rèn)值。

時(shí)間型

DATETIME：類型用在你需要同時(shí)包含日期和時(shí)間信息的值時(shí)。MySQL檢索并且以'YYYY-MM-DD HH:MM:SS'格式顯示DATETIME值，支持的范圍是'1000-01-01 00:00:00'到'9999-12-31 23:59:59'。
DATE：類型用在你僅需要日期值時(shí)，沒有時(shí)間部分。MySQL檢索并且以'YYYY-MM-DD'格式顯示DATE值，支持的范圍是'1000-01-01'到'9999-12-31'。
TIMESTAMP：列類型提供一種類型，你可以使用它自動(dòng)地用當(dāng)前的日期和時(shí)間標(biāo)記INSERT或UPDATE的操作。
TIME：數(shù)據(jù)類型表示一天中的時(shí)間。MySQL檢索并且以"HH:MM:SS"格式顯示TIME值。支持的范圍是'00:00:00'到'23:59:59'。

datetime和timestamp的區(qū)別：

1.datetime 的日期范圍比較大；如果有1970年以前的數(shù)據(jù)還是要用datetime.但是timestamp 所占存儲(chǔ)空間比較小。

2.timestamp 類型的列還有個(gè)特性：默認(rèn)情況下，在 insert, update 數(shù)據(jù)時(shí)，timestamp 列會(huì)自動(dòng)以當(dāng)前時(shí)間（CURRENT_TIMESTAMP）填充/更新。

3.timestamp比較受時(shí)區(qū)timezone的影響以及MYSQL版本和服務(wù)器的SQL MODE的影響。

字段的屬性值及用處

BINARY：不是函數(shù)，是類型轉(zhuǎn)換運(yùn)算符，它用來強(qiáng)制它后面的字符串為一個(gè)二進(jìn)制字符串，可以理解為在字符串比較的時(shí)候區(qū)分大小寫。
UNSIGNED：既為非負(fù)數(shù)，用此類型可以增加一倍數(shù)據(jù)長度!

UNSIGNED ZEROFILL：剛說過了，數(shù)字型字段當(dāng)存儲(chǔ)的字符長度小于INT(M)中的M值時(shí)，只有在設(shè)置了zerofill用0來填充，才能夠看到效果，換句話就是說，沒有zerofill，M值就是無用的。

timestamp有兩個(gè)屬性，分別是CURRENT_TIMESTAMP 和ON UPDATE CURRENT_TIMESTAMP兩種，使用情況分別如下

1，CURRENT_TIMESTAMP

當(dāng)要向數(shù)據(jù)庫執(zhí)行insert操作時(shí)，如果有個(gè)timestamp字段屬性設(shè)為

CURRENT_TIMESTAMP，則無論這個(gè)字段有沒有set值都插入當(dāng)前系統(tǒng)時(shí)間

2，ON UPDATE CURRENT_TIMESTAMP

當(dāng)執(zhí)行update操作是，并且字段有ON UPDATE CURRENT_TIMESTAMP屬性。則字段無論值有沒有變化，它的值也會(huì)跟著更新為當(dāng)前UPDATE操作時(shí)的時(shí)間。

字段的索引以及設(shè)置

先說下對(duì)索引的最簡(jiǎn)單的理解：

如果你給一個(gè)唯一性的字段加上索引，比如一個(gè)字段是房間號(hào)，你不設(shè)置字段的情況下去搜索RoomNumber=101，那么數(shù)據(jù)庫會(huì)去一條一條的搜索數(shù)據(jù)庫中的RoomNumber字段，直到找到101給你返回，但如果你把RoomNumber設(shè)置為索引，那么數(shù)據(jù)庫就會(huì)直接找到RoomNumber=101這條信息給你返回。

所以索引的作用是加快數(shù)據(jù)庫搜索的效率，但是同樣的，你給一個(gè)字段設(shè)置為索引，是要消耗資源的。

普通索引(INDEX)：最基本的索引，沒有任何限制。
唯一索引(UNIQUE)：與"普通索引"類似，不同的就是：索引列的值必須唯一，但允許有空值。
主鍵索引(PRIMARY)：它是一種特殊的唯一索引，不允許有空值。
全文索引(FULLTEXT )：僅可用于 MyISAM 表，用于在一篇文章中，檢索文本信息的, 針對(duì)較大的數(shù)據(jù)，生成全文索引很耗時(shí)耗空間。

說一下字段的長度問題

位（bit）：數(shù)據(jù)存儲(chǔ)的最小單位。每個(gè)二進(jìn)制數(shù)字0或者1就是1個(gè)位；
字節(jié)（byte）： 8個(gè)位構(gòu)成一個(gè)字節(jié)；即：1 byte (字節(jié))= 8 bit(位)；
字符：是指計(jì)算機(jī)中使用的字母、數(shù)字、字和符號(hào)。

比如不同的酒店的房間號(hào)的方式是不同的，可能是101，或者是FD101，再或者是亞特蘭蒂斯101，這樣不確定的字段，為了提高表的復(fù)用性，我們可以給出一個(gè)長度，比如char(32)，我們可以確定酒店的房間號(hào)無論如何都會(huì)超過這個(gè)長度，這樣就可以在節(jié)省空間的情況下適應(yīng)所有的情況，

但是還有一種情況比如數(shù)據(jù)庫中要存MAC地址，MAC地址格式為XX-XX-XX-XX-XX-XX，一共為12個(gè)數(shù)字和五個(gè)分隔符，那么我們就可以直接char(17)來定死這個(gè)字段的長度，因?yàn)闊o論如何MAC地址都不會(huì)超過17位。

其實(shí)也會(huì)有人覺得我定為char(32)那能比char(17)的查詢效率低多少？確實(shí)微乎其微，但是從習(xí)慣的角度來說，建表要養(yǎng)成一種好的習(xí)慣，所以當(dāng)我們可以確定一個(gè)字段的長度的時(shí)候，就要直接用規(guī)則定死，避免掉后期可能出現(xiàn)的優(yōu)化。