MySQL基礎之----資料型別篇(常用資料型別)

Mr.E5發表於2020-10-03

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。下面的表顯示了需要的每個整數型別的儲存和範圍。

  • 整數型別表
型別大小範圍(有符號)範圍(無符號)用途
tinyint1 byte(-128,127)(0,255)小整數值
smallint2 bytes(-32 768,32 767)(0,65 535)大整數值
mediumint3 bytes(-8 388 608,8 388 607)(0,16 777 215)大整數值
int或integer4 bytes(-2 147 483 648,2 147 483 647)(0,4 294 967 295)大整數值
bigint8 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為限制長度
  • 浮點型表

精度問題

型別大小範圍(有符號)範圍(無符號)用途
float4 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)單精度 浮點數值
double8 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)範圍格式用途
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混合日期和時間值,時間戳

例:

---------------------------------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 |
+---------------------+

注意:

  1. 單獨插入時間時,需要以字串的形式,按照對應的格式插入
  2. 插入年份時,儘量使用4位值

timestamp和datetime區別

在實際應用的很多場景中,MySQL的這兩種日期型別都能夠滿足我們的需要,儲存精度都為秒,但在某些情況下,會展現出他們各自的優劣。下面就來總結一下兩種日期型別的區別。

1.DATETIME的日期範圍是1001——9999年,TIMESTAMP的時間範圍是1970——2038年。

2.DATETIME儲存時間與時區無關,TIMESTAMP儲存時間與時區有關,顯示的值也依賴於時區。在mysql伺服器,作業系統以及客戶端連線都有時區的設定。

3.DATETIME使用8位元組的儲存空間,TIMESTAMP的儲存空間為4位元組。因此,TIMESTAMPDATETIME的空間利用率更高。

4.DATETIME的預設值為nullTIMESTAMP的欄位預設不為空(not null,預設值為當前時間(CURRENT_TIMESTAMP),如果不做特殊處理,並且update語句中沒有指定該列的更新值,則預設更新為當前時間。

  • 字串型別

字串型別指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET。該節描述了這些型別如何工作以及如何在查詢中使用這些型別。

型別大小用途
char0-255 bytes定長字串
varchar0-65535 bytes變長字串
tinyblob0-255 bytes不超過 255 個字元的二進位制字串
tinytext0-255 bytes短文字字串
blob0-65 535 bytes二進位制形式的長文字資料
text0-65 535 bytes長文字資料
mediumblob0-16 777 215 bytes二進位制形式的中等長度文字資料
mediumtext0-16 777 215 bytes中等長度文字資料
longblob0-4 294 967 295 bytes二進位制形式的極大文字資料
longtext0-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 |
+------+--------+-------+------------+

相關文章