[Mysql] 3.Mysql 資料型別
3.1 整數型別
整數型別 |
位元組數 |
無符合數的取值範圍 |
有符合數的取值範圍 |
TINYINT |
1 |
0~255 |
-128~127 |
SMALLINT |
2 |
0~65535 |
-32768~32767 |
MEDIUMINT |
3 |
0~16777215 |
-8388608~8388607 |
INT |
4 |
0~4294967295 |
-2147483648~2147483647 |
INTEGER |
4 |
0~4294967295 |
-2147483648~2147483647 |
BIGINT |
8 |
0~18446744073709551615 |
-922337203685477508 ~922337203685477507 |
mysql> create database test;
Query OK, 1 row affected (0.04 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test |
+--------------------+
5 rows in set (0.00 sec)
mysql> use test;
Database changed
mysql> create table intdata
-> (a tinyint,
-> b smallint,
-> c mediumint,
-> d int,
-> e bigint);
Query OK, 0 rows affected (0.39 sec)
mysql> desc intdata
-> ;
+-------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| a | tinyint(4) | YES | | NULL | |
| b | smallint(6) | YES | | NULL | |
| c | mediumint(9) | YES | | NULL | |
| d | int(11) | YES | | NULL | |
| e | bigint(20) | YES | | NULL | |
+-------+--------------+------+-----+---------+-------+
5 rows in set (0.01 sec)
mysql> create table intdata1
-> (a tinyint zerofill , #zerofill 表示不足的部分用 補充。
-> b smallint zerofill,
-> c mediumint zerofill,
-> d int zerofill,
-> e bigint zerofill);
Query OK, 0 rows affected (0.26 sec)
mysql> desc intdata1
-> ;
+-------+--------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------------------------+------+-----+---------+-------+
| a | tinyint(3) unsigned zerofill | YES | | NULL | |
| b | smallint(5) unsigned zerofill | YES | | NULL | |
| c | mediumint(8) unsigned zerofill | YES | | NULL | |
| d | int(10) unsigned zerofill | YES | | NULL | |
| e | bigint(20) unsigned zerofill | YES | | NULL | |
+-------+--------------------------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
mysql> insert into intdata values(1,2,3,4,5);
Query OK, 1 row affected (0.11 sec)
mysql> insert into intdata1 values(1,2,3,4,5);
Query OK, 1 row affected (0.03 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from intdata;
+------+------+------+------+------+
| a | b | c | d | e |
+------+------+------+------+------+
| 1 | 2 | 3 | 4 | 5 |
+------+------+------+------+------+
1 row in set (0.00 sec)
mysql> select * from intdata1;
+------+-------+----------+------------+----------------------+
| a | b | c | d | e |
+------+-------+----------+------------+----------------------+
| 001 | 00002 | 00000003 | 0000000004 | 00000000000000000005 |
+------+-------+----------+------------+----------------------+
1 row in set (0.00 sec)
mysql> create table intdata2
-> (a int(4),
-> b int(11));
Query OK, 0 rows affected (0.26 sec)
mysql> desc intdata2;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| a | int(4) | YES | | NULL | |
| b | int(11) | YES | | NULL | |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> insert into intdata2 values(111111,999999999);
Query OK, 1 row affected (0.06 sec)
mysql> select * from intdata2; # 雖然 a 欄位設定為 4 位,但是 int 最大長度為 10 ,故 111111
+--------+-----------+ # 能夠滿足 insert
| a | b |
+--------+-----------+
| 111111 | 999999999 |
+--------+-----------+
1 row in set (0.00 sec)
3.2 浮點數型別和定點數型別
浮點數型別和定點數型別用來表示小數,
浮點數型別包含單精度浮點數 (float) 和雙精度浮點數 (double).
定點數型別就是 decimal
整數型別 |
位元組數 |
負數的取值範圍 |
非負數的取值範圍 |
FLOAT |
4 |
-3.402823466E+38~ -1.175494351E-38 |
和 1.175494351E-38 ~3.402823466E+38 |
DOUBLE |
8 |
1.7976931348623157E+308 ~ 2.2250738585072014E-308 |
和 2.2250738585072014E-308 ~1.7976931348623157E+308 |
DECIMAIL(M,D) |
M+2 |
同 DOUBLE 型 |
同 DOUBLE 型 |
mysql> create table float1(
-> a float(6,2),
-> b double(6,2),
-> c decimal(6,2));
Query OK, 0 rows affected (0.26 sec)
mysql> desc float1
-> ;
+-------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| a | float(6,2) | YES | | NULL | |
| b | double(6,2) | YES | | NULL | |
| c | decimal(6,2) | YES | | NULL | |
+-------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> insert into float1 values(3.143,3.145,3.1434);
Query OK, 1 row affected, 1 warning (0.07 sec)
mysql> show warnings;
+-------+------+----------------------------------------+
| Level | Code | Message |
+-------+------+----------------------------------------+
| Note | 1265 | Data truncated for column 'c' at row 1 |
+-------+------+----------------------------------------+
1 row in set (0.00 sec)
mysql> select * from float1; #float,double 對 insert 的資料進行了四捨五入處理 .
+------+------+------+ #decimal 進行了擷取,併產生了 warning
| a | b | c |
+------+------+------+
| 3.14 | 3.15 | 3.14 |
+------+------+------+
1 row in set (0.00 sec)
mysql> create table float2(
-> a float,
-> b double,
-> c decimal(10,0));
Query OK, 0 rows affected (0.35 sec)
mysql> insert into float2 values(3.143,3.145,3.1434);
Query OK, 1 row affected, 1 warning (0.07 sec)
mysql> select * from float2;
+-------+-------+------+
| a | b | c |
+-------+-------+------+
| 3.143 | 3.145 | 3 |
+-------+-------+------+
1 row in set (0.00 sec)
位元組
3.3 日期和時間型別
(1)YEAR
(2)TIME
(3)DATE
(4)DATETIME
(5)TIMESTAMP
-
YEAR # 一個位元組 ,YYYY 形式 (1901~2155)
#2 個字串 , ‘ 00 ’到‘ 69 ’轉換為 2000 到 2069 ,‘ 70 ’到‘ 99 ’轉換為 1970 到 1999
#2 個數字, 1 到 69 轉換為 2001 到 2069 , 70 到 99 轉換為 1970 到 1999
mysql> insert into year values(1997),('1998'),('1900');
ERROR 1264 (22003): Out of range value for column 'a' at row 3
mysql> insert into year values(1997),('1998');
Query OK, 2 rows affected (0.07 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from year;
+------+
| a |
+------+
| 1997 |
| 1998 |
+------+
2 rows in set (0.00 sec)
mysql> insert into year values(55),(88),('55'),('77');
Query OK, 4 rows affected (0.07 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> select * from year;
+------+
| a |
+------+
| 1997 |
| 1998 |
| 2055 |
| 1988 |
| 2055 |
| 1977 |
+------+
6 rows in set (0.00 sec)
-
TIME # 使用 3 個位元組表示 HH:MM:SS 形式
mysql> insert into time values('2 23:50:50'),('22:22:22'),('11:11'),('2 20:20'),('2 20'),('30');
#2 表示 2 天 =48Hour
Query OK, 6 rows affected (0.17 sec)
Records: 6 Duplicates: 0 Warnings: 0
#
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from time;
+----------+
| a |
+----------+
| 71:50:50 |
| 22:22:22 |
| 11:11:00 |
| 68:20:00 |
| 68:00:00 |
| 00:00:30 |
+----------+
6 rows in set (0.00 sec)
mysql> create table time1 (a time);
Query OK, 0 rows affected (0.29 sec)
mysql> desc time1
-> ;
+-------+------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------+------+-----+---------+-------+
| a | time | YES | | NULL | |
+-------+------+------+-----+---------+-------+
1 row in set (0.00 sec)
mysql> insert into time1 values(121212),('131313'),(0),('0')
-> ; # 數字和字元都轉換了
Query OK, 4 rows affected (0.06 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> select * from time1;
+----------+
| a |
+----------+
| 12:12:12 |
| 13:13:13 |
| 00:00:00 |
| 00:00:00 |
+----------+
4 rows in set (0.00 sec)
mysql> insert into time1 value('1231231'),(1231231);
Query OK, 2 rows affected (0.08 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from time1;
+-----------+
| a |
+-----------+
| 123:12:31 |
| 123:12:31 |
+-----------+
2 rows in set (0.00 sec)
mysql> insert into time1 value('1231231'),(12312313);
ERROR 1292 (22007): Incorrect time value: '12312313' for column 'a' at row 2
mysql> insert into time1 values(current_time),(now());
Query OK, 2 rows affected (0.08 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from time1;
+----------+
| a |
+----------+
| 22:05:27 |
| 22:05:27 |
+----------+
2 rows in set (0.00 sec)
-
Date 4 個位元組表示日期 (10000-01-01 ~ 9999-12-31)
mysql> create table date(a date);
Query OK, 0 rows affected (0.24 sec)
mysql> desc date
-> ;
+-------+------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------+------+-----+---------+-------+
| a | date | YES | | NULL | |
+-------+------+------+-----+---------+-------+
1 row in set (0.00 sec)
mysql> insert into date values('1920-01-01'),('1959@2@3'),('19591212');
Query OK, 3 rows affected (0.06 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from date;
+------------+
| a |
+------------+
| 1920-01-01 |
| 1959-02-03 |
| 1959-12-12 |
+------------+
3 rows in set (0.00 sec)
mysql> insert into date values('12-12-12'),('12$12%18'),('451117'),('111212');
Query OK, 4 rows affected (0.03 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> select * from date;
+------------+
| a |
+------------+
| 2012-12-12 |
| 2012-12-18 |
| 2045-11-17 |
| 2011-12-12 |
+------------+
7 rows in set (0.00 sec)
mysql> insert into date values(current_date),(now());
Query OK, 2 rows affected, 1 warning (0.11 sec)
Records: 2 Duplicates: 0 Warnings: 1
mysql> select * from date;
+------------+
| a |
+------------+
| 2016-09-09 |
| 2016-09-09 |
+------------+
2 rows in set (0.00 sec)
-
DATIME 8 個位元組表示日期和時間 . 範圍 10000-01-01 00:00:00 ~ 9999-12-31 23:59:59
mysql> create table datetime(a datetime);
Query OK, 0 rows affected (0.29 sec)
mysql> desc datetime
-> ;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| a | datetime | YES | | NULL | |
+-------+----------+------+-----+---------+-------+
1 row in set (0.00 sec)
mysql> insert into datetime values('20160909090909'),('2016:09:09:09:09:10'),('2016@09@09@09@09@11'),(current_datetime),
(now());
ERROR 1054 (42S22): Unknown column 'current_datetime' in 'field list'
mysql>
mysql> insert into datetime values('20160909090909'),('2016:09:09:09:09:10'),('2016@09@09@09@09@11'),(now());
Query OK, 4 rows affected (0.06 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> select * from datetime;
+---------------------+
| a |
+---------------------+
| 2016-09-09 09:09:09 |
| 2016-09-09 09:09:10 |
| 2016-09-09 09:09:11 |
| 2016-09-09 22:17:53 |
+---------------------+
4 rows in set (0.00 sec)
mysql> insert into datetime values('160909090909');
Query OK, 1 row affected (0.07 sec)
mysql> insert into datetime values('1609090909');
Query OK, 1 row affected (0.11 sec)
mysql> insert into datetime values('16090909');
Query OK, 1 row affected (0.10 sec)
mysql> insert into datetime values('160909');
Query OK, 1 row affected (0.05 sec)
mysql> insert into datetime values('1609');
ERROR 1292 (22007): Incorrect datetime value: '1609' for column 'a' at row 1
mysql> insert into datetime values('16');
ERROR 1292 (22007): Incorrect datetime value: '16' for column 'a' at row 1
mysql> select * from datetime;
+---------------------+
| a |
+---------------------+
| 2016-09-09 09:09:00 |
| 1609-09-09 00:00:00 |
| 2016-09-09 00:00:00 |
+---------------------+
4 rows in set (0.00 sec)
-
TIMESTAMP 4 個位元組表示日期和時間 1970-01-01 08:00:01 ~ 2038-01-19 11:14:07
Timestamp 範圍比 datetime 範圍小
mysql> create table timestamp(a timestamp);
Query OK, 0 rows affected (0.30 sec)
mysql> desc timestamp;
+-------+-----------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------+------+-----+-------------------+-----------------------------+
| a | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+-------+-----------+------+-----+-------------------+-----------------------------+
1 row in set (0.01 sec)
mysql> insert into timestamp values('60909090909');
ERROR 1292 (22007): Incorrect datetime value: '60909090909' for column 'a' at row 1
mysql> insert into timestamp values('160909090909');
Query OK, 1 row affected (0.07 sec)
mysql> insert into timestamp values('1609090909');
Query OK, 1 row affected (0.04 sec)
mysql> insert into timestamp values('16090909');
ERROR 1292 (22007): Incorrect datetime value: '16090909' for column 'a' at row 1
mysql> insert into timestamp values('160909');
Query OK, 1 row affected (0.07 sec)
mysql> insert into timestamp values('1609');
ERROR 1292 (22007): Incorrect datetime value: '1609' for column 'a' at row 1
mysql> insert into timestamp values();
Query OK, 1 row affected (0.08 sec)
mysql> insert into timestamp values(0);
ERROR 1292 (22007): Incorrect datetime value: '0' for column 'a' at row 1
mysql> select * from timestamp;
+---------------------+
| a |
+---------------------+
| 2016-09-09 09:09:09 |
| 2016-09-09 09:09:00 |
| 2016-09-09 00:00:00 |
| 2016-09-09 22:37:55 |
+---------------------+
4 rows in set (0.00 sec)
mysql> insert into timestamp values(current_timestamp);
Query OK, 1 row affected (0.06 sec)
mysql> insert into timestamp values(null);
Query OK, 1 row affected (0.17 sec)
mysql> select * from timestamp;
+---------------------+
| a |
+---------------------+
| 2016-09-09 09:09:09 |
| 2016-09-09 09:09:00 |
| 2016-09-09 00:00:00 |
| 2016-09-09 22:37:55 |
| 2016-09-09 22:38:40 |
| 2016-09-09 22:39:17 |
+---------------------+
6 rows in set (0.00 sec)
3.4 字串型別
-
Char 長度固定
-
Varchar 長度可變
-
TEXT 特殊字串型別
型別 |
允許的長度 |
儲存空間 |
TINYTEXT |
0~255 位元組 |
值的長度 +2 個位元組 |
TEXT |
0~65535 位元組 |
值的長度 +2 個位元組 |
MEDIUMTEXT |
0~167772150 位元組 |
值的長度 +3 個位元組 |
LONGTEXT |
0~4294967295 位元組 |
值的長度 +4 個位元組 |
-
ENUM 列舉型別
mysql> create table enum(a enum('x','y'));
Query OK, 0 rows affected (0.30 sec)
mysql> insert into enum values('x');
Query OK, 1 row affected (0.05 sec)
mysql> insert into enum values('y');
Query OK, 1 row affected (0.04 sec)
mysql> insert into enum values('z');
ERROR 1265 (01000): Data truncated for column 'a' at row 1
mysql> insert into enum values(); # 預設存為 null
Query OK, 1 row affected (0.08 sec)
mysql> insert into enum values(null); # 預設存為 null
Query OK, 1 row affected (0.06 sec)
mysql> select * from enum;
+------+
| a |
+------+
| x |
| y |
| NULL |
| NULL |
+------+
4 rows in set (0.00 sec)
-
Set 型別 類似列舉型別 ,
Set 可以取多個元素 , 列舉只能去一個 .
mysql> create table set1(a set('a','b','c','d','e'));
Query OK, 0 rows affected (0.25 sec)
mysql> desc set1
-> ;
+-------+--------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------------------+------+-----+---------+-------+
| a | set('a','b','c','d','e') | YES | | NULL | |
+-------+--------------------------+------+-----+---------+-------+
1 row in set (0.00 sec)
mysql> insert into set1 values('b');
Query OK, 1 row affected (0.06 sec)
mysql> insert into set1 values('b','d','e');
ERROR 1136 (21S01): Column count doesn't match value count at row 1
mysql> insert into set1 values('b,d,e');
Query OK, 1 row affected (0.07 sec)
mysql> select * from set1;
+-------+
| a |
+-------+
| b |
| b,d,e |
+-------+
2 rows in set (0.00 sec)
mysql> insert into set1 values('b,d,f');
ERROR 1265 (01000): Data truncated for column 'a' at row 1
mysql> insert into set1 values('b,d,a');
Query OK, 1 row affected (0.11 sec)
mysql> select * from set1;
+-------+
| a |
+-------+
| b |
| b,d,e |
| a,b,d |
+-------+
3 rows in set (0.00 sec)
4.5 二進位制型別
整數型別 |
取值範圍 |
BINARY(M) |
位元組數為 M ,允許長度為 ~ M 的字長二進位制字串 |
VARBINARY(M) |
允許長度為 ~ M 的字長二進位制字串 , 位元組數為值的長度加 1 |
BIT(M) |
M 二進位制資料, M 最大值為 64 |
TINYBLOB |
可變長二進位制資料,最多 255 位元組 |
BLOB |
可變長二進位制資料,最多 2^16-1 個位元組 |
MEDIUMBLOB |
可變長二進位制資料,最多 2^24-1 個位元組 |
LONGBLOB |
可變長二進位制資料,最多 2^32-1 個位元組 |
mysql> create table vb(a binary(4),b varbinary(4));
Query OK, 0 rows affected (0.23 sec)
mysql> insert into vb values('a','a');
Query OK, 1 row affected (0.06 sec)
mysql> select * from vb;
+------+------+
| a | b |
+------+------+
| a | a |
+------+------+
1 row in set (0.00 sec)
mysql> select length(a),length(b) from vb;
+-----------+-----------+
| length(a) | length(b) |
+-----------+-----------+
| 4 | 1 |
+-----------+-----------+
1 row in set (0.02 sec)
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/24237320/viewspace-2124773/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- MYSQL 資料型別MySQL 資料型別
- [Mysql]資料型別MySql資料型別
- MySQL資料型別MySql資料型別
- MySQL 的資料型別MySql資料型別
- 理解MySQL資料型別MySql資料型別
- MySQL的資料型別MySql資料型別
- MySQL基本資料型別MySql資料型別
- Mysql 資料型別之整數型別MySQL 資料型別
- 【MySQL資料型別3之--字元型別】MySql資料型別字元
- MySQL基礎之----資料型別篇(常用資料型別)MySql資料型別
- 【MySQL資料型別1之--數值型別】MySql資料型別
- MySQL資料型別DECIMAL用法MySql資料型別Decimal
- MySQL資料型別筆記MySql資料型別筆記
- mysql常見資料型別MySql資料型別
- mysql 常用的資料型別MySql資料型別
- MySQL入門--資料型別MySql資料型別
- mysql 資料型別TIMESTAMPMySQL 資料型別
- MySQL 資料型別詳解MySQL 資料型別
- 詳解MySQL資料型別MySql資料型別
- MySQL 資料型別 詳解MySQL 資料型別
- mysql資料型別小結MySql資料型別
- 5mysql資料型別MySql資料型別
- MySQL入門系列:MySQL資料型別MySql資料型別
- 【MySQL資料型別2之--日期時間型別】MySql資料型別
- Java 資料型別和 MySql 資料型別對應一覽表JavaMySQL 資料型別
- Mysql資料庫學習(二):資料型別(數值型別 日期和時間型別 字串型別)MySql資料庫資料型別字串
- MYSQL 資料型別儲存-數值型MySQL 資料型別
- mysql 配置注意事項、 mysql 資料型別MySQL 資料型別
- MySQL JSON資料型別操作MySqlJSON資料型別
- 06. MySQL的資料型別MySql資料型別
- 【MySQL】資料型別的基本用法MySql資料型別
- mysql 資料型別,字符集MySQL 資料型別
- Java 支援的資料型別與 MySQL 支援的資料型別對比Java資料型別MySql
- MySQL(二) 資料庫資料型別詳解MySql資料庫資料型別
- MYSQL資料庫型別與JAVA型別對應關係MySql資料庫型別Java
- Mysql 基礎資料型別(無時間相關型別)MySql資料型別
- MySQL資料型別操作(char與varchar)MySql資料型別
- mysql整數資料型別深入解析MySql資料型別