MySQL配置文件 字段类型及约束条件

概要

• 字符编码与配置文件介绍

• 存储引擎

• MySQL字段类型

• MySQL字段约束条件

内容

1、字符编码与配置文件

# 查看MySQL默认字符编码: \s
如果是5.X系列 显示的编码有很多种
比如有:latin1
       gbk
如果是8.X系列 显示的统一是utf8mb4
其中utf8mb4是utf8的优化版本 支持存储表情

# 统一字符编码
	5.X默认编码有多种 可能会导致乱码的情况 所以应该统一编码
    my-default.ini配置文件
步骤1:拷贝一份该配置文件并修改名称为my.ini
步骤2:清空my.ini文件内的内容
步骤3:添加固定的配置信息即可
    [mysqld]    # 这个主要作用于服务端
    character-set-server=utf8
    collation-server=utf8_general_ci
    [client]    # 这个主要作用于客户端
    default-character-set=utf8
    [mysql]    # 作用于服务端(这个一般是MySQL数据库自带)
    default-character-set=utf8
步骤4:保存并重启服务端即可生效
cmd-net stop mysql-net start mysql-\s

2、存储引擎

# 什么是存储引擎
 	存储引擎可以理解为处理数据的不同方式
    比如:
        有一个a.txt的文件 ,每个人存储形式可能会不一样
        有的人会放在密码箱中 有的人会转成pdf的形式 有的人会做好多个备份 还有的人会制作封面美化等

# 查看存储引擎: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;
sql_mode补充
	1.如何查看
		show variables like '%mode%'
	2.单次修改(仅限于当前登录)
		set session sql_mode = '...'
	3.临时修改(仅限于当前服务)
		set global sql_mode = '...'
	4.永久修改
		在配置文件中的[mysqld]下面添加

### 3、创建表的完整语法

~~~python
create table 表名(
	字段名1 字段类型(数字) 约束条件,
    字段名2  字段类型(数字) 约束条件,
    字段名3  字段类型(数字) 约束条件);
需要注意:
    1.字段名和字段类型是必须要有的
    2.数字和约束条件是可选的 不一定要有
    3.约束条件可以写多个 空格隔开即可(后面详细讲解)  比如: 字段名1 字段类型(数字) 约束条件1 约束条件2 约束条件3
    4.最后一行字段结尾不能加"逗号"
    极其容易被忽略    

4、字段类型之整型

# 整型
	tinyint    1bytes
    smallint    2bytes
    int         4bytes
    bigint      8bytes
    上述整型的区别在于从上往下能够存储的数字范围越来越大
    注意事项
    1.需要考虑正负数的问题 如果需要存储负数 则需要占据一个比特位
    2.注意手机号如果使用整型来存储 需要使用bigint才可以
    
工作小技巧:有时候看似需要使用数字类型存储的数据其实可能使用的是字符串
    因为字符串可以解决不同语言对数字不精确的缺陷！！！
    create table t5(id tinyint);
    insert into t5 values(-129),(256);
    # 如果是在5.6版本不会报错 会自动处理成最大范围(没有意义)
    步骤1:set global sql_mode = 'STRICT_TRANS_TABLES';
    步骤2:退出客户端 重新登录即可
  # 如果是在5.7及以上版本 则会直接报销(更加合理)
验证(结论)发现所有的整型都默认带有正负号 如何修改不带正负号(结束条件)
	create table t6(id tinyint unsigned);

5、字段类型之浮点型

# 浮点型
float
double
decimal
上述浮点型从上往下精确度越来越高
float(255,30)  总共255位 小数位占30位
double(255,30) 总共255位 小数位占30位
decimal(65,30) 总共65位  小数位占30位
重点研究精确度问题
create table t7(id float(255,30));
create table t8(id double(255,30));
create table t9(id decimal(65,30));
insert into t7 values(1.111111111111);
insert into t8 values(1.111111111111);
insert into t9 values(1.111111111111);
decimal>double>float

虽然三者精确度有差距 但是具体用哪个应该结合实际情况
	比如正常业务  使用float足够
    如果是高精尖业务  可以使用decimal    

6、字段类型之字符类型

# 字符类型
char与varchar
上述两个字符类型的区别在于一个是定长一个是变长
eg:
    char(4)
    定长 最大只能存储四个字符 超出则报错 不够则空格填充至四个
    varchar(4)
    变长 最大只能存储四个字符 超出则报错 不够则有几个存几个
    验证定长与变长特性
    create table t10(name char(4));
    create table t11(name varchar(4));
    insert into t10 values('jason');
    insert into t11 values('jason');
    # 如果是5.6版本并且没有修改严格模式 则会自动截取四个字符(不合理)
    # 临时修改
    步骤1:set global sql_mode = 'STRICT_TRANS_TABLES';
    步骤2:退出客户端 重新登录即可
    # 永久修改
    修改my.int配置文件
    sql_mode = 'STRINT_TRANS_TABLES,ONLY_FULL_GROUP_BY'
    重启服务端之后永久生效
    
课外扩展研究
	char_length()  获取字段数据的长度
    该方法无法直接获取到定长的真实长度
    因为MySQL在存数据的时候会自动填充空格在取数据的数据又会自动移除空格
    
能不能让MySQL在取数据的时候不自动移除空格
	单次修改
    	set session sql_mode = 'pad_char_to_full_length'
        
# 工作中使用char还是varchar
	char
    整存整取 速度快
    会造成一定的存储空间浪费
    
    比如:
        jasonkevintony tom jerry
        
  varchar
    节省存储空间
    存储数据的速度没有char快(取数据不知道数据的精确长度)
 比如:
    varchar在存数据的时候会生成一个1bytes的报头 记录数据长度 
    varchar在取数据的时候先会读取1bytes的报头 从中获取真实数据长度
    1bytesjason1bytes+kevin1bytes+tony
    
# 以前几乎使用的都是char 现在varchar使用频率也越来越高

两者都有使用场景
比如:
    针对统一中国人的姓名 应该采取那个类型>>>varchar
    规模较小 数据量相对固定的字典>>>>char
很多时候字段类型的选取和命名都会在邮件中标明(组长、架构师、产品)    

7、数字的含义

字段类型括号内的数字大部分情况下是用来限制存储的长度
但是在整型中并不是用来限制长度 而是用来控制展示长度

create table t12(id int(3));
insert into t12 values(1111);  # 不会出现报错的情况

create table t13(id int(3) zerofill);  # 位数不够用0填充
insert into t13 values(1);  # 001
insert into t13 values(1111); # 有几位就展示几位

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

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

# 枚举
	多选一
  create table t14(
  	id int,
    name varchar(32),
    gender enum('male','female','others')
  );
  '''插入数据的时候 针对gender只能填写提前定义好的数值'''
  
# 集合
	多选多(也可以多选一)
  create table t15(
  	id int,
    name varchar(32),
    hobby set('篮球','足球','双色球','排球','水球','肉球')
  );

9、字段类型之日期类型

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

create table t16(
id int,
name varchar(32),
reg_time datetime,
    birth date,
    study_time time,
    join_time year);
# 针对时间数据一般都是通过代码自动获取并添加 我们这里手动模拟
insert into t16 values(1,'jason','2000-11-11','2002-01-21','11:11:11','2015');

10、约束条件

# 字段类型与约束条件的关系
约束条件是基于字段类型之上的额外限制
eg:id int unsigned
    字段类型int规定了id字段只能存整数
    约束条件unsigned指的是整数基础之上还必须是正数
    
# 1.无需正负号
unsigned
# 2.零填充
zerofill
# 3.非空
not null
create table t17(id int,name varchar(32));
插入数据的另一种方式 打破字段顺序
insert into t17(name,id) values('jason',1);
insert into t17(id) values(2);

create table t18(id int,name varchar(32) not null);
insert into t18(id) values(2);  # 报错
insert into t18(id,name) values(2,null); # 报错
insert into t18(id,name) values(2,'');   # 不报错
# 4.默认值
default
create table t19(id int,name varchar(32) default 'jason');
insert into t19(id) values(1);
insert into t19(id,name) values(2,'kevin');
# 5.唯一值
unique
"""单列唯一:某个字段下对应的数据不能重复 是唯一的"""
create table t20(
id int,
name varchar(32) unique);
"""多列唯一:多个字段下对应的数据组合到一起的结果不能重复 是唯一的"""
create table t21(
id int,
host varchar(32),
port int,
unique(host,port));
# 6.主键
primary key
1.单从约束层面上而言 相当于not null + unique(非空唯一)
	create table t22(id int primary key);
2.是InnoDB存储引擎规定的一张表有而且必须要有一个主键 用于构建表
	主要可以加快数据的查询速度(类似于书的目录)
    如果创建表创建的时候没有设置主键也没有其他的键那么InnoDB会采用一个隐藏的字段作为表的主键(隐藏就意味着而无法使用 即无法加快数据查询)
    如果没有主键但是有非空且唯一的字段 那么会自动升级成主键(从上往下的第一个)
    create table t23(
    tid int,
    pid int not null unique,
    cid int not null unique);
    
结论:创建表应该有一个序号字段(id\pid\cid)并且应该将该字段设置成主键
create table t24(
id int primary key,
name varchar(32));
# 也可以有联合主键(多个字段组合 本质还是一个主键) 了解即可
create table t24(
id int,
name varchar(32),
pwd int,
primary key(id,pwd));
# 7.自增
auto_increment
专门配合主键一起使用 用户以后在添加数据的时候就不需要自己记忆主键值
create table t25(id int primary key auto_increment,
name varchar(32));
总结
以后在创建规范的表的时候 一般都会有一个主键字段的编写如下
id int primary key auto_increment