一、数据类型
1. int(11) vs int(21)的区别？存储空间，还是存储范围？
本质上没有任何区别，不管是存储空间，还是存储范围，都是一样的。
区别就是，比如存数据1，int(11)前面会补10个0，int(21)前面会补20个0
2. 数据类型：char varchar text 的区别?
1). char与varchar存储的单位都是字符，text存储单位是字节，总大小为65535字节，约为64KB
2). char存储定长，容易造成空间的浪费, char数据类型最大为255字符
3). varchar存储变长，节省存储空间，可以存储超过255字符
4). text在数据内部大多存储格式为溢出页，效率不如char
3.字符与字节
1). 字节的单位是byte，衡量数据量的多少
2). 字符就是指在计算机使用的文字和符号而已
3). 它们之间的对应关系跟采用的编码技术有关，在utf8中，1个汉字对应3个字节，1个字母对应1个字节
4.时间类型：DATE、TIME、TIMESTAMP、DATETIME的区别
| 时间类型 | 存储空间 | 存储精度 | 存储范围 |
|---|---|---|---|
| DATE | 3字节 | 年月日 | - |
| TIME | 3字节 | 时分秒 | - |
| TIMESTAMP | 4字节 | 年月日时分秒 | 1970-2038年 |
| DATETIME | 8字节 | 年月日时分秒 | 1000-9999年 |
注：TIMESTAMP会根据系统时区进行转换，DATETIME不会
二、数据对象
1.MySQL常见的数据对象有
- database、schema、table、index
- view、trigger、function、procedure
2.唯一约束 与 外键约束
- 唯一约束(unique key)：是一种特殊的索引，对table的某个字段或者多个字段设置唯一约束，保证在这个表里对应的数据必须唯一。比如用户ID、手机号、身份证号等，主键(primary key)也是一种唯一约束。
- 外键约束(Foreign key): 外键是指两个table的数据通过某种条件关联起来。
3.使用外键约束的注意事项
- 必须是InnoDB表，Myisam和其他存储引擎不支持外键
- 相互约束的字段类型必须一样
- 主表的约束字段要求有索引
- 约束名称必须要唯一，即便不在一张表上
4.数据对象view的作用
- 简化数据库的访问，将多个查询语句结构化为一个虚拟结构
- 隐藏数据库后端表结构，提高数据库的安全性
- 视图也是一种权限管理，只对用户提供部分数据
5.触发器Trigger的作用：
可以在数据写入表之前或者之后触发一些其它操作
三、权限管理
1.如何新建一个用户并授权?
下面的Grant语句会判断是否存在该用户，如果不存在则新建。下面是为tom这个用户授权了select语句,with grant option就是允许tom把select这个权利授予给其它人
GRANT SELECT ON *.* TO 'tom'@'localhost' IDENTIFIED BY 'password' WITH GRANT OPTION;
2.其它语句
show grants; //查看当前用户的权限
show grants for tom@'localhost'; //查看其它用户的权限
revoke select from *.* on tom@'localhost'; //回收不需要的权限
grant insert on *.* to tom@'localhost'; //重新赋权
3.MySQL权限信息存储结构
- 权限信息存在mysql数据库user表中
- MySQL账号对应的密码也加密存储在数据库user表中
- 每一种权限类型在元数据里都是枚举类型，表明是否有该权限
4.与权限相关的表有:user、db、tables_priv、columns_priv，权限验证流程如下图：
权限验证流程.png
四、字符集(charset) 和字符序(collation)
1.数据库中的字符集有两层含义:一是各种文字的符号和集合；二是字符的编码方式
2.MySQL字符集编码分类:
ASCII(单字节编码，仅能表示128个字符)
GBK(双字节编码)
UTF-8(可变长度字符编码，使用1-4个字节为每个字符编码)
latin1(默认字符集)
3.字符序的含义：
字符的排序与比较规则，每个字符集都有对应的多套字符序。不同的字符序决定了字符串在比较排序中的精度和性能不同
4.mysql 的字符序遵从命名惯例：
_ci 表示大小写不敏感
_cs 大小写敏感
_bin 用编码值进行比较
5.charset和collation的设置级别：
服务器级>>数据库级>>表级>>列级
6.数据存储字符集使用规则
- 使用列集的character set设定值
- 若列级字符集不存在，则使用对应表级的default character set 设定值
- 若表级字符集不存在，则使用数据库级的default character set 设定值
- 若数据库级字符集不存在，则使用服务器级character_set_server设定值
7.客户端连接与字符集
- character_set_client:客户端来源数据使用的字符集
- character_set_connection:连接层字符集
- character_set_results:查询结果字符集
推荐把它们三个统一设置成一致的字符集 mysql> set names utf8;
配置文件设置：[mysql] default-character-set = utf8
8.常见乱码原因
- 数据存储字符集不能正确编码（不支持）client发来的数据:client(utf8) -> storage(latin1)
-
程序连接使用的字符集与通知mysql的character_set_client等不一致或不兼容
客户端连接与字符集.png
导入数据时，不用set names，而是用set character_set_database 与实际文件的编码一样
使用建议
- 创建数据库／表时显示的指定字符集，不使用默认
- 连接字符集与数据存储字符集设置一致，推荐使用utf8
- 驱动程序连接时显示指定字符集(set names xxx)
9.相关命令
//global可选，查看字符集
show [global] variables like 'character%';
//global可选，查看字符序
show [global] variables like 'collation%';;
//修改字符集
set global character_set_server=utf8;
alter table xxx convert to character set xxx;
//设置数据库级的字符集和字符序，主要影响load data等语句的默认字符集
create database db_name character set latin1 collate latin1_swdish_ci
// 设置表级的字符集
create table tb_name default character=utf8 default collate=utf8_bin;
网友评论