mysql安装与使用

本文最后更新于：2023年10月4日晚上

Win 安装`Mysql`

下载安装包

下载地址

配置`Mysql`

解压到任意文件夹

在MySQL的安装目录下创建 my.ini 的文件，作为MySQL的配置文件。配置如下

[mysqld]

port=3306

basedir=C:\\Program Files\\mysql-5.7.31-winx64

datadir=C:\\Program Files\\mysql-5.7.31-winx64\\data

查看配置文件优先级命令

1
2
3

"C:\Program Files\mysql-5.7.31-winx64\bin\mysqld.exe" --help --verbose
# Default options are read from the following files in the given order:
# C:\WINDOWS\my.ini C:\WINDOWS\my.cnf C:\my.ini C:\my.cnf C:\Program Files\mysql-5.7.31-winx64\my.ini C:\Program Files\mysql-5.7.31-winx64\my.cnf

初始化
1
"C:\Program Files\mysql-5.7.31-winx64\bin\mysqld.exe" --initialize-insecure
初始化命令在执行时，会自动读取配置文件并执行初始化，此过程主要会做两件事：
- 自动创建 data 目录，以后我们的数据都会存放在这个目录。
- 同时创建建必备一些的数据，例如默认账户 root （无密码），用于登录MySQL并通过指令操作MySQL。
添加环境变量

"C:\Program Files\mysql-5.7.31-winx64\bin\mysqld.exe"添加到系统环境变量，后续至需要输入命令mysqld即可，前面的路径可以省略

强烈，建议大家还是把配置文件放在MySQL安装目录下，这样以后电脑上想要安装多个版本的MySQL时，配置文件可以相互独立不影响。

注意：如果你电脑的上述其他目录存在MySQL配置文件，建议删除，否则可能会影响MySQL的启动。

启动`Mysql`

临时启动
1
"C:\Program Files\mysql-5.7.31-winx64\bin\mysqld.exe"
注意：此时程序会挂起，内部就是可以接收客户端发来的 MySQL 指令，关闭窗口或 Ctrl+c 就可以停止运行。

这种启动方式每次开机或想要开启都需要手动执行一遍命令比较麻烦。
制作 windows 服务，基于 windows 服务管理。
1
"C:\Program Files\mysql-5.7.31-winx64\bin\mysqld.exe" --install mysql57
创建好服务之后，可以通过命令启动和关闭服务，例如：
1
2
3
net start mysql57 net stop mysql57
以后不再想要使用 window 服务了，也可以将制作的这个MySQL服务删除。
1
"C:\Program Files\mysql-5.7.31-winx64\bin\mysqld.exe" --remove mysql57

Mac 安装`Mysql`

下载安装包

官方下载地址：MySQL

如何在官方地址找到适合自己的下载源：

首页找到DOWNLOADS
下载页找到MySQL Community (GPL) Downloads »，这个就是开源免费的版本

MySQL Enterprise Edition 、MySQL Cluster CGE是收费版本不选
MySQL Community Downloads页面找到Download Archives=>MySQL Community Server找到对应版本选择 dmg 下载即可

MySQL Community Downloads页面找到MySQL Community Server可以直接下载最新的 mysql 版本一般为8.xx
一般下载 5.3 几版本，我用的 5.3.1

安装 mysql

打开mysql-5.7.31-macos10.14-x86_64.dmg
继续安装->同意许可=>输入密码或者录入指纹授权安装

配置 Mysql

查看安装的 mysql 目录
- 通过访达的前往文件夹查看=>输入/usr/local/
- 通过命令行查看：cd /usr/local/mysql ls -la查看目录详情

设置环境变量

编辑~/.zshrc或者~/.zprofile

我是编辑的~/.zshrc，了解了下目前的 mac 都是用此配置文件，增加以下内容

vim ~/.zshrc以下是我的配置文件

source ~/.bash_profile
export ANDROID_HOME=$HOME/Library/Android/sdk
export PATH=$PATH:$ANDROID_HOME/emulator
export PATH=$PATH:$ANDROID_HOME/tools
export PATH=$PATH:$ANDROID_HOME/tools/bin
export PATH=$PATH:$ANDROID_HOME/platform-tools

#flutter环境变量
export PATH=/Users/thell/flutter/bin:$PATH

#mysql环境配置
export PATH=$PATH:/usr/local/mysql/bin
export PATH=$PATH:/usr/local/mysql
export PATH=$PATH:/usr/local/mysql/support-files

"~/.zshrc" 14L, 445B

让配置文件生效source ~/.zshrc,就可以使用mysql mysqld加命令，不用带/usr/local/mysql前缀

设置配置文件：新建/usr/local/mysql/etc/my.cnf，增加以下内容：
1
2
3
4
5
6
7
[mysqld] port=3306 basedir=/usr/local/mysql datadir=/usr/local/mysql/data

启动 mysql 服务报错处理：

$ mysql.server start
Starting MySQL
. ERROR! The server quit without updating PID file (/usr/local/mysql/data/jh.local.pid).
$ mysql.server status
/usr/local/mysql/support-files/mysql.server: line 365: pidof: command not found
 ERROR! MySQL is not running

Starting MySQL
 SUCCESS!
thell@hailongdeMacBook-Air mysql % /usr/local/mysql/bin/mysqld_safe: line 144: /usr/local/mysql/data/hailongdeMacBook-Air.local.err: Permission denied
cat: /usr/local/mysql/data/hailongdeMacBook-Air.local.pid: Permission denied
rm: /tmp/mysql.sock: Permission denied
/usr/local/mysql/bin/mysqld_safe: line 144: /usr/local/mysql/data/hailongdeMacBook-Air.local.err: Permission denied
/usr/local/mysql/bin/mysqld_safe: line 198: /usr/local/mysql/data/hailongdeMacBook-Air.local.err: Permission denied
/usr/local/mysql/bin/mysqld_safe: line 144: /usr/local/mysql/data/hailongdeMacBook-Air.local.err: Permission denied
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

- 因为 mac 系统启动自动启动了 mysql,查看是否启动了 mysql 命令：`ps -ef | grep mysql`

  <img src="assets/image-20211228234435404.png"/>

- 查案启动文件是否存在：`cd /Library/LaunchDaemons` `ls`可查看到`com.oracle.oss.mysql.mysqld.plist`

  <img src="assets/image-20211228234704493.png"/>

- 卸载系统自动启动：`sudo launchctl unload -w /Library/LaunchDaemons/com.oracle.oss.mysql.mysqld.plist`

  - 详细说明参考：[MySQL 启动报错问题排查 - 知乎 (zhihu.com)](https://zhuanlan.zhihu.com/p/25182827)

- 删除 mysql 的进程：`ps -ef | grep mysql`找到相应的进程 id，`kill -9 id号`全部删除掉

- `mysql`目录下的`data`下的日志文件要有读写权限`sudo chmod -R a+rwx /usr/local/mysql/data/hailongdeMacBook-Air.local.err`

```bash
Starting MySQL
./usr/local/mysql/bin/mysqld_safe: line 144: /usr/local/mysql/data/hailongdeMacBook-Air.local.err: Permission denied
/usr/local/mysql/bin/mysqld_safe: line 144: /usr/local/mysql/data/hailongdeMacBook-Air.local.err: Permission denied
/usr/local/mysql/bin/mysqld_safe: line 198: /usr/local/mysql/data/hailongdeMacBook-Air.local.err: Permission denied
/usr/local/mysql/bin/mysqld_safe: line 144: /usr/local/mysql/data/hailongdeMacBook-Air.local.err: Permission denied
 ERROR! The server quit without updating PID file (/usr/local/mysql/data/hailongdeMacBook-Air.local.pid).

mysql目录下的data用户组的是mysql不能为_mysql,执行命令:sudo chown -R mysql:mysql /usr/local/mysql/data/*

1
2
3

thell@hailongdeMacBook-Air LaunchDaemons % mysql.server start
Starting MySQL
.. ERROR! The server quit without updating PID file (/usr/local/mysql/data/hailongdeMacBook-Air.local.pid)

以上全部更改后,执行sudo mysql.server start,启动成功，不用 sudo 会提示权限不够

连接`Mysql`

命令

连接远程数据库

1	`mysql -h 42.192.55.206 -u blog -p`

连接本地数据库

1	`mysql -uroot -p`

本地无法连接服务器 Mysql

登录服务器

mysql -u root -p
#输入密码登录
use mysql;
select host from user where user='root';
select host from user where user='blog';

确定远程是无法连接 mysql,默认 mysql 是本地连接

开启远程连接数据库

查询远程连接权限

mysql> select host from user where user ='root';
+---------------+
| host          |
+---------------+
| 127.0.0.1     |
| ::1           |
| localhost     |
| vm-0-6-centos |
+---------------+
4 rows in set (0.00 sec)

mysql> select host from user where user ='blog';
+-----------+
| host      |
+-----------+
| 127.0.0.1 |
| localhost |
+-----------+
2 rows in set (0.00 sec)

在 MySql 本机连接 mysql。

use mysql        #打开mysql数据库

#将host设置为%表示任何ip都能连接mysql，当然您也可以将host指定为某个ip

update user set host='%' where user='root';

flush privileges;    #刷新权限表，使配置生效

然后我们就能远程连接我们的 mysql 了。

关闭远程连接

如果您想关闭远程连接，恢复 mysql 的默认设置(只能本地连接)，您可以通过以下步骤操作：

use mysql        #打开mysql数据库

#将host设置为localhost表示只能本地连接mysql

update user set host='localhost' where user='root';

flush privileges;    #刷新权限表，使配置生效

添加用户远程连接

查询所有用户的权限

mysql> select user,host from mysql.user;
+---------+---------------+
| user    | host          |
+---------+---------------+
| longhai | %             |
| blog    | 127.0.0.1     |
| root    | 127.0.0.1     |
| root    | ::1           |
| blog    | localhost     |
| longhai | localhost     |
| root    | localhost     |
| root    | vm-0-6-centos |
+---------+---------------+
8 rows in set (0.00 sec)


mysql>  SELECT DISTINCT CONCAT('User: ''',user,'''@''',host,''';') AS query FROM mysql.user;
+-------------------------------+
| query                         |
+-------------------------------+
| User: 'longhai'@'%';          |
| User: 'blog'@'127.0.0.1';     |
| User: 'root'@'127.0.0.1';     |
| User: 'root'@'::1';           |
| User: 'blog'@'localhost';     |
| User: 'longhai'@'localhost';  |
| User: 'root'@'localhost';     |
| User: 'root'@'vm-0-6-centos'; |
+-------------------------------+
8 rows in set (0.00 sec)

添加用户授权

添加一个用户名为 hailong，密码为 123456，权限为%(表示任意 ip 都能连接)的远程连接用户。命令参考如下：

#授权访问所有数据库示例:
grant all on *.* to '用户名'@'%' identified by '密码';
flush privileges;
#授权指定数据库示例:
mysql> mysql> grant all privileges on 数据库名.* to '用户名'@'%' IDENTIFIED  by '密码';
flush privileges;

mysql8.0 不支持以上语法

create USER '用户名'@'%' IDENTIFIED BY '密码';
# 将要修改的用户权限改为%
update user set host = '%' where user ='root';
# 刷新权限
flush privileges;

删除用户权限

mysql>  SELECT DISTINCT CONCAT('User: ''',user,'''@''',host,''';') AS query FROM mysql.user;
+-------------------------------+
| query                         |
+-------------------------------+
| User: 'longhai'@'%';          |
| User: 'blog'@'127.0.0.1';     |
| User: 'root'@'127.0.0.1';     |
| User: 'root'@'::1';           |
| User: 'blog'@'localhost';     |
| User: 'longhai'@'localhost';  |
| User: 'root'@'localhost';     |
| User: 'root'@'vm-0-6-centos'; |
+-------------------------------+
8 rows in set (0.00 sec)

mysql> delete from user where user='longhai' and host='localhost';
Query OK, 1 row affected (0.01 sec)

mysql>  SELECT DISTINCT CONCAT('User: ''',user,'''@''',host,''';') AS query FROM mysql.user;
+-------------------------------+
| query                         |
+-------------------------------+
| User: 'longhai'@'%';          |
| User: 'blog'@'127.0.0.1';     |
| User: 'root'@'127.0.0.1';     |
| User: 'root'@'::1';           |
| User: 'blog'@'localhost';     |
| User: 'root'@'localhost';     |
| User: 'root'@'vm-0-6-centos'; |
+-------------------------------+
7 rows in set (0.00 sec)

mysql> delete from user where user='longhai' and host='%';
Query OK, 1 row affected (0.00 sec)

mac 开机自动启动

1.编辑一个启动文件

1	`sudo vim /Library/LaunchDaemons/com.mysql.plist`

2.输入启动命令

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE plist PUBLIC "-//Apple//DTD PLIST 1.0//EN" "http://www.apple.com/DTDs/PropertyList-1.0.dtd">
<plist version="1.0">
  <dict>
    <key>KeepAlive</key>
    <true/>
    <key>Label</key>
    <string>com.mysql.mysqld</string>
    <key>ProgramArguments</key>
    <array>
    <string>/usr/local/mysql/bin/mysqld_safe</string>
    <string>--user=root</string>
    </array>
  </dict>
</plist>

3.加载文件

1	`sudo launchctl load -w /Library/LaunchDaemons/com.mysql.plist`

查看是否启动

1	`ps -ef \| grep mysql`

关于密码

在 windows 系统中模块默认 root 账户是没有密码的，如果想要为账户设定密码，可以在利用 root 账户登录成功之后，执行

设置修改密码

1	`set password = password("blog123");`

忘记`root`密码

如果你忘记了MySQL账户的密码。

修改配置文件，在 [mysqld] 节点下添加 skip-grant-tables=1
1
2
3
[mysqld] skip-grant-tables=1
重启MySQL，再次登录时，不需要密码直接可以进去了
- windows 重启
  1
  2
  net stop mysql57 net start mysql57
- mac 重启
  1
  sudo mysql.server restart
  重启后，无序密码就可以进入。
  1
  >>> mysql -u root -p
- 进入数据库后执行修改密码命令
  1
  2
  use mysql; update user set authentication_string = password('新密码'),password_last_changed=now() where user='root';
- 退出并再次修改配置文件，删除 [mysqld] 节点下的 skip-grant-tables=1
  1
  2
  3
  4
  [mysqld] ... # skip-grant-tables=1 ...
- 再次重启，以后就可以使用新密码登录了。

用户管理

在MySQL的默认数据库 mysql 中的 user 表中存储着所有的账户信息（含账户、权限等）。

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| day26              |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
10 rows in set (0.00 sec)

mysql> select user,authentication_string,host from  mysql.user;
+----------------------------------+-------------------------------------------+-------------------------------+
| user                             | authentication_string                     | host                          |
+----------------------------------+-------------------------------------------+-------------------------------+
| root                             | *FAAFFE644E901CFAFAEC7562415E5FAEC243B8B2 | localhost                     |
| mysql.session                    | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | localhost                     |
| mysql.sys                        | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | localhost                     |
+----------------------------------+-------------------------------------------+-------------------------------+
3 rows in set (0.00 sec)

创建和删除用户

1	`create user '用户名'@'连接者的IP地址' identified by '密码';`

create user wupeiqi1@127.0.0.1 identified by 'root123';
drop user wupeiqi1@127.0.0.1;

create user wupeiqi2@'127.0.0.%' identified by 'root123';
drop user wupeiqi2@'127.0.0.%';

create user wupeiqi3@'%' identified by 'root123';
drop user wupeiqi3@'%';

create user 'wupeiqi4'@'%' identified by 'root123';
drop user 'wupeiqi4'@'%';

修改用户

1	`rename user '用户名'@'IP地址' to '新用户名'@'IP地址';`

1
2
3

rename user wupeiqi1@127.0.0.1 to wupeiqi1@localhost;

rename user 'wupeiqi1'@'127.0.0.1' to 'wupeiqi1'@'localhost';

修改密码

1	`set password for '用户名'@'IP地址' = Password('新密码')`

1	`set password for 'wupeiqi4'@'%' = Password('123123');`

授权管理

创建好用户之后，就可以为用户进行授权了。

授权

1	`grant 权限 on 数据库.表 to '用户'@'IP地址'`

grant all privileges on *.* TO 'wupeiqi'@'localhost';         -- 用户wupeiqi拥有所有数据库的所有权限
grant all privileges on day26.* TO 'wupeiqi'@'localhost';     -- 用户wupeiqi拥有数据库day26的所有权限
grant all privileges on day26.info TO 'wupeiqi'@'localhost';  -- 用户wupeiqi拥有数据库day26中info表的所有权限

grant select on day26.info TO 'wupeiqi'@'localhost';          -- 用户wupeiqi拥有数据库day26中info表的查询权限
grant select,insert on day26.* TO 'wupeiqi'@'localhost';      -- 用户wupeiqi拥有数据库day26所有表的查询和插入权限

grant all privileges on day26db.* to 'wupeiqi4'@'%';


注意：flush privileges;   -- 将数据读取到内存中，从而立即生效。

对于权限

all privileges  除grant外的所有权限
select          仅查权限
select,insert   查和插入权限
...
usage                   无访问权限
alter                   使用alter table
alter routine           使用alter procedure和drop procedure
create                  使用create table
create routine          使用create procedure
create temporary tables 使用create temporary tables
create user             使用create user、drop user、rename user和revoke  all privileges
create view             使用create view
delete                  使用delete
drop                    使用drop table
execute                 使用call和存储过程
file                    使用select into outfile 和 load data infile
grant option            使用grant 和 revoke
index                   使用index
insert                  使用insert
lock tables             使用lock table
process                 使用show full processlist
select                  使用select
show databases          使用show databases
show view               使用show view
update                  使用update
reload                  使用flush
shutdown                使用mysqladmin shutdown(关闭MySQL)
super                   􏱂􏰈使用change master、kill、logs、purge、master和set global。还允许mysqladmin􏵗􏵘􏲊􏲋调试登陆
replication client      服务器位置的访问
replication slave       由复制从属使用

对于数据库和表

数据库名.*            数据库中的所有
数据库名.表名          指定数据库中的某张表
数据库名.存储过程名     指定数据库中的存储过程
*.*                  所有数据库

查看授权

1	`show grants for '用户'@'IP地址'`

1 2	`show grants for 'wupeiqi'@'localhost'; show grants for 'wupeiqi4'@'%';`

取消授权

1	`revoke 权限 on 数据库.表 from '用户'@'IP地址'`

revoke ALL PRIVILEGES on day26.* from 'wupeiqi'@'localhost';

revoke ALL PRIVILEGES on day26db.* from 'wupeiqi4'@'%';
注意：flush privileges;   -- 将数据读取到内存中，从而立即生效。

一般情况下，在很多的正规公司，数据库都是由 DBA 来统一进行管理，DBA 为每个项目的数据库创建用户，并赋予相关的权限。

条件

select * from info where age > 30;
select * from info where id > 1;
select * from info where id = 1;
select * from info where id >= 1;
select * from info where id != 1;
-- between and / and / or
select * from info where id between 2 and 4;   -- id大于等于2、且小于等于4
select * from info where name = '晚生' and age = 19;
select * from info where name = '隆海' or age = 49;
select * from info where (name = '后生' or email="pythl@foxmail.com")  and age=49;
-- in not/ in
select * from info where id in (1,4,6);
select * from info where id not in (1,4,6);
select * from info where id in (select id from depart);

通配符

1 2	`-- %所有 select * from info where name like "%生%";`

映射

select
	id,
	name,
	666 as num,
	( select max(id) from depart ) as mid, -- max/min/sum
	( select min(id) from depart) as nid, -- max/min/sum
	age
from info;

排序

order by asc/desc

1 2	`select * from info order by age desc; -- 倒序 select * from info order by age asc; -- 顺序`

取部分

limit offset

1	`select * from info limit 3 offset 2; -- 从位置2开始，向后获取前3数据`

数据库表中：1000 条数据。

第一页：select * from info limit 10 offset 0;
第二页：select * from info limit 10 offset 10;
第三页：select * from info limit 10 offset 20;
第四页：select * from info limit 10 offset 30;
…

分组

group by

1	`select age,max(id),min(id),count(id),sum(id),avg(id) from info group by age;`

左右连表

1
2
3

主表 left outer join 从表 on 主表.x = 从表.id
从表 right outer join 主表 on 主表.x = 从表.id
简写可以省略outer

sql 执行顺序

SQL执行顺序：
    join
    on
    where
    group by
    having
    order by
    limit

索引

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

索引的底层是基于 B+Tree 的数据结构存储的。

B+Tree 结构连接：https://www.cs.usfca.edu/~galles/visualization/BPlusTree.html

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

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

常见索引

在 innodb 引擎下，索引底层都是基于 B+Tree 数据结构存储（聚簇索引）。

在开发过程中常见的索引类型有：

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

索引的优缺点

优点：查找速度快、约束（唯一、主键、联合唯一）

缺点：插入、删除、更新速度比较慢，因为每次操作都需要调整整个 B+Tree 的数据结构关系。

一般情况下，我们针对只要通过索引列去搜搜都可以 命中 索引（通过索引结构加速查找）。

无法命中索引

类型不一致

使用不等于

or，当 or 条件中有未建立索引的列才失效。

排序，当根据索引排序时候，选择的映射如果不是索引，则不走索引。

like，模糊匹配时。

使用函数

最左前缀，如果是联合索引，要遵循最左前缀原则。

执行计划

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

1	`explain + SQL语句;`

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

ALL，全表扫描，数据表从头到尾找一遍。(一般未命中索引，都是会执行权标扫描)
INDEX，全索引扫描，对索引从头到尾找一遍
RANGE，对索引列进行范围查找
NDEX_MERGE，合并索引，使用多个单列索引搜索
REF，根据索引直接去查找（非键）。
EQ_REF，连表操作时常见。
CONST，常量，表最多有一个匹配行,因为仅有一行,在这行的列值可被优化器剩余部分认为是常数,const 表很快。
SYSTEM，系统，表仅有一行(=系统表)。这是 const 联接类型的一个特例。

其它列:

id，查询顺序标识

z，查询类型
    SIMPLE          简单查询
    PRIMARY         最外层查询
    SUBQUERY        映射为子查询
    DERIVED         子查询
    UNION           联合
    UNION RESULT    使用联合的结果
    ...

table，正在访问的表名

partitions，涉及的分区（MySQL支持将数据划分到不同的idb文件中，详单与数据的拆分）。 一个特别大的文件拆分成多个小文件（分区）。

possible_keys，查询涉及到的字段上若存在索引，则该索引将被列出，即：可能使用的索引。
key，显示MySQL在查询中实际使用的索引，若没有使用索引，显示为NULL。例如：有索引但未命中，则possible_keys显示、key则显示NULL。

key_len，表示索引字段的最大可能长度。(类型字节长度 + 变长2 + 可空1)，例如：key_len=195，类型varchar(64)，195=64*3+2+1

ref，连表时显示的关联信息。例如：A和B连表，显示连表的字段信息。

rows，估计读取的数据行数（只是预估值）
	explain select * from big where password ="025dfdeb-d803-425d-9834-445758885d1c";
	explain select * from big where password ="025dfdeb-d803-425d-9834-445758885d1c" limit 1;
filtered，返回结果的行占需要读到的行的百分比。
	explain select * from big where id=1;  -- 100，只读了一个1行，返回结果也是1行。
	explain select * from big where password="27d8ba90-edd0-4a2f-9aaf-99c9d607c3b3";  -- 10，读取了10行，返回了1行。
	注意：密码27d8ba90-edd0-4a2f-9aaf-99c9d607c3b3在第10行

extra，该列包含MySQL解决查询的详细信息。
    “Using index”
    此值表示mysql将使用覆盖索引，以避免访问表。不要把覆盖索引和index访问类型弄混了。
    “Using where”
    这意味着mysql服务器将在存储引擎检索行后再进行过滤，许多where条件里涉及索引中的列，当（并且如果）它读取索引时，就能被存储引擎检验，因此不是所有带where子句的查询都会显示“Using where”。有时“Using where”的出现就是一个暗示：查询可受益于不同的索引。
    “Using temporary”
    这意味着mysql在对查询结果排序时会使用一个临时表。
    “Using filesort”
    这意味着mysql会对结果使用一个外部索引排序，而不是按索引次序从表里读取行。mysql有两种文件排序算法，这两种排序方式都可以在内存或者磁盘上完成，explain不会告诉你mysql将使用哪一种文件排序，也不会告诉你排序会在内存里还是磁盘上完成。
    “Range checked for each record(index map: N)”
    这个意味着没有好用的索引，新的索引将在联接的每一行上重新估算，N是显示在possible_keys列中索引的位图，并且是冗余的。

事务

innodb 引擎中支持事务，myisam 不支持。

事务，就是来解决这种情况的。大白话：要成功都成功；要失败都失败。

四大特性

原子性（Atomicity）

1	`原子性是指事务包含的所有操作不可分割，要么全部成功，要么全部失败回滚。`

一致性（Consistency）

1	`执行的前后数据的完整性保持一致。`

隔离性（Isolation）

1	`一个事务执行的过程中,不应该受到其他事务的干扰。`

持久性（Durability）

1	`事务一旦结束,数据就持久到数据库`

锁

在用 MySQL 时，不知你是否会疑问：同时有很多做更新、插入、删除动作，MySQL 如何保证数据不出错呢？

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

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

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

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

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

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

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

而 select 则默认不会申请锁。

1	`select * from xxx;`

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

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

1
2
3

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

1
2
3

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

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

1
2
3

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

1
2
3

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

排它锁

排它锁（ 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()

共享锁

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

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

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

Mysql

#Mysql

mysql安装与使用

https://pythl.com/archives/ee3f912f.html

作者

晚生隆海

发布于

2019年12月12日

更新于

2023年10月4日

许可协议

Promise理解和使用上一篇

Dokcer安装使用下一篇

mysql安装与使用

Win 安装Mysql

下载安装包

配置Mysql

启动Mysql

Mac 安装Mysql

下载安装包

安装 mysql

配置 Mysql

连接Mysql

本地无法连接服务器 Mysql

开启远程连接数据库

查询远程连接权限

关闭远程连接

添加用户远程连接

查询所有用户的权限

添加用户授权

删除用户权限

mac 开机自动启动

关于密码

设置修改密码

忘记root密码

用户管理

授权管理

条件

通配符

映射

排序

取部分

分组

左右连表

sql 执行顺序

索引

常见索引

索引的优缺点

无法命中索引

执行计划

事务

四大特性

锁

排它锁

共享锁

Win 安装`Mysql`

配置`Mysql`

启动`Mysql`

Mac 安装`Mysql`

连接`Mysql`

忘记`root`密码