mysql數(shù)據(jù)類型和字段屬性原理與用法詳解

更新時間：2020年04月15日 11:03:04 作者：隨風(fēng)行云

這篇文章主要介紹了mysql數(shù)據(jù)類型和字段屬性,結(jié)合實(shí)例形式分析了mysql數(shù)據(jù)類型和字段屬性基本概念、原理、分類、用法及操作注意事項(xiàng),需要的朋友可以參考下

本文實(shí)例講述了mysql數(shù)據(jù)類型和字段屬性。分享給大家供大家參考，具體如下：

本文內(nèi)容：

數(shù)據(jù)類型
- 數(shù)值類型
  - 整數(shù)型
  - 浮點(diǎn)型
  - 定點(diǎn)型
- 日期時間類型
- 字符串類型
- 補(bǔ)充：
- - 顯示寬度與zerofll
  - 記錄長度
字段屬性
- 空\不為空值：NULL、NOT NULL
- 主鍵：primary key
- 唯一鍵：unique key
- 自增長：auto_increment
- 默認(rèn)值：default
- 字段描述：comment
- 補(bǔ)充：
- - 復(fù)合鍵

首發(fā)日期：2018-04-08

數(shù)據(jù)類型：

mysql的數(shù)據(jù)類型就是存儲數(shù)據(jù)的類型。

數(shù)值類型：

整數(shù)類型：tinyint,smallint,mediumint,integer,bigint
- 默認(rèn)是有符號，如果需要使用無符號的數(shù)據(jù)類型要在后面加"UNSIGNED"

類型	簡寫	字節(jié)大小	（signed）范圍	（unsigned）范圍	備注
整數(shù)型：
TINYINT		1	0～255	-128～127
SMALLINT		2	0～65535	-32768～32767
MEDIUMINT		3	0～16777215	-8388608～8388607
INTEGER	INT	4	0～4294967295	-2147483648～2147483647
BIGINT		8	0～2⁶⁴-1	-2⁶³～2⁶³-1

小數(shù)類型：
- 浮點(diǎn)類型：float(size,d),double(size,d)
  - size是數(shù)值的最大位數(shù)，d是小數(shù)點(diǎn)右側(cè)的位數(shù)（即使你沒輸夠那么多位也會補(bǔ)全那么多位）。
  - FLOAT的精度為6～7位（依據(jù)情況不同？畢竟機(jī)器是以二進(jìn)制存儲的），DOUBLE的精度為14～15位
  - 整數(shù)部分的個數(shù)為SIZE-D，直接存入的值的個數(shù)不能多于這個，但如果浮點(diǎn)數(shù)四舍五入導(dǎo)致整數(shù)進(jìn)位而溢出超過最大位數(shù)的，系統(tǒng)允許成立（某些版本貌似已經(jīng)不允許了，所以一般小數(shù)位也不要超過）。
  - 很多時候沒必要關(guān)注兩個浮點(diǎn)的數(shù)值范圍大小（是極大的），注重點(diǎn)應(yīng)該是它的精度。
- 定點(diǎn)型：decimal(size,d)
  - size是數(shù)值的最大位數(shù)，最大為65，d是小數(shù)點(diǎn)右側(cè)的最大位數(shù)，最大為30。
  - 定點(diǎn)型不允許因?yàn)樾?shù)部分四舍五入導(dǎo)致整數(shù)部分長度超出范圍。
  - DECIMAL是不會損失精度的?？梢岳斫獬墒褂谩白址贝鎯Γ∕YSQL權(quán)威指南這么說）。
- 直接不帶括號的數(shù)據(jù)類型float、double、decimal代表沒有小數(shù)部分

時間日期類型：

時間日期類型包括date,time,datetime,timestamp,year;

Datetime:時間日期，格式是YYYY-MM-DD HH:II:SS,表示的范圍是從1000到9999年（有些版本已經(jīng)允許0-9999？不確定）；

Date:日期，格式是YYYY-MM-DD ，表示的范圍是從1000到9999年
Time:時間段，格式是HH:II:SS, 指定的時間在某個區(qū)間之間，有正負(fù)。表示的范圍 -838:59:59~838:59:59，這代表某個日期的前后時間范圍【如果你僅僅想要用來表示24小時內(nèi)的時間，可以忽略它的時間范圍】
Timestamp:格式是YYYY-MM-DD HH:II:SS，表示的范圍從1970-01-01 00:00:00到2038-01-19 03:14:07 【一旦該行記錄被修改，那么timestamp會修改成當(dāng)前時間?！?
Year:年份，表示的范圍1901-2156
- 原本有兩種格式，year(2)和year(4)，但5.7中移除了year(2)。

字符串類型：

set :集合字符串。將一個個特定的字符串放到集合里面。
- 定義了集合之后，不能插入集合元素之外的字符串，（可以插入多個符合條件的字符串）
- 集合字符串實(shí)際上也是僅僅存儲數(shù)值，系統(tǒng)自動轉(zhuǎn)換成對應(yīng)的字符串。
- 集合中每一個元素對應(yīng)一個二進(jìn)制位，被選中的為1，沒選中的為0，左邊為二進(jìn)制中的低位，右邊為高位，從右到左得到二進(jìn)制結(jié)果

create table my_set(
hobby set('football','basketball','pingpong','computer')
)charset utf8;

insert into my_set values('basketball,computer');
insert into my_set values(3);-- 3=0011
insert into my_set values(5);-- 5=0101

enum :枚舉字符串
- 有點(diǎn)類似set,但這里只能插入一個字符串（set類型的字段的值可以是多個set元素，但enum只能是其中一個元素）
- 將可能出現(xiàn)的數(shù)據(jù)列舉出來，實(shí)際存儲的數(shù)據(jù)只能是列舉出來的數(shù)據(jù)。
- 使用枚舉有利于統(tǒng)一數(shù)據(jù)，以及節(jié)省存儲空間（枚舉只是存儲數(shù)值，系統(tǒng)轉(zhuǎn)換成字符串）。
- 枚舉元素實(shí)際上按定義的順序標(biāo)號，從1開始，所以可以直接插入枚舉元素對應(yīng)的數(shù)值。
- 如:

create table my_enum(
gender enum('male','female','unknown')
)charset utf8;

blob :二進(jìn)制數(shù)據(jù)字符串
- 存儲二進(jìn)制數(shù)據(jù)（少用）

text :文字字符串
- 存儲文字，當(dāng)字符大于255時，一般使用text;

varchar :變長字符串
- varchar(L),L代表字符長度，單位為字符，最大長度為65536。在實(shí)際存儲中需要1到2個字節(jié)來存儲長度（1還是2由實(shí)際大小決定，2¹⁶=35536,<=255的一般一個字節(jié)），在使用時根據(jù)定義的大小來分配空間，但會根據(jù)實(shí)際需要使用的縮小空間（比如規(guī)定60大小，實(shí)際只有30，那么實(shí)際空間就是31～32）。
- 當(dāng)L大于255時，建議使用Text，而不是varchar;

char :定長字符串
- char(L),L代表字符長度，單位為字符，最大長度為255，不使用L時默認(rèn)為1
- 不同環(huán)境中根據(jù)字符大小，實(shí)際占有的空間不同；在UTF8環(huán)境下，char(4)所用的字節(jié)數(shù)為4*3=12；

補(bǔ)充：

顯示寬度與填充：
- 在列類型后面顯示的數(shù)值，代表數(shù)據(jù)最終顯示的最少位數(shù)（包含符號）。
- 一般不夠顯示寬度的數(shù)據(jù)都會采用空格填充，也可以使用zerofill來使用零填充（零填充會導(dǎo)致數(shù)據(jù)類型自動變成無符號的）。
- tiny(3)的空格填充效果：，zerofill效果：

create table myInt(t1 TINYINT(3));
drop table myInt;
insert into myInt values(127),(1);
select * from myInt;
alter table myInt modify t1 tinyint(3) zerofill;
select * from myInt;

不要輕易的使用最大限度來存儲字符串，因?yàn)樯婕坝涗涢L度問題：
- MySQL中規(guī)定任何一條數(shù)據(jù)最長不能超過65535個字節(jié)
- 如果有任何一個字段允許為空，那么系統(tǒng)會自動從整個記錄中保存一個字節(jié)來存儲NULL；
- text文本不占用記錄長度，額外存儲。
- UTF8中varchar的最大限度：65535-2（需要兩個字節(jié)來存儲長度） /3=21844，所以最大字符數(shù)為21844
- GBK中varchar的最大限度:65535-2（需要兩個字節(jié)來存儲長度） /2=32766

字段屬性：

字段屬性是字段除數(shù)據(jù)類型外的屬性，一般有空\不為空值、主鍵、唯一鍵、自增長、默認(rèn)值、描述等屬性。

空\不為空值：NULL、NOT NULL

字段的數(shù)據(jù)默認(rèn)情況下是允許為空的，比如說一條人的信息記錄中可以沒有郵箱（或許有些人考慮用“空字符串”來代表），我們允許可以不填入數(shù)據(jù)的字段可以設(shè)置為null；但比如說某些必填數(shù)據(jù)，我們不想填入的時候留空，可以設(shè)置這個字段為not null
允許為空時的數(shù)據(jù)，空的數(shù)據(jù)顯示為null：
不為空的字段，插入null時，會報(bào)錯：
null\not null屬性的定義方式(因?yàn)槟J(rèn)允許空，所以主要關(guān)注not null)：；當(dāng)然也可以通過修改字段的形式來給字段定義not null屬性

主鍵：primary key

主鍵用于唯一標(biāo)識每一條記錄（每個人都有自己唯一的身份證）
每一張表只能有一個主鍵。
因?yàn)槲ㄒ粯?biāo)識，所以主鍵字段的數(shù)據(jù)不能為空，并且主鍵字段的數(shù)據(jù)值不能重復(fù)
主鍵也是一種索引，可以提高查找速率。
主鍵的定義：
- 1.定義表結(jié)構(gòu)的時候定義成字段的屬性。
- 2.修改表結(jié)構(gòu)，增加一個帶主鍵的字段。
- 3.修改表結(jié)構(gòu)，給某一字段增加主鍵屬性。
主鍵的用途：主鍵的主要用途是用來唯一標(biāo)識每一條記錄，比如我們想將兩個表（學(xué)生表跟老師表）的多對多關(guān)系封裝到一個表中，這個抽取的映射字段一般都是各自的主鍵。；并且由于主鍵的數(shù)據(jù)的不可重復(fù)性，也用來約束數(shù)據(jù)的唯一性。
主鍵的刪除：
```
alter table 表名 drop primary key;
```

唯一鍵：unique key

唯一鍵的功能與主鍵有點(diǎn)類型，但不同的是主鍵只能有一個，唯一鍵可以有多個，而且唯一鍵的字段的數(shù)據(jù)允許為空。
唯一鍵可以約束字段，使得字段的數(shù)據(jù)不能重復(fù)
如果唯一鍵同時也有not null,并且表中沒有主鍵的話，在desc查看表結(jié)構(gòu)中會顯示成主鍵
如果唯一鍵也不允許為空，那么功能與主鍵相同
唯一鍵的定義方法可以參考主鍵的。

唯一鍵的刪除：

alter table 表名 drop index 唯一鍵名; -- 唯一鍵默認(rèn)使用字段名來定義名字

自增長：auto_increment

自增長的功能是可以使某個字段的數(shù)據(jù)隨著記錄的插入而進(jìn)行增長（不給這個字段插入數(shù)據(jù)的情況下）
自增長的前提是這個字段必須是一個“索引”，比如主鍵、唯一鍵
自增長的前提這個字段的數(shù)據(jù)類型是一個數(shù)值型的，（如果給了float,也不會增長成小數(shù)，而僅僅是整數(shù)）
一個表只能有一個自增長。
只有不給值，或者給null的情況下，才能正確自增長；如果某一次自增長失敗了，那么下一次會從當(dāng)前字段的最大值開始繼續(xù)自增長。
自增長的定義方法：；當(dāng)然也可以通過修改字段的形式來給字段定義not null屬性
自增長的修改：
- 修改增長速度：set auto_increment = 值;
- 修改下次增長的值：alter table 表名 auto_increment = 值; 【修改的值只能變大，不能變小，因?yàn)榭赡軐?dǎo)致數(shù)據(jù)重復(fù)問題】
自增長的查看：show vairable like "auto_increment%";
自增長的刪除：利用alter來刪除，alter修改字段屬性的時候如果不帶上原來的屬性就會被刪掉：