②:MySQL數據類型詳解

2021-12-29 程式設計師路人

大家好，我是路人。

這是 mysql 系列第 2 篇文章。

環境：mysql5.7.25，cmd 命令中進行演示。

主要內容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

整數類型類型字節數有符號值範圍無符號值範圍tinyint[(n)] [unsigned]1[-

上面[]包含的內容是可選的，默認是有符號類型的，無符號的需要在類型後面跟上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 表示的是顯示寬度，不足的用 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 無符號最大值為 
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[(m,d)]4(-3.402823466E+38,3.402823466351E+38)[0,3.402823466E+38)單精度 
浮點數值double[(m,d)]8(-1.7976931348623157E+308,1.797693134 8623157E+308)[0,1.797693134862315 7E+308)雙精度
浮點數值decimal[(m,d)]對 DECIMAL(M,D) ，如果 M>D，為 M+2 否則為 D+2依賴於 M 和 D 的值依賴於 M 和 D 的值小數值
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 |    2 |
+--+--+-+
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。
日期類型類型字節大小範圍格式用途DATE31000-01-01/9999-12-31YYYY-MM-DD日期值TIME3'-838:59:59'/'838:59:59'HH:MM:SS時間值或持續時間YEAR11901/2155YYYY年份值DATETIME81000-01-01 00:00:00/9999-12-31 23:59:59YYYY-MM-DD HH:MM:SS混合日期和時間值TIMESTAMP41970-01-01 00:00:00/2038 結束時間是第 2147483647 秒，北京時間 2038-1-19 11:14:07，格林尼治時間 2038 年 1 月 19 日 凌晨 03:14:07YYYYMMDD HHMMSS混合日期和時間值，時間戳字符串類型類型範圍存儲所需字節說明char(M)[0,m]，m 的範圍[0,
char 類型佔用固定長度，如果存放的數據為固定長度的建議使用 char 類型，如：手機號碼、身份證等固定長度的信息。
表格中的 L 表示存儲的數據本身佔用的字節，L 以外所需的額外字節為存放該值的長度所需的字節數。
MySQL 通過存儲值的內容及其長度來處理可變長度的值，這些額外的字節是無符號整數。
請注意，可變長類型的最大長度、此類型所需的額外字節數以及佔用相同字節數的無符號整數之間的對應關係：
例如，MEDIUMBLOB 值可能最多 
mysql 類型和 java 類型對應關係MySQL Type NameReturn value ofGetColumnClassNameReturned as Java ClassBIT(1) (new in MySQL-5.0)BITjava.lang.BooleanBIT( > 1) (new in MySQL-5.0)BITbyte[]TINYINTTINYINTjava.lang.Boolean if the configuration property tinyInt1isBit is set to true(the default) and the storage size is 1, orjava.lang.Integer if not.BOOL, BOOLEANTINYINTSee TINYINT, above as these are aliases forTINYINT(1), currently.SMALLINT[(M)][unsigned]SMALLINT [UNSIGNED]java.lang.Integer (regardless if UNSIGNED or not)MEDIUMINT[(M)][unsigned]MEDIUMINT [UNSIGNED]java.lang.Integer, if UNSIGNEDjava.lang.LongINT,INTEGER[(M)][unsigned]INTEGER [UNSIGNED]java.lang.Integer, if UNSIGNEDjava.lang.LongBIGINT[(M)][unsigned]BIGINT [UNSIGNED]java.lang.Long, if UNSIGNEDjava.math.BigIntegerFLOAT[(M,D)]FLOATjava.lang.FloatDOUBLE[(M,B)]DOUBLEjava.lang.DoubleDECIMAL[(M[,D])]DECIMALjava.math.BigDecimalDATEDATEjava.sql.DateDATETIMEDATETIMEjava.sql.TimestampTIMESTAMP[(M)]TIMESTAMPjava.sql.TimestampTIMETIMEjava.sql.TimeYEAR[(2|4)]YEARIf yearIsDateType configuration property is set to false, then the returned object type isjava.sql.Short. If set to true (the default) then an object of type java.sql.Date (with the date set to January 1st, at midnight).CHAR(M)CHARjava.lang.String (unless the character set for the column is BINARY, then byte[]is returned.VARCHAR(M) [BINARY]VARCHARjava.lang.String (unless the character set for the column is BINARY, then byte[]is returned.BINARY(M)BINARYbyte[]VARBINARY(M)VARBINARYbyte[]TINYBLOBTINYBLOBbyte[]TINYTEXTVARCHARjava.lang.StringBLOBBLOBbyte[]TEXTVARCHARjava.lang.StringMEDIUMBLOBMEDIUMBLOBbyte[]MEDIUMTEXTVARCHARjava.lang.StringLONGBLOBLONGBLOBbyte[]LONGTEXTVARCHARjava.lang.StringENUM('value1','value2',...)CHARjava.lang.StringSET('value1','value2',...)CHARjava.lang.String數據類型選擇的一些建議選小不選大：一般情況下選擇可以正確存儲數據的最小數據類型，越小的數據類型通常更快，佔用磁碟，內存和 CPU 緩存更小。簡單就好：簡單的數據類型的操作通常需要更少的 CPU 周期，例如：整型比字符操作代價要小得多，因為字符集和校對規則(排序規則)使字符比整型比較更加複雜。儘量避免 NULL：儘量制定列為 NOT NULL，除非真的需要 NULL 類型的值，有 NULL 的列值會使得索引、索引統計和值比較更加複雜。記錄時間的建議使用 int 或者 bigint 類型，將時間轉換為時間戳格式，如將時間轉換為秒、毫秒，進行存儲，方便走索引最新資料

相關焦點

  • MySQL數據類型DECIMAL詳解
    前言： 當我們需要存儲小數，並且有精度要求，比如存儲金額時，通常會考慮使用DECIMAL欄位類型，可能大部分同學只是對DECIMAL類型略有了解，其中的細節還不甚清楚，本篇文章將從零開始，為你講述DECIMAL欄位類型的使用場景及方法。
  • MySQL--SQL的數據類型
    MySQL中定義數據欄位的類型對資料庫優化非常重要，前面介紹過Python的數據類型有6種，包含數字、字符串、列表、元組、集合、字典
  • 學習MySQL:MySQL 數據類型
    >（2）字符;（3）二進位;（4）時間;2.在每個類別中,存在多種特定的數據類型,這些數據類型使用的內存大小和磁碟空間各不相同,因此會對性能產生不同的影響;3.對於單個記錄,為列選擇最佳數據類型所產生的性能影響相對較小,但隨著資料庫的增大,這些較小的影響可能會匯聚成大的影響;應在設計過程中事先考慮這些影響,以免造成性能問題;
  • mysql field json MySQL JSON 類型數據操作
    test"'='1';https://dev.mysql.com/doc/refman/8.0/en/json-search-functions.htmlmysql>SET@j=』{「a」: 1, 「b」: 2, 「c」: {「d」: 4}}』;mysql>SET@j2=『1』;mysql>SELECTJSON_CONTAINS
  • MySQL中「詭異」的TIMESTAMP數據類型
    前    言涉及MySQL的日常開發與運維過程中少不了和TIMESTAMP數據類型打交道，有時候TIMESTAMP類型看起來莫名其妙，測試環境都測試OK了上了預發就直接報錯了；有時候TIMESTAMP類型看起來又很詭異，表中的真實數據跟開發人員提交的表結構又南轅北轍並非所需要的；本篇文章將抽絲剝繭帶你重新認識一下這個熟悉又陌生的TIMESTAMP類型。
  • MySQL mysqldump 數據導出詳解
    ，而mysqldump是導出數據過程中使用非常頻繁的一個工具；它自帶的功能參數非常多，文章中會列舉出一些常用的操作，在文章末尾會將所有的參數詳細說明列出來。databases db1 -F >/tmp/db1.sql6.只導出表結構不導出數據，--no-datamysqldump -uroot -proot --no-data --databases db1 >/tmp/db1.sql7.跨伺服器導出導入數據mysqldump
  • MySQL教程之MySQL數據類型詳解
    本文寫的內容是MySQL數據類型，之前寫MySQL系列文章的時候一直忽略的一個知識點，現在想來，我們學習一門語言，無非從兩個方面入手：基本語法，有了語法，我們才可以組織邏輯數據類型，即在特定場景下選擇合適的數據類型，到底是用整型還是浮點型還是字符串，每種數據機構佔多少字節，最大值是多少。
  • MySQL技術內幕:時間和日期數據類型
    【IT168 技術】MySQL資料庫中有五種與日期和時間有關的數據類型，各種日期數據類型所佔空間如表2-1所示。在MySQL 5.5版本之前(包括5.5版本)，資料庫的日期類型不能精確到微秒級別，任何的微秒數值都會被資料庫截斷，例如：mysql> CREATE TABLE t ( a
  • 一文詳解MySQL權限
    * from columns_priv where user=『root』 and host=『localhost』; ##無記錄mysql> select * from procs_priv where user=『root』 and host=『localhost』;MySQL權限詳解(1)All/All
  • 玩轉Mysql系列 - 第22篇:mysql索引原理詳解
    mysql中的頁mysql中和磁碟交互的最小單位稱為頁，頁是mysql內部定義的一種數據結構，默認為16kb，相當於4個磁碟塊，也就是說mysql每次從磁碟中讀取一次數據是16KB，要麼不讀取，要讀取就是16KB，此值可以修改的。
  • MySQL 避坑指南之隱式數據類型轉換
    當兩個不同類型的數據進行運算時，為了使得它們能夠兼容，MySQL 可能會執行隱式的數據類型轉換。例如，MySQL 在需要時會自動將字符串轉換為數字，反之亦然。mysql> SELECT 1+'1'; -> 2mysql> SELECT CONCAT(2,' test');        -> '2 test'我們也可使用 CAST() 函數將數字顯式轉換為字符串。CONCAT() 函數中的隱式類型轉換是因為它只能接收字符串類型的參數。
  • 面試題:mysql 數據類型
    點擊上方「程式設計師面試圈」，選擇「置頂或者星標」與你一起成長～公眾號回復[ 資源 ] 領取888G學習資源mysql數據類型MySQL數據類型含義（有符號）tinyint(m)1個字節 範圍(-128~127)smallint(m)2個字節 範圍(-32768~32767)mediumint(m)3個字節 範圍(-8388608~8388607)int(m)4個字節 範圍(-2147483648~2147483647)bigint(m)8個字節 範圍(+-9.22*10的18次方)取值範圍如果加了unsigned
  • mysql的explain詳解
    基於mysql的查詢，更多是注重掃描查詢的行數，以及是否使用到索引，我常看的最重要的是掃描的rows欄位，然後就key欄位，看下extra的屬性，這樣就基本能解決問題了。下面我們詳解一下explain的解析結果，常用欄位的說明：欄位：select_type解釋：select語句的類型常見值及其說明：simple：表示簡單的select，沒有union和子查詢primary：最外面的查詢 或者 主查詢，在有子查詢的語句中，最外面的select查詢就是
  • MySQL 中你應該使用什麼數據類型表示時間?
    使用 MySQL 原生的 DATE 類型還是使用 INT 欄位把日期和時間保存為一個純數字呢？在這篇文章中，我將解釋 MySQL 原生的方案，並給出一個最常用數據類型的對比表。我們也將對一些典型的查詢做基準測試，然後得出在給定場景下應該使用什麼數據類型的結論。如果你想直接看結論，請翻到文章最下方。
  • MySQL LOAD DATA 語句詳解
    但是，由於它能夠加載按照指定分隔符分割的純文本數據，通過MySQL Server發行軟體包中提供的mysqlimport工具還能實現批量導入純文本數據。所以，一些童鞋可能會將其用於資料庫軟體跨大版本升級或者數據的跨平臺流轉等場景中。不過，該語句存在著眾多的子選項，加上不常用，常常容易忘記，正巧，筆者曾經完整地研究過這些內容，現在，我們將其整理出來分享給大家。
  • MySQL 創建數據表 | Mysql Create Table
    column_definition它定義列的名稱以及每列的數據類型。表定義中的列由逗號運算符分隔。列定義的語法如下：column_name1 data_type(size) [NULL | 非空]table_constraints它指定了表約束，如 PRIMARY KEY、UNIQUE KEY、FOREIGN KEY、CHECK 等。
  • MySQL的Limit詳解
    問題：資料庫查詢語句，如何只返回一部分數據？TOP 子句用於規定要返回的記錄的數目。
  • mysql性能分析explain之id詳解
    前言在mysql的編程世界裡，有時候我們往往需要對自己編寫的sql語句進行分析，來去查看sql的執行計劃，這個還是很有必要的。因為我們在開發中，往往需要從資料庫中查詢數據，當數據量一旦大的時候，這個時候就會出現查詢瓶頸，我們首先呢可能就會去查看我們的sql語句的執行過程，判斷是否可進行優化，那如何去定位分析呢？這個就是本文講到的使用explain工具來去定位分析。
  • MySQL EXPLAIN 命令詳解學習
    2 各列詳解MySQL EXPLAIN命令能夠為SQL語句中的每個表生成以下信息：mysql> EXPLAIN SELECT * FROM inventory WHERE item_id = 16102176\G;    ********************* 1. row *************
  • MySQL中BINARY和VARBINARY類型學習--MySql語法
    BINARY和VARBINARY數據類型不同於CHAR BINARY和VARCHAR BINARY數據類型。對於後一種類型，BINARY屬性不會將列視為二進位字符串列。相反，它致使使用列字符集的二元 校對規則，並且列自身包含非二進位字符字符串而不是二進位字節字符串。