數據類型（列類型）

• 所謂的數據類型：對數據進行統一的分類，從系統的角度出發為了能夠使用統一的方式進行管理；更好的利用有限的空間
• SQL中將數據類型分成了三大類：數值類型，字符串類型和時間日期類型

數值型

• 數值型數據：都是數值
• 系統將數值型分為整數型和小數型
• 整數型
• 存儲整型數據：在SQL中因為更多要考慮如何節省磁盤空間，所以系統將整型又細分為5類：
  • Tinyint：迷你整型，使用一個字節存儲，表示的狀態最多為256種（常用）
  • Smallint：小數型：使用兩個字節存儲，表示的狀態最多為65536種
  • Mediumint：中整型，使用三個字節存儲
  • Int：標準整型，使用四個字節存儲（常用）
  • Bigint：大整型，使用八個字節存儲

• .eg

  1 2 3 4 5 6 7 8 9 10 11 12

  # 創建整型表 create table if not exists my_int( int_1 tinyint, int_2 smallint, int_3 int, int_4 bigint )charset utf8; # 插入數據 insert into my_int values(100, 100, 100, 100); -- 有效數據 insert into my_int values('a', 'b', '199', 'f'); -- 無效數據：類型限定(error: Incorrect integer value: 'a' for column 'int_1' at row 1) insert into my_int values(255, 10000, 100000, 10000000); -- 無效數據：超出範圍(error: Out of range value for column 'int_1' at row 1)

• SQL中的數值類型全部都是默認有符號：分正負

• 有時候需要使用無符號數據：需要給數據類型限定：int unsigned; – 無符號，從0開始

  1 2	# 給表添加一個無符號類型 alter table my_int add int_5 tinyint unsigned; -- 無符號類型

• 查看表結構的時候，發現每個字段的數據類型之後都會自帶一個括號，裡面有指定的數字

• 其作用：是數據最終顯示的位數如：-123是4位顯示寬度（包含符號）沒有特別的含義，只是默認顯示的告訴用戶可以顯示的形式而已，實際上用戶是可以控制的，這種控制不會改變數據本身的大小

  1 2	alter table my_int add int_6 tinyint(1) unsigned; -- 指定顯示寬度為1； insert into my_int values(127,0,0,0,255,277);

• 顯示寬度的意義：在於當數據不夠顯示寬度的時候，會自動讓數據變成對應的顯示寬度；通常需要搭配一個前導0來增加寬度，不改變值大小：zerofill(零填充)，零填充會導致數值自動變成無符號

  1	alter table my_int add int_7 tinyint(2) zerofill; -- 顯示寬度為2，0填充

• 零填充的意義（顯示寬度）：保證數據格式

小數型

• 小數型：帶有小數點或者範圍超出整型的數值類型

• SQL中：將小數型細分為兩種：浮點型和定點型

  • 浮點型：小數點浮動，精度有限，而且會丟失精度
  • 定點型：小數點固定，精度固定，不會丟失精度

• 浮點型：

• 浮點型數據是一種精度型數據：因為超出指定範圍之後，會丟失精度（自動四捨五入）
• 浮點型：理論分為兩種精度
• Float:但精度，佔用4個字節存儲數據，精度範圍大概為7位左右
• Double:雙精度，佔用8個字節存儲數據，精度範圍大概為15位左右
  | 類型 | 存儲空間(字節) | 最小值(理論) | 最大值(理論) |
  | :-: | :-: | :-: | :-: |
  | FLOAT | 4 | -3.402823466E+38 | 3.402823466E+38 |
  | DOUBLE | 8 | -1.797693`348623157E+308 | 1.7976931348623157E+308 |

• 創建浮點數表：直接float表示沒有小數部分；float(M,D):M代表總長度，D代表小數部分長度，整數部分長度位M-D

  1 2 3 4 5 6

  # 創建浮點數表 create table if not exists my_float( f1 float, f2 float(10, 2), -- 10位在精度範圍以外 f3 float(6, 2) -- 6位在精度範圍之內 )charset utf8;

• 插入數據：可以是直接小數，也可以是科學計數法

  1 2 3 4 5

  # 插入數據 insert into my_float values(1000.10, 1000.10, 1000.10); -- 符合條件 insert into my_float values(1234567890, 12345678.90, 1234.56); -- 符合條件 insert into my_float values(3e38, 3.01e7, 1234.56); insert into my_float values(9999999999, 99999999.99, 9999.99); -- 最大值

• 浮點型數據的插入：整型部分是不能超出長度的，單小數部分可以超出長度（系統會自動四捨五入）

  1 2 3

  # 超出長度插入數據 insert into my_float values(123456, 1234,12345678, 123.9876543); -- 小數部分超出範圍（系統自動四捨五入） insert into my_float values(123456, 1234.12, 12345.56); -- 整數部分超出範圍（Out of range value for column 'f3' at row 1）

• 結果：浮點數一定會進行四捨五入（超出精度範圍）：浮點數如果是應為系統進位導致整數部分超出指定的長度，那麼系統也允許成立

定點型

• 絕對保證整數部分不會被四捨五入（不會丟失精度），小數部分有可能（理論小數部分也不會丟失精度）

• 創建定點數表：以浮點數作為對比

  1 2 3 4

  create table if not exists my_decimal( f1 float(10, 2), d1 decimal(10, 2) )charset utf8;

• 插入數據：定點數的整數部分一定不能超出長度（進位不可以），小數部分的長度可以隨意超出（系統自動四捨五入）

  1 2	insert into my_decimal values(12345678.90, 12345678.90); -- 有效數據 insert into my_decimal values(1234.123456, 1234.123456); -- 小數部分超出（允許：有警告）

• 查看警告：show warnings;

• 浮點數如果進位導致長度溢出沒有問題，但是定位數不行

  1 2	insert into my_decimal values(99999999.99, 99999999.99); -- 沒有問題 insert into my_decimal values(99999999.99, 99999999.999); -- 進位超出（Out of range value for column 'd1' at row 1）

• 查看數據效果

時間日期類型

• Datatime：時間日期類型，格式是YYYY-mm-dd HH:ii:ss; 表示的範圍是從1000到9999年，有0值：0000-00-00 00:00:00
• Date：日期，就是datetime中的date部分
• Time：時間段，指定的某個區間之間，-時間 +時間
• TimeTamp：時間戳，並不是時間戳，只是從1970年開始的YYYY-mm-dd HH:ii:ss 格式與datetime完全一致
• Year：年份，兩種形式，year(2)和year(4)：表示1901年到2155年

• DATETIME, TIMESTAMP, DATE，在存儲數據時，針對數據格式與合法性進行驗證，不對日期的時間合法性進行驗證

• 創建時間日期表

  1 2 3 4 5 6 7

  create table if not exists my_date( d1 datetime, d2 date, d3 time, d4 timestamp, d5 year )charset utf8;

• 插入數據：時間time可以為負數，而且可以是很大的負數，year可以使用2位數插入，也可以使用4位數

  1	insert into my_date values('2015-10-01 11:33:56', '2015-06-12', '11:33:56', '2015-10-01 11:33:56', 2015);

1
2
3

# 時間使用負數
insert into my_date values('2015-10-01 11:33:56', '2015-06-12', '-211:33:56', '2015-10-01 11:33:56', 2015);
insert into my_date values('2015-10-01 11:33:56', '2015-06-12', '-2 11:33:56', '2015-10-01 11:33:56', 2015);

1
2
3

# year可以使用2位或者4位
insert into my_date values('2015-10-01 11:33:56', '2015-06-12', '-211:33:56', '2015-10-01 11:33:56', 69);
insert into my_date values('2015-10-01 11:33:56', '2015-06-12', '-211:33:56', '2015-10-01 11:33:56', 70);

• Timestamp字段：只要當前所在的紀錄被更新，該字段一定會自動更改為當前時間

  1 2	# timesTamp: 修改紀錄 update my_date set d1 = '2015-09-28 11:23:23' where d5 = 2069;

• 網站是以PHP為實現的主要操作對象：PHP中有非常強大的時間日期處理函數：date，只需要一個時間戳就可以轉換成任意類型的時間：以PHP為主的時候，都是在數據庫使用時間戳（整型）來存儲時間

字符串類型

• 在SQL中，將字符串類型分為6類：char, varchar, text, blob, enum 和 set
• 定長字符串
• 定長字符串：char，磁盤（二維表）在定義結構的時候，就已經確定了最終數據的存儲長度
  • Char(L):L代表length，可以存儲的長度，單位為字符，最大長度值可以為255
  • Char(4):在UTF8環境下，需要4 * 3 = 12個字節
• 變長字符串
• 變長字符串：varchar，在分配空間的時候，按照最大的空間分配；但是實際上最終用了多少，是根據具體的數據來確定
  • Varchar(L):L代表字符長度 理論長度是65536個字節，但是會多出1到2個字節來確定存儲的實際長度；但是實際上如果長度超過255，既不用定長也不用變長，使用文本字節text
  • Varchar(10):的確存了10個漢字，utf8環境，10 3 + 1 = 31(bytes)；存儲3個漢字 3 3 + 1 =10(bytes)
• 定長與變長的存儲實際空間(UTF8)

• 如何選擇定長或者是變長字符串？
  • 定長的磁盤空間比較浪費，但是效率高；如果數據基本上長度都一樣，就是用定長，如：身份證、電話號碼、手機號碼等
  • 變長的磁盤空間比較節省，但是效率低；如果數據不能確定長度（不同的數據有變化），如：姓名、地址等

文本字符串

• 如果數據量非常大，通常說超過255個字節就會使用文本字符串
• 文本字符串根據存儲的數據格式進行分類：text和blob
  • Text:存儲文字（二進制數據實際上都是存儲路徑）
  • Blob:存儲二進制數據（一般不用）

枚舉字符串

• 枚舉：enum，事先將所有可能出現的結果都設計好，實際上存儲的數據必須是規定好的數據中的一個
• 枚舉的使用方式
  • 定義：enum(可能出現的元素列表); // 如enum(‘男’, ‘女’, ‘其他’, ‘保密’);
  • 使用：存儲數據，只能存儲上面定義好的數據

    1 2 3 4

    # 創建枚舉表 create table if not exists my_enum( gender enum('男', '女', '其他', '保密') )charset utf8;

1
2
3

# 插入數據
insert into my_enum values('男'), ('保密');    -- 有效數據
insert into my_enum values('male');     -- 無效數據（error: Data truncated for column 'gender' at row 1）

• enum作用：

  • 規範數據格式：數據只能是規定的數據中的其中一個
  • 節省存儲空間（枚舉通常有一個別名：單選框）；枚舉實際存儲的是數值而不是字符串本身
    • 在SQL中，系統也是自動轉換數據格式的：而且基本與PHP一樣（尤其是字符串轉數字）

• 證明字段存儲的數據是數值：將數據取出來 + 0，就可以判斷出原來的數據存的到底是字符串還是數值，如果是字符串最終結果永遠為0，否則就是其他值

  1 2	# 將字段結果取出進行 +0 運算 select gender + 0, gender from my_enum;

• 枚舉元素實際規律：根據元素出現的順序，從1開始編號
• 枚舉原理：枚舉在進行數據規範的時候（定義的時候），系統會自動建立一個數字與枚舉元素的對應關係（關係放到日誌中）；然後進行數據插入的時候系統自動降字符轉換為對應的數字存儲，然後在進行數據提取的時候，系統自動將數值轉換成對應的字符串顯示
• 因為枚舉實際存儲的是數值，所以可以直接插入數值

  1 2	# 數值插入枚舉元素 insert into my_enum values(1), (2);

集合字符串

• 集合字符串跟枚舉很類似：實際存儲的是數值，而不是字符串（集合是多選的）
• 集合的使用方式：
  • 定義：Set(元素列表)
  • 使用： 可以使用元素列表中的元素（多個），使用逗號分割

    1 2 3 4 5 6 7

    # 創建集合分類 create table if not exists my_set( hobby set('足球', '籃球', '羽毛球', '乒乓球', '棒球', '網球', '台球', '排球') -- 集合中L：每一個元素都是對應一個二進制位，被選中為1，沒有則為0；最後反過來 -- 1 0 0 0 1 1 0 0 -- 反過來：00110001 = 49 )charset utf8;

• 插入數據：可以使用多個元素字符串集合：也可以直接數值

  1 2 3

  # 插入數據 insert into my_set values(3); insert into my_set values('足球, 排球, 棒球, 網球');

• 集合中元素的順序沒有關係：最終系統都會去匹配順序
• 集合的強大在於能夠規範數據和節省空間：PHP也可以貴發數據，但是對於PHP來說效率優先，而且數據的維護可以通過數字進行，增加PHP的維護成本；PHP根本沒有辦法判斷數據在數據庫中的形式

Mysql紀錄長度

• Mysql中規定：任何一條紀錄最長不能超過65535個字節（varchar永遠達不到理想值）
• Varchar的實際存儲長度能達到多少，主要看字符集編碼
  • UTF8下varchar的實際頂配：21844字節
  • GBK下的varchar的實際頂配：32766字節

    1 2 3 4 5 6 7 8

    # 求出varchar在utf8和GBK下的實際最大值 create table if not exists my_utf8( name varchar(65535) )charset utf8; create table if not exists my_gbk( name varchar(65535) )charset gbk;

1
2
3
4
5
6
7
8

# 求出varchar在utf8和GBK下的實際最大值
create table if not exists my_utf8(
    name varchar(21845)
)charset utf8;

create table if not exists my_gbk(
    name varchar(32767)
)charset gbk;

1
2
3
4
5
6
7
8

# 求出varchar在utf8和GBK下的實際最大值
create table if not exists my_utf8(
    name varchar(21844)     -- 21833 * 3 + 2 = 65532 + 2 = 65534
)charset utf8;

create table if not exists my_gbk(
    name varchar(32766)     -- 32766 * 2 + 2 = 65532 + 2 = 65534
)charset gbk;

• 想用完整個65535個字節長度：增加一個tinyint字節即可（有問題）

  1 2 3 4 5 6 7 8 9

  create table if not exists my_utf8_1( age tinyint, name varchar(21844) -- 21833 * 3 + 2 = 65532 + 2 = 65534 )charset utf8; create table if not exists my_gbk_1( age tinyint, name varchar(32766) -- 32766 * 2 + 2 = 65532 + 2 = 65534 )charset gbk;

• Mysql紀錄中，如果有任何一個字段允許為空，那麼系統會自動從整個紀錄中保留一個字節來存儲NULL（若想釋放NULL所占用的字節，必須保證所有的字段都不允許為空）

  1 2 3 4 5 6 7 8 9 10

  # 釋放NULL create table if not exists my_utf8_1( age tinyint not null, name varchar(21844) not null -- 21833 * 3 + 2 = 65532 + 2 = 65534 )charset utf8; create table if not exists my_gbk_1( age tinyint not null, name varchar(32766) not null -- 32766 * 2 + 2 = 65532 + 2 = 65534 )charset gbk;

• Mysql中的text文本字符串，不佔用紀錄長度；額外存儲，但是text文本字符串可是屬於紀錄的一部分，一定需要佔據紀錄中的部分長度：10個字節（保存數據的地址以及長度）

  1 2 3 4 5

  # text 占用是個字節長度 create table if not exists my_text( name varchar(21841) not null, -- 21831 * 3 + 2 = 65523 + 2 = 65525 content text not null -- 10 )charset utf8;

列屬性

• 列屬性：真正約束字段的是數據類型，但是數據類型的約束很大一，需要有一些額外的約束，來更加保証數據的合法性
• 列屬性有很多種：NULL/NOT NULL, default, Primary key, unique key, auto_increment, comment

空屬性

• 兩個值：NULL（默認的）和NOT NULL（不為空）
• 雖然默認的，數據庫基恩都是字段為空，但是實際上在真實開發的時候，盡可能的要保証所有的數據都不應該為空，空數據沒有意義，空數據沒辦法參加運算

• 實例

  1 2 3 4 5

  # 創建一個實際案例表：班機表（名字，教室） create table if not exists my_class( name varchar(20) not null, room varchar(20) null -- 代表允許為空：不寫默認就是允許為空 )charset utf8;

列描述

• 列描述：comment；描述，沒有實際含義，是專門用來描述字段，會根據表創建語句保存，用來給程序猿（數據庫管理者）來進行了解的

  1 2 3 4

  create table if not exists my_teachers( name varchar(20) not null comment '姓名', money decimal(20, 2) not null comment '工資' )charset utf8;

####默認值

• 默認值：某一種數據會經常性的出現某個具體的值，可以在一開始就制定好；在需要真實數據的時候，用戶可以選擇行的使用默認值

• 默認值關鍵字：default

  1 2 3 4

  create table if not exists my_default( name varchar(20) not null, age tinyint unsigned default 0 )charset utf8;

• 默認值的生效：使用，在數據進行插入的時候，不給該字段賦值

  1	insert into my_default (name) values('Vincent');

• 想要使用默認值，可以不一定去指定列表，故意不使用字段列表：可以使用default關鍵字代替

  1	insert into my_default values('Eli', default);