MySQL——常见数据类型

数据类型是什么

​ 数据类型（data_type）是指系统中所允许的数据的类型。MySQL数据类型定义了列中可以存储什么数据以及该数据怎样存储的规则。数据库中的每个列都应该有适当的数据类型，用于限制或允许该列中存储的数据。例如，列中存储的为数字，则相应的数据类型应该为数值类型。

MYSQL常见数据类型

​ MySQL支持多种类型，大致可以分为四类：数值型、浮点型、日期/时间和字符串(字符)类型。

1. 整型

整数类型	字节	最小值	最大值
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
BIGINT	8	有符号:-9223372036854775808 无符号:0	有符号:9223372036854775807 无符号:18447644073709551615

特点:

• 如果不设置无符号还是有符号，默认是有符号，如果想设置无符号，需要添加unsigned关键字

• 如果插入的数值超出了整型的范围，会报out of range异常，并且插入失败

  create table a(
  	b int(10) unsigned NOT NULL default 0
  );
  

2. 浮点型

浮点数类型	字节	最小值	最大值
FLOAT	4	±1.175494351E-38	±3.402823466E+38
DOUBLE	8	±2.2250739595072014E-308	±1.7976931348623157E+308
定点整数型	字节	描述
DEC(M,D) DECIMAL(M,D)	M+2	最大值与DOUBLE相同，给定DECIMAL 的有限制范围由M和D决定
位类型	字节	最小值	最大值
BIT(M)	1~8	BIT(1)	BIT(64)

​ 定点数的精度更大 ，M是整数部位+小数部位，D是小数点后的位数。如果小数位超过指定长度，则进行四舍五入截取。整数位长度超出则同int一样报错，插入失败。m,d可省略，如果decimal，则M默认为10，D默认为0。float与double，则根据插入的数据的精度的精度来决定精度。

使用方式：

• float(M,D)

• double(M,D)

• decimal(M,D)

  create table t3 (
  	a float(10, 2),
  	b double(10, 2),
  	c decimal(10, 2)
  );
  

3. 字符串类型

字符串类型	大小	用途
CHAR	0-255 bytes	定长字符串
VARCHAR	0-65535 bytes	变长字符串
TINYBLOB	0-255 bytes	不超过 255 个字符的二进制字符串
TINYTEXT	0-255 bytes	短文本字符串
BLOB	0-65 535 bytes	二进制形式的长文本数据
TEXT	0-65 535 bytes	长文本数据
MEDIUMBLOB	0-16 777 215 bytes	二进制形式的中等长度文本数据
MEDIUMTEXT	0-16 777 215 bytes	中等长度文本数据
LONGBLOB	0-4 294 967 295 bytes	二进制形式的极大文本数据
LONGTEXT	0-4 294 967 295 bytes	极大文本数据

char与varchar的区别：

4. 日期值

​ 表示时间值的日期和时间类型为DATETIME、DATE、TIMESTAMP、TIME和YEAR。

类型	大小 ( bytes)	范围	格式	用途
DATE	3	1000-01-01/9999-12-31	YYYY-MM-DD	日期值
TIME	3	‘-838:59:59’/‘838:59:59’	HH:MM:SS	时间值或持续时间
YEAR	1	1901/2155	YYYY	年份值
DATETIME	8	1000-01-01 00:00:00/9999-12-31 23:59:59	YYYY-MM-DD HH:MM:SS	混合日期和时间值
TIMESTAMP	4	1970-01-01 00:00:00/2038结束时间是第 2147483647 秒，北京时间 2038-1-19 11:14:07，格林尼治时间 2038年1月19日 凌晨 03:14:07	YYYYMMDD HHMMSS	混合日期和时间值，时间戳

​ 1. datetime

create table t6(
	create_time datetime
);
insert into t6 values('1999-01-01 02:03:59');
insert into t6 values('1999/01/01 02:03:59');
insert into t6 values(now());    # 插入现在的时间

2. time

   Time值得范围是从’-838:59:59’ 到’838:59:59’，Time类型不仅可以用于表示一天的时间（必须小于24小时）,还可能为某个过去的时间或两个事件的时间间隔（可以大于24小时，或者未负）

create table t7(
	create_time time
);
-- 时间的范围是: [-838:59:59 - 838:59:59]
insert into t7 values('100:59:59');
insert into t7 values('-100:59:59');

3. timestamp时间戳类型

   • 时间戳类型在显示方面和datetime是一样的，在存储上不一样。当插入日期时，会先转换为本地时区后再存放；当查询日期时，会将日期转换为本地时区后再显示。所以不同时区的人看到的同一时间是 不一样的。
   • 范围从1970-1-1 0:0:0 到 2038-1-19 11:14:07
   • 时间戳使用四个字节表示
   • 该值大小与存储的位长有关：2**(4 * 8 -1)

   create table t8(
   	create_time timestamp
   );
   insert into t8 values(now());
   insert into t8 values('2000-01-01 01:01:01');
   insert into t8 values('2038-01-19 11:14:07');
   insert into t8 values('2038-01-19 11:14:08');   # 报错
   

4. year

create table t9(
	create_time year
);
-- 从1900年开始 - 1900+255
insert into t9 values(now());
insert into t9 values('2155');
insert into t9 values('2156');   # 报错

5. 布尔值

mysql中的bool是0和1，代表false和true。mysql默认会转化为tinyint(1)类型

MySQL数据库的布尔类型BOOL或称布尔类型BOOLEAN，等同于微整型TINYINT(1)。MySQL数据库数据类型分类中确实存在布尔类型，但是MySQL数据库并没有真正实现布尔类型，而是借助微整型的方式实现，并且创建数据库表结构的时候，即使字段定义属性设置为布尔类型BOOL或布尔类型BOOLEAN，都会被默认改写成TINYINT(1)。

create table t10(
	num boolean
);
insert into t10 set num=True;
insert into t10 set num=False;
insert into t10 set num=1;
insert into t10 set num=10;
insert into t10 set num=0;

​ 建议：MySQL数据库产品没有真正实现对布尔类型的支持，建议大家不要使用MySQL布尔类型BOOL或布尔类型BOOLEAN，而是使用数据库类型微整型TINYINT替代。

6. 枚举(enum)

enum（可能出现的元素列表）,实现将所有可能出现的结果都设计好，实际上存储的数据必须是规定好的数据中的一个。

create table t4 (
    name varchar(32),
	sex enum('男','女','保密') default '保密'
);
-- 枚举类型的计数默认从1开始
insert into t4 set name='张三',sex=1;
-- 使用有效数据插入
insert into t4 set name='张三',sex='男';
-- 错误使用
insert into t4 set name='张三',sex='man'; -- 错误，没有该元素

优点：

1. 限制了可选值
2. 节省了空间
3. 运行效率高

缺点：

1. MySQL数据库枚举类型字段的元素增加，必须以尾部追加的方式，否则影响数据库提供数据服务；
2. 枚举类型字段不再需要的元素，也不能进行删除，否则影响数据库提供数据服务；
3. MySQL数据库枚举类型的字段定义属性元素值，不能随意调整其顺序，否则影响数据库提供数据服务；

枚举原理：

枚举在进行数据规范的时候（定义的时候），系统会自动建立一个数字与枚举元素的对应关系（关系放到日志中）；然后在进行数据插入的时候，系统自动将字符转换成对应的数字存储，然后在进行数据提取的时候，系统就自动将数字转换成字符串显示。因为我枚举实际存储的是数值，所以可以直接插入数值

7. 集合

SET是一个字符串对象，可以有零或多个值，其值来自表创建时规定的允许的一列值。指定包括多个SET成员的SET列值时各成员之间用逗号(‘,’)间隔开。这样SET成员值本身不能包含逗号。set的容纳范围为64个不同的成员…set其实和枚举差不多，set指定了一个集合范围，在我们插入数据的时候，需要插入在set范围之内的元素，如果插入了未被包含的元素，那么就会发出警告…

​ SET最多可以有64个不同的成员。类似于复选框, 有多少可以选多少。

create table t5 (
	name varchar(32),
	hobby set('吃','睡','玩','喝')
);
insert into t5 values('张三','睡,玩,吃,喝');
insert into t5 values('李四','睡,玩');