MySql知识总结（持续更新。。。）

「本文已参与「新人创作礼」活动，一起开启掘金创作之路。」

发现自己已经工作三年多了，在自己开始敲代码的时候就开始记录自己的所学所得，所以现在把自己记录的知识整理之后发布到这里。。。

安装

centos为例：

1.wget -i -c http://dev.mysql.com/get/mysql57-community-release-el7-10.noarch.rpm
2.yum -y install mysql57-community-release-el7-10.noarch.rpm
3.yum -y install mysql-community-server
4.systemctl start  mysqld.service
5.grep "password" /var/log/mysqld.log
6.mysql -uroot -p
7.set global validate_password_policy=0;
  set global validate_password_length=1;
8.ALTER USER 'root'@'localhost' IDENTIFIED BY 'new password';
9.
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'admin123' WITH GRANT OPTION; 
flush privileges;
10.yum -y remove mysql57-community-release-el7-10.noarch

数据库基本设计规范

• MySQL5.6以上的的存储引擎必须使用Innodb存储引擎，Innodb支持事务，行级锁，更好的恢复性，高并发下性能更好

• 数据库和表的字符集统一使用UTF8（一个字符4个字节 utf8mb4），可以避免由于字符集转换产生的乱码

• 表和字段都要加注释

• 禁止在表中建立预留字段

数据库字段设计规范

• 优先选择符合存储需要的最小的数据类型(过大长度会消耗内存)，varchar(N) N代表是字符数 不是字节数，如果使用UTF8存储汉字 varchar(255) = 765字节
• 对于非负性的数据，优先使用无符号的整型来存储
• 避免使用TEXT,BLOB数据类型，将TEXT,BLOB分离到的单独的表，TEXT,BLOB类型只能使用前缀索引
• 避免使用ENUM数据类型（枚举），修改ENUM值需要使用ALTER语句，ENUM类型的orderby操作效率低 需要额外操作，禁止使用数值作为ENUM的值
• 尽可能把所有列定义为NOT NULL
• 财务相关的数据,必须使用decimal类型，计算时不会丢失精度

索引类型

• 普通索引：最基本的索引 没有任何约束限制
• 唯一索引：与普通索引类似 但是具有唯一性约束
• 主键索引：特殊的唯一索引 不允许有空值
• 组合索引：将多个列组合在一起创建索引 可以覆盖多个列
• 外键索引：只有Innodb的表才可以使用 能够保证数据的完整性 一致性 和实现级联操作
• 全文索引：只能用于MyISAM 只能对英文进行全文检索
• 一个表可以有多个唯一索引 但是只能有一个主键索引
• 主键索引一定是唯一索引 唯一索引不是主键索引

索引设计规范

• 单张表的索引不超过五个
• 每个Innodb表必须要有一个主键（建议使用自增ID）

常见索引列的建议

• 常出现在select update delete的语句中的where中的列
• 包含在groupby orderby distinct的字段
• 多表join的字段

如何选择索引列的顺序

• 区分度最高的列放在联合索引的最左侧
• 尽量把字段长度小的列放在联合索引的最左侧
• 使用最频繁的列放在联合索引的最左侧

注:联合索引本质：

当创建(a,b,c)联合索引时，相当于创建了(a)单列索引，(a,b)联合索引以及(a,b,c)联合索引 想要索引生效的话,只能使用 a和a,b和a,b,c三种组合；当然，我们上面测试过，a,c组合也可以，但实际上只用到了a的索引，c并没有用到！

远程连接mysql

服务器上进入MySQL
mysql -uroot -p

然后输入密码
use mysql
update user set host = '%' where host = '127.0.0.1';
刷新权限：flush privileges;
然后在服务器的控制中心查看有没有3306的mysql端口 如果没有就添加
然后本地navicat就进行连接 输入浏览器访问的那个ip root 密码 就能进行远程连接了

查看所有数据库

show databases;

查看库里的所有表

show tables;

显示表结构

desc 表名;

show columns from books;

explain 表名;

删除表

drop table 表名;

清空表

delete from 表名;

删除数据库

drop database 库名;

更新权限

flush privileges;

外网请求

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '密码' WITH GRANT OPTION; 
 
flush privileges;

修改密码(5.7版本)

UPDATE user SET authentication_string = PASSWORD('L6o&D@vW@JUkRu$2') WHERE user = 'heQunChat' AND host = 'localhost';

复制表

create table 新的表名 as select * from 来源表 where 1=1

复制表结构

create table 新的表名 as select * from 来源表 where 1<>1

查看某个库下有哪些表

这一步就少用了一次 use 库

show tables from 库;

查看某个库下的某个表的 创建语句

show create table 库.表;

修改单个表名

alter table 表/库.表 rename to 新名

修改多个表名(修改单个也可以)

rename table 旧1 to 新1 ,旧2 to 新2；

修改字段属性

alter table zzs_learn modify age int(4) defalut '1';

alter table zzs_learn change 旧名 新名 属性;

出现重复值无法插入 进行忽略

insert ignore into 表 values();

如果存在就更新

insert into 表 values() on duplicate key update 字段 = 值;

InnoDB和MyISAM存储引擎的区别

1. InnoDB支持事务，MyISAM不支持，也就是MyISAM不可以回滚还原
2. MyISAM适合查询以及插入为主的应用，InnoDB适合频繁修改以及涉及到安全性较高的应用
3. InnoDB支持外键，MyISAM不支持
4. MyISAM是默认引擎，InnoDB需要指定
5. InnoDB不支持FULLTEXT类型的索引
6. InnoDB中不保存表的行数，如select count() from table时，InnoDB需要扫描一遍整个表来计算有多少行，但是MyISAM只要简单的读出保存好的行数即可。注意的是，当count()语句包含where条件时MyISAM也需要扫描整个表
7. 对于自增长的字段，InnoDB中必须包含只有该字段的索引，但是在MyISAM表中可以和其他字段一起建立联合索引
8. 清空整个表时，InnoDB是一行一行的删除，效率非常慢。MyISAM则会重建表
9. InnoDB支持行锁（某些情况下还是锁整表，如 update table set a=1 where user like '%lee%'
10. 对一个表格频繁使用 select和insert时候 应该使用MyIsam 因为myISAM 比 InnoDB执行以上两个命令的时候更快 如果用到的是全文搜索 也应该使用myISAM 当事务非常重要 或者 经常在insert和select交错执行的情况 应该使用 InnoDB

sql查询的安全方案

• 使用预处理语句防sql注入
• 写入数据库的数据要进行特殊字符的转义
• 查询错误信息不要返回给用户，将错误记录到日志
• 定期做数据备份
• 不给查询用户root权限 合理分配权限
• 关闭远程访问数据库权限
• 修改root口令 使用复杂的

脏读

读取了未提交的数据 但是这些数据是无效的 会回滚 是读到了别的事务回滚前的脏数据。 比如事务B执行过程中修改了数据X，在未提交前， 事务A读取了X，而事务B却回滚了，这样事务A就形成了脏读。

不可重复读

读取了提交之前的数据 提交之后发现数据不匹配 事务A首先读取了一条数据，然后执行逻辑的时候， 事务B将这条数据改变了，然后事务A再次读取的时候，发现数据不匹配了，就是所谓的不可重复读了。也就是说，当前事务先进行了一次数据读取， 然后再次读取到的数据是别的事务修改成功的数据， 导致两次读取到的数据不匹配，也就照应了不可重复读的语义。

幻读

根据条件查询的条数之后 其他不符合条件的数据变成符合之前的条件 导致原先查询的数据不正确 事务A首先根据条件索引得到N条数据， 然后事务B改变了这N条数据之外的M条或者增添了M条符合事务A搜索条件的数据， 导致事务A再次搜索发现有N+M条数据了，就产生了幻读。

mysql查询优化

1. 查找分析查询速度慢的原因 a. 查看慢查询日志 可以使用pt-query-digest工具 b. 使用show profile 命令：set profiling=1 show profiles 所有执行的语句会检测消耗的时间 存到临时表 查询某个语句消耗时间内 具体的时间分布 show profile for query 2(临时表的id) c. show status 会返回一些计数器 show global status 查看服务器级别的所有计数 d. show processlist 观察是否有大量线程处于不正常的状态和特征 e. explain或desc 分析单条sql语句
2. 优化查询过程中的数据访问 a.避免使用如下sql 查询不需要的记录 使用limit解决 多表关联返回全部列 指定a.id,a.name,b.id ,select * 会让优化器无法完成索引覆盖扫描的优化 b.重复查询相同的数据，可以缓存数据，下次直接读取缓存 c.使用explain来分析 如果发现查询需要扫描大量的数据但只返回少数的行，可通过如下技巧：使用索引覆盖扫描，把所有用的列都放到索引中，这样存储引擎不需要回表获取对应行就可以返回结果
3. 优化长难的查询语句 使用尽可能少的查询最好的，将一个大的查询分解成多个小的查询是很有必要的
4. 优化特定类型的语句 a. 优化count()查询 count(*)会忽略所有的列，直接统计所有列数。不要使用count(列名) b. 确定on或者using子句的列上有索引 要命中索引 c. 确定group by和orderby中只有一个表中的列 这样mysql才有可能使用索引 d. 优化groupby和dinstinct 使用索引进行优化是最有效的 e. 关联查询中 使用标识列进行分组的效率会更高 f. 优化union查询 union all的效率高于union

悲观锁

每次拿数据都认为别人会修改 所以拿数据的时候上锁 如 for update InnoDB默认行级锁。行级锁都是基于索引的 如果一条SQL语句用不到索引是不会使用行级锁的，会使用表级锁把整张表锁住，这点需要注意。 而且效率低。更新失败的概率比较低

乐观锁

每次拿数据都认为别人不会修改 在更新的时候判断在此期间有没有更新这个数据 乐观锁并未真正加锁，效率高。 一旦锁的粒度掌握不好，更新失败的概率就会比较高，容易发生业务失败。

select money from user where user_id = 1
		
update user set money = 2 where user_id = 1 and x = 3; 

数据类型-整型

tinyInt 1个字节 INT(1)和INT(10)仅仅在展示时可能有区别，在别的方面没有任何区别。 smallInt 2个字节 MediumInt 3个字节 Int 4个字节 bigint 8个字节

Q: 数据库自增ID用完了会怎样? A: int的话 最长是2^32 不够的话,首先 要mysql主键改为bigint，也就是8个字节, 设计的时候要考虑清楚值的上限是多少，如果业务频繁插入的话，21亿的数字其实还是有可能达到的。 自增ID达到上限用完了之后，分为两种情况： 如果设置了主键，那么将会报错主键冲突。 如果没有设置主键，数据库则会帮我们自动生成一个全局的row_id，新数据会覆盖老数据

数据类型-浮点

float 单精度 4个字节 double 双精度 8个字节 float(M,D) 相当于浮点数一共M位 小数点右边D位 对于小数-2.3来说有效数字个数就是2，对于小数0.9来说有效数字个数就是1。 对于DECIMAL(M, D)类型来说，给定的M和D的值不同，所需的存储空间大小也不同 与浮点数相比，定点数需要更多的空间来存储数据， 所以如果不是在某些需要存储精确小数的场景下，一般的小数用浮点数表示就足够了

Q：float单精度和double双精度为什么会丢失? A：那是因为转成二进制的时候,转换成的二进制小数就是一个无限小数，但是我们现在 只能用4个字节或者8个字节来表示这个小数，所以只能进行一些舍入来近似的表示,这就是精度丢失

数据类型-时间

YEAR 年 2020 DATE 年月日 2020-10-20 TIME 时分秒 20:10:20 DATETIME 年月日时分秒（由上面两个组成） timestamp 时间戳 用timestamp比datetime的好处是 timestamp根据时区显示 而后者不会 DATETIME(0)表示精确到秒，DATETIME(3)表示精确到毫秒，DATETIME(5)表示精确到10微秒

数据类型-时间

varchar 变长 字符串长短不一，那么使用CHAR(M)可能会浪费很多存储空间，VARCHAR(M)可以解决

char 定长 char默认存储的字符数量为1 所以 char = char(1) char(0) 只能存储 一旦你确定了CHAR(M)类型的M的值， 如果M的值很大，而你实际存储的字符串占用字节数又很少，会造成存储空间的浪费

tinyText 非常小型字符串 text 小型字符串 mediumText 中等字符串 LongText 大型字符串

函数

文本处理函数

1. LEFT('abc123',3) 结果是 abc
2. RIGHT('abc123',3) 结果是 123
3. length('abc') 结果是 3
4. lower('ABC') 结果是 abc
5. upper('abc') 结果是 ABC
6. ltrim(' abc') 结果是 abc
7. rtrim('abc ') 结果是 abc
8. substring('abc123',2,3) 结果是 bc1
9. concat('a','b','c')s 结果是 abc

时间函数

1. now() 返回当前日期和时间 now() 2019-08-16 17:10:43
2. curdate() 返回当前日期 curdate() 2019-08-16
3. curtime() 返回当前时间 curtime() 17:10:43
4. date() 将给定日期和时间值的日期提取出来 DATE('2019-08-16 17:10:43') 2019-08-16
5. date_add() DATE_ADD('2019-08-16 17:10:43', INTERVAL 2 DAY) 将给定的日期和时间值添加指定的时间间隔
6. date_sub() DATE_SUB('2019-08-16 17:10:43', INTERVAL 2 DAY) 将给定的日期和时间值减去指定的时间间隔
7. date_diff() DATEDIFF('2019-08-16', '2019-08-17'); 返回两个日期之间的天数
8. dete_format() date_format(now(),'%Y-%m-%d %H') 给定的格式显示日期

视图

复用这些查询语句 相当于把语句出来的结果 当作一个虚拟表 使用层面，我们完全可以把视图当作一个表去使用，但是它的实现原理却是在执行语句时转换为对底层表的操作 新创建的视图的名称不能和当前数据库中的其他视图或者表的名称冲突

通过查询语句生成视图

语法： create view 试图名字 as 查询语句; 创建之后如何使用: 平时怎么从真实表中查询信息，就可以怎么从视图中查询信息 如：select * from 试图名字 使用视图的好处也是显而易见的，视图可以简化语句的书写，避免了每次都要写一遍又臭又长的语句，而且对视图的操作更加直观，使用者也不用去考虑它的底层实现细节

通过视图生成视图 语法：create view 视图名 as 查询视图的语句;

create view by_view as select m1 , n2 from male_student_view;

也可以自定义字段名
create view by_view_column(c1,c2) as select m1,n2 from by_view;

查看有哪些视图

show tables;

查看视图的定义

show create view 视图名

删除视图

drop view 视图名

注：一般我们只在查询语句里使用视图，而不在INSERT、DELETE、UPDATE语句里使用视图！

变量

set @a = 1;
查看变量值 select @a;
查询的结果赋值给一个变量，前提是这个查询的结果只有一个值
select m1 from table limit 1 into @a;

语句结束分隔符

用delimiter命令来自定义MySQL的检测语句输入结束的符号，也就是所谓的

delimiter $
语句
$

delimiter $命令意味着修改语句结束分隔符为$

存储程序

可以被分为存储例程、触发器、事件这几种类型， 其中存储例程需要我们去手动调用 而触发器和事件都是MySQL服务器在特定条件下自己调用的

可以分为存储函数和存储过程

存储函数

规范跟go语言类似 参数不能有默认值

创建

CREATE FUNCTION 函数名
RETURNS 返回值类型
BEGIN
	函数内容
	END
	如：
	delimiter $ 
	CREATE FUNCTION avg_score(s VARCHAR(100)) 
	RETURNS DOUBLE 
	BEGIN 
		RETURN (AVG(s));  
	END $

调用

同mysql自带的函数一样
select  avg_score(11)

删除函数

drop function 名字

函数中使用变量（不再能使用@符号）

delemiter $
create function var_demo()
returns int
begin
# 声明变量
declare c int
	# 初始化
	set c = 5
	return c
end

在函数中给变量默认值  declare a default 1; return a;
在函数中自定义变量  set @a = 1; return @a;  
调用这个函数之后 再 调用变量 就会发现已经完成了赋值 如下：
create function user_defined_var_demo()
returns int
begin
	set @abc = 10
	return @abc;
end
然后调用函数  select user_defined_var_demo()
然后打印变量 发现通过函数的调用之后已经给变量一个初始化的值
select @abc;

常用语句

判断语句

IF XXX THEN
ELSEIF XXX THEN
ELSE
END IF
delimiter $
CREATE FUNCTION condition_demo(i INT)
RETURNS VARCHAR(10)
BEGIN
DECLARE result VARCHAR(10);
IF i = 1 THEN
SET result = '结果是1';
ELSEIF i = 2 THEN
SET result = '结果是2';
ELSEIF i = 3 THEN
SET result = '结果是3';
ELSE
SET result = '非法参数';
END IF;
RETURN result;
END $

循环语句

//写法1
WHILE XXX DO
END WHILE
delimiter $
CREATE FUNCTION sum_all(n INT UNSIGNED)
RETURNS INT
BEGIN
DECLARE result INT DEFAULT 0;
DECLARE i INT DEFAULT 1;
WHILE i <= n DO
SET result = result + i;
SET i = i + 1;
END WHILE;
RETURN result;
END $

//写法2
repeat
xxx
until xxx end repeat
CREATE FUNCTION sum_all(n INT UNSIGNED)
RETURNS INT
BEGIN
DECLARE result INT DEFAULT 0;
DECLARE i INT DEFAULT 1;
REPEAT
    SET result = result + i;
    SET i = i + 1;
UNTIL i > n END REPEAT;
RETURN result;
END

//写法3
loop
	xxx
end loop
CREATE FUNCTION sum_all(n INT UNSIGNED)
RETURNS INT
BEGIN
DECLARE result INT DEFAULT 0;
DECLARE i INT DEFAULT 1;
LOOP
IF i > n THEN
    RETURN result;
    END IF;
    SET result = result + i;
    SET i = i + 1;
END LOOP;
END

存储过程

与存储函数最直观的不同点就是，存储过程的定义不需要声明返回值类型

语法

create procedure 名字（in/out/inout 参数名  数据类型） 默认是in
	begin
		xxx
	end
	如：
		create procedure t1_operation(m1,n2)
		begin
			insert into table values(m1,n2);
		end

调用

CALL 过程名（参数）

如： call t1_operation(1,2)
存储过程在执行中 只要是查询的产生的所有结果集，全部将会被显示到客户端
in out inout:
in:用于调用者向存储过程传递数据
out:用于把存储过程运行过程中产生的数据赋值给OUT参数
inout:既可以用于调用者向存储过程传递数据，也可以用于存放存储过程中产生的数据以供调用者使用
			

存储过程和存储函数的区别

1. 前者不需要returns
2. 存储函数执行过程中产生的结果集并不会被显示到客户端，而存储过程执行过程中产生的结果集会被显示到客户端
3. 存储函数只支持IN参数，而存储过程支持IN参数、OUT参数、和INOUT参数
4. 存储函数直接在表达式中调用，而存储过程只能通过CALL语句来显式调用

触发器

语法

create trigger 名
		before/after
		insert/delete/update
		on 表
		for each row
		begin
			内容
		end

说明

after 是指在 某些动作执行之后触发这个触发器

beforec 是指  在某些动作执行之前进行触发

使用

当插入的值小于1 那就把这个值改成1 大于10 改成10 否则就原样插入

create trigger bi_t1
before insert on t1
for each row
begin
if new.m1 < 1 then
set new.m1 = 1;
elseif new.m1 > 10 then
set new.m1 = 10;
end if;
end $

指定字段去触发

CREATE TRIGGER tri_t_a_update AFTER UPDATE ON t_a
FOR EACH ROW
BEGIN
   IF new.balance <> old.balance THEN
      INSERT INTO t_a_history(
chain_id
,`new_balance`
,`old.balance`)
values(
new.chain_id
,new.balance
,old.balance);
   END IF;
END;

删除和查看

show triggers;
show create trigger 名字
drop trigger 名

事件

跟 JS的 setTimeOut 和 setInterval 类似

在某个时间点或者每隔一段时间自动地执行一些语句

语法

create event 名
on schedule
at 某个时间  /every 事件间隔
do 
begin
	内容
end

用法：在某个时间段插入数据

create event insert_t1_event
on schedule
at '2020-04-20 16:10:01'
do
begin
	insert into t1(m1,n1) values(6,'f');
end

或

CREATE EVENT insert_t1
ON SCHEDULE
EVERY 1 HOUR
DO
BEGIN
	INSERT INTO t1(m1, n1) VALUES(6, 'f');
END

查看和删除

show events;
		
show create event 名字;

drop event 名字;

关于缓存

5.7.20之后 不推荐使用 8.0废弃 为什么？ 虽然查询缓存有时候可以提升性能 但是也要因为维护这一块儿造成开销 比如缓存中没命中 例如使用now()函数查询这个事件的数据 做成缓存下次查询的话 就不符合要求了

字符集

一个字符映射成一个二进制数据的过程也叫做编码， 将一个二进制数据映射到一个字符的过程叫做解码

utf8字符集表示一个字符需要使用1～4个字节 阉割过的utf8是utf8mb3 一个字符需要3个字节 在MySQL中utf8是utf8mb3的别名 正宗的utf8是 utf8mb64

名称后缀意味着该比较规则是否区分语言中的重音、大小 _ci 不区分大小写
_cs 区分大小写

字符集（characters）和比较规则(collate)的四个级别

比较规则的作用通常体现比较字符串大小的表达式以及对某个字符串列进行排序中

服务器级别

字符集：character_set_server 查看：show variables like 'character_set_server' 比较规则：collation_server 查看：show variables like 'collation_server'

数据库级别 （写在创建库的语句后面）

如： create database charset_demo_db character set gb2312 collate gb2312_chinese_ci 查看当前数据库的字符集：show variables like 'character_set_database'; 查看当前数据库的比较规则：show variables like 'collation_database';

表级别（写在创建表的语句的括号外面 和配置引擎的地方一样）

create table vvv( col varchar(100))character set utf8 collate utf8_general_ci;

列级别

create table aaaa( col varchar(100) character set utf8 collate utf8_general_ci );

注：列没有制定字符集和比较规则 那么就往上找表，表没有就找库，库没有就找服务

MySQL中字符集的转换

character_set_client:服务器解码请求时使用的字符集 character_set_connection:服务器处理请求时会把请求字符串从character_set_client转为character_set_connection character_set_results:服务器向客户端返回数据时使用的字符集

为了防止出现问题：通常都是统一设置这三个变量（一般设置为utf8 windows设置为gbk）

set character_set_client = 字符集名;(客户端) set character_set_connection = 字符集名;（连接） set character_set_results = 字符集名; （返回结果）

总结

1. 字符集是某个字符范围的编码规则
2. 比较规则某个字符集中的字符比较大小的一种规则
3. 一个字符集有多个比较规则，有一个默认规则，一个比较规则对应一个字符集