一、字符集编码/权限处理

一、字符集

1. 默认设置

• MYSQL 5.7默认字符集为latin， MYSQL 8.0默认为utf8mb4

• utf8(utf8mb3): 阉割过的utf8字符集，使用1-3个字节表示字符

• utfmb4: 正宗的utf8字符集，使用1-4个字节表示字符(比如emoji表情)

-- 1. 查看
SHOW VARIABLES LIKE 'character%';

character_set_client,utf8mb4         # 客户端发送请求时候用的数据编码
character_set_connection,utf8mb4
character_set_database,utf8mb4       # 对应的database
character_set_filesystem,binary
character_set_results,utf8mb3
character_set_server,utf8mb4         # 对应的server
character_set_system,utf8mb3
character_sets_dir,/usr/share/mysql-8.0/charsets/

2. 依赖

# mysql包含四个级别的字符集和比较规则
# 不指定就会默认高等级的
# 也可显示的指定,一般不会显示的指定
- 服务器级别
- 数据库级别
- 表级别
- 列级别

3. 比较规则

• 每个字符集具有依赖的默认比较规则

Charset   DESC              DEFAULT collation     Max length
utf8      UTF-8 Unicode     utf8_general_ci       3
utf8mb4   UTF-8 Unicode     utf8mb4_0900_ai_ci     4

SHOW CHARACTER SET;
SHOW COLLATION;

4. Linux大小写规范

• 库，表，表别名，变量名(结构名称)区分大小写
• 关键字，函数名称在sql中不区分大小写
• 列名，列别名不区分大小写

二、目录结构

• 基于Docker

1. 目录结构

# 该目录下保存对应的db, 比如自定义的库，系统库
/var/lib/mysql/  
SHOW VARIABLES LIKE '%datadir%'; # 环境变量，对应着这个保存的目录

# 指定的数据库: 
# 表数据： employee.ibd  erick.ibd  erick_shu.ibd	nike_shoe.ibd
/var/lib/mysql/shuzhan_db

# 存放mysql的对应的一些指令
/usr/bin

# mysql 配置文件
/etc/mysql/my.cnf

2. 系统库

1. mysql库
- 目录结构：/var/lib/mysql/mysql
-  MYSQL系统自带的核心数据库
- 存储了MYSQL的用户账户和权限信息，一些存储过程，
- 事件的自定义信息，一些运行过程中产生的日志信息，一些帮助信息以及时区信息

2. information_schema库
- MYSQL系统自带的数据库
- 保存mysql服务器 维护的所有其他数据库的信息
- 有哪些表，视图，触发器，列，索引
- 并不是真实用户数据，而是一些描述信息，也称为 元数据

3. performance_schema库
- MYSQL系统自带的数据库
- 保存服务器运行过程中的一些状态信息
- 用来监控MYSQL服务的各类性能指标： 最近执行了哪些语句，执行阶段花费时间，内存使用情况等

4 sys库
- MYSQL系统自带的数据库
- 通过视图的形式把information_schema和performance_schema结合，监控mysql的技术性能

3. 文件结构

3.1 表空间-InnoDB

• 独立表空间，表数据会合并到 .idb文件中，方便管理
• /var/lib/mysql/erick_db （employee.ibd student.ibd）
• Oracle官方将 .frm 文件(表结构)的信息及更多信息移动到序列化字典信息(Serialized Directory Information, SDI), SDI被写在ibd文件内部

# .ibd解析文件， Oracle提供了一个应用程序叫 ibd2dsi
ibd2sdi --dump-file=employee.txt employee.ibd      #文件解析为txt

3.2 表空间-MyISAM

people.MYD	     # 存放数据
people.MYI       # 存放索引
people_416.sdi   # 表结构空间

三、权限管理

1. 用户管理

• 一个Server端可以包含多个用户，每个用户之间互不影响
• root用户权限最大，可以创建不同的用户
• 账户信息保存在mysql的 mysql库----user表中，密码采用掩码的方式保存

# 1. 默认用户: SELECT * FROM mysql.user;
%             root
localhost     mysql.infoschema
localhost     mysql.session
localhost     mysql.sys
localhost     root

# 2. 向user表中添加新的用户名及账户， 密码自动会转换为掩码
# 表主键： 会采用 host+user两个字段作为主键
CREATE USER 'shuzhan'@'%' IDENTIFIED BY '888888';
CREATE USER 'lucy'@'localhost' IDENTIFIED BY '123456';

#3. 删除用户
DROP USER 'lucy'@'localhost';

Host字段:                    访问权限
                            %：代表远程可以访问，localhost只能本地在本地访问数据库
User字段：                   用户名   
authentication_string字段：  密码

2.修改密码

• 不能直接修改authentication_string, 否则保存的密码就是明文
• 通过下面方式修改的，密码会加密保存，才可以正确访问
• 重启Server端，密码生效

# 1. 修改当前登陆用户的密码
ALTER USER USER() IDENTIFIED BY '111111';
SET PASSWORD = 'abcabc';

# 2. 修改其他用户的密码
ALTER USER  'shuzhan'@'%' IDENTIFIED BY '111111';
SET PASSWORD FOR 'shuzhan'@'%'='hello123';

3. 用户授权

• root 用户下面的表： information_schema、mysql 、performance_schema、sys
• create新增账户的表： information_schema
• 因此要将root权限的的部分表授权给新用户

- 最小权限管理
- 限制指定ip或者主机登陆
- 为每个用户设置满足密码复杂度的设置
- 定期清理不需要的用户和权限

# 查看权限
SHOW PRIVILEGES ;

# 1.  以root身份登录数据库
mysql -u root -p

# 2. 将root权限下所有数据库下所有表所有权限  都授权给shuzhan用户
GRANT ALL PRIVILEGES on *.* to 'shuzhan'@'%';

# 3. 将某个库的所有表的所有权限授权给shuzhan用户，gulimall_order.* 
GRANT ALL PRIVILEGES gulimall_order.* to 'shuzhan'@'%'；

# 4. 某个数据库的表的部分权限
# 多次赋权，是去增加权限，不是覆盖
GRANT select,delete,update,create,drop on gulimall_order.* 'shuzhan'@'%';

# 5. 删除权限
REVOKE select ON *.* FROM 'shuzhan'@'%' ;

# 6 底层实现： 通过一系列表来实现
mysql.user   mysql.db  mysql.tables_priv