mysql查询、存储过程、赋值、索引

基本查询

查看正在进行的操作

show processlist

MySQL出现Waiting for table metadata lock的原因：

场景一：长事物运行，阻塞DDL，继而阻塞所有同表的后续操作

​ 通过show processlist可以看到TableA上有正在进行的操作（包括读），此时alter table语句无法获取到metadata 独占锁，会进行等待。在after create步骤会获取metadata 独占锁，当进行到altering table的过程时（通常是最花时间的步骤），对该表的读写都可以正常进行，这就是online ddl的表现，并不会像之前在整个alter table过程中阻塞写入。（当然，也并不是所有类型的alter操作都能online的，具体可以参见官方手册：http://dev.mysql.com/doc/refman/5.6/en/innodb-create-index-overview.html）

处理方法： kill 掉 DDL所在的session.

场景二：未提交事物，阻塞DDL，继而阻塞所有同表的后续操作

​ 通过show processlist看不到TableA上有任何操作，但实际上存在有未提交的事务，可以在 information_schema.innodb_trx中查看到。在事务没有完成之前，TableA上的锁不会释放，alter table同样获取不到metadata的独占锁。

**处理方法：**通过 select * from information_schema.innodb_trx, 找到未提交事物的sid, 然后 kill 掉，让其回滚。

场景三：

​ 通过show processlist看不到TableA上有任何操作，在information_schema.innodb_trx中也没有任何进行中的事务。这很可能是因为在一个显式的事务中，对TableA进行了一个失败的操作（比如查询了一个不存在的字段），这时事务没有开始，但是失败语句获取到的锁依然有效，没有释放。从performance_schema.events_statements_current表中可以查到失败的语句。
也就是说除了语法错误，其他错误语句获取到的锁在这个事务提交或回滚之前，仍然不会释放掉。because the failed statement is written to the binary log and the locks protect log consistency 但是解释这一行为的原因很难理解，因为错误的语句根本不会被记录到二进制日志。

**处理方法：**通过performance_schema.events_statements_current找到其sid, kill 掉该session. 也可以 kill 掉DDL所在的session.

存储过程

python执行存储过程

import pymysql
conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='xx', db='test_python', charset='utf8')
cursor=conn.cursor(cursor=pymysql.cursors.DictCursor)
# 执行存储过程
# @_p3_0 = 1， 相当于Python帮我们创建了变量,所以返回值也是放在了这个变量里面，需要我们手动获取
# @_p3_1 = 2
# @_p3_2 = 3
# @_p3_3 = 0 (out)
ret1 =cursor.callproc('p3', (1, 2, 3, 0))#p3为存储过程名，（）后卫in和out的数值
# print('ret1:', ret1)  # 传递参数过去 ret1: (1, 2, 3, 0)，同时执行了存储过程将结果放在内存
result =cursor.fetchall() # 从内存获取返回值，[{'@_p3_0': 1, '@_p3_3': 7, '@_p3_1': 2, '@_p3_2': 103}]
print('返回的参数：', result, '\n')
# 获取执行完存储的参数，因为Python帮我们将结果放在了变量里，所以需要从变量里面获取
ret2 =cursor.execute("select @_p3_0,@_p3_1,@_p3_2,@_p3_3")
# print('ret2:', ret2)    # 什么都不执行： ret2: 1
result2 =cursor.fetchall()   # 获取返回值，[{'@_p3_0': 1, '@_p3_3': 7, '@_p3_1': 2, '@_p3_2': 103}]
result2[0]
print('返回的结果集：', result2)
conn.commit()  # 因为存储过程里面有多个sql语句，可能有selete,insert等语句，所以为了保证代码的完整
cursor.close()
conn.close()

mysql存储过程给变量赋值

小结：

mysql存储过程变量可以先定义，也可以过程中@变量动态定义

mysql存储过程游标必须定义在存储过程最前端

BEGIN
    DECLARE v_request_count INT;    #申请次数
    DECLARE v_plan_count         INT;    #安排次数
    DECLARE v_learn_count     INT;    #学习次数
    DECLARE v_learned_count INT;    #已经学习次数
    DECLARE v_pass_count         INT;    #通过次数
 
    DECLARE v_cid  INT;
    DECLARE v_course_id         INT;
 
    DECLARE stop INT DEFAULT 0; /* 终止标记 */
    DECLARE cur CURSOR FOR (SELECT cid, course_id FROM lms_company_course);
 
    DECLARE CONTINUE HANDLER FOR SQLSTATE '' SET stop = 1;
 
    OPEN cur;
    FETCH cur INTO v_cid, v_course_id;
    WHILE stop <> 1 DO
 
            #申请次数
            SELECT @v_request_count:=COUNT(*) FROM lms_course_request WHERE cid=v_cid AND course_id=v_course_id;
 
            #安排次数
            SELECT COUNT(*) INTO @v_plan_count FROM lms_student_course_relation WHERE cid=v_cid AND course_id=v_course_id AND type=2;
 
            #学习次数（这个方法本地项目中使用）
            SELECT COUNT(*) INTO v_learn_count FROM lms_student_course_relation WHERE cid=v_cid AND course_id=v_course_id;
            #多个赋值
            SELECT COUNT(*),SUM(qualified_rate) INTO v_learn_count,v_qualified_rate FROM lms_student_course_relation WHERE cid=v_cid AND course_id=v_course_id;
 
            #已经学习次数
            SET v_learned_count=(SELECT COUNT(*) FROM lms_student_course_relation WHERE cid=v_cid AND course_id=v_course_id AND progress=100);
 
            #企业课后测试通过率、通过次数
            SELECT @qualified_rate:=qualified_rate FROM lms_company_setting WHERE cid = v_cid;
            SET v_pass_count:=(SELECT COUNT(*) FROM lms_student_course_relation WHERE cid=v_cid AND course_id=v_course_id AND review_test_score>=@qualified_rate);
 
            #更新数据
            UPDATE lms_company_course SET request_count=@v_request_count, plan_count=@v_plan_count, learn_count=v_learn_count,
            learned_count=v_learned_count, pass_count=v_pass_count
            WHERE cid=v_cid AND course_id=v_course_id;
 
    FETCH cur INTO v_cid, v_course_id;
    END WHILE;
    CLOSE cur;
END

存储过程查看等

存储过程查看

show procedure status

如果要查看某个具体的存储过程，可以执行命令：

show create procedure name_to_grade

存储过程修改

​ 尽管MySQL数据库支持对存储过程的修改，但是我们依旧不可以修改存储过程中的内容，也不能修改存储过程的名称。如果想要修改存储过程的内容，只能删除原有的存储过程，然后再重新写一个存储过程；如果想要修改存储过程的名称，只能删除原有的存储过程，然后重新创建一个新的存储过程，并且把原有存储过程的内容写入到新的存储过程名称里面。

MySQL只支持修改存储过程的一些特性，该修改命令SQL示例如下：

alter procedure 【存储过程名】 【存储过程特性】;

可以写入的存储过程特性主要有以下6种：
1、contains sql。 表示子程序包含SQL语句，但是不包含读或者写的SQL语句。
2、no sql。 表示子程序不包含SQL语句。
3、reads sql data。 表示子程序中包含读数据的SQL语句。
4、modifies sql data。 表示子程序中包含写数据的SQL语句。
5、sql security define或sql security invoke。 如果是define，则表示该存储过程只有定义者自身才可以执行，如果是invoke，则表示调用者可以执行。
6、comment 【注释信息】。 表示向该存储过程添加注释信息。

存储过程删除

存储过程的删除可以使用以下命令： drop procedure 【存储过程名】;

drop procedure sss_ndad；

存储过程调用

1、调用无参数无返回值存储过程

call select_all_data

2、 创建存储过程将最低薪资通过OUT参数“ms”输出

调用存储过程： @表示该变量是用户自定义

call show_min_salary(@ms)

查看：

SELECT @ms;

3、 创建存储过程用IN参数empname输入员工姓名

CALL show_someone_salary(‘Abel’);

4、创建存储过程用IN参数empname输入员工姓名，用OUT参数empsalary输出员工薪资

调用存储过程：
SET @empname = ‘Abel’;
CALL show_someone_salary2(@empname,@empsalary);

SELECT @empsalary;

5、创建存储过程查询某个员工领导的姓名，并用INOUT参数“empname”输入员工姓名，输出领导的姓名

调用存储过程：
SET @empname := ‘Abel’;
CALL show_mgr_name(@empname);

SELECT @empname;

存储过程

（PROCEDURE）是事先经过编译并存储在数据库中的一段SQL语句的集合。

存储过程可以定义参数，参数分为IN、OUT、INOUT类型三种类型。

• IN类型的参数表示接受调用者传入的数据；
• OUT类型的参数表示向调用者返回数据；
• INOUT类型的参数即可以接受调用者传入的参数，也可以向调用者返回数据。

DELIMITER $$
CREATE
    PROCEDURE `demo`.`demo2`(IN s_sex CHAR(1),OUT s_count INT)
    -- 存储过程体
    BEGIN
        -- 把SQL中查询的结果通过INTO赋给变量
        SELECT COUNT(*) INTO s_count FROM student WHERE sex= s_sex;
        SELECT s_count;         
    END$$
DELIMITER ;

-- case
DELIMITER $$
CREATE
    PROCEDURE demo4(IN num INT)
    BEGIN
        CASE -- 条件开始
        WHEN num<0 THEN
            SELECT '负数';
        WHEN num>0 THEN
            SELECT '正数';
        ELSE
        SELECT '不是正数也不是负数';     
        END CASE; -- 条件结束
    END$$
DELIMITER;

-- 循环语句
DELIMITER $$
CREATE
    PROCEDURE demo6(IN num INT,OUT SUM INT)
    BEGIN
         SET SUM = 0;
         WHILE num<10 DO -- 循环开始
             SET num = num+1;
             SET SUM = SUM+num;
             END WHILE; -- 循环结束
    END$$
DELIMITER;

-- 循环语句REPEAT UNTLL
-- REPEATE…UNTLL 语句的用法和 Java中的 do…while 语句类似，都是先执行循环操作，再判断条件，区别是REPEATE 表达式值为 false时才执行循环操作，直到表达式值为 true停止。
DELIMITER $$
CREATE
    PROCEDURE demo7(IN num INT,OUT SUM INT)
    BEGIN
         SET SUM = 0;
         REPEAT-- 循环开始
        SET num = num+1;
        SET SUM = SUM+num ;
        UNTIL num>=10
        END REPEAT; -- 循环结束
    END$$
DELIMITER;

-- 一个循环语句 LOOP
-- 循环语句，用来重复执行某些语句。
-- 执行过程中可使用 LEAVE语句或者ITEREATE来跳出循环，也可以嵌套IF等判断语句。
-- LEAVE 语句效果对于Java中的break，用来终止循环；
-- ITERATE语句效果相当于Java中的continue，用来跳过此次循环。进入下一次循环。且ITERATE之下的语句将不在进行。
DELIMITER $$
CREATE
    PROCEDURE demo8(IN num INT,OUT SUM INT)
    BEGIN
         SET SUM = 0;
         demo_sum:LOOP-- 循环开始
        SET num = num+1;
        IF num > 10 THEN
            LEAVE demo_sum; -- 结束此次循环
        ELSEIF num < 9 THEN
            ITERATE demo_sum; -- 跳过此次循环
        END IF;    
        SET SUM = SUM+num;
        END LOOP demo_sum; -- 循环结束
    END$$
DELIMITER;

存储过程的源码

SHOW CREATE PROCEDURE 存储过程名;

mysql函数用法

if语句的三种用法

IF表达式

IF(expr1,expr2,expr3); 
-- 如果expr1为TRUE，则IF()返回值为expr2，否则返回值为expr3 
update salary set sex =if(sex = '男','女','男') 

IFNULL语句

IFNULL(expr1,expr2)
--假如expr1不为null，则返回expr1，否则返回expr2

IF…ELSE…语句

IF search_condition THEN
	statement_list
ELSE
	statement_list
END IF;
--search_condition表示条件，如果成立时执行THEN后面的statement_list语句，否则执行ELSE后面的statement_list语句。
--search_condition是一个条件表达式，可以由条件运算符组成，也可以使用AND、OR、NOT对多个表达式进行组合

isnull()用法

1.isnull(exper) 判断exper是否为空，是则返回1，否则返回0

2.ifnull(exper1,exper2)判断exper1是否为空，是则用exper2代替

3.nullif(exper1,exper2)如果expr1= expr2 成立，那么返回值为NULL，否则返回值为 expr1。

比如：c.SERVICE_OFFER_NAME 查出来是有数据的，但在isnull判断之后，RESULT字段就变为0了。

row_number()函数

在mysql不能使用row_number函数，在mysql 8.0以下版本不使用session变量。

mysql8.0：使用RANK

SELECT user_name,user_id
FROM   (SELECT user_name,
               user_id,
               pkval,
                 RANK()  OVER(PARTITION BY user_id ORDER BY pkval desc) rn
        FROM   usertable) t
WHERE  rn = 1

mysql5.7：只能使用MAX

select user_name, user_id
from usertable t
where pkval = (
    select max(t1.pkval) from usertable t1 where t1.user_id = t.user_id
)

赋值

同时定义多个变量

declare可用来同时定义多个同类型的变量；但是不能定义不同类型的变量：

DECLARE a,b,c,d,e INT DEFAULT 0; 

如果不同类型只能分开定义：

DECLARE age INT;
DECLARE address VARCHAR(255);

NUMERIC数据类型

numeric 和 decimal 数据类型的默认最大精度值是38，在 Transact-SQL 中，numeric 与 decimal 数据类型在功能上等效。 当数据值一定要按照指定精确存储时，可以用带有小数的 decimal 数据类型来存储数字。

decimal(m,d)
m是数字的最大位数，他的范围是从1-65；
d是小数点后的位数，他的范围是0-30，并且不能大于m。
如果m被省略了，那么m的值默认为10，
如果d被省略了，那么d的值默认为0.

变量

​ 在MySQL数据库的存储过程和函数中，可以使用变量来存储查询或计算的中间结果数据，或者输出最终
的结果数据。
  在 MySQL 数据库中，变量分为 系统变量 以及 用户自定义变量 。

系统变量

系统变量由系统定义，存储着一些对数据库操作的默认参数，定义了当前MySQL服务实例的属性、特征。比如创建数据库的默认字符集，表的默认存储引擎等。
  在MySQL中有些系统变量只能是全局的，例如 max_connections 用于限制服务器的最大连接数；有些系统变量作用域既可以是全局又可以是会话，例如 character_set_client 用于设置客户端的字符集；有些系统变量的作用域只能是当前会话，例如 pseudo_thread_id 用于标记当前会话的 MySQL 连接 ID。
  作为 MySQL 编码规范，MySQL 中的系统变量以 两个“@” 开头，其中“@@global”仅用于标记全局系统变量，“@@session”仅用于标记会话系统变量。“@@”首先标记会话系统变量，如果会话系统变量不存在，则标记全局系统变量。

全局系统变量

全局系统变量针对于所有会话（连接）有效，但不能跨重启 （MySQL8 做出了一些改变）。

会话系统变量

会话系统变量仅针对于当前会话（连接）有效。会话期间，当前会话对某个会话系统变量值的修改，不会影响其他会话同一个会话系统变量的值。
  会话1对某个全局系统变量值的修改会导致会话2中同一个全局系统变量值的修改。

查看系统变量

#查看所有全局系统变量 
show global variables;
#查看所有会话系统变量
show session variables; 
或
SHOW VARIABLES;

#查看指定的全局系统变量的值
SELECT @@global.变量名;

#查看指定的会话系统变量的值
SELECT @@session.变量名; 
#或者 
SELECT @@变量名;

修改系统变量值

#为某个全局系统变量赋值 
#方式1： 
SET @@global.变量名=变量值; 
#方式2： 
SET GLOBAL 变量名=变量值;

#为某个会话系统变量赋值
#方式1： 
SET @@session.变量名=变量值; 
#方式2： 
SET SESSION 变量名=变量值;

MySQL 8.0的新特性—全局变量的持久化

MySQL 8.0版本新增了 set persist 命令。
例如，设置服务器的最大连接数为1000：

SET PERSIST global max_connections = 1000;

用户变量

用户变量是用户自己定义的，作为 MySQL 编码规范，MySQL 中的用户变量以 一个“@” 开头。根据作用范围不同，又分为 会话用户变量 和 局部变量 。

• 会话用户变量：作用域和会话变量一样，只对 当前连接 会话有效。
• 局部变量：只在 begin和 end语句块中有效。局部变量只能在 存储过程和函数 中使用。
  通常我们使用的都是会话用户变量，如果在存储过程或存储函数中才使用局部变量

会话用户变量

定义变量

#方式1：“=”或“:=” SET 
 @用户变量 = 值; SET @用户变量 := 值;

#方式2：“:=” 或 INTO关键字
SELECT @用户变量 := 表达式 [FROM 等子句]; 
SELECT 表达式 INTO @用户变量 [FROM 等子句];

PS: 注意=和：= 的区别
  = 只有在set语句中有赋值的作用，：=只有赋值的作用。

局部变量

在存储函数、存储变量中使用！！

BEGIN
#声明局部变量 
DECLARE 变量名1 变量数据类型 [DEFAULT 变量默认值]; 
DECLARE 变量名2,变量名3,... 变量数据类型 [DEFAULT 变量默认值]; 

#为局部变量赋值 
SET 变量名1 = 值; SELECT 值 INTO 变量名2 [FROM 子句]; 

#查看局部变量的值
SELECT 变量1,变量2,变量3;

END

索引

索引结构

索引结构分为哈希和b+树

（1）HASH
　　　　用于对等比较，如"=“和” <=>"，查询单条快，范围查询慢
（2）BTREE
　　　　b树/b+树，层数越多，数据量指数级增长（mysql innodb默认）用在像 "=，>，>=，<，<=、BETWEEN、Like"等操作符查询效率较高

索引分类

按数据结构分类：B+tree索引、Hash索引 、Full-text索引
按物理存储分类：聚集索引、非聚集索引（也叫二级索引、辅助索引）
按字段特性分类：主键索引(PRIMARY KEY)、唯一索引(UNIQUE)、普通索引(INDEX)、全文索引(FULLTEXT)
按字段个数分类：单列索引、联合索引（也叫复合索引、组合索引）

但是其实联合索引、普通索引等严格说都属于非聚集索引

聚集索引

聚簇索引默认是主键，如果表中没有定义主键，InnoDB 会选择一个唯一的非空索引代替或隐式定义一个主键来作为聚簇索引(mysql内部会维护一个隐藏id去作为主键)
聚集索引(clustered index) 在查询方面,速度往往会更占优势。一个表中只能有一个，其他都是非聚簇索引

自增主键id作为聚簇会比较好，因为db的物理存储会按照索引排序，索引的键值逻辑顺序决定了表数据行的物理存储顺序。

索引中键值的逻辑顺序决定了表中相应行的物理顺序； 聚簇索引就是数据的存储方式（所有的用户记录都存储在了 叶子节点 ）

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-r9Rbzi18-1678678661594)(C:\study\pic\聚集索引.png)]

主键索引(PRIMARY KEY)

也就是聚集索引， 加速查找+约束（唯一且不为空）
一张表只能有一个主键索引（主键索引通常在建表的时候就指定，一般设置自增id作为主键）

CREATE TABLE `follow_worker_member_rel` (
	`id` int(11) NOT NULL AUTO_INCREMENT,
	`worker_id` int(11) NOT NULL,
	`member_id` int(11) NOT NULL,
	PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

非聚集索引（也叫二级索引、辅助索引）

在聚簇索引之上创建的索引称之为辅助索引，辅助索引访问数据总是需要二次查找。辅助索引叶子节点存储的不再是行的物理位置，而是主键值。通过辅助索引首先找到的是主键值，再通过主键值找到数据行的数据页，再通过数据页中的Page Directory找到数据行。

通过主键再去表查询数据页的过程叫做回表操作。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-NDTT56JR-1678678661602)(C:\study\pic\非聚集索引)]

普通索引（NORMAL）

作用很简单，就是加速查找（可不唯一可不为空）

ALTER TABLE `follow_worker_member_rel` ADD INDEX `idx_memberid` (`member_id`);

唯一索引（UNIQUE）

加速查找+约束 （唯一且可以为空）

ALTER TABLE `follow_worker_member_rel` ADD UNIQUE `idx_memberid` (`member_id`);

全文索引（FULLTEXT）

全文搜索的索引。FULLTEXT 用于搜索很长一篇文章的时候，效果最好。用在比较短的文本，如果就一两行字的，普通的 INDEX 也可以。

ALTER TABLE t_user ADD FULLTEXT u_index(USERNAME)

组合索引（复合索引）

组合索引就是把多个列加入到统一个索引

ALTER TABLE t_user ADD INDEX name_city_phone(USERNAME,CITY,PHONE)  //组合普通索引

ALTER TABLE t_user ADD UNIQUE name_city_phone(USERNAME,CITY,PHONE) //组合唯一索引

这样的组合索引，其实相当于分别建立了（USERANME,CITY,PHONE USERNAME,CITY USERNAME,PHONE）三个索引。

为什么没有（CITY,PHONE）索引呢？ 这是因为MYSQL组合查询“最左前缀”的结果。简单的理解就是只从最左边开始组合。

索引不足之处

（1）索引提高了查询的速度，但是降低了INSERT、UPDATE、DELETE的速度，因为在插入、修改、删除数据时，还要同时操作一下索引文件；

（2）建立索引未见会占用一定的磁盘空间。

索引使用注意事项

（1）只要列中包含NULL值将不会被包含在索引中，组合索引只要有一列含有NULL值，那么这一列对于组合索引就是无效的，所以我们在设计数据库的时候最好不要让字段的默认值为NULL;

（2）使用短索引

​ 如果可能应该给索引指定一个长度，例如：一个VARCHAR(255)的列，但真实储存的数据只有20位的话，在创建索引时应指定索引的长度为20，而不是默认不写。如下

使用短索引不仅能够提高查询速度，而且能节省磁盘操作以及I/O操作。

（3）索引列排序

​ Mysql在查询的时候只会使用一个索引，因此如果where子句已经使用了索引的话，那么order by中的列是不会使用索引的，所以order by尽量不要包含多个列的排序，如果非要多列排序，最好使用组合索引。

（4）Like 语句

​ 　一般情况下不是鼓励使用like,如果非使用，那么需要注意 like"%aaa%"不会使用索引；但like“aaa%”会使用索引。

（5）不使用 NOT IN和<>操作

[1] https://blog.csdn.net/m0_53824537/article/details/124997431

[2] https://blog.csdn.net/Koikoi12/article/details/123794329

[3] https://blog.csdn.net/u013050790/article/details/125975542