集合（SET）
創(chuàng)新互聯(lián)自2013年起，是專業(yè)互聯(lián)網(wǎng)技術(shù)服務(wù)公司，擁有項目網(wǎng)站設(shè)計制作、成都網(wǎng)站制作網(wǎng)站策劃，項目實施與項目整合能力。我們以讓每一個夢想脫穎而出為使命，1280元譙城做網(wǎng)站,已為上家服務(wù),為譙城各地企業(yè)和個人服務(wù),聯(lián)系電話:028-86922220
mysql create table jihe(f1 set('f','m'));
Query OK, 0 rows affected (0.11 sec)
mysql desc jihe;
+-------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| f1 | set('f','m') | YES | | NULL | |
+-------+--------------+------+-----+---------+-------+
可以插入值 "f" "m"
mysql insert into jihe values("f");
Query OK, 1 row affected (0.56 sec)
mysql insert into jihe values("m");
Query OK, 1 row affected (0.19 sec)
可以按照序號輸入 注意序號為 1 2 4 8 16 32 ....
mysql insert into jihe values("1");
Query OK, 1 row affected (0.11 sec)
mysql insert into jihe values("2");
Query OK, 1 row affected (0.11 sec)
所以如果是3那插入的值為 第一個和第二個
mysql insert into jihe values("3");
Query OK, 1 row affected (0.17 sec)
+-------+
| f1 |
+-------+
| f |
| m |
| f |
| m |
| f,m |
+-------+
其他字母不能插入
mysql insert into jihe values("q");
ERROR 1265 (01000): Data truncated for column 'f1' at row 1
插入空
mysql insert into jihe values("0");
Query OK, 1 row affected (0.11 sec)
超出序號之和不能插入
mysql insert into jihe values("4");
ERROR 1265 (01000): Data truncated for column 'f1' at row 1
字段類型為集合，建議采用泛型，如SetUser . 不采用泛型，如Set也是可以的，賦值與取值時注意類型轉(zhuǎn)換即可。
字段類型為集合，對應(yīng)數(shù)據(jù)庫的列，最常見的情況就是多對多的關(guān)系，如用戶、角色，他們之間有個關(guān)聯(lián)表，關(guān)聯(lián)表中有2個字段，都是指向其他表（用戶表、角色表）的外鍵，那么在用戶對象就有SetRole屬性，在角色對象就有SetUser屬性。1對多的情況類似
MySQL 數(shù)據(jù)類型細分下來，大概有以下幾類：
數(shù)值，典型代表為 tinyint,int,bigint
浮點/定點，典型代表為 float,double,decimal 以及相關(guān)的同義詞
字符串，典型代表為 char,varchar
時間日期，典型代表為 date,datetime,time,timestamp
二進制，典型代表為 binary,varbinary
位類型
枚舉類型
集合類型
以下內(nèi)容，我們在另一篇文章介紹
大對象，比如 text,blob
json 文檔類型
一、數(shù)值類型（不是數(shù)據(jù)類型，別看錯了）如果用來存放整數(shù)，根據(jù)范圍的不同，選擇不同的類型。
以上是幾個整數(shù)選型的例子。整數(shù)的應(yīng)用范圍最廣泛，可以用來存儲數(shù)字，也可以用來存儲時間戳，還可以用來存儲其他類型轉(zhuǎn)換為數(shù)字后的編碼，如 IPv4 等。示例 1用 int32 來存放 IPv4 地址，比單純用字符串節(jié)省空間。表 x1，字段 ipaddr，利用函數(shù) inet_aton，檢索的話用函數(shù) inet_ntoa。
查看磁盤空間占用，t3 占用最大，t1 占用最小。所以說如果整數(shù)存儲范圍有固定上限，并且未來也沒有必要擴容的話，建議選擇最小的類型，當然了對其他類型也適用。root@ytt-pc:/var/lib/mysql/3305/ytt# ls -sihl總用量 3.0G3541825 861M -rw-r----- 1 mysql mysql 860M 12月 10 11:36 t1.ibd3541820 989M -rw-r----- 1 mysql mysql 988M 12月 10 11:38 t2.ibd3541823 1.2G -rw-r----- 1 mysql mysql 1.2G 12月 10 11:39 t3.ibd
二、浮點數(shù) / 定點數(shù)先說?浮點數(shù)，float 和 double 都代表浮點數(shù)，區(qū)別簡單記就是 float 默認占 4 Byte。float(p) 中的 p 代表整數(shù)位最小精度。如果 p 24 則直接轉(zhuǎn)換為 double，占 8 Byte。p 最大值為 53，但最大值存在計算不精確的問題。再說?定點數(shù)，包括 decimal 以及同義詞 numeric，定點數(shù)的整數(shù)位和小數(shù)位分別存儲，有效精度最大不能超過 65。所以區(qū)別于 float 的在于精確存儲，必須需要精確存儲或者精確計算的最好定義為 decimal 即可。示例 3創(chuàng)建一張表 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 所有未知的類型?？梢院唵握f是萬能類型！
char(10) 代表最大支持 10 個字符存儲，varhar(10) 雖然和 char(10) 可存儲的字符數(shù)一樣多，不同的是 varchar 類型存儲的是實際大小，char 存儲的理論固定大小。具體的字節(jié)數(shù)和字符集相關(guān)。示例 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)
所以在 char 和 varchar 選型上，要注意看是否合適的取值范圍。比如固定長度的值，肯定要選擇 char；不確定的值，則選擇 varchar。
四、日期類型日期類型包含了 date,time,datetime,timestamp，以及 year。year 占 1 Byte，date 占 3 Byte?！?/p>
time,timestamp,datetime 在不包含小數(shù)位時分別占用 3 Byte,4 Byte,8 Byte；小數(shù)位部分另外計算磁盤占用，見下面表格。
請點擊輸入圖片描述
注意：timestamp 代表的時間戳是一個 int32 存儲的整數(shù)，取值范圍為 '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. 如果時間有可能超過時間戳范圍，優(yōu)先選擇 datetime。2. 如果需要單獨獲取年份值，比如按照年來分區(qū)，按照年來檢索等，最好在表中添加一個 year 類型來參與。3. 如果需要單獨獲取日期或者時間，最好是單獨存放，而不是簡單的用 datetime 或者 timestamp。后面檢索時，再加函數(shù)過濾，以免后期增加 SQL 編寫帶來額外消耗。
4. 如果有保存毫秒類似的需求，最好是用時間類型自己的特性，不要直接用字符類型來代替。MySQL 內(nèi)部的類型轉(zhuǎn)換對資源額外的消耗也是需要考慮的。
示例 5
建立表 t5，對這些可能需要的字段全部分離開，這樣以后寫 SQL 語句的時候就很容易了。
當然了，這種情形占用額外的磁盤空間。如果想在易用性與空間占用量大這兩點來折中，可以用 MySQL 的虛擬列來實時計算。比如假設(shè) c5 字段不存在，想要得到 c5 的結(jié)果。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 對應(yīng)了 char 和 varchar 的二進制存儲，相關(guān)的特性都一樣。不同的有以下幾點：
binary(10)/varbinary(10) 代表的不是字符個數(shù)，而是字節(jié)數(shù)。
行結(jié)束符不一樣。char 的行結(jié)束符是 \0，binary 的行結(jié)束符是 0x00。
由于是二進制存儲，所以字符編碼以及排序規(guī)則這類就直接無效了。
示例 6
來看這個 binary 存取的簡單示例，還是之前的變量 @a。
切記！這里要提前計算好 @a 占用的字節(jié)數(shù)，以防存儲溢出。
六、位類型
bit 為 MySQL 里存儲比特位的類型，最大支持 64 比特位， 直接以二進制方式存儲，一般用來存儲狀態(tài)類的信息。比如，性別，真假等。具有以下特性：
1. 對于 bit(8) 如果單純存放 1 位，左邊以 0 填充 00000001。2. 查詢時可以直接十進制來過濾數(shù)據(jù)。3. 如果此字段加上索引，MySQL 不會自己做類型轉(zhuǎn)換，只能用二進制來過濾。
示例 7
創(chuàng)建表 c1, 字段性別定義一個比特位。mysql-(ytt/3305)-create table c1(gender bit(1));Query OK, 0 rows affected (0.02 sec)
mysql-(ytt/3305)-select cast(gender as unsigned) ?'f1' from c1;+------+| f1 ? |+------+| ? ?0 || ? ?1 |+------+2 rows in set (0.00 sec)
過濾數(shù)據(jù)也一樣，二進制或者直接十進制都行。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 非常優(yōu)化的一種用法。
mysql-(ytt/3305)-create table c2(gender char(0));Query OK, 0 rows affected (0.03 sec)
那現(xiàn)在我給表 c1 簡單的造點測試數(shù)據(jù)。
mysql-(ytt/3305)-select count(*) from c1;+----------+| count(*) |+----------+| 33554432 |+----------+1 row in set (1.37 sec)
把 c1 的數(shù)據(jù)全部插入 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.9G4085684 933M -rw-r----- 1 mysql mysql 932M 12月 11 10:16 c1.ibd4082686 917M -rw-r----- 1 mysql mysql 916M 12月 11 10:22 c2.ibd
檢索方式稍微有些不同，不過效率也差不多。所以說，字符類型不愧為萬能類型。
七、枚舉類型
枚舉類型，也即 enum。適合提前規(guī)劃好了所有已經(jīng)知道的值，且未來最好不要加新值的情形。枚舉類型有以下特性：
1. 最大占用 2 Byte。2. 最大支持 65535 個不同元素。3. MySQL 后臺存儲以下標的方式，也就是 tinyint 或者 smallint 的方式，下標從 1 開始。4. 排序時按照下標排序，而不是按照里面元素的數(shù)據(jù)類型。所以這點要格外注意。
示例 8
創(chuàng)建表 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)
八、集合類型
集合類型 SET 和枚舉類似，也是得提前知道有多少個元素。SET 有以下特點：
1. 最大占用 8 Byte，int64。2. 內(nèi)部以二進制位的方式存儲，對應(yīng)的下標如果以十進制來看，就分別為 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 c7WITH 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
九、數(shù)據(jù)類型在存儲函數(shù)中的用法
函數(shù)里除了顯式聲明的變量外，默認 session 變量的數(shù)據(jù)類型很弱，隨著給定值的不同隨意轉(zhuǎn)換。
示例 10
定義一個函數(shù)，返回兩個給定參數(shù)的乘積。定義里有兩個變量，一個是 v_tmp 顯式定義為 int64，另外一個 @vresult 隨著給定值的類型隨意變換類型。
簡單調(diào)用下。
mysql-(ytt/3305)-select ytt_sample_data_type(1111,222) 'result';+--------------------------+| result ? ? ? ? ? ? ? ? ? |+--------------------------+| The result is: '246642'. |+--------------------------+1 row in set (0.00 sec)
總結(jié)
本篇把 MySQL 基本的數(shù)據(jù)類型做了簡單的介紹，并且用了一些容易理解的示例來梳理這些類型。我們在實際場景中，建議選擇適合最合適的類型，不建議所有數(shù)據(jù)類型簡單的最大化原則。比如能用 varchar(100)，不用 varchar(1000)。
MySQL數(shù)據(jù)庫的表是一個二維表，由一個或多個數(shù)據(jù)列構(gòu)成。
每個數(shù)據(jù)列都有它的特定類型，該類型決定了MySQL如何看待該列數(shù)據(jù)，我們可以把整型數(shù)值存放到字符類型的列中，MySQL則會把它看成字符串來處理。
MySQL中的列類型有三種：數(shù)值類、字符串類和日期/時間類。
從大類來看列類型和數(shù)值類型一樣，都是只有三種。但每種列類型都還可細分。
下面對各種列類型進行詳細介紹。
數(shù)值類的數(shù)據(jù)列類型
數(shù)值型的列類型包括整型和浮點型兩大類。
TINYINT：1字節(jié) 非常小的正整數(shù)，帶符號：-128~127，不帶符號：0~255
SMALLINT：2字節(jié) 小整數(shù)，帶符號：-32768~32767，不帶符號：0~65535
MEDIUMINT：3字節(jié) 中等大小的整數(shù)，帶符號：-8388608~8388607，不帶符號：0~16777215
INT：4字節(jié) 標準整數(shù)，帶符號：-2147483648~2147483647，不帶符號：0~4294967295
BIGINT：8字節(jié) 大整數(shù)，帶符號：-9223372036854775808~9233372036854775807，不帶符號：0~18446744073709551615
FLOAT：4字節(jié) 單精度浮點數(shù)，最小非零值：+-1.175494351E-38，最大非零值：+-3.402823466E+38
DOUBLE：8字節(jié) 雙精度浮點數(shù)，最小非零值：+-2.2250738585072014E-308，最大非零值：+-1.7976931348623157E+308
DECIMAL：M+2字節(jié) 以字符串形式表示的浮點數(shù)，它的取值范圍可變，由M和D的值決定。
MYSQL支持大量的列類型，它們可以被分為 3 類：數(shù)字類型、日期和時間類型以及字符串(字符)類型。這個章節(jié)首先給出可用類型的概述，并且總結(jié)各類型所需的存儲需求，然后提供各類型中的類型范疇更詳細的描述。概述有意地簡化了。更詳細的說明應(yīng)該參考特寫列類型的附加信息，例如你能為其指定值的允許格式。
MySQL 支持的列類型在下面列出。下列代碼字母用于描述中：
M指出最大的顯示尺寸。最大的顯示尺寸長度為 255。D適用于浮點類型。指出跟隨在十進制小數(shù)點后的數(shù)字數(shù)量。最大可能值為 30，但不應(yīng)大于M-2。
方括號 (“[”and“]”) 指定可選的類型修飾部份。
注意，如果為一個列指定了ZEROFILL，MySQL 將自動為這個列添加UNSIGNED屬性。
警告：你應(yīng)該知道當在兩個整數(shù)類型值中使用減法時，如有一個為UNSIGNED類型，那么結(jié)果也是無符號的。查看章節(jié)6.3.5 Cast 函數(shù)。
TINYINT[(M)] [UNSIGNED] [ZEROFILL]-128到127。無符號的范圍是0到255。
BITBOOL它們是TINYINT(1)的同義詞。
SMALLINT[(M)] [UNSIGNED] [ZEROFILL]一個小整數(shù)。有符號的范圍是-32768到32767。無符號的范圍是0到65535。
MEDIUMINT[(M)] [UNSIGNED] [ZEROFILL]一個中等大小的整數(shù)。有符號的范圍是-8388608到8388607。無符號的范圍是0到16777215。
INT[(M)] [UNSIGNED] [ZEROFILL]一個正常大小的整數(shù)。有符號的范圍是-2147483648到2147483647。無符號的范圍是0到4294967295。
INTEGER[(M)] [UNSIGNED] [ZEROFILL]INT的同義詞。
BIGINT[(M)] [UNSIGNED] [ZEROFILL]一個大的整數(shù)。有符號的范圍是-9223372036854775808到9223372036854775807。無符號的范圍是0到18446744073709551615。
你應(yīng)該知道的有關(guān)BIGINT列的一些事情：
BIGINT或DOUBLE值來完成的，因此你不應(yīng)該使用大于9223372036854775807(63 bits) 的無符號大整數(shù)，除了位函數(shù)之外！如果你這樣做了，結(jié)果中的某些大數(shù)字可能會出錯，因為將BIGINT轉(zhuǎn)換成DOUBLE時產(chǎn)生了舍入錯誤。MySQL 4.0 在下列情況下可以處理BIGINT：
在一個BIGINT列中使用整數(shù)存儲一個大的無符號值。
在MIN(big_int_column)和MAX(big_int_column)中。
當兩個操作數(shù)都是整數(shù)時使用操作符 (+、-、*、等)。
通常你可以在一個BIGINT列中以字符串方式存儲的一個精確的整數(shù)。在這種情況下，MySQL 將執(zhí)行一個字符串到數(shù)字的轉(zhuǎn)換，包括無 intermediate 的雙精度表示法。
當兩個參數(shù)均是整數(shù)值時，“-”、“+”和“*”將使用BIGINT運算！這就意味著，如果兩個大整數(shù)的乘積(或函數(shù)的結(jié)果返回整數(shù))的結(jié)果大于9223372036854775807時，你可能會得到意想不到的結(jié)果。
FLOAT(precision) [UNSIGNED] [ZEROFILL]一個浮點型數(shù)字。
precision可以是=24作為一個單精度的浮點數(shù)字和介于 25 和 53 之間作為一個雙精度的浮點數(shù)字。這些類型與下面描述的FLOAT和DOUBLE類型相似。FLOAT(X)有與相應(yīng)的FLOAT和DOUBLE類型同樣的范圍，但是顯示尺寸和十進制小數(shù)位數(shù)是未定義的。在 MySQL 3.23 中，它是一個真實的浮點值。而在 MySQL 早期的版本中，F(xiàn)LOAT(precision)通常有 2 小數(shù)位。 注意，由于在 MySQL 中所有的計算都是以雙精度執(zhí)行的，所以使用FLOAT可能帶來一些意想不到的問題。查看章節(jié)A.5.6 解決沒有匹配行的問題。
FLOAT[(M,D)] [UNSIGNED] [ZEROFILL]一個小的(單精度) 浮點數(shù)字。允許的值是-3.402823466E+38到-1.175494351E-38、0和1.175494351E-38到3.402823466E+38。如果UNSIGNED被指定，負值是不允許的。M是顯示寬度，D是小數(shù)位數(shù)。FLOAT沒有參數(shù)或有X= 24 的FLOAT(X)代表一個單精度的浮點數(shù)字。
DOUBLE[(M,D)] [UNSIGNED] [ZEROFILL]一個正常大小的(雙精度)浮上數(shù)字。允許的值是-1.7976931348623157E+308到-2.2250738585072014E-308、0和2.2250738585072014E-308到1.7976931348623157E+308。如果UNSIGNED被指定，負值是不允許的。M是顯示寬度，D是小數(shù)位數(shù)。DOUBLE沒胡參數(shù)或有 25 =X= 53 的FLOAT(X)代表一個雙精度的浮點數(shù)字。
DOUBLE PRECISION[(M,D)] [UNSIGNED] [ZEROFILL]
REAL[(M,D)] [UNSIGNED] [ZEROFILL]它們是DOUBLE同義詞。
DECIMAL[(M[,D])] [UNSIGNED] [ZEROFILL]一個未壓縮(unpacked)的浮點數(shù)。運作如同一個CHAR列：“unpacked” 意味著數(shù)字是以一個字符串存儲的，值的每一位將使用一個字符。小數(shù)點并且對于負數(shù)，“-”符號不在M中計算(但是它們的空間是被保留的)。如果D是 0，值將沒有小數(shù)點或小數(shù)部份。DECIMAL值的最大范圍與DOUBLE一致，但是對于一個給定的DECIMAL列，實際的范圍可以被所選擇的M和D限制。如果UNSIGNED被指定，負值是不允許的。 如果D被忽略，缺省為 0。如果M被忽略，缺省為 10。 在 MySQL 3.23 以前，M參數(shù)必須包含符號與小數(shù)點所需的空間。
DEC[(M[,D])] [UNSIGNED] [ZEROFILL]
NUMERIC[(M[,D])] [UNSIGNED] [ZEROFILL]DECIMAL的同義詞。
DATE一個日期。支持的范圍是'1000-01-01'到'9999-12-31'。MySQL 以'YYYY-MM-DD'格式顯示DATE值，但是允許你以字符串或數(shù)字給一個DATE列賦值。查看章節(jié)6.2.2.2DATETIME、DATE和TIMESTAMP類型。
DATETIME一個日期和時間的組合。支持的范圍是'1000-01-01 00:00:00'到'9999-12-31 23:59:59'。MySQL 以'YYYY-MM-DD HH:MM:SS'格式顯示DATETIME值，但是允許你以字符串或數(shù)字給一個DATETIME列賦值。查看章節(jié)6.2.2.2DATETIME、DATE和TIMESTAMP類型。
TIMESTAMP[(M)]一個時間戳。范圍是'1970-01-01 00:00:00'到2037年間的任意時刻。 MySQL 4.0 和更早版本中，TIMESTAMP值是以YYYYMMDDHHMMSS、YYMMDDHHMMSS、YYYYMMDD或YYMMDD格式顯示的，它取決于M是否是14(或省略)、12、8或6，但是允許你以字符串或數(shù)字給一個TIMESTAMP列賦值。 從 MySQL 4.1 開始，TIMESTAMP以'YYYY-MM-DD HH:MM:DD'格式作為字符返回。如果你你希望以數(shù)字形式返回則必須在該時間戳字段后加上 +0。不同的時間戳長度是不支持的。從 MySQL 4.0.12 開始，選項--new可以被用來使服務(wù)器與 4.1 一樣運作。TIMESTAMP列有益于記錄一個INSERT或UPDATE操作的日期和時間，因為如果你自己沒有給它賦值，它將被自動地設(shè)置為最近一次操作的日期和時間。也可以通過給它賦一個NULL而使它設(shè)置為當前的日期和時間。查看章節(jié)6.2.2 Date 和 Time 類型。 參數(shù)M只影響一個TIMESTAMP列的顯示格式；它的值總是占用 4 個字節(jié)存儲。 注意，當TIMESTAMP(M)列的M是 8 或 14 時，它返回的是數(shù)字而其它的TIMESTAMP(M)列返回的是字符串。這僅僅是為了可以可靠地轉(zhuǎn)儲并恢復到其它格式的表中。查看章節(jié)6.2.2.2DATETIME、DATE和TIMESTAMP類型。TIME一個時間。范圍是'-838:59:59'到'838:59:59'。MySQL 以'HH:MM:SS'格式顯示TIME值，但是允許你使用字符串或數(shù)字來給TIME列賦值。查看章節(jié)6.2.2.3TIME類型。YEAR[(2|4)]一個 2 或 4 位數(shù)字格式的年(缺省為 4 位)。允許的值是1901到2155、0000(4 位年格式) 以及使用 2 位格式的 1970-2069 (70-69)。MySQL 以YYYY格式顯示YEAR值，但是允許你使用字符串或數(shù)字來給YEAR列賦值。(YEAR類型在 MySQL 3.22 之前不支持。) 查看章節(jié)6.2.2.4YEAR類型。
[NATIONAL] CHAR(M) [BINARY]一個定長的字符串，當存儲時，總是以空格填滿右邊到指定的長度。M的范圍是 0 到 255 (在 MySQL 3.23 版本之前為 1 到 255)。當該值被檢索時，尾部空格將被刪除。CHAR值根據(jù)缺省的字符集進行忽略大小寫的排索與比較，除非指定了關(guān)鍵詞BINARY。NATIONAL CHAR(或短形式NCHAR) 是以 ANSI SQL 方式定義一個CHAR列，它將使用缺省的字符集。這在 MySQL 中是默認的。
CHAR是CHARACTER的縮寫。 MySQL 允許以CHAR(0)類型建立一個列。一些老程序運行時必需一個列，卻又并不使用這個列的值，你就不得不為了適應(yīng)它而建立該列，在這情況下，CHAR(0)將是很有益的。當需要一個列僅保存兩個值時：一個為CHAR(0)(該列沒有定義為NOT NULL)，這將僅占用一個比特位來存儲 2 個值：NULL或""。查看章節(jié)6.2.3.1CHAR和VARCHAR類型。CHAR這是CHAR(1)的同義詞。
[NATIONAL] VARCHAR(M) [BINARY]一個變長的字符串。注意：尾部的空格在存儲時將會被刪除(這與 ANSI SQL 約規(guī)不同)。M的范圍是 0 到 255 (在 MySQL 4.0.2 之前的版本中是 1 到 255)。
VARCHAR值以大小寫忽略方式進行排索與比較，除非關(guān)鍵詞BINARY被指定。查看章節(jié)6.5.3.1 隱式的列定義變化。VARCHAR是CHARACTER VARYING的縮寫。查看章節(jié)6.2.3.1CHAR和VARCHAR類型。
TINYBLOBTINYTEXT一個BLOB或TEXT列，最大長度為 255 (2^8 - 1) 個字符。查看章節(jié)6.5.3.1 隱式的列定義變化。查看章節(jié)6.2.3.2BLOB和TEXT類型。
BLOBTEXT一個BLOB或TEXT列，最大長度為 65535 (2^16 - 1) 個字符。查看章節(jié)6.5.3.1 隱式的列定義變化。查看章節(jié)6.2.3.2BLOB和TEXT類型。
MEDIUMBLOBMEDIUMTEXT一個BLOB或TEXT列，最大長度為 16777215 (2^24 - 1) 個字符。查看章節(jié)6.5.3.1 隱式的列定義變化。查看章節(jié)6.2.3.2BLOB和TEXT類型。
LONGBLOBLONGTEXT一個BLOB或TEXT列，最大長度為 4294967295 (2^32 - 1) 個字符。查看章節(jié)6.5.3.1 隱式的列定義變化。注意，由于服務(wù)器/客戶端的協(xié)議以及 MyISAM 表通常有一個 16M 每通信包/表行的限制，你仍然不能使用這個類型的整個范圍。查看章節(jié)6.2.3.2BLOB和TEXT類型。ENUM('value1','value2',...)一個枚舉類型。一個僅能有一個值的字符串對象，這個值選自值列'value1'、'value2'、...、NULL或特殊的""出錯值。一個ENUM列可以有最大 65535 不同的值。查看章節(jié)6.2.3.3ENUM類型。SET('value1','value2',...)一個集合。一個能有零個或更多個值的字符串對象，其中每個值必須選自值列'value1'、'value2'、...。一個SET列可以有最大 64 個成員。查看章節(jié)6.2.3.4SET類型。
MySQL 支持所有的 ANSI/ISO SQL92 數(shù)字類型。這些類型包括準確數(shù)字的數(shù)據(jù)類型(NUMERIC、DECIMAL、INTEGER和SMALLINT)，也包括近似數(shù)字的數(shù)據(jù)類型(FLOAT、REAL和DOUBLE PRECISION)。關(guān)鍵詞INT是INTEGER的同義詞，關(guān)鍵詞DEC是DECIMAL的同義詞。
NUMERIC和DECIMAL類型被 MySQL 以同樣的類型實現(xiàn)，這在 SQL92 標準中是允許的。他們用于保存對準確精度有重要要求的值，例如與金錢有關(guān)的數(shù)據(jù)。當以它們中的之一聲明一個列時，精度和數(shù)值范圍可以(通常是)被指定；例如：
salary DECIMAL(5,2)
在這個例子中，5(精度(precision)) 代表重要的十進制數(shù)字的數(shù)目，2(數(shù)據(jù)范圍(scale)) 代表在小數(shù)點后的數(shù)字位數(shù)。在這種情況下，因此，salary列可以存儲的值范圍是從-99.99到99.99。(實際上 MySQL 在這個列中可以存儲的數(shù)值可以一直到999.99，因為它沒有存儲正數(shù)的符號)。
譯者注：
M 與D 對DECIMAL(M, D) 取值范圍的影響
類型說明 取值范圍（MySQL 3.23） 取值范圍（MySQL = 3.23）
DECIMAL(4, 1) -9.9 到 99.9 -999.9 到 9999.9
DECIMAL(5, 1) -99.9 到 999.9 -9999.9 到 99999.9
DECIMAL(6, 1) -999.9 到 9999.9 -99999.9 到 999999.9
DECIMAL(6, 2) -99.99 到 999.99 -9999.99 到 99999.99
DECIMAL(6, 3) -9.999 到 99.999 -999.999 到 9999.999
# 在MySQL 3.23 及以后的版本中，DECIMAL(M, D) 的取值范圍等于早期版本中的DECIMAL(M + 2, D) 的取值范圍。注釋結(jié)束：
在 ANSI/ISO SQL92 中，句法DECIMAL(p)等價于DECIMAL(p,0)。同樣的，在執(zhí)行被允許決定值p的地方,句法DECIMAL等價于DECIMAL(p,0)。MySQL 目前還不支持DECIMAL/NUMERIC數(shù)據(jù)類型的這些變體形式中的任一種。一般來說這并不是一個嚴重的問題，通過明確地控制精度和數(shù)值范圍可以得到這些類型的主要功能益處。
DECIMAL和NUMERIC值是作為字符串存儲的，而不是作為二進制浮點數(shù)，以便保護這些值的十進制精確度。一個字符用于數(shù)值的每一位、小數(shù)點(如果scale 0) 和“-”符號(對于負值)。如果scale是 0，DECIMAL和NUMERIC值不包含小數(shù)點或小數(shù)部分。
DECIMAL和NUMERIC值的最大范圍與DOUBLE一致，但是對于一個給定的DECIMAL或NUMERIC列，它的實際范圍可制定該列時的precision或scale限制。當這樣的列被賦給了小數(shù)點的位數(shù)超過scale所指定的值時，該將根據(jù)scale進行四舍五入。當一個DECIMAL或NUMERIC列被賦與一個大小超過指定(或缺省)的precisionandscale的限止范圍時，MySQL 以該列范圍的端點值存儲該值。
Mysql支持的多種數(shù)據(jù)類型主要有：數(shù)值數(shù)據(jù)類型、日期/時間類型、字符串類型。?
1.整數(shù)數(shù)據(jù)類型及其取值范圍：
類型
說明
存儲需求（取值范圍）
tinyint ? ?很小整數(shù) ? ?1字節(jié)（[0~255]、[-128~127]）; 255=2^8-1;127=2^7-1 ?
smallint ? ?小整數(shù) ? ?2字節(jié)（0~65535、-32768~32767） ;65535=2^16-1 ?
mediumint ? ?中等 ? ?3字節(jié)（0~16777215） ;16777215=2^24-1 ?
int(integer) ? ?普通 ? ?4字節(jié)（0~4294967295） ;4294967295=2^32-1 ?
bigint ? ?大整數(shù) ? ?8字節(jié)（0~18446744073709551615）;18446744073709551615=2^64-1 ?
浮點數(shù)定點數(shù)：
類型名稱
說明
存儲需求
float ? ?單精度浮點數(shù) ? ?4字節(jié) ?
double ? ?雙精度浮點數(shù) ? ?8字節(jié) ?
decimal ? ?壓縮的“嚴格”定點數(shù) ? ?M+2字節(jié) ?
注：定點數(shù)以字符串形式存儲，對精度要求高時使用decimal較好；盡量避免對浮點數(shù)進行減法和比較運算。?
2.時間/日期類型：?
year范圍：1901~2155;?
time格式：‘HH:MM:SS’（如果省略寫，并且沒有冒號，則默認最右起2位為秒，再到分，最后到時）；?
插入系統(tǒng)當前時間：insert into 表名 values(current_date()),(now());?
date類型：‘YYYY-MM-DD’；?
datetime（日期+時間）：‘YYYY-MM-DD HH:MM:SS’或‘YYYYMMDDHHMMSS’，取值范圍：‘1000-01-01 00:00:00’~‘9999-12-31 23:59:59’；?
timestamp格式同datetime，但在存儲時需要4個字節(jié)(datetime需要8字節(jié))，并且以UTC（世界標準時間）進行存儲（即timestamp會隨設(shè)置的時區(qū)而變化，而datetime存儲的絕不會變化）；timestamp的范圍：1970-2037。?
3.字符串類型：?
text類型：tinytext、text、mediumtext、longtext;
類型
范圍
tinytext ? ?255=2^8-1 ?
text ? ?65535=2^16-1 ?
mediumtext ? ?16777215=2^24-1 ?
longtext ? ?4294967295=4GB=2^32-1 ?
char的存儲需求是定義時指定的固定長度；varchar的存儲需求是取決于實際值長度。?
set類型格式：set（’值1’,’值2’…) ——可以有0或者多個值，對于set而言，若插入的值為重復的，則只娶一個。插入的值亂序，則自動按順序插入排列。插入不正常值，則忽略。?
二進制類型：?
bit（M）——保存位字段值（位字段類型），M表示值的位數(shù)；?
eg:select BIN(b+0) from 表名；—–b為列名;b+0表示將二進制的結(jié)果轉(zhuǎn)換為對應(yīng)的數(shù)字的值，BIN（）函數(shù)將數(shù)字轉(zhuǎn)換為二進制。?
blog——-二進制大對象，用來存儲可變數(shù)量的數(shù)據(jù)。
數(shù)據(jù)類型
存儲范圍(字節(jié))
tinyblog ? ?最多255=2^8-1 字節(jié) ?
bolg ? ?最多65535=2^16-1 字節(jié) ?
mediumblog ? ?最多16777215=2^24-1 字節(jié) ?
longblog ? ?最多4294967295=4GB=2^32-1 字節(jié) ?
MySQL 數(shù)據(jù)類型細分下來，大概有以下幾類：
數(shù)值，典型代表為 tinyint,int,bigint
浮點/定點，典型代表為 float,double,decimal 以及相關(guān)的同義詞
字符串，典型代表為 char,varchar
時間日期，典型代表為 date,datetime,time,timestamp
二進制，典型代表為 binary,varbinary
位類型
枚舉類型
集合類型
大對象，比如 text,blob
json 文檔類型
一、數(shù)值類型（不是數(shù)據(jù)類型，別看錯了）如果用來存放整數(shù)，根據(jù)范圍的不同，選擇不同的類型。
以上是幾個整數(shù)選型的例子。整數(shù)的應(yīng)用范圍最廣泛，可以用來存儲數(shù)字，也可以用來存儲時間戳，還可以用來存儲其他類型轉(zhuǎn)換為數(shù)字后的編碼，如 IPv4 等。示例 1用 int32 來存放 IPv4 地址，比單純用字符串節(jié)省空間。表 x1，字段 ipaddr，利用函數(shù) inet_aton，檢索的話用函數(shù) inet_ntoa。
查看磁盤空間占用，t3 占用最大，t1 占用最小。所以說如果整數(shù)存儲范圍有固定上限，并且未來也沒有必要擴容的話，建議選擇最小的類型，當然了對其他類型也適用。root@ytt-pc:/var/lib/mysql/3305/ytt# ls -sihl總用量 3.0G3541825 861M -rw-r----- 1 mysql mysql 860M 12月 10 11:36 t1.ibd3541820 989M -rw-r----- 1 mysql mysql 988M 12月 10 11:38 t2.ibd3541823 1.2G -rw-r----- 1 mysql mysql 1.2G 12月 10 11:39 t3.ibd
二、浮點數(shù) / 定點數(shù)先說?浮點數(shù)，float 和 double 都代表浮點數(shù)，區(qū)別簡單記就是 float 默認占 4 Byte。float(p) 中的 p 代表整數(shù)位最小精度。如果 p 24 則直接轉(zhuǎn)換為 double，占 8 Byte。p 最大值為 53，但最大值存在計算不精確的問題。再說?定點數(shù)，包括 decimal 以及同義詞 numeric，定點數(shù)的整數(shù)位和小數(shù)位分別存儲，有效精度最大不能超過 65。所以區(qū)別于 float 的在于精確存儲，必須需要精確存儲或者精確計算的最好定義為 decimal 即可。示例 3創(chuàng)建一張表 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 所有未知的類型?？梢院唵握f是萬能類型！
char(10) 代表最大支持 10 個字符存儲，varhar(10) 雖然和 char(10) 可存儲的字符數(shù)一樣多，不同的是 varchar 類型存儲的是實際大小，char 存儲的理論固定大小。具體的字節(jié)數(shù)和字符集相關(guān)。示例 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)
所以在 char 和 varchar 選型上，要注意看是否合適的取值范圍。比如固定長度的值，肯定要選擇 char；不確定的值，則選擇 varchar。
四、日期類型日期類型包含了 date,time,datetime,timestamp，以及 year。year 占 1 Byte，date 占 3 Byte?！?/p>
time,timestamp,datetime 在不包含小數(shù)位時分別占用 3 Byte,4 Byte,8 Byte；小數(shù)位部分另外計算磁盤占用，見下面表格。
請點擊輸入圖片描述
請點擊輸入圖片描述
請點擊輸入圖片描述
注意：timestamp 代表的時間戳是一個 int32 存儲的整數(shù)，取值范圍為 '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. 如果時間有可能超過時間戳范圍，優(yōu)先選擇 datetime。2. 如果需要單獨獲取年份值，比如按照年來分區(qū)，按照年來檢索等，最好在表中添加一個 year 類型來參與。3. 如果需要單獨獲取日期或者時間，最好是單獨存放，而不是簡單的用 datetime 或者 timestamp。后面檢索時，再加函數(shù)過濾，以免后期增加 SQL 編寫帶來額外消耗。
4. 如果有保存毫秒類似的需求，最好是用時間類型自己的特性，不要直接用字符類型來代替。MySQL 內(nèi)部的類型轉(zhuǎn)換對資源額外的消耗也是需要考慮的。
示例 5
建立表 t5，對這些可能需要的字段全部分離開，這樣以后寫 SQL 語句的時候就很容易了。
當然了，這種情形占用額外的磁盤空間。如果想在易用性與空間占用量大這兩點來折中，可以用 MySQL 的虛擬列來實時計算。比如假設(shè) c5 字段不存在，想要得到 c5 的結(jié)果。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 對應(yīng)了 char 和 varchar 的二進制存儲，相關(guān)的特性都一樣。不同的有以下幾點：
binary(10)/varbinary(10) 代表的不是字符個數(shù)，而是字節(jié)數(shù)。
行結(jié)束符不一樣。char 的行結(jié)束符是 \0，binary 的行結(jié)束符是 0x00。
由于是二進制存儲，所以字符編碼以及排序規(guī)則這類就直接無效了。
示例 6
來看這個 binary 存取的簡單示例，還是之前的變量 @a。
切記！這里要提前計算好 @a 占用的字節(jié)數(shù)，以防存儲溢出。
六、位類型
bit 為 MySQL 里存儲比特位的類型，最大支持 64 比特位， 直接以二進制方式存儲，一般用來存儲狀態(tài)類的信息。比如，性別，真假等。具有以下特性：
1. 對于 bit(8) 如果單純存放 1 位，左邊以 0 填充 00000001。2. 查詢時可以直接十進制來過濾數(shù)據(jù)。3. 如果此字段加上索引，MySQL 不會自己做類型轉(zhuǎn)換，只能用二進制來過濾。
示例 7
創(chuàng)建表 c1, 字段性別定義一個比特位。mysql-(ytt/3305)-create table c1(gender bit(1));Query OK, 0 rows affected (0.02 sec)
mysql-(ytt/3305)-select cast(gender as unsigned) ?'f1' from c1;+------+| f1 ? |+------+| ? ?0 || ? ?1 |+------+2 rows in set (0.00 sec)
過濾數(shù)據(jù)也一樣，二進制或者直接十進制都行。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 非常優(yōu)化的一種用法。
mysql-(ytt/3305)-create table c2(gender char(0));Query OK, 0 rows affected (0.03 sec)
那現(xiàn)在我給表 c1 簡單的造點測試數(shù)據(jù)。
mysql-(ytt/3305)-select count(*) from c1;+----------+| count(*) |+----------+| 33554432 |+----------+1 row in set (1.37 sec)
把 c1 的數(shù)據(jù)全部插入 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.9G4085684 933M -rw-r----- 1 mysql mysql 932M 12月 11 10:16 c1.ibd4082686 917M -rw-r----- 1 mysql mysql 916M 12月 11 10:22 c2.ibd
檢索方式稍微有些不同，不過效率也差不多。所以說，字符類型不愧為萬能類型。
七、枚舉類型
枚舉類型，也即 enum。適合提前規(guī)劃好了所有已經(jīng)知道的值，且未來最好不要加新值的情形。枚舉類型有以下特性：
1. 最大占用 2 Byte。2. 最大支持 65535 個不同元素。3. MySQL 后臺存儲以下標的方式，也就是 tinyint 或者 smallint 的方式，下標從 1 開始。4. 排序時按照下標排序，而不是按照里面元素的數(shù)據(jù)類型。所以這點要格外注意。
示例 8
創(chuàng)建表 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)
八、集合類型
集合類型 SET 和枚舉類似，也是得提前知道有多少個元素。SET 有以下特點：
1. 最大占用 8 Byte，int64。2. 內(nèi)部以二進制位的方式存儲，對應(yīng)的下標如果以十進制來看，就分別為 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 c7WITH 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
九、數(shù)據(jù)類型在存儲函數(shù)中的用法
函數(shù)里除了顯式聲明的變量外，默認 session 變量的數(shù)據(jù)類型很弱，隨著給定值的不同隨意轉(zhuǎn)換。
示例 10
定義一個函數(shù)，返回兩個給定參數(shù)的乘積。定義里有兩個變量，一個是 v_tmp 顯式定義為 int64，另外一個 @vresult 隨著給定值的類型隨意變換類型。
簡單調(diào)用下。
mysql-(ytt/3305)-select ytt_sample_data_type(1111,222) 'result';+--------------------------+| result ? ? ? ? ? ? ? ? ? |+--------------------------+| The result is: '246642'. |+--------------------------+1 row in set (0.00 sec)
總結(jié)
本篇把 MySQL 基本的數(shù)據(jù)類型做了簡單的介紹，并且用了一些容易理解的示例來梳理這些類型。我們在實際場景中，建議選擇適合最合適的類型，不建議所有數(shù)據(jù)類型簡單的最大化原則。比如能用 varchar(100)，不用 varchar(1000)。
重慶分公司
重慶分公司
