MySQL基礎之----資料型別篇(常用資料型別)
MySQL資料型別
注: 轉載 https://www.runoob.com/mysql/mysql-data-types.html
MySQL中定義資料欄位的型別對資料庫的優化是非常有效的
MySQL支援多種型別, 大致可以分為三類: 數值, 日期/時間和字串(字元)型別
注: 本文全部使用小寫形式, SQL語句不區分大小寫命令
- 數值型別
MySQL 支援所有標準SQL數值資料型別
這些型別包括嚴格數值資料型別(integer, smallint, decimal和numeric) , 以及浮點型(float, real, 和 double,precision )
關鍵字int是integer的同義詞，關鍵字dec是decimal的同義詞。
bit資料型別儲存位欄位值，並且支援mylsam、memory、InnoDB和BDB表。
作為SQL標準的擴充套件，MySQL也支援整數型別tinyint、mediumint和bigint。下面的表顯示了需要的每個整數型別的儲存和範圍。
- 整數型別表
| 型別 | 大小 | 範圍（有符號） | 範圍（無符號） | 用途 |
|---|---|---|---|---|
| tinyint | 1 byte | (-128，127) | (0，255) | 小整數值 |
| smallint | 2 bytes | (-32 768，32 767) | (0，65 535) | 大整數值 |
| mediumint | 3 bytes | (-8 388 608，8 388 607) | (0，16 777 215) | 大整數值 |
| int或integer | 4 bytes | (-2 147 483 648，2 147 483 647) | (0，4 294 967 295) | 大整數值 |
| bigint | 8 bytes | (-9,223,372,036,854,775,808，9 223 372 036 854 775 807) | (0，18 446 744 073 709 551 615) | 極大整數值 |
例: 如果超過型別規定數值報錯是SQLmode設定了嚴格模式, 修改xxx即可
無符號tinyint
mysql> create table t2(x tinyint unsigned);
mysql> insert into t2 values(-1,0,255,256);
mysql> select * from t2;
+------+
| x |
+------+
| 0 | -1存成了0
| 0 | 無符號，最小值為0
| 255 | 無符號，最大值為255
| 255 | 256存成了255
+------+
有符號int(預設)
mysql> create table t3(x int); #預設為有符號整數
mysql> insert into t3 values
-> (-2147483649),
-> (-2147483648),
-> (2147483647),
-> (2147483648);
mysql> select * from t3;
+-------------+
| x |
+-------------+
| -2147483648 | #-2147483649存成了-2147483648
| -2147483648 | #有符號，最小值為-2147483648
| 2147483647 | #有符號，最大值為2147483647
| 2147483647 | #2147483648存成了2147483647
+-------------+
無符號int
mysql> create table t4(x int unsigned);
mysql> insert into t4 values(-1,0,4294967295,4294967296);
mysql> select * from t4;
+------------+
| x |
+------------+
| 0 | #-1存成了0
| 0 | #無符號，最小值為0
| 4294967295 | #無符號，最大值為4294967295
| 4294967295 | #4294967296存成了4294967295
+------------+
mysql> bigint/zerofill型別使用都一樣
zerofill為限制長度
- 浮點型表
精度問題
| 型別 | 大小 | 範圍（有符號） | 範圍（無符號） | 用途 |
|---|---|---|---|---|
| float | 4 bytes | (-3.402 823 466 E+38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38) | 0，(1.175 494 351 E-38，3.402 823 466 E+38) | 單精度 浮點數值 |
| double | 8 bytes | (-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308) | 0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308) | 雙精度 浮點數值 |
| decimal | 對DECIMAL(M,D) ，如果M>D，為M+2否則為D+2 | 依賴於M和D的值 | 依賴於M和D的值 | 小數值 |
例:
-------------------------------------float----------------------------------
float[(M,D)] [UNSIGNED] [ZEROFILL]
單精度浮點數（非準確小數值），m是數字總個數，d是小數點後個數。m最大值為255，d最大值為30
精確度： 隨著小數的增多，精度變得不準確
mysql> create table t1(x float(256,31));
ERROR 1425 (42000): Too big scale 31 specified for column 'x'. Maximum is 30.
mysql> create table t1(x float(256,30));
ERROR 1439 (42000): Display width out of range for column 'x' (max = 255)
mysql> create table t1(x float(255,30)); #建表成功
mysql> insert into t1 values(1.1111111111111111111111111111111); #小數點後31個1
mysql> select * from t1; #隨著小數的增多，精度開始不準確
+----------------------------------+
| x |
+----------------------------------+
| 1.111111164093017600000000000000 |
+----------------------------------+
---------------------------------double-------------------------------------
double[(M,D)] [UNSIGNED] [ZEROFILL]
雙精度浮點數（非準確小數值），m是數字總個數，d是小數點後個數。m最大值為255，d最大值為30
隨著小數的增多，精度比float要高，但也會變得不準確
mysql> create table t2(x double(255,30)); #建表成功
mysql> insert into t2 values(1.1111111111111111111111111111111);
mysql> select * from t2; #精度比float要準確點，但隨著小數的增多，同樣變得不準確
+----------------------------------+
| x |
+----------------------------------+
| 1.111111111111111200000000000000 |
+----------------------------------+
-------------------------------decimal--------------------------------------
decimal[(m[,d])] [unsigned] [zerofill]
準確的小數值，m是數字總個數（負號不算），d是小數點後個數。 m最大值為65，d最大值為30。
隨著小數的增多，精度始終準確
對於精確數值計算時需要用此型別
decaimal能夠儲存精確值的原因在於其內部按照字串儲存。
mysql> create table t3(x decimal(65,30)); #建表成功
mysql> insert into t3 values(1.1111111111111111111111111111111);
mysql> select * from t3; #精度始終準確,d為30，於是只留了30位小數
+----------------------------------+
| x |
+----------------------------------+
| 1.111111111111111111111111111111 |
+----------------------------------+
- 日期/時間型別
表示時間值的日期和時間型別為DATETIME、DATE、TIMESTAMP、TIME和YEAR。
每個時間型別有一個有效值範圍和一個"零"值，當指定不合法的MySQL不能表示的值時使用"零"值。
TIMESTAMP型別有專有的自動更新特性，將在後面描述。
| 型別 | 大小 ( bytes) | 範圍 | 格式 | 用途 |
|---|---|---|---|---|
| date | 3 | 1000-01-01/9999-12-31 | YYYY-MM-DD | 日期值 |
| time | 3 | ‘-838:59:59’/‘838:59:59’ | HH:MM:SS | 時間值或持續時間 |
| year | 1 | 1901/2155 | YYYY | 年份值 |
| datetime | 8 | 1000-01-01 00:00:00/9999-12-31 23:59:59 | YYYY-MM-DD HH:MM:SS | 混合日期和時間值 |
| timestamp | 4 | 1970-01-01 00:00:00/2038結束時間是第 2147483647 秒，北京時間 2038-1-19 11:14:07，格林尼治時間 2038年1月19日 凌晨 03:14:07 | YYYYMMDD HHMMSS | 混合日期和時間值，時間戳 |
例:
---------------------------------year--------------------------------------
mysql> create table t1(born_year year); #無論year指定何種寬度，最後都預設是year(4)
mysql> insert into t1 values(1000,1234,2020,2025);
mysql> select * from t1;
+-----------+
| born_year |
+-----------+
| 0000 |
| 1901 |
| 2155 |
| 0000 |
+-----------+
-----------------------date,time,datetime-----------------------------------
mysql> create table t1(d date,t time,dt datetime);
mysql> desc t1;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| d | date | YES | | NULL | |
| t | time | YES | | NULL | |
| dt | datetime | YES | | NULL | |
+-------+----------+------+-----+---------+-------+
mysql> insert into t1 values(now(),now(),now()); now()函式為當前時間, 包括年
mysql> select * from t1;
+------------+----------+---------------------+
| d | t | dt |
+------------+----------+---------------------+
| 2020-09-25 | 16:26:54 | 2020-09-25 16:26:54 |
+------------+----------+---------------------+
----------------------------timestamp-------------------------------------
mysql> create table t1(time timestamp);
mysql> insert into t1 values();
mysql> insert into t1 values(null);
mysql> select * from t1;
+---------------------+
| time |
+---------------------+
| 2017-07-25 16:29:17 |
| 2017-07-25 16:30:01 |
+---------------------+
注意:
- 單獨插入時間時，需要以字串的形式，按照對應的格式插入
- 插入年份時，儘量使用4位值
timestamp和datetime區別
在實際應用的很多場景中，MySQL的這兩種日期型別都能夠滿足我們的需要，儲存精度都為秒，但在某些情況下，會展現出他們各自的優劣。下面就來總結一下兩種日期型別的區別。
1.DATETIME的日期範圍是1001——9999年，TIMESTAMP的時間範圍是1970——2038年。
2.DATETIME儲存時間與時區無關，TIMESTAMP儲存時間與時區有關，顯示的值也依賴於時區。在mysql伺服器，作業系統以及客戶端連線都有時區的設定。
3.DATETIME使用8位元組的儲存空間，TIMESTAMP的儲存空間為4位元組。因此，TIMESTAMP比DATETIME的空間利用率更高。
4.DATETIME的預設值為null；TIMESTAMP的欄位預設不為空（not null）,預設值為當前時間（CURRENT_TIMESTAMP），如果不做特殊處理，並且update語句中沒有指定該列的更新值，則預設更新為當前時間。
- 字串型別
字串型別指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET。該節描述了這些型別如何工作以及如何在查詢中使用這些型別。
| 型別 | 大小 | 用途 |
|---|---|---|
| char | 0-255 bytes | 定長字串 |
| varchar | 0-65535 bytes | 變長字串 |
| tinyblob | 0-255 bytes | 不超過 255 個字元的二進位制字串 |
| tinytext | 0-255 bytes | 短文字字串 |
| blob | 0-65 535 bytes | 二進位制形式的長文字資料 |
| text | 0-65 535 bytes | 長文字資料 |
| mediumblob | 0-16 777 215 bytes | 二進位制形式的中等長度文字資料 |
| mediumtext | 0-16 777 215 bytes | 中等長度文字資料 |
| longblob | 0-4 294 967 295 bytes | 二進位制形式的極大文字資料 |
| longtext | 0-4 294 967 295 bytes | 極大文字資料 |
char(n) 和 varchar(n) 中括號中 n 代表字元的個數，並不代表位元組個數，比如 CHAR(30) 就可以儲存 30 個字元。
例: 常用char/varchar
- char型別：定長，簡單粗暴，浪費空間，存取速度快
mysql> CREATE TABLE t1 (a char(10),b varchar(10);
insert into t1 values('哈哈哈 ','哈哈哈 ');
mysql> SET sql_mode='';
mysql> select x,char_length(x),y,char_length(y) from t1;
+-----------+----------------+------------+----------------+
| x | char_length(x) | y | char_length(y) |
+-----------+----------------+------------+----------------+
| 哈哈哈 | 3 | 哈哈哈 | 4 |
+-----------+----------------+------------+----------------+
略施小計，讓char現出原形
mysql> SET sql_mode = 'PAD_CHAR_TO_FULL_LENGTH';
這下子char原形畢露了......
mysql> select x,char_length(x),y,char_length(y) from t1;
+-------------+----------------+------------+----------------+
| x | char_length(x) | y | char_length(y) |
+-------------+----------------+------------+----------------+
| 哈哈哈 | 5 | 哈哈哈 | 4 |
+-------------+----------------+------------+----------------+
- varchar型別：變長，精準，節省空間，存取速度慢
CREATE TABLE t ( a CHAR(10), b VARCHAR(10));
...
列舉型別/集合型別
欄位的值只能在給定範圍中選擇，如單選框，多選框
enum 單選 只能在給定的範圍內選一個值，如性別 sex 男male/女female
set 多選 在給定的範圍內可以選擇一個或一個以上的值（愛好1,愛好2,愛好3…）
mysql> create table consumer(
-> name varchar(50),
-> sex enum('male','female'),
-> level enum('vip1','vip2','vip3','vip4','vip5'), #在指定範圍內，多選一
-> hobby set('play','music','read','study') #在指定範圍內，多選多
-> );
mysql> insert into consumer values("chen",'male','vip5','read,study');
mysql> select * from consumer;
+------+--------+-------+------------+
| name | sex | level | hobby |
+------+--------+-------+------------+
| chen | male | vip5 | read,study |
+------+--------+-------+------------+
相關文章
- [java基礎]之資料型別Java資料型別
- 基礎資料型別資料型別
- mysql 常用的資料型別MySql資料型別
- Mysql 資料型別之整數型別MySQL 資料型別
- 【MySQL資料型別3之--字元型別】MySql資料型別字元
- Mysql 基礎資料型別(無時間相關型別)MySql資料型別
- python基礎之資料型別Python資料型別
- Go 基礎之基本資料型別Go資料型別
- go語言資料型別-基礎型別Go資料型別
- 資料型別之字串篇資料型別字串
- PHP基礎-資料型別PHP資料型別
- java資料型別基礎Java資料型別
- 【MySQL資料型別1之--數值型別】MySql資料型別
- js資料型別之基本資料型別和引用資料型別JS資料型別
- [Java基礎]之 資料型別轉換Java資料型別
- 前端基礎之JavaScript的資料型別前端JavaScript資料型別
- 前端基礎 - 資料型別篇（高頻面試！！！）前端資料型別面試
- 【MySQL資料型別2之--日期時間型別】MySql資料型別
- Kotlin 基礎 - 資料型別Kotlin資料型別
- PHP基礎-資料型別-booleanPHP資料型別Boolean
- PHP基礎-資料型別-floatPHP資料型別
- PHP基礎-資料型別-integetPHP資料型別
- Python資料型別基礎Python資料型別
- Python 基礎資料型別Python資料型別
- mysql資料型別MySql資料型別
- MYSQL 資料型別MySQL 資料型別
- Python 快速教程（基礎篇02）：基礎資料型別Python資料型別
- java基礎之java的基本資料型別Java資料型別
- 程式設計之基礎：資料型別（二）程式設計資料型別
- 程式設計之基礎：資料型別（一）程式設計資料型別
- 2.1 常用資料型別資料型別
- MySQL基礎教程---Chapter 5 資料型別和資料輸入MySqlAPT資料型別
- 【MySQL】MySQL基礎（SQL語句、約束、資料型別）MySql資料型別
- 常用資料庫基本資料型別資料庫資料型別
- 【Redis】Redis的資料型別速查（5種基礎型別，5特殊型別）Redis資料型別
- java基礎知識--資料型別Java資料型別
- PHP->GO 基礎-資料型別PHPGo資料型別
- 第 4 節：基礎資料型別資料型別