柚子快報(bào)激活碼778899分享：【MySQL】數(shù)據(jù)類型

http://yzkb.51969.com/

前言

在前一篇文章中，我們介紹了數(shù)據(jù)庫的基本操作，而在插入表時(shí)涉及了許多關(guān)于表的數(shù)據(jù)類型，接下來就一起來學(xué)習(xí)一下MySQL常見的一些文件類型吧。

整形類型

數(shù)據(jù)類型字節(jié)最小值最大值TINYINT1-128127UNSIGNED TINYINT10255SMALLINT2-3276832767UNSIGNED SMALLINT2065535MEDIUMINT3-83886088388607UNSIGNED MEDIUMINT3016777215INT4-21474836482147483647UNSIGNED INT404294967295

可以看到，有非常多的整型可以選擇，根據(jù)其占有的字節(jié)數(shù)，可以表示更大的數(shù)值。在int之上還有更大的 bigint 占 8 個(gè)字節(jié)。需要我們通過具體的情況，對(duì)數(shù)據(jù)類型進(jìn)行選擇，進(jìn)而達(dá)到節(jié)約內(nèi)存消耗的作用。

數(shù)值越界測(cè)試

接下來，我們針對(duì)數(shù)據(jù)類型進(jìn)行一些插入的測(cè)試，這里以 tinyint 為例(極值較小，比較好操作)。

mysql> create table t1(

-> id tinyint

-> );

Query OK, 0 rows affected (0.05 sec)

mysql> desc t1;

+-------+------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+-------+------------+------+-----+---------+-------+

| id | tinyint(4) | YES | | NULL | |

+-------+------------+------+-----+---------+-------+

1 row in set (0.00 sec)

如上，我們創(chuàng)建了一個(gè)表并設(shè)定了一個(gè) tinyint 類型的列，接下來我們嘗試往表中插入數(shù)據(jù)。

mysql> insert into t1 values(100);

Query OK, 1 row affected (0.01 sec)

mysql> insert into t1 values(-100);

Query OK, 1 row affected (0.00 sec)

mysql> insert into t1 values(127);

Query OK, 1 row affected (0.00 sec)

mysql> insert into t1 values(128);

ERROR 1264 (22003): Out of range value for column 'id' at row 1

mysql> select * from t1;

+------+

| id |

+------+

| 100 |

| -100 |

| 127 |

+------+

3 rows in set (0.00 sec)

可以看到，最后一個(gè)數(shù)據(jù)插入前，我們都成功地將數(shù)據(jù)插入了表格，通過上面那個(gè)數(shù)據(jù)類型的范圍表中，我們可以知道，tinyint 的最大值只有127，若我們插入128便超過了它能夠承受的范圍便無法插入。

若我們將列屬性轉(zhuǎn)換成無符號(hào)，便能夠插入128這個(gè)數(shù)字。

mysql> create table t2 (

-> id tinyint unsigned

-> );

Query OK, 0 rows affected (0.02 sec)

mysql> insert into t2 values(128);

Query OK, 1 row affected (0.00 sec)

mysql> select * from t2;

+------+

| id |

+------+

| 128 |

+------+

1 row in set (0.00 sec)

總結(jié)

每個(gè)數(shù)據(jù)類型都有其對(duì)應(yīng)的數(shù)據(jù)范圍，無法插入超出數(shù)據(jù)范圍的數(shù)。 對(duì)于MySQL來說一般會(huì)直接攔截我們的非法操作，換言之，若我們成功插入了數(shù)據(jù)，那么這個(gè)操作一定是合法的。 一般而言，數(shù)據(jù)類型本身也是MySQL的約束，為的是倒逼程序員進(jìn)行正確的插入。 盡量不適用unsigned，放在使用場(chǎng)景中，一般原類型可能存放不下的數(shù)據(jù)，再加上無符號(hào)大概率也是存放不下的，因此不如在一開始就使用更大的數(shù)據(jù)類型進(jìn)行存儲(chǔ)。

Bit類型

Bit類型的本質(zhì)就是一種位圖結(jié)構(gòu)，其位數(shù)為1~64(默認(rèn)為1)。

bit(位數(shù))

于是我們創(chuàng)建一個(gè)表，其中列的數(shù)據(jù)類型分別為 int 和 8 位的bit。

mysql> create table t1 (

-> id int,

-> b bit(8)

-> );

Query OK, 0 rows affected (0.02 sec)

mysql> desc t1;

+-------+---------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+-------+---------+------+-----+---------+-------+

| id | int(11) | YES | | NULL | |

| b | bit(8) | YES | | NULL | |

+-------+---------+------+-----+---------+-------+

2 rows in set (0.00 sec)

現(xiàn)在我們開始往其中插入數(shù)據(jù)。

mysql> insert into t1 value(1,10);

Query OK, 1 row affected (0.00 sec)

mysql> insert into t1 value(2,66);

Query OK, 1 row affected (0.00 sec)

mysql> insert into t1 value(3,67);

Query OK, 1 row affected (0.00 sec)

可以看到，雖然插入的是 66 和 67，但實(shí)際上打印出來的卻是 B 和 C，我們便可以推測(cè)，打印 bit 類型的數(shù)據(jù)時(shí)默認(rèn)情況下是使用 ASCII 碼的匹配模式進(jìn)行打印的。

mysql> select * from t1;

+------+------+

| id | b |

+------+------+

| 1 |

|

| 2 | B |

| 3 | C |

+------+------+

3 rows in set (0.01 sec)

若我們想要將 bit 類型的數(shù)據(jù)以十六進(jìn)制的形式進(jìn)行打印，只需在select時(shí)指明要轉(zhuǎn)換的進(jìn)制即可。

mysql> select id,hex(b) from t1;

+------+--------+

| id | hex(b) |

+------+--------+

| 1 | A |

| 2 | 42 |

| 3 | 43 |

+------+--------+

3 rows in set (0.00 sec)

若插入的數(shù)據(jù)超過了該列能承受的范圍，MySQL同樣會(huì)阻止這個(gè)行為。

mysql> insert into t1 value(6,256);

ERROR 1406 (22001): Data too long for column 'b' at row 1

浮點(diǎn)數(shù)類型

有了整型自然也會(huì)有浮點(diǎn)型的數(shù)據(jù)類型，其中當(dāng)屬這兩個(gè)數(shù)據(jù)類型最為常用。

float

定義浮點(diǎn)數(shù)類型時(shí)需要進(jìn)行如下操作，其中 m 表示整個(gè)浮點(diǎn)數(shù)的顯示長(zhǎng)度，而 d 表示小數(shù)的位數(shù)。且這個(gè)數(shù)據(jù)將占用 4 個(gè)字節(jié)。

float(m,d)

例如，現(xiàn)在我們有一個(gè)表，其中float后面跟著的是4和2，不難得知，這個(gè)浮點(diǎn)數(shù)的數(shù)據(jù)范圍為 -99.99~99.99。

mysql> desc t3;

+-------+------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+-------+------------+------+-----+---------+-------+

| id | int(11) | YES | | NULL | |

| score | float(4,2) | YES | | NULL | |

+-------+------------+------+-----+---------+-------+

2 rows in set (0.00 sec)

同樣，接下來嘗試插入幾個(gè)數(shù)據(jù)。

mysql> insert into t3 value(1,1.1);

Query OK, 1 row affected (0.04 sec)

mysql> insert into t3 value(2,1.115);

Query OK, 1 row affected (0.01 sec)

mysql> insert into t3 value(3,1.112);

Query OK, 1 row affected (0.00 sec)

mysql> insert into t3 value(4,99.99);

Query OK, 1 row affected (0.00 sec)

mysql> insert into t3 value(5,99.999);

ERROR 1264 (22003): Out of range value for column 'score' at row 1

在我們平時(shí)的使用中，大概率插入的都是第一個(gè)數(shù)據(jù)這種數(shù)值，而當(dāng)小數(shù)位數(shù)不足時(shí)，則會(huì)默認(rèn)補(bǔ)0。

若插入的數(shù)據(jù)的小數(shù)位數(shù)超過了限定的長(zhǎng)度，則會(huì)進(jìn)行四舍(數(shù)據(jù)3)五入(數(shù)據(jù)2)。

例如數(shù)據(jù)四，此時(shí)剛好就是當(dāng)前能存儲(chǔ)的最大數(shù)值，此時(shí)若再進(jìn)行進(jìn)位的話便會(huì)出現(xiàn)錯(cuò)誤(數(shù)據(jù)5)。

mysql> select * from t3;

+------+-------+

| id | score |

+------+-------+

| 1 | 1.10 |

| 2 | 1.12 |

| 3 | 1.11 |

| 4 | 99.99 |

+------+-------+

4 rows in set (0.00 sec)

若是定義成 unsigned 類型，便僅僅失去負(fù)數(shù)部分的數(shù)據(jù)。

decimal

decimal(m,d) m最大為65默認(rèn)為10，d最大值為30默認(rèn)為0

decimal使用起來跟float很像但又有些區(qū)別。

我們將float和decimal放在同一個(gè)表中，插入相同的數(shù)據(jù)。

mysql> insert into t4 values(12.24141251511,12.24141251511);

Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> select * from t4;

+---------+-------------+

| s1 | s2 |

+---------+-------------+

| 12.2414 | 12.24141252 |

+---------+-------------+

1 row in set (0.00 sec)

可以看到，雖然插入了同樣的數(shù)據(jù)，但decimal對(duì)于精度的保存明顯強(qiáng)于float。

因此對(duì)于小數(shù)的精度有要求時(shí)，便推薦使用decimal。

字符串類型

之前我們就已經(jīng)使用過了字符串類型，而其又分成char和varchar兩種字符串類型，分別是定長(zhǎng)字符串和可變長(zhǎng)度字符串。

在MySQL中，字符串既可以用 ’ ’ 圈定，同時(shí)也可以使用 " "。

char

char(L) L表示能存儲(chǔ)的字符數(shù)，最大長(zhǎng)度可以達(dá)到255

這里的字符指的就是，表選擇的字符集對(duì)應(yīng)的字符，就是實(shí)實(shí)在在的符號(hào)。

若設(shè)定的大小超過了最大長(zhǎng)度，系統(tǒng)則會(huì)提示我們可以換成一個(gè)更大的類型進(jìn)行存儲(chǔ)。

mysql> create table t2(str char(256));

ERROR 1074 (42000): Column length too big for column 'str' (max = 255); use BLOB or TEXT instead

如下列 L 為 3 的 char，前幾次無論我們插入的是字母還是漢字都能成功插入，而一旦長(zhǎng)度超出了限制，便會(huì)報(bào)錯(cuò)。

mysql> insert into t1 value('a');

Query OK, 1 row affected (0.01 sec)

mysql> insert into t1 value('aaa');

Query OK, 1 row affected (0.02 sec)

mysql> insert into t1 value('我我我');

Query OK, 1 row affected (0.00 sec)

mysql> insert into t1 value('aaaa');

ERROR 1406 (22001): Data too long for column 'str' at row 1

之所以叫做定長(zhǎng)字符串，正因?yàn)樗拖駭?shù)組一樣，若插入的數(shù)據(jù)的大小比申請(qǐng)的空間小，那么這些多出來的空間就被浪費(fèi)掉了。

varchar

而 varchar 則是可變長(zhǎng)度字符串，并非當(dāng)類型長(zhǎng)度不夠時(shí)會(huì)自動(dòng)變長(zhǎng)。

varchar(L) varchar最大長(zhǎng)度為65535個(gè)字節(jié)

而是在分配空間時(shí)只會(huì)分配足夠使用的空間，L則是決定了可分配空間的上限。

具體細(xì)節(jié)就和char一樣，需要注意插入字符的長(zhǎng)度不能超過設(shè)定的L。

最大值的計(jì)算

在上面的介紹中只說了 varchar 允許的最大長(zhǎng)度為 65535 個(gè)字節(jié)，那么 L 的最大值該怎么計(jì)算呢？

首先在空間的開頭有 1~3 個(gè)字節(jié)用于記錄數(shù)據(jù)的大小，極限情況下的最大可用字節(jié)為 65532 個(gè)。

接下來取決與此張表選擇的字符集：

例如表使用的是 utf8，在 utf8 中一個(gè)字符的大小為 3 個(gè)字節(jié)，因此 L 最大值為 65532 / 3 = 21844 .

再大 1 的 21845 便無法容納了，系統(tǒng)便建議我們換一個(gè)數(shù)據(jù)類型。

mysql> create table t1(

-> str varchar(21844)

-> );

Query OK, 0 rows affected (0.03 sec)

mysql> create table t2( str varchar(21845) );

ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs

若使用 gbk 編碼時(shí)，一個(gè)字符占 2 個(gè)字節(jié)，則 L 最大值為 65532 / 2 = 32766 .

如何選擇char和varchar

若數(shù)據(jù)確定長(zhǎng)度都一樣就使用char (如: 身份證)。若數(shù)據(jù)長(zhǎng)度有變化就使用varchar (如: 姓名,地址)。定長(zhǎng)字符串占用的磁盤空間相對(duì)浪費(fèi)，但訪問的效率較高，而變長(zhǎng)反之。

日期和時(shí)間類型

date

日期類型，只包含日期，格式為 ‘yyyy-mm-dd’ ,占用三個(gè)字節(jié)。

datetime

時(shí)間日期類型，從年一直到秒，格式為 'yyyy-mm-dd HH:ii:ss ’ ,占用八個(gè)字節(jié)。

timestamp

時(shí)間戳，從1970年開始，格式與 datetime 一致, 占用四個(gè)字節(jié)。

前兩種類型的數(shù)據(jù)需要程序員自己插入，而時(shí)間戳在每次更新數(shù)據(jù)時(shí)便會(huì)自動(dòng)更新。

mysql> desc t2;

+-------+-----------+------+-----+-------------------+-----------------------------+

| Field | Type | Null | Key | Default | Extra |

+-------+-----------+------+-----+-------------------+-----------------------------+

| d1 | date | YES | | NULL | |

| d2 | datetime | YES | | NULL | |

| d3 | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |

+-------+-----------+------+-----+-------------------+-----------------------------+

3 rows in set (0.00 sec)

如上表，我們開始往表中插入數(shù)據(jù)。

值得注意的一點(diǎn)是，插入時(shí)需要按類型規(guī)定的格式插入，才能被準(zhǔn)確識(shí)別。

mysql> insert into t2(d1,d2) values('2023-9-21','2023-9-21 21:13:50');

Query OK, 1 row affected (0.02 sec)

mysql> select * from t2;

+------------+---------------------+---------------------+

| d1 | d2 | d3 |

+------------+---------------------+---------------------+

| 2023-09-21 | 2023-09-21 21:13:50 | 2023-09-21 21:13:42 |

+------------+---------------------+---------------------+

1 row in set (0.00 sec)

enum和set類型

enum

相信枚舉類型對(duì)于學(xué)習(xí) C/C++ 的同學(xué)而言并不陌生，該類型中的值必須是先前設(shè)定好的，一旦試圖插入其他數(shù)據(jù)就會(huì)報(bào)錯(cuò)。

enum (選項(xiàng)1,選項(xiàng)2,...)

mysql> create table t3(

-> 科目 enum('語文','數(shù)學(xué)','英語')

-> );

Query OK, 0 rows affected (0.02 sec)

mysql> desc t3;

+--------+----------------------------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+--------+----------------------------------+------+-----+---------+-------+

| 科目 | enum('語文','數(shù)學(xué)','英語') | YES | | NULL | |

+--------+----------------------------------+------+-----+---------+-------+

1 row in set (0.01 sec)

因此，我們只能在這三個(gè)選項(xiàng)中選擇一個(gè)插入，若出現(xiàn)了系統(tǒng)不認(rèn)識(shí)的選項(xiàng)，便會(huì)被攔截起來。

mysql> insert into t3 values('語文');

Query OK, 1 row affected (0.01 sec)

mysql> insert into t3 values('數(shù)學(xué)');

Query OK, 1 row affected (0.01 sec)

mysql> insert into t3 values('英語');

Query OK, 1 row affected (0.00 sec)

mysql> insert into t3 values('體育');

ERROR 1265 (01000): Data truncated for column '科目' at row 1

mysql> select * from t3;

+--------+

| 科目 |

+--------+

| 語文 |

| 數(shù)學(xué) |

| 英語 |

+--------+

3 rows in set (0.00 sec)

不僅如此，我們還可以根據(jù)聲明的順序使用下標(biāo)(從1開始)選擇選項(xiàng)進(jìn)行插入。

mysql> insert into t3 values(1);

Query OK, 1 row affected (0.01 sec)

mysql> insert into t3 values(2);

Query OK, 1 row affected (0.01 sec)

mysql> insert into t3 values(3);

Query OK, 1 row affected (0.00 sec)

mysql> select * from t3;

+--------+

| 科目 |

+--------+

| 語文 |

| 數(shù)學(xué) |

| 英語 |

| 語文 |

| 數(shù)學(xué) |

| 英語 |

+--------+

6 rows in set (0.00 sec)

若插入時(shí)什么都不寫則為 NULL，代表無，而插入 0 代表這里有東西，但是只是進(jìn)行占位，沒有實(shí)際意義。

set

set 表示的是一個(gè)集合，每次插入只能插入它的子集。

set(選項(xiàng)1，選項(xiàng)2...)

mysql> create table t4(

-> 愛好 set('唱','跳','rap','籃球')

-> );

Query OK, 0 rows affected (0.03 sec)

mysql> desc t4;

+--------+---------------------------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+--------+---------------------------------+------+-----+---------+-------+

| 愛好 | set('唱','跳','rap','籃球') | YES | | NULL | |

+--------+---------------------------------+------+-----+---------+-------+

1 row in set (0.00 sec)

同樣，若想選擇不存在的選項(xiàng)則會(huì)被制止。

mysql> insert into t4 values('唱,跳,籃球');

Query OK, 1 row affected (0.01 sec)

mysql> insert into t4 values('唱,rap,籃球');

Query OK, 1 row affected (0.00 sec)

mysql> insert into t4 values('唱,籃球');

Query OK, 1 row affected (0.00 sec)

mysql> insert into t4 values('sing,dance');

ERROR 1265 (01000): Data truncated for column '愛好' at row 1

mysql> select * from t4;

+----------------+

| 愛好 |

+----------------+

| 唱,跳,籃球 |

| 唱,rap,籃球 |

| 唱,籃球 |

+----------------+

3 rows in set (0.00 sec)

那么 set 也有像 enum 那樣以數(shù)字的形式作進(jìn)行插入嗎？

當(dāng)然有了，但是由于 set 是以子集的形式插入，自然不能直接使用下標(biāo)作為依據(jù)。

而是需要先將數(shù)字轉(zhuǎn)換成對(duì)應(yīng)的位圖，以位圖的狀態(tài)決定某選項(xiàng)選擇與否。

例如 3 轉(zhuǎn)換成二進(jìn)制就是 0011，第一位和第二位為 1，因此下標(biāo)為 1 和 2 的被選中，所以就選中了唱和跳。

mysql> insert into t4 values(3);

Query OK, 1 row affected (0.00 sec)

mysql> select * from t4;

+---------+

| 愛好 |

+---------+

| 唱,跳 |

+---------+

1 row in set (0.00 sec)

enum和set類型的查找

對(duì)于 enum 來說，直接進(jìn)行嚴(yán)格匹配即可完成查找。

select * from 表名 where 列名 = ...;

指定列名后加上要查找的選項(xiàng)即可完成查找。

mysql> select * from t3 where 科目 = '語文';

+--------+

| 科目 |

+--------+

| 語文 |

| 語文 |

+--------+

2 rows in set (0.00 sec)

若是通過這個(gè)方式對(duì)set類型進(jìn)行查找便只會(huì)進(jìn)行嚴(yán)格的匹配。

mysql> select * from t4 where 愛好='跳';

+--------+

| 愛好 |

+--------+

| 跳 |

| 跳 |

+--------+

2 rows in set (0.00 sec)

哪天我們想要找愛好有跳即可的人該怎么辦？

我們便可使用 find_in_set(a,b) 這個(gè)函數(shù)。

該函數(shù)會(huì)在 b 中查找是否存在 a，并返回真假。

于是我們的查找操作便可以這樣寫。

mysql> select * from t4 where find_in_set('跳',愛好);

+-------------+

| 愛好 |

+-------------+

| 唱,跳 |

| 跳 |

| 跳,rap |

| 唱,跳,rap |

| 跳 |

+-------------+

5 rows in set (0.00 sec)

這下只要愛好有跳的人就都被篩選出來了。

好了，今天【MySQL】數(shù)據(jù)類型 的相關(guān)內(nèi)容到這里就結(jié)束了，如果這篇文章對(duì)你有用的話還請(qǐng)留下你的三連加關(guān)注。

柚子快報(bào)激活碼778899分享：【MySQL】數(shù)據(jù)類型

http://yzkb.51969.com/

相關(guān)文章