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)

ITPub博客

[Mysql] 3.Mysql 数据类型

最新文章