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

http://yzkb.51969.com/

前言

在前一篇文章中，我們介紹了數(shù)據(jù)庫的基本操作，而在插入表時涉及了許多關(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 個字節(jié)。需要我們通過具體的情況，對數(shù)據(jù)類型進(jìn)行選擇，進(jìn)而達(dá)到節(jié)約內(nèi)存消耗的作用。

數(shù)值越界測試

接下來，我們針對數(shù)據(jù)類型進(jìn)行一些插入的測試，這里以 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)建了一個表并設(shè)定了一個 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)

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

若我們將列屬性轉(zhuǎn)換成無符號，便能夠插入128這個數(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é)

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

Bit類型

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

bit(位數(shù))

于是我們創(chuàng)建一個表，其中列的數(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，我們便可以推測，打印 bit 類型的數(shù)據(jù)時默認(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時指明要轉(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同樣會阻止這個行為。

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

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

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

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

float

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

float(m,d)

例如，現(xiàn)在我們有一個表，其中float后面跟著的是4和2，不難得知，這個浮點(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)

同樣，接下來嘗試插入幾個數(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ù)據(jù)這種數(shù)值，而當(dāng)小數(shù)位數(shù)不足時，則會默認(rèn)補(bǔ)0。

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

例如數(shù)據(jù)四，此時剛好就是當(dāng)前能存儲的最大數(shù)值，此時若再進(jìn)行進(jìn)位的話便會出現(xiàn)錯誤(數(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放在同一個表中，插入相同的數(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對于精度的保存明顯強(qiáng)于float。

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

字符串類型

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

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

char

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

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

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

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，前幾次無論我們插入的是字母還是漢字都能成功插入，而一旦長度超出了限制，便會報(bào)錯。

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

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

varchar

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

varchar(L) varchar最大長度為65535個字節(jié)

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

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

最大值的計(jì)算

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

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

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

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

再大 1 的 21845 便無法容納了，系統(tǒng)便建議我們換一個數(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 編碼時，一個字符占 2 個字節(jié)，則 L 最大值為 65532 / 2 = 32766 .

如何選擇char和varchar

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

日期和時間類型

date

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

datetime

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

timestamp

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

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

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)是，插入時需要按類型規(guī)定的格式插入，才能被準(zhǔn)確識別。

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

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

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)

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

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)

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

set

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

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)則會被制止。

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)換成對應(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類型的查找

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

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

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

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

+--------+

| 科目 |

+--------+

| 語文 |

| 語文 |

+--------+

2 rows in set (0.00 sec)

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

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

+--------+

| 愛好 |

+--------+

| 跳 |

| 跳 |

+--------+

2 rows in set (0.00 sec)

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

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

該函數(shù)會在 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é)束了，如果這篇文章對你有用的話還請留下你的三連加關(guān)注。

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

http://yzkb.51969.com/

相關(guān)文章