一、數(shù)據(jù)類型的分類

• 數(shù)值類型

二、tinyint類型

2.1 創(chuàng)建有符號(hào)數(shù)值

默認(rèn)是有符號(hào)的。取值范圍[-128,127]：

mysql> create table if not exists t1(-> num tinyint-> );

插入tinyint類型數(shù)值范圍內(nèi)的數(shù)據(jù)是可以的：

mysql> insert into t1 values (-128);
Query OK, 1 row affected (0.01 sec)mysql> insert into t1 values (127);
Query OK, 1 row affected (0.01 sec)

超出范圍的數(shù)據(jù)會(huì)插入失敗：

mysql> insert into t1 values (128);
ERROR 1264 (22003): Out of range value for column 'num' at row 1
mysql> insert into t1 values (-129);
ERROR 1264 (22003): Out of range value for column 'num' at row 1

2.2 創(chuàng)建無符號(hào)數(shù)值

用tinyint unsigned數(shù)據(jù)類型創(chuàng)建表t2:

mysql> create table if not exists t2( num tinyint unsigned );
Query OK, 0 rows affected (0.02 sec)

我們知道tinyint unsigned類型的范圍是【0 ~ 255】：

mysql> insert into t2 values (0);
Query OK, 1 row affected (0.01 sec)mysql> insert into t2 values (255);
Query OK, 1 row affected (0.00 sec)mysql> insert into t2 values (-1);
ERROR 1264 (22003): Out of range value for column 'num' at row 1
mysql> insert into t2 values (256);
ERROR 1264 (22003): Out of range value for column 'num' at row 1

如果我們向MySQL特定的類型中插入不合法的數(shù)據(jù)，MySQL一般都是直接攔截，不讓我們做對應(yīng)的操作！
反過來，我們?nèi)绻呀?jīng)有數(shù)據(jù)被插入到MySql中了，那么一定是合法的。
所以數(shù)據(jù)類型本身就是一種約束。

MySql表中建立屬性列：列名稱 類型在后

num tinyint unsigned;

三、bit類型

bit[(M)] :位字段類型。M表示有幾位，范圍從1到64位。如果M被忽略，默認(rèn)為1。

mysql> create table if not exists t3( -> num1 bit(1),-> num2 bit(64)-> );
Query OK, 0 rows affected (0.03 sec)

對于num1只能插入0或1。

mysql> insert into t3 (num1, num2) values (1, 256);
Query OK, 1 row affected (0.01 sec)mysql> insert into t3 (num1, num2) values (0, 1024);
Query OK, 1 row affected (0.01 sec)

位類型顯示的是ASCII碼，如果想看到：可以使用十六進(jìn)制打印。

select hex(num1),hex(num2) from t3;

驗(yàn)證一下：
如果插入’a’或者97:

mysql> insert into t3 (num1, num2) values(1, 97);
Query OK, 1 row affected (0.01 sec)mysql> insert into t3 (num1, num2) values(1, 'a');
Query OK, 1 row affected (0.00 sec)

所以bit字段在顯示時(shí)，是按照ASCII碼對應(yīng)的值顯示。

三、浮點(diǎn)類型

3.1 float

float[(m, d)] [unsigned] : M指定顯示長度，d指定小數(shù)位數(shù)，占用空間4個(gè)字節(jié)

例如：
float(5,2) 表示的范圍是 -999.99 ~ 999.99
float(5,2) unsigned 表示的范圍 0 ~ 999.99

創(chuàng)建表：

mysql> create table if not exists t5(-> num float(4, 3)-> );
Query OK, 0 rows affected (0.04 sec)

插入數(shù)據(jù)：

mysql> insert into t5 (num) values (9.999);
Query OK, 1 row affected (0.00 sec)mysql> insert into t5 values (-9.999);
Query OK, 1 row affected (0.00 sec)

插入的如果是 1.2345，會(huì)變成1.234（超過5進(jìn)1）

mysql> insert into t5 values (1.2345);
Query OK, 1 row affected (0.01 sec)mysql> insert into t5 values (1.2346);
Query OK, 1 row affected (0.01 sec)

但是如果進(jìn)位以后超過范圍了就也無法插入成功。

mysql> insert into t5 values (9.9996);
ERROR 1264 (22003): Out of range value for column 'num' at row 1

如果是無符號(hào)浮點(diǎn)數(shù)就不能插入負(fù)數(shù)了，上限不變。

3.2 decimal類型

decimal(m, d) [unsigned] : M指定顯示的總長度（整數(shù)+小數(shù)），d指定小數(shù)位數(shù)。

decimal和float很像，表示的數(shù)據(jù)范圍一樣，但是精度不一樣。

創(chuàng)建表：

mysql> create table t7(-> num1 float(10, 8),-> num2 decimal(10, 8)-> );
Query OK, 0 rows affected (0.03 sec)

插入數(shù)據(jù)：

mysql> insert into t7 (num1, num2) values (10.0, 99.99);
Query OK, 1 row affected (0.01 sec)mysql> insert into t7 (num1, num2) values (23.12345612, 23.12345612);
Query OK, 1 row affected (0.00 sec)

可以看到float類型存在精度丟失，decimal存什么就是什么。（精度高）

float在精度過大會(huì)做一些動(dòng)作，而decimal不會(huì)。
float表示的精度大約是7位。decimal整數(shù)最大位數(shù)m為65。支持小數(shù)最大位數(shù)d是30。如果d被省略，默認(rèn)為0.如果m被省略，默認(rèn)是10。

建議：如果希望小數(shù)的精度高，推薦使用decimal或double而不是float。

四、字符串類型

4.1 char類型

char(L): 固定長度字符串，L是可以存儲(chǔ)的長度，單位為字符（注意是字符，不是字節(jié)），最大長度值可以為255

創(chuàng)建表：

mysql> create table t6(-> num char(2)-> );
Query OK, 0 rows affected (0.06 sec)

插入數(shù)據(jù)：

mysql> insert into t6 values ('a');
Query OK, 1 row affected (0.00 sec)mysql> insert into t6 values ('ab');
Query OK, 1 row affected (0.00 sec)mysql> insert into t6 values ('abc');
ERROR 1406 (22001): Data too long for column 'num' at row 1

如果插入漢字：對于gbk編碼一個(gè)占用2個(gè)字節(jié),utf8編碼一個(gè)漢字占用3個(gè)字節(jié)

mysql> insert into t6 values ('哈');
Query OK, 1 row affected (0.00 sec)mysql> insert into t6 values ('哈哈');
Query OK, 1 row affected (0.00 sec)mysql> insert into t6 values ('哈哈哈');
ERROR 1406 (22001): Data too long for column 'num' at row 1

mysql的字符代表的是符號(hào)，所以能夠插入成功。

4.2 varchar類型

varchar(L): 可變長度字符串，L表示字符長度，最大長度65535個(gè)字節(jié)

關(guān)于varchar(L),L到底是多大，這個(gè)len值，和表的編碼密切相關(guān)：
varchar長度可以指定為0到65535之間的值，但是有1 - 3 個(gè)字節(jié)用于記錄數(shù)據(jù)大小，所以說有效字節(jié)數(shù)是65532。(如果表中僅有一個(gè)varchar字段，有效字節(jié)數(shù)是65532，如果還有其他的字段，那么varchar的實(shí)際有效字節(jié)將會(huì)比65532低一點(diǎn))
當(dāng)我們的表的編碼是utf8時(shí)，varchar(n)的參數(shù)n最大值是65532/3=21844[因?yàn)閡tf中，一個(gè)字符占用3個(gè)字節(jié)]，如果編碼是gbk，varchar(n)的參數(shù)n最大是65532/2=32766（因?yàn)間bk中，一個(gè)字符占用2字節(jié)）。

創(chuàng)建表：

mysql> create table t4(-> id int,-> name varchar(6)-> );
Query OK, 0 rows affected (0.03 sec)

插入數(shù)據(jù)跟char類型是一樣的。

char和varchar的區(qū)別：

char是固定長度字符串，類似C/C++中的數(shù)組的概念，例如char(6)表示開辟6個(gè)字符的大小的空間，沒用完就是浪費(fèi)。
varchar是可變長度的字符串，例如varchar(6)表示最大長度為6個(gè)字符，如果用戶僅使用了一個(gè)字符，那么varchar只會(huì)分配出一個(gè)字符的空間+額外1個(gè)字節(jié)（記錄數(shù)據(jù)大小）用于存儲(chǔ)。

五、日期和時(shí)間類型

常用的日期有如下三個(gè)：

date :日期 ‘yyyy-mm-dd’ ，占用三字節(jié)
datetime 時(shí)間日期格式 ‘yyyy-mm-dd HH:ii:ss’ 表示范圍從 1000 到 9999 ，占用八字節(jié)
timestamp ：時(shí)間戳，從1970年開始的 yyyy-mm-dd HH:ii:ss 格式和 datetime 完全一致，占用四字節(jié)

創(chuàng)建表：

mysql> create table t8(-> t1 date,-> t2 datetime,-> t3 timestamp-> );
Query OK, 0 rows affected (0.04 sec)

對于時(shí)間戳timestamp:創(chuàng)建表結(jié)構(gòu)，插入數(shù)據(jù)是tinmestamp會(huì)自動(dòng)更新。所以不需要更改

mysql> insert into t8 (t1, t2) values ('2002-07-31', '2023-07-31 08:00:00');
Query OK, 1 row affected (0.00 sec)

此時(shí)更新t1:

mysql> update t8 set t1='2003-07-31';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0mysql> select * from t8;
+------------+---------------------+---------------------+
| t1         | t2                  | t3                  |
+------------+---------------------+---------------------+
| 2003-07-31 | 2023-07-31 08:00:00 | 2023-08-03 17:17:45 |
+------------+---------------------+---------------------+
1 row in set (0.00 sec)

t1和t3都會(huì)被更新，這也說明了時(shí)間戳timestamp會(huì)自動(dòng)更新,意義在于記住時(shí)間的更新。

六、枚舉和集合類型

6.1 enum的枚舉值和set的位圖結(jié)構(gòu)

創(chuàng)建帶有enum和set類型的表votes：

mysql> create table if not exists votes(-> username varchar(10),-> gander enum('男', '女'),-> hobby set('籃球', '足球', '跑步')-> );

插入數(shù)據(jù)：

mysql> insert into votes values ('張三', '男', '跑步');
Query OK, 1 row affected (0.01 sec)mysql> insert into votes values ('李四', '女', '籃球');
Query OK, 1 row affected (0.01 sec)mysql> insert into votes values ('趙五', 'a', '籃球');
ERROR 1265 (01000): Data truncated for column 'gander' at row 1

可以看到對于gander我們只能插入男或者女，不能插入其他字符。
但是可以插入1和2，也就是說枚舉類型可以寫常量也可以寫常量的下標(biāo)。

對于set也可輸入數(shù)字插入，數(shù)字代表位圖：

mysql> insert into votes values ('趙五', '男', '羽毛球');
ERROR 1265 (01000): Data truncated for column 'hobby' at row 1mysql> insert into votes values ('趙五', '男', '跑步,籃球')
Query OK, 1 row affected (0.01 sec)mysql> insert into votes values ('趙五', '男', '跑步,籃球,足球');
Query OK, 1 row affected (0.01 sec)

mysql> insert into votes (username) values ('小明');
Query OK, 1 row affected (0.02 sec)mysql> insert into votes values ('小明', 1, 0);
Query OK, 1 row affected (0.00 sec)mysql> insert into votes values ('小明', 1, 1);
Query OK, 1 row affected (0.00 sec)

NULL表示什么都沒有，而' '表示有東西但是是一個(gè)空串。

當(dāng)然set是一個(gè)位圖結(jié)構(gòu)，如果是3，那么就是1 | 2（籃球，足球）。

enum：在所有枚舉中選擇一種進(jìn)行插入。也可以直接使用枚舉值，枚舉值從1開始。
set：在集合中選擇一種或多種存在的選項(xiàng)進(jìn)行數(shù)據(jù)的插入。也可使用位圖的形式進(jìn)行數(shù)據(jù)插入。

6.2 查詢集合find_in_set

如果我們想要查詢愛好是籃球的：

mysql> select * from votes where hobby='籃球';
+----------+--------+--------+
| username | gander | hobby  |
+----------+--------+--------+
| 李四     | 女     | 籃球   |
| 小明     | 男     | 籃球   |
+----------+--------+--------+
2 rows in set (0.00 sec)

可以看到是精確篩選
下面介紹一個(gè)函數(shù)find_in_set：

mysql> select find_in_set('a', 'a,b,c');
+---------------------------+
| find_in_set('a', 'a,b,c') |
+---------------------------+
|                         1 |
+---------------------------+
1 row in set (0.00 sec)

這里的1表示true，說明’a’在集合’a,b,c’中。

這里只能查找一個(gè)元素是否在對應(yīng)的集合中。

mysql> select find_in_set('d', 'a,b,c');
+---------------------------+
| find_in_set('d', 'a,b,c') |
+---------------------------+
|                         0 |
+---------------------------+
1 row in set (0.00 sec)mysql> select find_in_set('a,b', 'a,b,c');
+-----------------------------+
| find_in_set('a,b', 'a,b,c') |
+-----------------------------+
|                           0 |
+-----------------------------+
1 row in set (0.00 sec)

當(dāng)一個(gè)要查找的字段位于集合中時(shí)，find_in_set函數(shù)會(huì)返回其下標(biāo)（非0），反之返回0。所以我們就可以在集合中篩選出愛好帶有“籃球”的人。

mysql> select * from votes where find_in_set ('籃球', hobby);
+----------+--------+----------------------+
| username | gander | hobby                |
+----------+--------+----------------------+
| 李四     | 女     | 籃球                 |
| 趙五     | 男     | 籃球,跑步            |
| 趙五     | 男     | 籃球,足球,跑步       |
| 小明     | 男     | 籃球                 |
| 小明     | 男     | 籃球,足球            |
+----------+--------+----------------------+
5 rows in set (0.00 sec)

也可以在集合中篩選出愛好既有“籃球”，又有“足球”人。使用and邏輯與進(jìn)行篩選：

mysql> select * from votes where find_in_set ('籃球', hobby) and find_in_set('足球', hobby); 
+----------+--------+----------------------+
| username | gander | hobby                |
+----------+--------+----------------------+
| 趙五     | 男     | 籃球,足球,跑步       |
| 小明     | 男     | 籃球,足球            |
+----------+--------+----------------------+
2 rows in set (0.00 sec)