Mysql系列第二講
- MySQL的資料型別
- 整數型別
- 示例1：有符號型別
- 示例2：無符號型別
- 型別(n)說明
- 示例1(重點)
- 示例2
- 日期型別
- 字串型別
- mysql型別和java型別對應關系
- 資料型別選擇的一些建議
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: 0
mysql> 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 1
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 1
mysql> insert into demo2 values (pow(2,8)+1);
ERROR 1264 (22003): Out of range value for column 'c1' at row 1
mysql> 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: 0
mysql> select * from demo2;
+------+
| c1 |
+------+
| 0 |
| 255 |
+------+
2 rows in set (0.00 sec)
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: 0
mysql> 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=utf8
1 row in set (0.00 sec)
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)
上面的結果中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: 5
mysql> 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)
結果說明（注意看）：
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: 2
mysql> 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)
說明：
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)
從上面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型別，將時間轉換為時間戳格式，如將時間轉換為秒、毫秒，進行存盤，方便走索引
Java
轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/13049.html
標籤：python