MySQL（下）

最新推荐文章于 2024-09-27 20:43:28 发布

Int mian[]

最新推荐文章于 2024-09-27 20:43:28 发布

阅读量103

点赞数

分类专栏：大数据文章标签： mysql 数据库

本文链接：https://blog.csdn.net/qq_58551342/article/details/132526385

版权

大数据专栏收录该内容

13 篇文章 0 订阅

订阅专栏

SQL优化（花里胡哨，就是推荐的语句）

插入数据

普通插入

采用批量插入（一次插入的数据不建议超过1000条）
手动提交事务
主键顺序插入->顺序插入，性能要高于乱序插入。

load大批量插入

如果一次性需要插入大批量数据，使用insert语句插入性能较低，此时可以使用MySQL数据库提供的load指令插入。

# 客户端连接服务端时，加上参数 --local-infile（这一行在bash/cmd界面输入）
mysql --local-infile -u root -p
# 设置全局参数local_infile为1，开启从本地加载文件导入数据的开关
set global local_infile = 1;
select @@local_infile;
# 执行load指令将准备好的数据，加载到表结构中
load data local infile '/root/sql1.log' into table 'tb_user' 
    fields terminated by ',' lines terminated by '\n';

主键优化

数据组织方式：在InnoDB存储引擎中，表数据都是根据主键顺序组织存放的，这种存储方式的表称为索引组织表（Index organized table, IOT）

行数据，都是存储在聚集索引的叶子节点上的。

在InnoDB引擎中，数据行是记录在逻辑结构 page 页中的，而每一个页的大小是固定的，默认16K。那也就意味着，一个页中所存储的行也是有限的，如果插入的数据行row在该页存储不小，将会存储到下一个页中，页与页之间会通过指针连接。

乱序插入时的页分裂/叶合并

抽象，看视频进阶-SQL优化-主键优化_哔哩哔哩

主键设计原则

满足业务需求的情况下，尽量降低主键的长度（二级索引节约空间）
插入数据时，尽量选择顺序插入，选择使用 AUTO_INCREMENT 自增主键
尽量不要使用 UUID（随机字符串）做主键或者是其他的自然主键，如身份证号
业务操作时，避免对主键的修改

order by优化

Using filesort：通过表的索引或全表扫描，读取满足条件的数据行，然后在排序缓冲区 sort buffer 中完成排序操作，所有不是通过索引直接返回排序结果的排序都叫 FileSort 排序
Using index：通过有序索引顺序扫描直接返回有序数据，这种情况即为 using index，不需要额外排序，操作效率高

如果order by字段全部使用升序排序或者降序排序，则都会走索引，但是如果一个字段升序排序，另一个字段降序排序，则不会走索引，explain的extra信息显示的是Using index, Using filesort，如果要优化掉Using filesort，则需要另外再创建一个索引，如：

create index idx_user_age_phone_ad on tb_user(age asc, phone desc);

此时使用select id, age, phone from tb_user order by age asc, phone desc;

会全部走索引

总结：

根据排序字段建立合适的索引，多字段排序时，也遵循最左前缀法则
尽量使用覆盖索引
多字段排序，一个升序一个降序，此时需要注意联合索引在创建时的规则（ASC/DESC）
如果不可避免出现filesort，大数据量排序时，可以适当增大排序缓冲区大小 sort_buffer_size（默认256k）

group by优化

所以，在分组操作中，我们需要通过以下两点进行优化，以提升性能：

A. 在分组操作时，可以通过索引来提高效率。

B. 分组操作时，索引的使用也是满足最左前缀法则的。

limit优化

常见的问题如limit 2000000, 10，此时需要 MySQL 排序前2000000条记录，但仅仅返回2000000 - 2000010的记录，其他记录丢弃，查询排序的代价非常大。
优化方案：一般分页查询时，通过创建覆盖索引能够比较好地提高性能，可以通过覆盖索引加子查询形式进行优化

-- 此语句耗时很长
select * from tb_sku limit 9000000, 10;
-- 通过覆盖索引加快速度，直接通过主键索引进行排序及查询
select id from tb_sku order by id limit 9000000, 10;
-- 下面的语句是错误的，因为 MySQL 不支持 in 里面使用 limit
-- select * from tb_sku where id in (select id from tb_sku order by id limit 9000000, 10);
-- 通过连表查询即可实现第一句的效果，并且能达到第二句的速度
select * from tb_sku as s, (select id from tb_sku order by id limit 9000000, 10) as a where s.id = a.id;

count优化

MyISAM 引擎把一个表的总行数存在了磁盘上，因此执行 count(*) 的时候会直接返回这个数，效率很高（前提是不适用where）；
InnoDB 在执行 count(*) 时，需要把数据一行一行地从引擎里面读出来，然后累计计数。
优化方案：自己计数，如创建key-value表存储在内存或硬盘，或者是用redis

count的几种用法：

如果count函数的参数（count里面写的那个字段）不是NULL（字段值不为NULL），累计值就加一，最后返回累计值
用法：count(*)、count(主键)、count(字段)、count(1)
count(主键)跟count(*)一样，因为主键不能为空；count(字段)只计算字段值不为NULL的行；count(1)引擎会为每行添加一个1，然后就count这个1，返回结果也跟count(*)一样；count(null)返回0

各种用法的性能：

count(主键)：InnoDB引擎会遍历整张表，把每行的主键id值都取出来，返回给服务层，服务层拿到主键后，直接按行进行累加（主键不可能为空）
count(字段)：没有not null约束的话，InnoDB引擎会遍历整张表把每一行的字段值都取出来，返回给服务层，服务层判断是否为null，不为null，计数累加；有not null约束的话，InnoDB引擎会遍历整张表把每一行的字段值都取出来，返回给服务层，直接按行进行累加
count(1)：InnoDB 引擎遍历整张表，但不取值。服务层对于返回的每一层，放一个数字 1 进去，直接按行进行累加
count(*)：InnoDB 引擎并不会把全部字段取出来，而是专门做了优化，不取值，服务层直接按行进行累加

按效率排序：count(字段) < count(主键) < count(1) < count(*)，所以尽量使用 count(*)

update优化（避免行锁升级为表锁）

InnoDB 的行锁是针对索引加的锁，不是针对记录加的锁，并且该索引不能失效，否则会从行锁升级为表锁。

如以下两条语句：

update student set no = '123' where id = 1;，这句由于id有主键索引，所以只会锁这一行；别的事务还可以修改这个表
update student set no = '123' where name = 'test';，这句由于name没有索引，所以会把整张表都锁住进行数据更新，解决方法是给name字段添加索引

视图

视图（View）是一种虚拟存在的表。视图中的数据并不在数据库中实际存在，行和列数据来自定义视图的查询中使用的表，并且是在使用视图时动态生成的。通俗的讲，视图只保存了查询的SQL逻辑，不保存查询结果。所以我们在创建视图的时候，主要的工作就落在创建这条SQL查询语句上。

创建

CREATE [ OR REPLACE ] VIEW 视图名称[（列名列表）] 
    AS SELECT 语句 [ WITH [ CASCADED | LOCAL ] CHECK OPTION ] 

create or replace view stu_wll as select id,name from student where id<=10;

查询视图

查看创建视图语句： SHOW CREATE VIEW视图名称；

查看视图数据：SELECT*FROM 视图名称； 
show create view stu_v_1;

修改视图

方式一：CREATE[OR REPLACE] VIEW 视图名称[（列名列表)）] AS SELECT 语句[ WITH[ CASCADED | LOCAL ] CHECK OPTION ]

方式二：ALTER VIEW 视图名称 [（列名列表)] AS SELECT语句 [WITH [CASCADED | LOCAL] CHECK OPTION]

删除视图

DROP VIEW [IF EXISTS] 视图名称 [视图名称]

视图检查选项

当使用WITH CHECK QPTION子句创建视图时，MySQL会通过视图检查正在更改的每个行，例如插入，更新，删除，以使其符合视图的定义。

MySQL允许基于另一个视图创建视图，它还会检查依赖视图中的规则以保持一致性。为了确定检查的范围，mysql提供了两个选项：CASCADED 和 LOCAL ，默认值为 CASCADED。

NOTE：如果没有开检查选项就不会进行检查。不同版本是不同含义的，要看版本。

CASCADED

级联，一旦选择了这个选项，除了会检查创建视图时候的条件，还会检查所依赖视图的条件。

比如下面的例子：创建stu_V_l 视图，id是小于等于 20的。

create or replace view stu_V_l as select id,name from student where id <=20;

再创建 stu_v_2 视图，20 >= id >=10。

create or replace view stu_v_2 as select id,name from stu_v_1 where id >=10 with cascaded check option;

再创建 stu_v_3 视图。

create or replace view stu_v_3 as select id,name from stu_v_2 where id<=15;

insert into stu_v_3 values(17,'Tom');

这条数据能够成功，stu_v_3 没有开检查选项所以不会去判断 id 是否小于等于15, 直接级联到检查是否满足 stu_v_2。

比如，v2视图是基于v1视图的，如果在v2视图创建的时候指定了检查选项为 cascaded，但是v1视图创建时未指定检查选项。则在执行检查时，不仅会检查v2，还会级联检查v2的关联视图v1。

LOCAL

本地的条件也会检查，还会向上检查。在向上找的时候，就要看是否上面开了检查选项，如果没开就不检查。和 CASCADED 的区别就是 CASCADED 不管上面开没开检查选项都会进行检查。

比如，v2视图是基于v1视图的，如果在v2视图创建的时候指定了检查选项为 local ，但是v1视图创建时未指定检查选项。则在执行检查时，知会检查v2，不会检查v2的关联视图v1。

视图UPDATE

要使视图可更新，视图中的行与基础表中的行之间必须存在一对一的关系。如果视图包含以下任何一项，则该视图不可更新

聚合函数或窗口函数 ( SUM()、MIN()、MAX()、COUNT() 等 )
DISTINCT
GROUP BY
HAVING
UNION 或者UNION ALL

例子：使用了聚合函数，插入会失败。 create view stu_v_count as select count(*) from student; insert into stu_v_count values(10);

作用

视图不仅可以简化用户对数据的理解，也可以简化他们的操作。那些被经常使用的查询可以被定义为视图，从而使得用户不必为以后的操作每次指定全部的条件。

安全数据库可以授权，但不能授权到数据库特定行和特定的列上。通过视图用户只能查询和修改他们所能见到的数据数据独立视图可帮助用户屏蔽真实表结构变化带来的影响。

总而言之类似于给表加上了一个外壳，通过这个外壳访问表的时候，只能按照所设计的方式进行访问与更新。

存储过程（几个语句集合）

存储过程是事先经过编译并存储在数据库中的一段SQL 语句的集合，调用存储过程可以简化应用开发人员的很多工作，减少数据在数据库和应用服务器之间的传输，对于提高数据处理的效率是有好处的。存储过程思想上很简单，就是数据库SQL 语言层面的代码封装与重用。

语句

创建

CREATE PROCEDURE 存储过程名称( [参数列表] ) 

BEGIN

	 SQL 语句 

END;


# 调用
CALL 名称 ([ 参数 ]);

NOTE: 在命令行中，执行创建存储过程的SQL时，需要通过关键字delimiter 指定SQL语句的结束符。默认是分号作为结束符。 # delimiter $ ，则 $ 符作为结束符

查看

查询指定数据库的存储过程及状态信息

SELECT* FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = 'xxx'

存储过程名称；--查询某个存储过程的定义

SHOW CREATE PROCEDURE 存储过程名称

删除

DROP PROCEDURE [ IFEXISTS ] 存储过程名称

示例

-- 存储过程基本语法
-- 创建
create procedure p1()
begin
select count(*) from student;
end;
-- 调用
call p1();
-- 查看
select * from information_schema.ROUTINES where ROUTINE_SCHEMA = 'itcast';
show create procedure p1;
-- 删除
drop procedure if exists p1;

if

IF 条件1 THEN
.....
ELSEIF 条件2 THEN -- 可选
.....
ELSE -- 可选
.....
END IF;

参数传递

CREATE PROCEDURE 存储过程名称 ([ IN/OUT/INOUT 参数名 参数类型 ])
BEGIN
-- SQL语句
END ;

案例

根据传入参数score，判定当前分数对应的分数等级，并返回。

score >= 85分，等级为优秀。

score >= 60分且 score < 85分，等级为及格。

score < 60分，等级为不及格

create procedure p4(in score int, out result varchar(10))
begin
    if score >= 85 then
        set result := '优秀';
    elseif score >= 60 then
        set result := '及格';
    else
        set result := '不及格';
    end if;
end;

-- 定义用户变量 @result来接收返回的数据, 用户变量可以不用声明
call p4(18, @result);
select @result;

游标

游标（CURSOR）是用来存储查询结果集的数据类型，在存储过程和函数中可以使用游标对结果集进行循环的处理。游标的使用包括游标的声明、OPEN、FETCH和CLOSE，其语法分别如下。

声明游标： DECLARE 游标名称 CURSOR FOR 查询语句

打开游标： OPEN 游标名称

获取游标记录： FETCH 游标名称INTO变量[变量]

案例

create procedure p11(in uage int)
    begin
        declare uname varchar(100);
        declare upro varchar(100);

        -- A. 声明游标, 存储查询结果集
        declare u_cursor cursor for 
            select name,profession from tb_user where age <=uage;

        -- B. 准备: 创建表结构
        drop table if exists tb_user_pro;
        create table if not exists tb_user_pro(
            id int primary key auto_increment,
            name varchar(100),
            profession varchar(100)
        );

        -- D. 获取游标中的记录
        open u_cursor;
        while true do
            fetch u_cursor into uname,upro;
            insert into tb_user_pro values (null, uname, upro);
        end while;

        close u_cursor;
    end;
call p11(30);

条件处理程序

条件处理程序（Handler）可以用来定义在流程控制结构执行过程中遇到问题时相应的处理步骤。具体语法为：

DECLARE 'handler action' HANDLER FOR 'condition' 'value' [,condition value]..statement

-- 声明条件处理程序 ： 当SQL语句执行抛出的状态码为02000时，将关闭游标u_cursor，并退出
declare exit handler for SQLSTATE '02000' close u_cursor;

handler_action CONTINUE：继续执行当前程序

EXIT：终止执行当前程序

condition_value :

SQLSTATE sqlstate_value：状态码，如02000

SQLWARNING：所有以01开头的SQLSTATE代码的简写

NOT FOUND：所有以02开头的SQLSTATE代码的简写

SQLEXCEPTION：所有没有被SQLWARNING或NOT FOUND捕获的SQLSTATE代码的简写

触发器！！！

触发器是与表有关的数据库对象，指在insert/update/delete之前或之后，触发并执行触发器中定义的SQL语句集合。触发器的这种特性可以协助应用在数据库端确保数据的完整性，日志记录，数据校验等操作。

使用别名OLD和NEW来引用触发器中发生变化的记录内容，这与其他的数据库是相似的。现在触发器还只支持行级触发（比如说一条语句影响了 5 行则会被触发 5 次），不支持语句级触发（比如说一条语句影响了 5 行则会被触发 1 次）。

触发器类型	NEW 和 OLD
INSERT	NEW 表示将要或者已经新增的数据
UPDATE	OLD表示修改之前的数据，NEW表示将要或已经修改后的数据
DELETE	OLD表示将要或者已经删除的数据

语句

CREATE TRIGGER trigger_name
    BEFORE/AFTER INSERT/UPDATE/DELETE
    ON tbl_name FOR EACH ROW          -- 行级触发器
BEGIN
    trigger_stmt ;
END;

SHOW TRIGGERS ;

DROP TRIGGER [schema_name.]trigger_name ; -- 如果没有指定 schema_name，
                                                默认为当前数据库 。

锁

锁是计算机协调多个进程或线程并发访问某一资源的机制。在数据库中，除传统的计算资源（CPU、RAM、I/O）的争用以外，数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。

NOTE : 针对事物才有加锁的意义。

MySQL中的锁，按照锁的粒度分，分为以下三类：

全局锁：锁定数据库中的所有表。
表级锁：每次操作锁住整张表。
行级锁：每次操作锁住对应的行数据。

全局锁：

全局锁就是对整个数据库实例加锁，加锁后整个实例就处于只读状态，后续的DML的写语句，DDL语句，已经更新操作的事务提交语句都将被阻塞。其典型的使用场景是做全库的逻辑备份，对所有的表进行锁定，从而获取一致性视图，保证数据的完整性。

表级锁：

表级锁，每次操作锁住整张表。锁定粒度大，发生锁冲突的概率最高，并发度最低。应用在MyISAM、InnoDB、BDB等存储引擎中。

对于表级锁，主要分为以下三类：

表锁：对于表锁，分为两类：1.表共享读锁（read lock）所有的事物都只能读（当前加锁的客户端也只能读，不能写），不能写 2.表独占写锁（write lock），对当前加锁的客户端，可读可写，对于其他的客户端，不可读也不可写。读锁不会阻塞其他客户端的读，但是会阻塞写。写锁既会阻塞其他客户端的读，又会阻塞其他客户端的写。

加上读锁之后，谁tm都别想操作了，只能读！！！

加上写锁之后，谁都别想读写一下，只有我能读写！！！

元数据锁（meta data lock，MDL），MDL加锁过程是系统自动控制，无需显式使用，在访问一张表的时候会自动加上。MDL锁主要作用是维护表元数据的数据一致性，在表上有活动事务的时候，不可以对元数据进行写入操作。在MySQL5.5中引入了MDL，当对一张表进行增删改查的时候，加MDL读锁（共享）;当对表结构进行变更操作的时候，加MDL写锁（排他）。

意向锁: 为了避免DML在执行时，加的行锁与表锁的冲突，在InnoDB中引入了意向锁，使得表锁不用检查每行数据是否加锁，使用意向锁来减少表锁的检查。一个客户端对某一行加上了行锁，那么系统也会对其加上一个意向锁，当别的客户端来想要对其加上表锁时，便会检查意向锁是否兼容，若是不兼容，便会阻塞直到意向锁释放。

意向锁兼容性：

意向共享锁（IS）：与表锁共享锁（read）兼容，与表锁排它锁（write）互斥。
意向排他锁（lX）：与表锁共享锁（read）及排它锁（write）都互斥。意向锁之间不会互斥。

行锁：

行级锁，每次操作锁住对应的行数据。锁定粒度最小，发生锁冲突的概率最低，并发度最高。应用在InnoDB存储引擎中。 InnoDB的数据是基于索引组织的，行锁是通过对索引上的索引项加锁来实现的，而不是对记录加的锁。对于行级锁，主要分为以下三类：

行锁（Record Lock）：锁定单个行记录的锁，防止其他事务对此行进行update和delete。在RC（read commit ）、RR（repeat read）隔离级别下都支持。
间隙锁（GapLock）：锁定索引记录间隙（不含该记录），确保索引记录间隙不变，防止其他事务在这个间隙进行insert，产生幻读。在RR隔离级别下都支持。比如说两个临近叶子节点为 15 23，那么间隙就是指 [15 , 23],锁的是这个间隙。
临键锁（Next-Key Lock）：行锁和间隙锁组合，同时锁住数据，并锁住数据前面的间隙Gap。在RR隔离级别下支持。

InnoDB实现了以下两种类型的行锁：

共享锁（S）：允许一个事务去读一行，阻止其他事务获得相同数据集的排它锁。
排他锁（X）：允许获取排他锁的事务更新数据，阻止其他事务获得相同数据集的共享锁和排他锁。

SQL	行锁类型	说明
insert	排他锁	自动加锁
update	排他锁	自动加锁
delete	排他锁	自动加锁
select	不加任何锁
select　lock　in　share mode	排他锁	需要手动在SELECT之后加LOCK IN SHARE MODE
select　for　update	排他锁	需要手动在SELECT之后加FOR UPDATE

行锁 - 演示

InnoDB 引擎

逻辑存储结构

表空间（ibd文件），一个mysql实例可以对应多个表空间，用于存储记录、索引等数据。

段，分为数据段（Leaf node segment）、索引段（Non-leaf node segment）、回滚段（Rollback segment），InnoDB是索引组织表，数据段就是B+树的叶子节点，索引段即为B+树的非叶子节点。段用来管理多个Extent（区）。

区，表空间的单元结构，每个区的大小为1M。默认情况下，InnoDB存储引擎页大小为16K，即一个区中一共有64个连续的页。

页，是InnoDB存储引擎磁盘管理的最小单元，每个页的大小默认为16KB。为了保证页的连续性，InnoDB存储引擎每从磁盘申请4-5个区。一页包含若干行。

行，InnoDB存储引擎数据是按进行存放的。

架构

当业务操作的时候直接操作的是内存缓冲区，如果缓冲区当中没有数据，则会从磁盘中加载到缓冲区，增删改查都是在缓冲区的，后台线程以一定的速率刷新到磁盘。

背吧：

InnoDB引擎-架构-内存结构1_哔哩哔哩_bilibili

事务原理

事务是一组操作的集合，它是一个不可分割的工作单位，事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时败。具有ACID四大特征。

原子性，一致性，持久性这三大特性由 redo log 和 undo log 日志来保证的。

隔离性 是由锁机制和MVCC保证的。

redo log

重做日志，记录的是事务提交时数据页的物理修改，是用来实现事务的持久性。

该日志文件由两部分组成：重做日志缓冲（redo log buffer）以及重做日志文件（redo log file），前者是在内存中，后者在磁盘中。当事务提交之后会把所有修改信息都存到该日志文件中，用于在刷新脏页到磁盘，发生错误时，进行数据恢复使用。

undo log

回滚日志，用于记录数据被修改前的信息，作用包含两个：提供回滚和MVCC（多版本并发控制）。 undo log和redo log记录物理日志不一样，它是逻辑日志。可以认为当delete一条记录，undo log中会记录一条对应的insert记录，反之亦然，当update一条记录时，它记录一条对应相反的update记录。当执行rollback时，就可以从undo log中的逻辑记录读取到相应的内容并进行回滚。

MVCC

当前读:

读取的是记录的最新版本，读取时还要保证其他并发事务不能修改当前记录，会对读取的记录进行加锁。对于我们日常的操作，如：

select...lock in share mode（共享锁）。
select..…for update、update、insert、delete（排他锁）都是一种当前读。

快照读:

简单的select（不加锁）就是快照读，快照读，读取的是记录数据的可见版本，有可能是历史数据，不加锁，是非阻塞读。

Read Committed：每次select，都生成一个快照读。
Repeatable Read：开启事务后第一个select语句才是快照读的地方。
Serializable：快照读会退化为当前读。

MVCC全称Multi-Version Concurrency Control，多版本并发控制。指维护一个数据的多个版本，使得读写操作没有冲突，快照读为MySQL实现MVCC提供了一个非阻塞读功能。MVCC的具体实现，还需要依赖于数据库记录中的三个隐式字段、undo log日志、readView。

原理

看不懂看不懂看不懂看不懂看不懂看不懂看不懂看不懂看不懂看不懂看不懂看不懂看不懂看不懂看不懂看不懂看不懂看不懂看不懂看不懂看不懂看不懂看不懂看不懂看不懂看不懂看不懂看不懂看不懂看不懂看不懂看不懂看不懂看不懂看不懂看不懂看不懂看不懂看不懂看不懂看不懂看不懂看不懂看不懂看不懂看不懂看不懂看不懂看不懂看不懂看不懂看不懂看不懂看不懂看不懂看不懂看不懂看不懂看不懂看不懂看不懂看不懂看不懂看不懂看不懂看不懂看不懂看不懂看不懂看不懂看不懂看不懂看不懂看不懂看不懂看不懂看不懂看不懂看不懂看不懂看不懂看不懂看不懂看不懂看不懂看不懂看不懂看不懂看不懂看不懂看不懂看不懂看不懂看不懂看不懂看不懂看不懂看不懂看不懂看不懂看不懂看不懂看不懂看不懂看不懂看不懂看不懂看不懂看不懂看不懂看不懂看不懂看不懂看不懂看不懂看不懂看不懂看不懂看不懂看不懂看不懂看不懂看不懂看不懂看不懂看不懂看不懂看不懂看不懂看不懂看不懂看不懂看不懂看不懂看不懂看不懂看不懂看不懂看不懂看不懂看不懂看不懂看不懂看不懂看不懂看不懂看不懂看不懂看不懂看不懂看不懂看不懂看不懂看不懂看不懂看不懂看不懂看不懂看不懂看不懂看不懂看不懂看不懂看不懂看不懂看不懂看不懂看不懂看不懂看不懂看不懂看不懂看不懂看不懂看不懂看不懂看不懂看不懂看不懂看不懂看不懂看不懂看不懂看不懂看不懂看不懂看不懂看不懂看不懂

系统变量、用户定义变量、局部变量

系统变量

系统变量是MySQL服务器提供，不是用户定义的，属于服务器层面。

分为全局变量（GLOBAL）、会话变量（SESSION）。

查看系统变量

SHOW [ SESSION | GLOBAL ] VARIABLES ; -- 查看所有系统变量
SHOW [ SESSION | GLOBAL ] VARIABLES LIKE '......'; -- 可以通过LIKE模糊匹配方式查找变量
SELECT @@[SESSION | GLOBAL] 系统变量名; -- 查看指定变量的值

设置系统变量

SET [ SESSION | GLOBAL ] 系统变量名 = 值 ;
SET @@[SESSION | GLOBAL]系统变量名 = 值 ;

注意: 如果没有指定SESSION/GLOBAL，默认是SESSION，会话变量。

mysql服务重新启动之后，所设置的全局参数会失效，要想不失效，可以在 /etc/my.cnf 中配置。

A. 全局变量(GLOBAL): 全局变量针对于所有的会话。

B. 会话变量(SESSION): 会话变量针对于单个会话，在另外一个会话窗口就不生效了。

用户定义变量

用户定义变量是用户根据需要自己定义的变量，用户变量不用提前声明，在用的时候直接用 "@变量名" 使用就可以。其作用域为当前连接。

SET @var_name = expr [, @var_name = expr] ... ;
SET @var_name := expr [, @var_name := expr] ... ;

SELECT @var_name := expr [, @var_name := expr] ... ;
SELECT 字段名 INTO @var_name FROM 表名;

SELECT @var_name ;
 用户定义的变量无需对其进行声明或初始化，只不过获取到的值为NULL。

-- 赋值
set @myname = 'itcast';
set @myage := 10;
set @mygender := '男',@myhobby := 'java';
select @mycolor := 'red';
select count(*) into @mycount from tb_user;
-- 使用
select @myname,@myage,@mygender,@myhobby;
select @mycolor , @mycount;

局部变量

局部变量是根据需要定义的在局部生效的变量，访问之前，需要DECLARE声明。可用作存储过程内的局部变量和输入参数，局部变量的范围是在其内声明的BEGIN ... END块

DECLARE 变量名 变量类型 [DEFAULT ... ] ;
INT、BIGINT、CHAR、VARCHAR、DATE、TIME等。

SET 变量名 = 值 ;
SET 变量名 := 值 ;
SELECT 字段名 INTO 变量名 FROM 表名 ... ;

-- 声明局部变量 - declare
-- 赋值
create procedure p2()
begin
declare stu_count int default 0;
select count(*) into stu_count from student;
select stu_count;
end;
call p2();