MySQL数据类型

MySQL 有三大类数据类型, 分别为数字、日期\时间、字符串, 这三大类中又更细致的划分了许多子类型:

数字类型:

  • 整数: tinyint、smallint、mediumint、int、bigint
  • 浮点数: float、double、real、decimal

日期和时间:

  • date、time、datetime、timestamp、year

字符串类型:

  • 字符串: char、varchar
  • 文本: tinytext、text、mediumtext、longtext
  • 二进制(可用来存储图片、音乐等): tinyblob、blob、mediumblob、longblob

整型

数值型数据类型主要用来存储数字,不同的数据类型提供不同的取值范围,可以存储的值的范围越大,其所需要的存储空间也会越大。整数类型的字段可以添加AUTO_INCREMENT自增约束条件。

类型

存储

最小值

最大值

(Bytes)

(Signed/Unsigned)

(Signed/Unsigned)

Tinyint

1

-128

127

0

255

Smallint

2

-32 768

32 767

0

65535

Mediumint

3

-8 388 608

8 388 607

0

16 777 215

Int

4

-2 147 483 648

2 147 483 647

0

4 294 967 295

Bigint

8

-9,223,372,036,854,775,808

9 223 372 036 854 775 807

0

18 446 744 073 709 551 615

验证整型字段有无符号及范围

整型字段默认有正负号
create table t1(id tinyint);
insert into t1 values(128),(-129);  数据超出范围无法插入

利用unsigned设置TINYINT为无符号型
create table t2(x tinyint unsigned);
insert into t2 values(-1),(256);  数据类型为无符号型且超出范围无法插入

利用不同的类型可以定义不同的数据范围
create table t3(x int unsigned);
insert into t3 values(4294967296);  超出int的范围无法插入

利用zerofill 显示时,不够8位用0填充,如果超出8位则正常显示
create table t5(x int(8) unsigned zerofill);
insert into t5 values(4294967296123);

强调:对于整型来说,数据类型后的宽度并不是存储限制,而是显示限制,所以在创建表时,如果字段采用的是整型类型,完全无需指定显示宽度, 默认的显示宽度,足够显示完整当初存放的数据

严格模式

我们有时候设置了char,tinyint,存储数据时超过它们的最大存储长度,发现数据也能正常存储进去,只是mysql帮我们自动截取了最大长度。但在实际情况下,我们应该尽量减少数据库的操作,缓解数据库的压力,让它仅仅只管理数据即可,这样的情况下就需要设置安全模式

like模糊查询
    %:匹配任意数量的任意字符
    _:匹配单个数量的任意字符
    
查看数据库配置中变量名包含mode的配置参数   
show variables like "%mode%";
修改安全模式
	set session  只在当前操作界面有效
	set global   全局有效
	
修改完之后退出当前客户端重新登陆即可
set global sql_mode ='STRICT_TRANS_TABLES'

如果不想让mysql帮你做自动去除末尾空格的操作,需要再添加一个模式
set global sql_mode="strict_trans_tables,PAD_CHAR_TO_FULL_LENGTH";

浮点型

类型

大小

范围(有符号)

范围(无符号)

用途

FLOAT

4 字节

(-3.402 823 466 E+38,-1.175 494 351 E-38),0,(1.175 494 351 E-38,3.402 823 466 351 E+38)

0,(1.175 494 351 E-38,3.402 823 466 E+38)

单精度 浮点数值

DOUBLE

8 字节

(-1.797 693 134 862 315 7 E+308,-2.225 073 858 507 201 4 E-308),0,(2.225 073 858 507 201 4 E-308,1.797 693 134 862 315 7 E+308)

0,(2.225 073 858 507 201 4 E-308,1.797 693 134 862 315 7 E+308)

双精度 浮点数值

DECIMAL

对应DECIMAL(M,D) ,如果M>D,为M+2否则为D+2

依赖于M和D的值

依赖于M和D的值

小数值

单精度:FLOAT(M,D) [UNSIGNED] [ZEROFILL],M表示数字的总长度,D表示小数点后的数字长度,UNSIGNED最大存储范围是99999.99,FLOAT的存储范围是-3.4E38~3.4E38;

双精度:DOUBLE(M,D) [ UNSIGNED ] [ZEROFILL],最大存储围为-1.79E308~1.79E308;

定点类型:浮点型在数据库中存放的是近似值,而定点类型在数据库中存放的是精确值。 decimal(M,D) 参数M < 65 是总个数,D < 30 且 D < M 是小数位。

# 存储限制
float(255,30)  m最大值为255,d最大值为30
double(255,30)  m最大值为255,d最大值为30
decimal(255,30)  m最大值为65,d最大值为30

# 精确度验证
create table t9(x float(255,30));
create table t10(x double(255,30));
create table t11(x decimal(65,30));

insert into t9 values(1.111111111111111111111111111111);
insert into t10 values(1.111111111111111111111111111111);
insert into t11 values(1.111111111111111111111111111111);


mysql> select * from t9;  随着小数的增多,精度变得不准确
+----------------------------------+
| x                                |
+----------------------------------+
| 1.111111164093017600000000000000 |
+----------------------------------+

mysql> select * from t10;  随着小数的增多,精度比float要高,但也会变得不准确
+----------------------------------+
| x                                |
+----------------------------------+
| 1.111111111111111200000000000000 |  
+----------------------------------+

mysql> select * from t11;  随着小数的增多,精度始终准确
+----------------------------------+
| x                                |
+----------------------------------+
| 1.111111111111111111111111111111 | 
+----------------------------------+

三者区别:
精度由低到高 :float→double→decimal

字符类型(char与varchar)

类型

大小

用途

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的区别

  • char(n) 若存入字符数小于n,则以空格补于其后,查询之时再将空格去掉。所以char类型存储的字符串末尾不能有空格,varchar不限于此。
  • char(n) 固定长度,char(4)不管是存入几个字符,都将占用4个字节,varchar是存入的实际字符数+1个字节(n<=255)或2个字节(n>255),所以varchar(4),存入3个字符将占用4个字节。
  • char类型的字符串检索速度要比varchar类型的快。
create table t1(name char(4));  超出四个字符报错,不够四个字符空格补全
create table t2(name varchar(4));    超出四个字符报错,不够四个有几个就存几个

验证存储限制
insert into t1 values('hello');  报错Data too long for column 'name' at row 1
insert into t2 values('hello');  报错Data too long for column 'name' at row 1
验证存储长度
insert into t1 values('a'); #'a    '
insert into t2 values('a'); #'a'

通过*无法查看真正的结果
select * from t1;
select * from t2;  

通过char_length()查看真正的结果
select char_length(name) from t1; # 4
select char_length(name) from t2; # 1

针对char类型,mysql在存储时会将数据用空格补全存放到硬盘中。但是会在读出结果的时候自动取掉末尾的空格,如果不想让mysql帮你做自动去除末尾空格的操作,需要再添加一个模式"strict_trans_tables,PAD_CHAR_TO_FULL_LENGTH"。

日期类型

表示时间值的日期和时间类型为DATETIME、DATE、TIMESTAMP、TIME和YEAR。每个时间类型有一个有效值范围和一个“零”值,当指定不合法的MySQL不能表示的值时使用“零”值。

类型

大小 (字节)

范围

格式

用途

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

混合日期和时间值,时间戳

create table student(
	id int,
  name char(16),
  born_year year,
  birth date,
  study_time time,
  reg_time datetime
);
insert into student values(1,'linwow','2019','2019-05-09','11:11:00','2019-11-11 11:11:11');

mysql> select * from student;
+------+--------+-----------+------------+------------+---------------------+
| id   | name   | born_year | birth      | study_time | reg_time            |
+------+--------+-----------+------------+------------+---------------------+
|    1 | linwow |      2019 | 2019-05-09 | 11:11:00   | 2019-11-11 11:11:11 |
+------+--------+-----------+------------+------------+---------------------+

枚举与集合类型

分类

  • 枚举enum 多选一
    枚举 enum 的存储原理:实际上字段上所存储的值并不是真正的字符串,而是字符串对应的下标,当系统设定枚举类型的时候会给枚举中每个元素定义一个下标,这个下标规则从1开始,
    enum(1=>‘male’,2=>‘female’,3=>‘others’)
    在MySQL中系统是自动进行类型转换的,如果数据碰到“+、-、*、/” 系统回自动将数据转换为数字,而普通的字符串转换成数值0,我们利用这个特点用select 字段名+0 from 表名,可以对上边的原理进行结论进行验证。
    既然实际enum 字段存储的结果是数值,那么在进行数据插入的时候就可以使用对应的数值进行操作。
  • 集合set 多选多
    集合:是一种将多个数据选项可以同时保存的数据类型,本质是将指定的项按照对应的二进制位来进行控制,1表示该选型被选中,0表示该选项没有被选中。
    系统为set提供了多个字节进行保存,但是系统会自动计算来选择具体的存储单元。
create table user(
	id int,
  	name char(16),
  	gender enum('male','female','others')
);
insert into user values(1,'wowlin','xxx');  报错
insert into user values(2,'linwow','male');   正确!
insert into user values(3,'wow',3);   正确!

create table teacher(
	id int,
      name char(16),
  	gender enum('male','female','others'),
  	hobby set('read','sleep','sanna','sing')
);
insert into teacher values(1,'peter','male','read,sleep')  
集合也可以只存一个