MySQL基础命令笔记

发则韩

已于 2023-03-24 15:12:27 修改

阅读量8.1k

点赞数 1

分类专栏：学习记录 mysql 文章标签： mysql基础学习笔记

于 2019-11-12 15:33:25 首次发布

本文链接：https://blog.csdn.net/weixin_43575868/article/details/103028595

版权

学习记录同时被 2 个专栏收录

31 篇文章 0 订阅

订阅专栏

mysql

3 篇文章 0 订阅

订阅专栏

学习链接
 数据类型（列类型）
DDL-数据定义语言
 DML-数据操作语言
 DCL-数据控制语言
 DQL-数据查询语言
 source命令的备份与还原
 数据库约束
 表关系
 三范式
 多表查询

文章目录

学习链接

MyISAM与InnoDB 的区别（9个不同点）
数据库三范式

DDL（Data Definition Language）：数据定义语言
　创建、删除、修改：库、表结构
DML（Data Manipulation Language）：数据操作语言
　增、删、改：表记录
DCL（Data Control Language）：数据控制语言
　用来定义访问权限和安全级别
DQL（Data Query Language）：数据查询语言
　用来查询记录（数据）

show variables like ‘character%’;：查看mysql当前使用的编码

//写多条语句时，需要加BEGIN和END

//默认情况下, 在CMD中操作mysql遇到分号也就是;就会认为语句结束了。
在创建存储过程中有许多分号，因此需要先执行一个DELIMITER //语句，将//作为语句结束的字符 delimiter是mysql分隔符,在mysql客户端中分隔符默认是分号，如果使用delimiter设置了其他的分隔符，再重新设置回分号即可。命令 delimiter ;

数据类型（列类型）

int:整型
double：浮点型，例如double（5,2）表示最多5位，其中必须有2位小数
decimal：浮点型，在表单钱方面使用该类型，因为不会出现精度缺失问题
char：固定长度字符串类型：char（255）,数据长度不足指定长度，补足到指定长度
varchar：可变长度字符串类型；varchar（65535）,数据长度不足不会补足空格
text(clob)：字符串类型：mysql独有的
blob：二进制数据存储,字节类型
date:日期类型，格式为:yyyy-MM-dd;
time:时间类型，格式为:hh:mm:ss
timestamp：时间戳类型，既有时分秒，又有年月日

DDL-数据定义语言

DDL之操作数据库：数据库或表的结构操作（[]中都是可选）

SHOW DATABASES;：查看所有数据库
USE 数据库名称;：进入数据库
CREATE DATABASE [IF NOT EXISTS] 数据库名称 [CHARSET=utf8];：创建数据库
DROP DATABASE [IF EXISTS] 数据库名称;：删除数据库
ALTER DATABASE 数据库名称 CHARACTER SET utf8;：修改数据库编码

DDL之操作表:

CREATE TABLE 表名 (列名列类型，列名列类型, …);：创建表
SHOW CREATE TABLE 表名;；查看指定表的创建语句（建表语句）
SHOW TABLES;:查看当前库的所有表
DROP TABLE 表名;：删除表
DESC 表名;：查看表结构
ALTER TABLE 表名 RENAME TO 新表名;：重命名表名
ALTER TABLE 表名 ADD 列名列类型, ADD 列名列类型,…;：添加列
ALTER TABLE 表名 DROP 列名：删除列
ALTER TABLE 表名 MODIFY 列名列类型;：修改列类型
ALTER TABLE 表名 ADD PRIMARY KEY(列名);：给表添加主键，删除把add改为drop
ALTER TABLE newcar CHANGE id id int(11) NOT NULL AUTO_INCREMENT;:修改mysql表的字段为自增长
CREATE PROCEDURE all_stu() select * from stutbl;：创建存储过程查询某表所有数据
ALTER TABLE 表名 character set 字符集;：修改字符集

DML-数据操作语言

DML之增、删、改数据

插入数据:
插入记录总是插入一行，未给定值的都插入NULL
INSERT INTO 表名(列名,列名,…) values (‘列值’,‘列值’,…);：按对应的列名插入值
INSERT INTO 表名 values (‘列值’,‘列值’,…):按表的顺序插入值
CREATE TABLE student2 LIKE student;：创建表student2结构和student一样
INSERT INTO student2(name,age) SELECT name,age FROM student;：将student中的name,age字段数据插入到student2
修改数据:
UPDATE 表名 SET 列名1 = ‘列值1’ [WHERE 条件]：修改数据
运算符：=、!=、<>，BETWEEN … AND …，NULL，IN(…)、IS NULL、NOT、OR、AND
删除数据:
DELETE FROM 表名 [WHERE 条件]:删除数据,不加条件数据全部被删除
TRUNCATE TABLE 表名;：删除表记录
truncate和delete的区别：
　　1. delete是将表中的数据一条一条删除
　　2. truncate是将整个表摧毁，重新创建一个新的表,新的表结构和原来表结构一模一样

DCL(理解)-数据控制语言

* 一个项目创建一个用户！一个项目对应的数据库只有一个！ * 这个用户只能对这个数据库有权限，其他数据库你就操作不了了！

创建用户
* CREATE USER ‘用户名’@‘IP地址’ IDENTIFIED BY ‘密码’;
用户只能在指定的IP地址上登录
* CREATE USER ‘用户名’@‘%’ IDENTIFIED BY ‘密码’;
用户可以在任意IP地址上登录
给用户授权
* GRANT 权限1，…，权限n ON 数据库.表名 TO 用户名@IP地址;
权限、用户、数据库
给用户分派在指定的数据库上的指定的权限
* GRANT ALL ON 数据库.* TO 用户名@IP地址
给用户分派指定数据库上的所有权限
撤销权限
* REVOKE 权限1，…，权限n ON 数据库.* FROM 用户名@IP地址;
撤销指定用户在指定数据库上的指定权限
查看权限
* SHOW GRANTS FOR 用户名@IP地址
查看指定用户的权限
删除用户
* DROP USER 用户名@IP地址

DQL-数据查询语言

----查询不会修改数据库表记录！
一、基本查询

查询所有列
SELECT * FROM 表名;
例：SELECT * FROM emp;
–> 其中“*”表示查询所有列
指定列查询
SELECT 列1[,列2,…,列n] FROM 表名;
例：SELECT empno,sal FROM emp;
完全重复的记录只显示一次
列名前加关键字 DISTINCT
当查询结果中多行记录一模一样时，只显示一行
SELECT DISTINCT * | 列1[，列2，…，列n] FROM 表名;
例：SELECT DISTINCT sal FROM emp;
–> 显示emp表中的工资，如果存在相同的工资只显示一次
列运算
4.1 数量类型的列可以做加、减、乘、除运算
　SELECT sal*1.5 FR0M emp;
　SELECT sal+ifnull(comm,0) FROM emp;
　 --> 必须是数值类型，字符型则等于0
4.2 转换NULL值
　 > 任何类型跟NULL相加还是NULL，要想相加，则需要用函数IFNULL(列名,值);
　 --> 有时需要把NULL转换成其他值,如com+1000时,如果com列存在NULL值,那么NULL+1000还是NULL,而我们希望把NULL当0来运算SELECT IFNULL(comm,0)+1000 FROM emp;
　--> IFNULL(comm,0)：如果comm中存在NULL值，那么当成0来运算。
4.3 字符串类型做连续运算
　> 函数CONCAT(‘str1’, ‘str2’[列名], …,)
　例：SELECT CONCAT(‘我叫’,ename,‘我的工作是’,job) AS 介绍 FROM emp;
4.4 给列起别名
　> SELECT 列名 AS 新列名(AS可以省略) FROM 表名;
　-->SELECT ename 姓名,job 职业 from emp;

二、条件查询

条件查询

与UPDATE、DELETE语句一样，SELECT语句也可以使用WHERE字句来控制记录。
模糊查询

模糊查询需要使用运算符：LIKE, 匹配一个任意字符, %匹配多个任意字符
例：SELECT * FROM emp WHERE ename LIKE '张’;
–> 查询姓张，名字只有一个字的人
SELECT * FROM emp WHERE ename LIKE ‘张%’;
–> 查询姓张，名字有一个或几个字的人
SELECT * FROM emp WHERE ename LIKE ‘%小%’;
–> 查询名字里含有小字的人
一些等价和注意的东西

WHERE age >= 18 AND age <= 80 等价于 WHERE age BETWEEN 18 AND 80
WHERE name=‘zhangsan’ OR name=‘lisi’ 等价于 WHERE name IN(‘zhangsan’,‘lisi’)
WHERE age IS NULL，不能使用等号
WHERE age IS NOT NULL

三、排序

升序

SELECT * FROM 表名 ORDER BY 列名 ASC;
–> ASC的全写是Ascend,可以省略
降序

SELECT * FROM 表名 ORDER BY 列名 DESC;
–> DESC的全写是Descend,不可以省略
使用多列作为排序条件

在第一列中存在重复值时，引入第二列排序或第n列排序
SELECT * FROM 表名 ORDER BY 字段名1 ASC,字段名2 DESC;

四、聚合函数

COUNT

SELECT COUNT(*) FROM 表名
–> 计算表中一行中任意一列不为NULL的记录的行数
SELECT COUNT(列名) FROM 表名
–> 计算表中的列不为NULL的记录的行数
MAX

求列中的最大值
MIN

求列中的最小值
SUM

查询一列的和
例：SELECT SUM(sal) 总和 FROM emp;
AVG

查询一列的平均数
例：SELECT COUNT(*) 人数,SUM(sal) 工资和,MAX(sal) 最高工资,MIN(sal) 最低工资 FROM emp;

五、分组查询
分组查询是把记录使用某一列进行分组，然后查询组信息。

mysql语法顺序：
select [distinct] 列名,聚合函数(*不能含有其他列)
from 表名
[join(如left join)]
[on]
[where 条件] -> 这是分组前筛选
[group by] 列名
[having 条件:一般为聚合函数] -> 这是分组后筛选
[union]
[order by 列名或聚合函数 ASC或DESC]
[limit 子句]

mysql执行顺序：
from
on
join
where
group by (可以使用select中的别名，后面的语句中都可以使用)
having
select
distinct
union
order by

例：SELECT sal,count() FROM emp WHERE sal>10000 GROUP BY deptno HAVING count()>=2 ORDER BY deptno DESC;

having和where的区别

having是在分组后对数据进行过滤；where是在分组前对数据进行过滤
having后面可以使用聚合函数；where后面不可以使用聚合函数

六、limit子句（方言）

只能在mysql中使用
LIMIT用来限定查询结果的起始行，以及总数行。起始行计算方法:(当前页-1)*每页记录数
LIMIT offset,length(其实就是从第0行开始跳过offset条，查询之后的length条数据)
例：SELECT * FROM emp LIMIT 0,5;
–> 其中0表示从第一行开始，显示五条记录

source命令的备份与还原

备份格式： `mysqldump -u用户名 -p密码数据库 > 文件的路径` 还原格式： `SOURCE 导入文件的路径` 注意：还原的时候需要先登录mysql，并选中对应的数据库

数据库约束

约束种类有：

PRIMARY KEY：主键
UNIQUE：唯一
NOT NULL：非空
DEFAULT：默认
5. FOREIGN KEY：外键

主键
　　特点：主键必须包含唯一的值，且主键不能为空
　　创建方式：在创建表的时候给字段添加主键
　　　　格式：字段名字段类型 PRIMARY KEY
　　删除主键：ALTER TABLE 表名 DROP PRIMARY KEY;
　　主键自增：
　　　　格式：字段名字段类型 PRIMARY KEY AUTO_INCREMENT
　　默认自增值为1,若修改起始值则：
　　　　格式：ALTER TABLE 表名 AUTO_INCREMENT = 起始值
　　DELETE和TRUNCATE的区别扩展：
　　　　DELETE 删除表中的数据，但不重置AUTO_INCREMENT的值；TRUNCATE 摧毁表，重建表，AUTO_INCREMENT重置为1
唯一
　　格式：字段名字段类型 UNIQUE
非空
　　格式：字段名字段类型 NOT NULL
默认值
　　格式：字段字段类型 DEFAULT 默认值
　　一张表中某个字段设置了非空与唯一约束，该字段与主键的区别：1、一张表只有一个字段可以设置成主键。2、一张表可以有多个字段非空与唯一约束。3、主键可以自动增长，非空与唯一约束的字段不能自动增长
外键
　　外键约束：一张从(副)表中的某个字段引用另一个主表的主键，其中主表用于约束别人，从表使用别人的数据，被别人约束
　　新建表时增加外键：
　　[CONSTRAINT] [外键约束名称] FOREIGN KEY(外键字段名) REFERENCES 主表名（主键字段名） [ON UPDATE CASCADE] [ON DELETE CASCADE]
　　修改表时增加外键：
　　ALTER TABLE 表名 ADD CONSTRAINT 外键约束名称 FOREIGN KEY(外键字段名) REFERENCES 主表名(主键字段名)
　　外键级联：在修改和删除主表的主键时，同时更新或删除副表的外键值
　　ON UPDATE CASCADE – 级联更新，主键发生更新时，外键也会更新
　　ON DELETE CASCADE – 级联删除，主键发生删除时，外键也会删除

表关系

一对多建表原则：在从表（多）创建一个字段作为外键指向主表（一）的主键
多对多建表原则：形成中间表，两个表为主表（一），中间表为从表（多），形成两个一对多关系
一是主，多是从，哪个表有外键哪个是从
一对一建表原则：两种，在实际的开发中应用不多.因为一对一可以创建成一张表。
　　1.外键唯一：主表的主键和从表的外键（唯一），形成主外键关系。
　　2.外键是主键：主表的主键和从表的主键，形成主外键关系。

三范式

第一范式：每一列都不能再拆开

第二范式：一张表只描述一件事情、表中的每个字段都依赖于主键
如果不遵守第二范式，数据冗余，相同数据无法区分。通过第二范式减少数据冗余，通过主键区分相同数据。

第三范式：使用主外键方式关联，而不是使用非主键关联
主键（非业务字段）非主键（业务字段）

多表查询

多表查询步骤：

确定表
确定关联关系
需要查询哪些字段

内连接:用左边表的记录去匹配右边表的记录，如果符合条件的则显示(两表的交集)
隐式内连接：看不到JOIN关键字，条件使用WHERE指定
SELECT 字段名 FROM 左表,右表 WHERE 条件;
显式内连接：使用INNER JOIN ... ON 语句，可以省略INNER
SELECT 字段名 FROM 左表 INNER JOIN 右表 ON 条件
左外连接:用左表的记录去匹配右边表的记录，符合条件则显示；否则显示NULL(在内连接基础上保证左表数据全部显示)
使用LEFT [OUTER] JOIN ... ON,OUTER可以省略
SELECT 字段名 FROM 左表 LEFT [OUTER] JOIN 右表 ON 条件;
右外连接:用右表的记录去匹配左边表的记录，符合条件则显示；否则显示NULL(在内连接基础上保证右表数据全部显示)
使用RIGHT [OUTER] JOIN ... ON,OUTER可以省略
SELECT 字段名 FROM 左表 RIGHT [OUTER] JOIN 右表 ON 条件;
子查询
子查询结果如果只是要单列，肯定在WHERE后面作为条件(多行时父查询用IN运算符)
SELECT 查询条件 FROM 表 WHERE 字段=(子查询);
子查询结果如果是要多列多行，肯定在FORM后面作为条件
SELECT 查询条件 FROM (子查询) 表别名 WHERE 条件

mysql拷贝数据

详细:https://www.cnblogs.com/lyjing/p/8483742.html

复制表结构:
CREATE TABLE 新表名 LIKE 旧表名
复制表数据:
CREATE TABLE 新表名 AS (SELECT * FROM 旧表名) 或 INSERT INTO 新表名 (SELECT * FROM 旧表名)
真正的复制一个表(表结构和表数据):
CREATE TABLE 新表名 LIKE 旧表名
CREATE TABLE 新表名 AS (SELECT * FROM 旧表名) 或 INSERT INTO 新表名 (SELECT * FROM 旧表名)

全局级别变量、会话级别变量、本地用户级别变量区别

				-- 本地用户级变量
				set @interactive_timeout = 170;
				select @interactive_timeout;
				 -- 会话级变量
				set @@interactive_timeout = 70;
				set @@wait_timeout = 70;
				select @@interactive_timeout;
				-- 全局变量
				set global interactive_timeout = 10;
				set global wait_timeout = 10;
				select @@global.interactive_timeout;

mysql 表锁状况查询

mysql 表锁状况查询原文链接：https://blog.csdn.net/ui466327/article/details/80857989

MySQL出现Waiting for table metadata lock的原因以及解决方法原文链接：https://www.cnblogs.com/digdeep/p/4892953.html

### mysql 表锁状况查询 ###

    -- 查看innodb引擎情况
    show engine innodb status\G;
 
    show status like 'Table%';
    -- Table_locks_immediate  指的是能够立即获得表级锁的次数
    -- Table_locks_waited  指的是不能立即获取表级锁而需要等待的次数
 
    -- 查看锁状态
    show status like '%lock%';
 
    -- 查看正在被锁定的的表
    show OPEN TABLES where In_use > 0;
 
    -- 查看事务的锁状况
    SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;
    SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;
 
    -- 查看正在执行的进程
    show processlist;
    
	show VARIABLES;
	SHOW GLOBAL VARIABLES;
	show global VARIABLES like '%timeout%'; 
	show global status;
    
### MySQL出现Waiting for table metadata lock的原因以及解决方法  ###

	-- 查看未提交的事务, 如果阻塞则 kill掉让其回滚
	select * from information_schema.innodb_trx;
	kill id;

mysql根据binlog恢复数据(非主从架构)

参考链接：Mysql误删表中数据与误删表的恢复方法
 MySQL之binlog
大厂基本功 | MySQL 三大日志 ( binlog、redo log 和 undo log ) 的作用？

mysql命令

-- 查看当前innodb引擎状态
show engine innodb status;

-- 查看所有的binlog文件
show master logs;

-- 查看最新的binlog文件以及位置
show master status;

-- 查看mysql数据库配置(linux下为/etc/my.cnf)
SHOW VARIABLES LIKE '%log_bin%';

-- 查看binlog记录格式 三种 
-- 1 statement 记录原sql(sql语句中涉及到NOW(), 恢复时无法获取当时的值)  
-- 2 row 不单单是简单是sql, 包含数据记录，使用mysqlbinlog导出时加-vv可以查看伪sql值
-- 3 mixed 记录的内容是前两者的混合, MySQL会判断这条SQL语句是否可能引起数据不一致，如果是，就用row格式，否则就用statement格式
SHOW VARIABLES LIKE '%binlog_format%';

-- 查看某个binlog的详细记录
show binlog events in 'binlog.000046';

系统下命令
mysqlbinlog [options] log-files

实验：误删除两条数据，进行恢复（前提恢复期间binlog文件不再新增记录）

方法一：找删除记录前后位置

先使用mysqlbinlog命令导出可以读懂的sql log,
mysqlbinlog -vv D:\mysql-8.0.27-winx64\data\binlog.000046 > temp.sql
查看temp.sql文件, 找到被删除操作位置, 假设删除第一条记录起始pos为 15437 , 删除第二条记录截止pos为 16067 , 整个binlog最新的pos为 16647

– 恢复删除前的记录
mysqlbinlog -vv --stop-position=15437 D:\mysql-8.0.27-winx64\data\binlog.000046 | mysql -uroot -p123456
– 恢复删除后的记录
mysqlbinlog -vv --start-position=16067 --stop-position=16647 D:\mysql-8.0.27-winx64\data\binlog.000046 | mysql -uroot -p123456

执行成功后, 可以看到数据已经被恢复

方法二：导出binlog为sql文件，并修改sql文件

直接在temp.sql中删除delete语句, 然后执行mysqlbinlog恢复
手动删除文件中的delete语句
执行mysqlbinlog命令 mysqlbinlog -vv D:\mysql-8.0.27-winx64\data\binlog.000046 | mysql -uroot -p123456

mysql启动、禁用外键或删除全部外键

---- 修改MySQL变量FOREIGN_KEY_CHECKS

-- Session级别
 -- 禁用Session外键约束
SET FOREIGN_KEY_CHECKS = 0;

-- 启用Session外键约束
SET FOREIGN_KEY_CHECKS = 1;

-- 查看Session外键约束状态
SELECT @@FOREIGN_KEY_CHECKS;


-- 全局级别
 -- 禁用全局外键约束
SET @@GLOBAL.FOREIGN_KEY_CHECKS = 0;

-- 启用全局外键约束
SET @@GLOBAL.FOREIGN_KEY_CHECKS = 1;

-- 查看全局外键约束状态
SELECT @@GLOBAL.FOREIGN_KEY_CHECKS;

---- 删除全部外键, 查询schema中所有外键名称然后拼接生成删除语句，再执行
SELECT CONCAT('ALTER TABLE ',TABLE_SCHEMA,'.',TABLE_NAME,' DROP FOREIGN KEY ',CONSTRAINT_NAME,' ;') 
FROM information_schema.TABLE_CONSTRAINTS c 
WHERE c.TABLE_SCHEMA='库名' AND c.CONSTRAINT_TYPE='FOREIGN KEY';

mysql索引

原文链接：https://www.bilibili.com/video/BV12b411K7Zu?p=209

left join：

left join或者是right join时，反着建立索引，左连接在右表上建索引，右连接在左表上建索引
尽量减少join语句中的NestedLoop循环总次数，永远用小结果集驱动大的结果集
优先优化NestedLoop的内层循环
保证join语句中被驱动表上的join条件字段已经被索引

in和exists的区别：

select * from A where id in (select id from B)
等价于：
for select id from B
for select * from A where A.id = B.id
当B表的数据集小于A表的数据集时，用in优于exists，即in里是小结果集，in外是大结果集

select * from A where exists (select 1 from B where A.id = B.id)
等价于：
for select * from A
for select 1 from B where A.id = B.id
当A表的数据集小于B表的数据集时，用exists优于in，即exists里是大结果集，exists外是小结果集

not in和not exists的区别：

原文链接：https://www.cnblogs.com/seasons1987/archive/2013/07/03/3169356.html

not in 逻辑上不完全等同于not exists，如果你误用了not in，小心你的程序存在致命的BUG，请看下面的例子：

create table #t1(c1 int,c2 int);

create table #t2(c1 int,c2 int);

insert into #t1 values(1,2);

insert into #t1 values(1,3);

insert into #t2 values(1,2);

insert into #t2 values(1,null);

 

select * from #t1 where c2 not in(select c2 from #t2);　　
-->执行结果：无

select * from #t1 where not exists(select 1 from #t2 where #t2.c2=#t1.c2)　　
-->执行结果：1　3

正如所看到的，not in出现了不期望的结果集，存在逻辑错误。如果看一下上述两个select 语句的执行计划，也会不同，后者使用了hash_aj，所以，请尽量不要使用not in(它会调用子查询)，而尽量使用not exists（它会调用关联子查询）。如果子查询中返回的任意一条记录含有空值，则查询将不返回任何记录。如果子查询字段有非空限制，这时可以使用not in，并且可以通过提示让它用hasg_aj或merge_aj连接。

如果查询语句使用了not in，那么对内外表都进行全表扫描，没有用到索引；而not exists的子查询依然能用到表上的索引。所以无论哪个表大，用not exists都比not in 要快。

group by：
group by分组之前必排序，会有临时表的产生（mysql8.0不排序，待考证）

explain：

type的优化级别（最优到最差）：system>const>eq_ref>ref>range>index>all
key_len可以看走了多少索引

sql索引可能失效的情况

全值匹配最好使，也就是=号的
最左匹配原则，查询索引的最左列开始且不跳过索引中的列
例如：abc三个索引，where后面的条件顺序必须是a，ab，abc，a没了索引直接失效
不要在索引列上做任何操作（计算、函数、（自动or手动）类型转换），会导致索引失效转向全表扫描
范围之后（即>、<、!=或<>、in、between … and …）索引失效，其实是走不全
尽量使用覆盖索引（即索引列和查询列一致），减少select *
is not null会索引失效， is null不会
字符串不加单引号索引失效，varchar类型不能失去单引号（mysql8.0索引不会失效）
like '%_%'可能会索引失效，覆盖索引可避免索引失效
少用or，or的每个字段都建立索引才有效
多表关联时，关联字段字符编码不一致

生产中sql调优，目前遇到的索引失效：

1、varchar类型不能失去单引号。 多表联查sql语句中，字段类型为varchar，参数类型为int，导致完全不走索引。【Mysql版本：5.6.16】

参数类型为int， explain后type为index级别
参数类型为string，explain后type为ref级别

2、关联字段字符集和排序规则不一致导致索引失效。 用户表user_info中字段userzj字符集为utf8mb4，排序规则为utf8mb4_unicode_ci； 城市表pem_mycity中字段investzj字符集为utf8，排序规则为utf8_general_ci。 select条件中通过子查询获取用户关注城市，字符集和排序规则不一致索引失效，查询速度从 4.72s -> 0.12s【Mysql版本：5.6.16】

pem_mycity_copy1为未修改前的字符集和排序规则； pem_mycity已修改为一致的字符集和排序规则

在这里插入图片描述

索引练习题：

假设index（a,b,c）
where a=3 | 使用到索引a

where a=3 and b=5 | 使用到索引a,b

where a=3 and b=5 and c=4 | 使用到索引a,b,c

where b=3或者where c=4或者where b=3 and c=4 | 没走索引，不符合最左匹配原则

where a=3 and c=5 | 使用到索引a，但是c不可以，b中间断了

where a=3 and b>4 and c=5 | 使用到索引ab，c失效，因为在b断了

where a=3 and b like ‘kk%’ and c=4 | 使用索引abc，因为like前面的kk是定值

where a=3 and b like ‘%kk’ and c=4 | 只用到索引a

where a=3 and b like ‘%kk%’ and c=4 | 只用到索引a

where a=3 and b like ‘k%kk%’ and c=4 | 用到索引abc
假设index（a,b,c,d）
select * from test03 where c1=‘a1’ and c5=‘a5’ order by c2,c3 | 走索引c1,c2,c3

select * from test03 where c1=‘a1’ and c5=‘a5’ order by c3,c2 | 走索引c1

select * from test03 where c1=‘a1’ and c2=‘a2’ order by c3,c2 | 走索引c1,c2,c3，因为有常量c2，相当于order by c3,固定值

select * from test03 where c1=‘a1’ and c4=‘a4’ group by c2,c3 | 只走索引c1

select * from test03 where c1=‘a1’ and c4=‘a4’ group by c3,c2 | 只走索引c1，但是extra有using temporary，using filesort

mysql字符集和排序规则

原文链接：MySQL字符集与排序规则 | mysql字符设置

-- 查看系统内字符集和排序规则
SHOW CHARACTER SET;
SHOW COLLATION;

-- 查看数据库字符集编码[global | session]
SHOW global VARIABLES LIKE 'character%';
SHOW session VARIABLES LIKE 'character%';

-- 查看数据库排序规则[global | session]
SHOW global VARIABLES LIKE 'collation%';
SHOW session VARIABLES LIKE 'collation%';

-- 字符串级别的自定义字符集和排序规则(用introducer指定)
-- 格式为：[_charset] ’string’ [COLLATE collation]
set @projectzj = _utf8mb4 '1A5CB63A9AFE4CC3A123F8DC65833164' COLLATE utf8mb4_unicode_ci;
select _utf8mb4 '1A5CB63A9AFE4CC3A123F8DC65833164' COLLATE utf8mb4_unicode_ci;

开发中遇到的问题

场景:通过sql导出数据，每次需要更改项目zj、用户zj和日期范围，通过set设置变量，sql中直接读取变量的值。实现过程出现问题

原因：做连表时关联字段排序规则不一致，mysql服务器使用utf8mb4_general_ci排序规则，用户表使用utf8mb4_unicode_ci。
解决：指定字符串变量的字符集和排序规则 set @projectzj = _utf8mb4 '1A5CB63A9AFE4CC3A123F8DC65833164' COLLATE utf8mb4_unicode_ci;
场景:记录一次Mysql出现事物锁等待超时Lock wait timeout exceeded的原因以及如何解决？
原因：在高并发下，Spring事务造成数据库死锁，后续操作超时抛出异常
解决：碰到的情况是 代码内使用了声明式事务，在方法return之前未对事务提交，提交事务后正常。详细解答：Mysql事物锁等待超时Lock wait timeout exceeded

常用sql记录

取指定范围内的某几天

set @startDate = '2022-08-22';
set @endDate = '2022-09-03';
SELECT help_topic_id, date_add(@startDate, INTERVAL help_topic_id day) day 
FROM mysql.help_topic 
WHERE help_topic_id <= DATEDIFF(@endDate, @startDate)
ORDER BY help_topic_id

发则韩

关注

1
点赞
踩
6

收藏

觉得还不错? 一键收藏
1
评论
MySQL基础命令笔记

DDL（Data Definition Language）：数据定义语言　创建、删除、修改：库、表结构DML（Data Manipulation Language）：数据操作语言　增、删、改：表记录DCL（Data Control Language）：数据控制语言　用来定义访问权限和安全级别DQL（Data Query Language）：数据查询语言　用来查询记录（数据）...
复制链接

扫一扫

专栏目录