整數(shù)類型的 UNSIGNED 屬性有什么用？

MySQL 中的整數(shù)類型可以使用可選的 UNSIGNED 屬性來表示不允許負(fù)值的無符號整數(shù)。使用 UNSIGNED 屬性可以將正整數(shù)的上限提高一倍，因?yàn)樗恍枰鎯ω?fù)數(shù)值。

例如， TINYINT UNSIGNED 類型的取值范圍是 0 ~ 255，而普通的 TINYINT 類型的值范圍是 -128 ~ 127。INT UNSIGNED 類型的取值范圍是 0 ~ 4,294,967,295，而普通的 INT 類型的值范圍是 -2,147,483,648 ~ 2,147,483,647。

對于從 0 開始遞增的 ID 列，使用 UNSIGNED 屬性可以非常適合，因?yàn)椴辉试S負(fù)值并且可以擁有更大的上限范圍，提供了更多的 ID 值可用。

char和varchar的區(qū)別

CHAR

• CHAR類型用于存儲固定長度字符串：MySQL總是根據(jù)定義的字符串長度分配足夠的空間。當(dāng)存儲CHAR值時(shí)，MySQL會刪除字符串中的末尾空格同時(shí)，CHAR值會根據(jù)需要采用空格進(jìn)行剩余空間填充，以方便比較和檢索。但正因?yàn)槠溟L度固定，所以會占據(jù)多余的空間，也是一種空間換時(shí)間的策略；

• CHAR適合存儲很短或長度近似的字符串。例如，CHAR非常適合存儲密碼的MD5值、定長的身份證等，因?yàn)檫@些是定長的值。

• 對于經(jīng)常變更的數(shù)據(jù)，CHAR也比VARCHAR更好，因?yàn)槎ㄩL的CHAR類型占用磁盤的存儲空間是連續(xù)分配的，不容易產(chǎn)生碎片。

• 對于非常短的列，CHAR比VARCHAR在存儲空間上也更有效率。例如用CHAR(1)來存儲只有Y和N的值，如果采用單字節(jié)字符集只需要一個字節(jié)，但是VARCHAR(1)卻需要兩個字節(jié)，因?yàn)檫€有一個記錄長度的額外字節(jié)。

VARCHAR：

• VARCHAR類型用于存儲可變長度字符串，是最常見的字符串?dāng)?shù)據(jù)類型。它比固定長度類型更節(jié)省空間，因?yàn)樗鼉H使用必要的空間(根據(jù)實(shí)際字符串的長度改變存儲空間)。

• VARCHAR需要使用1或2個額外字節(jié)記錄字符串的長度：如果列的最大長度小于或等于255字節(jié)，則只使用1個字節(jié)表示，否則使用2個字節(jié)。假設(shè)采用latinl字符集，一個VARCHAR(10)的列需要11個字節(jié)的存儲空間。VARCHAR(1000)的列則需要1002 個字節(jié)，因?yàn)樾枰?個字節(jié)存儲長度信息。

• VARCHAR節(jié)省了存儲空間，所以對性能也有幫助。但是，由于行是變長的，在UPDATE時(shí)可能使行變得比原來更長，這就導(dǎo)致需要做額外的工作。如果一個行占用的空間增長，并且在頁內(nèi)沒有更多的空間可以存儲，在這種情況下，不同的存儲引擎的處理方式是不一樣的。例如，MylSAM會將行拆成不同的片段存儲，InnoDB則需要分裂頁來使行可以放進(jìn)頁內(nèi)。

• 操作內(nèi)存的方式：對于varchar數(shù)據(jù)類型來說，硬盤上的存儲空間雖然都是根據(jù)字符串的實(shí)際長度來存儲空間的，但在內(nèi)存中是根據(jù)varchar類型定義的長度來分配占用的內(nèi)存空間的，而不是根據(jù)字符串的實(shí)際長度來分配的。顯然，這對于排序和臨時(shí)表會較大的性能影響。

VARCHAR(100)和 VARCHAR(10)的區(qū)別是什么？

VARCHAR(100)和 VARCHAR(10)都是變長類型，表示能存儲最多 100 個字符和 10 個字符。因此，VARCHAR (100) 可以滿足更大范圍的字符存儲需求，有更好的業(yè)務(wù)拓展性。而 VARCHAR(10)存儲超過 10 個字符時(shí)，就需要修改表結(jié)構(gòu)才可以。

雖說 VARCHAR(100)和 VARCHAR(10)能存儲的字符范圍不同，但二者存儲相同的字符串，所占用磁盤的存儲空間其實(shí)是一樣的，這也是很多人容易誤解的一點(diǎn)。

不過，VARCHAR(100) 會消耗更多的內(nèi)存。這是因?yàn)?VARCHAR 類型在內(nèi)存中操作時(shí)，通常會分配固定大小的內(nèi)存塊來保存值，即使用字符類型中定義的長度。例如在進(jìn)行排序的時(shí)候，VARCHAR(100)是按照 100 這個長度來進(jìn)行的，也就會消耗更多內(nèi)存。

DECIMAL 和 FLOAT/DOUBLE 的區(qū)別是什么？

DECIMAL 和 FLOAT 的區(qū)別是：DECIMAL 是定點(diǎn)數(shù)，F(xiàn)LOAT/DOUBLE 是浮點(diǎn)數(shù)。DECIMAL 可以存儲精確的小數(shù)值，F(xiàn)LOAT/DOUBLE 只能存儲近似的小數(shù)值。

DECIMAL 用于存儲具有精度要求的小數(shù)，例如與貨幣相關(guān)的數(shù)據(jù)，可以避免浮點(diǎn)數(shù)帶來的精度損失。

在 Java 中，MySQL 的 DECIMAL 類型對應(yīng)的是 Java 類 java.math.BigDecimal。

int(10)和char(10)的區(qū)別？

int(10)中的10表示的是顯示數(shù)據(jù)的長度，而char(10)表示的是存儲數(shù)據(jù)的長度。

為什么不推薦使用 TEXT 和 BLOB？

數(shù)據(jù)庫規(guī)范通常不推薦使用 BLOB 和 TEXT 類型，這兩種類型具有一些缺點(diǎn)和限制，例如：

• 不能有默認(rèn)值。

• 在使用臨時(shí)表時(shí)無法使用內(nèi)存臨時(shí)表，只能在磁盤上創(chuàng)建臨時(shí)表（《高性能 MySQL》書中有提到）。

• 檢索效率較低。

• 不能直接創(chuàng)建索引，需要指定前綴長度。

• 可能會消耗大量的網(wǎng)絡(luò)和 IO 帶寬。

• 可能導(dǎo)致表上的 DML 操作變慢。

• ……

DATETIME 和 TIMESTAMP 的區(qū)別是什么？

DATETIME 類型沒有時(shí)區(qū)信息，TIMESTAMP 和時(shí)區(qū)有關(guān)。

TIMESTAMP 只需要使用 4 個字節(jié)的存儲空間，但是 DATETIME 需要耗費(fèi) 8 個字節(jié)的存儲空間。但是，這樣同樣造成了一個問題，Timestamp 表示的時(shí)間范圍更小。

• DATETIME：1000-01-01 00:00:00 ~ 9999-12-31 23:59:59

• Timestamp：1970-01-01 00:00:01 ~ 2037-12-31 23:59:59

Boolean 類型如何表示？

MySQL 中沒有專門的布爾類型，而是用 TINYINT(1) 類型來表示布爾值。TINYINT(1) 類型可以存儲 0 或 1，分別對應(yīng) false 或 true。

為什么不建議使用null作為默認(rèn)值

Mysql不建議用Null作為列默認(rèn)值不是因?yàn)椴荒苁褂盟饕?，而是因?yàn)椋?/p>

• 索引列存在 NULL 就會導(dǎo)致優(yōu)化器在做索引選擇的時(shí)候更加復(fù)雜，更加難以優(yōu)化。比如進(jìn)行索引統(tǒng)計(jì)時(shí)，count(1),max(),min() 會省略值為NULL 的行。

• NULL 值是一個沒意義的值，但是它會占用物理空間，所以會帶來的存儲空間的問題，因?yàn)?InnoDB 存儲記錄的時(shí)候，如果表中存在允許為 NULL 的字段，那么行格式 (opens new window)中至少會用 1 字節(jié)空間存儲 NULL 值列表。建議用""或默認(rèn)值0來代替NULL

不建議使用null作為默認(rèn)值，并且建議必須設(shè)置默認(rèn)值，原因如下：

• 既然都不可為空了，那就必須要有默認(rèn)值，否則不插入這列的話，就會報(bào)錯；

• 數(shù)據(jù)庫不應(yīng)該是用來查問題的，不能靠mysql報(bào)錯來告知業(yè)務(wù)有問題，該不該插入應(yīng)該由業(yè)務(wù)說了算；

• 對于DBA來說，允許使用null是沒有規(guī)范的，因?yàn)椴煌娜瞬煌挠梅ā?/p>

但像合同生效時(shí)間、獲獎時(shí)間 等這種不可控字段，是可以不設(shè)置默認(rèn)值的，但同樣需要not null

為什么禁止使用外鍵

• 外鍵會降低數(shù)據(jù)庫的性能。在MySQL中，外鍵會自動加上索引，這會使得對該表的查詢等操作變得緩慢，尤其是在大型數(shù)據(jù)表中。

• 外鍵也會限制了表結(jié)構(gòu)的調(diào)整和更改。在實(shí)際應(yīng)用中，表結(jié)構(gòu)經(jīng)常需要進(jìn)行更改，而如果表之間使用了外鍵約束，這些更改可能會非常難以實(shí)現(xiàn)。因?yàn)楦囊粋€表的結(jié)構(gòu)，需要涉及到所有以其為父表的子表，這會導(dǎo)致長時(shí)間鎖定整個數(shù)據(jù)庫表，甚至可能會導(dǎo)致數(shù)據(jù)丟失。

• 在MySQL中，外鍵約束可能還會引發(fā)死鎖問題。當(dāng)想要對多個表中的數(shù)據(jù)進(jìn)行插入、更新、刪除操作時(shí)，由于外鍵約束的存在，可能會導(dǎo)致死鎖，需要等待其他事務(wù)釋放鎖。

• MySQL中使用外鍵還會增加開發(fā)難度。開發(fā)人員需要處理數(shù)據(jù)在表之間的關(guān)系，而這樣的處理需要花費(fèi)更多的時(shí)間和精力，以及對數(shù)據(jù)庫的深入理解。同時(shí)，外鍵也會增加代碼的復(fù)雜度，使得SQL語句變得難以理解和調(diào)試。

在阿里巴巴開發(fā)手冊中也有提到，傳送門

使用自增主鍵有什么好處？

自增主鍵可以讓主鍵索引盡量地保持遞增順序插入，避免了頁分裂，因此索引更緊湊，在查詢的時(shí)候，效率也就更高。

自增主鍵保存在什么地方？

不同的引擎對于自增值的保存策略不同：

• MyISAM引擎的自增值保存在數(shù)據(jù)文件中。

• 在MySQL8.0以前，InnoDB引擎的自增值是存在內(nèi)存中。MySQL重啟之后內(nèi)存中的這個值就丟失了，每次重啟后第一次打開表的時(shí)候，會找自增值的最大值max(id)，然后將最大值加1作為這個表的自增值；MySQL8.0版本會將自增值的變更記錄在redo log中，重啟時(shí)依靠redo log恢復(fù)。

自增主鍵一定是連續(xù)的嗎？

不一定，有幾種情況會導(dǎo)致自增主鍵不連續(xù)。

1、唯一鍵沖突導(dǎo)致自增主鍵不連續(xù)。當(dāng)我們向一個自增主鍵的InnoDB表中插入數(shù)據(jù)的時(shí)候，如果違反表中定義的唯一索引的唯一約束，會導(dǎo)致插入數(shù)據(jù)失敗。此時(shí)表的自增主鍵的鍵值是會向后加1滾動的。下次再次插入數(shù)據(jù)的時(shí)候，就不能再使用上次因插入數(shù)據(jù)失敗而滾動生成的鍵值了，必須使用新滾動生成的鍵值。

2、事務(wù)回滾導(dǎo)致自增主鍵不連續(xù)。當(dāng)我們向一個自增主鍵的InnoDB表中插入數(shù)據(jù)的時(shí)候，如果顯式開啟了事務(wù)，然后因?yàn)槟撤N原因最后回滾了事務(wù)，此時(shí)表的自增值也會發(fā)生滾動，而接下里新插入的數(shù)據(jù)，也將不能使用滾動過的自增值，而是需要重新申請一個新的自增值。

3、批量插入導(dǎo)致自增值不連續(xù)。MySQL有一個批量申請自增id的策略：

• 語句執(zhí)行過程中，第一次申請自增id，分配1個自增id

• 1個用完以后，第二次申請，會分配2個自增id

• 2個用完以后，第三次申請，會分配4個自增id

• 依次類推，每次申請都是上一次的兩倍（最后一次申請不一定全部使用）

如果下一個事務(wù)再次插入數(shù)據(jù)的時(shí)候，則會基于上一個事務(wù)申請后的自增值基礎(chǔ)上再申請。此時(shí)就出現(xiàn)自增值不連續(xù)的情況出現(xiàn)。

4、自增步長不是1，也會導(dǎo)致自增主鍵不連續(xù)。

InnoDB的自增值為什么不能回收利用？

主要為了提升插入數(shù)據(jù)的效率和并行度。

假設(shè)有兩個并行執(zhí)行的事務(wù)，在申請自增值的時(shí)候，為了避免兩個事務(wù)申請到相同的自增 id，肯定要加鎖，然后順序申請。

假設(shè)事務(wù) A 申請到了 id=2， 事務(wù) B 申請到 id=3，那么這時(shí)候表 t 的自增值是 4，之后繼續(xù)執(zhí)行。

事務(wù) B 正確提交了，但事務(wù) A 出現(xiàn)了唯一鍵沖突。

如果允許事務(wù) A 把自增 id 回退，也就是把表 t 的當(dāng)前自增值改回 2，那么就會出現(xiàn)這樣的情況：表里面已經(jīng)有 id=3 的行，而當(dāng)前的自增 id 值是 2。

接下來，繼續(xù)執(zhí)行的其他事務(wù)就會申請到 id=2，然后再申請到 id=3。這時(shí)，就會出現(xiàn)插入語句報(bào)錯“主鍵沖突”。

而為了解決這個主鍵沖突，有兩種方法：

• 每次申請 id 之前，先判斷表里面是否已經(jīng)存在這個 id。如果存在，就跳過這個 id。但是，這個方法的成本很高。因?yàn)椋緛砩暾?id 是一個很快的操作，現(xiàn)在還要再去主鍵索引樹上判斷 id 是否存在。

• 把自增 id 的鎖范圍擴(kuò)大，必須等到一個事務(wù)執(zhí)行完成并提交，下一個事務(wù)才能再申請自增 id。這個方法的問題，就是鎖的粒度太大，系統(tǒng)并發(fā)能力大大下降。

可見，這兩個方法都會導(dǎo)致性能問題。

因此，InnoDB 放棄了“允許自增 id 回退”這個設(shè)計(jì)，語句執(zhí)行失敗也不回退自增 id。

utf8 、utf8mb3和 utf8mb4的區(qū)別

utf8mb3：只支持最長三個字節(jié)的BMP（Basic Multilingual Plane，基本多文種平面）字符（不支持補(bǔ)充字符）。

utf8mb4：mb4即 most bytes 4，即最多使用4個字節(jié)來表示完整的UTF-8，具有以下特征：

• 支持BMP和補(bǔ)充字符。

• 每個多字節(jié)字符最多需要四個字節(jié)。

utf8mb4是utf8的超集并完全兼容它，是MySQL 在 5.5.3 版本之后增加的一個新的字符集，能夠用四個字節(jié)存儲更多的字符，幾乎包含了世界上所有能看到見的語言字符。

• 差異比較

如何選擇？一句話就是，根據(jù)具體的業(yè)務(wù)需求和實(shí)際情況，選擇最合適的字符集。

轉(zhuǎn)自https://www.cnblogs.com/seven97-top/p/18537862