MySQL常用命令 – 乘风破浪

启动与关闭MySQL
- 单实例MySQL启动方法

/etc/init.d/mysqld start #启动

ss -lnt|grep 3306 #查看端口

[root@nfs ~]# ps -ef|grep 3306|grep -v grep #查看进程

root 6743 1 2 18:24 pts/1 00:00:00 /bin/sh /application/mysql/bin/mysqld_safe –defaults-file=/data/3306/my.cnf

mysql 7501 6743 5 18:24 pts/1 00:00:00 /application/mysql-5.5.48/bin/mysqld –defaults-file=/data/3306/my.cnf –basedir=/application/mysql –datadir=/data/3306/data –plugin-dir=/application/mysql/lib/plugin –user=mysql –log-error=/data/3306/mysql_oldboy3306.err –open-files-limit=1024 –pid-file=/data/3306/mysqld.pid –socket=/data/3306/mysql.sock –port=3306

提示：nginx master（管理进程）worker（工作进程）

MySQL启动基本原理说明

/etc/init.d/mysqld是一个shell启动脚本，启动后最终会调用mysqld_safe脚本最后调用mysqld主程序启动mysql，如下，/etc/init.d/mysqld脚本中调用mysqld_safe的程序。

cat support-files/mysql.server #mysql自带启动脚本

$bindir/mysqld_safe –datadir=”$datadir” –pid-file=”$mysqld_pid_file_path” $other_args >/dev/null 2>&1 &

初始化数据库时MySQL系统输出给出的启动方法

mysqld_safe –user=mysql &

单实例mysql关闭方法

/etc/init.d/mysqld stop

ss -lnt|grep 3306

cat support-files/mysql.server #mysql自带启动脚本

mysqld_pid=`cat “$mysqld_pid_file_path”`

kill $mysqld_pid

强制关闭数据库方法：

ki1lall mysqld

pki11 mysqld

killall -9 mysqld

kill -9 pid

有关killall,kill,pkill等进程管理命令看总结

强调：尽量不要野蛮粗鲁杀死数据库，生产高并发环境可能会引起数据丢失。

野蛮粗鲁杀死数据库导致故障企业案例：

http://oldboy.blog.5lcto.com/2561410/1431161

http://oldboy.blog.5Icto.com/2561410/1431172

http://www.cnblogs.com/peida/archive/2012/12/20/2825837.html

优雅关闭数据库方法

法一：mysq1admin -uroot -poldboy123 shutdown

法二：/etc/init.d/mysqld stop

法三：kill -USR2 `cat path/pid`

mysql多实例启动与关闭方法

/bin/sh ${CmdPath}/mysqld_safe –defaults-file=/data/${port}/my.cnf 2>&1 > /dev/null &

${CmdPath}/mysqladmin -u ${mysql_user} -p${mysql_pwd} -S /data/${port}/mysql.sock shutdown

登录mysql及密码设置
- 几种登录方式

mysql ##刚装完mysql，无密码登录方式。该方法默认使用root用户, 可使用select user();查看当前用户，默认为root@localhost

mysql -S /tmp/mysql.sock -u root -p密码 #该方法适用于在安装MySQL主机上进行本地登录

mysql -h 127.0.0.1 -u root -p密码 #标准的dba命令行登录方式，使用’root’@’127.0.0.1’这个用户登录，非脚本中一般不用这样登录

mysql -h localhost -u root -p 密码 #该方式等价与【方式二】，且和【方式三】属于两个不同的“用户”

登陆后默认提示符是：mysql>,这个提示符也是可以改的，就像linux命令行提示符

命令行修改登录提示符

mysql> prompt \u@oldboy \r:\m:\s->

PROMPT set to ‘\u@oldboy \r:\m:\s->’

root@oldboy 07:50:21->

配置文件修改登录提示符

在my.cnf配置文件中[mysql］模块下添加如下内容（注意，不是[mysqld]),保存后，无需重启mysql，退出当前session，重新登录即可。如果在my.cnf配置文件中加，可以用\\，避免转义带来的问题。

[mysql]

prompt=\\u@oldboy \\r:\\m:\\s->

多实例My5QL登录
- 多实例My5QL本地登录

mysql -uroot -p -S /data/3306/mysql.sock

多实例的远程连接

mysql -uroot -p -h 192.168.10.106 -P3307

注意：多实例的远程连接无需指定Sock路径

免密码登录
- 方式一cnf增加[client]标签

[client]

user=”root”

password=”你的密码”

#单独定义不同的客户端

[mysql] # 这个是给/usr/loca/mysql/bin/mysql 使用的

user=root

password=”你的密码”

[mysqladmin] # 这个是给/usr/local/mysql/bin/mysqladmin使用的

user=root

password=”你的密码”

每个不同的客户端需要定义不同的标签，使用[client]可以统一

方式二 login-path

mysql> set password=password(‘123456’); #设置mysql登录密码

[root@node2 mysql]# ./bin/mysql_config_editor set -G vm1 -u root -p

Enter password:

[root@node2 mysql]# ./bin/mysql_config_editor print –all

[vm1]

user = root

password = *****

[root@node2 mysql]# ./bin/mysql –login-path=vm1 #这样登录就不需要密码，且文件二进制存储 ,位置是 ~/.mylogin.cnf

[root@node2 mysql]# ls ~/.mylogin.cnf

/root/.mylogin.cnf

该方式相对安全。如果server被黑了，该二进制文件还是会被破解

方式三 ~/.my.cnf, 自己当前家目录

#Filename: ~/.my.cnf

[client]

user=”root”

password=”你的密码”

退出MySQL数据库方法

quit (\q) Quit mysql.

exit (\q) Exit mysql. Same as quit.

ctrl+c或ctrl+d

善用MySQL的帮助命令help

MySQL中的help命令和linux命令行的man是类似的，和linux下查看内置命令的help是同名的。要想查看MySQL中的命令使用语法，就需要用help,help后面接相关命令及命令组合即可，例如：help create;默认情况MySQL中的命令执行不区分大小写，这点和linux命令行严格区分大小写是有区别的。

mysql命令行支持快捷键，同linux命令行

设置及修改mysql用户密码
- MySQL数据库用户安全策略介绍

安装mysql数据库后，默认的管理员root密码为空，这很不安全。因此需设置一个个密码。其实在安装My5QL单实例后，我们已经做了一些安全措施：

a．为root设置比较复杂密码。

b．删除无用的mysql库内的用户账号。

c．删除默认存在的teSt数据库。

d．增加用户的时候，尽量授权的权限最小，允许访问的主机范围最小。

除了上面的方法，针对My5QL数据库的用户处理，我们还有更严格的做法如下。

更安全的措施例如删除root，添加新的管理员用户

删除所有mysql中的用卢，包括root超级用户

delete from mysql.user;

增加system并提升为超级管理员，即和root等价的管理员用户，只是名字不同。

grant all privileges on *.* to system@’localhost’ identified by ‘123456’ with grant option;

flush privileges;

为管理员root用户设置密码

mysqladmin -uroot password ‘1234567’ #新安装的mysql设置密码

mysqladmin -uroot -p123456 password ‘1234567’ -S /data/3306/mysql.sock #修改密码

update mysql.user set password=password(‘oldboy’) where user=’root’ and host=’localhost’;

set password=password(‘123456’); #修改当前用户

找回丢失的mysql root用户密码

修改丢失的MySQL单实例root密码方法

/etc/init.d/mysqld stop #首先停止mysql

mysqld_safe –skip-grant-tables –user=mysql & #忽略授权登录验证启动

mysql #空密码登录mysql

update mysql.user set password=password(‘123456′) where user=’root’ and host=’localhost’;

flush privileges;

quit

/etc/init.d/mysqld stop

/etc/init.d/mysqld start

提示：启动时加–skip-grant-tables参数启动登录修改完密码后一定要重启再对外提供服务。

修改丢失的MySQL多实例root密码方法

/data/3306/mysql stop

mysqld_safe –defaults-file=/data/3306/my.cnf –skip-grant-table &

mysql -uroot -S /data/3306/mysql.sock

update mysql.user set password=password(‘123456′) where user=’root’ and host=’localhost’;

flush privileges;

quit

/data/3306/mysql restart

MySQL参数介绍和设置
- 参数的分类

全局参数：GLOBAL

可修改参数
不可修改参数

mysql> show session variables;

会话参数：SESSION

可修改参数
不可修改参数

mysql> show global variables;

用户可在线修改非只读参数，只读参数只能预先在配置文件中进行设置，通过重启数据库实例,方可生效。
所有的在线修改过的参数(GLOBAL/SESSION)，在重启后，都会丢失，不会写如my.cnf，无法将修改进行持久化
有些参数，即存在于GLOBAL又存在于SESSION, 比如autocommit (PS：MySQL默认是提交的)
- 查看参数

show variables; # 显示当前mysql的所有参数，且无隐藏参数

show variables like “max_%”; #查以max_开头的变量

设置参数

设置全局(GLOBAL)参数

mysql> set global slow_query_log = off; #不加global，会提示错误，slow_query_log是全局参数

mysql> set slow_query_log = off; # 下面就报错了，默认是会话参数

ERROR 1229 (HY000): Variable ‘slow_query_log’ is a GLOBAL variable and should be set with SET GLOBAL

设置会话(SESSION)参数

mysql> set autocommit = 0; # 当前会话生效。或者

mysql> set session autocommit = 0; # 当前会话生效

autocommit同样在GLOBAL中, 也有同样的参数

注意：如果这个时候/etc/init.d/mysqld restart, 则全局的autocommit的值会变成默认值，或者依赖于my.cnf的设置值。

执行的效果如下：

mysql> show variables like “slow%”; # 原值为ON

+———————+———-+

| Variable_name | Value |

+———————+———-+

| slow_launch_time | 2 |

| slow_query_log | OFF |

| slow_query_log_file | slow.log |

+———————+———-+

3 rows in set (0.00 sec)

mysql> select @@session.autocommit; # 等价于 slect @@autocomit;

+———————-+

| @@session.autocommit |

+———————-+

| 0 |

+———————-+

1 row in set (0.00 sec)

mysql> select @@global.autocommit;

+———————+

| @@global.autocommit |

+———————+

| 1 |

+———————+

1 row in set (0.00 sec)

权限管理
- “用户 + IP”的概念

MySQL中同一个用户名，比如db,能否登录，以及用什么密码登录，可以访问什么库等等，都需要加上IP，才可以表示一个完整的用户标识

db@127.0.0.1 和 db@loalhost 以及 db@192.168.1.100 这三个其实是不同的用户标识，即不同的用户名

用户权限管理

系统表权限信息

a) 用户名和IP是否允许
b) 查看user表 // 查看全局所有库的权限
c) 查看db表 // 查看指定库的权限
d) 查看table_priv表 // 查看指定表的权限
e) 查看column_priv表 // 查看指定列的权限

常用权限：

SQL语句：SELECT、INSERT、UPDATE、DELETE、INDEX
存储过程：CREATE ROUTINE、ALTER ROUTINE、EXECUTE、TRIGGER
管理权限：SUPER、RELOAD、SHOW DATABASE、SHUTDOWN、
所有权限

可选资源:

MAX_QUERIES_PER_HOUR count

MAX_UPDATES_PER_HOUR count

MAX_CONNECTIONS_PER_HOUR count

MAX_USER_CONNECTIONS count

tips:只能精确到小时，对于部分场景不适用，可以考虑中间件方式

显示当前用户的权限

#这三个是同一个意思

mysql> show grants;

mysql> show grants for current_user;

mysql> show grants for current_user();

授权基本操作

mysql> create user ‘db’@’127.0.0.1’ identified by ‘123’; #创建一个认证用户为’db’@’127.0.0.1′,密码是123

mysql> grant all on sysbench.* to ‘sbb’@’127.0.0.1’; #授予他sysbench库下面所有表的所有访问权限; *.*表示所有库的所有表

mysql> grant all on sysbench.* to ‘alex’@’127.0.0.1’ identified by ‘123456’; #这个grant语句会搜索用户，如果用户不存在，则自动创建用户，如果不带identified by, 则该用户名密码为空

mysql> grant all on *.* to ‘gcdb’@’192.168.24.%’ identified by ‘iforgot’ with grant option;

#表示这个用户’gcdb’@’192.168.24.%’可以访问所有库的所有表，同时，他还可以给其他用户授予权限(with grant option)，注意如果，*.*改成了某一个指定的非USER库，则gcdb没法去新建其他用户了，因为User库没有权限了,192.168.24.% 表示属于192.168.24.0/24网段的用户可以访问

撤销权限

revoke 关键字，该关键字只删除用户权限，不删除用户
revoke 语法同grant一致, 从grant … to 变为revoke … from
- 授权和用户管理
GRANT与创建用户

mysql> grant select on sysbench.* to ‘fanghao’@’localhost’ identified by ‘haoshijiwudi’;

Query OK, 0 rows affected, 2 warnings (0.00 sec) #这里有2个warning

mysql> show warnings; #输入warning的Message如下：

| Warning | 1285 | MySQL is started in –skip-name-resolve mode; you must restart it without this switch for this grant to work |

| Warning | 1287 | Using GRANT for creating new user is deprecated and will be removed in future release.Create new user with CREATE USER statement. |

–第一条的意思，跳过域名解析模式，暂时不管

–第二条的意思是使用GRANT赋权限的同时创建’fanghao’@’localhost’这个用户，但是出现了warning，从给出的提示看来，以后的MySQL版本会废弃掉这种方式

–正确的创建用户并赋权的方式:

mysql> create user ‘fanghao’@’localhost’ identified by ‘haoshijiwudi’;

Query OK, 0 rows affected (0.00 sec)

mysql> grant select on sysbench.* to ‘fanghao’@’localhost’;

Query OK, 0 rows affected (0.01 sec)

查看某一个用户的权限

mysql> show grants for ‘fanghao’@’localhost’;

+——————————————————————————————-+

| Grants for fanghao@localhost |

+——————————————————————————————-+

| GRANT USAGE ON *.* TO ‘fanghao’@’localhost’ IDENTIFIED BY PASSWORD ‘*1A7B1D37BD41047EC4F’ |

| GRANT SELECT PRIVILEGES ON `sysbench`.* TO ‘fanghao’@’localhost’ |

+——————————————————————————————-+

2 rows in set (0.00 sec)

–USAGE表示用户可以登录

–对sysbench库的所有表有SELECT权限

删除某一个用户

mysql> drop user ‘fanghao’@’localhost’;

Query OK, 0 rows affected (0.00 sec)

MySQL权限信息

mysql> select * from mysql.user where user=’fanghao’ \G;

*********************** 1. row ***************************

Host: localhost

User: fanghao #由于fanghao用户是对sysbench库有权限，所以这里（USER）全是N

Select_priv: N

Insert_priv: N

……

password_last_changed: 2017-11-22 16:31:57

password_lifetime: NULL

account_locked: N — 如果这里为Y表示被锁定，该用户就无法使用了

1 row in set (0.01 sec)

mysql> select * from mysql.db where user=’fanghao’ \G;

*************************** 1. row ***************************

Host: localhost –授权主机

Db: sysbench –sysbench数据库

User: fanghao –用户名

Select_priv: Y –授权的select权限

Insert_priv: N

Update_priv: N

……

Trigger_priv: N –注意：不建议使用INSERT或者update对元数据表进行修改，来达到修改权限的目的

1 row in set (0.01 sec)

–使用fanghao账号登陆

mysql> select user();

+——————-+

| user() |

+——————-+

| fanghao@localhost |

+——————-+

1 row in set (0.00 sec)

mysql> show databases;

+——————–+

| Database |

+——————–+

| information_schema | — 这是一个统计信息的数据库，use可以进去，部分表没有权限查询

| sysbench |

+——————–+

2 rows in set (0.00 sec)

mysql> select * from views;

Empty set (0.00 sec)

mysql> select * from innodb_cmp;

ERROR 1227 (42000): Access denied; you need (at least one of) the PROCESS privilege(s) for this operation

MySQL模拟角色
- 角色的定义

角色(Role)可以用来批量管理用户，同一个角色下的用户，拥有相同的权限。 7.X以后可以模拟角色(Role)的功能，通过mysql.proxies_priv模拟实现。
proxies_priv在5.5.X和5.6.X的时候就存在，但是无法模拟角色(Role)功能。
- 配置proxy

mysql> show variables like “%proxy%”; # #查看当前proxy是否开启，下图表示没有开启

+———————————–+——-+

| Variable_name | Value |

+———————————–+——-+

| check_proxy_users | OFF |

| mysql_native_password_proxy_users | OFF |

| proxy_user | |

| sha256_password_proxy_users | OFF |

+———————————–+——-+

4 rows in set (0.01 sec)

mysql> set global check_proxy_users =on; #开启proxy

Query OK, 0 rows affected (0.00 sec)

mysql> set global mysql_native_password_proxy_users = on;

Query OK, 0 rows affected (0.00 sec)

以上设置参数，对当前会话无效，需要退出后重新登录，或直接设置到my.cnf中去

模拟角色操作

mysql> create user ‘shiji_dba’@’localhost’; #相当于定于一个老司机角色(Role),但这只是个普通的用户，名字比较有(Role)的感觉,有点类似用户组

Query OK, 0 rows affected (0.00 sec)

mysql> GRANT SELECT ON `sysbench`.* TO ‘shiji_dba’@’localhost’; #授权select权限

Query OK, 0 rows affected (0.00 sec)

mysql> create user ‘bangwen’@’localhost’; #测试用户（邦文袍哥）

Query OK, 0 rows affected (0.00 sec)

mysql> create user ‘xuge’@’localhost’; #测试用户（许哥小司机）

Query OK, 0 rows affected (0.00 sec)

mysql> grant proxy on ‘shiji_dba’@’localhost’ to ‘bangwen’@’localhost’; #将shiji_dba的权限映射(map)给bangwen

Query OK, 0 rows affected, 2 warnings (0.00 sec)

mysql> grant proxy on ‘shiji_dba’@’localhost’ to ‘xuge’@’localhost’; #将shiji_dba的权限映射(map)给xuge

Query OK, 0 rows affected, 3 warnings (0.00 sec)

mysql> show grants for ‘shiji_dba’@’localhost’; #查看shiji_dba角色赋予的实际权限

+———————————————————+

| Grants for shiji_dba@localhost |

+———————————————————+

| GRANT USAGE ON *.* TO ‘shiji_dba’@’localhost’ |

| GRANT SELECT ON `sysbench`.* TO ‘shiji_dba’@’localhost’ |

+———————————————————+

2 rows in set (0.01 sec)

mysql> show grants for ‘bangwen’@’localhost’; #查看bangwen老司机的权限

+—————————————————————–+

| Grants for bangwen@localhost |

+—————————————————————–+

| GRANT USAGE ON *.* TO ‘bangwen’@’localhost’ |

| GRANT PROXY ON ‘shiji_dba’@’localhost’ TO ‘bangwen’@’localhost’ |

+—————————————————————–+

2 rows in set (0.00 sec)

mysql> select * from mysql.proxies_priv; #查看 proxies_priv的权限

+———–+———+————–+————–+————+—————-+———————+

+———–+———+————–+————–+————+—————-+———————+

+———–+———+————–+————–+————+—————-+———————+

2 rows in set (0.00 sec)

总结mysql.proxies_priv仅仅是对Role的模拟，和Oracle的角色还是有所不同.官方称呼为Role like

Workbench与Utilities介绍
- Workbench功能概述

SQL语句格式化 SQL关键字upcase MySQL Dashboard SQL语法提示 ER图 Forward Engine //ER图 –> DB表结构 Reverse //DB表结构 –> ER图

Utilities介绍和安装

MySQL Utilities介绍

MySQL Utilities 提供一组命令行工具用于维护和管理 MySQL 服务器，包括：

管理工具 (克隆、复制、比较、差异、导出、导入)
复制工具 (安装、配置)
一般工具 (磁盘使用情况、冗余索引、搜索元数据)

MySQL Utilities是一系列的命令行工具以及Python库更容易完成管理的任务。库是用Python语言写的，这就意味着不需要安装其他任何工具和库。当前是基于Python2.6版本设计的，不支持Python3.1版本.

MySQL Utilities提供了各种平台的软件包，如果没有找到对应自己平台的包，可以通过源码进行编译安装。

最新的MySQL Utilities可以在此处下载：(http://dev.mysql.com/downloads/utilities)

MySQL Utilities依赖环境

MySQL Utilities需要Python2.6版本，所有的代码都是基于该版本编写的。同时，还需要连接驱动MySQL Connector/Python通用版本（高于1.0.8）。MySQL Connector/Python下载地址：http://dev.mysql.com/downloads/connector/python/

MySQL Utilities源码安装

wget https://cdn.mysql.com/archives/mysql-utilities/mysql-utilities-1.6.5.tar.gz

tar xvf mysql-utilities-1.6.5.tar.gz

cd mysql-utilities-1.6.5

python setup.py build

python setup.py install

mysqldiff –version

MySQL数据库体系结构
1. MySQL数据库体系结构介绍
  - 数据库定义

数据库（数据库文件）是指物理操作系统文件或者其他文件形式类型的集合。
可以是frm、myd、myi、ibd结尾的文件。
- 数据库实例
由数据库后台进程/线程以及一个共享内存区组成，共享内存可以被运行的后台进程/线程所共享
数据库实例才是真正用来操作数据库文件的

注意：MySQL中，数据库实例和数据库是一一对应的。没有Oracle的一对多(RAC)的机制。

MySQL体系结构
- 单进程多线程结构

不会影响MySQL的性能，看程序如何写。（多进程程序，进程间通信开销大于多线程）

存储引擎的概念

可以理解成文件系统，例如FAT32, NTFS, EXT4。
一个表是一个分区，引擎就是分区的文件系统存储引擎的对象就是表
show tablestatus; 可以看到每个表对应的是上面引擎(Engine)
除了特殊情况，我们现在就只考虑INNODB
1. 体系结构图

体系分层结构说明

第一层：主要功能是连接处理、授权认证、安全等。

第二层：包含了MySQL服务端的核心功能，包含查询缓存、查询解析、分析、优化等功能。

第三层：主要是存储引擎，存储引擎主要负责数据的存储和提取。

各模块组成

1 Connectors

指的是不同语言中与SQL的交互

2 Management Serveices & Utilities：

系统管理和控制工具

3 Connection Pool: 连接池

管理缓冲用户连接，线程处理等需要缓存的需求。

负责监听对 MySQL Server 的各种请求，接收连接请求，转发所有连接请求到线程管理模块。
每一个连接上 MySQL Server 的客户端请求都会被分配（或创建）一个连接线程为其单独服务。
而连接线程的主要工作就是负责 MySQL Server 与客户端的通信，接受客户端的命令请求，传递 Server 端的结果信息等。线程管理模块则负责管理维护这些连接线程。包括线程的创建，线程的 cache 等。

4 SQL Interface: SQL接口。

接受用户的SQL命令，并且返回用户需要查询的结果。比如select from就是调用SQL Interface

5 Parser: 解析器。

SQL命令传递到解析器的时候会被解析器验证和解析。解析器是由Lex和YACC实现的，是一个很长的脚本。

主要功能：

a . 将SQL语句进行语义和语法的分析，分解成数据结构，然后按照不同的操作类型进行分类，然后做出针对性的转发到后续步骤，以后SQL语句的传递和处理就是基于这个结构的。

如果在分解构成中遇到错误，那么就说明这个sql语句是不合理的

6 Optimizer: 查询优化器。

SQL语句在查询之前会使用查询优化器对查询进行优化。就是优化客户端请求的 query（sql语句），根据客户端请求的 query 语句，和数据库中的一些统计信息，在一系列算法的基础上进行分析，得出一个最优的策略，告诉后面的程序如何取得这个 query 语句的结果

他使用的是“选取-投影-联接”策略进行查询。

用一个例子就可以理解： select uid,name from user where gender = 1;这个select 查询先根据where 语句进行选取，而不是先将表全部查询出来以后再进行gender过滤；这个select查询先根据uid和name进行属性投影，而不是将属性全部取出以后再进行过滤；将这两个查询条件联接起来生成最终查询结果

7 Cache和Buffer：查询缓存。

他的主要功能是将客户端提交给MySQL 的 Select 类 query 请求的返回结果集 cache 到内存中，与该 query 的一个 hash 值做一个对应。该 Query 所取数据的基表发生任何数据的变化之后， MySQL 会自动使该 query 的Cache 失效。

在读写比例非常高的应用系统中， Query Cache 对性能的提高是非常显著的。当然它对内存的消耗也是非常大的。

如果查询缓存有命中的查询结果，查询语句就可以直接去查询缓存中取数据。这个缓存机制是由一系列小缓存组成的。比如表缓存，记录缓存，key缓存，权限缓存等

8 存储引擎接口

存储引擎接口模块可以说是 MySQL 数据库中最有特色的一点了。目前各种数据库产品中，基本上只有 MySQL 可以实现其底层数据存储引擎的插件式管理。这个模块实际上只是一个抽象类，但正是因为它成功地将各种数据处理高度抽象化，才成就了今天 MySQL 可插拔存储引擎的特色。

MySQL区别于其他数据库的最重要的特点就是其插件式的表存储引擎。MySQL插件式的存储引擎架构提供了一系列标准的管理和服务支持，这些标准与存储引擎本身无关，可能是每个数据库系统本身都必需的，如SQL分析器和优化器等，而存储引擎是底层物理结构的实现，每个存储引擎开发者都可以按照自己的意愿来进行开发。

注意:存储引擎是基于表的，而不是数据库。

sql在各个模块的执行过程

逻辑存储结构

MySQL逻辑存储结构

instance
database
schema
table
view

一个DB对应一个schema

一个DB对应一个文件夹

一个表对应一组文件

|–> table1 — | view1 |

MySQL Instance —–> Database —-> Schema —> |–> table2 — | view2 |

|–> table3 — | View3 |

注意： MySQL中一个Database对应一个Schema

物理存储结构
- MySQL配置文件

datadir：存储数据二进制文件的路径

表结构的组成

frm：表结构定义文件
MYI：索引文件
MYD：数据文件可以用hexdump -c XXX.frm查看二进制文件(意义不大)

查看建表语句

show create table tablename;

mysqlfrm (utilities工具包)

mysqlfrm –diagnostic /home/mysql/mysql/data/mysql/help_category.frm

错误日志文件

log_err 建议配置成统一的名字,方便定位错误

慢查询日志文件

将运行超过某一个时间阈值的SQL语句记录到文件

MySQL < 5.1 ：以秒为单位

MySQL >= 5.1 : 以毫秒为单位

MySQL >= 5.5 : 可以将慢查询日志记录到表

MySQL >= 5.6 : 以更细的粒度记录慢查询

MySQL >= 5.7 : 增加timestamps支持

slow_query_log_file：指定路径文件名，建议配置成统一的名字,用于优化查询

通用日志

作用：可以记录数据库所有相关操作
参数:general_log
默认文件名：机器名.log

同样可以将日志保存到表

开启性能下降明显（下降62%）

慢查询日志
- 慢查询日志相关参数：

slow_query_log:是否开启慢查询日志

slow_query_log_file:慢查询日志文件名, 默认是机器名-slow.log

long_query_time:制定慢查询阈值, 单位是秒，且当版本 >=5.5.X，支持毫秒。例如0.5即为500ms,大于该值，不包括值本身。例如该值为2，则执行时间正好等于2的SQL语句不会记录:

log_queries_not_using_indexes:将没有使用索引的SQL记录到慢查询日志,如果一开始因为数据少，查表快，耗时的SQL语句没被记录，当数据量大时，该SQL可能会执行很长时间,需要测试阶段就要发现问题，减小上线后出现问题的概率

log_throttle_queries_not_using_indexes:限制每分钟内，在慢查询日志中，去记录没有使用索引的SQL语句的次数；版本需要>=5.6.X,因为没有使用索引的SQL可能会短时间重复执行，为了避免日志快速增大，限制每分钟的记录次数

min_examined_row_limit:扫描记录少于该值的SQL不记录到慢查询日志。结合去记录没有使用索引的SQL语句的例子，有可能存在某一个表，数据量维持在百行左右，且没有建立索引。这种表即使不建立索引，查询也很快，扫描记录很小，如果确定有这种表，则可以通过此参数设置，将这个SQL不记录到慢查询日志。

log_slow_admin_statements：记录超时的管理操作SQL到慢查询日志，比如ALTER/ANALYZE TABLE

log_output：慢查询日志的格式，[FILE | TABLE | NONE]，默认是FILE；版本>=5.5

如果设置为TABLE，则记录的到mysql.slow_log

log_slow_slave_statements：在从服务器上开启慢查询日志

log_timestamps：写入时区信息。可根据需求记录UTC时间或者服务器本地系统时间

慢查询日志测试

查看慢查询记录的相关参数(终端A)

(root@localhost) 20:58:43 [(none)]> select version();

+————+

| version() |

+————+

| 5.7.25-log |

+————+

1 row in set (0.00 sec)

(root@localhost) 20:58:50 [(none)]> show variables like “slow%”;

+———————+————————–+

| Variable_name | Value |

+———————+————————–+

| slow_launch_time | 2 |

| slow_query_log | ON | #slow_query_log已打开

| slow_query_log_file | /data/mysqldata/slow.log | #slow日志位置

+———————+————————–+

3 rows in set (0.00 sec)

(root@localhost) 20:58:55 [(none)]> show variables like “long_query%”;

+—————–+———-+

| Variable_name | Value |

+—————–+———-+

| long_query_time | 1.000000 |

+—————–+———-+

1 row in set (0.00 sec)

(root@localhost) 20:58:59 [(none)]> show variables like “min_ex%”;

+————————+——-+

| Variable_name | Value |

+————————+——-+

| min_examined_row_limit | 0 | # my.cnf中未配置，默认值为0

+————————+——-+

1 row in set (0.00 sec)

(root@localhost) 21:00:00 [(none)]>

查看慢查询日志(终端B)

[root@node3 ~]# tail -f /data/mysqldata/slow.log

bin/mysqld, Version: 5.7.25-log (MySQL Community Server (GPL)). started with:

Tcp port: 3306 Unix socket: /data/mysqldata/mysql.sock

Time Id Command Argument

进行模拟耗时操作(终端A)

(root@localhost) 21:00:00 [(none)]> select sleep(4);

+———-+

| sleep(4) |

+———-+

| 0 |

+———-+

1 row in set (4.00 sec)

最终产生慢查询日志(终端B)

[root@node3 ~]# tail -f /data/mysqldata/slow.log

/usr/local/mysql/bin/mysqld,Version: 5.7.25-log (MySQL Community Server (GPL)).started with:

Tcp port: 3306 Unix socket: /data/mysqldata/mysql.sock

Time Id Command Argument

# Time: 2019-02-05T21:09:41.866813+08:00

# User@Host: root[root] @ localhost [] Id: 2

# Query_time: 4.001604 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 0

SET timestamp=1549372181;

select sleep(4);

注意:如果在终端A中set global min_examined_row_limit = 100;, 然后执行select sleep(8)，会发现该记录仍然被记录到慢查询日志中。原因是因为set global min_examined_row_limit设置的是全局变量，此次会话不生效。

但是我们上面set global slow_query_log = 1；却是在线生效的，这点有所不通

mysqldumpslow

慢日志分析工具

[root@node3 ~]# /usr/local/mysql/bin/mysqldumpslow –help #查看帮助

Usage: mysqldumpslow [ OPTS… ] [ LOGS… ]

-s 按照那种方式排序

c：访问计数

l：锁定时间

r:返回记录

al：平均锁定时间

ar：平均访问记录数

at：平均查询时间

-t 是top n的意思，返回多少条数据。

-g 可以跟上正则匹配模式，大小写不敏感。

[root@node3 ~]# /usr/local/mysql/bin/mysqldumpslow /data/mysqldata/slow.log

Reading mysql slow query log from /data/mysqldata/slow.log

Count: 2 Time=4.00s (8s) Lock=0.00s (0s) Rows=1.0 (2), root[root]@localhost

select sleep(N)

Count: 1 Time=0.00s (0s) Lock=0.00s (0s) Rows=0.0 (0), 0users@0hosts

bin/mysqld, Version: N.N.N-log (MySQL Community Server (GPL)). started with:

如果在线上操作，不需要mysqldumpslow去扫整个slow.log，可以去tail -n 10000 slow.log > last_10000_slow.log(10000这个数字根据实际情况进行调整),然后进行mysqldumpslow last_10000_slow.log

慢查询日志存入表演示

(root@localhost) 10:11:18 [(none)]> show variables like “log_output%”;

+—————+——-+

| Variable_name | Value |

+—————+——-+

| log_output | FILE |

+—————+——-+

1 row in set (0.01 sec)

(root@localhost) 10:11:38 [(none)]> set global log_output=”table”; #设置为输出为表

Query OK, 0 rows affected (0.00 sec)

(root@localhost) 10:12:12 [(none)]> show variables like “slow_query_log”;

+—————-+——-+

| Variable_name | Value |

+—————-+——-+

| slow_query_log | ON |

+—————-+——-+

1 row in set (0.01 sec)

(root@localhost) 10:12:29 [(none)]> show variables like “long_query%”;

+—————–+———-+

| Variable_name | Value |

+—————–+———-+

| long_query_time | 1.000000 |

+—————–+———-+

1 row in set (0.00 sec)

(root@localhost) 10:13:06 [(none)]> select sleep (5); # 执行语句

+———–+

| sleep (5) |

+———–+

| 0 |

+———–+

1 row in set (5.04 sec)

(root@localhost) 10:13:36 [(none)]> select * from mysql.slow_log\G

*************************** 1. row ***************************

start_time: 2019-02-06 10:13:28.075311

user_host: root[root] @ localhost []

query_time: 00:00:05.040310

lock_time: 00:00:00.000000

rows_sent: 1

rows_examined: 0

db:

last_insert_id: 0

insert_id: 0

server_id: 49180

sql_text: select sleep (5)

thread_id: 2

1 row in set (0.00 sec)

(root@localhost) 10:14:06 [(none)]> show create table mysql.slow_log\G

*************************** 1. row ***************************

Table: slow_log

Create Table: CREATE TABLE `slow_log` (

`start_time` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),

`user_host` mediumtext NOT NULL,

`query_time` time(6) NOT NULL,

`lock_time` time(6) NOT NULL,

`rows_sent` int(11) NOT NULL,

`rows_examined` int(11) NOT NULL,

`db` varchar(512) NOT NULL,

`last_insert_id` int(11) NOT NULL,

`insert_id` int(11) NOT NULL,

`server_id` int(10) unsigned NOT NULL,

`sql_text` mediumblob NOT NULL,

`thread_id` bigint(21) unsigned NOT NULL

) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT=’Slow log’ # ENGINE=CSV 这里使用的是CSV的引擎,性能较差

1 row in set (0.00 sec)

建议将slow_log表的存储引擎改成MyISAM

(root@localhost) 10:14:12 [(none)]> alter table mysql.slow_log engine = innodb; #提示我正在记录日志中，不能转换myisam引擎

ERROR 1580 (HY000): You cannot ‘ALTER’ a log table if logging is enabled

(root@localhost) 10:19:48 [(none)]> alter table mysql.slow_log engine = myisam; #提示我正在记录日志中，不能转换myisam引擎

ERROR 1580 (HY000): You cannot ‘ALTER’ a log table if logging is enabled

(root@localhost) 10:20:07 [(none)]> set global slow_query_log = 0; #先停止记录日志

Query OK, 0 rows affected (0.00 sec)

(root@localhost) 10:20:29 [(none)]> alter table mysql.slow_log engine = innodb; #显示不支持innodb引擎

ERROR 1579 (HY000): This storage engine cannot be used for log tables”

(root@localhost) 10:20:49 [(none)]> alter table mysql.slow_log engine = myisam; #转换表的myisam引擎

Query OK, 1 row affected (0.05 sec)

Records: 1 Duplicates: 0 Warnings: 0

(root@localhost) 10:21:06 [(none)]> set global slow_query_log = 1; #再开启记录日志

Query OK, 0 rows affected (0.00 sec)

(root@localhost) 10:21:37 [(none)]> show create table mysql.slow_log\G

……

) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT=’Slow log’

……

使用TABLE的优势在于方便查询，但是记住当在备份的时候，不要备份慢查询日志的表，避免备份过大。
使用FILE也可以，需要定时清除该文件，避免单文件过大。
1. 通用日志(generic_log)与审计
  - 通用日志作用

当需要查找某条特定SQL语句，且该SQL语句执行较快，无法记录到slow_log中时，可以开启通用日志generic_log,进行全面记录，可用于审计Audit

通用日志演示

A终端设置开启通用日志

(root@localhost) 10:21:40 [(none)]> show variables like ‘%general_log%’; #查看通用日志

+——————+—————————+

| Variable_name | Value |

+——————+—————————+

| general_log | OFF |

| general_log_file | /data/mysqldata/node3.log |

+——————+—————————+

2 rows in set (0.00 sec)

(root@localhost) 10:53:27 [(none)]> set global general_log=on; #开启通用日志

Query OK, 0 rows affected (0.02 sec)

(root@localhost) 11:33:54 [(none)]> set global log_output=’file’;

Query OK, 0 rows affected (0.00 sec)

(root@localhost) 11:41:19 [(none)]> select * from mysql.general_log\G

B终端显示日志

[root@node3 ~]# tail -f /data/mysqldata/node3.log

/usr/local/mysql/bin/mysqld,Version: 5.7.25-log (MySQL Community Server (GPL)).started with:

Tcp port: 3306 Unix socket: /data/mysqldata/mysql.sock

Time Id Command Argument’

2019-02-06T11:35:12.015280+08:00 2 Query select * from mysql.general_log

2019-02-06T11:44:06.794171+08:00 3 Connect root@localhost on using Socket

2019-02-06T11:44:06.795243+08:00 3 Query select @@version_comment limit 1

2019-02-06T11:44:06.797411+08:00 3 Query select USER()

通用日志会记录所有操作，性能下降明显。所以如果需要审计，需要Audit Plugin

审计插件

MariaDB Audit 插件

MySQL社区版本目前没有提供Audit的功能，企业版本提供了该功能。MariaDB 提供了开源的Audit插件，且MySQL也能使用。

[官方注册下载插件](https://mariadb.com/my_portal/download/audit_plugin)

Mcaffer插件下载

https://bintray.com/mcafee/mysql-audit-plugin/release#files 点击Files文件

audit-plugin-mysql-5.7-1.1.4-725-linux-x86_64.zip](https://bintray.com/mcafee/mysql-audit-plugin/download_file?file_path=audit-plugin-mysql-5.7-1.1.4-725-linux-x86_64.zip)

mcafee Audit Plugin安装

MySQL5.7.18 安装mcaffer audit-plugin-mysql-5.7-1.1.4-725 版本审计插件步骤如下:

(root@localhost) 12:25:49 [(none)]> show global variables like ‘plugin%’;

+—————+——————————+

| Variable_name | Value |

+—————+——————————+

| plugin_dir | /usr/local/mysql/lib/plugin/ |

+—————+——————————+

1 row in set (0.00 sec)

安装审计插件

[root@node3 ~]# wget https://bintray.com/mcafee/mysql-audit-plugin/download_file?file_path=1.0.9-release%2Faudit-plugin-mysql-5.7-1.0.9-585-linux-x86_64.zip

[root@node3 ~]# unzip download_file\?file_path\=1.0.9-release%2Faudit-plugin-mysql-5.7-1.0.9-585-linux-x86_64.zip

[root@node3 ~]# cp audit-plugin-mysql-5.7-1.0.9-585/lib/libaudit_plugin.so /usr/local/mysql/lib/plugin/

my.cnf添加plugin-load=AUDIT=libaudit_plugin.so,并重启

[root@node3 ~]# vim /etc/my.cnf

[mysqld]

……

plugin-load=AUDIT=libaudit_plugin.so #添加配置文件load模式，另外还有install模式：INSTALL PLUGIN AUDIT SONAME ‘libaudit_plugin.so’;

……

[root@node3 ~]# /etc/init.d/mysql.server restart

Shutting down MySQL…. SUCCESS!

Starting MySQL….. SUCCESS!

测试插件

(root@localhost) 14:36:58 [(none)]> show plugins;

+—————————-+———-+——————–+———+———+

+—————————-+———-+——————–+———+———+

…..

+—————————-+———-+——————–+——————–+——-+

45 rows in set (0.00 sec)

root@gczheng 23:25: [(none)]> show global status like ‘AUDIT_version’; –显示审计日志版本

+—————+———–+

| Variable_name | Value |

+—————+———–+

| Audit_version | 1.1.4-725 |

+—————+———–+

1 row in set (0.00 sec)

以上仅为基本功能操作，详细的细粒度控制请参考Mcafee文档https://github.com/mcafee/mysql-audit/wiki

存储引擎
- MySQL上支持的存储引擎

(root@localhost) 14:43:51 [(none)]> show engines;

+——————–+———+—————————————————————-+————–+——+————+

+——————–+———+—————————————————————-+————–+——+————+

| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |

| CSV | YES | CSV storage engine | NO | NO | NO |

| MyISAM | YES | MyISAM storage engine | NO | NO | NO |

| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |

| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |

| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |

| ARCHIVE | YES | Archive storage engine | NO | NO | NO |

| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |

+——————–+———+—————————————————————-+————–+——+————+

9 rows in set (0.00 sec)

存储引擎的概念

用来处理数据库的相关CRUD操作
每个数据库都有存储引擎，只是MySQL比较强调存储引擎的概念。
- MySQL存储引擎

官方存储引擎

MyISAM
InnoDB — 推荐；其他引擎已经体停止维护和开发
Memory
Federated
CSV
Archive

第三方存储引擎

TokuDB — 开源，适合插入密集型
InfoBright — 商业，开源版本有数据量限制。属于列存储，面向OLAP场景
Spider

第三方存储引擎在特定场合下比较适合，除此之外，都应该使用InnoDB

存储引擎之MyISAM

MyISAM特点
1版本之前的默认存储引擎
堆表数据结构
表锁设计
支持数据静态压缩
不支持事物
数据容易丢失
索引容易损坏
唯一优点 :数据文件可以直接拷贝到另一台服务器使用
现在MySQL中还有用MyISAM的表，

主要是历史原因。数据库文件以MY开头的基本都是MyISAM的表

部分如User，DB等系统表(MyISAM引擎)，可以直接拷贝，比较方便

性能好，或者存储小不是MyISAM的优点，也不是存在的原因

MyISAM文件组成
frm 表结构文件
MYI 索引文件
MYD 数据文件

数据文件是堆表数据结构，堆是无序数据的集合

MYI中的叶子节点，指向MYD中的数据页

当数据移动到页外时，需要修改对应指针

myisamchk

功能：检查并修复myisam表

myisamchk 最好是关闭数据库，然后修复。不停机需要锁住表 lock table sysbench.test_log_copy read 再修复

[root@node3 ~]# /usr/local/mysql/bin/myisamchk /data/mysqldata/mysql/user

Checking MyISAM file: /data/mysqldata/mysql/user

Data records: 3 Deleted blocks: 0

– check file-size

– check record delete-chain

– check key delete-chain

– check index reference

– check data record references index: 1

– check record links

myisamchk通过扫描MYD文件来重建MYI文件；如果MYD文件中某条记录有问题，将跳过该记录

Memory存储引擎

Memory介绍
全内存存储的引擎
数据库重启后数据丢失
支持哈希索引
不支持事物
Memory特性
千万不要用Memory存储引擎去做缓存(Cache), 性能上不及Redis和Memcahced
Memory不能禁用，当涉及内部排序操作的临时表时，使用该存储引擎
max_heap_table_size决定使用内存的大小，默认时16M;无论该表使用的什么引擎，只要使用到临时表，或者指定Memory，都受参数影响。当上面设置的内存放不下数据时，(>=5.6)转为MyISAM,(>=5.7)转为InnoDB,注意磁盘上临时路径空间的大小(tmpdir)。内存使用为会话(SESSION)级别，当心内核OOM
支持哈希索引，且仅支持等值查询

(root@localhost) 18:22:14 [mysql]> show global status like “%tmp%tables”;

+————————-+——-+

| Variable_name | Value |

+————————-+——-+

| Created_tmp_disk_tables | 0 |#内存放不下，转成磁盘存储的数量,如果过大，考虑增大内存参数

| Created_tmp_tables | 2 |#创建临时表的数量

+————————-+——-+

2 rows in set (0.00 sec)

(root@localhost) 18:32:33 [mysql]> show variables like “tmp%”;

+—————-+—————–+

| Variable_name | Value |

+—————-+—————–+

| tmp_table_size | 33554432 |

| tmpdir | /data/mysqldata | #memory转成磁盘存储的路径

+—————-+—————–+

2 rows in set (0.00 sec)

(root@localhost) 18:33:43 [mysql]>

Memory的物理特性
内存不会一次性分配最大空间，而是随着使用逐步增到到最大值
通过链表管理空闲空间
使用固定长度存储数据
不支持BLOB和TEXT类型
可以创建自增主键
- CSV存储引擎
CSV介绍
CSV – Comma-Separated Values，使用逗号分隔
不支持特殊字符
CSV是一种标准文件格式
文件以纯文本形式存储表格数据
使用广泛
CSV文件组成
frm 表结构
CSV 数据文件
CSM 元数据信息
CSV特性
MySQL CSV存储引擎运行时，即创建CSV文件
通过MySQL标准接口来查看和修改CSV文件
无需将CSV文件导入到数据库，只需创建相同字段的表结构，拷贝CSV文件即可
CSV存储引擎表每个字段必须是NOT NULL属性
- Federated存储引擎
Federated介绍
允许本地访问远程MySQL数据库中表的数据
本地不存储任何数据文件
类似Oracle中的DBLink
Federated存储引擎默认不开启, 需要在cnf的[mysqld]标签下添加 federated
MySQL的Federated不支持异构数据库访问，MariaDB中的FederatedX支持

Federated 语法

scheme://user_name[:password]@host_name[:port_num]/db_name/tbl_name

CONNECTION=’mysql://username:password@hostname:port/database/tablename’

例句如下：

CREATE TABLE `test001` (

`scenario` varchar(30) NOT NULL DEFAULT ” COMMENT ‘测试场景’,

`server_name` varchar(15) NOT NULL COMMENT ‘主机名’,

`test_type` varchar(15) NOT NULL COMMENT ‘read-only,read-write,insert等’,

`sb_threads` int(11) NOT NULL DEFAULT ‘0’ COMMENT ‘sysbench 测试线程’,

`create_time` datetime DEFAULT NULL COMMENT ‘开始时间’,

`done_time` datetime DEFAULT NULL COMMENT ‘完成时间’,

`server_load` decimal(12,2) NOT NULL DEFAULT ‘0.00’ COMMENT ‘以当前线程测试完后立刻记录15分钟负载值’,

`request_read` int(11) NOT NULL DEFAULT ‘0’,

`request_write` int(11) NOT NULL DEFAULT ‘0’,

`transactions_per_second` decimal(12,2) NOT NULL DEFAULT ‘0.00’ COMMENT ‘QPS’,

`request_per_second` decimal(12,2) NOT NULL DEFAULT ‘0.00’ COMMENT ‘TPS’,

`95_pct_time` decimal(12,2) NOT NULL DEFAULT ‘0.00’ COMMENT ‘单位毫秒’,

KEY `createtime` (`create_time`)

) ENGINE=FEDERATED DEFAULT CHARSET=utf8 CONNECTION=’mysql://federated:123456@192.168.80.102:3306/sysbench/test_log’;

Federated测试

终端A(192.168.80.102)

(root@localhost) 19:10:43 [information_schema]> create database sysbench;

Query OK, 1 row affected (0.02 sec)

(root@localhost) 19:06:50 [information_schema]> create user federated@’192.168.80.%’ identified by ‘123456’;

Query OK, 0 rows affected (0.00 sec)

(root@localhost) 19:11:06 [information_schema]> grant select on sysbench.* to federated@’192.168.80.%’;

Query OK, 0 rows affected (0.00 sec)

(root@localhost) 19:11:35 [information_schema]> flush privileges;

Query OK, 0 rows affected (0.00 sec)

(root@localhost) 19:11:50 [information_schema]> show grants for federated@’192.168.80.%’;

+————————————————————+

| Grants for federated@192.168.80.% |

+————————————————————+

| GRANT USAGE ON *.* TO ‘federated’@’192.168.80.%’ |

| GRANT SELECT ON `sysbench`.* TO ‘federated’@’192.168.80.%’ |

+————————————————————+

2 rows in set (0.00 sec)

(root@localhost) 19:35:48 [sysbench]> CREATE TABLE `test_log` (

`scenario` varchar(30) NOT NULL DEFAULT ” COMMENT ‘测试场景’,

`server_name` varchar(15) NOT NULL COMMENT ‘主机名’,

`test_type` varchar(15) NOT NULL COMMENT ‘read-only,read-write,insert等’,

`sb_threads` int(11) NOT NULL DEFAULT ‘0’ COMMENT ‘sysbench 测试线程’,

`create_time` datetime DEFAULT NULL COMMENT ‘开始时间’,

`done_time` datetime DEFAULT NULL COMMENT ‘完成时间’,

`server_load` decimal(12,2) NOT NULL DEFAULT ‘0.00’ COMMENT ‘以当前线程测试完后立刻记录15分钟负载值’,

`request_read` int(11) NOT NULL DEFAULT ‘0’,

`request_write` int(11) NOT NULL DEFAULT ‘0’,

`transactions_per_second` decimal(12,2) NOT NULL DEFAULT ‘0.00’ COMMENT ‘QPS’,

`request_per_second` decimal(12,2) NOT NULL DEFAULT ‘0.00’ COMMENT ‘TPS’,

`95_pct_time` decimal(12,2) NOT NULL DEFAULT ‘0.00’ COMMENT ‘单位毫秒’,

KEY `createtime` (`create_time`)

) ENGINE=INNODB DEFAULT CHARSET=utf8;

Query OK, 0 rows affected (0.05 sec)

(root@localhost) 19:39:40 [sysbench]> insert into test_log values(‘oltp_update_index’,’localhost’,’read-write’,2,now(),now(),1.2,0,0,245,23,23);

Query OK, 1 row affected (0.00 sec)

终端B(192.168.80.101)

[root@node2 ~]# vim /usr/local/mysql/etc/my.cnf

# … 省略 …

[mysqld]

federated #新增的配置项，表示打开Federated引擎

# … 省略 …

[root@node2 ~]# /etc/init.d/mysql.server restart

[root@node2 mysql]# ./bin/mysql -h192.168.80.102 -ufederated -p123456 -e “select scenario ,server_name, test_type,sb_threads,create_time,done_time ,server_load from sysbench.test_log limit 1;”

mysql: [Warning] Using a password on the command line interface can be insecure.

+——————-+————-+————+————+———————+———————+————-+

+——————-+————-+————+————+———————+———————+————-+

| oltp_update_index | localhost | read-write | 2 | 2019-02-07 19:40:17 | 2019-02-07 19:40:17 | 1.20 |

+——————-+————-+————+————+———————+———————+————-+

(root@localhost) 19:42:19 [(none)]> create database federated;

Query OK, 1 row affected (0.01 sec)

(root@localhost) 21:27:08 [(none)]> use federated;

Database changed

(root@localhost) 21:29:36 [federated]> CREATE TABLE `test001` (

`scenario` varchar(30) NOT NULL DEFAULT ” COMMENT ‘测试场景’,

`server_name` varchar(15) NOT NULL COMMENT ‘主机名’,

`test_type` varchar(15) NOT NULL COMMENT ‘read-only,read-write,insert等’,

`sb_threads` int(11) NOT NULL DEFAULT ‘0’ COMMENT ‘sysbench 测试线程’,

`create_time` datetime DEFAULT NULL COMMENT ‘开始时间’,

`done_time` datetime DEFAULT NULL COMMENT ‘完成时间’,

`server_load` decimal(12,2) NOT NULL DEFAULT ‘0.00’ COMMENT ‘以当前线程测试完后立刻记录15分钟负载值’,

`request_read` int(11) NOT NULL DEFAULT ‘0’,

`request_write` int(11) NOT NULL DEFAULT ‘0’,

`transactions_per_second` decimal(12,2) NOT NULL DEFAULT ‘0.00’ COMMENT ‘QPS’,

`request_per_second` decimal(12,2) NOT NULL DEFAULT ‘0.00’ COMMENT ‘TPS’,

`95_pct_time` decimal(12,2) NOT NULL DEFAULT ‘0.00’ COMMENT ‘单位毫秒’,

KEY `createtime` (`create_time`)

) ENGINE=FEDERATED DEFAULT CHARSET=utf8 CONNECTION=’mysql://federated:123456@192.168.80.102:3306/sysbench/test_log’;

(root@localhost) 21:29:28 [federated]> select scenario ,server_name, test_type,sb_threads,create_time,done_time ,server_load from test001;

+——————-+————-+————+————+———————+———————+————-+

+——————-+————-+————+————+———————+———————+————-+

| oltp_update_index | localhost | read-write | 2 | 2019-02-07 19:40:17 | 2019-02-07 19:40:17 | 1.20 |

+——————-+————-+————+————+———————+———————+————-+

1 row in set (0.00 sec)

(root@localhost) 21:46:10 [federated]> insert into test001 values(‘oltp_update_index’,’localhost’,’read-write’,2,now(),now(),1.2,0,0,245,23,23); #由于只有select权限，无法对该`federated`.`test001`表进行insert操作

ERROR 1296 (HY000): Got error 10000 ‘Error on remote system: 1142: INSERT command denied to user ‘federated’@’node2’ for table ‘test_log” from FEDERATED

多实例安装（可以同版本或异版本）
- 多实例介绍

一台服务器上安装多个MySQL数据库实例
可以充分利用服务器的硬件资源（注意io资源）
通过mysqld_multi进行管理
- 配置文件要点

[mysqld_multi] 是否需要配置

cnf上直接配置[mysqld1]、[mysqld2]、[mysqld3] 实例标签，而不配置[mysqld_multi],使用mysqld_multi start 1也是可以启动数据库实例的，但是没有mysqld_safe的守护进程。所以该标签需要配置
如果在[client]和[mysqld_multi]标签中同时存在user和password, 则在关闭数据库实例中会使用[mysqld_multi]中的user去关闭。

存在精确匹配的标签，则优先使用精确匹配标签下的配置项

multi_admin用户的作用

通过官方文档https://dev.mysql.com/doc/refman/5.7/en/mysqld-multi.html中我们看到，’multi_admin’@’localhost’这个用户主要的作用是用来关闭数据库实例，因为文档中只授权了SHUTDOWN权限。所以在[mysqld_multi]标签下，我们需要配置user和password来进行关闭数据库实例。但是实际上有bug，还不如用（mysqld_multi –user=root –password=iforgot stop 1）授权有足够权限，这个权限还在摸索中，哪位有验证提供一下，谢谢！

环境说明

mysqld1 — MySQL 5.7.18

mysqld2 — MySQL 5.7.18

mysqld3 — MySQL 5.6.36

配置及其说明

MySQL实例1

mysqld1 (MySQL 5.7.18)

port = 33016

datadir = /r2/mysqldata/mysql_33016

socket = /r2/mysqldata/mysql_33016/mysql.sock

MySQL实例2

mysqld2 (MySQL 5.7.18)

port = 33026

datadir = /r2/mysqldata/mysql_33026

socket = /r2/mysqldata/mysql_33026/mysql.sock

MySQL实例3

mysqld3 (MySQL 5.6.36)

port = 33036

datadir = /r2/mysqldata/mysql_33036

socket = /r2/mysqldata/mysql_33036/mysql.sock

这三个参数必须定制且必须不同 (port/datadir/socket)。server-id和多数据库实例没有关系，和数据库复制有关系。注意MySQL5.6.36的plugin_dir的路径

配置说明：

1：配置的标签顺序没有关系，不会影响最终配置的有效性。

2：同类型标签中的配置项会合并，形成一个大的配置项

3：匹配度高的标签中的配置项的值，会覆盖掉匹配度低的标签中的配置项的值

[mysqldN]中的配置项会和[mysqld]中的配置项进行合并，并且[mysqldN]中已有的配置项的值，会覆盖掉[mysqld]中的配置项的值,如datadir, port等

配置文件

[client] # 这个标签如果配置了用户和密码，

# 并且[mysqld_multi]下没有配置用户名密码，

# 则mysqld_multi stop时, 会使用这个密码

# 如果没有精确的匹配，则匹配[client]标签

user = root

password = iforgot

[mysqld_multi]

mysqld =/usr/local/mysql/bin/mysqld_safe

mysqladmin =/usr/local/mysql/bin/mysqladmin

log = /usr/local/mysql/mysqld_multi.log

user = multi_admin

pass = 123456

# 官方文档中写的password，但是存在bug，需要改成pass(v5.7.18)

# 写成password，start时正常，stop时，报如下错误

# Access denied for user ‘multi_admin’@’localhost’ (using password: YES)

###############################################################

################################################################

[mysqld1] # mysqld后面的数字为GNR, 是该实例的标识（mysqld_multi start 1）

server-id = 33016

mysqld = mysqld

mysqladmin = mysqladmin

group_concat_max_len = 102400

user = mysql

port = 33016

socket = /r2/mysqldata/mysql_33016/mysql.sock

basedir = /usr/local/mysql

datadir = /r2/mysqldata/mysql_33016/data

pid_file = /r2/mysqldata/mysql_33016/data/mysql.pid

log_error = /r2/mysqldata/mysql_33016/logs/mysql_error.log

log_bin = /r2/mysqldata/mysql_33016/logs/binlog

slow_query_log_file = /r2/mysqldata/mysql_33016/logs/slow.log

log_timestamps = system

################################################################

[mysqld2]

server-id = 33026

mysqld = mysqld

mysqladmin = mysqladmin

user = mysql

port = 33026

socket = /r2/mysqldata/mysql_33026/mysql.sock

basedir = /usr/local/mysql

datadir = /r2/mysqldata/mysql_33026/data/

pid_file = /r2/mysqldata/mysql_33026/data/mysql.pid

log_error = /r2/mysqldata/mysql_33026/logs/mysql_error.log

log_bin = /r2/mysqldata/mysql_33026/logs/binlog

slow_query_log_file = /r2/mysqldata/mysql_33026/logs/slow.log

log_timestamps = system

################################################################

[mysqld3]

server-id = 33036

mysqld = /usr/local/mysql56/bin/mysqld

mysqladmin = /usr/local/mysql56/bin/mysqladmin

user = mysql

port = 33036

socket = /r2/mysqldata/mysql_33036/mysql.sock

basedir = /usr/local/mysql56

datadir = /r2/mysqldata/mysql_33036/data

pid_file = /r2/mysqldata/mysql_33036/data/mysql.pid

log_error = /r2/mysqldata/mysql_33036/logs/mysql_error.log

log_bin = /r2/mysqldata/mysql_33036/logs/binlog

slow_query_log_file = /r2/mysqldata/mysql_33036/logs/slow.log

plugin_dir = /usr/local/mysql56/lib/plugin

################################################################

[mysqld]

wait_timeout = 31536000

#interactive_timeout = 600

sql_mode = “STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER”

skip_name_resolve = 1

lower_case_table_names = 0

character-set-server = utf8

#auto_increment_increment = 1

#auto_increment_offset = 1

init_connect = ‘set name utf8;’

#log_timestamps = system

tmpdir = /r2/mysqldata

#plugin_dir = /usr/local/mysql/lib/plugin

#==========================================================

#bin setting

#==========================================================

binlog_format = MIXED

log_bin_trust_function_creators = 1

binlog_cache_size = 64M

max_binlog_cache_size = 1G

max_binlog_size = 512M

expire_logs_days = 7

#==========================================================

# replication relay-log

#==========================================================

log-slave-updates = 1

slave-net-timeout = 60

sync-master-info = 1

sync-relay-log = 1

sync-relay-log-info = 1

#==========================================================

#no need to sync database

#==========================================================

binlog-ignore-db = test

binlog-ignore-db = information_schema

binlog-ignore-db = mysql

#==========================================================

#performance setting

#==========================================================

open_files_limit = 1024000

max_connections = 1000

max_user_connections = 9990

max_connect_errors = 100000

table_open_cache = 1024

max_allowed_packet = 32M

thread_cache_size = 64

max_heap_table_size = 16M

query_cache_type = 0

###global cache ###

key_buffer_size = 16M

query_cache_size = 0

###session cache ###

sort_buffer_size = 8M #排序缓冲

join_buffer_size = 4M #表连接缓冲

read_buffer_size = 8M #顺序读缓冲

read_rnd_buffer_size = 8M #随机读缓冲

tmp_table_size = 32M #内存临时表

binlog_cache_size = 4M #二进制日志缓冲

thread_stack = 256KB #线程的堆栈的大小

#==========================================================

#innodb setting

#==========================================================

default-storage-engine = InnoDB

innodb_buffer_pool_size = 512M

innodb_open_files = 1000

innodb_flush_log_at_trx_commit = 1

innodb_file_per_table = 1

innodb_flush_method = O_DIRECT

innodb_log_file_size = 128M

innodb_log_files_in_group = 2

innodb_data_file_path = ibdata1:128M:autoextend

innodb_buffer_pool_instances = 8

innodb_lock_wait_timeout = 5

innodb_io_capacity = 1000

innodb_io_capacity_max = 20000

innodb_large_prefix = 0

innodb_thread_concurrency = 64

innodb_strict_mode = OFF

innodb_sort_buffer_size = 4194304

innodb_file_format = Barracuda

innodb_file_format_max = Barracuda

#==========================================================

#slow setting

#==========================================================

slow-query-log = on

long_query_time = 1

[mysqld-5.7]

innodb_buffer_pool_dump_pct = 40

innodb_page_cleaners = 4

innodb_undo_log_truncate = 1

innodb_max_undo_log_size = 1G # 该参数减小到1G

innodb_purge_rseg_truncate_frequency = 128

binlog_gtid_simple_recovery = 1

log_timestamps = system

transaction_write_set_extraction= MURMUR32

show_compatibility_56 = on

安装多实例

yum remove MariaDB-common MariaDB-compat MariaDB-server #rhel7删除mariadb

yum install gcc gcc-c++ bzip2 bzip2-devel bzip2-libs python-devel -y #安装mysql环境包

tar zxvf mysql-5.6.36-linux-glibc2.5-x86_64.tar.gz -C /usr/local/ #解压mysql到/usr/local目录

tar zxvf mysql-5.7.18-linux-glibc2.5-x86_64.tar.gz -C /usr/local/ #同上

cd /usr/local/

ln -s mysql-5.7.18-linux-glibc2.5-x86_64/ mysql #创建软连接

ln -s mysql-5.6.36-linux-glibc2.5-x86_64/ mysql56 #同上

groupadd mysql && useradd -r -g mysql -s /bin/false mysql #添加用户

mkdir -p /r2/mysqldata/ #创建主目录

chown -R mysql:mysql /usr/local/mysql #授权访问mysql-5.7.18-linux-glibc2.5-x86_64

chown -R mysql:mysql /r2/mysqldata/ #同上

mv /tmp/multi_my.cnf /etc/my.cnf #multi_my.cnf配置文件上面已附上

cat <<EOF >>/etc/profile #下面添加到环境变量

export PATH=\$PATH:/usr/local/mysql/bin/

EOF

source /etc/profile

–开始创建并初始化mysqld1 （mysql5.7临时密码在日志输出上面有提示，下同）

mkdir -p /r2/mysqldata/mysql_33016/{logs,data}

chown -R mysql.mysql /r2/mysqldata/mysql_33016

cd /usr/local/mysql/bin/

./mysqld –initialize –user=mysql –basedir=/usr/local/mysql –datadir=/r2/mysqldata/mysql_33016/data/

–开始创建并初始化mysqld2

mkdir -p /r2/mysqldata/mysql_33026/{logs,data}

chown -R mysql.mysql /r2/mysqldata/mysql_33026

cd /usr/local/mysql/bin/

./mysqld –initialize –user=mysql –basedir=/usr/local/mysql –datadir=/r2/mysqldata/mysql_33026/data/

–开始创建并初始化mysqld3 （mysql5.6初始化密码是空）

mkdir -p /r2/mysqldata/mysql_33036/{logs,data}

chown -R mysql.mysql /r2/mysqldata/mysql_33036

cd /usr/local/mysql56/

./scripts/mysql_install_db –user=mysql –basedir=/usr/local/mysql56 –datadir=/r2/mysqldata/mysql_33036/data/

启动数据库，并修改root账号，授权其他账号

–使用mysqld_multi启动，mysqld_multi [OPTIONS] {start|reload|stop|report} [GNR,GNR,GNR…]

[root@multi mysql56]# mysqld_multi start 1

[root@multi mysql56]# mysqld_multi start 2

[root@multi mysql56]# mysqld_multi start 3

[root@multi mysql56]# mysqld_multi report

Reporting MySQL servers

MySQL server from group: mysqld1 is running

MySQL server from group: mysqld2 is running

MySQL server from group: mysqld3 is running

–查看三个mysql端口

[root@multi mysql56]# netstat -tunlp | grep mysql

tcp6 0 0 :::33036 :::* LISTEN 35400/mysqld

tcp6 0 0 :::33016 :::* LISTEN 37735/mysqld

tcp6 0 0 :::33026 :::* LISTEN 37738/mysqld

— 使用sock进行登录，并输入临时密码后，修改密码，如下：

root@multi mysqldata]# mysql -uroot -p -S /r2/mysqldata/mysql_33016/mysql.sock

mysql>alter user ‘root’@’localhost’ identified by ‘iforgot’ ;

mysql>create user ‘multi_admin’@’localhost’ identified by ‘123456’;

mysql>grant shutdown on *.* to ‘multi_admin’@’localhost’;

mysql>flush privileges;

mysql>exit;

–mysqld2 同上；mysqld3 由于是mysql5.6.36版本直接使用mysql -S /r2/mysqldata/mysql_33036/mysql.sock登陆

[root@multi mysqldata]# mysql -S /r2/mysqldata/mysql_33036/mysql.sock

mysql>set password=password(‘iforgot’);

mysql>create user ‘multi_admin’@’localhost’ identified by ‘123456’;

mysql>grant shutdown on *.* to ‘multi_admin’@’localhost’;

mysql>flush privileges;

mysql>exit;

–检测port和socket

[root@multi mysql56]# ps -ef | grep mysqld

mysql 35400 1 0 14:56 pts/1 00:00:10 /usr/local/mysql56/bin/mysqld –server-id=33036 –user=mysql –port=33036 –socket=/r2/mysqldata/mysql_33036/mysql.sock –basedir=/usr/local/mysql56 –datadir=/r2/mysqldata/mysql_33036/data –pid_file=/r2/mysqldata/mysql_33036/data/mysql.pid –log_error=/r2/mysqldata/mysql_33036/logs/mysql_error.log –log_bin=/r2/mysqldata/mysql_33036/logs/binlog –slow_query_log_file=/r2/mysqldata/mysql_33036/logs/slowlogs/slow.log –plugin_dir=/usr/local/mysql56/lib/plugin

mysql 37735 1 0 16:33 pts/1 00:00:05 mysqld –server-id=33016 –group_concat_max_len=102400 –user=mysql –port=33016 –socket=/r2/mysqldata/mysql_33016/mysql.sock –basedir=/usr/local/mysql –datadir=/r2/mysqldata/mysql_33016/data –pid_file=/r2/mysqldata/mysql_33016/data/mysql.pid –log_error=/r2/mysqldata/mysql_33016/logs/mysql_error.log –log_bin=/r2/mysqldata/mysql_33016/logs/binlog –slow_query_log_file=/r2/mysqldata/mysql_33016/logs/slow.log –log_timestamps=system

mysql 37738 1 0 16:33 pts/1 00:00:04 mysqld –server-id=33026 –user=mysql –port=33026 –socket=/r2/mysqldata/mysql_33026/mysql.sock –basedir=/usr/local/mysql –datadir=/r2/mysqldata/mysql_33026/data/ –pid_file=/r2/mysqldata/mysql_33026/data/mysql.pid –log_error=/r2/mysqldata/mysql_33026/logs/mysql_error.log –log_bin=/r2/mysqldata/mysql_33026/logs/binlog –slow_query_log_file=/r2/mysqldata/mysql_33026/logs/slow.log –log_timestamps=system

设置login-path

设置login-path主要为了能够简化登录，同时还可以让每个数据库的密码都不同，避免使用[client]下的统一用户名密码

[root@multi mysqldata]# mysql_config_editor set -G mysqld1 -u root -p -S /r2/mysqldata/mysql_33016/mysql.sock

Enter password:

[root@multi mysqldata]# mysql_config_editor set -G mysqld2 -u root -p -S /r2/mysqldata/mysql_33026/mysql.sock

Enter password:

[root@multi mysqldata]# mysql_config_editor set -G mysqld3 -u root -p -S /r2/mysqldata/mysql_33036/mysql.sock

Enter password:

[root@multi mysqldata]#

然后可以使用mysql –login-path=mysql1 这种方式登录

SSL配置

SSL（Secure Socket Layer）是维护Client – Server之间加密通讯的一套安全协议；

(root@localhost) 19:27:01 [(none)]> show variables like ‘%ssl%’; #默认ssl未开启

+—————+———-+

| Variable_name | Value |

+—————+———-+

| have_openssl | DISABLED |

| have_ssl | DISABLED |

……

9 rows in set (0.06 sec)

开启SSL (5.7.18)并测试

环境说明

服务端A：MySQLserver； IP：192.168.80.102

[root@node3 mysql]# bin/mysql_ssl_rsa_setup –datadir/data/mysqldata/ –user=mysql –uid=mysql #使用–uid后，就不需要chown mysql.mysql *.pem

Generating a 2048 bit RSA private key

…..

[root@node2 mysql]# ls -l /data/mysqldata/|grep pem

-rw——- 1 mysql mysql 1679 2月 9 21:26 ca-key.pem

-rw-r–r– 1 mysql mysql 1107 2月 9 21:26 ca.pem

-rw-r–r– 1 mysql mysql 1107 2月 9 21:26 client-cert.pem

-rw——- 1 mysql mysql 1679 2月 9 21:26 client-key.pem

-rw——- 1 mysql mysql 1675 2月 9 21:26 private_key.pem

-rw-r–r– 1 mysql mysql 451 2月 9 21:26 public_key.pem

-rw-r–r– 1 mysql mysql 1107 2月 9 21:26 server-cert.pem

-rw——- 1 mysql mysql 1675 2月 9 21:26 server-key.pem

重启mysql

[root@node3 mysql]# ./bin/mysqladmin -uroot -piforgot -S /r2/mysqldata/mysql_33016/mysql.sock shutdown

[root@node3 mysql]# mysqld_multi start 1 #或者

[root@node3 ~]# /etc/init.d/mysql.server restart

服务端A测试：MySQLserver； IP：192.168.80.102

(root@localhost) 19:27:01 [(none)]> show variables like ‘%ssl%’;

+—————+—————–+

| Variable_name | Value |

+—————+—————–+

| have_openssl | YES |

| have_ssl | YES |

| ssl_ca | ca.pem |

……

(root@localhost) 19:27:14 [(none)]> \s #status

……

Current user: root@localhost

SSL: Not in use #此时本地socket登录，不用SSL

Current pager: stdout

Using outfile: ”

Using delimiter: ;

Server version: 5.7.25-log MySQL Community Server (GPL)

Protocol version: 10

Connection: Localhost via UNIX socket

……

服务端A创建测试账号：MySQLserver； IP：192.168.80.102

(root@localhost) 19:31:39 [(none)]> create user ‘ssl’@’%’ identified by ‘ssltest’;

Query OK, 0 rows affected (0.01 sec)

(root@localhost) 19:31:45 [(none)]> drop user ‘ssl’@’%’;

Query OK, 0 rows affected (0.01 sec)

(root@localhost) 19:32:12 [(none)]> create user ‘ssl’@’%’ identified by ‘ssltest’;

Query OK, 0 rows affected (0.00 sec)

(root@localhost) 19:32:25 [(none)]> grant all on *.* to ‘ssl’@’%’;

Query OK, 0 rows affected (0.00 sec)

(root@localhost) 19:32:41 [(none)]> flush privileges;

Query OK, 0 rows affected (0.00 sec)

(root@localhost) 19:32:53 [(none)]> show grants for ‘ssl’@’%’;

+——————————————+

| Grants for ssl@% |

+——————————————+

| GRANT ALL PRIVILEGES ON *.* TO ‘ssl’@’%’ |

+——————————————+

1 row in set (0.00 sec)

(root@localhost) 19:33:09 [(none)]> select ssl_type from mysql.user where user=’ssl’;

+———-+

| ssl_type |

+———-+

| | #看到ssl_还没有配置

+———-+

1 row in set (0.00 sec)

客户端B测试：MySQClient； IP：192.168.80.101；默认使用ssl登录

[root@node2 ~]# mysql -h192.168.80.102 -ussl -pssltest

mysql> \s

……

Current user: ssl@node2

SSL: Cipher in use is DHE-RSA-AES256-SHA #已经使用了ssl登录了

Current pager: stdout

……

客户端B测试：MySQClient； IP：192.168.80.101；使用skip ssl登录

[root@node2 ~]# mysql -h192.168.80.102 -ussl -pssltest –skip-ssl

mysql> \s

Current user: ssl@node2

SSL: Not in use #表示为不用ssl

Current pager: stdout

强制用户使用ssl登录

服务端A操作：MySQLserver； IP：192.168.80.102

(root@localhost) 19:45:17 [(none)]> alter user ‘ssl’@’%’ require ssl;

Query OK, 0 rows affected (0.03 sec)

(root@localhost) 19:45:20 [(none)]> show grants for ‘ssl’@’%’;

+——————————————+

| Grants for ssl@% |

+——————————————+

| GRANT ALL PRIVILEGES ON *.* TO ‘ssl’@’%’ |

+——————————————+

1 row in set (0.00 sec)

(root@localhost) 19:45:26 [(none)]> select ssl_type from mysql.user where user=’ssl’;

+———-+

| ssl_type |

+———-+

| ANY |

+———-+

1 row in set (0.00 sec)

客户端B测试：MySQClient； IP：192.168.80.101；

[root@node2 ~]# mysql -h192.168.80.102 -ussl -pssltest –skip-ssl #登录失败

mysql: [Warning] Using a password on the command line interface can be insecure.

WARNING: –ssl is deprecated and will be removed in a future version. Use –ssl-mode instead.

ERROR 1045 (28000): Access denied for user ‘ssl’@’node2’ (using password: YES)

[root@node2 ~]# mysql -h192.168.80.102 -ussl -pssltest #登录成功

mysql> \s

Current user: ssl@node2

SSL: Cipher in use is DHE-RSA-AES256-SHA

Current pager: stdout

开启证书认证(5.7.18)

服务端A操作：MySQLserver； IP：192.168.80.102

(root@localhost) 19:52:07 [(none)]> create user ‘sslcatti’@’%’ identified by ‘sslcatti’;

Query OK, 0 rows affected (0.01 sec)

(root@localhost) 19:52:11 [(none)]> grant all on *.* to ‘sslcatti’@’%’;

Query OK, 0 rows affected (0.00 sec)

(root@localhost) 19:52:29 [(none)]> alter user ‘sslcatti’@’%’ require x509;

Query OK, 0 rows affected (0.02 sec)

(root@localhost) 19:52:48 [(none)]> select ssl_type from mysql.user where user=’sslcatti’;

+———-+

| ssl_type |

+———-+

| X509 |

+———-+

1 row in set (0.00 sec)

客户端B测试：MySQClient； IP：192.168.80.101；

[root@node2 ~]# mysql -h192.168.80.102 -usslcatti -psslcatti #即使默认开启了ssl，也是无法登录的

mysql: [Warning] Using a password on the command line interface can be insecure.

ERROR 1045 (28000): Access denied for user ‘sslcatti’@’node2’ (using password: YES)

把pem文件拷贝到客服端B（A机器操作）

[root@node3 ~]# cd /data/mysqldata/

[root@node3 mysqldata]# scp client-cert.pem client-key.pem 192.168.80.101:~

root@192.168.80.101’s password:

client-cert.pem 100% 1107 1.1KB/s 00:00

client-key.pem 100% 1679 1.6KB/s 00:00

客户端用证书登录

[root@node2 ~]# mysql -h192.168.80.102 -usslcatti -psslcatti –ssl-cert=./client-cert.pem –ssl-key=./client-key.pem

mysql> \s

Current user: sslcatti@node2

SSL: Cipher in use is DHE-RSA-AES256-SHA #加密方式登录，且通过证书

mysql数据类型
1. INT类型
  - INT类型的分类

TINYINT

存储空间：1 字节

取值范围：有符号(signed) ： [-128, 127]；无符号(unsigned) ：[0, 255]

SMALLINT

存储空间： 2 字节

取值范围：有符号(signed) ： [-32768, 32767]；无符号(unsigned) ：[0, 65535]

MEDIUMINT

存储空间： 3 字节

取值范围：有符号(signed)：[-8388608, 8388607]；无符号(unsigned)：[0, 16777215]

INT

存储空间： 4 字节

取值范围：有符号(signed)：[-2147483648, 2147483647]；无符号(unsigned)：[0, 4294967295]

BIGINT

存储空间： 8 字节

取值范围：

有符号(signed) ： [-9223372036854775808, 9223372036854775807]

无符号(unsigned) ：[0, 18446744073709551615]

INT类型的使用

自增长ID：推荐使用BIGINT，而不是INT；

unsigned or signed：根据实际情况使用，一般情况下推荐默认的sigend

unsigned 的注意事项

(root@localhost) 20:34:35 [(none)]> create database dbtype;

Query OK, 1 row affected (0.01 sec)

(root@localhost) 20:34:53 [(none)]> use dbtype

Database changed

(root@localhost) 20:35:08 [dbtype]> create table t_unsigned(a int unsigned,b int unsigned);

Query OK, 0 rows affected (0.06 sec)

(root@localhost) 20:35:23 [dbtype]> insert into t_unsigned values(1,2);

Query OK, 1 row affected (0.06 sec)

(root@localhost) 20:35:39 [dbtype]> select * from t_unsigned;

+——+——+

| a | b |

+——+——+

| 1 | 2 |

+——+——+

1 row in set (0.00 sec)

(root@localhost) 20:35:52 [dbtype]> select a – b from t_unsigned;

ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in ‘(`dbtype`.`t_unsigned`.`a` – `dbtype`.`t_unsigned`.`b`)’ #表示超出数据范围

(root@localhost) 20:36:05 [dbtype]> set sql_mode = ‘no_unsigned_subtraction’; #设置sql_mode模式

Query OK, 0 rows affected, 1 warning (0.00 sec)

(root@localhost) 20:36:24 [dbtype]> select a – b from t_unsigned;

+——-+

| a – b |

+——-+

| -1 |

+——-+

1 row in set (0.00 sec)

一般情况下使用int时，推荐有符号数(signed)，使用无符号数只是比原来多一倍的取值，数量级上没有改变。

如果需要取值超过10位以上，直接选择用BIGINT类型

INT(N)

(root@localhost) 20:41:08 [dbtype]> show create table t_unsigned\G

*************************** 1. row ***************************

Table: t_unsigned

Create Table: CREATE TABLE `t_unsigned` (

`a` int(10) unsigned DEFAULT NULL,

`b` int(10) unsigned DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

1 row in set (0.00 sec)

int(N) 和 zerofill

int(N)中的N是显示宽度，不表示存储的数字的长度的上限。
zerofill表示当存储的数字长度 < N时，用数字0填充左边，直至补满长度N
当存储数字的长度超过N时，按照实际存储的数字显示

(root@localhost) 20:45:07 [dbtype]> create table t_int_num(a int(3) zerofill); #显示宽度N=3

Query OK, 0 rows affected (0.02 sec)

(root@localhost) 20:45:12 [dbtype]> insert into t_int_num values(10);

Query OK, 1 row affected (0.01 sec)

(root@localhost) 20:45:26 [dbtype]> insert into t_int_num values(2);

Query OK, 1 row affected (0.00 sec)

(root@localhost) 20:45:47 [dbtype]> select * from t_int_num;

+——+

| a |

+——+

| 010 | #不满 N=3时，左边用0填充

| 002 |

+——+

2 rows in set (0.00 sec)

(root@localhost) 20:45:59 [dbtype]> insert into t_int_num values(444444);

Query OK, 1 row affected (0.01 sec)

(root@localhost) 20:46:21 [dbtype]> select * from t_int_num;

+——–+

| a |

+——–+

| 010 |

| 002 |

| 444444 | #超过N=3的长度时，是什么数字，显示什么数字

+——–+

3 rows in set (0.00 sec)

(root@localhost) 20:46:34 [dbtype]> select a, HEX(a) from t_int_num\G

*************************** 1. row ***************************

a: 010

HEX(a): A #实际存储的还是十六进制A

*************************** 2. row ***************************

a: 002

HEX(a): 2

*************************** 3. row ***************************

a: 444444

HEX(a): 6C81C #实际存储的还是十六进制444444

3 rows in set (0.00 sec)

int(N)中的N和zerofill配合才有意义，且仅仅是显示的时候才有意义，和实际存储没有关系，不会去截取数字的长度。

AUTO_INCREMENT

自增
每张表一个
必须是索引的一部分

(root@localhost) 20:53:23 [dbtype]> create table t_auto_inc(a int auto_increment); #自增类必须为主键

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

(root@localhost) 20:53:27 [dbtype]> create table t_auto_inc(a int auto_increment primary key);

Query OK, 0 rows affected (0.05 sec)

(root@localhost) 20:53:41 [dbtype]> insert into t_auto_inc values(null); #插入null值

Query OK, 1 row affected (0.01 sec)

(root@localhost) 20:53:59 [dbtype]> select * from t_auto_inc;

+—+

| a |

+—+

| 1 | #插入NULL值，便可以让其自增，且默认从1开始

+—+

1 row in set (0.00 sec)

(root@localhost) 21:15:02 [dbtype]> insert into t_auto_inc values(1);

ERROR 1062 (23000): Duplicate entry ‘1’ for key ‘PRIMARY’

(root@localhost) 21:15:23 [dbtype]> insert into t_auto_inc values(2);

Query OK, 1 row affected (0.01 sec)

(root@localhost) 21:15:38 [dbtype]> insert into t_auto_inc values(22); #插入数值，如果值大于原来自增值就会用新值来表示

Query OK, 1 row affected (0.01 sec)

(root@localhost) 21:15:58 [dbtype]> select * from t_auto_inc;

+—-+

| a |

+—-+

| 1 |

| 2 |

| 22 | #变成自增值

+—-+

3 rows in set (0.00 sec)

(root@localhost) 21:16:03 [dbtype]> insert into t_auto_inc values(3); #插入值小于自增值22所以还是没变

Query OK, 1 row affected (0.01 sec)

(root@localhost) 21:16:25 [dbtype]> select * from t_auto_inc;

+—-+

| a |

+—-+

| 1 |

| 2 |

| 3 |

| 22 | #自增值不变

+—-+

4 rows in set (0.00 sec)

(root@localhost) 21:16:34 [dbtype]> insert into t_auto_inc values(null); #当插如null值

Query OK, 1 row affected (0.00 sec)

(root@localhost) 21:17:04 [dbtype]> select * from t_auto_inc;

+—-+

| a |

+—-+

| 1 |

| 2 |

| 3 |

| 22 |

| 23 | #自增值+1

+—-+

6 rows in set (0.00 sec)

(root@localhost) 21:17:19 [dbtype]> insert into t_auto_inc values(20); #插入20值小于自增值23

Query OK, 1 row affected (0.01 sec)

(root@localhost) 21:17:42 [dbtype]> insert into t_auto_inc values(-1); #插入-1

Query OK, 1 row affected (0.01 sec)

(root@localhost) 21:17:57 [dbtype]> select * from t_auto_inc;

+—-+

| a |

+—-+

| -1 | #插入到自增值前面

| 1 |

| 2 |

| 3 |

| 20 | #插入到自增值前面

| 22 |

| 23 |

+—-+

8 rows in set (0.00 sec)

(root@localhost) 21:18:07 [dbtype]> insert into t_auto_inc values(‘0’); #-数字 0 这个值比较特殊，插入0和插入NULL的效果是一样的，都是代表自增

Query OK, 1 row affected (0.00 sec)

(root@localhost) 21:20:38 [dbtype]> select * from t_auto_inc;

+—-+

| a |

+—-+

| -1 |

| 1 |

| 2 |

| 3 |

| 20 |

| 22 |

| 23 |

| 24 |

+—-+

9 rows in set (0.00 sec)

(root@localhost) 21:20:40 [dbtype]> update t_auto_inc set a =0 where a =-1; #无法插入0，只能update

Query OK, 1 row affected (0.00 sec)

Rows matched: 1 Changed: 1 Warnings: 0

(root@localhost) 21:21:15 [dbtype]> select * from t_auto_inc;

+—-+

| a |

+—-+

| 0 |

| 1 |

| 2 |

| 3 |

| 20 |

| 22 |

| 23 |

| 24 |

+—-+

9 rows in set (0.00 sec)

(root@localhost) 21:26:17 [dbtype]>

AUTO_INCREMENT是实例启动时，取当前表的最大值，然后 +1 即为下次自增的值。（MAX + 1)
注意: insert into tablename select NULL; 等价与 insert into tablename values (NULL);
1. 数字类型

数字类型的分类

单精度类型：FLOAT;存储空间：4 字节;精确性：低
双精度类型：DOUBLE;占用空间：8 字节;精确性：低，比FLOAT高
高精度类型：DECIMAL;占用空间：变长;精确性：非常高

注意：财务系统必须使用DECIMAL

字符串类型
- 字符串类型介绍

类型	说明	N的含义	是否有字符集	最大长度
CHAR(N)	定长字符	字符	是	255
VARCHAR(N)	变长字符	字符	是	16384
BINARY(N)	定长二进制字节	字节	否	255
VARBINARY(N)	变长二进制字节	字节	否	16384
TINYBLOB(N)	二进制大对象	字节	否	256
BLOB(N)	二进制大对象	字节	否	16K
MEDIUMBLOB(N)	二进制大对象	字节	否	16M
LONGBLOB(N)	二进制大对象	字节	否	4G
TINYTEXT(N)	大对象	字节	是	256
TEXT(N)	大对象	字节	是	16K
MEDIUMTEXT(N)	大对象	字节	是	16M
LONGTEXT(N)	大对象	字节	是	4G

N和字符集

char(N)

假设当前table的字符集的最大长度为W, 则char(N)的最大存储空间为 (N * W)Byte;假设使用UTF-8，则char(10)可以最小存储10个字节的字符，最大存储30个字节的字符，其实是另一种意义上的varchar

当存储的字符数小于N时，尾部使用空格填充，并且填充最小字节的空格

char(N)测试

(root@localhost) 09:14:53 [dbtype]> create table t_char(a char(10));

Query OK, 0 rows affected (0.05 sec)

(root@localhost) 09:15:43 [dbtype]> show create table t_char \G

*************************** 1. row ***************************

Table: t_char

Create Table: CREATE TABLE `t_char` (

`a` char(10) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

1 row in set (0.00 sec)

(root@localhost) 09:15:48 [dbtype]> insert into t_char values(‘abc’);

Query OK, 1 row affected (0.01 sec)

(root@localhost) 09:16:13 [dbtype]> insert into t_char values(‘你好哦’);

Query OK, 1 row affected (0.01 sec)

(root@localhost) 09:16:32 [dbtype]> insert into t_char values(‘你好abc’);

Query OK, 1 row affected (0.00 sec)

(root@localhost) 09:16:54 [dbtype]> insert into t_char values(‘abc你好’);

Query OK, 1 row affected (0.01 sec)

(root@localhost) 09:17:10 [dbtype]> insert into t_char values(‘很不好abc’);

Query OK, 1 row affected (0.01 sec)

(root@localhost) 09:17:26 [dbtype]> insert into t_char values(‘很不abc好’);

Query OK, 1 row affected (0.01 sec)

(root@localhost) 09:17:46 [dbtype]> insert into t_char values(‘非常不好abc’);

Query OK, 1 row affected (0.01 sec)

(root@localhost) 10:02:47 [dbtype]> insert into t_char values(‘abcdefghijk’); #超范围报错

ERROR 1406 (22001): Data too long for column ‘a’ at row 1

(root@localhost) 09:18:01 [dbtype]> select a, length(a) from t_char;

+—————–+———–+ #length(str)返回字符串str的长度，以字节为单位

| a | length(a) | #CHAR_LENGTH(str), 返回字符串str的长度，以字符为单位。

+—————–+———–+

| abc | 3 |

| 你好哦 | 9 |

| 你好abc | 9 |

| abc你好 | 9 |

| 很不好abc | 12 |

| 很不abc好 | 12 |

| 非常不好abc | 15 |

+—————–+———–+

7 rows in set (0.00 sec)

(root@localhost) 09:18:28 [dbtype]> select a,hex(a) from t_char; ##注意这里，以及下面的16进制值，一会可以对比

+—————–+——————————–+

| a | hex(a) |

+—————–+——————————–+

| abc | 616263 |

| 你好哦 | E4BDA0E5A5BDE593A6 |

| 你好abc | E4BDA0E5A5BD616263 |

| abc你好 | 616263E4BDA0E5A5BD |

| 很不好abc | E5BE88E4B88DE5A5BD616263 |

| 很不abc好 | E5BE88E4B88D616263E5A5BD |

| 非常不好abc | E99D9EE5B8B8E4B88DE5A5BD616263 |

+—————–+——————————–+

7 rows in set (0.00 sec)

(root@localhost) 09:19:18 [dbtype]> select hex(‘ ‘);

+———-+

| hex(‘ ‘) |

+———-+

| 20 | #注意`空格`,空格对应的16进制数字是 `20`

+———-+

1 row in set (0.00 sec)

[root@node3 dbtype]# hexdump -C t_char.ibd #查看t_char表实际二进制存储文件

……

0000c070 73 75 70 72 65 6d 75 6d 0a 00 00 00 10 00 24 00 |supremum……$.|

0000c080 00 00 00 12 04 00 00 40 18 07 2c b8 00 01 df 9c |…….@..,…..|

0000c090 01 10 61 62 63 20 20 20 20 20 20 20 0a 00 00 00 |..abc ….| –abc为61 62 63 空格为20 后面补了7个空格也就是 ’61 62 63 20 20 20 20 20 20 20′

0000c0a0 18 00 24 00 00 00 00 12 05 00 00 40 18 07 2d b9 |..$……..@..-.|

0000c0b0 00 01 df 9d 01 10 e4 bd a0 e5 a5 bd e5 93 a6 20 |…………… | –你好哦表示 e4 bd a0 e5 a5 bd e5 93 a6 20 后面补了空格20

0000c0c0 0a 00 00 00 20 00 24 00 00 00 00 12 06 00 00 40 |…. .$……..@|

0000c0d0 18 07 32 bc 00 01 df b0 01 10 e4 bd a0 e5 a5 bd |..2………….| –你好abc表示 e4 bd a0 e5 a5 bd 61 62 63 20 后面补了空格20

0000c0e0 61 62 63 20 0a 00 00 00 28 00 24 00 00 00 00 12 |abc ….(.$…..|

0000c0f0 07 00 00 40 18 07 33 bd 00 01 df a1 01 10 61 62 |…@..3…….ab| –abc你好表示 61 62 63 e4 bd a0 e5 a5 bd 20 后面补了空格20

0000c100 63 e4 bd a0 e5 a5 bd 20 0c 00 00 00 30 00 26 00 |c…… ….0.&.|

0000c110 00 00 00 12 08 00 00 40 18 07 3f a7 00 01 df 97 |…….@..?…..|

0000c120 01 10 e5 be 88 e4 b8 8d e5 a5 bd 61 62 63 0c 00 |………..abc..| –很不好abc表示 e5 be 88 e4 b8 8d e5 a5 bd 61 62 63 没有空格补

0000c130 00 00 38 00 26 00 00 00 00 12 09 00 00 40 18 07 |..8.&……..@..|

0000c140 40 a8 00 01 df 8d 01 10 e5 be 88 e4 b8 8d 61 62 |@………….ab| –很不abc好表示 e5 be 88 e4 b8 8d 61 62 63 e5 a5 bd 没有空格补

0000c150 63 e5 a5 bd 0f 00 00 00 40 ff 15 00 00 00 00 12 |c…….@…….|

0000c160 0a 00 00 40 18 07 4c b2 00 01 df 81 01 10 e9 9d |…@..L………|

0000c170 9e e5 b8 b8 e4 b8 8d e5 a5 bd 61 62 63 00 00 00 |……….abc…|

0000c180 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 |…………….|

……

varchar(N)

(root@localhost) 09:58:28 [dbtype]> create table t_varchar(a varchar(10));

Query OK, 0 rows affected (0.07 sec)

(root@localhost) 09:58:37 [dbtype]> show create table t_varchar \G

*************************** 1. row ***************************

Table: t_varchar

Create Table: CREATE TABLE `t_varchar` (

`a` varchar(10) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

1 row in set (0.00 sec)

(root@localhost) 09:58:41 [dbtype]> insert into t_varchar values(‘abc’);

Query OK, 1 row affected (0.01 sec)

(root@localhost) 09:58:57 [dbtype]> insert into t_varchar values(‘abc你好’);

Query OK, 1 row affected (0.00 sec)

(root@localhost) 09:59:08 [dbtype]> insert into t_varchar values(‘很不好abc’);

Query OK, 1 row affected (0.01 sec)

(root@localhost) 09:59:21 [dbtype]> insert into t_varchar values(‘非常不好abc’);

Query OK, 1 row affected (0.00 sec)

(root@localhost) 09:59:34 [dbtype]> select a, length(a) from t_varchar;

+—————–+———–+

| a | length(a) |

+—————–+———–+

| abc | 3 |

| abc你好 | 9 |

| 很不好abc | 12 |

| 非常不好abc | 15 |

+—————–+———–+

4 rows in set (0.00 sec)

(root@localhost) 09:59:48 [dbtype]> select a,hex(a) from t_varchar;

+—————–+——————————–+

| a | hex(a) |

+—————–+——————————–+

| abc | 616263 |

| abc你好 | 616263E4BDA0E5A5BD |

| 很不好abc | E5BE88E4B88DE5A5BD616263 |

| 非常不好abc | E99D9EE5B8B8E4B88DE5A5BD616263 |

+—————–+——————————–+

4 rows in set (0.00 sec)

[root@node3 dbtype]# hexdump -C t_varchar.ibd #和char一样观察，都没有进行空格的填充

……

0000c070 73 75 70 72 65 6d 75 6d 03 00 00 00 10 00 1d 00 |supremum……..|

0000c080 00 00 00 12 0f 00 00 40 18 08 17 fb 00 01 df 8a |…….@……..|

0000c090 01 10 61 62 63 09 00 00 00 18 00 23 00 00 00 00 |..abc……#….|

0000c0a0 12 10 00 00 40 18 08 18 fc 00 01 df b5 01 10 61 |….@……….a|

0000c0b0 62 63 e4 bd a0 e5 a5 bd 0c 00 00 00 20 00 26 00 |bc………. .&.|

0000c0c0 00 00 00 12 11 00 00 40 18 08 1d ff 00 01 df 8b |…….@……..|

0000c0d0 01 10 e5 be 88 e4 b8 8d e5 a5 bd 61 62 63 0f 00 |………..abc..|

0000c0e0 00 00 28 ff 8b 00 00 00 00 12 12 00 00 40 18 08 |..(……….@..|

0000c0f0 1e 80 00 01 df b6 01 10 e9 9d 9e e5 b8 b8 e4 b8 |…………….|

0000c100 8d e5 a5 bd 61 62 63 00 00 00 00 00 00 00 00 00 |….abc………|

0000c110 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 |…………….|

插入数据尾部带空格

(root@localhost) 10:12:01 [dbtype]> insert into t_char values(‘开黑中 ‘); # 后面有3个空格

Query OK, 1 row affected (0.01 sec)

(root@localhost) 10:12:46 [dbtype]> select a, length(a) from t_char;

+—————–+———–+

| a | length(a) |

+—————–+———–+

| abc | 3 |

| 你好哦 | 9 |

| 你好abc | 9 |

| abc你好 | 9 |

| 很不好abc | 12 |

| 很不abc好 | 12 |

| 非常不好abc | 15 |

| 开黑中 | 9 | #9个字节

+—————–+———–+

9 rows in set (0.00 sec)

(root@localhost) 10:13:01 [dbtype]> select a,hex(a) from t_char;

+—————–+——————————–+

| a | hex(a) |

+—————–+——————————–+

| abc | 616263 |

| 你好哦 | E4BDA0E5A5BDE593A6 |

| 你好abc | E4BDA0E5A5BD616263 |

| abc你好 | 616263E4BDA0E5A5BD |

| 很不好abc | E5BE88E4B88DE5A5BD616263 |

| 很不abc好 | E5BE88E4B88D616263E5A5BD |

| 非常不好abc | E99D9EE5B8B8E4B88DE5A5BD616263 |

| 开黑中 | E5BC80E9BB91E4B8AD | #无填充

+—————–+——————————–+

9 rows in set (0.00 sec)

(root@localhost) 10:13:45 [dbtype]> insert into t_varchar values(‘开黑中 ‘);

Query OK, 1 row affected (0.01 sec)

(root@localhost) 10:14:03 [dbtype]> select a, length(a) from t_varchar;

+—————–+———–+

| a | length(a) |

+—————–+———–+

| abc | 3 |

| abc你好 | 9 |

| 很不好abc | 12 |

| 非常不好abc | 15 |

| 开黑中 | 12 | #9个字节 + 3个字节的空格

+—————–+———–+

6 rows in set (0.00 sec)

(root@localhost) 10:14:16 [dbtype]> select a,hex(a) from t_varchar;

+—————–+——————————–+

| a | hex(a) |

+—————–+——————————–+

| abc | 616263 |

| abc你好 | 616263E4BDA0E5A5BD |

| 很不好abc | E5BE88E4B88DE5A5BD616263 |

| 非常不好abc | E99D9EE5B8B8E4B88DE5A5BD616263 |

| 开黑中 | E5BC80E9BB91E4B8AD202020 | #后面有20 20 20 ，表示3个自己的空格

+—————–+——————————–+

6 rows in set (0.00 sec)

上面的现象无法用统一的规则进行表述，但是官方文档给出的解释是，这样的安排是为了避免索引页的碎片

BLOB和TEXT

在BLOB和TEXT上创建索引时，必须指定索引前缀的长度

(root@localhost) 10:22:23 [dbtype]> create table t_text(a int primary key, b text, key(b));

ERROR 1170 (42000): BLOB/TEXT column ‘b’ used in key specification without a key length

(root@localhost) 10:22:26 [dbtype]> create table t_text(a int primary key, b text, key(b(64)));

Query OK, 0 rows affected (0.04 sec)

BLOB和TEXT列不能有默认值
BLOB和TEXT列排序时只使用该列的前max_sort_length个字节

(root@localhost) 10:22:42 [dbtype]> select @@max_sort_length;

+——————-+

| @@max_sort_length |

+——————-+

| 1024 |

+——————-+

1 row in set (0.00 sec)

不建议在MySQL中存储大型的二进制数据，比如歌曲，视频

字符集
- 常见的字符集

utf8 #utf8，最长3字节
utf8mb4 #utf8 + mobile端字符
gbk #gbk，表示的字符有限
gb18030 #gb18030,最长4个字节

(root@localhost) 10:23:04 [dbtype]> show character set;

+———-+———————————+———————+——–+

+———-+———————————+———————+——–+

| big5 | Big5 Traditional Chinese | big5_chinese_ci | 2 |

| gb2312 | GB2312 Simplified Chinese | gb2312_chinese_ci | 2 |

| utf8 | UTF-8 Unicode | utf8_general_ci | 3 |

| gbk | GBK Simplified Chinese | gbk_chinese_ci | 2 |

| utf32 | UTF-32 Unicode | utf32_general_ci | 4 |

| gb18030 | China National Standard GB18030 | gb18030_chinese_ci | 4 |

……

+———-+———————————+———————+——–+

41 rows in set (0.00 sec)

(root@localhost) 10:26:19 [dbtype]>

collation

collation的含义是指排序规则，ci（case insensitive）结尾的排序集是不区分大小写

(root@localhost) 10:40:25 [dbtype]> select ‘a’ = ‘A’;

+———–+

| ‘a’ = ‘A’ |

+———–+

| 1 | #因为大小写无关，所以返回1

+———–+

1 row in set (0.00 sec)

(root@localhost) 10:42:00 [dbtype]> create table t_ci (a varchar(10), key(a));

Query OK, 0 rows affected (0.05 sec)

(root@localhost) 10:42:14 [dbtype]> insert into t_ci values(‘a’);

Query OK, 1 row affected (0.01 sec)

(root@localhost) 10:42:29 [dbtype]> insert into t_ci values(‘A’);

Query OK, 1 row affected (0.00 sec)

(root@localhost) 10:42:41 [dbtype]> select * from t_ci where a = ‘a’;

+——+

| a |

+——+

| a |

| A | #A也被我们查到了

+——+

2 rows in set (0.00 sec)

(root@localhost) 10:43:14 [dbtype]> select ‘a’ =’a ‘; #插入5个空格也会被查询到，这是个坑

+————-+

| ‘a’ =’a ‘ |

+————-+

| 1 |

+————-+

1 row in set (0.00 sec)

(root@localhost) 10:43:32 [dbtype]> select ‘a’ =’A ‘;

+————-+

| ‘a’ =’A ‘ |

+————-+

| 1 |

+————-+

1 row in set (0.00 sec)

上面的情况如果从业务的角度上看，可以很好理解，比如创建一个用户叫做GAVIN，你是不希望再创建一个叫做gavin的用户

修改默认的collation

(root@localhost) 10:43:44 [dbtype]> set names utf8mb4 collate utf8mb4_bin;

Query OK, 0 rows affected (0.00 sec)

(root@localhost) 10:44:07 [dbtype]> select ‘a’ = ‘A’;

+———–+

| ‘a’ = ‘A’ |

+———–+

| 0 |

+———–+

1 row in set (0.00 sec)

字符集的指定，可以在创建数据库的时候指定，也可以在创建表的时候单独指定，也可以创建列的时候进行指定

集合类型

集合类型ENUM 和 SET
ENUM类型最多允许65536个值
SET类型最多允许64个值
通过sql_mode参数可以用户约束检查
ENUM类型

(root@localhost) 19:45:40 [dbtype]> create table t_col (user varchar(10),sex enum(‘male’, ‘female’)); #虽然写的是字符串，单其实存储的整型，效率还是可以的

Query OK, 0 rows affected (0.07 sec)

(root@localhost) 19:46:29 [dbtype]> insert into t_col values(“fanghao”,”male”); #正常插入

Query OK, 1 row affected (0.10 sec)

(root@localhost) 19:46:57 [dbtype]> insert into t_col values(“yanglaoshi”,”female”);

Query OK, 1 row affected (0.01 sec)

(root@localhost) 19:47:36 [dbtype]> set sql_mode=”; #设置为sql_mode 空模式

Query OK, 0 rows affected, 1 warning (0.00 sec)

(root@localhost) 19:47:56 [dbtype]> insert into t_col values(“haozong”,”xmale”); #可以插入非法sql

Query OK, 1 row affected, 1 warning (0.01 sec)

(root@localhost) 19:48:45 [dbtype]> select * from t_col;

+————+——–+

| user | sex |

+————+——–+

| fanghao | male |

| yanglaoshi | female |

| haozong | |

+————+——–+

5 rows in set (0.00 sec)

(root@localhost) 19:48:17 [dbtype]> set sql_mode=’strict_trans_tables’; #设置为严格模式

Query OK, 0 rows affected, 1 warning (0.00 sec)

(root@localhost) 19:48:31 [dbtype]> insert into t_col values(“xiaoxiaohao”,”xmale”); #插入报错

ERROR 1406 (22001): Data too long for column ‘user’ at row 1

集合类型的排序

(root@localhost) 10:35:45 [dbtype]> create table t_col_sort(user char(10),type enum(‘aaa’,’zzz’,’bbb’,’yyy’,’fff’)); #aaa=0, zzz=1, bbb=2, yyy=3, fff=4

Query OK, 0 rows affected (0.03 sec)

(root@localhost) 10:35:57 [dbtype]> insert into t_col_sort values(“user1″,”aaa”);

Query OK, 1 row affected (0.01 sec)

(root@localhost) 10:36:21 [dbtype]> insert into t_col_sort values(“user2″,”bbb”);

Query OK, 1 row affected (0.01 sec)

(root@localhost) 10:36:42 [dbtype]> insert into t_col_sort values(“user3″,”yyy”);

Query OK, 1 row affected (0.01 sec)

(root@localhost) 10:37:11 [dbtype]> insert into t_col_sort values(“user4″,”zzz”);

Query OK, 1 row affected (0.00 sec)

(root@localhost) 10:37:44 [dbtype]> select * from t_col_sort order by type asc; #以type作为key，进行升序排序

+——-+——+

| user | type |

+——-+——+

| user1 | aaa |

| user4 | zzz |

| user2 | bbb |

| user3 | yyy |

+——-+——+

4 rows in set (0.00 sec)

(root@localhost) 10:38:07 [dbtype]> select * from t_col_sort; #枚举类型实际是整型数据，默认按照插入顺序进行排列

+——-+——+

| user | type |

+——-+——+

| user1 | aaa |

| user2 | bbb |

| user3 | yyy |

| user4 | zzz |

+——-+——+

4 rows in set (0.00 sec)

(root@localhost) 10:38:26 [dbtype]> select * from t_col_sort order by cast(type as char) asc; #使用cast()函数转换成某种型，这里我们转成char型，然后进行排序(ascii)

+——-+——+

| user | type |

+——-+——+

| user1 | aaa |

| user2 | bbb |

| user3 | yyy |

| user4 | zzz |

+——-+——+

4 rows in set (0.00 sec)

(root@localhost) 10:39:01 [dbtype]> select * from t_col_sort order by concat(type) asc; #concat()是连接字符串函数，相当于字符串排序

+——-+——+

| user | type |

+——-+——+

| user1 | aaa |

| user2 | bbb |

| user3 | yyy |

| user4 | zzz |

+——-+——+

4 rows in set (0.01 sec)

(root@localhost) 10:39:44 [dbtype]> select concat(“abc”, “大家好”);

+—————————-+

| concat(“abc”, “大家好”) |

+—————————-+

| abc大家好 |

+—————————-+

1 row in set (0.00 sec)

日期类型
- 日志类型分类

日期类型	占用空间(byte)（<5.6）	占用空间(byte)(>=5.6)	表示范围
DATETIME	8	5 + 微秒存储空间	1000-01-01 00:00:00 ~ 9999-12-31 23:59:59
DATE	3	3	1000-01-01 ~ 9999-12-31
TIMESTAMP	4	4 + 微秒存储空间	1970-01-01 00:00:00UTC ~ 2038-01-19 03:14:07UTC
YEAR	1	1	YEAR(2):1970-2070, YEAR(4):1901-2155
TIME	3	3 + 微秒存储空间	-838:59:59 ~ 838:59:59

微秒位数	所需存储空间
0	0
1, 2	1 byte
3, 4	2 bytes
5, 6	3 bytes

TIMESTAMP 带时区功能

TIMESTAMP和DATETIME

(root@localhost) 20:12:30 [dbtype]> create table t_time(a timestamp, b datetime);

Query OK, 0 rows affected (0.05 sec)

(root@localhost) 20:13:01 [dbtype]> insert into t_time values(now(),now());

Query OK, 1 row affected (0.01 sec)

(root@localhost) 20:13:17 [dbtype]> select * from t_time;

+———————+———————+

| a | b |

+———————+———————+

| 2019-02-14 20:13:17 | 2019-02-14 20:13:17 |

+———————+———————+

1 row in set (0.00 sec)

(root@localhost) 20:13:31 [dbtype]> select @@time_zone;

+————-+

| @@time_zone |

+————-+

| SYSTEM |

+————-+

1 row in set (0.00 sec)

(root@localhost) 20:13:50 [dbtype]> set time_zone=’+00:00′;

Query OK, 0 rows affected (0.00 sec)

(root@localhost) 20:14:08 [dbtype]> select * from t_time;

+———————+———————+

| a | b |

+———————+———————+

| 2019-02-14 12:13:17 | 2019-02-14 20:13:17 | #相差8个小时，时区的差别体现出来了

+———————+———————+

1 row in set (0.00 sec)

微秒

从MySQL5.6.X开始，支持微秒，最大显示6位

(root@localhost) 20:17:38 [dbtype]> select now(6);

+—————————-+

| now(6) |

+—————————-+

| 2019-02-14 12:17:41.936884 |

+—————————-+

1 row in set (0.00 sec)

(root@localhost) 20:17:41 [dbtype]> select now(7); #不支持，最大到6位

ERROR 1426 (42000): Too-big precision 7 specified for ‘now’. Maximum is 6.

(root@localhost) 20:17:56 [dbtype]> create table t_time_fac (t datetime(6));

Query OK, 0 rows affected (0.04 sec)

(root@localhost) 20:18:23 [dbtype]> insert into t_time_fac values(now(6));

Query OK, 1 row affected (0.01 sec)

(root@localhost) 20:18:48 [dbtype]> select * from t_time_fac;

+—————————-+

| t |

+—————————-+

| 2019-02-14 12:18:48.751350 | #由于是用了6位微秒位数，根据表格显示，

+—————————-+ #实际存储的空间是 5 + 3 = 8 byte

1 row in set (0.00 sec)

时间函数

常用函数

函数名	函数说明	备注
NOW	返回SQL执行时的时间	如果不考虑其他因素，可以理解为写完SQL，敲下回车瞬间的时间
CURRENT_TIMESTAMP	与NOW()函数同义
SYSDATE	返回函数执行时的时间	MySQL处理你的函数时的时间，统一SQL语句中，大于NOW
DATA_ADD(date, interval expr uint)	增加时间
DATA_SUB(date, interval expr uint)	减少时间	可用ADD，然后unit给负数
DATE FORMAT	格式化时间

所有时间函数–官方文档https://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html

NOW和SYSDATE的区别

(root@localhost) 20:36:14 [(none)]> select now(),sysdate();

+———————+———————+

| now() | sysdate() |

+———————+———————+

| 2019-02-14 20:36:16 | 2019-02-14 20:36:16 |

+———————+———————+

1 row in set (0.01 sec)

(root@localhost) 20:36:16 [(none)]> select now(),sysdate(),sleep(2),now(),sysdate();

+———————+———————+———-+———————+———————+

+———————+———————+———-+———————+———————+

| 2019-02-14 20:36:39 | 2019-02-14 20:36:39 | 0 | 2019-02-14 20:36:39 | 2019-02-14 20:36:41 |

+———————+———————+———-+———————+———————+

1 row in set (2.00 sec)

(root@localhost) 20:36:41 [(none)]> select date_add(now(),interval 7 day); #增加7天

+——————————–+

| date_add(now(),interval 7 day) |

+——————————–+

| 2019-02-21 20:37:58 |

+——————————–+

1 row in set (0.00 sec)

(root@localhost) 20:37:58 [(none)]> select date_add(now(),interval 1 month); #增量1个月

+———————————-+

| date_add(now(),interval 1 month) |

+———————————-+

| 2019-03-14 20:38:20 |

+———————————-+

1 row in set (0.00 sec)

(root@localhost) 20:38:20 [(none)]> select date_add(now(),interval -2 month); #减少2个月

+———————————–+

| date_add(now(),interval -2 month) |

+———————————–+

| 2018-12-14 20:38:39 |

+———————————–+

1 row in set (0.00 sec)

(root@localhost) 20:38:39 [(none)]> SELECT DATE_FORMAT((select now(6)), ‘%H:%i:%s’);

+——————————————+

| DATE_FORMAT((select now(6)), ‘%H:%i:%s’) |

+——————————————+

| 20:39:05 |

+——————————————+

1 row in set (0.00 sec)

字段更新时间

(root@localhost) 20:45:09 [dbtype]> create table t_field_update(a int(32), b timestamp not null default current_timestamp on update current_timestamp);

Query OK, 0 rows affected (0.06 sec)

(root@localhost) 20:45:13 [dbtype]> insert into t_field_update values(1, now(6));

Query OK, 1 row affected (0.02 sec)

(root@localhost) 20:45:53 [dbtype]> select * from t_field_update;

+——+———————+

| a | b |

+——+———————+

| 1 | 2019-02-14 20:45:54 | #上面使用了now(6)，但是这里没有微秒，是因为定义的时候就是timestamp，如果写成timestamp(6),就可以显示微秒

+——+———————+

1 row in set (0.00 sec)

(root@localhost) 20:46:09 [dbtype]> update t_field_update set a=10 where a=1; #只更新a字段

Query OK, 1 row affected (0.01 sec)

Rows matched: 1 Changed: 1 Warnings: 0

(root@localhost) 20:46:54 [dbtype]> select * from t_field_update; #发现b字段跟着改变了

+——+———————+

| a | b |

+——+———————+

| 10 | 2019-02-14 20:46:36 |

+——+———————+

1 row in set (0.01 sec)

(root@localhost) 20:47:13 [dbtype]> create table t_time_disp(a int(10),b timestamp(6) not null default current_timestamp(6) on update current_timestamp(6)); #定义了微妙

Query OK, 0 rows affected (0.04 sec)

(root@localhost) 20:47:35 [dbtype]> insert into t_time_disp values(1, now(6));

Query OK, 1 row affected (0.01 sec)

(root@localhost) 20:47:51 [dbtype]> select * from t_time_disp;

+——+—————————-+

| a | b |

+——+—————————-+

| 1 | 2019-02-14 20:47:51.205962 | #插入了now(6), 这里就显示了6位微秒

+——+—————————-+

1 row in set (0.00 sec)

JSON类型
- JSON介绍
什么是 JSON ？

JSON 指的是 JavaScript 对象表示法（JavaScript Object Notation）
JSON 是轻量级的文本数据交换格式
JSON 独立于语言 *
JSON 具有自我描述性，更易理解

7.8开始支持JSON数据类型。
对比存储在字符串，JSON格式的JSON数据类型提供了这些优点：

自动验证存储在JSON列中的JSON文档。无效的文档会产生错误。
优化的存储格式

官方文档(JSON类型)https://dev.mysql.com/doc/refman/5.7/en/json.html

JSON格式示例

这个 employee 对象是包含 3 个员工记录（对象）的数组。

{

“employees”: [

{ “firstName”:”John” , “lastName”:”Doe” },

{ “firstName”:”Anna” , “lastName”:”Smith” },

{ “firstName”:”Peter” , “lastName”:”Jones” }

]

}

JSON VS BLOB

JSON

JSON数据可以做有效性检查;
JSON使得查询性能提升;
JSON支持部分属性索引，通过虚拟列的功能可以对JSON中的部分数据进行索引;

BLOB

BLOB类型无法在数据库层做约束性检查;
BLOB进行查询，需要遍历所有字符串;
BLOB做只能做指定长度的索引;
- 结构化和非结构化

结构化

二维表结构（行和列）
使用SQL语句进行操作

非结构化

使用Key-Value格式定义数据，无结构定义
Value可以嵌套Key-Value格式的数据
使用JSON进行实现

SQL创建User表

create table user (

id bigint not null auto_increment,

user_name varchar(10),

age int,

primary key(id)

);

JSON定义的User表

db.user.insert({

user_name:”tom”,

age:30

})

db.createCollection(“user”)

JSON操作示例

JSON入门

创建带json字段的表

CREATE TABLE t_user (

uid BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,

name VARCHAR(32) NOT NULL,

email VARCHAR(128) NOT NULL,

address VARCHAR(256) NOT NULL,

UNIQUE KEY (name),

UNIQUE KEY (email)

)charset=utf8mb4;

INSERT INTO t_user VALUES (NULL,’David’,’david@gmail’,’Shanghai …’);

INSERT INTO t_user VALUES (NULL,’Amy’,’amy@gmail’,’Beijing …’);

INSERT INTO t_user VALUES (NULL,’Tom’,’tom@gmail’,’Guangzhou …’);

CREATE TABLE t_user_json(

uid BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,

data JSON

);

JSON_OBJECT将对象列表转化成JSON对象(key唯一,参数个数必须是偶数key，value)

insert into t_user_json SELECT uid,JSON_OBJECT(‘name’,name,’email’,email,’address’,address) AS data FROM t_user;

(root@localhost) 14:37:36 [dbtype]> select * from t_user_json;

+—–+———————————————————————-+

| uid | data |

+—–+———————————————————————-+

| 1 | {“name”: “David”, “email”: “david@gmail”, “address”: “Shanghai …”} |

| 2 | {“name”: “Amy”, “email”: “amy@gmail”, “address”: “Beijing …”} |

| 3 | {“name”: “Tom”, “email”: “tom@gmail”, “address”: “Guangzhou …”} |

+—–+———————————————————————-+

JSON_EXTRACT() 是JSON提取函数，$.address 就是一个 JSON path，表示定位文档的 address 字段

(root@localhost) 14:37:59 [dbtype]> SELECT uid,JSON_EXTRACT(data,’$.address’) from t_user_json;

+—–+——————————–+

| uid | JSON_EXTRACT(data,’$.address’) |

+—–+——————————–+

| 1 | “Shanghai …” |

| 2 | “Beijing …” |

| 3 | “Guangzhou …” |

+—–+——————————–+

3 rows in set (0.00 sec)

JSON_INSERT() 是JSON 插入函数

UPDATE t_user_json set data = json_insert(data,”$.address2″,”HangZhou …”) where uid = 1;

(root@localhost) 14:39:28 [dbtype]> SELECT uid,JSON_EXTRACT(data,’$.address2′) from t_user_json;

+—–+———————————+

| uid | JSON_EXTRACT(data,’$.address2′) |

+—–+———————————+

| 1 | “HangZhou …” |

| 2 | NULL |

| 3 | NULL |

+—–+———————————+

3 rows in set (0.00 sec)

JSON_MERGE将两个或以上的JSON对象融合

(root@localhost) 14:39:56 [dbtype]> select json_merge(JSON_EXTRACT(data,’$.address’) ,JSON_EXTRACT(data,’$.address2′)) from t_user_json;

+—————————————————————————–+

| json_merge(JSON_EXTRACT(data,’$.address’) ,JSON_EXTRACT(data,’$.address2′)) |

+—————————————————————————–+

| [“Shanghai …”, “HangZhou …”] |

| NULL |

+—————————————————————————–+

3 rows in set, 1 warning (0.00 sec)

begin;

UPDATE t_user_json set data = json_array_append(data,”$.address”,JSON_EXTRACT(data,’$.address2′))

where JSON_EXTRACT(data,’$.address2′) IS NOT NULL AND uid >0;

select JSON_EXTRACT(data,’$.address’) from t_user_json;

UPDATE t_user_json set data = JSON_REMOVE(data,’$.address2′) where uid>0;

commit;

JSON常用函数介绍

(root@localhost) 14:55:32 [dbtype]> create table demo(id int unsigned primary key auto_increment,comment json);

Query OK, 0 rows affected (0.01 sec)

(root@localhost) 14:56:57 [dbtype]> insert into demo(id,comment) values(1,'{“programmers”: [{“email”: “aaaa”, “lastName”: “McLaughlin”, “firstName”: “Brett”}, {“email”: “bbbb”, “lastName”: “Hunter”, “firstName”: “Jason”}]}’);

Query OK, 1 row affected (0.01 sec)

获取第一层的key值 json_keys

(root@localhost) 14:57:08 [dbtype]> select json_keys(comment) from demo;

+——————–+

| json_keys(comment) |

+——————–+

| [“programmers”] |

+——————–+

1 row in set (0.00 sec)

从JSON中提取 json_extract

(root@localhost) 14:57:58 [dbtype]> select json_extract(comment,’$.programmers[0].email’) from demo;

+————————————————+

| json_extract(comment,’$.programmers[0].email’) |

+————————————————+

| “aaaa” |

+————————————————+

1 row in set (0.00 sec)

从Json中去除元素 json_remove

(root@localhost) 14:59:54 [dbtype]> select json_extract(comment,’$.programmers’),json_remove(comment,’$.programmers[0]’) from demo\G

*************************** 1. row ***************************

json_extract(comment,’$.programmers’): [{“email”: “aaaa”, “lastName”: “McLaughlin”, “firstName”: “Brett”}, {“email”: “bbbb”, “lastName”: “Hunter”, “firstName”: “Jason”}]

json_remove(comment,’$.programmers[0]’): {“programmers”: [{“email”: “bbbb”, “lastName”: “Hunter”, “firstName”: “Jason”}]}

1 row in set (0.00 sec)

Json是否包含当前路径 json_contains_path

(root@localhost) 15:04:11 [dbtype]> select json_contains_path(comment,”all”,”$.programmers[0].firstName”) from demo;

+—————————————————————-+

| json_contains_path(comment,”all”,”$.programmers[0].firstName”) |

+—————————————————————-+

| 1 |

+—————————————————————-+

1 row in set (0.00 sec)

(root@localhost) 15:04:14 [dbtype]> select json_contains_path(comment,”all”,”$.programmers[0].firstNam”) from demo;

+—————————————————————+

| json_contains_path(comment,”all”,”$.programmers[0].firstNam”) |

+—————————————————————+

| 0 |

+—————————————————————+

1 row in set (0.00 sec)

判断JSON当前路径对象类型 JSON_TYPE

(root@localhost) 15:04:40 [dbtype]> select JSON_TYPE(comment),JSON_TYPE(comment->”$.programmers[0].firstName”),JSON_TYPE(comment->”$.programmers”)=”ARRAY” from demo;

+——————–+————————————————–+———————————————+

| JSON_TYPE(comment) | JSON_TYPE(comment->”$.programmers[0].firstName”) | JSON_TYPE(comment->”$.programmers”)=”ARRAY” |

+——————–+————————————————–+———————————————+

| OBJECT | STRING | 1 |

+——————–+————————————————–+———————————————+

1 row in set (0.00 sec)

创建数组对象 JSON_ARRAY

(root@localhost) 15:05:43 [dbtype]> SELECT JSON_ARRAY(‘a’, 1, RAND());

+——————————+

| JSON_ARRAY(‘a’, 1, RAND()) |

+——————————+

| [“a”, 1, 0.5044683539350222] |

+——————————+

1 row in set (0.00 sec)

将对象列表转化成JSON对象(key唯一, key – value) JSON_OBJECT

(root@localhost) 15:06:40 [dbtype]> SELECT JSON_OBJECT(‘key1’, 1, ‘key2’, ‘abc’),JSON_OBJECT(‘key1’, 1, ‘key2’, ‘abc’, ‘key1’, ‘def’);

+—————————————+——————————————————+

| JSON_OBJECT(‘key1’, 1, ‘key2’, ‘abc’) | JSON_OBJECT(‘key1’, 1, ‘key2’, ‘abc’, ‘key1’, ‘def’) |

+—————————————+——————————————————+

| {“key1”: 1, “key2”: “abc”} | {“key1”: 1, “key2”: “abc”} |

+—————————————+——————————————————+

1 row in set (0.00 sec)

将两个或以上的JSON对象融合 JSON_MERGE

(root@localhost) 15:10:11 [dbtype]> SELECT JSON_MERGE(‘[“a”, 1]’, ‘{“key”: “value”}’),JSON_MERGE(‘{“a”: 2,”b”:”2″}’,'{“key”: “value”}’),JSON_MERGE(‘{“a”: 1, “b”: 2}’, ‘{“c”: 3, “a”: 4}’)\G

*************************** 1. row ***************************

JSON_MERGE(‘[“a”, 1]’, ‘{“key”: “value”}’): [“a”, 1, {“key”: “value”}]

JSON_MERGE(‘{“a”: 2,”b”:”2″}’,'{“key”: “value”}’): {“a”: 2, “b”: “2”, “key”: “value”}

JSON_MERGE(‘{“a”: 1, “b”: 2}’, ‘{“c”: 3, “a”: 4}’): {“a”: [1, 4], “b”: 2, “c”: 3}

1 row in set, 3 warnings (0.00 sec)

用户定义的变量不能JSON数据类型函数 COLLATION(返回字符串参数的排序方式)

(root@localhost) 15:10:20 [dbtype]> SET @j = JSON_OBJECT(‘key’, ‘value’); SELECT CHARSET(@j), COLLATION(@j);

Query OK, 0 rows affected (0.00 sec)

+————-+—————+

| CHARSET(@j) | COLLATION(@j) |

+————-+—————+

| utf8mb4 | utf8mb4_bin |

+————-+—————+

1 row in set (0.00 sec)

(root@localhost) 15:13:53 [dbtype]> SELECT JSON_ARRAY(‘x’) = JSON_ARRAY(‘X’); #因为utf8mb4_bin是二进制排序规则，JSON值比较是区分大小写的。

+———————————–+

| JSON_ARRAY(‘x’) = JSON_ARRAY(‘X’) |

+———————————–+

| 0 |

+———————————–+

1 row in set (0.00 sec

JSON对大小写敏感,SQL非空不敏感

(root@localhost) 15:15:13 [dbtype]> SELECT JSON_VALID(‘null’), JSON_VALID(‘Null’), JSON_VALID(‘NULL’),ISNULL(null), ISNULL(Null), ISNULL(NULL);

+——————–+——————–+——————–+————–+————–+————–+

+——————–+——————–+——————–+————–+————–+————–+

| 1 | 0 | 0 | 1 | 1 | 1 |

+——————–+——————–+——————–+————–+————–+————–+

1 row in set (0.00 sec)

将JSON中元素替换;如果位置不存在，则追加 JSON_SET

(root@localhost) 15:16:18 [dbtype]> SET @j = ‘[“a”, {“b”: [true, false]}, [10, 20]]’;SELECT JSON_SET(@j, ‘$[1].b[0]’, 1, ‘$[2][3]’, 3);

Query OK, 0 rows affected (0.00 sec)

+——————————————–+

| JSON_SET(@j, ‘$[1].b[0]’, 1, ‘$[2][3]’, 3) |

+——————————————–+

| [“a”, {“b”: [1, false]}, [10, 20, 3]] |

+——————————————–+

1 row in set (0.00 sec)

向JSON中添加元素,原来位置存在数据不会替换 JSON_INSERT

(root@localhost) 15:22:48 [dbtype]> SELECT JSON_INSERT(@j, ‘$[1].b[0]’, 1, ‘$[2][2]’, 2);

+———————————————–+

| JSON_INSERT(@j, ‘$[1].b[0]’, 1, ‘$[2][2]’, 2) |

+———————————————–+

| [“a”, {“b”: [true, false]}, [10, 20, 2]] |

+———————————————–+

1 row in set (0.00 sec)

替换JSON中原有值,不存在的不会替换 JSON_REPLACE

(root@localhost) 15:22:52 [dbtype]> SELECT JSON_REPLACE(@j, ‘$[1].b[0]’, JSON_OBJECT(‘key’, ‘value’), ‘$[2][2]’, 2);

+————————————————————————–+

| JSON_REPLACE(@j, ‘$[1].b[0]’, JSON_OBJECT(‘key’, ‘value’), ‘$[2][2]’, 2) |

+————————————————————————–+

| [“a”, {“b”: [{“key”: “value”}, false]}, [10, 20]] |

+————————————————————————–+

1 row in set (0.00 sec)

移除JSON元素列表 JSON_REMOVE

(root@localhost) 15:26:45 [dbtype]> SELECT JSON_REMOVE(@j, ‘$[2]’, ‘$[1].b[1]’, ‘$[1].b[1]’);

+—————————————————+

| JSON_REMOVE(@j, ‘$[2]’, ‘$[1].b[1]’, ‘$[1].b[1]’) |

+—————————————————+

| [“a”, {“b”: [true]}] |

+—————————————————+

1 row in set (0.00 sec)

判断JSON中是否包含’值’,在这个路径下 JSON_CONTAINS

(root@localhost) 15:31:05 [dbtype]> SELECT JSON_CONTAINS(‘{“a”: 1, “b”: 2, “c”: {“d”: 4}}’, ‘1’, ‘$.a’);

+————————————————————–+

| JSON_CONTAINS(‘{“a”: 1, “b”: 2, “c”: {“d”: 4}}’, ‘1’, ‘$.a’) |

+————————————————————–+

| 1 |

+————————————————————–+

1 row in set (0.00 sec)

去掉JSON格式””号 json_unquote , ->> 这两个是等价的

(root@localhost) 15:33:45 [dbtype]> select json_unquote(comment->”$.programmers[1].email”), comment->>”$.programmers[1].email”from demo;

+————————————————-+————————————+

| json_unquote(comment->”$.programmers[1].email”) | comment->>”$.programmers[1].email” |

+————————————————-+————————————+

| bbbb | bbbb |

+————————————————-+————————————+

1 row in set (0.00 sec)

json_remove 从json记录中删除数据

(root@localhost) 15:33:48 [dbtype]> set @j = ‘[“a”, [“b”, “c”], “d”]’;

Query OK, 0 rows affected (0.00 sec)

(root@localhost) 15:34:49 [dbtype]> select json_remove(@j, ‘$[1]’);

+————————-+

| json_remove(@j, ‘$[1]’) |

+————————-+

| [“a”, “d”] |

+————————-+

1 row in set (0.00 sec)

(root@localhost) 15:36:50 [dbtype]> update t_user_json set data = json_remove(data, “$.address_2”) where uid = 1;

官方文档(JSON函数) https://dev.mysql.com/doc/refman/5.7/en/json-function-reference.html

JSON创建索引

JSON类型数据本身无法直接创建索引，需要将需要索引的JSON数据重新生成虚拟列(Virtual Columns)之后，对该列进行索引

官方文档–JSON创建索引https://dev.mysql.com/doc/refman/5.7/en/create-table.html#create-table-secondary-indexes-virtual-columns

新建表时创建JSON索引

create table test_index_1(

data json,

gen_col varchar(10) generated always as (json_extract(data, ‘$.name’)), #抽取data中的name，生成新的一列，名字为gen_col

index idx (gen_col) #将gen_col 作为索引

);

(root@localhost) 15:42:45 [dbtype]> show create table test_index_1\G

*************************** 1. row ***************************

Table: test_index_1

Create Table: CREATE TABLE `test_index_1` (

`data` json DEFAULT NULL,

`gen_col` varchar(10) GENERATED ALWAYS AS (json_extract(`data`,’$.name’)) VIRTUAL,

KEY `idx` (`gen_col`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

1 row in set (0.00 sec)

(root@localhost) 15:45:11 [dbtype]> insert into test_index_1(data) values (‘{“name”:”tom”, “age”:18, “address”:”SH”}’);

Query OK, 1 row affected (0.00 sec)

(root@localhost) 15:45:15 [dbtype]> insert into test_index_1(data) values (‘{“name”:”jim”, “age”:28, “address”:”SZ”}’);

Query OK, 1 row affected (0.01 sec)

(root@localhost) 15:45:28 [dbtype]> select * from test_index_1;

+———————————————+———+

| data | gen_col |

+———————————————+———+

| {“age”: 18, “name”: “tom”, “address”: “SH”} | “tom” |

| {“age”: 28, “name”: “jim”, “address”: “SZ”} | “jim” |

+———————————————+———+

2 rows in set (0.00 sec)

(root@localhost) 15:45:37 [dbtype]> select json_extract(data,”$.name”) as username from test_index_1 where gen_col=”tom”; #如果这样做，为空，原因如下

Empty set (0.00 sec)

(root@localhost) 15:46:04 [dbtype]> select hex(‘”‘);

+———-+

| hex(‘”‘) |

+———-+

| 22 | #双引号的 16进制

+———-+

1 row in set (0.00 sec)

(root@localhost) 15:46:14 [dbtype]> select hex(gen_col) from test_index_1;

+————–+

| hex(gen_col) |

+————–+

| 226A696D22 | #双引号本身也作为了存储内容

| 22746F6D22 |

+————–+

2 rows in set (0.00 sec)

(root@localhost) 15:46:33 [dbtype]> select json_extract(data,”$.name”) as username from test_index_1 where gen_col='”tom”‘; #使用'”tome”‘,用单引号括起来

+———-+

| username |

+———-+

| “tom” |

+———-+

1 row in set (0.00 sec)

(root@localhost) 15:46:57 [dbtype]> explain select json_extract(data,”$.name”) as username from test_index_1 where gen_col='”tom”‘\G

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: test_index_1

partitions: NULL

type: ref

possible_keys: idx #使用了 key idx

key: idx

key_len: 43

ref: const

rows: 1

filtered: 100.00

Extra: NULL

1 row in set, 1 warning (0.00 sec)

建立表的时候去掉双引用

(root@localhost) 15:47:17 [dbtype]> CREATE TABLE `test_index_2` (

-> `data` json DEFAULT NULL,

-> `gen_col` varchar(10) GENERATED ALWAYS AS (json_unquote( #使用json_unquote函数进行去掉双引号

-> json_extract(data, “$.name”)

-> )) VIRTUAL,

-> KEY `idx` (`gen_col`)

-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Query OK, 0 rows affected (0.03 sec)

(root@localhost) 15:47:50 [dbtype]> show create table test_index_2\G

*************************** 1. row ***************************

Table: test_index_2

Create Table: CREATE TABLE `test_index_2` (

`data` json DEFAULT NULL,

`gen_col` varchar(10) GENERATED ALWAYS AS (json_unquote(json_extract(`data`,’$.name’))) VIRTUAL,

KEY `idx` (`gen_col`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

1 row in set (0.00 sec)

(root@localhost) 15:48:17 [dbtype]> insert into test_index_2(data) values (‘{“name”:”tom”, “age”:18, “address”:”SH”}’);

Query OK, 1 row affected (0.00 sec)

(root@localhost) 15:48:57 [dbtype]> insert into test_index_2(data) values (‘{“name”:”jim”, “age”:28, “address”:”SZ”}’);

Query OK, 1 row affected (0.00 sec)

(root@localhost) 15:49:13 [dbtype]> select json_extract(data,”$.name”) as username from test_index_2 where gen_col=”tom”; #未加单引号

+———-+

| username |

+———-+

| “tom” |

+———-+

1 row in set (0.00 sec)

(root@localhost) 15:49:27 [dbtype]> explain select json_extract(data,”$.name”) as username from test_index_2 where gen_col=”tom”\G

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: test_index_2

partitions: NULL

type: ref

possible_keys: idx #使用了 key idx

key: idx

key_len: 43

ref: const

rows: 1

filtered: 100.00

Extra: NULL

1 row in set, 1 warning (0.00 sec)

修改已存在的表创建JSON索引

(root@localhost) 15:57:35 [dbtype]> CREATE TABLE `user` (

-> `uid` int(11) NOT NULL AUTO_INCREMENT,

-> `data` json DEFAULT NULL,

-> PRIMARY KEY (`uid`)

-> ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 ;

Query OK, 0 rows affected (0.04 sec)

(root@localhost) 15:58:02 [dbtype]> show create table user\G

*************************** 1. row ***************************

Table: user

Create Table: CREATE TABLE `user` (

`uid` int(11) NOT NULL AUTO_INCREMENT,

`data` json DEFAULT NULL,

PRIMARY KEY (`uid`)

) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4

1 row in set (0.00 sec)

(root@localhost) 16:00:15 [dbtype]> insert into user SELECT uid,JSON_OBJECT(‘name’,name,’email’,email,’address’,address) AS data FROM t_user;

(root@localhost) 16:01:43 [dbtype]> select * from user; +—–+———————————————————————-+

| uid | data |

+—–+———————————————————————-+

| 1 | {“name”: “David”, “email”: “david@gmail”, “address”: “Shanghai …”} |

| 2 | {“name”: “Amy”, “email”: “amy@gmail”, “address”: “Beijing …”} |

| 3 | {“name”: “Tom”, “email”: “tom@gmail”, “address”: “Guangzhou …”} |

+—–+———————————————————————-+

3 rows in set (0.00 sec)

(root@localhost) 16:01:57 [dbtype]> alter table user add user_name varchar(32) generated always as (json_extract(data,”$.name”)) virtual; # virtual 关键字是不将该列的字段值存储，对应的是stored

Query OK, 0 rows affected (0.10 sec)

Records: 0 Duplicates: 0 Warnings: 0

(root@localhost) 16:04:46 [dbtype]> alter table user add index idx(user_name);

Query OK, 0 rows affected (0.07 sec)

Records: 0 Duplicates: 0 Warnings: 0

(root@localhost) 16:04:55 [dbtype]> select * from user where user_name='”tom”‘; #加单引号

+—–+——————————————————————-+———–+

| uid | data | user_name |

+—–+——————————————————————-+———–+

| 3 | {“name”: “Tom”, “email”: “tom@gmail”, “address”: “Guangzhou …”} | “Tom” |

+—–+——————————————————————-+———–+

1 row in set (0.00 sec)

(root@localhost) 16:05:07 [dbtype]> explain select * from user where user_name='”tom”‘\G

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: user

partitions: NULL

type: ref

possible_keys: idx

key: idx #使用了 key idx

key_len: 131

ref: const

rows: 1

filtered: 100.00

Extra: NULL

1 row in set, 1 warning (0.00 sec)

(root@localhost) 16:05:56 [dbtype]> show create table user\G

*************************** 1. row ***************************

Table: user

Create Table: CREATE TABLE `user` (

`uid` int(11) NOT NULL AUTO_INCREMENT,

`data` json DEFAULT NULL,

`user_name` varchar(32) GENERATED ALWAYS AS (json_extract(`data`,’$.name’)) VIRTUAL,

PRIMARY KEY (`uid`),

KEY `idx` (`user_name`)

) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4

1 row in set (0.00 sec)

表(TABLE)
- 表的介绍

表是关系数据库的核心
表 = 关系
表是记录的集合
二维表格模型易于人的理解
MySQL默认存储引擎都是基于行(记录)存储
每行记录都是基于列进行组织的
- 表是数据的集合

select * from table_name limit 1;

集合是无序的，上面的SQL语句的意思是从表(集合)中随机选出一条数据，结果是不确定的, 不能简单的认为是取出第一条数据。

select * from table_name order by col_name limit 1;

只有通过order by排序之后取出的数据，才是确定的。

创建临时表

临时表的作用
临时表主要的作用是给当前登录的用户存储临时数据或者临时结果的。
不要和SQL优化器在排序过程中内部帮你创建的临时表相混淆。
临时表的存储引擎及位置

(root@localhost) 18:51:36 [dbtype]> show variables like “default%tmp%”;

+—————————-+——–+

| Variable_name | Value |

+—————————-+——–+

| default_tmp_storage_engine | InnoDB | #临时表默认存储引擎就是InnoDB

+—————————-+——–+

1 row in set (0.11 sec)

(root@localhost) 18:51:37 [dbtype]> show variables like ‘tmpdir’;

+—————+—————–+

| Variable_name | Value |

+—————+—————–+

| tmpdir | /data/mysqldata |

+—————+—————–+

1 row in set (0.00 sec)

(root@localhost) 18:52:43 [dbtype]> system ls /data/mysqldata/ |grep ‘#’

#sqld6c_3_0.frm # -临时表保存的表结构

(root@localhost) 18:53:08 [dbtype]> show variables like “innodb_temp%”;

+—————————-+———————–+

| Variable_name | Value |

+—————————-+———————–+

| innodb_temp_data_file_path | ibtmp1:12M:autoextend |

+—————————-+———————–+

1 row in set (0.00 sec)

(root@localhost) 18:53:23 [dbtype]> system ls -lh /data/mysqldata/ |grep ‘tmp’

-rw-r—– 1 mysql mysql 12M 2月 15 18:46 ibtmp1 #这个是我们的表结构对应的数据

MySQL5.7.18 把临时表结构放在tmpdir，而数据表数据放在datadir

临时表的创建
临时表是SESSION级别的, 当前用户logout或者其他用户登录上来，是无法看到这张表的
当临时表和普通表同名时，当前用户只能看到同名的临时表
创建表时带上if not exists进行表的存在性检查；同时建议在临时表的表名前面加上统一的prefix

(root@localhost) 18:41:28 [dbtype]> create temporary table t1 (a int);

Query OK, 0 rows affected (0.00 sec)

(root@localhost) 18:41:43 [dbtype]> insert into t1 select 1;

Query OK, 1 row affected (0.00 sec)

Records: 1 Duplicates: 0 Warnings: 0

(root@localhost) 18:41:50 [dbtype]> select * from t1;

+——+

| a |

+——+

| 1 |

+——+

1 row in set (0.00 sec)

(root@localhost) 18:42:32 [dbtype]> show create table t1\G

*************************** 1. row ***************************

Table: t1

Create Table: CREATE TEMPORARY TABLE `t1` (

`a` int(11) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

1 row in set (0.00 sec)

从其他终端登录的用户(session)无法看到临时表t1

临时表和普通表同名问题

(root@localhost) 18:45:46 [dbtype]> create table t_1 (a int); #创建一张普通的表叫做 t_1

(root@localhost) 18:45:49 [dbtype]> insert into t_1 values(3);

(root@localhost) 18:45:57 [dbtype]> select * from t_1;

+——+

| a |

+——+

| 3 | #可以看到插入的数据

+——+

(root@localhost) 18:46:05 [dbtype]> create temporary table t_1 (a int); #创建一种和t_1 同名的临时表

(root@localhost) 18:46:16 [dbtype]> insert into t_1 values(1000); #插入一个不一样的值

(root@localhost) 18:46:32 [dbtype]> select * from t_1; #只能搜索到临时表中的数据

+——+

| a |

+——+

| 1000 |

+——+

(root@localhost) 18:46:41 [dbtype]> create temporary table if not exists table_name (a int); #使用if not exists进行判断

查看表结构

(root@localhost) 18:57:45 [dbtype]> show create table t_1\G #显示表结构（建表语句）

*************************** 1. row ***************************

Table: t_1

Create Table: CREATE TEMPORARY TABLE `t_1` (

`a` int(11) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

1 row in set (0.00 sec)

(root@localhost) 18:57:46 [dbtype]> desc t_1\G #表的描述,描述二维表信息

*************************** 1. row ***************************

Field: a

Type: int(11)

Null: YES

Key:

Default: NULL

Extra:

1 row in set (0.00 sec)

(root@localhost) 18:57:57 [dbtype]> show table status like ‘t_1’\G #看表结构的元数据信息

*************************** 1. row ***************************

Name: t_1

Engine: InnoDB

Version: 10

Row_format: Dynamic

Rows: 1

Avg_row_length: 16384

Data_length: 16384

Max_data_length: 0

Index_length: 0

Data_free: 0

Auto_increment: NULL

Create_time: 2019-02-15 18:45:49

Update_time: 2019-02-15 18:45:57

Check_time: NULL

Collation: utf8mb4_general_ci

Checksum: NULL

Create_options:

Comment:

1 row in set (0.00 sec)

ALTER TABLE

(root@localhost) 22:42:09 [dbtype]> alter table t_1 add column b char(10);

(root@localhost) 22:43:16 [dbtype]> alter table t_1 drop column b;

(root@localhost) 10:19:29 [employees]> alter table newsal add column (update_data date);

Query OK, 0 rows affected (29.05 sec) –2844047行数据做ONLINE DDL花费的时间，并且会锁表，线上操作要谨慎。

注意:当表记录很大的时候，alter table会很耗时，影响性能

ONLINE DDL:5.6以后对在线DDL操作进行了优化，以提高性能。官方文档https://dev.mysql.com/doc/refman/5.7/en/innodb-online-ddl-operations.html

MySQL约束
- MySQL约束分类

MySQL中约束保存在information_schema数据库的table_constraints中，可以通过该表查询约束信息；常用5种约束：

not null:　非空约束，指定某列不为空
unique: 唯一约束，指定某列和几列组合的数据不能重复
primary key: 主键约束，指定某列的数据不能重复、唯一
foreign key: 外键，指定该列记录属于主表中的一条记录，参照另一条数据
check: 检查，指定一个表达式，用于检验指定数据

MySQL不支持check约束，但可以使用check约束，而没有任何效果；

根据约束数据列限制，约束分为：

单列约束：每个约束只约束一列
多列约束：每个约束约束多列数据
- not null约束

非空约束用于确保当前列的值不为空值，非空约束只能出现在表对象的列上。

Null类型特征：所有的类型的值都可以是null，包括int、float 等数据类型

#下面的table 加上了非空约束，也可以用alter来修改或增加非空约束

create table temp(

id int not null,

name varchar(255) not null default ‘abc’,

sex char null

);

alter table temp modify sex varchar(2) not null; #增加非空约束

alter table temp modify sex varchar(2) null; #取消非空约束

alter table temp modify sex varchar(2) default ‘abc’ null; #取消非空约束，增加默认值

unique

唯一约束是指定table的列或列组合不能重复，保证数据的唯一性。
唯一约束不允许出现重复的值，但是可以为多个null。
同一个表可以有多个唯一约束，多个列组合的约束。
在创建唯一约束时，如果不给唯一约束名称，就默认和列名相同。
唯一约束不仅可以在一个表内创建，而且可以同时多表创建组合唯一约束。
MySQL会给唯一约束的列上默认创建一个唯一索引；

CREATE TABLE `temp` (

`id` int(11) NOT NULL,

`name` varchar(25) DEFAULT NULL,

`password` varchar(16) DEFAULT NULL,

UNIQUE KEY `uk_name_pwd` (`name`,`password`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; #使用表级约束语法，表示用户名和密码组合不能重

//添加唯一约束

alter table temp add unique (name, password);

alter table temp modify name varchar(25) unique;

//删除约束

alter table temp drop index name;

primary key

主键约束相当于唯一约束 + 非空约束的组合，主键约束列不允许重复，也不允许出现空值。
每个表最多只允许一个主键，建立主键约束可以在列级别创建，也可以在表级别创建。
当创建主键的约束时，系统默认会在所在的列和列组合上建立对应的唯一索引。
MySQL的主键名总是PRIMARY。

//列模式

create table temp1(

id int primary key,

name varchar(25)

);

CREATE TABLE `temp2` (

`id` int(11) NOT NULL,

`name` varchar(25) DEFAULT NULL,

`pwd` varchar(15) DEFAULT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

//组合模式

CREATE TABLE `temp3` (

`id` int(11) NOT NULL,

`name` varchar(25) NOT NULL,

`pwd` varchar(15) NOT NULL,

PRIMARY KEY (`name`,`pwd`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

alter table temp drop primary key; //alter删除主键约束

alter table temp add primary key(name, pwd); //alter添加主键约束

alter table temp modify id int primary key; //alter 修改列为主键

//设置主键自增

CREATE TABLE `temp4` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`name` varchar(20) DEFAULT NULL,

`pwd` varchar(16) DEFAULT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 //auto_increment自增模式，设置自增后插入数据的时候就不需要给该列插入值了。

foreign key约束

外键约束是保证一个或两个表之间的参照完整性，外键是构建于一个表的两个字段或是两个表的两个字段之间的参照关系

CREATE TABLE parent (

id INT NOT NULL,

PRIMARY KEY (id)

) ENGINE=INNODB;

CREATE TABLE child (

id INT,

parent_id INT,

INDEX par_ind (parent_id),

FOREIGN KEY (parent_id)

REFERENCES parent(id)

ON DELETE CASCADE ON UPDATE CASCADE

) ENGINE=INNODB;

(root@localhost) 23:41:29 [dbtype]> insert into child values(1,1); #直接报错了，因为此时parent表中没有任何记录

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`dbtype`.`child`, CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`) ON DELETE CASCADE ON UPDATE CASCADE)

(root@localhost) 23:42:06 [dbtype]> insert into parent values(1);

(root@localhost) 23:43:20 [dbtype]> insert into child values(1,1); #然后在child中插入记录，且parent_id是在parent中存在的

(root@localhost) 23:44:21 [dbtype]> update parent set id=100 where id=1;

(root@localhost) 23:44:48 [dbtype]> select * from child;

+——+———–+

| id | parent_id |

+——+———–+

| 1 | 100 | #自动变化，这是on update cascade的作用，联级更新，parent更新，child也跟着更新

+——+———–+

1 row in set (0.00 sec)

(root@localhost) 23:45:00 [dbtype]> delete from parent where id=100;

Query OK, 1 row affected (0.01 sec)

(root@localhost) 23:45:27 [dbtype]> select * from child; # id=1，parent_id=100的记录跟着被删除了。on delete cascade的作用

Empty set (0.00 sec)

(root@localhost) 23:45:39 [dbtype]> alter table child drop foreign key child_ibfk_1;

Query OK, 0 rows affected (0.01 sec) #删除之前的外键

Records: 0 Duplicates: 0 Warnings: 0

(root@localhost) 23:45:57 [dbtype]> alter table child add foreign key(parent_id) references parent(id) on update cascade on delete restrict; #使用严格模式

Query OK, 0 rows affected (0.20 sec)

Records: 0 Duplicates: 0 Warnings: 0

(root@localhost) 23:46:42 [dbtype]> insert into parent values(50);

Query OK, 1 row affected (0.01 sec)

(root@localhost) 23:46:57 [dbtype]> insert into child values(3,50);

Query OK, 1 row affected (0.00 sec)

(root@localhost) 23:47:09 [dbtype]> insert into child values(3,51); #和之前一样会提示错误

(root@localhost) 23:48:37 [dbtype]> delete from parent where id=50; #删除失败了，因为是restrict模式

ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`dbtype`.`child`, CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`) ON UPDATE CASCADE)

cascade方式:在父表上update/delete记录时，同步update/delete掉子表的匹配记录
set null方式:在父表上update/delete记录时，将子表上匹配记录的列设为null,要注意子表的外键列不能为not null
No action方式:如果子表中有匹配的记录,则不允许对父表对应候选键进行update/delete操作
Restrict方式:同no action, 都是立即检查外键约束
Set default方式:父表有变更时,子表将外键列设置成一个默认的值但Innodb不能识别

外键约束，可以让数据进行一致性更新，但是会有一定的性能损耗，线上业务使用不多。

通常上述级联更新和删除都是由应用层业务逻辑进行判断并实现。

check约束

MySQL可以使用check约束，但check约束对数据验证没有任何作用。

create table temp(

id int auto_increment,

name varchar(20),

age int,

primary key(id),

check(age > 20)

);

上面check约束要求age必须大于0，但没有任何作用。创建table的时候没有任何错误或警告。

Rank、视图、触发器、MySQL内建函数
- Rank排名

给出不同的用户的分数，然后根据分数计算排名

(root@localhost) 19:41:47 [mytest]> create table t_rank(id int,score int);

(root@localhost) 19:41:59 [mytest]> insert into t_rank values(1, 10), (2, 20), (3, 30), (4, 30), (5, 40), (6, 40);

(root@localhost) 19:42:08 [mytest]> select * from t_rank;

+——+——-+

| id | score |

+——+——-+

| 1 | 10 |

| 2 | 20 |

| 3 | 30 |

| 4 | 30 |

| 5 | 40 |

| 6 | 40 |

+——+——-+

(root@localhost) 19:42:16 [mytest]> SET @prev_value := NUll; #假设比较到第N行，设置一个变量prev_value用于存放第N-1行score的分数，用于比较第N行的score和第N-1行的score，prev_value可以理解为是临时保存第N-1行的score的变量

(root@localhost) 19:43:03 [mytest]> set @rank_count := 0; #用于存放当前的排名

(root@localhost) 19:43:20 [mytest]> select id, score,

-> case

-> when @prev_value = score then @rank_count #相等则prev_value不变，并返回rank_count

-> when @prev_value := score then @rank_count := @rank_count + 1 #不等，则第N行的score赋值

-> end as rank_column # case 开始的，end结尾

-> from t_rank

-> order by score desc;

+——+——-+————-+

| id | score | rank_column |

+——+——-+————-+

| 5 | 40 | 1 |

| 6 | 40 | 1 |

| 3 | 30 | 2 |

| 4 | 30 | 2 |

| 2 | 20 | 3 |

| 1 | 10 | 4 |

+——+——-+————-+

6 rows in set (0.04 sec)

快速执行

select id, score,

case

when @prev_value = score then @rank_count

when @prev_value := score then @rank_count := @rank_count + 1

end as rank_column

from t_rank

order by score desc;

SQL结构化查询语言

什么是SQL?

SQL，英文全称Structured Query Language，中文意思是结构化查询语言，它是一种对关系数据库中的数据进行定义和操作的语言方法，是大多数关系数据库管理系统所支持的工业标准。

结构化查询语言SQL是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统；同时也是数据库脚本文件的扩展名。结构化查询语言是高级的非过程化编程语言，允许用户在高层数据结构上工作。它不要求用户指定对数据的存放方法，也不需要用户了解具体的数据存放方式，所以，具有完全不同于底层结构的，不同数据库系统可以使用相同的结构化查询语言作为数据输入与管理的接口。结构化查询语言语句可以嵌套，这使得它具有极大的灵活性和强大的功能。不同的数据库系统的sQL语言会有一些差别。

SQL的分类

SQL结构化查询语言包含6个部分：

数据查询语言（DQL)

DQL全称Data Query Language，其语句，也称为“数据检索语句”，作用是从表中获取数据，确定数据怎样在应用程序给出。关键字SELECT是DQL（也是所有SQL）用得最多的动词，其他DQL常用的保留字有WHERE,ORDER BY,GROUPBY和HAVING。这些DQL保留字常与其他类型的SQL语句一起使用。

数据操作语言（DML)

DML全称Data Manipulation Language，其语句包括动词INSERT，UPDATE和DELETE。它们分别用于添加，工修改和删除表中的行（数据）。也称为动作查询语言。

事务处理语言（TPL)

它的语句能确保被DML语句影响的表的所有行及时得以更新。TPL语句包括BEGIN TRANSACTION,COMMIT和ROLLBACK。

数据控制语言（DCL:

DCL全称（oataControlLanguage), 它的语句通过GRANT或REVOKE获得许可，确定单个用户和用户组对数据库对象的访问。某些RDBMS可用GRANT或REVOKE控制对表单个列的访问。

数据定义语言（DDL):

全称，(Data Definition Language)，其语句包括动词CREATE和DROP,ALTER。在数据库中创建新表或删除表（CREATE TABLE或DROP TABLE);为表加入索引等。DDL包括许多与人数据库目录中获得数据有关的保留字。它也是动作查询的一部分。

指针控制语言（CCL):

全称CURSOR Control Language，它的语句，像DECLAR、CURSOR,FETCHINTO和UPDATE WHERE CURRENT用于对一个或多个表单独行的操作。

小结：SQL语句最常见的分类一般就是3类：

DDL(Data Definition Language）一数据定义语言（CREATE,ALTER,DROP)，管理基础数据的，例如：库，表运维要熟练
DCL(DataControlLanguage）一数据控制语言（GRANT,REVOKE,COMMIT,ROLLBACK)，用户授权，权限回收，数据提交等回滚奋运维要熟练
DML(Data Manipulation Language）一数据操作语言（SELECT,INSERT,DELETE,UPDATE)，针对数据库里的表，记录开发要熟练，运维要了解
MySQL数据库及权限管理

创建数据库

命令语法：create database＜数据库名＞ #注意库名不能数字开头

show create database＜数据库名＞ #查看创建的语句

help create database; #查看创建数据库帮助

show character set; #查看字符集及校对规则名字的方法

建立一个名为oldboy_ghk的GBK字符集数据库

CREATE DATABASE oldboy_gbk CHARACTER SET gbk COLLATE gbk_chinese_ci;

show CREATE DATABASE oldboy_ghk; #查看创建库的语句

CREATE DATABASE `oldboy_ghk` /*!40100 DEFAULT CHARACTER SET gbk */

建立一个名为oldboy–utf8的UTF8数据库

create database oldboy_utf8 CHARACTER SET utf8 COLLATE utf8_general_ci;

由于编译安装数据库的时候指定字符集为utf8，所以在此可以不指定（生产场景根据开发的程序确定字符集，常用utf8）

字符集的不一致是数据库中文内容乱码的罪魁祸首，有关字符集的问题见后文。

如果编译的时候指定了特定的字符集，则以后创建对应字符集的数据库就不需要指定字符集了。如下：

–default_charset=utf8

–default_collation=utf8_general_ci

–extra_charsets=gbk,gb2312,utf8,ascii #数据库支持创建库的字符集

编译的时候没有指定字符集或者指定了和程序不同的字符集，指定字符集创建数据库即可。

显示数据库：

show databases;

show databases like ‘oldboy%’;

删除数据库

drop database oldboy_utf8;

help drop database #查看删除数据库的帮助

连接数据库

use ＜数据库名＞

查看数据库连接等信息

select database(); #查看当前连接数据库

select user(); #查看当前连接用户

select version; #查看数据库版本

删除mysql系统多余账户

select user.host from mysql.user; #查看已有的账户

drop user ”@’localhost’,”@’nfs.etiantian.org’,’root’@’nfs.etiantian.org’; #删除多个用户

flush privileges;

如果drop删除不了（一般是特殊字符或大写），可以用下面方式删除（以root用户，oldboy主机为例）:

de1ete from mysql.user where user=’root’ and host=’oldboy’;

flush privileges;

创建mysql用户及赋予用户权限

help grant #通过help查看grant命令帮助

语法：grant all privileges on dbname.* to username@’localhost’ identified by ‘passwd’

例子：grant all privileges on *.* to liwen@’localhost’ identified by ‘passworda1!’;

grant all on *.* to test@’10.0.0.0/255.255.255.0′ identified by ‘testl23′;

注意：不能用’10.0.0.0/24’

创建和授权分离

CREATE USER ‘jeffrey’@’localhost’ IDENTIFIED BY ‘mypass’;

GRANT ALL ON db1.* TO ‘jeffrey’@’localhost’;

查看用户权限

show grants for rep@’192.168.10.%’;

select * from mysql.db where user=’web_w’ and host=’192.168.10.%’\G;

回收权限

help revoke

REVOKE INSERT ON *.* FROM ‘jeffrey’@’localhost’;

企业生产环境如何授权用户权限

博客，CMS等产品的数据库授权

对于web连接用户授权尽量采用最小化原则，很多开源软件都是web界面安装，因此，在安装期间除了select,insert,update,delete4个权限外，还需要create,drop等比较危险的权限。生成数据库表后，要收回create、drop授权

MySQL数据库应用管理实战

建表语句及表的基础知识

语法：

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name

[(create_definition,…)]

[table_options] [select_statement]

查看表结构：desc tbl_name;或者show columns from tbl_name;

查看建表语句：show create tbl_name;

创建索引

索引就象书的目录一样，如果在字段上建立了索引，那么以索引列为查询条件时可以加快查询数据的速度，这是mysql优化的重要内容之一，后面的课程会详细讲到。查询数据库，按主键查询是最快的，每个表只能有一个主键列，但是可以有多个普通索引列。主键列要求列的所有内容必须唯一，而普通索引列不要求内容必须唯一。

索引列的创建及生效条件

问题1、既然索引可以加快查询速度，那么就给所有的列建索引吧？

解答：因为索引不但占用系统空间，而且更新数据库时还需要维护索引数据的，因此，索引是一把双刃剑，并不是越多越好，例如：数十到几百行的小表上无需建立索引，更新频繁，读取比较少的表要少建立索引。

问题2、需要在哪些列上创建索引呢？

select user,host from mysql.user where passwoord＝…，索引一定要创建在

where后的条件列上，而不是select后的选择数据的列上。另外，我们要尽量

选择在唯一值多的大表上的列建立索引，例如，男女性别列唯一值，不适合建

立索引。

创建索引命令集合

创建主键索引：

alter table student change id id int primary key auto increment;

删除主键索引：

alter tabte student drop primary key;

创建昔通索引：

aIter table student add index index_dept(dept(8));

根据列的前n个字符创建索引

create index index_dept on student(dept(8));

根据多个列创建联合索引

create index ind_name_depton student(name,dept);

根据多个列的前n个字符创建联合索引

create index ind_name_dept on student(name(8),dept(10));

创建唯一索引

create unique index uni_ind_name on student(name);

删除普通索引与唯一索引

alter table student drop index index_dept;

drop 1ndex index_dept on student;

创建索引的基本知识小结：

索引类似书籍的目录，会加快查询数据的速度。
要在表的列（字段）上创建索引。
索引会加快查询速度，但是也会会影响更新的速度，因为更新要维护索引数据。
索引列并不是越多越好，要在频繁查询的where后的条件列上创建索引。
小表或重复值很多的列上可以不建索引，要在大表以及重复值少的条件列上创建索引。
多个列联合索引有前缀生效特性。
当字段内容前N个字符已经接近唯一时，可以对字段的前N个字符创建索引
索引从工作方式区分，有主键，唯一，普通索引。
索引类型会有BTRREE（默认）和hash（适合做缓存（内存数据库）等。
提示：order by后面的列也要建立索引。

http://blog.csdn.net/dyllove98/article/details/9631303

SQL查寻语句

语法：Select ＜字段1，字段2,> from ＜表名＞ where ＜表达式＞;

将查出来的内容输出到文件：select id,name from test where id>1 and id<5 order by id desc INTO OUTFILE ‘/tmp/a.txt’;

单表查询

select * from `TStudent`; #查询所有字段

select Sname,sex,email from TStudent; #查询指定字段

select Sname,sex,email,Class from TStudent where class=’java’ #查询指定记录

select * from TStudent where left(sname,1) in (‘王’,’刘’,’石’); #带IN关键字的查询

select * from TStudent where convert(studentid,signed) between 100 and 150 ;等价于

select * from TStudent where convert(studentid,signed)>=100 and convert(studentid,signed)<=150; 等价于

select * from TStudent where studentid between 100 and 150; #自动转换类型

select * from s where email is null #查询空值

select * from TStudent where sex=’男’ and Class=’net’ and studentid>20 and studentid<50;# 带AND的多条件查询

select * from TStudent where sname like ‘%志%’ or class=’net’; #带OR的多条件查询

select distinct class from TStudent; #查询一共有几个班, 查询结果不重复

带LIKE的字符匹配查询

百分号通配符‘%’，匹配任意长度的字符，甚至包括零字符

下划线通配符‘_’，一次只能匹配任意一个字符

select * from TStudent where sname like ‘%志%’; #查找姓名中有“志”这个字的学生

select * from s where email is null

用LIMIT限制查询结果的数量

select * from TStudent limit 10; #返回前10个学生, 随机取出

select * from TStudent limit 10,10; #limit start, offset;返回第11-20个学生,从11开始取10个

以上这个语法有一种分页的效果，但是会随着start的增加，性能会下降，因为会扫描表(从 1 到 start)

select * from TStudent where student_id >100 limit 10; #相对比较推荐的方法

合并查询结果union

利用UNION关键字，可以给出多条SELECT语句，并将它们的结果组合成单个结果集。合并时，两个表对应的列数和数据类型必须相同。各个SELECT语句之间使用UNION或UNION ALL关键字分隔

select studentid,sname from TStudent where studentid<=10 union select studentid, sname from TStudent where sname like ‘王%’; # union会去重

select * from t_union02 union all select * from t_union01; #使用 union all 显示不去重

如果知道数据本身具有唯一性，没有重复，则建议使用union all，因为union会做去重操作，性能会比union all要低

为表和字段取别名

为字段取别名:列名 [AS] 列别名

select studentid as 学号,sname as 姓名,sex as 性别 from TStudent;

select studentid 学号,sname 姓名,sex 性别 from TStudent;

为表取别名: 表名 [AS] 表别名

select a.studentid 学号,a.sname 姓名,a.sex 性别 from TStudent as a;

select a.studentid 学号,a.sname 姓名,a.sex 性别 from TStudent a;

多表join连接查询

内连接查询

内连接(INNER JOIN)使用比较运算符进行表间某(些)列数据的比较操作，并列出这些表中与连接条件相匹配的数据行，组合成新的记录，也就是说，在内连接查询中，只有满足条件的记录才能出现在结果关系中。(同时拥有的记录)

示例：

select a.StudentID,a.Sname,c.subJectName,b.mark from TStudent a join TScore b on a.StudentID=b.StudentID join TSubject c on b.subJectID=c.subJectID

如果某列只在一张表中，就可以不用指明是哪个表中的列。

select a.StudentID,a.Sname,subJectName,mark from TStudent a join TScore b on a.StudentID=b.StudentID join TSubject c on b.subJectID=c.subJectID

外连接查询

返回到查询结果集合中的不仅包含符合连接条件的行，而且还包括左表(左外连接或左连接)、右表(右外连接或右连接)或两个边接表(全外连接)中的所有数据行。

使用内连接查询，可以看到该学号的成绩没有出现在查询结果

select a.studentid,a.sname,b.studentid from TStudent a join TScore b on a.StudentID=b.StudentID

使用右连接查询，可以看到出现在该学号的成绩出现在查询结果

select a.studentid,a.sname,b.studentid from TStudent a right join TScore b on a.StudentID=b.StudentID

自连接查询

使用自连接查找姓名相同的学生。

select a.studentid,a.sname,b.studentid,b.sname from TStudent a join TStudent b

on a.Sname=b.Sname where a.studentid<>b.StudentID

在 inner join中，过滤条件放在where或者on中都是可以的

在 outer join中条件放在where和on中是不一样的

对查询结果排序

MySQL中可以通过在SELECT使用ORDER BY子句对查询的结果进行排序。

单列排序orderby

ASC 代表结果会以由小往大的顺序列出，而 DESC 代表结果会以由大往小的顺序列出。如果两者皆没有被写出的话，默认就会用 ASC。

select * from `TStudent` order by birthday asc

select * from `TStudent` order by birthday desc

多列排序orderby

select a.StudentID,a.Sname,subJectName,mark from TStudent a join TScore b on a.StudentID=b.StudentID join TSubject c on b.subJectID=c.subJectID where c.`subJectID`=’0001′ order by mark desc,a.studentID desc

分组查询

分组查询是对数据按照某个或多个字段进行分组。

使用集合函数查询

COUNT()函数

按班分组统计每个班的总人数

select class,COUNT(*) from `TStudent` group by class

SUM()函数

查询每个学生总分

select concat(a.StudentID,’ ‘,a.sname) ss,SUM(b.mark) from `TStudent` a join TScore b on a.`StudentID`=b.`StudentID` group by ss

AVG()函数

统计每个班平均分

select class,AVG(mark) from `TStudent` a join TScore b on a.`StudentID`=b.`StudentID` group by class

MAX()函数
MIN()函数
- 多字段分组
统计每班每科平均分，需要按两列分组class和subJectName

select class,subJectName,AVG(mark) from TStudent a join TScore b on a.StudentID=b.StudentID join TSubject c on b.subJectID=c.subJectID group by class,subJectName

使用HAVING过滤分组

查询平均分大于80的学生

select concat(a.StudentID,’ ‘,a.sname) ss,avg(b.mark) m from `TStudent` a join TScore b on a.`StudentID`=b.`StudentID` group by ss having m>80;

having对分组的聚合函数做过滤

按时间分组

按周分组修复bug统计

select DATE_FORMAT(event_ptime, ‘%u’),count(*) from events where event_ptime < “2017-10-00 00:36:02” and event_email_title regexp ‘bug|修复|问题’ group by DATE_FORMAT(event_ptime, ‘%u’);

按天分组

select DATE_FORMAT(event_ptime, ‘%c.%e’),count(*) from events where event_ptime < “2017-10-00 00:36:02” group by DATE_FORMAT(event_ptime, ‘%c.%e’) order by event_ptime into outfile ‘/tmp/everyday3.xls’;

GROUP BY和ORDER BY一起使用

查找平均分大于80分，按平均分排序

select concat(a.StudentID,’ ‘,a.sname) ss,avg(b.mark) m from `TStudent` a join TScore b on a.`StudentID`=b.`StudentID` group by ss having m>80 order by m;

查找客户每周（以年，月，周显示）产生的订单量

SELECT o_custkey AS 客户, count(o_orderkey) AS 订单总数, YEAR (o_orderDATE) AS 年, MONTH (o_orderDATE) AS 月, WEEK(o_orderDATE) AS 周 FROM orders GROUP BYo_custkey, YEAR (o_orderDATE) , MONTH (o_orderDATE), WEEK(o_orderDATE) LIMIT 30;

在GROUP BY子句中使用WITH ROLLUP

使用 GROUP BY 的 WITH ROLLUP 字句可以检索出更多的分组聚合信息，它不仅仅能像一般的 GROUP BY 语句那样检索出各组的聚合信息，还能检索出本组类的整体聚合信息

以下查询语句，能够统计每班每科的平均成绩，每班的平均成绩也能统计，全部班级的全部课程平均成绩也能统计。

select class,subJectName,AVG(mark) from TStudent a join TScore b on a.StudentID=b.StudentID join TSubject c on b.subJectID=c.subJectID group by class,subJectName with rollup;

子查询

子查询就是指在一个select语句中嵌套另一个select语句。同时，子查询必须包含括号。MySQL 5.6.x 版本之前，MySQL的子查询性能较差，但是从5.6开始，不存在性能差的问题。

子查询的分类

独立子查询

不依赖外部查询而运行的子查询

select a from t1 where a in (1,2,3,4,5);

相关子查询

引用了外部查询列的子查询

select a from t1 where a in (select * from t2 where t1.a = t2.a);

带IN关键字的子查询

IN关键字进行子查询时，内层查询语句仅仅返回一个数据列，这个数据列里的值将提供给外层查询语句进行比较操作。

in是ANY的一种特殊情况：”in” equals “= any”

select * from TStudent where studentid in (select distinct studentid from TScore where mark>98)

带EXISTS关键字的子查询

EXISTS关键字后面的参数是一个任意的子查询，系统对子查询进行运算以判断它是否返回行，如果至少返回一行，那么EXISTS的结果为true，此时外层查询语句将进行查询；如果子查询没有返回任何行，那么EXISTS返回的结果是false，此时外层语句将不进行查询。

select * from TStudent where studentid=’01001′ and exists (select * from TScore where studentid=’01001′);

EXISTS不管返回值是什么，而是看是否有行返回，所以EXISTS中子查询都是select *、select 1等，因为只关心返回是否有行（结果集）

带ANY、SOME关键字的子查询

ANY和SOME关键字是同义词，表示满足其中任一条件，它们允许创建一个表达式对子查询的返回值列表进行比较，只要满足内层子查询中的任何一个比较条件，就返回一个结果作为外层查询的条件。

select * from TStudent where studentid=any (select distinct studentid from TScore where mark>98); 等价于

select * from TStudent where studentid=some (select distinct studentid from TScore where mark>98); 等价于

select * from TStudent where studentid in (select distinct studentid from TScore where mark>98)

以下SQL语句子查询查出考试成绩大于98的学生的studentid，比如查出的结果有三个‘00010’，‘00021’，‘00061’，外查询将会查询比00010学号大的学生。

select * from TStudent where studentid>some (select distinct studentid from TScore where mark>98)

以下SQL语句子查询查出考试成绩大于98的学生的studentid，比如查出的结果有三个‘00010’，‘00021’，‘00061’，外查询将会查询比00061学号小的学生。

select * from TStudent where studentid<some (select distinct studentid from TScore where mark>98)

ANY关键词必须与一个比较操作符一起使用： =, >, <, >=, <=, <> (这个是!=的意思)

带ALL关键字的子查询

ALL关键字与ANY和SOME不同，使用ALL时需要同时满足所有内层查询的条件

以下SQL语句子查询查出考试成绩大于98的学生的studentid，比如查出的结果有三个‘00010’，‘00021’，‘00061’，外查询将会查询比00010学号小的学生。

select * from TStudent where studentid<all (select distinct studentid from TScore where mark>98)

子查询的优化

MySQL5.6之前

在MySQL5.6之前，优化器会把子查询重写成exists的形式

select a from t1 where a in (select a from t2); — 这个是一条独立的子查询，时间复杂度 O(M+N)

经过优化器重写后

select a from t1 where exists (select 1 from t2 where t1.a = t2.a); — 这是相关子查询，复杂度O(M*N + M)

MySQL 5.6之后

在MySQL 5.6之后，优化器不会将子查询重写成exists的形式，而是自动优化，性能有了大幅提升

包含NULL值的NOT IN

数据库的比较操作，除了返回1(True), 0(False)之外，还会返回NULL

NULL和NULL的比较，返回的还是NULL

(root@localhost) 20:47:55 [(none)]> select null not in (‘a’, ‘b’, null);

+——————————+

| null not in (‘a’, ‘b’, null) |

+——————————+

| NULL |

+——————————+

1 row in set (0.00 sec)

(root@localhost) 20:47:59 [(none)]> select null in (‘a’, ‘b’, null);

+————————–+

| null in (‘a’, ‘b’, null) |

+————————–+

| NULL |

+————————–+

1 row in set (0.00 sec)

对于包含了NULL值的IN操作，总是返回True或者NULL

(root@localhost) 00:07:55 [(none)]> select ‘a’ in (‘a’, ‘b’, null);

+————————-+

| ‘a’ in (‘a’, ‘b’, null) |

+————————-+

| 1 |

+————————-+

1 row in set (0.00 sec)

(root@localhost) 00:08:05 [(none)]> select ‘c’ in (‘a’, ‘b’, null);

+————————-+

| ‘c’ in (‘a’, ‘b’, null) |

+————————-+

| NULL |

+————————-+

1 row in set (0.00 sec)

NOT IN返回0(False)或者NOT NULL (NULL)

(root@localhost) 00:09:11 [(none)]> select ‘a’ not in (‘a’, ‘b’, null);

+—————————–+

| ‘a’ not in (‘a’, ‘b’, null) |

+—————————–+

| 0 |

+—————————–+

1 row in set (0.00 sec)

(root@localhost) 00:09:22 [(none)]> select ‘c’ not in (‘a’, ‘b’, null);

+—————————–+

| ‘c’ not in (‘a’, ‘b’, null) |

+—————————–+

| NULL |

+—————————–+

1 row in set (0.00 sec)

和 null比较，使用is和is not，而不是 = 和 <>

(root@localhost) 20:54:06 [(none)]> select null = null;

+————-+

| null = null |

+————-+

| NULL |

+————-+

1 row in set (0.00 sec)

(root@localhost) 20:54:22 [(none)]> select null <> null;

+————–+

| null <> null |

+————–+

| NULL |

+————–+

1 row in set (0.00 sec)

(root@localhost) 20:54:36 [(none)]> select null is null;

+————–+

| null is null |

+————–+

| 1 |

+————–+

1 row in set (0.00 sec)

(root@localhost) 20:54:52 [(none)]> select null is not null;

+——————+

| null is not null |

+——————+

| 0 |

+——————+

1 row in set (0.00 sec)

显示行号(RowNumber)

方法一

set @rn:=0; #产生 SESSION(会话)级别的变量

(gcdb@localhost) 11:42:21 [employees]> select @rn:=@rn+1 as rownumber, emp_no, gender from employees limit 4; #@rn:=1 是赋值的意思

+———–+——–+——–+

| rownumber | emp_no | gender |

+———–+——–+——–+

| 1 | 10001 | M |

| 2 | 10002 | F |

| 3 | 10003 | M |

| 4 | 10004 | M |

方法二（推荐）

(gcdb@localhost) 11:42:31 [employees]> select @rn1:=@rn1+1 as rownumber, emp_no, gender from employees, (select @rn1:=0) as a limit 4;

+———–+——–+——–+

| rownumber | emp_no | gender |

+———–+——–+——–+

| 1 | 10001 | M |

| 2 | 10002 | F |

| 3 | 10003 | M |

| 4 | 10004 | M |

“:=” 和 “=”

(root@localhost) 20:54:45 [(none)]> set @a:=100; #赋值为100

Query OK, 0 rows affected (0.00 sec)

(root@localhost) 20:54:56 [(none)]> select @a; #查看

+——+

| @a |

+——+

| 100 |

+——+

1 row in set (0.00 sec)

(root@localhost) 10:11:21 [(none)]> set @a=200; #等价于set @a:=200

Query OK, 0 rows affected (0.00 sec)

(root@localhost) 10:11:22 [(none)]> select @a;

+——+

| @a |

+——+

| 200 |

+——+

1 row in set (0.00 sec)

(root@localhost) 22:45:37 [(none)]> select @a=99; #进行比较

+——-+

| @a=99 |

+——-+

| 0 |

+——-+

1 row in set (0.00 sec)

使用正则表达式查询

正则表达式作用是匹配文本，将一个模式（正则表达式）与一个文本串进行比较。MySQL用WHERE子句对正则表达式提供了初步的支持，允许你指定用正则表达式过滤SELECT检索出的数据。

在SQL查询语句中，查询条件REGEXP后所跟的东西作为正则表达式处理。

模式	什么模式匹配
^	字符串的开始
$	字符串的结尾
.	任何单个字符
[…]	在方括号内的任何字符列表
[^…]	非列在方括号内的任何字符
p1\|p2\|p3	交替匹配任何模式P1，P2或P3
*	零个或多个前面的元素
+	前面的元素的一个或多个实例
{n}	前面的元素的n个实例
{m,n}	m到n个实例前面的元素

字符‘^’匹配以特定字符或者字符串开头的文本。

select * from TStudent where sname regexp ‘^刘平’

字符‘$’匹配以特定字符或者字符串结尾的文本。

select * from TStudent where cardid regexp ’36$’

字符‘.’匹配任意一个字符。

select * from TStudent where sname regexp ‘.康.’

找出身份证以19开始，以6结束的学生

select * from TStudent where cardid regexp ‘^19.*6$’

找出身份证号中有123的学生

select * from TStudent where cardid regexp ‘.*123+.*’

如要匹配多个字符串，多个字符串之间使用分隔符‘|’隔开。

select * from TStudent where sname regexp ‘武|尹|罗’

只匹配其中任何一个字符，即为所查找的文本。不支持汉字。

select * from TStudent where email regexp ‘[w-z]’;

select * from TStudent where cardid regexp ‘^[1-3,7]’;

“[^字符集合]”匹配不在指定集合中的任何字符。

select * from TStudent where cardid regexp ‘^[^1-7]’;

查找身份证中出现138并且后面有8位0-9的数字的学生。

select * from TStudent where cardid regexp ‘138[0-9]{8}’

使用explain查看select语句的执行计划

例子：

mysql> explain select user,host from mysql.user\G

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: user

type: index

possible_keys: NULL

key: PRIMARY

key_len: 228

ref: NULL

rows: 5

Extra: Using index

1 row in set (0.00 sec)

使用explain优化SQL语句的基本流程

抓慢查询

a.show ful1 prcesslist;

b．分析慢查询日志。

long_query_time=l #记录下查询时间查过1秒

log-slow-queries=/data/3306/slow.log #日志目录

log_queries_not_using_indexes # #表示记录下没有使用索引的查询

explain语句检查索引执行情况。
对需要建索引的列建立索引
- 分析mysql慢查询日志的好工具–mysqlsla

mysqlsla不仅仅可用来处理慢查询日志，也可以用来分析其它日志比如二进制日志，普通查询日志等等，其对sql语句的抽象功能非常实用，参数设定简练易用，很好上手。

mysqlsla是perl编写的脚本，运行mysqlsla需要perl-DBI和per-DBD-Mysql两模块的支持，因此在运行mysqlsla前需要首先安装DBI模块和相应的数据库DBD驱动，而默认情况下linux不安装这两个模块，需要自行下载安装。

切割mysql慢查询

cd /data/3306/

/bin/mv slow.log /backup/$(date +%F)_slow.log

mysqladmin -uroot -p123456 -S /data/3306/mysql.sock flush-logs

mysqlsla /backup/$(date +%F)_slow.log >/backup/new_(date +%F)_slow.log

mail -s “$(date +%F)_slow.log” 1935845114@qq.com </backup/new_(date +%F)_slow.log

将上面语句编写成脚本，然后做定时任务

mysql优化使用profile功能

mysql> SELECT @@profiling; #查看是否启动profile（0：未启动，1：开启）

+————-+

| @@profiling |

+————-+

| 0 |

+————-+

1 row in set (0.00 sec)

mysql> SET profiling = 1; #启动profile

Query OK, 0 rows affected (0.00 sec)

mysql> DROP TABLE IF EXISTS t1; #删除表

Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE TABLE T1 (id INT); #创建表

Query OK, 0 rows affected (0.01 sec)

mysql> SHOW PROFILES; #查看上面几个命令的执行时间

+———-+———-+————————–+

| Query_ID | Duration | Query |

+———-+———-+————————–+

| 0 | 0.000088 | SET PROFILING = 1 |

| 1 | 0.000136 | DROP TABLE IF EXISTS t1 |

| 2 | 0.011947 | CREATE TABLE t1 (id INT) |

+———-+———-+————————–+

3 rows in set (0.00 sec)

mysql> SHOW PROFILE; #查看上一条语句执行的过程

+———————-+———-+

| Status | Duration |

+———————-+———-+

| checking permissions | 0.000040 |

| creating table | 0.000056 |

| After create | 0.011363 |

| query end | 0.000375 |

| freeing items | 0.000089 |

| logging slow query | 0.000019 |

| cleaning up | 0.000005 |

+———————-+———-+

7 rows in set (0.00 sec)

mysql> SHOW PROFILE FOR QUERY 1; #查看Query_ID号为1的语句详细执行过程

+——————–+———-+

| Status | Duration |

+——————–+———-+

| query end | 0.000107 |

| freeing items | 0.000008 |

| logging slow query | 0.000015 |

| cleaning up | 0.000006 |

+——————–+———-+

4 rows in set (0.00 sec)

mysql> SHOW PROFILE CPU FOR QUERY 2; #查看CPU对Query_ID号为1的语句详细执行过程

+———————-+———-+———-+————+

+———————-+———-+———-+————+

| checking permissions | 0.000040 | 0.000038 | 0.000002 |

| creating table | 0.000056 | 0.000028 | 0.000028 |

| After create | 0.011363 | 0.000217 | 0.001571 |

| query end | 0.000375 | 0.000013 | 0.000028 |

| freeing items | 0.000089 | 0.000010 | 0.000014 |

| logging slow query | 0.000019 | 0.000009 | 0.000010 |

| cleaning up | 0.000005 | 0.000003 | 0.000002 |

+———————-+———-+———-+————+

插入更新删除记录

插入数据

为表的所有字段插入数据

语法：INSERT INTO table_name (column_list) VALUES (value_list);

例子：insert into `TSubject` (subjectID,subjectName,BookName,Publisher) values (‘0004′,’英语’,’大学英语走遍美国’,’清华出版社’); 等价于

insert into `TSubject` values (‘0005′,’高等数学’,’高等数学教材’,’清华出版社’);

为表的指定字段插入数据

insert into TSubject (subjectID,subjectName) values (‘0006′,’高等数学2’);

同时插入多条记录

insert into TSubject (subjectName,subjectID) values (‘C#开发’,’0008′),(‘苹果开发’,’0009′);

将查询结果插入到表中

insert into ST (subectid,subjectName) select subjectid,subjectName from TSubject where `Publisher` is not null;

补充强调：我们平时一登录网站发帖子，发博文，实质上都是调用Web网站的程序连接MySQL数据库，通过上述的insert语句把帖子博文数据存入数据库的。

更新数据（注意加条件）
- 根据本表的条件更改记录

update `TStudent` set sname=concat(sname,’net’) where class=’net’;

update `TStudent` set sname=left(sname,3) where class=’net’;

根据另一张表的条件更改

update TStudent a join TScore b on a.`StudentID`=b.`StudentID` set a.`Sname`=concat(sname,’*’) where b.mark<60;

update TStudent a join TScore b on a.`StudentID`=b.`StudentID` set a.`Sname`=concat(sname,’+’), b.mark=b.`mark`+5 where b.mark<60;

update TStudent set Sname=concat(sname,’#’) where studentid in (select studentid from TScore where mark>98); #子查询

replace语句

replace的原理是：先delete，在insert ；注意：需要delete和insert权限

(gcdb@localhost) 11:24:23 [mytest]> replace into t4 values(5,15); #没有替换对象时，类似插入效果

Query OK, 1 row affected (0.03 sec) #只影响1行

关联更新

create table t5 (a int, b int);

insert into t5 values(1,1);

update t5 set a= a+1,b=a where a=1;

select * from t5;

+——+——+

| a | b |

+——+——+

| 2 | 2 | — SQL Server和Oracle中得到的值是2, 1

+——+——+

1 row in set (0.00 se

删除数据
- 根据本表的条件删除记录

delete from TStudent where studentid<‘00010;

根据另一张表的条件删除

delete a from TStudent a join TScore b on a.`StudentID`=b.`StudentID` where b.mark<60;

delete from TStudent where studentid in (select studentid from TScore where mark>90);

truncate和delete区别

truncate table test;速度更快。直接清空对应数据的物理文件。

delete from test;速度慢，逻辑清除，按行删。

通过update伪删除数据

在开发人员开发程序时，页面显示，一般是通过状态来判断的

网页正常显示的数据：select * from test where state=l;

删除oldgirl的记录：update test set state=0 where name=’oldgirl’;

防止人为误操作MySQL数据库技巧一例
- mysql帮助说明

[oldboy_c64 ~]# mysql –help|grep dummy

-U, –i-am-a-dummy Synonym for option –safe-updates, -U.

i-am-a-dummy FALSE

在mysql命令加上选项-U后，当发出没有WHERE或LIMIT关键字的UPDATE或DELETE时，mysql程序就会拒绝执行

做成别名防止老大和DBA误操作

echo “alias mysql=’mysql -U'” >>/etc/profile

tail -1 /etc/profile

. /etc/profile

增删改表的字段等

mysql> help alter table;

Name: ‘ALTER TABLE’

Description:

Syntax:

ALTER [ONLINE | OFFLINE] [IGNORE] TABLE tbl_name

[alter_specification [, alter_specification] …]

[partition_options]

alter_specification:

table_options

| ADD [COLUMN] col_name column_definition

[FIRST | AFTER col_name ]

| ADD [COLUMN] (col_name column_definition,…)

| ADD {INDEX|KEY} [index_name]

[index_type] (index_col_name,…) [index_option] …

| ADD [CONSTRAINT [symbol]] PRIMARY KEY

[index_type] (index_col_name,…) [index_option] …

| ADD [CONSTRAINT [symbol]]

UNIQUE [INDEX|KEY] [index_name]

[index_type] (index_col_name,…) [index_option] …

| ADD FULLTEXT [INDEX|KEY] [index_name]

(index_col_name,…) [index_option] …

| ADD SPATIAL [INDEX|KEY] [index_name]

(index_col_name,…) [index_option] …

| ADD [CONSTRAINT [symbol]]

FOREIGN KEY [index_name] (index_col_name,…)

reference_definition

| ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}

| CHANGE [COLUMN] old_col_name new_col_name column_definition

[FIRST|AFTER col_name]

| MODIFY [COLUMN] col_name column_definition

[FIRST | AFTER col_name]

| DROP [COLUMN] col_name

| DROP PRIMARY KEY

| DROP {INDEX|KEY} index_name

| DROP FOREIGN KEY fk_symbol

| DISABLE KEYS

| ENABLE KEYS

| RENAME [TO|AS] new_tbl_name

| ORDER BY col_name [, col_name] …

| CONVERT TO CHARACTER SET charset_name [COLLATE collation_name]

| [DEFAULT] CHARACTER SET [=] charset_name [COLLATE [=] collation_name]

| DISCARD TABLESPACE

| IMPORT TABLESPACE

| FORCE

| ADD PARTITION (partition_definition)

| DROP PARTITION partition_names

| TRUNCATE PARTITION {partition_names | ALL}

| COALESCE PARTITION number

| REORGANIZE PARTITION [partition_names INTO (partition_definitions)]

| ANALYZE PARTITION {partition_names | ALL}

| CHECK PARTITION {partition_names | ALL}

| OPTIMIZE PARTITION {partition_names | ALL}

| REBUILD PARTITION {partition_names | ALL}

| REPAIR PARTITION {partition_names | ALL}

| PARTITION BY partitioning_expression

| REMOVE PARTITIONING

index_col_name:

col_name [(length)] [ASC | DESC]

index_type:

USING {BTREE | HASH}

index_option:

KEY_BLOCK_SIZE [=] value

| index_type

| WITH PARSER parser_name

| COMMENT ‘string’

table_options:

table_option [[,] table_option] … (see CREATE TABLE options)

partition_options:

(see CREATE TABLE options)

例子：

添加与删除字段

alter table score add object char(20) after sname;

alter table score drop object;

修改字段类型

alter table test modify age char(4) after name;

修改字段名称

alter table test change age oldboy age char(4) after name;

更改表名

rename tab1e test to oldboy;

alter table oldboy rename to test;

修改数据库字符集

alter database oldboy_gbk character set utf8 collate utf8_general_ci;

删除表

drop table student;

mysql数据库字符集知识

mysql字符集介绍

简单地说，字符集就是一套文字符号及其编码、比较规则的集合。MySQL数据库字符集包括字符集（CHARACTER）和校对规则个概念。其中，字符集是用来定义MySQL数据字符串的存储方式。而校对规则则是定义比较字符串的方式。

预备知识

字符：Character，如a、b、c、d等
字符集合：一组字符。如ASCII字符集合、扩展ASCII字符集合、拉丁语系、GB2312、BIG5、Unicode字符集合。
字符编码：给字符集合中的字符指定一个数字来标识。
字符集：字符集合+编码=字符集，字符集合可以有多种编码方式，也就有多个字符集
字符序：Collation 定义字符集中字符的排序规则。一个字符集可以有多个排序规则，也就有多个字符序，常见有下面三个：

以_ci 大小写不敏感

以_cs 大小写敏感

以_bin 按编码值比较

常见的字符集

ASCII字符集
扩展ASCII字符集，同latin1 字符集：8位二进制，包括ASCII字符集中的全部字符
GB2312 、BIG5 、GBK字符集：16位二进制
Unicode字符集：全球语言：16位二进制
- Unicode字符集：两种编码

Unicode编码：一个字符两个字节

UTF8：一个英文字符一个字节；一个中文字符使用3个字节。

UTF-8是一种变长字节编码方式。对于某一个字符的UTF-8编码，如果只有一个字节则其最高二进制位为0；如果是多字节，其第一个字节从最高位开始，连续的二进制位值为1的个数决定了其编码的位数，其余各字节均以10开头。UTF-8最多可用到6个字节。

例如：

韩 Unicode 0100 1001 0100 0100

韩 UTF8编码 1110 0100 1010 0101 1000 0100

mysql支持的字符集

show character set; #创建数据库，如果不指定数据库的字符集，会使用服务器字符集

show variables like “character_set%”; #查看当前使用的字符集

| character_set_client | utf8 | #mysql客户端字符集

| character_set_connection | utf8 | #连接字符集

| character_set_database | utf8 | #数据库字符集，配置文件指定或建库建表指定。

| character_set_results | utf8 | #返回结果字符集；

| character_set_server | utf8 | #服务器字符集，配置文件指定或建库建表指定

| character_set_system | utf8 | #系统字符集

更改mysql字符集
- 临时更改mysql客户端字符集

mysql -uroot -p123456 -S /data/3306/mysql.sock –default-character-set=gbk相当于

set names gbk; #修改客户端字符集,相当于下面命令（临时生效）

mysql -uroot -p123456 -S /data/3306/mysql.sock –default-character-set=gbk相当于下面三个

SET character_set_c1ient=gbk;

SET character_set_results=gbk;

SET character_set_connection= gbk;

单实例永久更改mysql客户端默认字符集

在/etc/my.cnf配置文件中[client]模块中添加，多实例配置文件中不生效

default-character-set=utf8

修改mysql服务端字符集

在/data/3306/my.cnf配置文件中[mysqld]模块中添加

default-character-set=utf8 #适合5.1及以前版本

character-set-server=utf8 #适合5.5

指定数据库和表以及列的字符集

创建数据库，指定数据库的默认字符集

create database db default CHARSET latin1

创建表，指定表的默认字符集

create table student (sdi INT,sname char(10)) default charset utf8;

指定列的字符集

create table student2(sdi INT,sname char(10),address char(20) character set latin1) default charset utf8;

更改数据库和表和列的字符集

alter database db character set ‘latin1’ #更改数据库默认字符集

alter table student character set ‘latin1’ #更改表的默认字符集

alter table student2 change address address char(20) character set ‘latin1’ #更改列字符集，不会更改列中存储的数据，现有数据还是以以前的字符集编码存储，但你的数据库会使用新的字符集解码，将会产生乱码。以后在列中插入的数据将会使用新的字符集编码存储。

指定客户端使用的字符集

set names latin1

当客户端连接服务器时会告诉服务器客户端的字符集，服务器向客户端传输数据时会将服务器端的数据编码为客户端字符集才传输

字符集兼容性

UTF8兼容Latin1

GBK兼容UTF8：支持中英文传输

更改数据库字段字符集由latin1可以正常转换成UTF8 由UTF8转换成Latin1，一个汉字将会变为一个？。

MySQL如何选择合适的字符集？

如果处理各种各样的文字，发布到不同语言国家地区，应选Unlcode字符集，对mysql来说就是UTF-8（每个汉字三字节），如果应用需处理英文，仅有少量汉字UTF-8更好。
如只需支持中文，并且数据量很大，性能要求也很高，可选GBK（定长每个汉字占双字节，英文也占双字节），如果需大量运算，比较排序等，定长字符集，更快，性能高
处理移动互联网业务，可能需要使用utf8mb4字符集。
1. 如何更改生产中MySQL数据库库表的字符集
mysqldump数据备份
在备份的数据中把字符集改了，例如：sed，

mysqldump -uroot -p123456 -B student -S /data/3306/mysql.sock|gzip >/opt/bak_$(date +%F).sql.gz

gzip -d bak_2016-04-15.sql.gz

sed -i ‘s#utf8#gbk#g’ bak_2016-04-15.sql

mysql -uroot -p123456 -S /data/3306/mysql.sock <bak_2016-04-15.sql

注意：修改前要修改字符集，防止乱码。

然后修改系统配置调整字符集生效
通过mysql将数据还原
开发人员调整程序的字符集

注意：alter table student character set ‘latin1’修改对新数据生效，对旧数据有影响

发送评论 编辑评论

发送评论编辑评论