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

1. show variables like 'explicit%'; 来查看变量
2. TIMESTAMP默认CURRENT_TIMESTAMP和not null和on update CURRENT_TIMESTAMP
3. MySQL中只给表中的第一个TIMESTAMP字段设置默认值为系统日期, 如果有第二个TIMESTAMP类型, 则默认值设置为0
4. MySQL5.6之前,可以修改第二个timestamp类型字段为其他日期常量,但是不能再修改为current_timestamp.同一张表中只有一列的默认值为current_timestamp. 在MySQL5.6版本之后, 这个限制已经去掉了
5. 如果把1中的explict_defaults_for_timestamp设置为on, 则默认值 . not null 和 on update current_timestamp不会自动设置
6. TIMESTAMP重要特点, 就是和时区相关.
   • 当插入日期的时候, 会先转化为本地时区后存放
   • 当从数据库取出后, 也同样需要将日期转换为本地时区后显示

TIMESTAMP与DATETIME区别

1. timestamp支持的时间范围小, 而datetime范围大.两者都可以设置默认值和on update current_timestamp属性. 使得日期列可以随其他列更新而自动更新为最新时间.
2. timestamp在MySQL5.6.6版本之后增加了控制参数 explicit_defaults_for_timestamp, 如果设置为on 则timestamp需要显示指定默认值和on update current_timestamp属性. 如果设置为off, 则会自动设置默认值.
3. 当explicit_defaults_for_timestamp设置为off时, 表中额第一个TIMESTAMP列自动设置为系统时间. 如果在一个TIMESTAMP列中插入NULL, 则该列值会自动设置为当前的日期和时间. 在插入或更新一行但不明确给TIMESTAMP列赋值时也会自动设置该列的值为当前的日期和时间
4. TIMESTAMP的插入和查询都受当地时区的影响, 更能反映出实际的日期, DATETIME则只能反映出插入时当地的时区, 其他时区的人查看数据必然有很大的误差.
5. 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;