Java面試題沖刺第十三天–數據庫(3)

面試題1：MySQL有哪些數據類型？

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

數值類型

MySQL支持所有標準SQL數值數據類型。

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

關鍵字INT是INTEGER的同義詞，關鍵字DEC是DECIMAL的同義詞。

BIT數據類型保存位字段值，並且支持MyISAM、MEMORY、InnoDB和BDB表。

作為SQL標準的擴展，MySQL也支持整數類型TINYINT、SMALLINT、MEDIUMINT、INT和BIGINT。分別使用8、16、24、32、64位存儲空間，

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

下表顯示瞭需要的每個整數類型的存儲和范圍。

日期和時間類型

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

每個時間類型有一個有效值范圍和一個”零”值，當指定不合法的MySQL不能表示的值時使用”零”值。

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

字符串類型

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

註意： char(n) 和 varchar(n) 中括號中 n 代表字符的個數，並不代表字節個數，比如 CHAR(30) 就可以存儲 30 個字符。

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

BINARY 和 VARBINARY 類似於 CHAR 和 VARCHAR，不同的是它們包含二進制字符串而不要非二進制字符串。也就是說，它們包含字節字符串而不是字符字符串。這說明它們沒有字符集，並且排序和比較基於列值字節的數值值。

BLOB 是一個二進制大對象，可以容納可變數量的數據。有 4 種 BLOB 類型：TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB。它們區別在於可容納存儲范圍不同。

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

追問1：char 和 varchar 的區別是什麼？

1、固定長度 & 可變長度

VARCHAR

VARCHAR類型用於存儲可變長度字符串，是最常見的字符串數據類型。它比固定長度類型更節省空間，因為它僅使用必要的空間(根據實際字符串的長度改變存儲空間)。

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

CHAR

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

同時，CHAR值會根據需要采用空格進行剩餘空間填充，以方便比較和檢索。但正因為其長度固定，所以會占據多餘的空間，也是一種空間換時間的策略；

2、存儲方式

• VARCHAR

VARCHAR需要使用1或2個額外字節記錄字符串的長度：如果列的最大長度小於或等於255字節，則隻使用1個字節表示，否則使用2個字節。假設采用latinl字符集，一個VARCHAR(10)的列需要11個字節的存儲空間。VARCHAR(1000)的列則需要1002 個字節，因為需要2個字節存儲長度信息。

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

• CHAR

CHAR適合存儲很短或長度近似的字符串。例如，CHAR非常適合存儲密碼的MD5值，因為這是一個定長的值。對於經常變更的數據，CHAR也比VARCHAR更好，因為定長的CHAR類型不容易產生碎片。對於非常短的列，CHAR比VARCHAR在存儲空間上也更有效率。例如用CHAR(1)來存儲隻有Y和N的值，如果采用單字節字符集隻需要一個字節，但是VARCHAR(1)卻需要兩個字節，因為還有一個記錄長度的額外字節。

3、存儲容量

• CHAR

對於char類型來說，最多隻能存放的字符個數為255，和編碼無關，任何編碼最大容量都是255。

• VARCHAR

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

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

為什麼是65532個字符？算法如下（有餘數時向下取整）：

最大長度(字符數) = （行存儲最大字節數 – NULL標識列占用字節數 – 長度標識字節數） / 字符集單字符最大字節數

NULL標識列占用字節數：允許NULL時，占一字節

長度標識字節數：記錄長度的標識，長度小於等於255（28）時，占1字節；小於65535時（216）,占2字節

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

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

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

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

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

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

假設當前還有6字節可以存放字符，按單字符占用最大字節數來算，可以存放3個GBK、或2個utf8、或1個utf8mb4。

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

沒錯，相信你已經有答案瞭，別這麼幹！

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

事實證明有很大的優勢。更長的列會消耗更多的內存，因為MySQL通常會分配固定大小的內存塊來保存內部值。

當然，在沒拿到存儲引擎存儲的數據之前，並不會知道我這一行拿出來的數據到底有多長，可能長度隻有1，可能長度是500，那怎麼辦呢？那就隻能先把最大空間分配好瞭，避免放不下的問題發生，這樣實際上對於真實數據較短的varchar確實會造成空間的浪費。

舉例：我向數據類型為：varchar（1000）的列插入瞭1024行數據，但是每個隻存一個字符，那麼這1024行真實數據量其實隻有1K，但是我卻需要約1M的內存去適應他。所以最好的策略是隻分配真正需要的空間。

5、在SQL中需要註意的點

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

-- 建表語句
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;

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

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

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

-- 測試數據查詢
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)

當檢索這些值的時候，會發現id=8行中，char類型的”陳哈哈 “末尾的空格被截斷瞭，而VARCHAR(10)字段存儲相同的值時，末尾的空格被保留瞭。另外，id=7行的數據前面空格都被保留瞭。

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

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

varchar(50)

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

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

char(50)

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

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

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

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

int的范圍

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

int(10)的意思

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

當你輸入的數據不足10位時，會自動幫你補全位數。

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

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

面試題2：MySQL 的內連接、左連接、右連接有什麼區別？

摘取一下SQL大腿群裡同學的回復吧：

例：ab兩表關聯，a表數據有，b表關聯數據沒有，a表的就不要顯示或顯示null的問題；幾個連接怎麼使用，總結一下：

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

面試題3：MySQL的隱式轉換問題遇到過麼？說說你的理解。

1、SQL語句中隱式轉換的坑

先看一下官方的隱試轉換說明：

翻譯成人話：

• 兩個參數至少有一個是 NULL 時，比較的結果也是 NULL，例外是使用 <=> 對兩個 NULL 做比較時會返回 1，這兩種情況都不需要做類型轉換。
• 兩個參數都是字符串，會按照字符串來比較，不做類型轉換
• 兩個參數都是整數，按照整數來比較，不做類型轉換
• 十六進制的值和非數字做比較時，會被當做二進制串
• 有一個參數是 TIMESTAMP 或 DATETIME，並且另外一個參數是常量，常量會被轉換為 timestamp
• 有一個參數是 decimal 類型，如果另外一個參數是 decimal 或者整數，會將整數轉換為 decimal 後進行比較，如果另外一個參數是浮點數，則會把 decimal 轉換為浮點數進行比較
• 所有其他情況下，兩個參數都會被轉換為浮點數再進行比較。（這裡所說的浮點數一般默認為double類型）

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

我們用一些具體示例來看一下，通過下述SQL可見，當1234沒有引號也就是整數時，’1234abcd’ = 1234 → true，說明MySQL對'1234abcd'做瞭轉型，轉成瞭浮點類型，結果是：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呢？ 其實’1234’和’abcd’都會轉成浮點數，即：1234+0=1234，非數字類型的都被直接轉成瞭 0

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

你發現瞭什麼？原來字符串涉及到 +、=、-、/ 等等運算符時都會進行隱式轉型，也就是轉成double，那麼字符串轉double是怎麼轉的呢？

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

可見，是以字符串從左向右取值的，且從非數字起後面的值都被轉成 0，如a11111，第一位為a,則整體轉為 0；1aaaa第一位為1,第二位為a，從第二位往後轉成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)

現在我們就明白為什麼能匹配到提莫瞭。因為在不同類型轉換時”1234abcd”被轉成瞭浮點類型，”abcd”轉成浮點型後為0，因此MySQL判為：“1234abcd” = ‘1234′ + 0 。

2、黑客同學喜歡用隱式轉換進行SQL註入攻擊

通過第一部分隱式轉換的瞭解，我們可以預測一些簡單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)

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

如果有些朋友公司的網站用的是下面的寫法：

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

不然有些小夥伴友好的把請求參數構建成 username → a’ OR 1=’1 ，那麼password是啥都無所謂瞭，是吧。

懂我意思吧，快改瞭。

當然，其實很多註入攻擊的真實目的，並不是用來破解用戶賬號的，而是破壞服務器。一般我們在頁面F12發現有問題的接口後，通過腳本模擬請求參數（構造註入參數），去不斷嘗試自定義構造limit、order、where等條件，或許花不瞭多久就能通過一個不規范的請求入口，檢索出該表甚至其他大表全量信息。導致公司服務器負載異常，連接數打滿，CPU200%等有趣的情況。有興趣的同學可以花幾小時嘗試破解自己公司的web~~

3、索引中隱式轉換的坑

同理，在MySQL根據索引進行查詢時，如果你的username字段有索引且為varchar類型，且查詢如下時：

select * from t_user where username=123;

該SQL會出現兩個問題：

1、索引失效

無法使用到索引查詢，因為mysql會在引擎層進行類型隱式轉換（CONVERT_IMPLICIT），會先把username隱式轉換成浮點數，然後再跟你的123進行比較，然而你的索引是建在username上的，並不是在轉換後的username上的，所以進行轉換後的username相當於沒有索引。會全表掃描，換做大表中，無法使用索引，你懂得。

2、查詢結果不準確

第一部分我們已經舉例說明，MySQL在隱式轉換時的varchar轉double，會出現很多意想不到的情況，比如 “123”，” 123″，”123a”都會轉成123，實際場景中都是不允許出現的。

總結

本篇文章就到這裡瞭，希望能給你帶來幫助，也希望您能夠多多關註WalkonNet的更多內容！

推薦閱讀：

• MySQL 5.7常見數據類型
• MySQL中建表與常見的類型設計陷阱詳解
• MySQL 數據類型詳情
• mysql explain中key_len的含義以及計算方法
• MySQL長字符截斷的實現示例