MySQL 字元型別

雲崖先生發表於2020-08-30
MySql字元型別

字元型別

　　 MySQL提供了多種關於字元儲存的型別，但是在大多數情況下我們只使用char和varchar即可

型別大小用途
CHAR 0 - 255位元組 定長字串
VARCHAR 0 - 65535 位元組 變長字串
TINYBLOB 0 - 255位元組 不超過 255 個字元的二進位制字串
TINYTEXT 0 - 255位元組 短文字字串
BLOB 0 - 65 535位元組 二進位制形式的長文字資料
TEXT 0 - 65 535位元組 長文字資料
MEDIUMBLOB 0 - 16 777 215位元組 二進位制形式的中等長度文字資料
MEDIUMTEXT 0 - 16 777 215位元組 中等長度文字資料
LONGBLOB 0 - 4 294 967 295位元組 二進位制形式的極大文字資料
LONGTEXT 0 - 4 294 967 295位元組 極大文字資料

基本定義

　　 對於字元型別而言，其指定寬度均為存入寬度，以字元為單位。

create table temp(
        ch char(5) not null, # 最多5個字元
        vc varcha(5) not null # 最多5個字元
);

定長變長

區別差異

　　 char是定長字串，當存入的字元數量不足指定寬度時，將會使用進行填充。

　　 varchar是變長字串，當存入的字元數類不足指定寬度時，不會使用任何字元進行填充。

　　 通常情況下來講，char的存取速度要比varchar要高（約50%），但是更加浪費磁碟空間。

　　 在InnoDB儲存引擎中，不存在這種差異。

MySQL 字元型別 
#官網：https://dev.mysql.com/doc/refman/5.7/en/char.html
#注意：char和varchar括號內的引數指的都是字元的長度

#char型別：定長，簡單粗暴，浪費空間，存取速度快
    字元長度範圍：0-255（一箇中文是一個字元，是utf8編碼的3個位元組）
    儲存：
        儲存char型別的值時，會往右填充空格來滿足長度
        例如：指定長度為10，存>10個字元則報錯，存<10個字元則用空格填充直到湊夠10個字元儲存

    檢索：
        在檢索或者說查詢時，查出的結果會自動刪除尾部的空格，除非我們開啟pad_char_to_full_length SQL模式（SET sql_mode = 'PAD_CHAR_TO_FULL_LENGTH';）

#varchar型別：變長，精準，節省空間，存取速度慢
    字元長度範圍：0-65535（如果大於21845會提示用其他型別 。mysql行最大限制為65535位元組，字元編碼為utf-8：https://dev.mysql.com/doc/refman/5.7/en/column-count-limit.html）
    儲存：
        varchar型別儲存資料的真實內容，不會用空格填充，如果'ab  ',尾部的空格也會被存起來
        強調：varchar型別會在真實資料前加1-2Bytes的字首，該字首用來表示真實資料的bytes位元組數（1-2Bytes最大表示65535個數字，正好符合mysql對row的最大位元組限制，即已經足夠使用）
        如果真實的資料<255bytes則需要1Bytes的字首（1Bytes=8bit 2**8最大表示的數字為255）
        如果真實的資料>255bytes則需要2Bytes的字首（2Bytes=16bit 2**16最大表示的數字為65535）
    
    檢索：
        尾部有空格會儲存下來，在檢索或者說查詢時，也會正常顯示包含空格在內的內容
差異詳解
插入值CHAR(4)存入值CHAR(4)佔據空間VARCHAR(4)存入值VARCHAR(4)佔據空間
'' ' ' 4 bytes '' 1 byte
'ab' 'ab ' 4 bytes 'ab' 3 bytes
'abcd' 'abcd' 4 bytes 'abcd' 5 bytes
'abcdefgh' 'abcd' 4 bytes 'abcd' 5 bytes

由於varchar型別是變長儲存，所以需要有一個頭部來標識真實內容佔了多少字元，這個頭部通常佔據1個位元組。

即存入abcd的時候算上頭部實際上是佔用了5個位元組。這也是為什麼說通常情況下varchar比較節省記憶體，而不是說全部情況。

差異對比

　　 如果要想進定長與變長的差異對比試驗，需要用到以下兩個函式。

　　 length()：檢視位元組數

　　 char_length():檢視字元數

mysql> SET sql_mode = 'PAD_CHAR_TO_FULL_LENGTH';  # 必須修改模式，使其檢視定長字元型別時不會將填充空格移除。
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> create table ch(  # 建立定長字元型別的資料表，最大字元數為5字元
    ->         name char(5) not null
    -> );
Query OK, 0 rows affected (0.05 sec)

mysql> create table vc(  # 建立變長字元型別的資料表，最大字元數為5字元
    ->         name varchar(5) not null
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql> insert into  ch(name) values("ab");  # 插入2字元的資料
Query OK, 1 row affected (0.01 sec)

mysql> select char_length(name) from ch;  # 可以看到這裡的字元數為5，預設填充了3個空白字元
+-------------------+
| char_length(name) |
+-------------------+
|                 5 |
+-------------------+
1 row in set (0.00 sec)

mysql> insert into  vc(name) values("ab");  # 插入2字元的資料
Query OK, 1 row affected (0.00 sec)

mysql> select char_length(name) from vc;  # 字元數量依舊為2字元
+-------------------+
| char_length(name) |
+-------------------+
|                 2 |
+-------------------+
1 row in set (0.00 sec)

mysql>

　　 小總結：建議在同一張資料表中統一使用varchar或char型別，這裡更推薦使用char型別。

常用函式

大小寫轉換

　　 upper()小寫轉大寫，lower()大寫轉小寫

mysql> select upper("mysql"),lower("MYSQL");
+----------------+----------------+
| upper("mysql") | lower("MYSQL") |
+----------------+----------------+
| MYSQL          | mysql          |
+----------------+----------------+
1 row in set (0.00 sec)

mysql>

指定量取

　　 left()與right()函式用於取左或右指定數量的字元

　　 以下將示例取左邊3個字元。

mysql> select left("一二三四五",3);
+----------------------+
| left("一二三四五",3) |
+----------------------+
| 一二三               |
+----------------------+
1 row in set (0.00 sec)

mysql>

中間字元

　　 mid()從中間取字串，引數二為指定起始位置（從1開始），引數三為取的字元數量（可不指定，一直取完）。

　　 以下將演示從第二個字元開始取兩個。

mysql> select mid("一二三四五",2,2);
+-----------------------+
| mid("一二三四五",2,2) |
+-----------------------+
| 二三                  |
+-----------------------+
1 row in set (0.00 sec)

mysql>

擷取子串

　　 substring()從指定起始位置開始取出向右所有字串，可指定結束位置。（其實我感覺這個和mid()好像沒啥區別）

mysql> select substring("一二三四五六七八九",1,4);
+-------------------------------------+
| substring("一二三四五六七八九",1,4) |
+-------------------------------------+
| 一二三四                            |
+-------------------------------------+
1 row in set (0.00 sec)

mysql>

字元長度

　　 char_length()可獲取字元長度，如果要獲取char型別的字元長度且包括填充空白符，需要修改sql模式。

　　 修改sql模式：SET sql_mode = 'PAD_CHAR_TO_FULL_LENGTH';

mysql> select char_length("一二三四五六七八九");
+-----------------------------------+
| char_length("一二三四五六七八九") |
+-----------------------------------+
|                                 9 |
+-----------------------------------+
1 row in set (0.00 sec)

mysql>

位元組長度

　　 length()可獲取位元組長度。

mysql> select length("一二三四五六七八九");
+------------------------------+
| length("一二三四五六七八九") |
+------------------------------+
|                           18 |
+------------------------------+
1 row in set (0.00 sec)

mysql>

字元連線

　　 concat()可將多段字元進行連線。

　　 以下示例將展示一二三四五，---以及12345這三段字元的連線。

mysql> select concat("一二三四五","---","12345");
+------------------------------------+
| concat("一二三四五","---","12345") |
+------------------------------------+
| 一二三四五---12345                 |
+------------------------------------+
1 row in set (0.00 sec)

mysql>

相關文章