mysql basic_MySQL数据类型(basic)

基本类型

整数类型

字节

最小值

最大值

TINYINT

1

有符号 -128

无符号 0

有符号 127

无符号 255

SMALLINT

2

有符号 -32768

无符号 0

有符号 32767

无符号 65535

MEDIUMINT

3

有符号 -8388608

无符号 0

有符号 8388607

无符号 1677215

INT INTEGER

4

有符号 -2147483648

无符号 0

有符号 2147483647

无符号 4294967295

浮点数类型

字节

最小值

最大值

FLOAT

4

\(\pm1.175494351E-38\)

\(\pm3.102823466E+38\)

DOUBLE

8

\(\pm2.2250738585074014E-308\)

\(\pm1.7976931348623157E+308\)

定点数类型

字节

描述

描述

DEC(M,D),

DECIMAL(M,D)

M+2

最大取值范围与DOUBLE相同,

给定DECIMAL的有效取值范围

由M和D决定

位类型

字节

最小值

最大值

BIT(M)

1~8

BIT(1)

BIT(64)

日期类型

日期和时间类型

字节

最小值

最大值

DATE

4

1000-01-01

9999-12-32

DATETIME

8

1000-01-01 00:00:00

9999-12-31 23:59:59

TIMESTAMP

4

19700101080001

2038年的某个时刻

TIME

3

-838:59:59

838:59:59

YEAR

1

1901

2155

TIMESTAMP

show variables like 'explicit%'; 来查看变量

TIMESTAMP默认CURRENT_TIMESTAMP和not null和on update CURRENT_TIMESTAMP

MySQL中只给表中的第一个TIMESTAMP字段设置默认值为系统日期, 如果有第二个TIMESTAMP类型, 则默认值设置为0

MySQL5.6之前,可以修改第二个timestamp类型字段为其他日期常量,但是不能再修改为current_timestamp.同一张表中只有一列的默认值为current_timestamp. 在MySQL5.6版本之后, 这个限制已经去掉了

如果把1中的explict_defaults_for_timestamp设置为on, 则默认值 . not null 和 on update current_timestamp不会自动设置

TIMESTAMP重要特点, 就是和时区相关.

当插入日期的时候, 会先转化为本地时区后存放

当从数据库取出后, 也同样需要将日期转换为本地时区后显示

TIMESTAMP与DATETIME区别

timestamp支持的时间范围小, 而datetime范围大.两者都可以设置默认值和on update current_timestamp属性. 使得日期列可以随其他列更新而自动更新为最新时间.

timestamp在MySQL5.6.6版本之后增加了控制参数 explicit_defaults_for_timestamp, 如果设置为on 则timestamp需要显示指定默认值和on update current_timestamp属性. 如果设置为off, 则会自动设置默认值.

当explicit_defaults_for_timestamp设置为off时, 表中额第一个TIMESTAMP列自动设置为系统时间. 如果在一个TIMESTAMP列中插入NULL, 则该列值会自动设置为当前的日期和时间. 在插入或更新一行但不明确给TIMESTAMP列赋值时也会自动设置该列的值为当前的日期和时间

TIMESTAMP的插入和查询都受当地时区的影响, 更能反映出实际的日期, DATETIME则只能反映出插入时当地的时区, 其他时区的人查看数据必然有很大的误差.

TIMESTAMP的属性受MySQL版本和服务器SQLMode的影响很大.

字符串类型

字符串类型

字节/字符

描述及存储需求

CHAR(M)

M个字符

M为0~255整数

VARCHAR(M)

0~M个字符

M为0~65535之间整数, 值的长度+1一个字节

BITARY(M)

M个字符

M为0~255之间的整数

VARBINARY(M)

0~M个字符

M为0~65535之间整数,值的长度+1字节

TINYBLOB

允许长度0~255字节,值长度+1字节

BLOB

0~65535 value+2

MEDIUMBLOB

0~167772150 value+3

LONGBLOB

0~4294967295 value+4

TINYTEXT

0~255 value+1

TEXT

0~65535 value+2

MEDIUMTEXT

0~167772150 value+3

LONGTEXT

0~4294967295 value+4

CHAR和VARCHAR类型

CHAR与VARCHAR很类似, 都是用来保存MySQL较短的字符串. 两者的区别主要是存储方式的不同

CHAR列的长度固定为创建表时声明的长度, 长度可以为从0~255的任何值

VARCHAR列中的值为可变长度, 长度可以指定0~65535之间的值

在检索的时候CHAR去除了尾部的空格, 而VARCHAR则保留了这些空格

BINARY和VARBINARY类型

类似于CHAR和VARCHAR

不同的是它们是包含二进制字符串而不包含非二进制字符串

create table t(

c BINARY(3)

);

insert into t set c='a';

select *,hex(c),c='a',c='a\0',c='a\0\0' from t;

从上面可以看出 BINARY(3)列当插入"a"变为"a\0\0"

ENUM类型

枚举类型, 值范围需要在创建表时通过枚举方式显式指定 对1~255个成员的枚举需要1个字节存储 对255~65535个成员需要2个字节存储.

最多允许拥有65535个成员

create table t (gender enum('M','F'));

insert into t values('M'),('1'),('f'),(NULL);

select * from t;

上述例子中可以看出ENUM类型是忽略大小写的, 1代表了枚举中的第一个元素M

SET类型

SET和ENUM类型非常类似, 也是由一个字符串对象, 里面可以包含0~64个成员, 成员不同存储上也不同

1~8 -> 一字节

9~16 -> 2

17~24 -> 3

25~32 -> 4

33~64 ->8

create table t(col set('a','b','c','d'))

insert into t values('a,b'),('a,d,a'),('a,c'),('a');

select * from t;

JSON类型

JSON是JavaScript Object Notation缩写(很有用), 它是一种数据交换格式.

JSON出现前数据交换大多使用XML类传递数据

但是XML规范太JB多了

在2002年雅虎工程师Douglas Crockford于2002年发明了JSON, 风靡WEB

自5.7.8版本起, MySQL开始支持JSON类型. 此前通常使用VARCHAR或TEXT来保存JSON格式数据.

JSON有如下优点

JSON数据类型会自动校验数据是否为JSON格式. 如果不是JSON格式会报error

MySQL提供了一组操作JSON数据的内置函数, 可以方便地提取各类数据, 可以修改特定的键值.

优化的存储格式, 存储在JSON列中的JSON数据被转换成内部的存储格式, 允许快速读取

create table t1(id1 json);

insert into t1 values('{"age":20,"time":"2018-07-14 10:52:00"}');

select JSON_TYPE('"abc"') js1,JSON_TYPE('[1,2,"abc"]') js2,JSON_TYPE('{"k1":"value"}') js3;