MySQL字段类型

目录

• 字符编码
• 存储引擎
• 创建表的完整语法
• 字段类型之整型
• 字段类型之浮点型
• 字段类型之字符串
• 数字含义
• 字段类型之枚举与集合
• 字段类型之日期类型
• 字段类型约束条件

内容 字符编码 查看MySQL默认字符编码

\s

5.x系列显示的编码有很多种：Latin1；gbk

8.x系列：统一显示utf8mb4

utf8mb4是utf8优化版本，支持存储表情

统一字符编码

5.X默认编码有多种，可能会导致乱码的情况，所以应该统一编码

my-default.ini配置文件

• 步骤1：拷贝一份该配置文件并修改名称为my.ini
• 步骤2：清空my.ini文件内的内容
  
• 步骤3：添加固定的配置信息即可
• 步骤4：保存并重启服务端即可生效：

配置信息：

[mysqld]

character-set-server=utf8

collation-server=utf8_general_ci

[client]

default-character-set=utf8

[mysql]

default-character-set=utf8

存储引擎 存储引擎可以理解为处理数据的不同方式 查看存储引擎

show engines

引擎

MyISAM：5.1之前版本MySQL默认的存储引擎；特点:存取数据的速度快，但是功能很少，安全性较低

InnoDB：5.1之后版本MySQL默认的存储引擎；特点：有诸多功能，安全性较高，存取速度没有MyISAM快

BlackHole：任何写入的数据都会立刻消失（类似于垃圾回收处理站）

Memory：以内存作为数据存取地，速度快但是断电立刻丢失

自定义选择存储引擎

create table t1(id int)engine=myisam;

create table t2(id int)engine=innodb;

create table t3(id int)engine=blackhole;

create table t4(id int)engine=memory;

创建表的完整语法

create table 表名(字段名1 字段类型(数字) 约束条件, 字段名2 字段类型(数字) 约束条件, 字段名3 字段类型(数字) 约束条件 );

注意事项

字段名和字段类型是必须的（否则会报错）

数字和约束条件是可选的

约束条件可以写多个，空格隔开即可

最后一行字段结尾不能加逗号

字段类型之整型 整型

tinyint           1bytes

smallint        2bytes

int                 4bytes

bigint            8bytes

存储范围

注意事项

需要考虑正负数的问题，如果需要存储负数，则需要占据一个比特位（比如1bytes（1bytes=2的八次方，256）如果需要存储负数，那么需要占据一个比特位放负数符号，剩下2的七次方）就剩128了

注意手机号如果使用整型来存储，需要使用bigint才可以

有时候看似需要使用数字类型存储的数据其实可能使用的是字符串，因为字符串可以解决不同语言对数字不精确的缺陷

create table t2(id tinyint); 

insert into t5 values(-129),(256);

如果是在5.6版本不会报错 会自动处理成最大范围（没有意义）

修改配置：

• 步骤1:set global sql_mode = 'STRICT_TRANS_TABLES';
• 步骤2:退出客户端 重新登录即可

如果是在5.7及以上版本 则会直接报错（更加合理）

验证发现所有的整型都默认带有正负号，如何修改不带正负号(约束条件)

字段类型之浮点型 浮点型

float(255,30) 总共255位 小数位占30位 double(255,30) 总共255位 小数位占30位 decimal(65,30) 总共65位 小数位占30位

验证浮点型从上往下精确度越来越高；正常业务，使用float足够

字段类型之字符类型 字符类型

char；varchar

区别在于一个是定长一个是变长

char(n)：定长；最大只能存储n个字符，超出则报错，不够则空格填充至n个

varchar(n)：变长；最大只能存储四个字符，超出则报错 不够则有几个存几个

验证定长与变长特性

create table t12(name char(4));

create table t13(name varchar(4));

insert into t12 values('hello');

insert into t13 values('hello');

如果是5.6版本并且没有修改严格模式 则会自动截取四个字符（不合理）

临时修改

• 步骤1：set global sql_mode = 'STRICT_TRANS_TABLES';
• 步骤2：退出客户端，重新登录即可

永久修改

修改my.ini配置文件

sql_mode ='STRICT_TRANS_TABLES,ONLY_FULL_GROUP_BY'

重启服务端之后永久生效

char_length()

char_length()：获取字段数据的长度

该方法无法直接获取到定长的真实长度，因为MySQL在存数据的时候会自动填充空格在取数据的时候又会自动移除空格

让MySQL在取数据的时候不自动移除空格：

单次修改：set session sql_mode = 'pad_char_to_full_length'

char与varchar的区别

char：整存整取，速度快，会造成一定的存储空间浪费

varchar：节省存储空间，存取数据的速度没有char快（取数据不知道数据的精确长度）

varchar在存数据的时候会生成一个1bytes的报头，记录数据长度

varchar在取数据的时候先会读取1bytes的报头，从中获取真实数据长度

现在varchar使用频率也越来越高

两者都有使用场景：

varchar：针对统一中国人的姓名

char：规模较小，数据量相对固定的字典

数字含义

字段类型括号内的数字大部分情况下是用来限制存储的长度

但是在整型中并不是用来限制长度，而是用来控制展示长度

直接存数据不会报错

更形象的表示：

以后涉及到整型字段，都无需自己定义长度，直接使用自带的即可，而针对其他类型的字段需要自己添加数字

字段类型之枚举与集合 枚举

多选一

存入数据的时候，针对gender只能填写提前定义好的数值

集合

多选多（也可以多选一）

字段类型之日期类型

date 年月日 datetime 年月日时分秒 time 时分秒 year 年

针对时间数据一般都是通过代码自动获取并添加，这里手动模拟一下：

字段类型约束条件 字段类型与约束条件的关系

约束条件是基于字段类型之上的额外限制

id int unsigned

字段类型int规定了id字段只能存整数，约束条件unsigned指的是整数基础之上还必须是正数

约束条件

无需正负号：unsigned

零填充：zerofill

非空：not null

插入数据的另外一种方式：打破字段顺序

不加not null 之前：

加了约束条件之后，不符合会报错（空字符串不会报错）

默认值：default

唯一值：unique

单列唯一：某个字段下对应的数据不能重复，是唯一的

多列唯一：多个字段下对应的数据组合到一起的结果不能重复，是唯一的

主键：primary key

单从约束层面上而言，相当于not null + unique（非空且唯一）

是InnoDB存储引擎规定的一张表有且必须要有一个主键，用于构建表

主键可以加快数据的查询速度（类似于书的目录）

如果创建表创建的时候没有设置主键也没有其他的键，那么InnoDB会采用一个隐藏的字段作为表的主键（隐藏就意味着而无法使用，即无法加快数据查询）

如果没有主键但是有非空且唯一的字段，那么会自动升级成主键（从上往下的第一个）

创建表应该有一个序号字段(id\pid\cid)并且应该将该字段设置成主键

也可以有联合主键（多个字段组合，本质还是一个主键）

自增：auto_increment

专门配合主键一起使用，户以后在添加数据的时候就不需要自己记忆主键值

以后在创建规范的表的时候，一般都会有一个主键字段的编写

id int primary key auto_increment