MySQL技術內幕(SQL編程)-資料類型

標籤：sysdate   添加   fill   mon   bin   upd   允許   長度   ict   

一：MySQL儲存引擎

MySQL外掛程式式儲存引擎可以讓儲存引擎層的開發人員設計他們希望的儲存層（滿足事務需求、滿足資料放到記憶體中等），常見的儲存引擎如下：

InnoDB儲存引擎：支援事務，面向聯機交易處理的應用。特點是行鎖設計、支援外鍵、預設讀取操作不會產生鎖。最為常用的引擎，5.5.8版本後的預設儲存引擎。

MyISAM：表鎖設計、支援全文索引、不支援事務，面向OLAP資料庫應用，5.5.8版本前曾是預設儲存引擎。其緩衝池只緩衝索引檔案，不快取資料檔案。

NDB:叢集儲存引擎，結構是share nothing的叢集架構，可提供更進階的高可用性。資料全部放在記憶體中，主鍵尋找速度極快，面向OLTP資料庫應用類型。

Memory:資料全放在記憶體中，重啟或崩潰表中資料即消失，預設使用雜湊索引，而不是B+樹索引。

 

回顧1：封鎖類型

• 寫鎖（獨佔鎖定，X鎖）：T對A加了寫鎖，只允許T讀寫A，其他事務不能對A加鎖，直到T釋放A上的鎖，其降低了整個系統的並行性；
• 讀鎖（共用鎖定，S鎖）：T只能讀不能改A，其他事務只能對A加S鎖，直到T釋放）

回顧2：封鎖粒度

資料庫中為了實現並發控制而採用封鎖技術，封鎖對象的大小稱為封鎖粒度（Granularity）。

• 表鎖：開銷小，鎖定整張表，寫操作要獲得寫鎖，阻塞其他使用者的讀寫操作。
• 行鎖：開銷大，對指定記錄加鎖，其他進程可對錶中其他資料進行操作，較大程度支援並發處理。

 

二：資料類型1.UNSIGNED：

將數字類型無符號化。INT類型範圍：-2^31~2^31-1    INT UNSIGNED範圍：0~2^32-1

看似適合主鍵自增長的類型，但實際應用中有負面影響，例如：CREATE TABLE `t1` (`a` int(10) unsigned,  `b` int(10) unsigned) ;

當插入(1,2)時，SELECT a-b FROM t1會報錯：[Err] 1690 - BIGINT UNSIGNED value is out of range in…

原因：a-b的十六進位結果為0xFFFFFFFF，對於無符號整型值來說為4294967295（整型最大值）；對於有符號整型數來說，符號位1即負數，取反加1，最終結果為-1。

解決方式：

SET sql_mode=‘NO_UNSIGNED_SUBTRACTION‘;

SELECT a-b FROM t1;

建議：盡量不用UNSIGNED，其可能帶來某些意想不到的效果，且對INT類型存放不了的資料，INT UNSIGNED同樣可能存放不下，還不如使用BIGINT。

 

2.ZEROFILL：

 `a` int(10) unsigned DEFAULT NULL ： 對於int(10)來說，其實沒有ZEROFILL這個屬性，括弧內的數字毫無意義。

 `a` int(4) unsigned zerofill，插入1時a的顯示結果會是0001，寬度小於4則會自動填滿0。

一旦啟用zerofill屬性，mysql資料庫為列自動添加UNSIGNED屬性。

 

3.SQL_MODE設定：

預設為空白，該設定可以允許一些非法操作，如將NULL插入NOT NULL欄位，或插入非法日期。生產環境強烈建議將該值設為strict 模式（strict 模式是將SQL_MODE設定為STRICT_TRANS_TABLES或STRICT_ALL_TABLES中的至少一種）。

 SELECT @@session.sql_mode （查看當前會話SQL_MODE設定）

 SELECT @@global.sql_mode（查看當前全域SQL_MODE設定）

設定為strict 模式：SET GLOBAL sql_mode=‘strict_trans_tables‘;

 

4.日期和時間函數：

DATETIME（8位元組）：範圍“1000-01-01 00:00:00”到“9999-12-31 23:59:59”

DATE（3位元組）：範圍“1000-01-01”到“9999-12-31”

TIMESTAMP（4位元組）：範圍“1970-01-01 00:00:00”到“2038-01-19 03:14:07”實際儲存“1970-01-01 00:00:00”到目前時間的毫秒數。

YEAR（1位元組）： 可指定寬度，YEAR(4)範圍1901-2155，YEAR(2)範圍1970-2070（00-69代表2000-2069）

TIME（3位元組）：範圍“-838:59:59”-"838:59:59"。該類型可儲存一天中的時間，還可以儲存時間間隔

 TIMESTAMP和DATETIME的區別：

除了時間範圍不一樣，還有：建表時TIMESTAMP可設定預設值，DATETIME不行；更新表時TIMESTAMP類型的列可以自動更新時間為目前時間。

建表案例：

  `GMT_CREATE` timestamp NOT NULL DEFAULT ‘0000-00-00 00:00:00‘,

  `GMT_MODIFIED` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

注意：執行UPDATE操作後，若值並沒有得到更新，那麼GMT_MODIFIED欄位不會得到更新。

 

時間函數：

1.SELECT NOW(),CURRENT_TIMESTAMP(),SYSDATE(),SLEEP(2),NOW(),CURRENT_TIMESTAMP(),SYSDATE();

CURRENT_TIMESTAMP和NOW相同，返回執行SQL語句的時間；SYSDATE返回執行到當前函數時的時間

2.DATE_ADD和DATE_SUB：SELECT DATE_ADD(NOW(),INTERVAL -30 DAY);可以使用的資料類型:MICROSECOND、SECOND、MINUTE、HOUR、WEEK、MONTH

3.DATE_FORMAT：SELECT DATE_FORMAT(NOW(),‘%m/%d/%Y‘); 07/06/2016

注意：SELECT DATE_FORMAT(NOW(),‘%Y-%m-%d‘)=‘2017-01-01‘;存在的問題：一般來說表中都會有一個對日期類型的索引，如果使用上述的語句，最佳化器絕對不會使用索引，也不可能通過索引來查詢資料，所以此查詢的執行效率非常低。

 

5.數字類型和字元類型：

1.單精確度的FLOAT和雙精確度的DOUBLE都是非精確的類型，並不能保證運算的正確性。FLOAT(M,D):M表示該值顯示M位，D為小數點後面的位元，在儲存值時會四捨五入。

2.DECIMAL和NUMERIC被視作相同的類型，該資料類型用於要求非常高的精確度的計算中，MySQL在內部把DECIMAL資料類型儲存為字串，更精確地保留它們的值。

3.對列的字元集設定： a VARCHAR(10) CHARSET gbk;

4.定序的命名：_ci結尾表示大小不敏感(case insensitive) _cs表示大小寫敏感（case sensitive） _bin表示二進位的比較。utf8字元集預設的定序是utf_general_ci，大小寫不敏感。當插入a和A時，會被視作一致的字元而返回(SELECT ‘a‘=‘A‘;顯示為1)。【在有關情境，如使用者填寫註冊名時，欄位要注意區分大小寫】。修改定序：ALTER TABLE t MODIFY COLUMN a VARCHAR(10) COLLATE UTF8_BIN; 定序還會影響索引：當使用utf_general_ci時，列t中含有A及a，則建立唯一索引會提示有重複資料。

5.CHAR(N):儲存固定長度的字串。N的範圍0-255。VARCHAR(N):儲存變長字元類型。N的範圍0-65535。N都代表字元長度，而非位元組長度。對於CHAR來說，資料庫會自動對儲存列的右邊進行填充直到長度為N，而在讀取時會自動將填充的字串刪除。LENGTH函數表示字串佔用的位元組數，CHAR_LENGTH表示字元長度。

6.VARCHAR在儲存時需要在前置長度列表加上實際儲存的字元，當小於255位元組時需要1位元組的空間，大於255位元組時需要2位元組的空間。所以VARCHAR(10)的最大佔用空間數是11位元組，其中的1個位元組來存放字元長度。

7.BINARY和VARBINARY：與CHAR和VARCHAR類似，BINARY和VARBINARY儲存的是二進位的字串，而非字元型字串，其沒有字元集的概念，排序和比較按照二進位值進行。BINARY(N)和VARBINARY(N)中的N都表示位元組長度。BINARY填充字元是0x00，而CHAR填充字元為0x20。

8.BLOB用來儲存二進位大資料類型，大多數情況下BLOB類型視為足夠大的VARBINARY類型的列，同樣TEXT類型的列視為足夠大的VARCHAR類型的列。BLOB和TEXT類型不能有預設值。排序時只使用列的前max_sort_length位元組（預設1024，可設定SET GLOBAL max_sort_length=2048）。為了有效儲存類型為BLOB或TEXT的大資料類型，一般將列的值存放到行溢出頁中，資料頁儲存的行資料只包含BLOB或TEXT類型資料前一部分資料。

9.ENUM和SET類型都是集合類型，ENUM最多可枚舉65535個元素，SET類型最多可枚舉64個元素。

 

 

 

  

 

MySQL技術內幕(SQL編程)-資料類型