标签：存储 name 视图 cursor 索引 mysql id select

索引和函数视图及存储过程

1.索引

在数据库中索引最核心的作用是：加速查找

1.1 索引原理

为什么加上索引之后速度能有这么大的提升呢？因为索引的底层是基于B+Tree的数据结构存储的

很明显，如果有了索引结构的查询效率比表中逐行查询的速度要快很多且数据量越大越明显

数据库的索引是基于上述B+Tree的数据结构实现，但在创建数据库表时，如果指定不同的引擎，底层使用的B+Tree结构的原理有些不同。

myisam引擎，非聚簇索引（数据和索引结构分开存储）
innodb引擎，聚簇索引（数据和主键索引结构存储在一起）

1.1.1 非聚簇索引（mysiam引擎）

索引文件和数据文件是分开的，即索引文件不包含实际数据
每个索引文件都是一个独立的文件，包含了对应数据列的索引信息

create table 表名(
    id int not null auto_increment primary key, 
    name varchar(32) not null,
    age int
)engine=myisam default charset=utf8;

1.1.2 聚簇索引（innodb引擎）

create table 表名(
    id int not null auto_increment primary key, 
    name varchar(32) not null,
    age int
)engine=innodb default charset=utf8;

在MySQL文件存储中的体现：

root@192 userdb # pwd
/usr/local/mysql/data/userdb
root@192 userdb # ls -l
total 1412928
-rw-r-----  1 _mysql  _mysql       8684 May 15 22:51 big.frm，表结构。
-rw-r-----  1 _mysql  _mysql  717225984 May 15 22:51 big.ibd，数据和索引结构。
-rw-r-----  1 _mysql  _mysql       8588 May 16 11:38 goods.frm
-rw-r-----  1 _mysql  _mysql      98304 May 16 11:39 goods.ibd
-rw-r-----  1 _mysql  _mysql       8586 May 26 10:57 t2.frm，表结构
-rw-r-----  1 _mysql  _mysql          0 May 26 10:57 t2.MYD，数据
-rw-r-----  1 _mysql  _mysql       1024 May 26 10:57 t2.MYI，索引结构

上述聚簇索引和非聚簇索引底层均利用了B+Tree结构结构，只不过内部数据存储有些不同罢了。

在企业开发中一般都会使用 innodb 引擎（内部支持事务、行级锁、外键等特点），在MySQL5.5版本之后默认引擎也是innodb。

1.2 常见索引

常见索引：

主键索引：加速查找、不能为空、不能重复。 + 联合主键索引（约束）
唯一索引：加速查找、不能重复。 + 联合唯一索引（允许为空）
普通索引：加速查找。 + 联合索引

1.2.1 主键和联合主键索引

create table 表名(
    id int not null auto_increment primary key,   -- 主键
    name varchar(32) not null
);

create table 表名(
    id int not null auto_increment,
    name varchar(32) not null,
    primary key(id)
);

create table 表名(
    id int not null auto_increment,
    name varchar(32) not null,
    primary key(列1,列2)          -- 如果有多列，称为联合主键（不常用且myisam引擎支持）
);
alter table 表名 add primary key(列名);
alter table 表名 drop primary key;

注意：删除索引时可能会报错，自增列必须定义为键

ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key

alter table 表 change id id int not null;
create table t7(
    id int not null,
    name varchar(32) not null,
    primary key(id)
);

alter table t6 drop primary key;

1.2.2 唯一和联合唯一索引

create table 表名(
    id int not null auto_increment primary key,
    name varchar(32) not null,
    email varchar(64) not null,
    unique ix_name (name),
    unique ix_email (email),
);

create table 表名(
    id int not null auto_increment,
    name varchar(32) not null,
    unique (列1,列2)               -- 如果有多列，称为联合唯一索引。
);
create unique index 索引名 on 表名(列名);
drop unique index 索引名 on 表名;

1.2.3 索引和联合索引

create table 表名(
    id int not null auto_increment primary key,
    name varchar(32) not null,
    email varchar(64) not null,
    index ix_email (email),
    index ix_name (name),
);

create table 表名(
    id int not null auto_increment primary key,
    name varchar(32) not null,
    email varchar(64) not null,
    index ix_email (name,email)     -- 如果有多列，称为联合索引。
);
create index 索引名 on 表名(列名);
drop index 索引名 on 表名;

1.3 执行计划

MySQL中提供了执行计划，让你能够预判SQL的执行（只能给到一定的参考，不一定完全能预判准确）。

explain + SQL语句;

其中比较重要的是 type，他他SQL性能比较重要的标志，性能从低到高依次：all < index < range < index_merge < ref_or_null < ref < eq_ref < system/const

ALL，全表扫描，数据表从头到尾找一遍。(一般未命中索引，都是会执行权标扫描)

select * from big;

特别的：如果有limit，则找到之后就不在继续向下扫描.
	select * from big limit 1;

INDEX，全索引扫描，对索引从头到尾找一遍

explain select id from big;
explain select name from big;

RANGE，对索引列进行范围查找

explain select * from big where id > 10;
explain select * from big where id in (11,22,33);
explain select * from big where id between 10 and 20;
explain select * from big where name > "serein" ;

INDEX_MERGE，合并索引，使用多个单列索引搜索

explain select * from big where id = 10 or name="formerly";

REF，根据索引直接去查找（非键）。
```
select *  from big where name = 'formerly';
```

EQ_REF，连表操作时常见。

explain select big.name,users.id from big left join users on big.age = users.id;

CONST，常量，表最多有一个匹配行,因为仅有一行,在这行的列值可被优化器剩余部分认为是常数,const表很快。
```
explain select * from big where id=11;					-- 主键
explain select * from big where email="[email protected]";	-- 唯一索引
```
SYSTEM，系统，表仅有一行(=系统表)。这是const联接类型的一个特例。
```
 explain select * from (select * from big where id=1 limit 1) as A;
```

2.函数

存储过程是自定义函数，函数就类似于内置函数，mysql内置的函数只能在sql语句中使用！
部分函数列表

CHAR_LENGTH(str)
    返回值为字符串str 的长度，长度的单位为字符。一个多字节字符算作一个单字符。
    对于一个包含五个二字节字符集, LENGTH()返回值为 10, 而CHAR_LENGTH()的返回值为5。

CONCAT(str1,str2,...)
    字符串拼接
    如有任何一个参数为NULL ，则返回值为 NULL。
CONCAT_WS(separator,str1,str2,...)
    字符串拼接（自定义连接符）
    CONCAT_WS()不会忽略任何空字符串。 (然而会忽略所有的 NULL）。

CONV(N,from_base,to_base)
    进制转换
    例如：
        SELECT CONV('a',16,2); 表示将 a 由16进制转换为2进制字符串表示

FORMAT(X,D)
    将数字X 的格式写为'#,###,###.##',以四舍五入的方式保留小数点后 D 位， 并将结果以字符串的形式返回。若  D 为 0, 则返回结果不带有小数点，或不含小数部分。
    例如：
        SELECT FORMAT(12332.1,4); 结果为： '12,332.1000'
INSERT(str,pos,len,newstr)
    在str的指定位置插入字符串
        pos：要替换位置其实位置
        len：替换的长度
        newstr：新字符串
    特别的：
        如果pos超过原字符串长度，则返回原字符串
        如果len超过原字符串长度，则由新字符串完全替换
INSTR(str,substr)
    返回字符串 str 中子字符串的第一个出现位置。

LEFT(str,len)
    返回字符串str 从开始的len位置的子序列字符。

LOWER(str)
    变小写

UPPER(str)
    变大写

LTRIM(str)
    返回字符串 str ，其引导空格字符被删除。
RTRIM(str)
    返回字符串 str ，结尾空格字符被删去。
SUBSTRING(str,pos,len)
    获取字符串子序列

LOCATE(substr,str,pos)
    获取子序列索引位置

REPEAT(str,count)
    返回一个由重复的字符串str 组成的字符串，字符串str的数目等于count 。
    若 count <= 0,则返回一个空字符串。
    若str 或 count 为 NULL，则返回 NULL 。
REPLACE(str,from_str,to_str)
    返回字符串str 以及所有被字符串to_str替代的字符串from_str 。
REVERSE(str)
    返回字符串 str ，顺序和字符顺序相反。
RIGHT(str,len)
    从字符串str 开始，返回从后边开始len个字符组成的子序列

SPACE(N)
    返回一个由N空格组成的字符串。

SUBSTRING(str,pos) , SUBSTRING(str FROM pos) SUBSTRING(str,pos,len) , SUBSTRING(str FROM pos FOR len)
    不带有len 参数的格式从字符串str返回一个子字符串，起始于位置 pos。带有len参数的格式从字符串str返回一个长度同len字符相同的子字符串，起始于位置 pos。 使用 FROM的格式为标准 SQL 语法。也可能对pos使用一个负值。假若这样，则子字符串的位置起始于字符串结尾的pos 字符，而不是字符串的开头位置。在以下格式的函数中可以对pos 使用一个负值。

    mysql> SELECT SUBSTRING('Quadratically',5);
        -> 'ratically'

    mysql> SELECT SUBSTRING('foobarbar' FROM 4);
        -> 'barbar'

    mysql> SELECT SUBSTRING('Quadratically',5,6);
        -> 'ratica'

    mysql> SELECT SUBSTRING('Sakila', -3);
        -> 'ila'

    mysql> SELECT SUBSTRING('Sakila', -5, 3);
        -> 'aki'

    mysql> SELECT SUBSTRING('Sakila' FROM -4 FOR 2);
        -> 'ki'

TRIM([{BOTH | LEADING | TRAILING} [remstr] FROM] str) TRIM(remstr FROM] str)
    返回字符串 str ， 其中所有remstr 前缀和/或后缀都已被删除。若分类符BOTH、LEADIN或TRAILING中没有一个是给定的,则假设为BOTH 。 remstr 为可选项，在未指定情况下，可删除空格。

    mysql> SELECT TRIM('  bar   ');
            -> 'bar'

    mysql> SELECT TRIM(LEADING 'x' FROM 'xxxbarxxx');
            -> 'barxxx'

    mysql> SELECT TRIM(BOTH 'x' FROM 'xxxbarxxx');
            -> 'bar'

    mysql> SELECT TRIM(TRAILING 'xyz' FROM 'barxxyz');
            -> 'barx'

3. 存储过程

存储过程，是一个存储在MySQL中的SQL语句集合，当主动去调用存储过程时，其中内部的SQL语句会按照逻辑执行。

创建存储过程

delimiter $$
create procedure p1()
BEGIN
    select * from d1;
END $$
delimiter ;

执行存储过程

call p1();

#!/usr/bin/env python
# -*- coding:utf-8 -*-
import pymysql

conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='root123', db='userdb')
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
# 执行存储过程
cursor.callproc('p1')
result = cursor.fetchall()

cursor.close()
conn.close()

print(result)

删除存储过程
```
drop procedure proc_name;
```

3.1 参数类型

存储过程的参数可以有如下三种：

in，仅用于传入参数用
out，仅用于返回值用
inout，既可以传入又可以当作返回值

delimiter $$
create procedure p2(
    in i1 int,
    in i2 int,
    inout i3 int,
    out r1 int
)
BEGIN
    DECLARE temp1 int;
    DECLARE temp2 int default 0;
    
    set temp1 = 1;

    set r1 = i1 + i2 + temp1 + temp2;
    
    set i3 = i3 + 100;

end $$
delimiter ;
set @t1 =4;
set @t2 = 0;
CALL p2 (1, 2 ,@t1, @t2);
SELECT @t1,@t2;

python 执行
#!/usr/bin/env python
# -*- coding:utf-8 -*-
import pymysql

conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='root123', db='userdb')
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)

# 执行存储过程
cursor.callproc('p2',args=(1, 22, 3, 4))

# 获取执行完存储的参数
cursor.execute("select @_p2_0,@_p2_1,@_p2_2,@_p2_3")
result = cursor.fetchall()
# {"@_p2_0":11 }

cursor.close()
conn.close()

print(result)

3.2 返回值 & 结果集

delimiter $$
create procedure p3(
    in n1 int,
    inout n2 int,
    out n3 int
)
begin
    set n2 = n1 + 100;
    set n3 = n2 + n1 + 100;
    select * from d1;
end $$
delimiter ;
set @t1 =4;
set @t2 = 0;
CALL p3 (1,@t1, @t2);
SELECT @t1,@t2;


python执行
#!/usr/bin/env python
# -*- coding:utf-8 -*-
import pymysql

conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='root123', db='userdb')
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
# 执行存储过程
cursor.callproc('p3',args=(22, 3, 4))
table = cursor.fetchall() # 得到执行存储过中的结果集

# 获取执行完存储的参数
cursor.execute("select @_p3_0,@_p3_1,@_p3_2")
rets = cursor.fetchall()

cursor.close()
conn.close()

print(table)
print(rets)

3.3 事务& 异常

事务，成功都成功，失败都失败。

delimiter $$
create PROCEDURE p4(
    OUT p_return_code tinyint
)
BEGIN 
  DECLARE exit handler for sqlexception 
  BEGIN 
    -- ERROR 
    set p_return_code = 1; 
    rollback; 
  END; 
 
  DECLARE exit handler for sqlwarning 
  BEGIN 
    -- WARNING 
    set p_return_code = 2; 
    rollback; 
  END; 
 
  START TRANSACTION;  -- 开启事务
    delete from d1;
    insert into tb(name)values('seven');
  COMMIT;  -- 提交事务
 
  -- SUCCESS 
  set p_return_code = 0; 
 
  END $$
delimiter ; 
set @ret =100;
CALL p4(@ret);
SELECT @ret;


python执行
#!/usr/bin/env python
# -*- coding:utf-8 -*-
import pymysql

conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='root123', db='userdb')
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
# 执行存储过程
cursor.callproc('p4',args=(100))

# 获取执行完存储的参数
cursor.execute("select @_p4_0")
rets = cursor.fetchall()

cursor.close()
conn.close()

print(table)
print(rets)

3.4 游标

delimiter $$
create procedure p5()
begin 
    declare sid int;
    declare sname varchar(50); 
    declare done int default false;


    declare my_cursor CURSOR FOR select id,name from d1;
    
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    
    open my_cursor;
        xxoo: LOOP
            fetch my_cursor into sid,sname;
            IF done then 
                leave xxoo;
            END IF;
            insert into t1(name) values(sname);
        end loop xxoo;
    close my_cursor;
end $$
delimiter ; 
call p5();

4.视图

视图其实是一个虚拟表，临时表（非真实存在），其本质是【根据SQL语句获取动态的数据集，并为其命名】，用户使用时只需使用【名称】即可获取结果集，并可以将其当作表来使用。

SELECT
    *
FROM
    (SELECT nid,name FROM tb1 WHERE nid > 2) AS A
WHERE
    A.name > 'zzp';

创建视图

create view v1 as select id,name from d1 where id > 1;

使用视图

select * from v1;

-- select * from (select id,name from d1 where id > 1) as v1;

删除视图
```
drop view v1;
```
修改视图
```
alter view v1 as SQL语句
```

注意：基于视图只能查询，针对视图不能执行增加、修改、删除。如果源表发生变化，视图表也会发生变化。

5.触发器

对某个表进行【增/删/改】操作的前后如果希望触发某个特定的行为时，可以使用触发器。

# 插入前
CREATE TRIGGER tri_before_insert_tb1 BEFORE INSERT ON tb1 FOR EACH ROW
BEGIN
    ...
END

# 插入后
CREATE TRIGGER tri_after_insert_tb1 AFTER INSERT ON tb1 FOR EACH ROW
BEGIN
    ...
END

# 删除前
CREATE TRIGGER tri_before_delete_tb1 BEFORE DELETE ON tb1 FOR EACH ROW
BEGIN
    ...
END

# 删除后
CREATE TRIGGER tri_after_delete_tb1 AFTER DELETE ON tb1 FOR EACH ROW
BEGIN
    ...
END

# 更新前
CREATE TRIGGER tri_before_update_tb1 BEFORE UPDATE ON tb1 FOR EACH ROW
BEGIN
    ...
END

# 更新后
CREATE TRIGGER tri_after_update_tb1 AFTER UPDATE ON tb1 FOR EACH ROW
BEGIN
    ...
END
DROP TRIGGER tri_after_insert_tb1;

示例：

在 t1 表中插入数据之前，先在 t2 表中插入一行数据。

delimiter $$
CREATE TRIGGER tri_before_insert_t1 BEFORE INSERT ON t1 FOR EACH ROW
BEGIN
	-- NEW.id  NEW.name  NEW.email
	-- INSERT INTO t2 (name) VALUES();
	IF NEW.name = 'zzp' THEN
        INSERT INTO t2 (name) VALUES(NEW.id);
    END IF;

END $$
delimiter ;

insert into t1(id,name,email)values(1,"zzp","[email protected]")

在t1表中删除数据之后，再在t2表中插入一行数据。

delimiter $$
CREATE TRIGGER tri_after_insert_t1 AFTER DELETE ON t1 FOR EACH ROW
BEGIN

IF OLD.name = 'zzp' THEN
    INSERT INTO t2 (name) VALUES(OLD.id);
END IF;

END $$
delimiter ;

特别的：NEW表示新数据，OLD表示原来的数据。

6.事务（ACID）

事务的四大特性：

原子性（Atomicity）
- 事务被视为一个原子操作，不可再分割。
- 要么所有的操作都成功执行，要么所有的操作都会被回滚到事务开始前的状态，确保数据的一致性
一致性（Consistency）
- 事务执行前后，数据库应保持一致的状态。
- 在事务开始之前和结束之后，数据库必须满足所有的完整性约束，如数据类型、关系等
隔离性（Isolation）
- 事务的执行结果对其他并发执行的事务是隔离的。
- 即一个事务的执行不应受到其他事务的干扰，各个事务之间应该相互独立工作，从而避免数据的不一致性
持久性（Durability）
- 也叫永久性
- 一旦事务被提交，其结果应该永久保存在数据库中，并且可以被系统故障恢复。
- 即使系统发生宕机或崩溃，事务提交后的更改也应该是永久性的

6.1 mysql 操作

mysql> select * from users;
+----+---------+---------+
| id | name    | amount  |
+----+---------+---------+
|  1 | serein |    5    |
|  2 |  zzp   |    6    |
+----+---------+---------+
3 rows in set (0.00 sec)

mysql> begin;  -- 开启事务 start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> update users set amount=amount-2 where id=1;   -- 执行操作
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> update users set amount=amount+2 where id=2;   -- 执行操作
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> commit;  -- 提交事务  rollback;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from users;
+----+---------+---------+
| id | name    | amount  |
+----+---------+---------+
|  1 | serein |    3    |
|  2 |  zzp  |    8    |
+----+---------+---------+
3 rows in set (0.00 sec)

mysql> select * from users;
+----+---------+---------+
| id | name    | amount  |
+----+---------+---------+
|  1 | serein |    3    |
|  2 |  zzp  |    8    |
+----+---------+---------+
3 rows in set (0.00 sec)

mysql> begin; -- 开启事务
Query OK, 0 rows affected (0.00 sec)

mysql> update users set amount=amount-2 where id=1; -- 执行操作（此时数据库中的值已修改）
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> rollback; -- 事务回滚（回到原来的状态）
Query OK, 0 rows affected (0.00 sec)

mysql> select * from users;
+----+---------+---------+
| id | name    | amount  |
+----+---------+---------+
|  1 | serein |    3    |
|  2 |  zzp  |    8    |
+----+---------+---------+
3 rows in set (0.00 sec)

6.2 python操作

import pymysql

conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='root123', charset="utf8", db='userdb')
cursor = conn.cursor()

# 开启事务
conn.begin()

try:
    cursor.execute("update users set amount=1 where id=1")
    int('asdf')
    cursor.execute("update tran set amount=2 where id=2")
except Exception as e:
    # 回滚
    print("回滚")
    conn.rollback()
else:
    # 提交
    print("提交")
    conn.commit()

cursor.close()
conn.close()

7.锁

MySQL中自带了锁的功能，可以帮助我们实现开发过程中遇到的同时处理数据的情况。对于数据库中的锁，从锁的范围来讲有：

表级锁，即A操作表时，其他人对整个表都不能操作，等待A操作完之后，才能继续。
行级锁，即A操作表时，其他人对指定的行数据不能操作，其他行可以操作，等待A操作完之后，才能继续。

MYISAM支持表锁，不支持行锁；
InnoDB引擎支持行锁和表锁。

即：在MYISAM下如果要加锁，无论怎么加都会是表锁。
    在InnoDB引擎支持下如果是基于索引查询的数据则是行级锁，否则就是表锁。

所以，一般情况下我们会选择使用innodb引擎，并且在搜索时也会使用索引（命中索引）。

接下来的操作就基于innodb引擎来操作：

CREATE TABLE `L1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `count` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

在innodb引擎中，update、insert、delete的行为内部都会先申请锁（排它锁），申请到之后才执行相关操作，最后再释放锁。

所以，当多个人同时像数据库执行：insert、update、delete等操作时，内部加锁后会排队逐一执行。

而select则默认不会申请锁。

select * from xxx;

如果，你想要让select去申请锁，则需要配合事务 + 特殊语法来实现。

for update，排它锁，加锁之后，其他不可以读写。

begin; 
	select * from L1 where name="serein" for update;    -- name列不是索引（表锁）
commit;

begin; -- 或者 start transaction;
	select * from L1 where id=1 for update;			  -- id列是索引（行锁）
commit;

lock in share mode ，共享锁，加锁之后，其他可读但不可写。

begin; 
	select * from L1 where name="serein" lock in share mode;    -- 假设name列不是索引（表锁）
commit;

begin; -- 或者 start transaction;
	select * from L1 where id=1 lock in share mode;           -- id列是索引（行锁）
commit;

7.1 排它锁

排它锁（ for update），加锁之后，其他事务不可以读写。

应用场景：总共100件商品，每次购买一件需要让商品个数减1 。

A: 访问页面查看商品剩余 100
B: 访问页面查看商品剩余 100

此时 A、B 同时下单，那么他们同时执行SQL：
	update goods set count=count-1 where id=3
由于Innodb引擎内部会加锁，所以他们两个即使同一时刻执行，内部也会排序逐步执行。


但是，当商品剩余 1个时，就需要注意了。
A: 访问页面查看商品剩余 1
B: 访问页面查看商品剩余 1

此时 A、B 同时下单，那么他们同时执行SQL：
	update goods set count=count-1 where id=3
这样剩余数量就会出现 -1，很显然这是不正确的，所以应该怎么办呢？


这种情况下，可以利用 排它锁，在更新之前先查询剩余数量，只有数量 >0 才可以购买，所以，下单时应该执行：
	begin; -- start transaction;
	select count from goods where id=3 for update;
	-- 获取个数进行判断
	if 个数>0:
		update goods set count=count-1 where id=3;
	else:
		-- 已售罄
	commit;

基于Python代码示例：

import pymysql
import threading


def task():
    conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='root123', charset="utf8", db='userdb')
    cursor = conn.cursor(pymysql.cursors.DictCursor)
    # cursor = conn.cursor()
	
    # 开启事务
    conn.begin()

    cursor.execute("select id,age from tran where id=2 for update")
    # fetchall      ( {"id":1,"age":10},{"id":2,"age":10}, )   ((1,10),(2,10))
    # {"id":1,"age":10}   (1,10)
    result = cursor.fetchone()
    current_age = result['age']
    
    if current_age > 0:
        cursor.execute("update tran set age=age-1 where id=2")
    else:
        print("已售罄")

    conn.commit()

    cursor.close()
    conn.close()


def run():
    for i in range(5):
        t = threading.Thread(target=task)
        t.start()


if __name__ == '__main__':
    run()

7.2 共享锁

共享锁（ lock in share mode），可以读，但不允许写。

加锁之后，后续其他事物可以可以进行读，但不允许写（update、delete、insert），因为写的默认也会加锁。

Locking Read Examples

Suppose that you want to insert a new row into a table child, and make sure that the child row has a parent row in table parent. Your application code can ensure referential integrity throughout this sequence of operations.

First, use a consistent read to query the table PARENT and verify that the parent row exists. Can you safely insert the child row to table CHILD? No, because some other session could delete the parent row in the moment between your SELECT and your INSERT, without you being aware of it.

To avoid this potential issue, perform the SELECT using LOCK IN SHARE MODE:

SELECT * FROM parent WHERE NAME = 'Jones' LOCK IN SHARE MODE;

After the LOCK IN SHARE MODE query returns the parent 'Jones', you can safely add the child record to the CHILD table and commit the transaction. Any transaction that tries to acquire an exclusive lock in the applicable row in the PARENT table waits until you are finished, that is, until the data in all tables is in a consistent state.

总结

索引，加速查找 & 约束。
- innodb和myisam的区别，聚簇索引和非聚簇索引。
- 常见的索引：主键、唯一、普通。
- 命中索引
- 执行计划
函数，提供了一些常见操作 & 配合SQL语句，执行后返回结果。
存储过程，一个SQL语句的集合，可以出发复杂的情况，最终可以返回结果 + 数据集。
视图，一个虚拟的表。
触发器，在表中数据行执行前后自定义一些操作。

标签：存储,name,视图,cursor,索引,mysql,id,select
From： https://www.cnblogs.com/Formerly/p/17996857

索引和函数视图及存储过程

索引和函数视图及存储过程

1.索引

1.1 索引原理

1.1.1 非聚簇索引（mysiam引擎）

1.1.2 聚簇索引（innodb引擎）

1.2 常见索引

1.2.1 主键和联合主键索引

1.2.2 唯一和联合唯一索引

1.2.3 索引和联合索引

1.3 执行计划

2.函数

3. 存储过程

3.1 参数类型

3.2 返回值 & 结果集

3.3 事务& 异常

3.4 游标

4.视图

5.触发器

6.事务（ACID）

6.1 mysql 操作

6.2 python操作

7.锁

7.1 排它锁

7.2 共享锁

总结

相关文章

赞助商

阅读排行