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

-> ;

+-------+--------------+------+-----+---------+-------+

+-------+--------------+------+-----+---------+-------+

| 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

-> ;

+-------+--------------------------------+------+-----+---------+-------+

+-------+--------------------------------+------+-----+---------+-------+

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

+-------+---------+------+-----+---------+-------+

+-------+---------+------+-----+---------+-------+

| 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

-> ;

+-------+--------------+------+-----+---------+-------+

+-------+--------------+------+-----+---------+-------+

| 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

-> ;

+-------+------+------+-----+---------+-------+

+-------+------+------+-----+---------+-------+

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

+----------+

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 |

+-----------+

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 |

+----------+

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

-> ;

+-------+------+------+-----+---------+-------+

+-------+------+------+-----+---------+-------+

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

+------------+

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

-> ;

+-------+----------+------+-----+---------+-------+

+-------+----------+------+-----+---------+-------+

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

+-------+-----------+------+-----+-------------------+-----------------------------+

+-------+-----------+------+-----+-------------------+-----------------------------+

+-------+-----------+------+-----+-------------------+-----------------------------+

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 |

+------+

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

-> ;

+-------+--------------------------+------+-----+---------+-------+

+-------+--------------------------+------+-----+---------+-------+

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

[Mysql] 3.Mysql 資料型別

相關文章