SHOW VARIABLES LIKE '%dir%';#结果显示，数据目录就是datadir的所在位置，即 /var/lib/mysql/ service mysqld stop #停止mysql service mysqld start #启动mysql
systemctl status mysqld #查看mysql状态，当然service mysqld status也行#所以systemctl status mysqld里面的status修改成start或者stop#这样也可以使得启动（start）或者关闭（stop，停止）#他们是一样的操作，只是命令格式不同而已
进入到 /mysql 目录，执行打包命令，将数据文件打包备份：
cd /var/lib/ # 进入其上级目录tar jcvf /root/backup.tar.bz2 mysql/ # 打包压缩到 root目录下，j代表bz的方式，z代表gz的方式
DROP DATABASE lagou_edu;
CREATE DATABASE lagou_edu CHARACTER SET 'utf8';
#在mysql的linux客户端下，通常需要;来结尾，但有些不需要，如use命令
#大概是不同客户端自带的添加吧（这里是linux指定，而我们的图形化界面一般有添加，如果存在自然不会添加）
#但空格基本都是不算（操作）的，除了用特殊符号或者分号（单引号和双引号）的空格，他们的空格会算的（会操作的）
恢复数据：
gunzip < lagou_edu.sql.gz | mysql -uroot -p lagou_edu
#将指定文件的语句结果，通过gzip解压并给到mysql执行
对应的IS NOT NULL可能也不会操作，那如果是这个的话，就没有什么办法了，尽量不使用IS NOT NULL即可
SQL的执行顺序：
程序员编写的SQL
MySQL执行的SQL（下面排序的从上到下是执行顺序，即from先执行开始）：
/*
1：FORM子句：左右两个表的笛卡尔积
2：ON：筛选满足条件的数据
3：JOIN：如果是 inner join（内连接） 那就正常
如果是 outer join（外连接） 则会添加回来上面一步过滤掉的一些行
比如：select 字段名... from 左表 inner join 右表 on 连接条件
select 字段名 from 左表 right outer join 右表 on 条件
上面的inner和outer可以省略，也可以不省略，反正默认添加（没有的话）
4：WHERE：对不满足条件的行进行移除，并且不能恢复
5：GROUP BY：分组后只能得到每组的第一行数据，或者聚合函数的数值
6：HAVING：对分组后的数据进行筛选
7：SELECT：执行select操作，获取需要的列
后面需要操作select的数据（通常需要显示的列名及其数据，由于要显示的列名，所以需要先select）
所以通常在select后面，显示的列名：select查询的列名，即可以被后面操作的列名
条件的列名是隐藏的，即from后面指定的表的所有列
8：DISTINCT：去重
9：ORDER BY：排序
10：LIMIT：取出指定行的记录，并将结果返回
*/
上面给出了具体的执行顺序
查看下面的SQL 分析执行顺序：
select
id,
sex,
count(*) AS num
from
employee
where name is not null
group by sex
order by id
上面的SQL执行执行顺序如下：
/*
1. 首先执行 FROM 子句，从 employee 表组装数据源的数据
2. 执行 WHERE 子句，筛选 employee 表中所有name不为 NULL 的数据
3. 执行 GROUP BY 子句，按 "性别" 列进行分组
4. 执行select操作，获取需要的列
5. 最后执行order by，对最终的结果进行排序
所以通常来说指向顺序是
from 关键字后面的语句，where 关键字后面的语句，group by 后面的语句，select 后面的语句，order by 后面的语句
当然还有其他语句或者他的顺序，前面也说明过了，如果还有其他语句或者他的顺序，具体也可以百度查看
但我们通常只需要知道主要的语句和顺序（这里就是）
*/
JOIN查询的七种方式：
7中JOIN，可以分为四类：内连接 、左连接 、右连接、 全连接
JOIN查询SQL编写：
创建表 插入数据：
-- 部门表
DROP TABLE IF EXISTS `t_dept`;
CREATE TABLE `t_dept` (
`id` varchar(40) NOT NULL,
`name` varchar(40) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- 员工表
DROP TABLE IF EXISTS `t_emp`;
CREATE TABLE `t_emp` (
`id` varchar(40) NOT NULL,
`name` varchar(40) DEFAULT NULL,
`age` int(3) DEFAULT NULL,
`deptid` varchar(40) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `deptid` (`deptid`),
CONSTRAINT `deptid` FOREIGN KEY (`deptid`) REFERENCES `t_dept` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- 插入部门数据
INSERT INTO `t_dept` VALUES ('1', '研发部');
INSERT INTO `t_dept` VALUES ('2', '人事部');
INSERT INTO `t_dept` VALUES ('3', '财务部');
-- 插入员工数据
INSERT INTO `t_emp` VALUES ('1', '赵四', 23, '1');
INSERT INTO `t_emp` VALUES ('2', '刘能', 25, '2');
INSERT INTO `t_emp` VALUES ('3', '广坤', 27, '1');
INSERT INTO `t_emp` VALUES ('4', '玉田', 43, NULL);
内连接：
SELECT * FROM t_emp e INNER JOIN t_dept d ON e.deptid = d.id
-- 自然表的先后也影响返回结果，谁先显示的问题
左连接：
SELECT * FROM t_emp e LEFT JOIN t_dept d ON e.deptid = d.id
#如果操作了外连接，那么首先以对应的表为主，也就是说，要满足表
#而不是直接的笛卡尔积或者说在笛卡尔积的基础上进行改变（一般是进行改变），即会有顺序
#这里的执行与上面的直接的执行对比就知道了
#下面的解释需要后面对基准和被匹配的描述才可明白：
#简单来说，"这里"（因为他满足这个条件）需要将右边的表的一条数据全部对应好
#才可以下一个，简称满足左表（右表一条数据有多个左边对应）
#所以执行这个，可以看到右表出现两个"研发部"才会下一个（索引的问题）
#换言之就是以左表为基准匹配右表的数据，后面的右连接则是相反的，但说明也是差不多的
#所以这个LEFT代表充分使用左表，即充分满足左边（都进行操作）
#只要操作外连接基本都是如此，或者说，需要以谁为基准的都是如此
左连接去重叠部分 ：
SELECT * FROM t_emp e LEFT JOIN t_dept d ON e.deptid = d.id WHERE e.deptid IS NULL;
右连接：
SELECT * FROM t_emp e RIGHT JOIN t_dept d ON e.deptid = d.id
右连接去重叠部分 ：
SELECT * FROM t_emp e RIGHT JOIN t_dept d ON e.deptid = d.id WHERE e.id IS NULL
全连接：
SELECT * FROM t_emp e LEFT JOIN t_dept d ON e.deptid = d.id
UNION
SELECT * FROM t_emp e RIGHT JOIN t_dept d ON e.deptid = d.id
#当然，谁的select在前面，就是以谁的结果为基准，所以，这里还是右边是两个研发部，且优先显示上面select的结果
#简单来说，就是后面的select在上面的select的基础上添加新的数据
#注意：UNION使用是有条件的，两边的结果中，字段的数量需要一致，否则执行报错，且显示的字段以前面的字段为主
#自己测试就知道了
MySQL的UNION 操作符用于连接两个以上的 SELECT 语句的结果组合到一个结果集合中
多个 SELECT 语句会删除重复的数据，所以不用担心重复的数据
各自独有：
SELECT * FROM t_emp e LEFT JOIN t_dept d ON e.deptid = d.id
WHERE e.deptid IS NULL
UNION
SELECT * FROM t_emp e RIGHT JOIN t_dept d ON e.deptid = d.id
WHERE e.id IS NULL
set global slow_query_log=1; -- 1代表开启，0代表关闭
SHOW VARIABLES LIKE '%slow_query_log%'; -- 若结果是ON，则代表开启了
SET GLOBAL slow_query_log_file='/var/lib/mysql/bdd'; -- 修改日志文件的位置
-- 注意：在关闭状态下，如果文件的权限不够或者不存在，可能修改不了
-- 并且也启动不了，即报错
-- 当然并不决定，他有时只是指定，可以执行，不会报错，启动后，就自动的创建了，大概是有什么巡查的东西
-- 如果是启动状态下，一般会自动的创建文件，前提是目录需要存在（否则也会报错）
-- 最后，注意：每次的设置，启动后，或者在启动中，都会知道，也就是说，当我们查询文件时，如果出现：
/usr/sbin/mysqld, Version: 5.7.37-log (MySQL Community Server (GPL)). started with:
Tcp port: 3306 Unix socket: /var/lib/mysql/mysql.sock
Time Id Command Argument
/usr/sbin/mysqld, Version: 5.7.37-log (MySQL Community Server (GPL)). started with:
Tcp port: 3306 Unix socket: /var/lib/mysql/mysql.sock
Time Id Command Argument
/usr/sbin/mysqld, Version: 5.7.37-log (MySQL Community Server (GPL)). started with:
Tcp port: 3306 Unix socket: /var/lib/mysql/mysql.sock
Time Id Command Argument
-- 则代表你肯定设置了三次该文件（心血来潮的执行三次，(●ˇ∀ˇ●)），代表设置的日志记录
使用 set global slow_query_log=1 开启了慢查询日志只对当前数据库（主机，而不是数据库名）生效
show variables like 'long_query_time';
set global long_query_time=1; -- 修改为1秒
show variables like 'long_query_time';
#但是我们发现并没有修改
#实际上是因为使用命令 set global long_query_time=1 修改后，需要重新连接或新开一个会话才能看到修改值
#即他们有会话的固定（该固定针对所有已经正在连接的会话，所以通常需要新连接）
#如果重启mysql，那么不会使用缓存，且默认为10了
#但是也要注意：并不是修改后，立即就会生效（比如可能受网络或者数据库太卡的影响）
#即通常需要等待他后台操作完毕才可，通常来说是很快的，一般来说创建新的会话就会可以查询到
#在这之前，或者说固定没有改变之前，新的连接还是原来的，并且固定好后，就不会变化了（一个会话只能赋值一次固定）
#所以通常需要新连接
#根据上面的说明，通过后面的超时测试，我发现，当前会话固定好的值，才是将sql语句放在日志的主要原因
#也就是说，只要分配了，那么就是操作这个阈值，而不是真实的，所以可能出现
#两个会话，一个会话的语句会记录，另外一个会话的语句不会记录
#因为他们的阈值不同，即与设置的无关，只与分配的固定阈值有关
SHOW VARIABLES LIKE '%log_output%';
SET GLOBAL log_output = 'file,table' -- 修改存储方式
SET GLOBAL log_output = 'table'
SET GLOBAL log_output = file
-- 其中因为table是真的关键字，所以需要使用引号表示
-- 很明显，我们查询什么，基本上可以直接的使用SET GLOBAL 查询的 = 值，来设置
#当然大多数代表全局的都可以这样
-- 修改存储方式
-- 经过测试：
SET GLOBAL log_output = 'file,table' -- 文件和表都有添加数据
SET GLOBAL log_output = 'table' -- 只有表添加了数据
SET GLOBAL log_output = file -- 只有文件添加了数据
-- 即的确改变了日志的存储方式
CREATE TABLE tablename(
a INT,
b INT
)
CREATE INDEX a ON tablename (a);
ALTER TABLE tablename ADD INDEX (b);
ALTER TABLE tablename ADD INDEX c (b);
ALTER TABLE tablename ADD INDEX (a);
ALTER TABLE tablename ADD INDEX (b);
CREATE TABLE ccc(
a INT,
INDEX(a)
)
CREATE TABLE cccc(
a INT,
INDEX a(a)
)
CREATE TABLE ccccc(
a INT,
INDEX b(a)
)
CREATE UNIQUE INDEX t ON tablename (a);
ALTER TABLE tablename ADD UNIQUE INDEX af (a);
ALTER TABLE tablename ADD UNIQUE INDEX (a);
CREATE TABLE cccccc(
a INT,
UNIQUE INDEX b(a)
)
CREATE TABLE ccccccc(
a INT,
UNIQUE INDEX (a)
)
CREATE TABLE eee(
a INT,
PRIMARY KEY(a)
)
CREATE TABLE eeee(
a INT,
PRIMARY KEY ab (a)
)
ALTER TABLE tablename ADD PRIMARY KEY (a);
CREATE INDEX vv ON tablename (a,b); -- 多次创建，索引名称相同，会报错
ALTER TABLE tablename ADD INDEX vvv (a,b,b); -- 报错，不能有相同的字段
ALTER TABLE tablename ADD INDEX (b,a); -- 以第一个字段作为有序名称的主体
CREATE TABLE eeeettt(
a INT,
b INT,
INDEX abb (a,b)
)
CREATE UNIQUE INDEX ttt ON tablename (a,b);
ALTER TABLE tablename ADD UNIQUE INDEX (b,a);
ALTER TABLE tablename ADD ff VARCHAR(20);
CREATE FULLTEXT INDEX afg ON tablename (ff); -- 全文索引必须在字符串、文本字段上建立
ALTER TABLE tablename ADD FULLTEXT (ff);
ALTER TABLE tablename ADD FULLTEXT fgd (ff);
CREATE TABLE kskd(
n VARCHAR(20),
FULLTEXT KEY (n)
)
CREATE TABLE kskdd(
n VARCHAR(20),
FULLTEXT KEY v (n)
)
主要的表tablename的信息：
可以根据我的测试操作来看下面的语句格式
普通索引：
这是最基本的索引类型，基于普通字段建立的索引，没有任何限制
CREATE INDEX <索引的名字> ON tablename (字段名);
ALTER TABLE tablename ADD INDEX [索引的名字] (字段名);
CREATE TABLE tablename ( [...], INDEX [索引的名字] (字段名) );
-- 上面的[]代表可以写可不行（除了创建表的[]），其他的基本都需要写，否则报错
唯一索引：
与"普通索引"类似，不同的就是：索引字段的值必须唯一，但允许有空值
CREATE UNIQUE INDEX <索引的名字> ON tablename (字段名);
ALTER TABLE tablename ADD UNIQUE INDEX [索引的名字] (字段名);
CREATE TABLE tablename ( [...], UNIQUE [索引的名字] (字段名) );
-- 无非就是多了个UNIQUE
主键索引：
它是一种特殊的唯一索引，不允许有空值，在创建或修改表时追加主键约束即可，每个表只能有一个主键
CREATE TABLE tablename ( [...], PRIMARY KEY (字段名) ); -- 如果是PRIMARY KEY ab (a)，名称直接忽略
ALTER TABLE tablename ADD PRIMARY KEY (字段名);
CREATE PRIMARY KEY <索引的名字> ON tablename (字段名); -- 没有这样的情况
复合索引：
用户可以在多个列上建立索引，这种索引叫做组复合索引（组合索引）
复合索引可以代替 多个单一索引，相比多个单一索引，复合索引所需的开销更小
CREATE INDEX <索引的名字> ON tablename (字段名1，字段名2...);
ALTER TABLE tablename ADD INDEX [索引的名字] (字段名1，字段名2...);
CREATE TABLE tablename ( [...], INDEX [索引的名字] (字段名1，字段名2...) );
CREATE TABLE course(
a INT
);
EXPLAIN SELECT * FROM course;
EXPLAIN字段介绍（主要的）：
数据准备：
-- 创建数据库
CREATE DATABASE test_explain CHARACTER SET 'utf8';
-- 创建表
CREATE TABLE L1(id INT PRIMARY KEY AUTO_INCREMENT,title VARCHAR(100) );
CREATE TABLE L2(id INT PRIMARY KEY AUTO_INCREMENT,title VARCHAR(100) );
CREATE TABLE L3(id INT PRIMARY KEY AUTO_INCREMENT,title VARCHAR(100) );
CREATE TABLE L4(id INT PRIMARY KEY AUTO_INCREMENT,title VARCHAR(100) );
-- 每张表插入3条数据，即后面的执行三次
INSERT INTO L1(title) VALUES('lagou01'); -- 执行三次
INSERT INTO L2(title) VALUES('lagou02'); -- 执行三次
INSERT INTO L3(title) VALUES('lagou03'); -- 执行三次
INSERT INTO L4(title) VALUES('lagou04'); -- 执行三次
ID介绍：
select查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序
id相同，执行顺序由上至下：
EXPLAIN SELECT * FROM L1,L2,L3 WHERE L1.id=L2.id AND L2.id = L3.id;
EXPLAIN SELECT * FROM L2,L1,L3 WHERE L1.id=L2.id AND L2.id = L3.id;
即的确有顺序，从上到下，但很明显，是id相同时，是从上到下的（根据表的顺序）
那么如果id不同呢，那么表的读取顺序是什么呢：
id不同，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行
EXPLAIN SELECT * FROM L2 WHERE id = (
SELECT id FROM L1 WHERE id = (SELECT id FROM L3 WHERE title = 'lagou03'));
-- 这里记得修改一下对应的三个表的数据，使得只有一个，否则语句是错误的
-- 操作子查询时，直接讲他看成整体，所以字段之间一般不能互相使用（上面的id只操作自己的，所以不会冲突）
-- 所以一般情况下
-- 子查询不能使用外部查询的字段，可能在有些数据库或者版本中可以使用，但也仅限于当前外部的字段，而不是外部的外部
-- 如果是这样的话，就需要考虑字段重复问题了，通常使用命名解决该问题
-- 这里了解即可
很明显，越是往里面的子查询，通常是先操作的，即L3先执行
实际上根据执行语句的顺序来说，的确需要越子查询的先执行
因为如果是主的先执行，那么后面的就会看成整体了，所以也就没有子查询的操作了，即可能会出现问题
所以mysql使得越子查询的越先执行，来得到数据，到那时，看成整体就不会出现问题了
注意：一般操作相同查询时，如果有表操作了索引
那么后执行，因为需要等待值来匹配他，所以通常操作了索引的条件的表，一般在后面
select_type和table介绍 ：
查询类型，主要用于区别普通查询，联合查询，子查询等的复杂查询
simple：简单的select查询，查询中不包含子查询或者UNION
EXPLAIN SELECT * FROM L1;
primary：查询中若包含任何复杂的子部分，最外层查询被标记
EXPLAIN SELECT * FROM L2 WHERE id = (
SELECT id FROM L1 WHERE id = (SELECT id FROM L3 WHERE title = 'lagou03'));
subquery：在select或where列表中包含了子查询
EXPLAIN SELECT * FROM L2 WHERE L2.id = (SELECT id FROM L3 WHERE title = 'lagou03')
-- 在测试之前，首先看如下sql
EXPLAIN SELECT * FROM (SELECT * FROM L1 ,L2 ) c
-- 他可以执行吗，答：不可以，因为一个表里面，或者说查询的表，不能有相同的字段（c是结合造成的）
-- 所以我们需要如下：
EXPLAIN SELECT * FROM (SELECT * FROM L1 a UNION SELECT * FROM L2 b) c
-- 注意：使用子查询代表表时，必须加上别名，否则执行报错，因为要使用时，怎么使用呢（因为是新的表）
-- 所以规定需要别名
CREATE TABLE T1(
a INT PRIMARY KEY,
b INT NOT NULL,
c INT DEFAULT NULL,
d CHAR(10) NOT NULL
);
使用explain 进行测试：
EXPLAIN SELECT * FROM T1 WHERE a > 1 AND b = 1;
索引中只包含了1列，所以key_len是4，为什么是4，这里就不做说明了，因为影响的因素实在太多
可以认为一个整型INT就是4（前提是使用了索引，且包括空），因为他就是索引使用后出现的值
比如因素也有这样的，如果在字段后面加上NOT NULL，那么他的值一般会减少1
为b字段添加索引：
ALTER TABLE T1 ADD INDEX idx_b(b);
再次测试：
EXPLAIN SELECT * FROM T1 WHERE a > 1 AND b = 1;
不是操作等值，即索引没有冲突，即都操作了
返回了8，表示两列都使用了索引，所以这里ken_len是8
为d字段添加索引：
ALTER TABLE T1 ADD INDEX idx_d(d);
执行测试：
EXPLAIN SELECT * FROM T1 WHERE d = '';
字符集是utf8，一个字符3个字节，d字段是 char（10）代表的是10个字符，相当30个字节
如果包括空，自然算31，可是有NOT NULL即这里是30了
ref 介绍：
显示索引的哪一列被使用了（即使用谁，或者操作谁，通常是基准）
如果可能的话，是一个常数。哪些列或常量被用于查找索引列上的值
L1.id=‘1’，1是常量，ref = const
EXPLAIN SELECT * FROM L1 WHERE L1.id='1';
L2表被关联查询的时候，使用了主键索引
而值使用的是驱动表L1表的ID
执行计划中靠前的表通常是驱动表，通常是"被驱动表"使用索引，这里的"被驱动表"就是L2
除非他有普通索引或者唯一索引，那么会靠前（因为可以指定字段，需要先操作）
所以这里的 ref = lagou_edu.L1.id
即查询（操作）该值
EXPLAIN SELECT * FROM L1 LEFT JOIN L2 ON L1.id = L2.id WHERE L1.title = 'lagou01';
-- 这里修改了对应的L2表的索引名称，即id字段设置的索引名称是id，这里好像是普通索引
rows 介绍：
表示MySQL根据表统计信息及索引选用情况，估算的找到所需的记录所需要读取的行数，越少越好
使用like 查询，会产生全表扫描，L2中有3条记录，就需要读取3条记录进行查找
EXPLAIN SELECT * FROM L1,L2 WHERE L1.id = L2.id AND L2.title LIKE '%la%';
-- 前面的%去掉，那么就只使用title索引，但好像没有实际使用，若都去掉，那么通常都会使用，没有造成索引冲突
-- 实际上是因为在and出现后，需要考虑他们整体了，所以局部就没有索引冲突了
-- 而or，则认为局部的冲突也是，即只有满足，那么就是冲突，而不会当成整体
也就是之前说的，以基准匹配被匹配，所以是基准的所有匹配其一个，依次类推
我们也将rows值中的1称为被匹配数，3称为基准或者匹配数
如果使用等值查询，则可以直接找到要查询的记录，返回即可，所以只需要读取一条
EXPLAIN SELECT * FROM L1,L2 WHERE L1.id = L2.id AND L2.title = 'lagou03';
-- 创建数据库
CREATE DATABASE test_lock CHARACTER SET 'utf8';
USE test_lock;
-- 创建表，选择 MYISAM存储引擎
CREATE TABLE mylock01(
id INT PRIMARY KEY AUTO_INCREMENT,
title VARCHAR(20)
)ENGINE MYISAM;
-- 创建表
CREATE TABLE mylock02(
id INT PRIMARY KEY AUTO_INCREMENT,
title VARCHAR(20)
)ENGINE MYISAM;
-- mylock01表中向插入数据
INSERT INTO mylock01(title) VALUES('a1');
INSERT INTO mylock01(title) VALUES('b1');
INSERT INTO mylock01(title) VALUES('c1');
INSERT INTO mylock01(title) VALUES('d1');
INSERT INTO mylock01(title) VALUES('e1');
-- mylock02表中向插入数据
INSERT INTO mylock02(title) VALUES('a');
INSERT INTO mylock02(title) VALUES('b');
INSERT INTO mylock02(title) VALUES('c');
INSERT INTO mylock02(title) VALUES('d');
INSERT INTO mylock02(title) VALUES('e');
SELECT * FROM mylock01;
加锁语法：
查看表中加过的锁：
-- 0表示没有加锁，当前的所有数据库表都没有加锁
SHOW OPEN TABLES;
-- 查询加锁的表，条件In_use 大于0
SHOW OPEN TABLES WHERE In_use > 0;
手动增加表锁：
-- 语法格式：LOCK TABLE 表名 READ(WRITE),表名2 READ(WRITE), （以此类推）;
-- 为mylock01加读锁（共享锁），给mylock02加写锁(排他锁)
lock table mylock01 read,mylock02 write; -- 会开启两个锁的进程，不能是这样
-- LOCK TABLE mylock01 READ,mylock01 WRITE; ，即一次性只能给一个表加一个锁，否则报错
SHOW OPEN TABLES WHERE In_use > 0;
-- 加共享锁（S）：
select * from table_name where ... lock in share mode;
-- 加排他锁（x）：
select * from table_name where ... for update;
-- 针对自己
锁兼容：
共享锁只能兼容共享锁，不兼容排它锁
排它锁互斥共享锁和其它排它锁
也就是前面说的可以操作多个只读，但只能操作一个只写
行级锁都是基于索引的，如果一条 SQL 语句用不到索引是不会使用行级锁的
而会使用表级锁把整张表锁住，这点需要咱们格外的注意，虽然看起来并没有什么不同
行锁测试：
更新时的行锁测试：
数据准备：
#创建表
CREATE TABLE innodb_lock(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20),
age INT,
INDEX idx_name(NAME)
);
-- 插入数据
INSERT INTO innodb_lock VALUES(NULL,'a', 13);
INSERT INTO innodb_lock VALUES(NULL,'b', 23);
INSERT INTO innodb_lock VALUES(NULL,'c', 33);
INSERT INTO innodb_lock VALUES(NULL,'d', 43);
打开两个窗口，都开启手动提交事务 （提交事务或回滚事务就会释放锁）
#开启MySQL数据库手动提交
SET autocommit=0;
-- 查看状态，设置为0则是OFF，设置为1则是ON
SHOW VARIABLES LIKE 'autocommit';
-- 如果再变成1，那么操作后就会自动提交
执行不同会话修改操作，窗口1读，窗口2 写
窗口1 进行，对id为1的数据进行更新操作，但是不进行commit
执行之后，在当前窗口查看表数据，发现被修改了
update innodb_lock set name = 'aaa' where id=1;
select * from innodb_lock;
select语句加排他锁方式：select * from table_name where … for update
for update 的作用：
for update 是在数据库中上锁用的，可以为数据库中的行上一个排他锁
for update 的应用场景：
存在高并发并且对于数据的准确性很有要求的场景，是需要了解和使用for update的
for update 的注意点：
for update 仅适用于InnoDB，并且必须开启事务，在begin与commit之间才生效
当然如果设置了不自动提交也行，因为他们执行时，一般也是默认开启事务的
在窗口1中, 首先开启事务，然后对 id为1 的数据进行排他查询
select * from innodb_lock where id = 1 for update;
-- 如果设置了不自动提交，那么也认为是在开启事务的，没提交，虽然查询并没有说是自动提交的
-- 但实际上也可以认为，也是事务操作自动提交
在窗口2中，对同一数据分别使用 排他锁 和 共享锁 两种方式查询
-- 排他锁查询
select * from innodb_lock where id = 1 for update;
-- 共享锁查询
select * from innodb_lock where id = 1 lock in share mode;
我们看到开了排他锁查询和共享锁查询都会处于阻塞状态，因为id=1的数据已经被加上了排他 锁
此处阻塞是等待排他锁释放
如果只是使用普通查询，我们发现是可以的
select * from innodb_lock where id = 1;
-- 也就是说，对应的锁不针对普通查询，本质上只是没有锁的冲突而已，因为没有使用锁
-- 由此可知，实际上他们只是操作锁的冲突，所以，只要你的锁没有冲突，自然可以查询
-- 即而前面说的行锁的共享和排他，都只是对锁来实现了
查询时的共享锁测试：
添加共享锁：select * from table_name where … lock in share mode
事务获取了共享锁，在其他查询中也只能加共享锁，但是不能加排它锁
窗口1 开启事务，使用共享锁查询 id = 2 的数据，但是不要提交事务
select * from innodb_lock where id = 2 lock in share mode;
窗口2 开启事务，使用普通查询和共享锁查询 id = 2 的数据，是可以的
select * from innodb_lock where id = 2 lock in share mode;
select * from innodb_lock where id = 2;
加排他锁就查不到，因为排他锁与共享锁不能存在同一数据上：
select * from innodb_lock where id = 2 for update; -- 阻塞，当然阻塞也可以使用UNLOCK TABLES来解决
1193
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
