Mysql数据类型、架构体系

Mysql数据类型

Int类型

1）有无符号
在项目中使用 BIGINT，而且是有符号的。

create table test_unsigned
(a int unsigned, b int unsigned); 
insert into test_unsigned values(1, 2);
select b - a from test_unsigned;
select a - b from test_unsigned; --运行出错

2）Int（N）是什么？

int（N）中的N是显示宽度，不表示存储的字的长度的上限
Zerofill表示当存储的数字长度 < N时，用数字0填充坐标，直至补满长度N
当存储数字的长度都超多N时，按照实际存储的数字显示
3）自动增长
这列语法有错误吗？
create table test_auto_increment(a int auto_increment);

create table test_auto_increment(a int auto_increment primary key);

insert into test_auto_increment values(NULL);


insert into test_auto_increment values(0);

insert into test_auto_increment values(-1);


insert into test_auto_increment values(null),(100),(null),(10),(null);

字符类型

排序类型

select ‘a’ = ‘A’;

create table test_ci (a varchar(10), key(a));
insert into test_ci values(‘a’);
insert into test_ci values(‘A’);

select * from test_ci where a = ‘a’; --结果是什么？

时间类型

Datetime与Timestamp的 区别：
create table test_time(a timestamp, b datetime);
insert into test_time values (now(), now());

select * from test_time;

select @@time_zone;
set time_zone=’+00:00’;
select * from test_time;

JSON类型

JSON入门

#新建表 
create table json_user ( 
uid int auto_increment, 
data json, 
primary key(uid) ); 
#插入数据 
insert into json_user values ( 
null, 
'{ "name":"sa", "age":18, "address":"sa add" }'
 ); 
insert into json_user values ( 
null, 
'{"name":"sb", "age":28, "mail":"sb@163.com" }'
);

JSON函数

1）Json_extract抽取函数
select json_extract(’[10, 20, [30, 40]]’, ‘$[0]’);

select json_extract(’[10, 20, [30, 40]]’, ‘$[1]’);

select json_extract(’[10, 20, [30, 40]]’, ‘$[2]’);

Select json_extract(data, ‘$.nam{e}^{\prime }),jso{n}_{e}xtract(data{,}^{\prime }$.address’) from json_user;

2）Json_object将对象转为json
select json_object(“name”, “sc”, “email”, “sc.com”, “age”,35);

insert into json_user values (
null, json_object(“name”, “sc”, “email”, “sc.com”, “age”,35)
);

3）Json_insert插入函数
JSON_INSERT(json_doc, path, val[, path, val] …)
set @json = ‘{ “a”: 1, “b”: [2, 3]}’;
select json_insert(@json, ‘$.a^{\prime },10{,}^{\prime }$.c’, ‘[true,false]’);

UPDATE json_user SET DATA = json_insert ( DATA, "$.addres{s}_2","tyy")WHEREuid=1;4）Jso{n}_{m}erge合并数据并返回SELECTjso{n}_{m}erge{(}^{\prime }{"name":"tyy"}^{\prime }{,}^{\prime }{"id":47}^{\prime });SELECTjso{n}_{m}erge(jso{n}_{e}xtract(DATA{,}^{\prime }$.address’),
json_extract ( DATA, ‘$.address_2’)
)
FROM json_user WHERE uid = 1;

JSON索引

待定

Mysql架构

体系

1）连接层

当Mysql启动（Mysql服务器就是一个进程），等待客户端连接，每一个被客户端连接请求，服务器都会新建一个线程处理（如果是线程池的话，则是分配一个空的线程），每个线程独立，拥有各自的内存处理空间。

//显示服务器最大线程连接数
SHOW VARIABLES LIKE '%max_connections%';

连接到服务器，服务器需要对其进行验证，也就是用户名、IP、密码验证，一旦连接成功，还要验证是否具有执行某个特定查询的权限（例如：是否允许客户端对某个数据库的某个表的操作）
2）SQL处理层

这一层主要功能有：SQL语句的解析、优化、缓存的查询；Mysql内置函数的实现，跨存储引擎功能（所谓跨存储引擎就是说每个引擎都需要提供的功能（引擎需对外提供接口）），例如：存储过程、触发器、视图等；
1、如果是查询语句（select语句），首先会查询缓存是否已有相应的结果，无则进行下一步（如果不是查询语句，同样会调到下一步）
2、解析查询，创建一个内部数据结构（解析树），这个解析树主要用来SQL语句的语义与语法解析；
3、优化，优化SQL语句，例如重写查询，决定表的读取顺序，以及选择需要的索引等。这一阶段用户是可以查询的，查询服务器优化器是如何进行优化的，便于用户重构查询和修改相关配置，达到最优化。这一阶段还涉及到存储引擎，优化器会询问存储引擎，比如某个操作的开销信息，是否对特定索引有查询优化等。

21）缓存

SHOW VARIABLES LIKE '%query_cache_type%';
SHOW VARIABLES LIKE '%query_cache_size%';
SET GLOBAL query_cache_type = 1; --会报错 

uery_cache_type 只能配置在 my.cnf 文件中，这大大限制了 qc 的作用
在生产环境建议不开启，除非经常有 sql 完全一模一样的查询 。
QC 严格要求 2 次 SQL 请求要完全一样，包括 SQL 语句，连接的数据库、协议版本、字符 集等因素都会影响
22）解析查询

23）优化

通过上面的sql大概能看出，一个sql并不一定会去查询物理数据集，sql解析器会通过优化器来优化程序员写的sql，
EXPLAIN SELECT * FROM account t WHERE t.id IN ( SELECT t2.id FROM account t2 ) ;
SHOW WARNINGS;

逻辑架构

在 mysql 中其实还有个 schema 的概念，这概念没什么太多作用，只是为了兼容其他数据库， 所以也提出了这个。 在 mysql 中 database 和 schema 是等价的。

create database demo;
show databases;
drop schema demo;
show databases;

物理存储结构

1）数据库的数据目录
Mysql安装的时候都要指定datadir，其查看方式为：
Show variables like ‘datadir’，其规定所有建立的数据库存放位置

2）数据库
创建一个数据库后，会在上面的datadir目录新建一个子文件夹

3）表文件
用户建立的表都会在上面的目录中，他和具体的存储引擎相关，但是有个共同的就是有个frm文件，他存放的是表的数据格式。