mysql修改字符编码、多种存储引擎以及字段类型的说明

字符编码与配置文件

查询本机数据库的版本信息

\s -- 查看数据库基本信息(用户、字符编码、版本)

window系统下5.6版本的mysql下，编码格式不统一，会造成乱码，我们需要统一修改成utf8，步骤如下：

  1. 拷贝my-default.ini 文件(windos下MySQL的默认配置文件)，到同级目录，并且重命名为my.ini
  2. 在my.ini中原来的内容删除，重新添加如下字符编码相关的配置
[mysqld]
    character-set-server=utf8
    collation-server=utf8_general_ci
[client]
    default-character-set=utf8
[mysql]
    default-character-set=utf8
  1. 如果配置文件涉及到了mysqld相关的配置修改，那么需要重启服务端才可以生效

我们也可以在配置文件中的mysql下提前写好用户名和密码，之后用此账号密码登录mysql

存储引擎

存储引擎的分类

我们可以简单的理解为存储引擎就是针对相同的数据采用不同的存取策略

show engines;	-- 查看所有的存储引擎

image-20220815155550757

在8.X版本的MySQL中有这么多存储引擎，需要掌握的存储引擎有如下：

存储引擎 说明 特点
MyISAM MySQL5.5及之前版本默认的存储引擎 存储数据的速度快、功能少、安全性低
InnoDB MySQL5.5之后版本默认的存储引擎 存取数据的速度没有MyISAM快，支持事务、行锁、外键等功能，安全性高
Memory 基于内存的存储引擎、存取数据极快、但是断电立刻丢失
BlackHole 黑洞、任何写进去的数据会立刻丢失、类似于垃圾站

MySQL中默认是大小写不敏感的(忽略大小写)

不同存储引擎之间底层文件的区别

  • InnoDB

    .frm  -- 表结构
.ibd  -- 表数据、表索引(加快数据查询)

  • MyISAM

    .frm  -- 表结构
.MYD  -- 表数据
.MYI  -- 表索引(加快数据查询)

  • Memory

    .frm  -- 表结构

  • BlackHole

    .frm  -- 表结构

在mysql中分别创建这四个存储引擎的表

-- 创建
create table t1(id int)engine=innodb;
create table t2(id int)engine=myisam;
create table t3(id int)engine=memory;
create table t4(id int)engine=blackhole;

-- 插入数据
insert into t1 values(1);
insert into t2 values(2);
insert into t3 values(3);
insert into t4 values(4);

创建表的完整语法

create table 表名(
	字段名1 字段类型(数字) 约束条件，
  字段名1 字段类型(数字) 约束条件，
  字段名1 字段类型(数字) 约束条件，
  ...
)engine=存储引擎;

当我们创建表时，应注意如下几点：

  • 字段名和字段类型是必须的(至少写一个)
  • 数字和约束条件是可选的(可有可无)
  • 约束条件可以写多个，空格隔开即可
  • 最后一个字段的结尾千万不能加逗号

字段类型之整型

类型 字节数 范围
tinyint 1b (-128~127) (0~256)
smallint 2byte (-32768~32767) (0~65535)
int 4byte (-2^31~ 2^31-1) (0~2^32-1)
bigint 8byte (-2^63~ 2^63-1) (0~2^64-1)

  1. 验证整型是否自带负号

    create table t5(id tinyint);
insert  into t5 values(-129),(256);
--  结果发现填写为两个边界值，数据失真，没有实际意义

    上诉的所有的整型类型默认都会带有符号

  2. 自定义移除负号

    unsigned是约束条件之一，意思是不需要符号

    create table t6(id tinyint unsigned);
insert into t6 values(-129),(256);  -- 结果报错:Out of range value for column 'id'无法插入数据

  3. 配置文件的修改

    插入的数据值超出了数据类型的范围，不应该让其插入并自动修改，没有意义，数据库应该直接报错(这个特性其实是有的，只不过被我们改了>>>:配置文件)

    1. 方式1：命令行修改

      set session sql_mode= 'strict_trans_tables'  -- 当前客户端操作界面有效(局部)
set global sql_mode = 'strict_trans_tables'  -- 服务端不重启永久有效(全局)

    2. 方式2: 配置文件永久修改

      [mysqld]
  sql_mode = 'strict_trans_tables'

字段类型之浮点型

float 、double、decimal三者都可以存储浮点型数据，但是各自的精确度不一致

使用方式

float(255,30)   -- 第一个参数表示总共多少位，第二个参数表示小数占多少位
double(255,30)
decimal(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(2.222222222222222222222222222222);
insert into t8 values(2.222222222222222222222222222222);
insert into t9 values(2.222222222222222222222222222222);
image-20220815170319430

由图可以看出，精度：float<double<decimal

一般情况下float足够使用了，如果想追求非常完美的精准度，可以使用字符串来代替

字段类型之字符类型

字符类型 特点 说明
char(4) 定长 最大只能存储四个字符、超过范围则直接报错，不超出范围则用空格填充至四个字符
varchar(4) 变长 最大只能存储四个字符，超过范围则直接报错，不超出则有几位存几位

验证两者的区别

create table t10(id int,name char(4));
create table t11(id int,name varchar(4));

insert into t10 values(1,'x');
insert into t11 values(1,'x');

  1. 结果验证，超出范围两者都会报错

  2. 验证定长和变长特性

    -- char_length()  统计字段数据的长度
结果如下图
    image-20220815171554214

  3. 注意

    默认情况下char在存储的时候针对没有满足固定位数的字符会自动填充空格，然后在读取的时候又会自动将填充的空格移除，如果想要取消该机制,需要输入如下：

    set global sql_mode='strict_trans_tables,pad_char_to_full_length'

char和varchar性能对比

  • char: 整存整取，速度快，但是浪费存储空间
  • varchar：节省存储空间，存取数据的速度慢于char

补充

char(4)  -- 因为每次存储都是固定4个字符
 a  aaaa   bbbb
varchar(4)  -- 每次存储都是可变长度，读取的话需要报头
1bytes+a1bytes+bbb1bytes+jaso

存储数据都需要操作报头(耗时)

两者的使用频率都很高，现在默认很多时候是varchar

数字的含义

数字大部分情况下都是用来限制字段的存储长度，但是整型除外。它不是用来限制存储的长度，而是展示的长度

create table t12(id int(3));
insert into t12 values(1111); -- 不会报错

create table t13(id int(3) zerofill);
insert into t13 values(1); -- 001

总结：以后涉及到整型字段的定义，类型后面不需要加括号写数字，除非有业务需求必须固定位数

eg:
	000000000001
	000022222222

字段类型之枚举与集合

枚举：多选一

eg:性别(男、女、其他)

create table t14(
	id int,
  name varchar(32),
  gender enum('male','female','others')
);

insert into t14 values(1,'jason','male');
image-20220815174826809

集合：多选多(包含多选一)

eg：爱好

create table t15(
	id int,
  name varchar(32),
  hobbies set('read','run','game','sing')
);

insert into t15 values(1,'jason','read,sing');
image-20220815174846457

字段类型之日期类型

字段类型 含义
data 年月日
datatime 年月日时分秒
time 时分秒
year 年份

示例

create table t17(
	id int primary key,
  name varchar(32) unique,
  birth date,
  reg_time datetime,
  work_time time,
  join_time year
);
insert into t17 values(1,'jason','2000-1-1','2020-1-1 12:20:33','11:22:22','2018');

image-20220815181942022

字段约束条件

约束 解释 举例
unsigned 无负号 id int unsigned
zerofill 零填充 id int zerofill
not null 不为空 name varchar(32) not null
default 默认值 name varchar(32) default 'Jason'
unique 唯一值 id int unique(单列唯一)
primary key 主键约束 id int primary key

unique补充:

-- 唯一约束unique重要的用法
create table t16(
	id int primary key,
  host varchar(32),
  port int,
  unique(host,port)
);

insert into t16 values(1,'127.0.0.1',8080);
insert into t16 values(2,'127.0.0.1',8000); 
insert into t16 values(3,'127.0.0.2',8000); 
insert into t16 values(3,'127.0.0.2',8000);   --  报错

由此可以得出结论：联合唯一约束下，括号内字段，不全部相同，就不为唯一。

posted @ 2022-08-15 18:07  荀飞  阅读(113)  评论(0)  编辑  收藏  举报