# MySQL学习笔记

MySQL相关知识

MySQL 是最流行的关系型数据库管理系统之一，主要用于持久化存储我们的系统中的一些数据比如用户信息，是一个开源免费的关系型数据库管理系统，RDBMS

# 一、MySQL各种安装姿势

# 1.win10

系统win10，安装的是MySQL Server解压版官网下载地址 https://dev.mysql.com/downloads/

MySQL Installer for Windows 这个是安装包

MySQL Community Server 解压版安装

安装之前记得卸载干净之前的

win中查看MySQL进程命令 tasklist /fi "imagename eq mysqld.exe"

# 1.1 下载MySQL Community Server (opens new window)

# 1.2 解压放到指定路径下：如下

# 1.3 准备配置文件 `my.ini`

新建 Data 文件夹，新建 my.ini 配置文件

如下配置文件复制即可，需修改两处 basedir 和 datadir 改成自己对应的路径即可，注意使用 \\

   [mysqld]
   # 设置端口号
   port=3306
   # 设置mysql的安装目录
   basedir=D:\\software\\mysql-8.0.29-winx64
   # 设置mysql数据库的数据的存放目录
   datadir=D:\\software\\mysql-8.0.29-winx64\\Data
   # 允许最大连接数
   max_connections=200
   # 允许连接失败的次数。这是为了防止有人从该主机试图攻击数据库系统
   max_connect_errors=10
   # 服务端使用的字符集默认为UTF8/utf8mb4
   character-set-server=utf8mb4
   # 创建新表时将使用的默认存储引擎
   default-storage-engine=INNODB
   # 默认使用“mysql_native_password”插件认证
   default_authentication_plugin=mysql_native_password
   [mysql]
   # 设置mysql客户端默认字符集/utf8mb4
   default-character-set=utf8mb4
   [client]
   # 设置mysql客户端连接服务端时默认使用的端口/utf8mb4
   port=3306
   default-character-set=utf8mb4

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

# 1.4 配置环境变量

编辑 Path 新增 MySQL 的安装目录的bin目录，我的是 D:\software\mysql-8.0.29-winx64\bin

# 1.5 初始化数据库，安装服务，启动

以管理员方式运行cmd,输入如下命令

mysqld --initialize --console : 初始化，这里务必记住初始密码，后面会用到 (如果密码没记住，可以删除 datadir 目录，重新执行当前命令)

mysqld --install : 安装服务，成功后出现Service successfully installed

net start mysql : 启动服务

# 1.6 安装完成，修改密码

登录MySQL mysql -uroot -p 输入上面那个初始密码

执行修改密码 ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '新密码';

# 2.CentOS-二进制方式安装-多实例

这里以5.7版本为例 http://mirrors.sohu.com/mysql/MySQL-5.7/mysql-5.7.36-linux-glibc2.12-x86_64.tar.gz

# 2.1 下载二进制包解压到/usr/local/mysql

# 下载
wget http://mirrors.sohu.com/mysql/MySQL-5.7/mysql-5.7.36-linux-glibc2.12-x86_64.tar.gz
mkdir -p /usr/local/my_mysql
tar -xvzf mysql-5.7.36-linux-glibc2.12-x86_64.tar.gz -C /usr/local/my_mysql

# 安装mysql运行所需依赖
yum instal ncurses-devel libaio-devel gcc net-tools make cmake -y

1
2
3
4
5
6
7

# 2.2 创建mysql用户

useradd -s /sbin/nologin -M mysql  # 不允许登录shell

# 2.3 准备多实例目录

mkdir -p /usr/local/my_mysql/{3306,3307}
yum install tree -y

[root@localhost myapps]# tree /usr/local/my_mysql/
/usr/local/my_mysql/
├── 3306
├── 3307
└── mysql-5.7.36-linux-glibc2.12-x86_64

1
2
3
4
5
6
7
8

# 2.4 准备多个配置文件

实例1：3306配置文件 cat /usr/local/my_mysql/3306/my.cnf

[client]

[mysqld]
port=3306
socket=/usr/local/my_mysql/3306/mysql.sock
basedir=/usr/local/my_mysql/mysql-5.7.36-linux-glibc2.12-x86_64
datadir=/usr/local/my_mysql/3306/data
log-bin=/usr/local/my_mysql/3306/mysql-bin
server-id=1

[mysqld_safe]
log-error=/usr/local/my_mysql/3306/mysql_3306_error.log
pid-file=/usr/local/my_mysql/3306/mysqld_3306.pid

1
2
3
4
5
6
7
8
9
10
11
12
13

实例2：3307配置文件

[client]

[mysqld]
port=3307
socket=/usr/local/my_mysql/3307/mysql.sock
basedir=/usr/local/my_mysql/mysql-5.7.36-linux-glibc2.12-x86_64
datadir=/usr/local/my_mysql/3307/data
log-bin=/usr/local/my_mysql/3307/mysql-bin
server-id=2

[mysqld_safe]
log-error=/usr/local/my_mysql/3307/mysql_3307_error.log
pid-file=/usr/local/my_mysql/3307/mysqld_3307.pid

1
2
3
4
5
6
7
8
9
10
11
12
13

# 2.5 准备mysql启停止脚本

3306/mysql_3306 chmod +x mysql_3306

port=3306
mysql_user="mysql"
cmdpath="/usr/local/my_mysql/mysql-5.7.36-linux-glibc2.12-x86_64/bin"
mysql_sock="/usr/local/my_mysql/${port}/mysql.sock"
mysqld_pid_file_path=/usr/local/my_mysql/${port}/mysqld_${port}.pid

start(){
	if [ ! -e "$mysql_sock" ];then
		printf "Starting MySQL...\n"
		/bin/sh ${cmdpath}/mysql_safe --defaults-file=/usr/local/my_mysql/${port}/my.cnf --pid-file=$mysqld_pid_file_path 2>&1 > /dev/null &
		sleep 3
	else
		printf "MySQL is running.\n"
		exit 1
	fi
}

stop(){
	if [ ! -e "$mysql_sock" ];then
		printf "MySQL is stopped.\n"
		exit 1
	else
		printf "Stoping MySQL...\n"
		mysqld_pid=`cat "$mysqld_pid_file_path"`
	if (kill -0 $mysqld_pid 2>/dev/null)
		then
		kill $mysqld_pid
		sleep 2
		fi
	fi
}

restart(){
	printf "Restarting MySQL...\n"
	stop
	sleep 2
	start
}

case "$1" in
start) 
	start
;;
stop)
	stop
;;
restart)
	restart
;;
*)
	printf "Usage: /usr/local/my_mysql/${port}/mysql{start|stop|restart}\n"
esac

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52

3307/mysql_3307 和上面一样修改

注意，修改完脚本需要赋予可执行权限 chmod +x mysql_3307

# 2.6 用户、组授权：降低权限，全部赋予mysql

chown -R mysql.mysql /usr/local/my_mysql

# 2.7 Path配置

# vim /etc/profile

export PATH=/usr/local/my_mysql/mysql-5.7.36-linux-glibc2.12-x86_64/bin:$PATH
source /etc/profile
echo $PATH

1
2
3
4
5

# 2.8 创建多个实例对应得数据目录

mkdir -p /usr/local/my_mysql/{3306,3307}/data
./my_mysql/
├── 3306
│   ├── data
│   ├── my.conf
│   └── mysql_3306
├── 3307
│   ├── data
│   ├── my.cnf
│   └── mysql_3307
└── mysql-5.7.36-linux-glibc2.12-x86_64

1
2
3
4
5
6
7
8
9
10
11

# 4.9 mysql多实例初始化

# 3306
[root@localhost my_mysql]# /usr/local/my_mysql/mysql-5.7.36-linux-glibc2.12-x86_64/bin/mysqld --defaults-file=/usr/local/my_mysql/3306/my.cnf --initialize --basedir=/usr/local/my_mysql/mysql-5.7.36-linux-glibc2.12-x86_64 --datadir=/usr/local/my_mysql/3306/data --user=mysql
2023-03-25T13:39:53.956088Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2023-03-25T13:39:54.100183Z 0 [Warning] InnoDB: New log files created, LSN=45790
2023-03-25T13:39:54.131455Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2023-03-25T13:39:54.192412Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 86550c3c-cb12-11ed-965f-000c29341fba.
2023-03-25T13:39:54.194378Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2023-03-25T13:39:54.747652Z 0 [Warning] A deprecated TLS version TLSv1 is enabled. Please use TLSv1.2 or higher.
2023-03-25T13:39:54.747671Z 0 [Warning] A deprecated TLS version TLSv1.1 is enabled. Please use TLSv1.2 or higher.
2023-03-25T13:39:54.748368Z 0 [Warning] CA certificate ca.pem is self signed.
2023-03-25T13:39:55.306249Z 1 [Note] A temporary password is generated for root@localhost: )fVsm9M7i9f9


# 3307
[root@localhost my_mysql]# /usr/local/my_mysql/mysql-5.7.36-linux-glibc2.12-x86_64/bin/mysqld --defaults-file=/usr/local/my_mysql/3307/my.cnf --initialize --basedir=/usr/local/my_mysql/mysql-5.7.36-linux-glibc2.12-x86_64 --datadir=/usr/local/my_mysql/3307/data --user=mysql
2023-03-25T14:30:03.032719Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2023-03-25T14:30:03.227199Z 0 [Warning] InnoDB: New log files created, LSN=45790
2023-03-25T14:30:03.256065Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2023-03-25T14:30:03.326754Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 87ea7db9-cb19-11ed-92ac-000c29341fba.
2023-03-25T14:30:03.329990Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2023-03-25T14:30:04.734226Z 0 [Warning] A deprecated TLS version TLSv1 is enabled. Please use TLSv1.2 or higher.
2023-03-25T14:30:04.734244Z 0 [Warning] A deprecated TLS version TLSv1.1 is enabled. Please use TLSv1.2 or higher.
2023-03-25T14:30:04.734927Z 0 [Warning] CA certificate ca.pem is self signed.
2023-03-25T14:30:05.273750Z 1 [Note] A temporary password is generated for root@localhost: <f<iFW.u5tub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

# 4.10 启动3306 3307数据库

# 查询端口，是否有MySQL
[root@localhost my_mysql]# netstat -tunlp|grep mysql
[root@localhost my_mysql]# 
[root@localhost my_mysql]# /usr/local/my_mysql/3306/mysql_3306 start
Starting MySQL...
[root@localhost my_mysql]# /usr/local/my_mysql/3307/mysql_3307 start
Starting MySQL...
[root@localhost my_mysql]# netstat -tunlp|grep mysql
tcp6       0      0 :::3306                 :::*                    LISTEN      3214/mysqld         
tcp6       0      0 :::3307                 :::*                    LISTEN      3418/mysqld

1
2
3
4
5
6
7
8
9
10

# 4.11 登录数据库

密码在4.8初始化里最后

通过mysql.sock套接字登录：客户端和服务端在同一台机器 mysql -S /usr/local/my_mysql/3306/mysql.sock -p
通过ip+port mysql -uroot -p -P3306 -h127.0.0.1

# 3306
[root@localhost my_mysql]# mysql -S /usr/local/my_mysql/3306/mysql.sock -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.36-log

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

# 3307
[root@localhost my_mysql]# mysql -S /usr/local/my_mysql/3307/mysql.sock -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.36-log

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33

# 4.12 改密码

[root@localhost my_mysql]# mysqladmin -uroot -S /usr/local/my_mysql/3306/mysql.sock password -p
Enter password: 
New password: 
Confirm new password: 
Warning: Since password will be sent to server in plain text, use ssl connection to ensure password safety.
[root@localhost my_mysql]# 
[root@localhost my_mysql]# mysql -uroot -p -P3306 -h127.0.0.1
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.7.36-log MySQL Community Server (GPL)

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

# 4.13 开机自启(可忽略，多种方法都可)

cat /etc/rc.local # 开机自启文件

# MySQL启动脚本
/usr/local/my_mysql/3306/mysql_3306 start
/usr/local/my_mysql/3307/mysql_3307 start

# 启动脚本追加写入开机自启文件即可
echo "/usr/local/my_mysql/3306/mysql_3306 start" >> /etc/rc.local
echo "/usr/local/my_mysql/3307/mysql_3307 start" >> /etc/rc.local

tail -2 /etc/rc.local

1
2
3
4
5
6
7
8
9

# 3.CentOS-rmp包安装

# 3.1 下载安装包

http://mirrors.sohu.com/mysql/MySQL-5.7/ 也可以区官网或者镜像站找其他版本等

找到rpm包,wget下载,或者下载好上传到服务器例如：mysql-8.0.28-1.el8.x86_64.rpm-bundle.tar

# 3.2 解压安装

tar -xvf mysql-8.0.28-1.el8.x86_64.rpm-bundle.tar

# rpm安装，顺序不能乱
rpm -ivh mysql-community-common-8.0.28-1.el8.x86_64.rpm

rpm -ivh mysql-community-client-plugins-8.0.28-1.el8.x86_64.rpm

rpm -ivh mysql-community-libs-8.0.28-1.el8.x86_64.rpm

#rpm -ivh mysql-community-libs-compat-8.0.28-1.el8.x86_64.rpm # 可能没有,就省略
#rpm -ivh mysql-community-devel-8.0.28-1.el8.x86_64.rpm # 可能没有,就省略

rpm -ivh mysql-community-client-8.0.28-1.el8.x86_64.rpm

rpm -ivh mysql-community-icu-data-files-8.0.28-1.el8.x86_64.rpm

rpm -ivh mysql-community-server-8.0.28-1.el8.x86_64.rpm

mysqld --initialize --console
chown -R mysql:mysql /var/lib/mysql/
systemctl start mysqld
cat /var/log/mysqld.log | grep localhost
#root@localhost: 4hQqurDMke:x
mysql -uroot -p 
alter user 'root'@'localhost' identified by 'root';

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

备注：可以使用一个命令安装当前路径下所有rpm包 yum localinstall ./*

MySQL 默认配置文件 cat /etc/my.cnf

# 4.yum方式：一般配置阿里云镜像

yum install mariadb-server mariadb -y
systemctl start mariadb

1
2

# 5.源码编译安装

# 5.1 环境准备

准备编译环境比如gcc等

yum install ncurses-devel libaio-devel gcc make cmake net-tools wget vim -y

创建mysql用户和组，用于授权目录

useradd -s /sbin/nologin -M mysql  # 不允许登录shell
id mysql

1
2

# 5.2 安装步骤

1 获取mysql源代码包，解压缩

# 1.下载
mkdir -p /home/myapps 
cd /home/myapps 
weget http://mirrors.sohu.com/mysql/MySQL-5.7/mysql-5.7.36.tar.gz

# 2.解压缩
tar xzf mysql-8.0.27.tar.gz
cd mysql-8.0.27

1
2
3
4
5
6
7
8

2 编译安装：执行cmake，生成makefile，用于编译安装

# 编译参数
cmake . -DCMAKE_INSTALL_PREFIX=/usr/local/mysql-8.0.27 \
-DMYSQL_DATADIR=/usr/local/mysql-8.0.27/data \
-DMYSQL_UNIX_ADDR=/usr/local/mysql-8.0.27/tmp/mysql.sock \
-DDEFAULT_CHARSET=utf8 \
-DDEFAULT_COLLATION=utf8_general_ci \
-DWITH_EXTRA_CHARSETS=all \
-DWITH_INNOBASE_STORAGE_ENGINE=1 \
-DWITH_FEDERATED_STORAGE_ENGINE=1 \
-DWITH_BLACKHOLE_STORAGE_ENGINE=1 \
-DWITHOUT_EXAMPLE_STORAGE_ENGINE=1 \
-DWITH_ZLIB=bundled \
-DWITH_SSL=bundled \
-DENABLED_LOCAL_INFILE=1 \
-DWITH_EMBEDDED_SERVER=1 \
-DENABLE_DOWNLOADS=1 \
-DWITH_DEBUG=0


make && make install

# 创建软连接
ln -s /usr/local/mysql-8.0.27  /usr/local/mysql

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

3 初始化

/usr/local/mysql/scripts/mysql_install_db --basedir=/usr/local/mysql/ --datadir=/usr/local/mysql/data --user=mysql

# 看到如下目录，表示初始化完成
ls /usr/local/mysql/data
# mysql performance_schema...

1
2
3
4
5

4 准备启动脚本和配置文件

cp /usr/local/mysql/support-files/my-default.cnf /etc/my.cnf  # MySQL配置文件
cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld  # MySQL管理脚本

1
2

5 启动mysql

/etc/init.d/mysqld status
/etc/init.d/mysqld start
# 启动报错，是缺少这个目录
mkdir -p /usr/local/mysql/tmp
# 启动报错，权限问题
chown -R mysql.mysql

# 再次尝试启动，启动成功

1
2
3
4
5
6
7
8

6 登录，开始使用了

mysql -uroot -p

# 6.Docker方式安装

# 二、远程连接

# 2.1 确认数据库是否允许远程连接

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

# host是%代表允许远程连接，user表Host字段如果是localhost，说明root用户只能本地登录，现在把他改成远程登录
mysql> update mysql.user set host='%' where user='root';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

# 2.2 查看防火墙是否开放端口

[root@localhost ~]# firewall-cmd --list-port
3306/tcp 3307/tcp
# 如果没有开放端口，必须放开端口
[root@localhost ~]# firewall-cmd --add-port=8080/tcp --permanent

1
2
3
4

# 2.3 远程工具

比如Nacicat,Dbeaver,sqlyog,datagrip等

# 三、MySQL概念的东西

# 一、存储引擎

MySQL 5.5 之前，MyISAM是默认存储引擎，5.5 版本之后，引入了 InnoDB（事务性数据库引擎）

项目	MyISAM	InnoDB
是否支持行级锁	只有表级锁	支持行级锁和表级锁（默认为行级锁）
是否支持事务	不支持	InnoDB 支持
是否支持数据库异常崩溃后的安全恢复	不支持	支持（依赖于 redo log）
是否支持 MVCC	不支持	支持

备注：

InnoDB 引擎使用 redo log(重做日志) 保证事务的持久性，使用 undo log(回滚日志) 来保证事务的原子性
InnoDB 引擎通过锁机制、MVCC 等手段来保证事务的隔离性，默认隔离级别 REPEATABLE-READ
MVCC 可以看作是行级锁的一个升级，可以有效减少加锁操作，提供性能

# 二、事务

概念：事务是逻辑上的一组操作，要么都执行，要么都不执行。
流程：

事务开启START TRANSACTION; 或者 BEGIN;开启事务后，所有被执行的SQL语句均被认作当前事务内的SQL语句。
事务提交COMMIT;

事务回滚ROLLBACK;如果部分操作发生问题，映射到事务开启前。

# 开启一个事务
START TRANSACTION;
# 多条 SQL 语句
UPDATE t_user SET phone='15365657896' WHERE id = 10001;
## 提交事务
COMMIT;

1
2
3
4
5
6

ACID特性：

☑️原子性：事务是最小的执行单位，不允许分割。事务的原子性确保动作要么全部完成，要么完全不起作用；
☑️一致性：执行事务前后，数据保持一致，例如转账业务中，无论事务是否成功，转账者和收款人的总额应该是不变的；
☑️隔离性：并发访问数据库时，一个用户的事务不被其他事务所干扰，各并发事务之间数据库是独立的；
☑️持久性：一个事务被提交之后。它对数据库中数据的改变是持久的，即使数据库发生故障也不应该对其有任何影响。
并发事务带来的问题

☑️脏读：当一个事务正在访问数据并且对数据进行了修改，而这种修改还没有提交到数据库中，这时另外一个事务也访问了这个数据，然后使用了这个数据。因为这个数据是还没有提交的数据，那么另外一个事务读到的这个数据是“脏数据”
☑️不可重复读：指在一个事务内多次读同一数据。在这个事务还没有结束时，另一个事务也访问该数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改导致第一个事务两次读取的数据可能不太一样。这就发生了在一个事务内两次读到的数据是不一样的情况，因此称为不可重复读。

☑️幻读：幻读与不可重复读类似。它发生在一个事务（T1）读取了几行数据，接着另一个并发事务（T2）插入了一些数据时。在随后的查询中，第一个事务（T1）就会发现多了一些原本不存在的记录，就好像发生了幻觉一样，所以称为幻读。

不可重复读和幻读区别：不可重复读的重点是修改比如多次读取一条记录发现其中某些列的值被修改，幻读的重点在于新增或者删除比如多次读取一条记录发现记录增多或减少了
隔离级别 ☑️读未提交 READ-UNCOMMITTED：可能会导致脏读、幻读或不可重复读。
☑️读已提交 READ-COMMITTED：可以阻止脏读，但是幻读或不可重复读仍有可能发生。
☑️可重复读 REPEATABLE-READ：可以阻止脏读和不可重复读，但幻读仍有可能发生。
☑️可串行化 SERIALIZABLE：该级别可以防止脏读、不可重复读以及幻读。

	脏读	不可重复读	幻读	备注
读未提交	❌	❌	❌
读已提交	✔️	❌	❌
可重复读	✔️	✔️	❌	gap锁，MySQL默认级别
串行化	✔️	✔️	✔️	读锁

查询MySQL(8.0)的默认隔离级别SELECT @@transaction_isolation;

注意：在使用分布式事务时，InnoDB 存储引擎的事务隔离级别必须设置为 SERIALIZABLE。

编程式事务

声明式事务

spring的事务失效：

1. 方法内部自调用，相当于用this调用，应该用代理对象调用
2. 方法是private的，因为AOP代理对象可能是子类对象，子类无法调用private的方法
3. 方法是final的，同上
4. 单独的线程调用，会创建新的连接，事务是基于一个连接的，所以失效
5. 异常被吃点
6. 类没有被Spring管理

# 三、索引

MySQL索引的类型有 BTree索引，哈希索引，全文索引

InnoDB存储引擎中有页的概念，默认每个页的大小为 16KB show variables like 'innodb_page_size';

InnoDB 存储引擎就是用 B+Tree 实现其索引结构。

索引大大减少了 MySQL 服务器需要扫描的数据量。(全表扫描)

索引可以帮助 MySQL 服务器避免排序和临时表。

索引可以将随机 I/O 变为顺序 I/O。

# 1、操作索引

添加 PRIMARY KEY（主键索引）

ALTER TABLE table_name ADD PRIMARY KEY ( column )
添加 UNIQUE(唯一索引)

ALTER TABLE table_name ADD UNIQUE ( column )
添加 INDEX(普通索引)

ALTER TABLE table_name ADD INDEX index_name ( column )
添加 FULLTEXT(全文索引)

ALTER TABLE table_name ADD FULLTEXT ( column)
添加多列索引

ALTER TABLE table_name ADD INDEX index_name ( column1, column2, column3 )
重建索引，在常规的数据库维护操作中经常使用，实质上的对表的修复。

repair table index_test quick;
查询索引

show index from|in table_name
删除索引

DROP INDEX idx on table_name;

CREATE [UNIQUE/FULLTEXT] INDEX <索引名> ON <表名>(<列名>)
ALTER TABLE <表名> ADD INDEX|UNIQUE|PRIMARY KEY|FULLTEXT <索引名>(<列名>)

DROP INDEX <索引名> ON <表名>
ALTER TABLE <表名> DROP INDEX <索引名>

1
2
3
4
5

# 四、日志

MySQL日志有很多种，比较重要的有二进制日志bin log（归档日志）和事务日志redo log（重做日志）和 undo log（回滚日志）

bin log MySQL服务器层实现
redo log InnoDB引擎实现
undo log InnoDB引擎实现

MySQL InnoDB 引擎使用 redo log(重做日志) 保证事务的持久性，使用 undo log(回滚日志) 来保证事务的原子性。
MySQL数据库的数据备份、主备、主主、主从都离不开binlog，需要依靠binlog来同步数据，保证数据一致性。

✔️MySQL误操作delete from...的后续，使用binlog恢复数据

# 4.1 bin-log

即二进制日志,它记录了数据库上的所有改变，并以二进制的形式保存在磁盘中

它可以用来查看数据库的变更历史、数据库增量备份和恢复、Mysql的复制（主从数据库的复制）。

三种模式：

Row：基于行的复制
Statement：基于SQL语句的复制
Mixed：混合模式复制，实际上就是Statement与Row的结合

查看bin-log是否开启：SHOW VARIABLES LIKE 'log_bin';

如果结果为OFF,则没有开启，开启方式为修改mysql配置文件,重启MySQL服务 systemctl restart mysqld

[mysqld]
log-bin=mysql-bin
binlog-format=MIXED

# 补充其他配置信息
#保存日志的位置，实际生成的日志会在后面添加对应的标志
log-bin = /var/lib/mysql/mysql-bin
#日志保存天数
expire-logs-days = 14
#日志大小
max-binlog-size = 500M
#用于同步时区分mysql实例，唯一，5.7以上版本必须填写
server-id = 1
#日志格式，MIXED，ROW,STATEMENT
binlog_format ='MIXED'

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

查看binlog日志信息： SHOW BINARY LOGS;
查看目前使用的binlog文件： SHOW MASTER STATUS;
查看指定binlog文件内容: SHOW BINLOG EVENTS IN 'MYSQL-BIN.000029';
刷新log日志，自此刻开始产生一个新编号的binlog日志文件 flush logs;
命令查看 mysqlbinlog --no-defaults --base64-output=decode-rows -v mysql-bin.003271

# 五、数据类型

整数类型：bit、bool、tinyint、smallint、mediumint、int、bigint
浮点数类型：float、double、decimal
字符串类型：char、varchar、tinyblob、blob、mediumblob、longblob、tinytext、text、mediumtext、longtext
⽇期类型：Date、DateTime、TimeStamp、Time、Year
其他类型：JSON类型

# JSON类型

JSON 类型是 MySQL 5.7 版本新增的数据类型，用好 JSON 数据类型可以有效解决很多业务中实际问题。

推荐用 MySQL 8.0.17 以上的版本，性能更好
不要将有明显关系型的数据用 JSON 存储
JSON 数据类型推荐使用在不经常更新的静态数据

# 用户登录示例

新建一个表，测试JSON类型

DROP TABLE IF EXISTS user_login;

CREATE TABLE user_login(
	user_id BIGINT NOT NULL,
	login_info JSON,
	PRIMARY KEY(user_id)
);

1
2
3
4
5
6
7

插入测试数据

SET @a = '
{
   "cellphone" : "13918888888",
   "wxchat" : "破产码农",
   "QQ" : "82946772"
}
';

INSERT INTO user_login VALUES (1, @a);

SET @b = '
{
	"cellphone" : "15026888888"
}
';

INSERT INTO user_login VALUES (2, @b);

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

查询数据

SELECT user_id, 
	JSON_UNQUOTE(JSON_EXTRACT(login_info, "$.cellphone")) as cellphone,
	JSON_UNQUOTE(JSON_EXTRACT(login_info, "$.wxchat")) as wxchat,
	JSON_UNQUOTE(JSON_EXTRACT(login_info, "$.QQ")) as qq
FROM user_login;
-- 每次写 JSON_EXTRACT、JSON_UNQUOTE 非常麻烦，MySQL 还提供了 ->> 表达式
SELECT user_id, 
	login_info->>"$.cellphone" cellphone,
	login_info->>"$.wxchat" wxchat,
	login_info->>"$.QQ" qq
FROM user_login;

1
2
3
4
5
6
7
8
9
10
11

json数据量大时，建立索引
```
ALTER TABLE user_login ADD COLUMN cellphone VARCHAR(255) AS (login_info->>"$.cellphone");
ALTER TABLE user_login ADD UNIQUE INDEX idx_cellphone(cellphone);
```
1
2
上述 SQL 首先创建了一个虚拟列 cellphone，这个列是由函数 loginInfo->>"$.cellphone" 计算得到的。然后在这个虚拟列上创建一个唯一索引 idx_cellphone。这时再通过虚拟列 cellphone 进行查询，就可以看到优化器会使用到新创建的 idx_cellphone 索引
```
EXPLAIN SELECT * FROM user_login WHERE cellphone = '13918888888'\G
```
1
\G:将查询到的横向表格纵向输出，方便阅读

我们可以在一开始创建表的时候，就完成虚拟列及函数索引的创建。如下表创建的列 cellphone 对应的就是 JSON 中的内容，是个虚拟列；uk_idx_cellphone 就是在虚拟列 cellphone 上所创建的索引
```
CREATE TABLE user_login (
	user_id BIGINT,
	login_info JSON,
	cellphone VARCHAR(255) AS (login_info->>"$.cellphone"),
	PRIMARY KEY(user_id),
	UNIQUE KEY uk_idx_cellphone(cellphone)
);
```
1
2
3
4
5
6
7

# 用户画像示例

创建表

CREATE TABLE Tags (
	tagId bigint auto_increment,
	tagName varchar(255) NOT NULL,
	primary key(tagId)
);

1
2
3
4
5

查询标签

SELECT * FROM Tags;
+-------+--------------+
| tagId | tagName      |
+-------+--------------+
|     1 | 70后         |
|     2 | 80后         |
|     3 | 90后         |
|     4 | 00后         |
|     5 | 爱运动       |
|     6 | 高学历       |
|     7 | 小资         |
|     8 | 有房         |
|     9 | 有车         |
|    10 | 常看电影     |
|    11 | 爱网购       |
|    12 | 爱外卖       |
+-------+--------------+

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

若不用 JSON 数据类型进行标签存储，通常会将用户标签通过字符串，加上分割符的方式，在一个字段中存取用户所有的标签：

+-------+---------------------------------------+
|用户    |标签                                   |
+-------+---------------------------------------+
|David  |80后 ； 高学历 ； 小资 ； 有房 ；常看电影   |
|Tom    |90后 ；常看电影 ； 爱外卖                 |
+-------+---------------------------------------

1
2
3
4
5
6

这样做的缺点是：不好搜索特定画像的用户，另外分隔符也是一种自我约定，在数据库中其实可以任意存储其他数据，最终产生脏数据。

DROP TABLE IF EXISTS UserTag;
CREATE TABLE UserTag (
	userId bigint NOT NULL,
	userTags JSON,
	PRIMARY KEY (userId)
);

INSERT INTO UserTag VALUES (1,'[2,6,8,10]');
INSERT INTO UserTag VALUES (2,'[3,10,12]');

1
2
3
4
5
6
7
8
9

MySQL 8.0.17 版本开始支持 Multi-Valued Indexes，用于在 JSON 数组上创建索引，并通过函数 member of、json_contains、json_overlaps 来快速检索索引数据。所以你可以在表 UserTag 上创建 Multi-Valued Indexes：
```
ALTER TABLE UserTag
ADD INDEX idx_user_tags ((cast((userTags->"$") as unsigned array)));
```
1
2

如果想要查询用户画像为常看电影的用户，可以使用函数 MEMBER OF：

EXPLAIN SELECT * FROM UserTag 
WHERE 10 MEMBER OF(userTags->"$")\G
*************************** 1. row ***************************
		   id: 1
  select_type: SIMPLE
		table: UserTag
   partitions: NULL
		 type: ref
possible_keys: idx_user_tags
		  key: idx_user_tags
	  key_len: 9
		  ref: const
		 rows: 1
	 filtered: 100.00
		Extra: Using where
1 row in set, 1 warning (0.00 sec)

SELECT * FROM UserTag 
WHERE 10 MEMBER OF(userTags->"$");
+--------+---------------+
| userId | userTags      |
+--------+---------------+
|      1 | [2, 6, 8, 10] |
|      2 | [3, 10, 12]   |
+--------+---------------+
2 rows in set (0.00 sec)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

如果想要查询画像为 80 后，且常看电影的用户，可以使用函数 JSON_CONTAINS：

EXPLAIN SELECT * FROM UserTag 
WHERE JSON_CONTAINS(userTags->"$", '[2,10]')\G
*************************** 1. row ***************************
		   id: 1
  select_type: SIMPLE
		table: UserTag
   partitions: NULL
		 type: range
possible_keys: idx_user_tags
		  key: idx_user_tags
	  key_len: 9
		  ref: NULL
		 rows: 3
	 filtered: 100.00
		Extra: Using where
1 row in set, 1 warning (0.00 sec)

SELECT * FROM UserTag 
WHERE JSON_CONTAINS(userTags->"$", '[2,10]');
+--------+---------------+
| userId | userTags      |
+--------+---------------+
|      1 | [2, 6, 8, 10] |
+--------+---------------+
1 row in set (0.00 sec)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

如果想要查询画像为 80 后、90 后，且常看电影的用户，则可以使用函数 JSON_OVERLAP：

EXPLAIN SELECT * FROM UserTag 
WHERE JSON_OVERLAPS(userTags->"$", '[2,3,10]')\G
*************************** 1. row ***************************
		   id: 1
  select_type: SIMPLE
		table: UserTag
   partitions: NULL
		 type: range
possible_keys: idx_user_tags
		  key: idx_user_tags
	  key_len: 9
		  ref: NULL
		 rows: 4
	 filtered: 100.00
		Extra: Using where
1 row in set, 1 warning (0.00 sec)

SELECT * FROM UserTag 
WHERE JSON_OVERLAPS(userTags->"$", '[2,3,10]');
+--------+---------------+
| userId | userTags      |
+--------+---------------+
|      1 | [2, 6, 8, 10] |
|      2 | [3, 10, 12]   |
+--------+---------------+
2 rows in set (0.01 sec)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

# 三、MySQL语句

# 3.1 基础操作

登录MySQL

- 连接服务器
  mysql -h 地址 -P 端口 -u 用户名 -p 密码
- 查看当前数据库
  select database();
- 显示当前时间、用户名、数据库版本
  select now(), user(), version();
- 未登录情况下查询MySQL版本
  mysql --version
  mysql -V

1
2
3
4
5
6
7
8
9

建/删/查库

show databases;                 -- 展示所有的库
create database testdb;         -- 创建testdb库
use testdb;                     -- 切换到testdb这个库
drop database testdb;           -- 删除testdb库

1
2
3
4

展示表 show tables;

查/创建/删除用户 用户信息存在于mysql.user表里

select user,host,authentication_string,plugin  from mysql.user;     -- 查用户
-- user表Host字段如果是localhost，说明root用户只能本地登录，现在把他改成远程登录
update user set host='%' where user='root';
FLUSH PRIVILEGES;

create user user01@'localhost' identified by 'password';            -- 创建user01,只能本地访问
create user user02@'%' identified by 'password'                     -- 创建user02,可以远程访问
drop user 'admin'@'%';                                              -- 删除用户

1
2
3
4
5
6
7
8

改密码

MySQL8.0之前的版本密码加密规则：mysql_native_password，MySQL8.0密码加密规则：caching_sha2_password

# 修改mysql加密规则
ALTER USER 'root'@'%' IDENTIFIED BY 'xxxxxx' PASSWORD EXPIRE NEVER;
ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'xxxxxx';
FLUSH PRIVILEGES;

set password for 'user01'@'localhost'=password('newpassword'); # 5.7可以 8.0不可以
set password for 'user01'@'localhost'='newpassword'; # 通用
alter user 'root'@'localhost' identified BY 'xxxxxx';

1
2
3
4
5
6
7
8

查/授权限

*.* 表示所有库所有权限 testdb.* testdb库下的所有权限

show grants for 'admin'@'%';                                           -- 查询用户的权限
grant all privileges on *.* to 'user01'@'%' identified by 'newpwd';    -- 授予所有权限
grant all privileges on testdb.* to 'user01'@'localhost';              -- 授予user01管理testdb的全部权限
grant select on *.* to 'user02'@'%' identified by 'newpassword';       -- 授予user02查看权限，并修改密码
revoke select on *.* from 'admin'@'%';                                 -- 撤销select权限
flush privileges;                                                      -- 刷新权限

1
2
3
4
5
6

查询参数变量 SHOW VARIABLES LIKE "group_concat_max_len%"; #查询最大值
查看表结构 DESC tablename SHOW CREATE TABLE tablename

# 3.2 分类

DQL（Data Query Language）：数据查询语⾔ select 相关语句
DML（Data Manipulate Language）：数据操作语⾔ insert、update、delete 语句
DDL（Data Define Languge）：数据定义语⾔ create、drop、alter 语句
DCL（Data Control Lanage）：数据控制语言 grant 语句
TCL（Transaction Control Language）：事务控制语⾔ set autocommit=0、start、transaction、savepoint、commit、rollback

# 1.DQL查询

select ...

# 查询mysql库里用户信息
select user,host,authentication_string,plugin from mysql.user;

1
2

# 2.DML数据操作语句

insert update delete ...

# 3.DDL数据定义语言

create drop alter ...

# 创建数据库
mysql> CREATE DATABASE IF NOT EXISTS admin DEFAULT CHARSET utf8 COLLATE utf8_general_ci;
Query OK, 1 row affected (0.01 sec)

mysql> show create database admin;
+----------+----------------------------------------------------------------+
| Database | Create Database                                                |
+----------+----------------------------------------------------------------+
| admin    | CREATE DATABASE `admin` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+----------------------------------------------------------------+
1 row in set (0.00 sec)
# 删库
mysql> DROP DATABASE IF EXISTS admin;
Query OK, 0 rows affected (0.00 sec)

1
2
3
4
5
6
7
8
9
10
11
12
13
14

# 4.DCL数据控制语句

grant ...

创建用户

mysql> create user user01@'%' identified by '123456';
Query OK, 0 rows affected (0.00 sec)

mysql> select user,host,authentication_string,plugin from mysql.user;
+---------------+-----------+-------------------------------------------+-----------------------+
| user          | host      | authentication_string                     | plugin                |
+---------------+-----------+-------------------------------------------+-----------------------+
| root          | %         | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B | mysql_native_password |
| mysql.session | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | mysql_native_password |
| mysql.sys     | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | mysql_native_password |
| user01        | %         | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | mysql_native_password |
+---------------+-----------+-------------------------------------------+-----------------------+
4 rows in set (0.00 sec)

1
2
3
4
5
6
7
8
9
10
11
12
13

查询用户权限

USAGE权限，只能登录MySQL

mysql> show grants for user01@'%';
+------------------------------------+
| Grants for user01@%                |
+------------------------------------+
| GRANT USAGE ON *.* TO 'user01'@'%' |
+------------------------------------+
1 row in set (0.00 sec)

mysql>

1
2
3
4
5
6
7
8
9

授权

授于DQL权限 select

grant 权限 on 数据库.数据表 to 用户@'主机';

eg: grant select,insert,update,delete on mysql.user to user01@'%';

  # 给user01用户授予查看mysql.user这个表的权限
  mysql> grant select on mysql.user to user01@'%';
  Query OK, 0 rows affected (0.00 sec)
  
  mysql> flush privileges;
  Query OK, 0 rows affected (0.00 sec)
  
  mysql> show grants for user01@'%';
  +------------------------------------------------+
  | Grants for user01@%                            |
  +------------------------------------------------+
  | GRANT USAGE ON *.* TO 'user01'@'%'             |
  | GRANT SELECT ON `mysql`.`user` TO 'user01'@'%' |
  +------------------------------------------------+
  2 rows in set (0.00 sec)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

# 3.3 函数

# 1.case when

如图，我们想实现这个数据查询，怎么做？

解决思路，CASE WHEN 实现

SELECT * FROM tb_stu_cause;

SELECT 姓名,
CASE WHEN 课程 = '语文' THEN 分数 END AS '语文',
CASE WHEN 课程 = '数学'	THEN 分数 END AS '数学'
FROM tb_stu_cause;

SELECT 姓名,
MAX(CASE WHEN 课程 = '语文' THEN 分数 END) AS '语文',
MAX(CASE WHEN 课程 = '数学' THEN 分数 END) AS '数学'
FROM tb_stu_cause
GROUP BY 姓名;

1
2
3
4
5
6
7
8
9
10
11
12

# 2.load data

use base;
#查看是否开启加载本地文件
show variables like 'local_infile';
#开启全局本地文件设置
set global local_infile=on;

load data local infile './t_user.txt'
into table base.t_user 
fields terminated by ','
lines terminated by '\n'
ignore 1 lines;

select * from base.t_user;

1
2
3
4
5
6
7
8
9
10
11
12
13

# 3.常用函数

# 3.1 聚合函数

count sum avg max min

# 3.2 数学函数

   SELECT 
    abs(-8) 									AS '绝对值' 
   ,bin(10) 									AS '二进制'
   ,oct(10) 									AS '八进制'
   ,hex(10) 									AS '十六进制'
   ,pi() 										AS '圆周率'
   ,ceil(5.5) 									AS '大于x的最小整数值'
   ,floor(5.5) 								AS '小于x的最大整数值'
   ,greatest(13, 21, 34) 						AS '集合中最大的值'
   ,least(13, 21, 34, 41) 						AS '集合中最小的值'
   ,mod(1023, 10) 								AS '余数'
   ,rand() 									AS '返回0-1内的随机值'
   ,rand(9)  									AS '提供一个参数生成一个指定值'
   ,rand('test123')  							AS '提供一个参数生成一个指定值'
   ,round(1023.1023) 							AS '四舍五入'
   ,round(1023.1023, 3) 						AS '四舍五入,保留n位小数'
   ,round(1023.1023, -1)  						AS '四舍五入整数位'
   ,round(1025.1025, -1)  						AS '四舍五入整数位'
   ,truncate(1023.1023, 3) 					AS '截断为n位小数'
   ,truncate(1023.1023, -1)  					AS '截断为整数位'
   ,truncate(1025.1025, -1) 					AS '截断为整数位'
   ,sign(-6) 									AS '-1表示负数'
   ,sign(0) 									AS '0 表示零'
   ,sign(6) 									AS '1 表示正数'
   ,sqrt(10) 									AS '平方根'

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

# 3.3 字符串函数

# 连接字符串 'fx67ll'
   SELECT concat('f', 'x', '67', 'll');
   
   # 用分隔符连接字符串 'fx67ll'，注意如果分隔符为NULL，则结果为NULL
   SELECT concat_ws('-', 'fx', '6', '7', 'l', 'l');  # fx-6-7-l-l
   SELECT concat_ws(NULL, 'fx', '6', '7', 'l', 'l');  # NULL
   
   # 将字符串 'fx67ll' 从3位置开始的2个字符替换为 '78'
   SELECT insert('fx67ll', 3, 2, '78');  # fx78ll
   
   # 返回字符串 'fx67ll' 左边的3个字符：fx6
   SELECT left('fx67ll', 3);
   
   # 返回字符串 'fx67ll' 右边的4个字符: 67ll
   SELECT right('fx67ll', 4);
   
   # 返回字符串 'fx67ll' 第3个字符之后的子字符串：67ll
   SELECT substring('fx67ll', 3);
   
   # 返回字符串 'fx67ll' 倒数第3个字符之后的子字符串：7ll
   SELECT substring('fx67ll', -3);
   
   # 返回字符串 'fx67ll' 第3个字符之后的2个字符：67
   SELECT substring('fx67ll', 3, 2);
   
   # 切割字符串 ' fx67ll ' 两边的空字符，注意字符串左右有空格：'fx67ll'
   SELECT trim(' fx67ll ');
   
   # 切割字符串 ' fx67ll ' 左边的空字符：'fx67ll '
   SELECT ltrim(' fx67ll ');
   
   # 切割字符串 ' fx67ll ' 右边的字符串：' fx67ll'
   SELECT rtrim(' fx67ll ');
   
   # 重复字符 'fx67ll' 三次：fx67llfx67llfx67ll
   SELECT repeat('fx67ll', 3);
   
   # 对字符串 'fx67ll' 进行反向排序：ll76xf
   SELECT reverse('fx67ll');
   
   # 返回字符串的长度：6
   SELECT length('fx67ll');
   
   # 对字符串进行大小写处理，大小写各两种方式
   SELECT upper('FX67ll');  # FX67LL
   SELECT lower('fx67LL');  # fx67ll
   SELECT ucase('fX67Ll');  # FX67LL
   SELECT lcase('Fx67lL');  # fx67ll
   
   # 返回 'f' 在 'fx67ll' 中的第一个位置：1
   SELECT position('f' IN 'fx67ll');
   
   # 返回 '1' 在 'fx67ll' 中的第一个位置，不存在返回0：0
   SELECT position('1' IN 'fx67ll');
   
   # 比较字符串，第一个参数小于第二个返回负数，否则返回正数，相等返回0
   SELECT strcmp('abc', 'abd');  # -1
   SELECT strcmp('abc', 'abb');  # 1
   SELECT strcmp('abc', 'abc');  # 0

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59

# 3.4 时间函数

   # 返回当前日期，时间，日期时间
   SELECT current_date, current_time, now();
   
   # 返回当前时间的时，分，秒
   SELECT hour(current_time), minute(current_time), second(current_time);
   
   # 返回当前日期的年，月，日
   SELECT year(current_date), month(current_date), day(current_date);
   
   # 返回当前日期的季度
   SELECT quarter(current_date);
   
   # 返回当前月份的名称，当前星期的名称
   SELECT monthname(current_date), dayname(current_date);
   
   # 返回当前日在星期的天数，当前日在月的天数，当前日在年的天数
   SELECT dayofweek(current_date), dayofmonth(current_date), dayofyear(current_date);
   
   # 最近7天
   SELECT create_time FROM user WHERE DATE_SUB(CURDATE, INTERVAL 6 DAY) <= DATE(create_time)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

# 3.5 控制流函数

   # IF判断：1
   SELECT IF(2>1, '1', '0')  # 1
   
   # IFNULL判断
   # 判断第一个表达式是否为NULL，如果为NULL则返回第二个参数的值，否则返回第一个参数的值
   SELECT IFNULL(NULL, 1);  # 1
   SELECT IFNULL('fx67ll', 0);  # fx67ll
   
   # ISNULL判断
   # 接受1个参数，并测试该参数是否为NULL，如果参数为NULL，则返回1，否则返回0
   SELECT ISNULL(1);  # 0
   SELECT ISNULL(1/0);  # 1
   
   # NULLIF判断
   # 接受2个参数，如果第1个参数等于第2个参数，则返回NULL，否则返回第1个参数
   SELECT NULLIF('fx67ll', 'fx67ll');  # NULL
   SELECT NULLIF('fx67ll', 'll76xf');  # fx67ll
   
   # NULLIF类似于下面的CASE表达式
   CASE WHEN expression_1 = expression_2
      THEN NULL
   ELSE
      expression_1
   END;
   
   # CASE判断：second
   SELECT CASE 2
       WHEN 1 THEN 'first'
       WHEN 2 THEN 'second'
       WHEN 3 THEN 'third'
       ELSE 'other'
       END;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32

# 3.3 常用技巧

指定字段规则排序

按照星期日，星期一...星期六排序

    SELECT `week` 
    FROM t_user 
    ORDER BY FIELD(`week`,'星期天','星期一','星期二','星期三','星期四','星期五','星期六') ASC

1
2
3

分页查询

-- 如果查询user表中的前10条记录，并且是从第1条数据开始查，那么索引0可以省略不写，如下：
SELECT * FROM USER LIMIT 10;
-- 从第5条数据开始查，查3条记录（注意：第一条数据索引是0，第二条数据索引是1，以此类推）
SELECT * FROM USER LIMIT 4,3;
SELECT * FROM USER LIMIT 3 OFFSET 4; -- MySQL5.0后支持这种语法，结果和上面一样

1
2
3
4
5

# 四、存储过程

删除存储过程 DROP PROCEDURE IF EXISTS sp_report_user_total;
存储过程基本语句格式
```
DELIMITER //
CREATE PROCEDURE [数据库名].存储过程名([IN 参数 类型, OUT 变量名 类型, ...]) 
BEGIN
	-- 存储过程语句块 SELECT * FROM table_name
END //
DELIMITER ;
```
1
2
3
4
5
6
- DELIMITER // 讲默认分隔符转换为 //
- 存储过程中的参数有 IN(入参), OUT(出参), INOUT(既是入参也是出参)三种
- 存储过程语句必须在BEGIN和END之间

举例

创建存储过程sp_report_user_total()，统计user表某个平台用户总量。
输入平台名称 platform=pc/wechat/ios 输出 total

DELIMITER // 
CREATE PROCEDURE sp_report_user_total(IN platform VARCHAR(20), OUT total INT) 
BEGIN
    -- SQL SECURITY DEFINER 
    -- COMMENT '统计某个平台用户数量'
    SELECT COUNT(*) INTO total FROM tb_user t WHERE t.platform = platform;

END //
DELIMITER ;

1
2
3
4
5
6
7
8
9

调用存储过程

SET @total = 0;
SET @platform = 'wechat';
CALL sp_report_user_total(@platform, @total);
SELECT @total;

1
2
3
4

# 五、MySQL数据迁移和备份

# 5.0 表备份 MySQL快速创建表

-- 方式一，使用LIKE方式：会复制索引等信息
CREATE TABLE base.book_bak LIKE base.book;
INSERT INTO base.book_bak SELECT * FROM base.book;

-- 方式二，使用AS：建表，复制数据，不会指定主键和索引
CREATE TABLE base.book_bak AS SELECT * FROM base.book;

1
2
3
4
5
6

# 5.1 数据备份

mysqldump 命令格式

-- 使用“-d"选项，说明只保存数据库的表结构
mysqldump -uroot -p[密码] --databases 库名1 [库名2].. >/备份路径/备份文件名.sql
mysqldump -u root -p[密码] [-d] 库名 表名1 [表名2] ... > /备份路径/备份文件名.sql

1
2
3

参数	描述
-h	指定主机名
-P	指定端口
--all-databases/-A	导出全部数据库
--databases/-B	指定要备份的数据库
--no-data/-d	只导出表结构，忽略数据

-- 单个数据库备份
mysqldump -u username -p password dbname > dbname_bak.sql

-- 备份数据库dbname的表table1,table2 
mysqldump -u username -p password dbname table1 table2 > backup_name.sql

-- 多个数据库备份
mysqldump -u username -p passwords --databases dbname1 dbname2 > backup_name.sql

1
2
3
4
5
6
7
8

# 5.2 数据恢复

-- 使用mysqldump命令备份的数据库的语法如下
mysql -u username -p password dbname < backup_name.sql

-- 登录mysql后，命令恢复
mysql> source /data/backup_name.sql;

1
2
3
4
5

备份案例 - 备份远程MySQL库到本地

1、登录远程服务器

ssh root@192.168.198.128

2、MySQL在Docker里，进入Docker容器内部

docker ps
docker exec -it ufs_mysql bash

1
2

3、备份ufs库的所有表

mysqldump -uroot -proot --databases ufs > /ufs_`date +%Y%m%d`.sql
ls /

1
2

4、把备份的SQL脚本拷贝到宿主机root目录下

docker cp ufs_mysql:/ufs_20230905.sql /root

5、到本地服务器，登录MySQL创建库

CREATE DATABASE ufs DEFAULT CHARSET utf8mb4 COLLATE utf8mb4_general_ci;

6、导入脚本

mysql -uroot -proot ufs < ./Desktop/ufs_20230905.sql

# 5.3 数据备份脚本

可以设置定期执行脚本，cron是一个 Liunx 下的定时执行工具，使用自行查询

#!/bin/bash

#保存备份个数，备份31天数据
number=31
#备份保存路径
backup_dir=/root/mysqlbackup
#日期
dd=`date +%Y-%m-%d-%H-%M-%S`
#备份工具
tool=mysqldump
#用户名
username=root
#密码
password=123456
#将要备份的数据库
database_name=mydb

#如果文件夹不存在则创建
if [ ! -d $backup_dir ];
then     
    mkdir -p $backup_dir;
fi

#简单写法 mysqldump -u root -p123456 users > /root/mysqlbackup/users-$filename.sql
$tool -u $username -p$password $database_name > $backup_dir/$database_name-$dd.sql

#写创建备份日志
echo "create $backup_dir/$database_name-$dd.dupm" >> $backup_dir/log.txt

#找出需要删除的备份
delfile=`ls -l -crt $backup_dir/*.sql | awk '{print $9 }' | head -1`

#判断现在的备份数量是否大于$number
count=`ls -l -crt $backup_dir/*.sql | awk '{print $9 }' | wc -l`

if [ $count -gt $number ]
then
  #删除最早生成的备份，只保留number数量的备份
  rm $delfile
  #写删除文件日志
  echo "delete $delfile" >> $backup_dir/log.txt
fi

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42

# 5.4. 使用binlog恢复数据

害，今天写了条非常简单的sql语句 delete from t_xxx where id in ('a', 'b') ，然后跟往常一样喝了口82年的白开水，就打开navicat去执行这个代码。然鹅...我勒个去，卧槽、尼玛、fk、淦。你猜发生了什么... select * from t_xxx 竟然没数据，当时血压指数飙升，我就赶紧戴上我的八倍镜，仔细看了看。原因是执行的时候竟然没选中where就执行了...aka 炸了，我在情急之中突然想起有个binlog。于是乎就有了这篇文章，对于这种十万火急的事情，我直接先说解决办法。

二进制日志（binnary log）以事件形式记录了对MySQL数据库执行更改的所有操作

查看是否开启binlog
```
show variables like '%log_bin%';
```
1
查看数据库文件存放路径，bin-log路径
```
show variables like '%datadir%';
```
1

找到某个时间段的binlog，翻译成伪sql

mysqlbinlog --base64-output=decode-rows \
-v --database=base \
--start-datetime="2022-11-3 14:40:00" \
--stop-datetime="2022-11-3 14:45:00" \
"/var/lib/mysql/binlog.000003" > /tmp/mysqllog.sql

1
2
3
4
5

-v 表示显示伪SQL语句

--set-charset=utf8 表示设置编码

在生成的 mysqllog.sql 文件同路径下，生成将delete翻译为insert的sql文件

cat mysqllog.sql | sed -n '/###/p' | sed 's/### //g;s/\/\*.*/,/g;s/DELETE FROM/;INSERT INTO/g;s/WHERE/SELECT/g;' |sed -r 's/(@17.*),/\1;/g' | sed 's/@1=//g'| sed 's/@[1-9]=/,/g' | sed 's/@[1-9][0-9]=/,/g' > mysqllogOK.sql

打开生成的sql文件找到相应的insert，将翻译的insert语句复制到mysql里面执行

直接使用binlog数据恢复

mysqlbinlog -no-defaults --start-position=1089 --stop-position=1220 /var/lib/mysql/binlog.000003 | mysql -uroot -p123456

所以哈，操作一定要谨慎谨慎谨慎啊！然后做了下总结，看下面

不小心干了莫名其妙的操作之后，紧急处理思路，前提是必须开启binlog

# 1. 查看binlog日志， 
ll /usr/local/mysql/data | grep mysql-bin  
# 2. 备份binlog到/root/下
cp -v /usr/local/mysql/data/mysql-bin.000023 /root/ 
# 3. 重新开始新的binlog日志记录文件，不会再有后续日志写入mysql-bin.000023了
mysql> flush logs;  
mysql> show master status;
# 4. 读取binlog日志，分析问题
# 4.1 方式一：使用mysqlbinlog读取binlog日志
/usr/local/mysql/bin/mysqlbinlog  /usr/local/mysql/data/mysql-bin.000023
# 4.2 方式二：登录服务器，并查看(推荐)
mysql> show binlog events in 'mysql-bin.000023';  
# 通过分析，造成数据库破坏的pos点区间是介于 1437--1538 之间，只要恢复到1437前就可

/usr/local/mysql/bin/mysqlbinlog --start-datetime="2013-11-29 13:18:54" --stop-datetime="2013-11-29 13:21:53" --database=zyyshop /usr/local/mysql/data/mysql-bin.000021 | /usr/local/mysql/bin/mysql -uroot -p123456 -v zyyshop
# 所谓恢复，就是让mysql将保存在binlog日志中指定段落区间的sql语句逐个重新执行一次而已。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

# 六、MySQL统计

information_schema 存放了其他的数据库的信息
USE information_schema;
查询所有数据的大小
SELECT concat(round(sum(data_length/1024/1024),2),'MB') AS data FROM TABLES;
查询biubiu库的大小
SELECT concat(round(sum(data_length/1024/1024),2),'MB') AS data FROM TABLES WHERE table_schema='biubiu';
查看biubiu.tb_user的大小
SELECT concat(round(sum(data_length/1024/1024),2),'MB') AS data FROM TABLES WHERE table_schema='biubiu' AND table_name='tb_user';
查询mysql数据库中哪些表的数据量最大
SELECT table_schema,table_name,table_rows FROM tables ORDER BY table_rows ASC LIMIT 10;
查询某个表的数据量
SELECT table_schema,table_name,table_rows FROM tables WHERE table_schema='biubiu' AND table_name='sys_user' ORDER BY table_rows ASC LIMIT 10;

# 七、SQL执行流程

sql解析器：查询缓存 5.7 版本默认禁用，8.0版本直接删除了

检测SQL的正确性，是否符合DDL、DQL、DML、DCL等规则

针对不同的SQL分类，分发给不同的底层模块执行

比如接收到的是select，会先去~~cache~~里寻找

准备sql执行计划

执行sql计划，查询数据

数据找到放入~~cache~~,便于下次快速查找 -- 删除了

客户端 -> 处理连接 -> 查询缓存 -> 语法解析 -> 优化器 -> 执行器 -> 存储引擎InnoDB -> 文件系统

# 八、MySQL大屏监控

prometheus, Grafana, MySQLd exporter

需要这三个软件，win linux都有，我阿里云盘有

# 1.MySQLd exporter

mysqld_exporter-0.14.0.windows-amd64文件夹下，需要配置下MySQL客户端

# my.cnf
[client]
user=root
password=root
host=localhost
port=3306

1
2
3
4
5
6

启动 mysqld_exporter.exe --config.my-cnf=./my.cnf
访问 http://ip:9104

# 2.prometheus

修改普罗米修斯配置文件,新增数据采集端 mysql http://ip:9090

  # prometheus.yml
  - job_name: "mysql"
    static_configs:
      - targets: ["localhost:9104"]

1
2
3
4

# 3.Grafana

http://ip:3000

初始密码：admin/admin

添加prometheus数据源

MySQL仪表盘 https://grafana.com/grafana/dashboards/7362-mysql-overview/

中国省市区SQL表数据

# MySQL学习笔记

# 一、MySQL各种安装姿势

# 1.win10

# 1.1 下载MySQL Community Server (opens new window)

# 1.2 解压放到指定路径下：如下

# 1.3 准备配置文件 my.ini

# 1.4 配置环境变量

# 1.5 初始化数据库，安装服务，启动

# 1.6 安装完成，修改密码

# 2.CentOS-二进制方式安装-多实例

# 2.1 下载二进制包 解压到/usr/local/mysql

# 2.2 创建mysql用户

# 2.3 准备多实例目录

# 2.4 准备多个配置文件

# 2.5 准备mysql启停止脚本

# 2.6 用户、组授权：降低权限，全部赋予mysql

# 2.7 Path配置

# 2.8 创建多个实例对应得数据目录

# 4.9 mysql多实例初始化

# 4.10 启动3306 3307数据库

# 4.11 登录数据库

# 4.12 改密码

# 4.13 开机自启(可忽略，多种方法都可)

# 3.CentOS-rmp包安装

# 3.1 下载安装包

# 3.2 解压安装

# 4.yum方式：一般配置阿里云镜像

# 5.源码编译安装

# 5.1 环境准备

# 5.2 安装步骤

# 6.Docker方式安装

# 二、远程连接

# 2.1 确认数据库是否允许远程连接

# 2.2 查看防火墙是否开放端口

# 2.3 远程工具

# 三、MySQL概念的东西

# 一、存储引擎

# 二、事务

# 三、索引

# 1、操作索引

# 四、日志

# 4.1 bin-log

# 五、数据类型

# JSON类型

# 用户登录示例

# 用户画像示例

# 三、MySQL语句

# 3.1 基础操作

# 3.2 分类

# 1.DQL查询

# 2.DML数据操作语句

# 3.DDL数据定义语言

# 4.DCL数据控制语句

# 3.3 函数

# 1.case when

# 2.load data

# 3.常用函数

# 3.1 聚合函数

# 3.2 数学函数

# 3.3 字符串函数

# 3.4 时间函数

# 3.5 控制流函数

# 3.3 常用技巧

# 四、存储过程

# 五、MySQL数据迁移和备份

# 5.0 表备份 MySQL快速创建表

# 5.1 数据备份

# 5.2 数据恢复

# 5.3 数据备份脚本

# 5.4. 使用binlog恢复数据

# 六、MySQL统计

# 七、SQL执行流程

# 八、MySQL大屏监控

# 1.MySQLd exporter

# 2.prometheus

# 3.Grafana

# 1.3 准备配置文件 `my.ini`

# 2.1 下载二进制包解压到/usr/local/mysql