学习链接
数据类型(列类型)
DDL-数据定义语言
DML-数据操作语言
DCL-数据控制语言
DQL-数据查询语言
source命令的备份与还原
数据库约束
表关系
三范式
多表查询
文章目录
学习链接
- 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 | 使用到索引awhere 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,c3select * 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