和眾多程式語言一樣，SQL（結構化查詢語言）也有自己的資料型別，在這片文章中，我將簡單介紹MySQL中的資料型別，及其如何應用和一些注意點。

MySQL資料型別一覽：

• 整數型別 （如int）
• 浮點數型別 （如double）
• 定點數型別 （如decimal）
• 日期和時間型別 （如date）
• 字串型別 （如char、varchar）
• 二進位制型別 （如binary、varbinary）
• 其他型別 （如enum、json、bit）

---

Table of Contents

數字型 - 整型

數字型 - 浮點型

時間型

字串 - 小串

字串 - 大文字

二進位制

超大二進位制

JSON

列舉型別(enum)

bit

---

數字型 - 整型

tinyInt	1位元組	表示範圍[-127, +127]，當無符號時為[0, 255]
smallint	2位元組	範圍[-2^15, 2^15-1](-32768, 32767)
mediumint	3位元組	範圍[-8388608, 8388607]
int(integer)	4位元組	範圍[-2^31 (-2,147,483,648), 2^31 - 1 (2,147,483,647)]
bigint	8位元組	範圍[-2^63, 2^63-1] (-9223372036854775808, 9223372036854775807)

注意：如果在插入時資料大得超過的儲存範圍，則會報錯

CREATE TABLE test_int ( -- 建立一個測試表
	id INT AUTO_INCREMENT PRIMARY KEY,

	ti tinyint,
	si SMALLINT,
	mi MEDIUMINT,
	ii INT,
	bi bigint
);

---

數字型 - 浮點型

float	4位元組	單精度浮點數（參照IEEE754標準）
double	8位元組	雙精度浮點數
decimal（m,d）	M+2個位元組	其實decimal並非浮點型，頂點型。小數點前後資料不損失。 M代表總共能儲存M位，N代表小數點後保留位數，1 < M < 254, 0 < N < 60;

使用選擇：

1. float：僅能精確儲存長度為6位數字的浮點型數字，超出的將會被截斷（四捨五入）。
2. double：僅能精確儲存長度為16位數字的浮點型數字，超出的將會被截斷（四捨五入）。其中，小數點後能精度為9位。此外，關於float和double的儲存小數的原理，是IEEE754標準規定的。
3. decimal：能精確儲存長度為m位數字，且小數點後的d位數字也能精確地儲存，不會發生截斷。

深度理解float

DROP TABLE IF EXISTS test_floating;
CREATE TABLE test_floating
(
	id int AUTO_INCREMENT PRIMARY KEY, -- 表主鍵
	my_float float  -- 僅能儲存長度為6位數字的浮點數

);

-- 共10位，float只能保證前6位數字精確，即3.14159; 其後的數字將不會精確
INSERT INTO test_floating (my_float) VALUES (3.141592653); 

-- 共12位，float只能保證前6位數字精確，即333.141; 其後的數字將不會精確
INSERT INTO test_floating (my_float) VALUES (333.141592653); 

SELECT * FROM test_floating ;

深度理解double

DROP TABLE IF EXISTS test_double;
CREATE TABLE test_double
(
	id int AUTO_INCREMENT PRIMARY KEY,
	my_double double  -- 僅能儲存長度為13位數字的浮點數
);

-- 共14位，double能保證16位的精度，全部會被儲存
INSERT INTO test_double (my_double) VALUES (3.1415926535898);

-- 共21位，但double只能保證前16的精度，      即3.141592653589895
INSERT INTO test_double (my_double) VALUES (3.14159265358989546545);

-- 共24位，但double只能保證前16位的精度，    即3333333333.1415925
INSERT INTO test_double (my_double) VALUES (3333333333.1415926535898);

SELECT * FROM test_double ;

深度理解decimal

DROP TABLE IF EXISTS test_decimal;
CREATE TABLE test_decimal
(
	id int AUTO_INCREMENT PRIMARY KEY,
	my_decimal decimal(10,6)  -- 能儲存總長為10個，小數點後為6個
);

-- 共14位，但decimal被要求精度為小數點後6個，即3.141593
INSERT INTO test_decimal (my_decimal) VALUES (3.1415926535898);

-- 共22位
INSERT INTO test_decimal (my_decimal) VALUES (33333333.1415926535898);
-- 執行時報錯：Out of range value for column 'my_decimal' at row 1
-- 原因：總長不能超過decimal中定義的長度

---

時間型

Year	1位元組	範圍：1901~2155 支援的型別為：YYYY YY（即2030年，可以寫成30） YYYY YY
Time	3位元組	範圍：-838:59:59~838:59:59 hh:mm:ss hhmmss hhmmss
Date	4位元組	範圍：1000-01-01~9999-12-31 YYYY-MM-DD YY-MM-DD YYYYMMDD YYMMDD YYYYMMDD YYMMDD
Timestamp 時間戳	4位元組	範圍：19700101000000 到 2038-01-19 03:14:07 YY-MM-DD hh:mm:ss YYYYMMDDhhmmss YYMMDDhhmmss YYYYMMDDhhmmss YYMMDDhhmmss
Datetime	8位元組	範圍：1000-01-01 00:00:00~ 9999-12-31 23:59:59 YYYY-MM-DD HH:MM:SS

如何插入日期、時間、時間戳

CREATE TABLE test_date
(
	id INT AUTO_INCREMENT PRIMARY KEY,
	mydate DATE, -- 日期
	mytime TIME, -- 時間
	myts TIMESTAMP -- 時間戳
)

INSERT INTO test_date(mydate,mytime,myts) VALUES 
	('1997-01-06','20:30:00','19970106203000'),
	('2020-06-01','19:16:00','20200601191600')

SELECT * FROM test_date;

---

字串 - 小串

char(M)	1位元組	固定長度的字串，M為0~255之間的整數 指定了M，就會開闢儲存M個字元的儲存空間 效率較高
VARCHAR(M)	4位元組	可變長度的字串，M為0~65535之間的整數 指定了M，還是會根據實際的字元數，開闢儲存空間 效率較低

特性：

1. 這裡的char(M)、varchar(M)中的M，指的是字元的個數，一箇中文漢字、一個字母、一個數字、一個標點符號都算一個字元
2. 為什麼char佔空間1位元組，M卻能取到255？—— 這是因為1位元組的有符號取值範圍為[-127, +127]，而無符號的取值範圍為[0, 255]。

超過了範圍就會報錯

超過了資料型別的容量

varchar(數字)：數字就是表示可儲存字元的個數

字串 - 大文字

大文字資料（儲存如文章內容、評論）

1. 超長字串
2. 超越varchar(65535)的字串

TINYTEXT	可存0~255字元（約1位元組）	常應用於博文摘錄，文章摘要等。 效率低於char。所以一般情況下比較少使用該資料型別。
TEXT	可存0~65535字元（4位元組）	可儲存文章的正文。 注意到varchar也能儲存65535個字元，且text較慢，所以儘量選擇varchar
MEDIUMTEXT	可存0~16777215字元（約16MB）	用於超出了text、varchar的儲存範圍時使用
LONGTEXT	可存0~4294967295字元（約4GB）

特性：

1. text 在定義時，不需要定義長度，也不會計算總長度。
2. text 型別在定義時，不可給default值
3. TEXT資料不儲存在資料庫伺服器的記憶體中，因此，每當查詢TEXT資料時，MySQL都必須從磁碟讀取它，這與CHAR和VARCHAR相比要慢得多。

---

二進位制

binary(M)	1位元組，M的範圍[0, 255]	固定長度的二進位制資料（基於位元組，而char、varchar是基於字元）
varbinary(M)	4位元組，M的範圍[0,65535]	可變長度的二進位制資料

超過了資料型別的範圍： 

超大二進位制

大二進位制資料（儲存如圖片、PDF）

1. 關鍵字BLOB（binary large object）
2. 儲存超大二進位制資料

TINYBLOB	允許長度0~255（1位元組）
BLOB	允許長度0~65535（4位元組）
MEDIUMBLOB	允許長度0~16777215位元組（約16MB）
LONGBLOB	允許長度0~4294967295位元組（4GB）

---

JSON

5.7版本提供了對json格式的支援, type型別就是json.

建立表
create table json_test(
id int unsigned auto_increment primary key,
info json not null
);

插入資料
insert into json_test VALUES (null,'{"name":"jack","age":20,"gender":"male","height":180}');

---

列舉型別(enum)

理解：

1. 和C、Java語言中的列舉型別一樣
2. 如果確定某個欄位的資料只有那麼幾個值：如性別欄位的可選值只會為：男，女，保密。系統就可以在設定欄位的時候規定當前欄位只能存在固定的幾個值

系統提供了1到2個位元組來儲存列舉資料：

1. 通過計算enum列舉的具體值來選擇實際的儲存空間, 如果資料值列表在255以內, 1個位元組就夠了
2. 如果超過255但是小於65535, 那麼系統採用兩個位元組來儲存。

create table test_enum(
    gender enum('男','女','保密')
);

特性：

1. 只有插入enum設定的這幾個值才能插入成功, 否則的話系統會報錯.
2. 列舉enum的儲存原理：實際上欄位上所儲存的值並不是真正的字串，而是字串對應的下標；
3. 當系統設定列舉型別的時候，會給列舉中每個元素定義一個下標，這個下標規則從1開始Enum(1=>‘男’,2=>’女’,3=>’保密’).
4. 在MySQL中系統是自動進行型別轉換的：如果資料碰到“+、-、*、/”系統就會自動將資料轉換成數值：而普通字串轉換成數值為0。例如：Select 欄位名 + 0 from 表名.
5. 既然實際enum欄位儲存的結果是數值：那麼在進行資料插入的時候，就可以使用對應的數值來進行.

---

bit

用於儲存bit值，能夠儲存位元長度範圍為1~64，預設為1

快速理解bit的用法
CREATE TABLE test_bit
(
	id int PRIMARY KEY AUTO_INCREMENT,
	my_bit bit(5)
);

-- MySQL會自動將十進位制的4，轉為二進位制存入。
INSERT INTO test_bit (my_bit) VALUES (4);

-- 如果十進位制的數字轉換為二進位制，所得二進位制長度超過了規定的長度，報錯
INSERT INTO test_bit (my_bit) VALUES (44); -- 報錯： Data truncation: Data too long for column 'my_bit'

-- 直接插入二進位制
INSERT INTO test_bit (my_bit) VALUES (b'01011')

SELECT * FROM test_bit;

定義bit時的長度不能超過64：

---