MySQL之数据类型

作者: 8411e9740257 | 来源:发表于2020-03-02 15:12 被阅读0次

MySql 常用指令
MySql___(4) MySQL 数据类型
MYSQL-数据类型优化
MySQL
细节爆炸！腾讯用13个案例实战讲明白MySQL，没想到这么全
MySQL学习笔记(4) - 数据类型和运算符
mysql学习
Java数据类型和MySql数据类型对应表
MySQL 之数据类型
MySQL之数据类型

引用

整型

数据类型	字节数	带符号最小值	带符号最大值	不带符号最大值
TINYINT	1	-128	127	255
SMALLINT	2	-32768	32767	65535
MEDIUMINT	3	-8388608	8388607	16777215
INT	4	-2147483648	2147483647	4294967295
BIGINT	8	-9223372036854775808	9223372036854775807	18446744073709551616

整型（N）形式
在开发中，我们会碰到有些定义整型的写法是int(11)，这种写法从我个人开发的角度看我认为是没有多大用，不过作为一个知识点做一下讲解吧。
int(N)我们只需要记住两点：
- 无论N等于多少，int永远占4个字节
- N表示的是显示宽度，不足的用0补足，超过的无视长度而直接显示整个数字，但这要整型设置了unsigned zerofill才有效

常用SQL语句

-- 如果存在就删除表
drop table if exists test_int;

-- 新建数据表
create table test_int (
a int(5) ,
b int(5) unsigned,
c int(5) unsigned zerofill,
) engine=innodb charset=utf8;

-- 插入数据
insert into test_int values(1,2,3);

-- 删除数据
delete from test_int where a = 1;

-- 更新数据
update test_int set b = 6 where a= 1;

-- 查询数据
select * from test_int;

浮点型

数据类型	字节数	备注
float	4	单精度浮点型
double	8	双精度浮点型
decimal		定点型

float(M,D)、double(M、D)的用法规则
- 前面的M指定了总的位数，后面的D指定了小数的位数，所以整数位数为M-D
- D表示浮点型数据小数点之后的精度，假如超过D位则四舍五入，即1.233四舍五入为1.23，1.237四舍五入为1.24
- M表示浮点型数据总共的位数，D=2则表示总共支持五位，即小数点前只支持三位数
- 不指定M、D的时候，会按照实际的精度来处理
decimal和float/double的区别，主要体现在两点上：
- float/double在db中存储的是近似值，而decimal则是以字符串形式进行保存的
- decimal(M,D)的规则和float/double相同，但区别在float/double在不指定M、D时默认按照实际精度来处理而decimal在不指定M、D时默认为decimal(10, 0)

常用SQL语句

-- 新建数据表
create table if not exists test_float (
  f float(5,2),
  d double(5,3),
) engine=innodb charset=utf8;

-- 插入数据
insert into test_float values(1.23,1.234);

-- 删除数据
delete from test_float where f >= 1.23;

-- 更新数据
update test_float set d = 2.234 where f >= 1.23;

-- 查询数据
select * from test_float;

日期类型

日期时间类型	占用空间	日期格式	最小值	最大值	零值表示
DATETIME	8 bytes	YYYY-MM-DD HH:MM:SS	1000-01-01 00:00:00	9999-12-31 23:59:59	0000-00-00 00:00:00
TIMESTAMP	4 bytes	YYYY-MM-DD HH:MM:SS	19700101080001	2038 年的某个时刻	00000000000000
DATE	3 bytes	YYYY-MM-DD	1000-01-01	9999-12-31	0000-00-00
TIME	3 bytes	HH:MM:SS	-838:59:59	838:59:59	00:00:00
YEAR	1 bytes	YYYY	1901	2155	0000

DATETIME
DATETIME 用于表示年月日时分秒，是 DATE 和 TIME 的组合，并且记录的年份（见上表）比较长久。如果实际应用中有这样的需求，就可以使用 DATETIME 类型。
TIMESTAMP
TIMESTAMP 用于表示年月日时分秒，但是记录的年份（见上表）比较短暂。
TIMESTAMP 和时区相关，更能反映当前时间。当插入日期时，会先转换为本地时区后再存放；当查询日期时，会将日期转换为本地时区后再显示。所以不同时区的人看到的同一时间是不一样的。
表中的第一个 TIMESTAMP 列自动设置为系统时CURRENT_TIMESTAMP）。当插入或更新一行，但没有明确给 TIMESTAMP 列赋值，也会自动设置为当前系统时间。如果表中有第二个 TIMESTAMP 列，则默认值设置为0000-00-00 00:00:00。
TIMESTAMP 的属性受 Mysql 版本和服务器 SQLMode 的影响较大。
如果记录的日期需要让不同时区的人使用，最好使用 TIMESTAMP。
DATE
DATE 用于表示年月日，如果实际应用值需要保存年月日就可以使用 DATE。
TIME
TIME 用于表示时分秒，如果实际应用值需要保存时分秒就可以使用 TIME。
YEAR
YEAR 用于表示年份，YEAR 有 2 位（最好使用4位）和 4 位格式的年。默认是4位。如果实际应用只保存年份，那么用 1 bytes 保存 YEAR 类型完全可以。不但能够节约存储空间，还能提高表的操作效率。

如需存储时间戳，可用BIGINT保存指定时间的毫秒值。

-- 新建数据表
create table if not exists test_datetime (
  date0 date,
  time0 time,
  year0 year,
  timestamp0 timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP(),
  datetime0 datetime,
  create_time timestamp default current_timestamp comment "创建时间",
  update_time timestamp default current_timestamp on update current_timestamp comment "修改时间",
) engine=innodb charset=utf8;

-- 插入数据
insert into test_datetime values(now(),now(),now(),now(),now());

-- 删除数据
delete from test_datetime where date0 < now();

字符串

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

区别
- CHAR 和 VARCHAR 类型类似，但它们保存和检索的方式不同。它们的最大长度和是否尾部空格被保留等方面也不同。在存储或检索过程中不进行大小写转换。
- BINARY 和 VARBINARY 类似于 CHAR 和 VARCHAR，不同的是它们包含二进制字符串而不要非二进制字符串。也就是说，它们包含字节字符串而不是字符字符串。这说明它们没有字符集，并且排序和比较基于列值字节的数值值。
- BLOB 是一个二进制大对象，可以容纳可变数量的数据。有 4 种 BLOB 类型：TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB。它们区别在于可容纳存储范围不同。
- TEXT 有 4 种文本类型：TINYTEXT、TEXT、MEDIUMTEXT 和 LONGTEXT。对应的这 4 种 BLOB 类型，可存储的最大长度不同，可根据实际情况选择。
char和varchar类型
1. char是固定长度字符串，其长度范围为0~255且与编码方式无关，无论字符实际长度是多少，都会按照指定长度存储，不够的用空格补足；
2. varchar为可变长度字符串，在utf8编码的数据库中其长度范围为0~21844;
3. char实际占用的字节数即存储的字符所占用的字节数，varchar实际占用的字节数为存储的字符+1或+2或+3;
4. MySQL处理char类型数据时会将结尾的所有空格处理掉而varchar类型数据则不会;
5. 如果char插入的字符串的长度已经大于了可以插入的最大值，那么这个时候数据库会报错，而varchar会截取字符串。
varchar、text和blob
1. 先说一下text，text和varchar是一组既有区别又有联系的数据类型，其联系在于当varchar(M)的M大于某些数值时，varchar会自动转为text：
  M>255时转为tinytext
  M>500时转为text
  M>20000时转为mediumtext
  所以过大的内容varchar和text没有区别，同事varchar(M)和text的区别在于：
2. 单行64K即65535字节的空间，varchar只能用63352/65533个字节，但是text可以65535个字节全部用起来。
  text可以指定text(M)，但是M无论等于多少都没有影响
  text不允许有默认值，varchar允许有默认值
  varchar和text两种数据类型，使用建议是能用varchar就用varchar而不用text（存储效率高），varchar(M)的M有长度限制，之前说过，如果大于限制，可以使用mediumtext（16M）或者longtext（4G）。
3. 至于text和blob，简单过一下就是text存储的是字符串而blob存储的是二进制字符串，简单说blob是用于存储例如图片、音视频这种文件的二进制数据的。
ENUM类型（枚举类型）
- ENUM('值1'，'值2'，...，'值n')
  n-代表参数列表中的第n个值，这些值末尾的空格会被系统直接删除。
  ENUM类型的值只能取列表中的一个元素。
  取值列表中最多能有65535成员。
  列表中的每一个值都有一个顺序排列的编号，MySQL存入的是这个编号，而不是值。
SET类型
在创建表时，SET类型的取值范围就以列表的形式指定了。
SET('值1'，'值2'，...，'值n')
SET类型的值最多只以是有64个元素构成的组合。取值可以是其中的一个或多个。其它特性，与ENUM类似。