面試題1：MySQL有哪些數(shù)據(jù)類型？

MySQL支持多種類型，大致可以分為三類：數(shù)值、日期/時(shí)間和字符串(字符)類型。

數(shù)值類型

MySQL支持所有標(biāo)準(zhǔn)SQL數(shù)值數(shù)據(jù)類型。

這些類型包括嚴(yán)格數(shù)值數(shù)據(jù)類型(INTEGER、SMALLINT、DECIMAL和NUMERIC)，以及近似數(shù)值數(shù)據(jù)類型(FLOAT、REAL和DOUBLE PRECISION)。

關(guān)鍵字INT是INTEGER的同義詞，關(guān)鍵字DEC是DECIMAL的同義詞。

BIT數(shù)據(jù)類型保存位字段值，并且支持MyISAM、MEMORY、InnoDB和BDB表。

作為SQL標(biāo)準(zhǔn)的擴(kuò)展，MySQL也支持整數(shù)類型TINYINT、SMALLINT、MEDIUMINT、INT和BIGINT。分別使用8、16、24、32、64位存儲空間，

他們存儲值的范圍為：-2(n-1) ~ 2(n-1)-1，其中N是存儲空間的位數(shù)。

下表顯示了需要的每個整數(shù)類型的存儲和范圍。

日期和時(shí)間類型

表示時(shí)間值的日期和時(shí)間類型為DATETIME、DATE、TIMESTAMP、TIME和YEAR。

每個時(shí)間類型有一個有效值范圍和一個"零"值，當(dāng)指定不合法的MySQL不能表示的值時(shí)使用"零"值。

TIMESTAMP類型有專有的自動更新特性，將在后面描述。

字符串類型

字符串類型指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET。該節(jié)描述了這些類型如何工作以及如何在查詢中使用這些類型。

注意： char(n) 和 varchar(n) 中括號中 n 代表字符的個數(shù)，并不代表字節(jié)個數(shù)，比如 CHAR(30) 就可以存儲 30 個字符。

CHAR 和 VARCHAR 類型類似，但它們保存和檢索的方式不同。它們的最大長度和是否尾部空格被保留等方面也不同。在存儲或檢索過程中不進(jìn)行大小寫轉(zhuǎn)換。

BINARY 和 VARBINARY 類似于 CHAR 和 VARCHAR，不同的是它們包含二進(jìn)制字符串而不要非二進(jìn)制字符串。也就是說，它們包含字節(jié)字符串而不是字符字符串。這說明它們沒有字符集，并且排序和比較基于列值字節(jié)的數(shù)值值。

BLOB 是一個二進(jìn)制大對象，可以容納可變數(shù)量的數(shù)據(jù)。有 4 種 BLOB 類型：TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB。它們區(qū)別在于可容納存儲范圍不同。

有 4 種 TEXT 類型：TINYTEXT、TEXT、MEDIUMTEXT 和 LONGTEXT。對應(yīng)的這 4 種 BLOB 類型，可存儲的最大長度不同，可根據(jù)實(shí)際情況選擇。

追問1：char 和 varchar 的區(qū)別是什么？

1、固定長度 & 可變長度

VARCHAR

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

有一種情況例外，如果MySQL表使用ROW_FORMAT=FIXED創(chuàng)建的話，每一行都會使用定長存儲。

CHAR

CHAR類型用于存儲固定長度字符串：MySQL總是根據(jù)定義的字符串長度分配足夠的空間。當(dāng)存儲CHAR值時(shí)，MySQL會刪除字符串中的末尾空格(在MySQL 4.1和更老版本中VARCHAR 也是這樣實(shí)現(xiàn)的——也就是說這些版本中CHAR和VARCHAR在邏輯上是一樣的，區(qū)別只是在存儲格式上)。

同時(shí)，CHAR值會根據(jù)需要采用空格進(jìn)行剩余空間填充，以方便比較和檢索。但正因?yàn)槠溟L度固定，所以會占據(jù)多余的空間，也是一種空間換時(shí)間的策略；

2、存儲方式

• VARCHAR

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)。

• CHAR

CHAR適合存儲很短或長度近似的字符串。例如，CHAR非常適合存儲密碼的MD5值，因?yàn)檫@是一個定長的值。對于經(jīng)常變更的數(shù)據(jù)，CHAR也比VARCHAR更好，因?yàn)槎ㄩL的CHAR類型不容易產(chǎn)生碎片。對于非常短的列，CHAR比VARCHAR在存儲空間上也更有效率。例如用CHAR(1)來存儲只有Y和N的值，如果采用單字節(jié)字符集只需要一個字節(jié)，但是VARCHAR(1)卻需要兩個字節(jié)，因?yàn)檫€有一個記錄長度的額外字節(jié)。

3、存儲容量

• CHAR

對于char類型來說，最多只能存放的字符個數(shù)為255，和編碼無關(guān)，任何編碼最大容量都是255。

• VARCHAR

MySQL行默認(rèn)最大65535字節(jié)，是所有列共享（相加）的，所以VARCHAR的最大值受此限制。

表中只有單列字段情況下，varchar一般最多能存放(65535 - 3)個字節(jié)，varchar的最大有效長度通過最大行數(shù)據(jù)長度和使用的字符集來確定，通常的最大長度是65532個字符（當(dāng)字符串中的字符都只占1個字節(jié)時(shí)，能達(dá)到65532個字符）；

為什么是65532個字符？算法如下（有余數(shù)時(shí)向下取整）：

最大長度(字符數(shù)) = （行存儲最大字節(jié)數(shù) - NULL標(biāo)識列占用字節(jié)數(shù) - 長度標(biāo)識字節(jié)數(shù)） / 字符集單字符最大字節(jié)數(shù)

NULL標(biāo)識列占用字節(jié)數(shù)：允許NULL時(shí)，占一字節(jié)

長度標(biāo)識字節(jié)數(shù)：記錄長度的標(biāo)識，長度小于等于255（28）時(shí)，占1字節(jié)；小于65535時(shí)（216）,占2字節(jié)

VARCHAR類型在4.1和5.0版本發(fā)生了很大的變化，使得情況更加復(fù)雜。從MySQL 4.1開始，每個字符串列可以定義自己的字符集和排序規(guī)則。這些東西會很大程度上影響性能。

• 4.0版本及以下，MySQL中varchar長度是按字節(jié)展示，如varchar(20)，指的是20字節(jié)；
• 5.0版本及以上，MySQL中varchar長度是按字符展示。如varchar(20)，指的是20字符。

當(dāng)然，行總長度還是65535字節(jié)，而字符和字節(jié)的換算，則與編碼方式有關(guān)，不同的字符所占的字節(jié)是不同的。編碼劃分如下：

GBK編碼：一個英文字符占一個字節(jié)，中文2字節(jié)，單字符最大可占用2個字節(jié)。

UTF-8編碼：一個英文字符占一個字節(jié)，中文3字節(jié)，單字符最大可占用3個字節(jié)。

utf8mb4編碼：一個英文字符占一個字節(jié)，中文3字節(jié)，單字符最大占4個字節(jié)（如emoji表情4字節(jié)）。

假設(shè)當(dāng)前還有6字節(jié)可以存放字符，按單字符占用最大字節(jié)數(shù)來算，可以存放3個GBK、或2個utf8、或1個utf8mb4。

4、思考：既然VARCHAR長度可變，那我要不要定到最大?

沒錯，相信你已經(jīng)有答案了，別這么干！

就像使用VARCHAR(5)和VARCHAR(200)存儲 '陳哈哈'的磁盤空間開銷是一樣的。那么使用更短的列有什么優(yōu)勢呢？

事實(shí)證明有很大的優(yōu)勢。更長的列會消耗更多的內(nèi)存，因?yàn)镸ySQL通常會分配固定大小的內(nèi)存塊來保存內(nèi)部值。

當(dāng)然，在沒拿到存儲引擎存儲的數(shù)據(jù)之前，并不會知道我這一行拿出來的數(shù)據(jù)到底有多長，可能長度只有1，可能長度是500，那怎么辦呢？那就只能先把最大空間分配好了，避免放不下的問題發(fā)生，這樣實(shí)際上對于真實(shí)數(shù)據(jù)較短的varchar確實(shí)會造成空間的浪費(fèi)。

舉例：我向數(shù)據(jù)類型為：varchar（1000）的列插入了1024行數(shù)據(jù)，但是每個只存一個字符，那么這1024行真實(shí)數(shù)據(jù)量其實(shí)只有1K，但是我卻需要約1M的內(nèi)存去適應(yīng)他。所以最好的策略是只分配真正需要的空間。

5、在SQL中需要注意的點(diǎn)

下面通過一個具體的示例來說明CHAR和VARCHAR類型存儲時(shí)的區(qū)別。我們創(chuàng)建一張同時(shí)存在CHAR(10)字段、VARCHAR(10)字段的表，并且往里面插入一些值來做對比驗(yàn)證：

-- 建表語句
CREATE TABLE `str_table` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `str_char` char(10) DEFAULT NULL,
  `str_varchar` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4;

分別插入一些字符串前面和后面都有空格的示例

-- 插入測試數(shù)據(jù)
INSERT INTO `str_table` (`id`, `str_char`, `str_varchar`) 
VALUES 
(null, '陳哈哈', '陳哈哈'),
(null, '  陳哈哈', '  陳哈哈'),
(null, '陳哈哈  ', '陳哈哈  ');

測試數(shù)據(jù)查詢語句如下，通過拼接能更好的看出對比效果：

-- 測試數(shù)據(jù)查詢
select id,concat("|",str_char,"|") as `char`,concat("|",str_varchar,"|") as `varchar` from str_table;

mysql> select id,concat("|",str_char,"|") as `char`,concat("|",str_varchar,"|") as `varchar` from str_table;
+----+---------------+---------------+
| id | char          | varchar       |
+----+---------------+---------------+
|  6 | |陳哈哈|      | |陳哈哈|      |
|  7 | |  陳哈哈|    | |  陳哈哈|    |
|  8 | |陳哈哈|      | |陳哈哈  |    |
+----+---------------+---------------+
3 rows in set (0.00 sec)

當(dāng)檢索這些值的時(shí)候，會發(fā)現(xiàn)id=8行中，char類型的"陳哈哈 "末尾的空格被截?cái)嗔耍鳹ARCHAR(10)字段存儲相同的值時(shí)，末尾的空格被保留了。另外，id=7行的數(shù)據(jù)前面空格都被保留了。

可見，CHAR會默認(rèn)切掉字符串末尾的空格，如果需要保留末尾的空格，記得用varchar類型！

追問2：varchar(50)、char(50)中50的涵義是什么？

varchar(50)

VARCHAR列中的值為可變長字符串。長度可以指定為0到65535之間的值。VARCHAR的最大有效長度由最大行大小和使用的字符集確定。

MySQL 4.1之前：VARCHAR(50)的“50”指的是50字節(jié)（bytes）。如果存放UTF8漢字時(shí)，那么最多只能存放16個（每個漢字3字節(jié)）。 MySQL 4.1版本及以后：VARCHAR(50)的“50”指的是50字符（character），無論存放的是數(shù)字、字母還是UTF8漢字（每個漢字3字節(jié)），都可以存放50個。

char(50)

CHAR和VARCHAR類型聲明的長度表示保存的最大字符數(shù)。例如，CHAR(30)可以占用30個字符。

對于MyISAM表，推薦CHAR類型；對于InnoDB表，推薦VARCHAR類型。

另外，在進(jìn)行檢索的時(shí)候，若列值的尾部含有空格，則CHAR列會刪除其尾部的空格，而VARCHAR則會保留空格。

追問3：那int(10)中10的涵義呢？int(1)和int(20)有什么不同？

int的范圍

有符號的整型范圍是-2147483648~2147483647 （-2^32 ~ 2^32）無符號的整型范圍是0~4294967295

int(10)的意思

int(10)的意思是假設(shè)有一個變量名為id，它的能顯示的寬度能顯示10位。在使用id時(shí)，假如我給id輸入10，那么mysql會默認(rèn)給你存儲0000000010。

當(dāng)你輸入的數(shù)據(jù)不足10位時(shí)，會自動幫你補(bǔ)全位數(shù)。

假如我設(shè)計(jì)的id字段是int(20)，那么我輸入id = 10時(shí)，mysql會自動補(bǔ)全18個0（00000000000000000010），補(bǔ)到20位為止。

引用一下《高性能MySQL》的說明：

面試題2：MySQL 的內(nèi)連接、左連接、右連接有什么區(qū)別？

摘取一下SQL大腿群里同學(xué)的回復(fù)吧：

例：ab兩表關(guān)聯(lián)，a表數(shù)據(jù)有，b表關(guān)聯(lián)數(shù)據(jù)沒有，a表的就不要顯示或顯示null的問題；幾個連接怎么使用，總結(jié)一下：

左鏈接取A集合，右鏈接取B集合，full join取并集，inner join 取交集。

面試題3：MySQL的隱式轉(zhuǎn)換問題遇到過么？說說你的理解。

1、SQL語句中隱式轉(zhuǎn)換的坑

先看一下官方的隱試轉(zhuǎn)換說明：

翻譯成人話：

• 兩個參數(shù)至少有一個是 NULL 時(shí)，比較的結(jié)果也是 NULL，例外是使用 <=> 對兩個 NULL 做比較時(shí)會返回 1，這兩種情況都不需要做類型轉(zhuǎn)換。
• 兩個參數(shù)都是字符串，會按照字符串來比較，不做類型轉(zhuǎn)換
• 兩個參數(shù)都是整數(shù)，按照整數(shù)來比較，不做類型轉(zhuǎn)換
• 十六進(jìn)制的值和非數(shù)字做比較時(shí)，會被當(dāng)做二進(jìn)制串
• 有一個參數(shù)是 TIMESTAMP 或 DATETIME，并且另外一個參數(shù)是常量，常量會被轉(zhuǎn)換為 timestamp
• 有一個參數(shù)是 decimal 類型，如果另外一個參數(shù)是 decimal 或者整數(shù)，會將整數(shù)轉(zhuǎn)換為 decimal 后進(jìn)行比較，如果另外一個參數(shù)是浮點(diǎn)數(shù)，則會把 decimal 轉(zhuǎn)換為浮點(diǎn)數(shù)進(jìn)行比較
• 所有其他情況下，兩個參數(shù)都會被轉(zhuǎn)換為浮點(diǎn)數(shù)再進(jìn)行比較。（這里所說的浮點(diǎn)數(shù)一般默認(rèn)為double類型）

可以看到，非前六種以外的類型轉(zhuǎn)換都要轉(zhuǎn)成浮點(diǎn)類型來處理，這意味著什么？意味著MySQL承認(rèn)了隱式轉(zhuǎn)換這個事兒，還表示不愛看官方文檔的哥們兒出問題活該~~

我們用一些具體示例來看一下，通過下述SQL可見，當(dāng)1234沒有引號也就是整數(shù)時(shí)，'1234abcd' = 1234 → true，說明MySQL對'1234abcd'做了轉(zhuǎn)型，轉(zhuǎn)成了浮點(diǎn)類型，結(jié)果是：1234abcd => 1234

# 0：false；1：true
mysql> SELECT '1234abcd' = '1234';
+---------------------+
| '1234abcd' = '1234' |
+---------------------+
|                   0 |
+---------------------+
1 row in set (0.00 sec)
# 0：false；1：true
mysql> SELECT '1234abcd' = 1234;
+-------------------+
| '1234abcd' = 1234 |
+-------------------+
|                 1 |
+-------------------+
1 row in set, 1 warning (0.00 sec)

為啥1234abcd => 1234呢？ 其實(shí)'1234'和'abcd'都會轉(zhuǎn)成浮點(diǎn)數(shù)，即：1234+0=1234，非數(shù)字類型的都被直接轉(zhuǎn)成了 0

mysql> SELECT '1234' + 'abcd';
+-----------------+
| '1234' + 'abcd' |
+-----------------+
|            1234 |
+-----------------+
1 row in set, 1 warning (0.00 sec)

你發(fā)現(xiàn)了什么？原來字符串涉及到 +、=、-、/ 等等運(yùn)算符時(shí)都會進(jìn)行隱式轉(zhuǎn)型，也就是轉(zhuǎn)成double，那么字符串轉(zhuǎn)double是怎么轉(zhuǎn)的呢？

# 轉(zhuǎn)成：'1aaaa' = 1
mysql> SELECT '1aaaa' = 1;
+-------------+
| '1aaaa' = 1 |
+-------------+
|           1 |
+-------------+
1 row in set, 1 warning (0.00 sec)
# 轉(zhuǎn)成：'a1111' = 0
mysql> SELECT 'a1111' = 1;
+-------------+
| 'a1111' = 1 |
+-------------+
|           0 |
+-------------+
1 row in set, 1 warning (0.00 sec)
# 轉(zhuǎn)成：0 + 0 =0
mysql> SELECT 'aa' + 'aa' = 1;
+-----------------+
| 'aa' + 'aa' = 1 |
+-----------------+
|               0 |
+-----------------+
1 row in set, 2 warnings (0.00 sec)
# 轉(zhuǎn)成：0 + 0 + 1 =1
mysql> SELECT 'aa' + 'aa' + '1' = 1;
+-----------------------+
| 'aa' + 'aa' + '1' = 1 |
+-----------------------+
|                     1 |
+-----------------------+
1 row in set, 2 warnings (0.00 sec)

可見，是以字符串從左向右取值的，且從非數(shù)字起后面的值都被轉(zhuǎn)成 0，如a11111，第一位為a,則整體轉(zhuǎn)為 0；1aaaa第一位為1,第二位為a，從第二位往后轉(zhuǎn)成0，得a11111 → 0

mysql> SELECT * from t_user where `password`=1234;
+----+-----------+----------+
| id | username  | password |
+----+-----------+----------+
|  2 | 僑布斯    | 1234     |
|  3 | 提莫      | 1234abcd |
+----+-----------+----------+
2 rows in set, 1 warning (0.00 sec)

現(xiàn)在我們就明白為什么能匹配到提莫了。因?yàn)樵诓煌愋娃D(zhuǎn)換時(shí)"1234abcd"被轉(zhuǎn)成了浮點(diǎn)類型，"abcd"轉(zhuǎn)成浮點(diǎn)型后為0，因此MySQL判為：“1234abcd” = ‘1234' + 0 。

2、黑客同學(xué)喜歡用隱式轉(zhuǎn)換進(jìn)行SQL注入攻擊

通過第一部分隱式轉(zhuǎn)換的了解，我們可以預(yù)測一些簡單SQL注入的方式：

mysql> SELECT * from t_user where username='陳哈哈' and `password`=0;
+----+-----------+----------+
| id | username  | password |
+----+-----------+----------+
|  1 | 陳哈哈    | abcd1234 |
+----+-----------+----------+
1 row in set, 1 warning (0.00 sec)

果然，我賬號的密碼被毫無意外的攻破了。。。想想賬號被陌生人登錄，保存多年的情書也會被泄露出去~~

如果有些朋友公司的網(wǎng)站用的是下面的寫法：

select * from t_user where username=${username} and `password`=${password};

不然有些小伙伴友好的把請求參數(shù)構(gòu)建成 username → a' OR 1='1 ，那么password是啥都無所謂了，是吧。

懂我意思吧，快改了。

當(dāng)然，其實(shí)很多注入攻擊的真實(shí)目的，并不是用來破解用戶賬號的，而是破壞服務(wù)器。一般我們在頁面F12發(fā)現(xiàn)有問題的接口后，通過腳本模擬請求參數(shù)（構(gòu)造注入?yún)?shù)），去不斷嘗試自定義構(gòu)造limit、order、where等條件，或許花不了多久就能通過一個不規(guī)范的請求入口，檢索出該表甚至其他大表全量信息。導(dǎo)致公司服務(wù)器負(fù)載異常，連接數(shù)打滿，CPU200%等有趣的情況。有興趣的同學(xué)可以花幾小時(shí)嘗試破解自己公司的web~~

3、索引中隱式轉(zhuǎn)換的坑

同理，在MySQL根據(jù)索引進(jìn)行查詢時(shí)，如果你的username字段有索引且為varchar類型，且查詢?nèi)缦聲r(shí)：

select * from t_user where username=123;

該SQL會出現(xiàn)兩個問題：

1、索引失效

無法使用到索引查詢，因?yàn)閙ysql會在引擎層進(jìn)行類型隱式轉(zhuǎn)換（CONVERT_IMPLICIT），會先把username隱式轉(zhuǎn)換成浮點(diǎn)數(shù)，然后再跟你的123進(jìn)行比較，然而你的索引是建在username上的，并不是在轉(zhuǎn)換后的username上的，所以進(jìn)行轉(zhuǎn)換后的username相當(dāng)于沒有索引。會全表掃描，換做大表中，無法使用索引，你懂得。

2、查詢結(jié)果不準(zhǔn)確

第一部分我們已經(jīng)舉例說明，MySQL在隱式轉(zhuǎn)換時(shí)的varchar轉(zhuǎn)double，會出現(xiàn)很多意想不到的情況，比如 “123”，" 123"，"123a"都會轉(zhuǎn)成123，實(shí)際場景中都是不允許出現(xiàn)的。

總結(jié)

本篇文章就到這里了，希望能給你帶來幫助，也希望您能夠多多關(guān)注腳本之家的更多內(nèi)容！