第01期：MySQL 數據類型的藝術

專欄序

對於關係型資料庫來說，表的設計 以及 SQL 的寫法 尤為重要，佔據性能的 90% 都不為過，所以這次專門針對這兩大類知識領域，為您抽絲剝繭詳細分析。

本專欄語言通俗易懂，選取大量示例為您詳細說明箇中奧妙~

面向的對象：

  • MySQL DBA
  • MySQL 開發者
  • 想從其他資料庫轉到 MySQL 的同學

第 01 期：MySQL 數據類型的藝術

那今天的開篇即為 MySQL 的數據類型，在這裡主要包含了兩部分：

MySQL 欄位類型的選擇，欄位類型定義的合適與否直接影響到磁碟空間的佔用以及 MySQL 性能極致的發揮。

MySQL 數據類型在存儲程式中的應用，如存儲函數，存儲過程，觸發器等。

MySQL 數據類型介紹

MySQL 數據類型細分下來，大概有以下幾類：

  • 數值，典型代表為 tinyint,int,bigint
  • 浮點/定點，典型代表為 float,double,decimal 以及相關的同義詞
  • 字元串，典型代表為 char,varchar
  • 時間日期，典型代表為 date,datetime,time,timestamp
  • 二進位，典型代表為 binary,varbinary
  • 位類型
  • 枚舉類型
  • 集合類型

以下內容，我們在另一篇文章介紹

  • 大對象，比如 text,blob
  • json 文檔類型

一、數值類型（不是數據類型，別看錯了）

如果用來存放整數，根據範圍的不同，選擇不同的類型。

以上是幾個整數選型的例子。整數的應用範圍最廣泛，可以用來存儲數字，也可以用來存儲時間戳，還可以用來存儲其他類型轉換為數字後的編碼，如 IPv4 等。

示例 1

用 int32 來存放 IPv4 地址，比單純用字元串節省空間。表 x1，欄位 ipaddr，利用函數 inet_aton，檢索的話用函數 inet_ntoa。

mysql-(ytt/3305)->create table x1( ipaddr int unsigned);    Query OK, 0 rows affected (0.04 sec)          mysql-(ytt/3305)->insert into x1 values (inet_aton('192.168.2.171'));    Query OK, 1 row affected (0.01 sec)          mysql-(ytt/3305)->insert into x1 values (inet_aton('192.168.2.172'));    Query OK, 1 row affected (0.01 sec)          mysql-(ytt/3305)->insert into x1 values (inet_aton('192.168.2.173'));    Query OK, 1 row affected (0.01 sec)          mysql-(ytt/3305)->insert into x1 values (inet_aton('192.168.2.174'));    Query OK, 1 row affected (0.01 sec)

更改表結構，加一個虛擬列來轉換欄位 ipaddr。

mysql-(ytt/3305)->alter table x1 add column        ->ipaddr_real varchar(20) generated always as (inet_ntoa(ipaddr)) virtual;    Query OK, 0 rows affected (0.02 sec)    Records: 0  Duplicates: 0  Warnings: 0          mysql-(ytt/3305)->select * from x1;    +------------+---------------+    | ipaddr     | ipaddr_real   |    +------------+---------------+    | 3232236203 | 192.168.2.171 |    | 3232236204 | 192.168.2.172 |    | 3232236205 | 192.168.2.173 |    | 3232236206 | 192.168.2.174 |    +------------+---------------+    4 rows in set (0.00 sec)

示例 2

簡單的對比佔用磁碟空間大小，我定義了三張表 t1,t2,t3。欄位分別為 tinyint,int32,int64。每張表數據相同，記錄數為 2000W 行。

mysql-(ytt/3305)->CALL `get_schema_records`();    +------------+------------+------------+    | t1 records | t2 records | t3 records |    +------------+------------+------------+    |   20000000 |   20000000 |   20000000 |    +------------+------------+------------+    1 row in set (1.89 sec)          Query OK, 0 rows affected (1.89 sec)

查看磁碟空間佔用，t3 佔用最大，t1 佔用最小。所以說如果整數存儲範圍有固定上限，並且未來也沒有必要擴容的話，建議選擇最小的類型，當然了對其他類型也適用。

root@ytt-pc:/var/lib/mysql/3305/ytt# ls -sihl    總用量 3.0G    3541825 861M -rw-r----- 1 mysql mysql 860M 12月 10 11:36 t1.ibd    3541820 989M -rw-r----- 1 mysql mysql 988M 12月 10 11:38 t2.ibd    3541823 1.2G -rw-r----- 1 mysql mysql 1.2G 12月 10 11:39 t3.ibd

二、浮點數 / 定點數

先說 浮點數，float 和 double 都代表浮點數，區別簡單記就是 float 默認占 4 Byte。

float(p) 中的 p 代表整數位最小精度。如果 p > 24 則直接轉換為 double，占 8 Byte。p 最大值為 53，但最大值存在計算不精確的問題。

再說 定點數，包括 decimal 以及同義詞 numeric，定點數的整數位和小數位分別存儲，有效精度最大不能超過 65。

所以區別於 float 的在於精確存儲，必須需要精確存儲或者精確計算的最好定義為 decimal 即可。

示例 3

創建一張表 y1，分別給欄位 f1,f2,f3 不同的類型。

mysql-(ytt/3305)->create table y1(f1 float,f2 double,f3 decimal(10,2));    Query OK, 0 rows affected (0.03 sec)

插入一些數值。

mysql-(ytt/3305)->insert into y1 values (10.2,10.2,10.2);    Query OK, 1 row affected (0.01 sec)          mysql-(ytt/3305)->insert into y1 values (100.12,100.12,100.12);    Query OK, 1 row affected (0.01 sec)          mysql-(ytt/3305)->insert into y1 values (1001.12,1001.12,1001.12);    Query OK, 1 row affected (0.01 sec)          mysql-(ytt/3305)->insert into y1 values (12001.12,12001.12,12001.12);    Query OK, 1 row affected (0.01 sec)          mysql-(ytt/3305)->insert into y1 values (12001222.12,12001222.12,12001222.12);    Query OK, 1 row affected (0.00 sec)

檢索記錄數，發現 f1 裡面存放的記錄數已經不準確了。

mysql-(ytt/3305)->select * from y1;    +---------+----------+----------+    | f1      | f2       | f3       |    +---------+----------+----------+    |    10.2 |     10.2 |    10.20 |    |  100.12 |   100.12 |   100.12 |    | 1001.12 |  1001.12 |  1001.12 |    | 12001.1 | 12001.12 | 12001.12 |    +---------+----------+----------+    4 rows in set (0.00 sec)

那把 f3 的精度改大點，看看 double 的存儲。

mysql-(ytt/3305)->alter table y1 modify f3 decimal(50,10);    Query OK, 5 rows affected (0.06 sec)    Records: 5  Duplicates: 0  Warnings: 0

插入一條更大的記錄。

mysql-(ytt/3305)->insert into y1 values (123456789010.1234567,123456789010.1234567,123456789010.1234567);    Query OK, 1 row affected (0.01 sec)

檢索發現只有 f3 的值是精確的。

mysql-(ytt/3305)->select * from y1;    +--------------+--------------------+-------------------------+    | f1           | f2                 | f3                      |    +--------------+--------------------+-------------------------+    |         10.2 |               10.2 |           10.2000000000 |    |       100.12 |             100.12 |          100.1200000000 |    |      1001.12 |            1001.12 |         1001.1200000000 |    |      12001.1 |           12001.12 |        12001.1200000000 |    |     12001200 |        12001222.12 |     12001222.1200000000 |    | 123457000000 | 123456789010.12346 | 123456789010.1234567000 |    +--------------+--------------------+-------------------------+    6 rows in set (0.00 sec)

三、字元類型

字元類型和整形一樣，用途也很廣。用來存儲字元、字元串、MySQL 所有未知的類型。可以簡單說是萬能類型！

char(10) 代表最大支援 10 個字元存儲，varchar(10) 雖然和 char(10) 可存儲的字元數一樣多，不同的是 varchar 類型存儲的是實際大小，char 存儲的理論固定大小。具體的位元組數和字符集相關。

示例 4

例如下面表 t4 ，兩個欄位 c1,c2，分別為 char 和 varchar。

mysql-(ytt/3305)->create table t4 (c1 char(20),c2 varchar(20));    Query OK, 0 rows affected (0.02 sec)

插入一條記錄。

mysql-(ytt/3305)->set @a = "我是傻傻的小月亮！！！！";    Query OK, 0 rows affected (0.00 sec)          mysql-(ytt/3305)->insert into t4 values (@a,@a);    Query OK, 1 row affected (0.00 sec)          mysql-(ytt/3305)->select * from t4;    +--------------------------------------+--------------------------------------+    | c1                                   | c2                                   |    +--------------------------------------+--------------------------------------+    | 我是傻傻的小月亮！！！！| 我是傻傻的小月亮！！！！|    +--------------------------------------+--------------------------------------+    1 row in set (0.00 sec)

分別拿出來兩個欄位的值，發現 c1 比 c2 多了很多內容，包括字元個數，位元組數。

mysql-(ytt/3305)->SELECT 'c1' AS 'column list',       -> char_length(c1) '  as characters',       -> length(c1) ' as bytes'       -> FROM t4       -> UNION all       -> SELECT 'c2',       -> char_length(c2) as ' characters',       -> length(c2) as ' bytes'       -> FROM t4;    +-------------+---------------+----------+    | column list | as characters | as bytes |    +-------------+---------------+----------+    | c1          |            20 |       44 |    | c2          |            12 |       36 |    +-------------+---------------+----------+    2 rows in set, 4 warnings (0.00 sec)

所以在 char 和 varchar 選型上，要注意看是否合適的取值範圍。比如固定長度的值，肯定要選擇 char；不確定的值，則選擇 varchar。

四、日期類型

日期類型包含了 date,time,datetime,timestamp，以及 year。year 占 1 Byte，date 占 3 Byte。　

time,timestamp,datetime 在不包含小數位時分別佔用 3 Byte,4 Byte,8 Byte；小數位部分另外計算磁碟佔用，見下面表格。

注意：

timestamp 代表的時間戳是一個 int32 存儲的整數，取值範圍為 '1970-01-01 00:00:01.000000' 到 '2038-01-19 03:14:07.999999'；

datetime 取值範圍為 '1000-01-01 00:00:00.000000' 到 '9999-12-31 23:59:59.999999'。

綜上所述，日期這塊類型的選擇遵循以下原則：

1. 如果時間有可能超過時間戳範圍，優先選擇 datetime。

2. 如果需要單獨獲取年份值，比如按照年來分區，按照年來檢索等，最好在表中添加一個 year 類型來參與。

3. 如果需要單獨獲取日期或者時間，最好是單獨存放，而不是簡單的用 datetime 或者 timestamp。後面檢索時，再加函數過濾，以免後期增加 SQL 編寫帶來額外消耗。

4. 如果有保存毫秒類似的需求，最好是用時間類型自己的特性，不要直接用字元類型來代替。MySQL 內部的類型轉換對資源額外的消耗也是需要考慮的。

示例 5

建立表 t5，對這些可能需要的欄位全部分離開，這樣以後寫 SQL 語句的時候就很容易了。

mysql-(ytt/3305)->create table t5 (c1 date,c2 datetime(3),c3 timestamp(3),c4 time(3),c5 year);    Query OK, 0 rows affected (1.01 sec)          mysql-(ytt/3305)->set @a='2018-03-25 12:22:33.342';    Query OK, 0 rows affected (0.00 sec)          mysql-(ytt/3305)->insert into t5 values (date(@a), @a,@a,time(@a),year(@a));    Query OK, 1 row affected (0.01 sec)          mysql-(ytt/3305)->select * from t5;    +------------+-------------------------+-------------------------+--------------+------+    | c1         | c2                      | c3                      | c4           | c5   |    +------------+-------------------------+-------------------------+--------------+------+    | 2018-03-25 | 2018-03-25 12:22:33.342 | 2018-03-25 12:22:33.342 | 12:22:33.342 | 2018 |    +------------+-------------------------+-------------------------+--------------+------+

當然了，這種情形佔用額外的磁碟空間。如果想在易用性與空間佔用量大這兩點來折中，可以用 MySQL 的虛擬列來實時計算。比如假設 c5 欄位不存在，想要得到 c5 的結果。

mysql-(ytt/3305)->alter table t5 drop c5, add c5 year generated always as (year(c1)) virtual;    Query OK, 1 row affected (2.46 sec)    Records: 1  Duplicates: 0  Warnings: 0

五、二進位類型

binary 和 varbinary 對應了 char 和 varchar 的二進位存儲，相關的特性都一樣。不同的有以下幾點：

  • binary(10)/varbinary(10) 代表的不是字元個數，而是位元組數。
  • 行結束符不一樣。char 的行結束符是 ，binary 的行結束符是 0x00。
  • 由於是二進位存儲，所以字元編碼以及排序規則這類就直接無效了。

示例 6

來看這個 binary 存取的簡單示例，還是之前的變數 @a。

切記！這裡要提前計算好 @a 佔用的位元組數，以防存儲溢出。

mysql-(ytt/3305)->set @a = "我是傻傻的小月亮!!!!";    Query OK, 0 rows affected (0.00 sec)          mysql-(ytt/3305)->create table t6 (c1 binary(28),c2 varbinary(28));    Query OK, 0 rows affected (0.03 sec)          mysql-(ytt/3305)->insert into t6 values (@a,@a);    Query OK, 1 row affected (0.01 sec)          mysql-(ytt/3305)->select * from t6;    +------------------------------+------------------------------+    | c1                           | c2                           |    +------------------------------+------------------------------+    | 我是傻傻的小月亮!!!!         | 我是傻傻的小月亮!!!!         |    +------------------------------+------------------------------+    1 row in set (0.00 sec)

六、位類型

bit 為 MySQL 里存儲比特位的類型，最大支援 64 比特位， 直接以二進位方式存儲，一般用來存儲狀態類的資訊。比如，性別，真假等。具有以下特性：

1. 對於 bit(8) 如果單純存放 1 位，左邊以 0 填充 00000001。

2. 查詢時可以直接十進位來過濾數據。

3. 如果此欄位加上索引，MySQL 不會自己做類型轉換，只能用二進位來過濾。

示例 7

創建表 c1, 欄位性別定義一個比特位。

mysql-(ytt/3305)->create table c1(gender bit(1));    Query OK, 0 rows affected (0.02 sec)

插入兩條記錄

mysql-(ytt/3305)->insert into c1 values (b'0');    Query OK, 1 row affected (0.01 sec)          mysql-(ytt/3305)->insert into c1 values (b'1');    Query OK, 1 row affected (0.00 sec)

檢索數據，直接以十進位方式顯示。

mysql-(ytt/3305)->select gender+0 'f1'  from c1;    +------+    | f1   |    +------+    |    0 |    |    1 |    +------+    2 rows in set (0.01 sec)

也可以用類型顯示轉換。

mysql-(ytt/3305)->select cast(gender as unsigned)  'f1' from c1;    +------+    | f1   |    +------+    |    0 |    |    1 |    +------+    2 rows in set (0.00 sec)

過濾數據也一樣，二進位或者直接十進位都行。

mysql-(ytt/3305)->select conv(gender,16,10) as gender        -> from c1 where gender = b'1';    +--------+    | gender |    +--------+    | 1      |    +--------+    1 row in set (0.00 sec)        mysql-(ytt/3305)->select conv(gender,16,10) as gender         -> from c1 where gender = '1';    +--------+    | gender |    +--------+    | 1      |    +--------+    1 row in set (0.00 sec)

其實這樣的場景，也可以定義為 char(0)，這也是類似於 bit 非常優化的一種用法。

mysql-(ytt/3305)->create table c2(gender char(0));    Query OK, 0 rows affected (0.03 sec)

那現在我給表 c1 簡單的造點測試數據。

mysql-(ytt/3305)->select count(*) from c1;    +----------+    | count(*) |    +----------+    | 33554432 |    +----------+    1 row in set (1.37 sec)

把 c1 的數據全部插入 c2。

mysql-(ytt/3305)->insert into c2 select if(gender = 0,'',null) from c1;    Query OK, 33554432 rows affected (2 min 18.80 sec)    Records: 33554432  Duplicates: 0  Warnings: 0

兩張表的磁碟佔用差不多。

root@ytt-pc:/var/lib/mysql/3305/ytt# ls -sihl    總用量 1.9G    4085684 933M -rw-r----- 1 mysql mysql 932M 12月 11 10:16 c1.ibd    4082686 917M -rw-r----- 1 mysql mysql 916M 12月 11 10:22 c2.ibd

檢索方式稍微有些不同，不過效率也差不多。所以說，字元類型不愧為萬能類型。

mysql-(ytt/3305)->select count(*) from c1 where gender = 0;    +----------+    | count(*) |    +----------+    | 16772213 |    +----------+    1 row in set (12.03 sec)          mysql-(ytt/3305)->select count(*) from c2 where gender = '';    +----------+    | count(*) |    +----------+    | 16772213 |    +----------+    1 row in set (12.53 sec)

七、枚舉類型

枚舉類型，也即 enum。適合提前規劃好了所有已經知道的值，且未來最好不要加新值的情形。枚舉類型有以下特性：

1. 最大佔用 2 Byte。

2. 最大支援 65535 個不同元素。

3. MySQL 後台存儲以下標的方式，也就是 tinyint 或者 smallint 的方式，下標從 1 開始。

4. 排序時按照下標排序，而不是按照裡面元素的數據類型。所以這點要格外注意。

示例 8

創建表 t7。

mysql-(ytt/3305)->create table t7(c1 enum('mysql','oracle','dble','postgresql','mongodb','redis','db2','sql server'));    Query OK, 0 rows affected (0.03 sec)

用下標插入數據。

mysql-(ytt/3305)->insert into t7 values (1);    Query OK, 1 row affected (0.40 sec)          mysql-(ytt/3305)->insert into t7 values (2);    Query OK, 1 row affected (0.00 sec)

用真實元素插入數據。

mysql-(ytt/3305)->insert into t7 values ('postgresql');    Query OK, 1 row affected (0.01 sec)          mysql-(ytt/3305)->insert into t7 values ('dble');    Query OK, 1 row affected (0.01 sec)          mysql-(ytt/3305)->insert into t7 values ('sql server');

查詢結果出來，發現是按照之前定義的元素排序，也就是下標排序。

mysql-(ytt/3305)->select * from t7 order by c1;    +------------+    | c1         |    +------------+    | mysql      |    | oracle     |    | dble       |    | postgresql |    | sql server |    +------------+    5 rows in set (0.00 sec)

八、集合類型

集合類型 SET 和枚舉類似，也是得提前知道有多少個元素。SET 有以下特點：

1. 最大佔用 8 Byte，int64。

2. 內部以二進位位的方式存儲，對應的下標如果以十進位來看，就分別為 1,2,4,8，…，pow(2,63)。

3. 最大支援 64 個不同的元素，重複元素的插入，取出來直接去重。

4. 元素之間可以組合插入，比如下標為 1 和 2 的可以一起插入，直接插入 3 即可。

示例 9

定義表 c7 欄位 c1 為 set 類型，包含了 8 個值，也就是下表最大為 pow(2,7)。

mysql-(ytt/3305)->create table c7(c1 set('mysql','oracle','dble','postgresql','mongodb','redis','db2','sql server'));    Query OK, 0 rows affected (0.02 sec)

插入 1 到 128 的所有組合。

mysql-(ytt/3305)->INSERT INTO c7    WITH RECURSIVE ytt_number (cnt) AS (            SELECT 1 AS cnt            UNION ALL            SELECT cnt + 1            FROM ytt_number            WHERE cnt < pow(2, 7)        )    SELECT *    FROM ytt_number;    Query OK, 128 rows affected (0.01 sec)    Records: 128  Duplicates: 0  Warnings: 0

查詢的時候也是直接用下標或者元素來查。

mysql-(ytt/3305)->select * from c7 where c1 = 1;    +-------+    | c1    |    +-------+    | mysql |    +-------+    1 row in set (0.00 sec)          mysql-(ytt/3305)->select * from c7 where c1 = 'mysql';    +-------+    | c1    |    +-------+    | mysql |    +-------+    1 row in set (0.00 sec)

九、數據類型在存儲函數中的用法

函數里除了顯式聲明的變數外，默認 session 變數的數據類型很弱，隨著給定值的不同隨意轉換。

示例 10

定義一個函數，返回兩個給定參數的乘積。定義里有兩個變數，一個是 v_tmp 顯式定義為 int64，另外一個 @vresult 隨著給定值的類型隨意變換類型。

DELIMITER $$    CREATE DEFINER=`ytt`@`127.0.0.1` FUNCTION `ytt_sample_data_type`(`f1` INT, `f2` INT) RETURNS varchar(100) CHARSET latin1       NO SQL    begin    declare v_tmp bigint default 0;    set v_tmp = f1 * f2;    set @v_result = concat('The result is: ''',v_tmp,'''.');    return @v_result;    end$$    DELIMITER ;

簡單調用下。

mysql-(ytt/3305)->select ytt_sample_data_type(1111,222) 'result';    +--------------------------+    | result                   |    +--------------------------+    | The result is: '246642'. |    +--------------------------+    1 row in set (0.00 sec)

總結

本篇把 MySQL 基本的數據類型做了簡單的介紹，並且用了一些容易理解的示例來梳理這些類型。我們在實際場景中，建議選擇適合最合適的類型，不建議所有數據類型簡單的最大化原則。比如能用 varchar(100)，不用 varchar(1000)。

VirMach 便宜 VPS

QNews

QNews