Mysql系列第二講 詳解mysql資料型別（重點）

qwer1030274531發表於2020-09-25
MySql資料型別

MySQL的資料型別

主要包括以下五大類

  • 整數型別：bit、bool、tinyint、smallint、mediumint、int、bigint

  • 浮點數型別：float、double、decimal

  • 字串型別：char、varchar、tinyblob、blob、mediumblob、longblob、tinytext、text、mediumtext、longtext

  • 日期型別：Date、DateTime、TimeStamp、Time、Year

  • 其他資料型別：暫不介紹，用的比較少。

整數型別

在這裡插入圖片描述
上面表格中有符號和無符號寫反了，[]包含的內容是可選的，預設是無符號型別的，無符號的需要在型別後面跟上unsigned

示例1：有符號型別

mysql> create table demo1(
      c1 tinyint
     );Query OK, 0 rows affected (0.01 sec)mysql> insert into demo1 values(-pow(2,7)),(pow(2,7)-1);Query OK, 2 rows affected (0.00 sec)Records: 2  Duplicates: 0  Warnings: 0mysql> select * from demo1;+------+| c1   |+------+| -128 ||  127 |+------+2 rows in set (0.00 sec)mysql> insert into demo1 values(pow(2,7));ERROR 1264 (22003): Out of range value for column 'c1' at row 11234567891011121314151617181920

demo1表中c1欄位為tinyint有符號型別的，可以看一下上面的演示，有超出範圍報錯的。

關於數值對應的範圍計算方式屬於計算機基礎的一些知識，可以去看一下計算機的二進位制表示相關的文章。

示例2：無符號型別

mysql> create table demo2(
      c1 tinyint unsigned
     );Query OK, 0 rows affected (0.01 sec)mysql> insert into demo2 values (-1);ERROR 1264 (22003): Out of range value for column 'c1' at row 1mysql> insert into demo2 values (pow(2,8)+1);ERROR 1264 (22003): Out of range value for column 'c1' at row 1mysql> insert into demo2 values (0),(pow(2,8));mysql> insert into demo2 values (0),(pow(2,8)-1);Query OK, 2 rows affected (0.00 sec)Records: 2  Duplicates: 0  Warnings: 0mysql> select * from demo2;+------+| c1   |+------+|    0 ||  255 |+------+2 rows in set (0.00 sec)1234567891011121314151617181920212223

c1是無符號的tinyint型別的，插入了負數會報錯。

型別(n)說明

在開發中，我們會碰到有些定義整型的寫法是int(11)，這種寫法個人感覺在開發過程中沒有什麼用途，不過還是來說一下，int(N)我們只需要記住兩點：

  • 無論N等於多少，int永遠佔4個位元組

  • N表示的是顯示寬度，不足的用0補足，超過的無視長度而直接顯示整個數字，但這要整型設定了unsigned zerofill才有效

看一下示例，理解更方便：

mysql> CREATE TABLE test3 (
       `a` int,
       `b` int(5),
       `c` int(5) unsigned,
       `d` int(5) zerofill,
       `e` int(5) unsigned zerofill,
       `f` int    zerofill,
       `g` int    unsigned zerofill     );Query OK, 0 rows affected (0.01 sec)mysql> insert into test3 values (1,1,1,1,1,1,1),(11,11,11,11,11,11,11),(12345,12345,12345,12345,12345,12345,12345);Query OK, 3 rows affected (0.00 sec)Records: 3  Duplicates: 0  Warnings: 0mysql> select * from test3;+-------+-------+-------+-------+-------+------------+------------+| a     | b     | c     | d     | e     | f          | g          |+-------+-------+-------+-------+-------+------------+------------+|     1 |     1 |     1 | 00001 | 00001 | 0000000001 | 0000000001 ||    11 |    11 |    11 | 00011 | 00011 | 0000000011 | 0000000011 || 12345 | 12345 | 12345 | 12345 | 12345 | 0000012345 | 0000012345 |+-------+-------+-------+-------+-------+------------+------------+3 rows in set (0.00 sec)mysql> show create table test3;| Table | Create Table                                                   | test3 | CREATE TABLE `test3` (
  `a` int(11) DEFAULT NULL,
  `b` int(5) DEFAULT NULL,
  `c` int(5) unsigned DEFAULT NULL,
  `d` int(5) unsigned zerofill DEFAULT NULL,
  `e` int(5) unsigned zerofill DEFAULT NULL,
  `f` int(10) unsigned zerofill DEFAULT NULL,
  `g` int(10) unsigned zerofill DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf81 row in set (0.00 sec)12345678910111213141516171819202122232425262728293031323334353637

show create table test3;輸出了表test3的建立語句，和我們原始的建立語句不一致了，原始的d欄位用的是無符號的，可以看出當使用了zerofill自動會將無符號提升為有符號。

說明：
int(5)輸出寬度不滿5時，前面用0來進行填充

int(n)中的n省略的時候，寬度為對應型別無符號最大值的十進位制的長度，如bigint無符號最大值為2的64次方-1等於18,446,744,073,709,551,615‬；

長度是20位，來個bigint左邊0填充的示例看一下 

mysql> CREATE TABLE test4 (
       `a`  bigint    zerofill     );Query OK, 0 rows affected (0.01 sec)mysql> insert into test4 values(1);Query OK, 1 row affected (0.00 sec)mysql> select *from test4;+----------------------+| a                    |+----------------------+| 00000000000000000001 |+----------------------+1 row in set (0.00 sec)123456789101112131415

上面的結果中1前面補了19個0，和期望的結果一致。
在這裡插入圖片描述
float數值型別用於表示單精度浮點數值，而double數值型別用於表示雙精度浮點數值，float和double都是浮點型，而decimal是定點型。

浮點型和定點型可以用型別名稱後加（M，D）來表示，M表示該值的總共長度，D表示小數點後面的長度，M和D又稱為精度和標度。

float和double在不指定精度時，預設會按照實際的精度來顯示，而DECIMAL在不指定精度時，預設整數為10，小數為0。

示例1(重點)

mysql> create table test5(a float(5,2),b double(5,2),c decimal(5,2));Query OK, 0 rows affected (0.01 sec)mysql> insert into test5 values (1,1,1),(2.1,2.1,2.1),(3.123,3.123,3.123),(4.125,4.125,4.125),(5.115,5.115,5.115),(6.126,6.126,6.126),(7.116,7.116,7.116),(8.1151,8.1151,8.1151),(9.1251,9.1251,9.1251),(10.11501,10.11501,10.11501),(11.12501,11.12501,11.12501);Query OK, 7 rows affected, 5 warnings (0.01 sec)Records: 7  Duplicates: 0  Warnings: 5mysql> select * from test5;+-------+-------+-------+| a     | b     | c     |+-------+-------+-------+|  1.00 |  1.00 |  1.00 ||  2.10 |  2.10 |  2.10 ||  3.12 |  3.12 |  3.12 ||  4.12 |  4.12 |  4.13 ||  5.12 |  5.12 |  5.12 ||  6.13 |  6.13 |  6.13 ||  7.12 |  7.12 |  7.12 ||  8.12 |  8.12 |  8.12 ||  9.13 |  9.13 |  9.13 || 10.12 | 10.12 | 10.12 || 11.13 | 11.13 | 11.13 |+-------+-------+-------+11 rows in set (0.00 sec)123456789101112131415161718192021222324

結果說明（注意看）：
c是decimal型別，認真看一下輸入和輸出，發現decimal採用的是四捨五入

認真看一下a和b的輸入和輸出，盡然不是四捨五入，一臉悶逼，float和double採用的是四捨六入五成雙

decimal插入的資料超過精度之後會觸發警告。

什麼是四捨六入五成雙？
就是5以下捨棄5以上進位，如果需要處理數字為5的時候，需要看5後面是否還有不為0的任何數字，如果有，則直接進位，如果沒有，需要看5前面的數字，若是奇數則進位，若是偶數則將5舍掉

示例2

我們將浮點型別的（M,D）精度和標度都去掉，看看效果：

mysql> create table test6(a float,b double,c decimal);Query OK, 0 rows affected (0.02 sec)mysql> insert into test6 values (1,1,1),(1.234,1.234,1.4),(1.234,0.01,1.5);Query OK, 3 rows affected, 2 warnings (0.00 sec)Records: 3  Duplicates: 0  Warnings: 2mysql> select * from test6;+-------+-------+------+| a     | b     | c    |+-------+-------+------+|     1 |     1 |    1 || 1.234 | 1.234 |    1 || 1.234 |  0.01 |    1 |+-------+-------+------+3 rows in set (0.00 sec)12345678910111213141516

說明：
a和b的資料正確插入，而c被截斷了

浮點數float、double如果不寫精度和標度，則會按照實際顯示

decimal不寫精度和標度，小數點後面的會進行四捨五入，並且插入時會有警告!

再看一下下面程式碼：

mysql> select sum(a),sum(b),sum(c) from test5;+--------+--------+--------+| sum(a) | sum(b) | sum(c) |+--------+--------+--------+|  67.21 |  67.21 |  67.22 |+--------+--------+--------+1 row in set (0.00 sec)mysql> select sum(a),sum(b),sum(c) from test6;+--------------------+--------------------+--------+| sum(a)             | sum(b)             | sum(c) |+--------------------+--------------------+--------+| 3.4679999351501465 | 2.2439999999999998 |      4 |+--------------------+--------------------+--------+1 row in set (0.00 sec)123456789101112131415

從上面sum的結果可以看出float、double會存在精度問題，decimal精度正常的，比如銀行對統計結果要求比較精準的建議使用decimal。

日期型別

在這裡插入圖片描述

字串型別

在這裡插入圖片描述
char型別佔用固定長度，如果存放的資料為固定長度的建議使用char型別，如：手機號碼、身份證等固定長度的資訊。

表格中的L表示儲存的資料本身佔用的位元組，L 以外所需的額外位元組為存放該值的長度所需的位元組數。

MySQL 透過儲存值的內容及其長度來處理可變長度的值，這些額外的位元組是無符號整數。

請注意，可變長型別的最大長度、此型別所需的額外位元組數以及佔用相同位元組數的無符號整數之間的對應關係：

例如，MEDIUMBLOB 值可能最多2的24次方 - 1位元組長並需要3個位元組記錄其長度，3 個位元組的整數型別MEDIUMINT 的最大無符號值為2的24次方 - 1。

mysql型別和java型別對應關係

在這裡插入圖片描述

資料型別選擇的一些建議

  • 選小不選大：一般情況下選擇可以正確儲存資料的最小資料型別，越小的資料型別通常更快，佔用磁碟，記憶體和CPU快取更小。

  • 簡單就好：簡單的資料型別的操作通常需要更少的CPU週期，例如：整型比字元操作代價要小得多，因為字符集和校對規則(排序規則)使字元比整型比較更加複雜。

  • 儘量避免NULL：儘量制定列為NOT NULL，除非真的需要NULL型別的值，有NULL的列值會使得索引、索引統計和值比較更加複雜。

  • 浮點型別的建議統一選擇decimal

  • 記錄時間的建議使用int或者bigint型別，將時間轉換為時間戳格式，如將時間轉換為秒、毫秒，進行儲存，方便走索引


來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/30239065/viewspace-2724064/，如需轉載，請註明出處，否則將追究法律責任。

相關文章