mysql详解（持续更新）

库表基础属性

数据库操作

 建表规范

 字段类型

 建表约束

 存储引擎

 数据记录存储格式

 mysql表操作

 mysql表内容操作

mysql函数

 数字函数

 字符函数

 日期时间函数

mysql关键字

 union 与 union all

 in、exists、case when、insert into select

 join

数据库拓展操作

 数据库视图

 存储过程

数据库系统操作

 系统表

 binlog日志

mysql事务

 隔离机制

 锁定机制

  表级锁定

  行级锁定

  页级锁定

  排查死锁

 undo log（回滚日志）

 mvcc（多版本并发控制）

mysql索引

 单列索引

 组合索引

 空间索引

mysql优化

 ->设计角度

  存储引擎

 ->功能角度

  索引优化

  分页优化

 ->测试

  执行计划（explain）

  慢查询分析（profiling）

 ->架构角度

  分库分表

单表迁移分布式表思路

mysql中间件

 MySQL Router

快速导航

库表基础属性

数据库字符集

数据库变量

表字段属性

1. 数据库字符集

   

   Mysql默认的字符集编码是Latin1，其不支持中文，所以建库时需要进行设置charset=utf8

2. 数据库变量

   
   变量分为，系统变量和自定义变量两种。系统变量是由系统提供，不是用户定义，属于服务器层面；自定义变量则是用户自定义的。

   系统变量下有全局变量和会话变量。服务器每次启动将为所有的全局变量赋初始值，针对于所有的会话(连接)有效，但不能跨重启，而会话变量则仅仅针对于当前会话(连接)有效。

   1. 全局变量使用示例

      语句	内容
      show global variables;	查看所有的系统变量
      show global variables like ‘%char%’;	查看满足条件的部分系统变量
      select @@global.系统变量名;	查看指定的某个系统变量的值
      set @@global.系统变量名=值;	为某个系统变量赋值
      set global 系统变量名=值;	为某个系统变量赋值

   2. 会话变量使用示例

      语句	内容
      show session variables;	查看所有的会话变量
      show session variables like ‘%char%’;	查看满足条件的部分会话变量
      select @@session.tx_isolation;	查看指定的某个会话变量的值
      set @@tx_isolation=‘’ ;	为某个会话变量赋值
      set session tx_isolation=‘’;	为某个会话变量赋值

   [注]如果是全局级别，则需要加global，如果是会话级别，则需要加session,如果不写，默认session

   自定义变量分为用户变量和局部变量。用户变量针对于当前会话（连接）有效，同于会话变量的作用域，可应用在任何地方，也就是begin end 里面或begin end外面；局部变量则仅仅在定义它的begin end 中有效，应该再 begin end中的第一句话！

   1. 用户变量使用示例

      1. 声明并初始化

         方式一：set @用户变量名=值；

         方式二：set @用户变量名:=值；

         方式三：select @用户变量名:=值；

      2. 使用变量

         select @用户变量名;

   2. 局部变量使用示例

      1. 声明变量

         方式一：declare 变量名 类型;

         方式二：declare 变量名 类型 default 值 ;

      2. 赋值变量

         方式一：set @局部用户变量名=值 ;

         方式二：set @局部用户变量名:=值 ;

         方式三：select @局部用户变量名 :=值 ;

      3. 使用变量

         select 局部变量名;

3. 表字段属性

   

   1）默认：设置默认的值，如sex，默认值为男，如果不指定该列的值，则会有默认的值

   2）自动递增：自动在上一条记录的基础上+1(默认)，通常用来设计唯一的主键 index ,必须是整数类型，可以自定义设计主键自增的起始值和步长

   3）无符号：无符号的整数，声明了该列不能声明负数

   4）填充零：不足的位数，使用0来填充，如int(3),存5 ，005

   5）不是null：设置为 not null,如果不给它赋值，就会报错；null,如果不填写值，默认就是null

sql的类别有：
• DDL(data Definition Language) 数据定义语言：用来定义数据库对象：库、表、列 等。关键字：create ,drop ,alter 等
• DML(data Manipulation Language) 数据操作语言：用来对数据库中表的数据进行增删改，关键字：insert, delete ,update 等
• DQL(data Query Language) 数据查询语言：用来查询数据库中表的记录（数据）。关键字：select where 等
• DCL(data Control Language) 数据控制语言：用来定义数据库的访问权限和安全级别，及创建用户，关键字：GRANT ,REVOKE 等

DDL(data Definition Language) 数据定义语言

数据库操作

1. 创建数据库

   #创建数据库
   create database 数据库名称;
   
   #创建数据库，判断为不存在，再创建
   create database if not exists 数据库名称;
   
   #创建数据库，判断为不存在，在创建，并制定字符集为gbk
   create database if not exists 数据库名称 character set gbk;
   

2. 显示数据库

   #查询所有数据库的名称
   show databases;	
   
   #查询当前正在使用的数据库
   select database ();
   
   #查询某个数据库的字符集：查询某个数据库的创建语句
   show create database  数据库名称;
   

3. 修改数据库

   #修改数据库的字符集
   alter database 数据库名称 character set 字符集名称;
   

4. 删除数据库

   #删除数据库
   drop database 数据库名称;	
   
   #判断数据库为存在，再删除数据库
   drop database  if exists  数据库名称;
   

   [注]使用数据库命令：use 数据库名称;

建表规范

名字

建表的时候，给 表、字段、索引 起个好的名字能够提高沟通和降低维护成本。名字要遵守如下规范：

1. 见名知意：如：user_name
2. 大小写：尽量都用小写，小写字母更容易让人读懂
3. 分隔线：多个单词间采用_分隔，如：product_name
4. 表名：带上业务前缀，如果是订单相关的业务表，可以在表名前面加个前缀order_
5. 索引名：每张表的主键只有一个，一般使用id或者sys_no命名，普通索引和联合索引，其实是一类。在建立该类索引时，可以加ix_前缀,如：ix_product_status；唯一索引，可以加ux_前缀，比如：ux_product_code

字段类型

数据类型	作用	占字节数
tinyint	非常小的数据	1个字节
smallint	较小的数据	2个字节
mediumint	中等大小的数据	3个字节
int	标准的整数	4个字节
bigint	较大的数据	8个字节
float	浮点数	4个字节
double	浮点数	8个字节（精度问题）
decimal	字符串形式的浮点数 金融计算的时候，一般使用decimal
char	定长字符串，长度是固定的	字符串固定大小的0~255
varchar	可变长字符串，长度是可变的	可变字符串0~65535
tinytext	微型文本	2^8-1
text	用于存储大字符串，且有一个字符集，根据字符集的校对规则对值进行排序和比较	2^16-1
date	日期格式
time	时间格式
datetime	最常用的时间格式
timestamp	时间戳
year	年份格式
null	未知

在navicat如下所处位置：

相关字段的异同

1. date 和 datetime

   当你只需要存储日期（年、月、日）时，应使用 DATE 类型
   当你需要存储日期和时间（年、月、日、时、分、秒）时，应使用 DATETIME 类型

   1. date类型

      用于表示日期值，格式为 YYYY-MM-DD（年-月-日），不包含时间部分（小时、分钟、秒）

   2. datetime类型

      用于表示日期和时间值，格式为 YYYY-MM-DD HH:MM:SS（年-月-日 时:分:秒），包含了时间部分（小时、分钟、秒）

2. char 和 varchar

     char：• char表示定长字符串，长度是固定的；
         • 如果插入数据的长度小于char的固定长度时，则用空格填充；
         • 因为长度固定，所以存取速度要比varchar快很多，甚至能快50%，但正因为其长度固定，所以会占据多余的空间，是空间换时间的做法；
         • 对于char来说，最多能存放的字符个数为255，和编码无关

     varchar：• varchar表示可变长字符串，长度是可变的；
           • 插入的数据是多长，就按照多长来存储；
           • varchar在存取方面与char相反，它存取慢，因为长度不固定，但正因如此，不占据多余的空间，是时间换空间的做法；
           • 对于varchar来说，最多能存放的字符个数为65532

     [注]日常的设计，对于长度相对固定的字符串，可以使用char，对于长度不确定的，使用varchar更合适一些。

3. DateTime 和 TimeStamp

     相同点： • 两个数据类型存储时间的表现格式一致。均为 YYYY-MM-DD HH:MM:SS
           • 两个数据类型都包含「日期」和「时间」部分
           • 两个数据类型都可以存储微秒的小数秒（秒后6位小数秒）

     区 别： • DATETIME 的日期范围是 1000-01-01 00:00:00.000000 到 9999-12-31 23:59:59.999999；
             TIMESTAMP 的时间范围是1970-01-01 00:00:01.000000 UTC 到 ``2038-01-09 03:14:07.999999 UTC
           • 存储空间：DATETIME 的存储空间为 8 字节；TIMESTAMP 的存储空间为 4 字节
           • 时区相关：DATETIME 存储时间与时区无关；TIMESTAMP 存储时间与时区有关，显示的值也依赖于时区
           • 默认值：DATETIME 的默认值为 null；TIMESTAMP 的字段默认不为空(not null)，默认值为当前时间(CURRENT_TIMESTAMP)

空间字段的拓展

mysql空间还扩展支持几何数据的存储、生成、分析、优化

MySQL支持以下数据类型（存储）：

数据类型	说明
Geometry	是几何对象的基类, 也就是说Point, LineString, Polygon都是Geometry的子类
Point	(简单点)有一个坐标值，没有长度、面积、边界。数据格式为『经度(longitude)在前，维度(latitude)在后，用空格分隔』 例: POINT(121.213342 31.234532)
LineString	(简单线)由一系列点连接而成。如果线从头至尾没有交叉，那就是简单的（simple）、如果起点和终点重叠，那就是封闭的（closed）数据格式为『点与点之间用逗号分隔；一个点中的经纬度用空格分隔，与POINT格式一致』例：LINESTRING(121.342423 31.542423,121.345664 31.246790,121.453178 31.456862)
POLYGON	(简单面)多边形对象。可以是一个实心平面形，即没有内部边界，也可以有空洞，类似纽扣.数据格式为『实心型: 一个表示外部边界的LineString和0个表示内部边界的LineString组成』例:POLYGON((121.342423 31.542423,121.345664 31.246790,121.453178 31.456862),(121.563633 31.566652,121.233565 31.234565,121.568756 31.454367))；『纽扣型: 一个表示外部边界的LineString和多个表示内部边界的LineString组成』例: POLYGON((0 0,10 0, 10 10, 0 10))
MultiPoint	多点对象的集合
MULITILINESTRING	多线对象的集合
MUILITIPOLYGON	很多方面对象的集合
GEOMETRYCOLLECTION	任何几何集合对象的集合

在创建表的时候可以根据需求选择合适的几何类型存储你的空间数据。

空间数据类型生成

MySQL支持WKB,WKT数据生成空间数据类型，提供如下函数：

GeomFromText（wtk [，srid）PointFromText LINESTRINGFROMTEXT …
GeomFromWKB（wtk [，srid）GeomFromWKB GeomFromWKB …

建表约束

目的：约束用于限制加入表中的数据类型的范围，以保证数据库中数据的准确性和可靠性。
影响：约束是强制的，如果不满足约束条件，则无法插入或更新数据。
存储方式：约束是表结构的一部分，不占用额外的物理存储空间（除了外键约束可能需要在内部表中存储额外信息）
使用：在创建表或修改表结构时定义

1. 主键约束

   唯一标识表中的每一行。一个表只能有一个主键，主键列的值必须唯一，且不能为NULL。建表时有两种方式定义主键：

   • 在列定义中添加PRIMARY KEY

   CREATE TABLE projects (
       project_id INT PRIMARY KEY,
       project_name VARCHAR(255),
       start_date DATE NOT NULL,
       end_date DATE NOT NULL
   );
   

   • 不使用PRIMARY KEY约束作为列约束，而是使用表约束

   --CREATE TABLE语句末尾的CONSTRAINT子句将project_id列指定为主键。
   CREATE TABLE projects (
       project_id INT,
       project_name VARCHAR(255),
       start_date DATE NOT NULL,
       end_date DATE NOT NULL,
       CONSTRAINT pk_id PRIMARY KEY (project_id)
   );
   

   当主键由两列或以上组成时，必须使用PRIMARY KEY作为表约束。

   CREATE TABLE project_assignments (
       project_id INT,
       employee_id INT,
       join_date DATE NOT NULL,
       CONSTRAINT pk_assgn PRIMARY KEY (project_id , employee_id)
   );
   

   在初建表没有定义主键的话（尽管这不是一个好习惯），可以使用ALTER TABLE语句将主键添加到表中。

   例如，以下语句创建没有主键的project_milestones表。

   CREATE TABLE project_milestones(
       milestone_id INT,
       project_id INT,
       milestone_name VARCHAR(100)
   );
   

   现在，可以使用以下ALTER TABLE语句将milestone_id列设置为主键。

   ALTER TABLE project_milestones
   ADD CONSTRAINT pk_milestone_id PRIMARY KEY (milestone_id);
   

   一般很少删除表的主键。 但是，如果必须这样做，可以使用ALTER TABLE语句，如下所示：

   ALTER TABLE table_name
   DROP CONSTRAINT primary_key_constraint;
   

   例如，要删除project_milestones表的主键约束，请使用以下语句。

   ALTER TABLE project_milestones 
   DROP CONSTRAINT pk_milestone_id;
   

2. 外键约束

3. 唯一约束

   唯一约束即UNIQUE约束，有时，希望确保一列或多列中的值不重复。 例如，employees表中不能接受的重复电子邮件。由于电子邮件列不是主键的一部分，因此防止电子邮件列中重复值的唯一方法是使用UNIQUE约束。

   根据定义，SQL UNIQUE约束定义了一个规则，该规则可防止存储在不参与主键的特定列中有重复值。

   要注意，PRIMARY KEY约束最多只能有一个，而表中可以有多个UNIQUE约束。 如果表中有多个UNIQUE约束，则所有UNIQUE约束必须在不同的列集。

   与PRIMARY KEY约束不同，UNIQUE约束允许NULL值。 这取决于RDBMS要考虑NULL值是否唯一。
   例如，MySQL将NULL值视为不同的值，因此，可以在参与UNIQUE约束的列中存储多个NULL值。 但是，Microsoft SQL Server或Oracle数据库不是这种情况。

   建表时有两种方式定义UNIQUE约束：

   • 在创建表时创建UNIQUE约束

   CREATE TABLE users (
       user_id INT AUTO_INCREMENT PRIMARY KEY,
       username VARCHAR(255) NOT NULL UNIQUE,
       password VARCHAR(255) NOT NULL
   );
   

   • 使用表约束语法创建的UNIQUE约束

   CREATE TABLE users (
       user_id INT AUTO_INCREMENT PRIMARY KEY,
       username VARCHAR(255) NOT NULL,
       password VARCHAR(255) NOT NULL,
       CONSTRAINT uc_username UNIQUE (username)
   );
   

   在初建表没有定义UNIQUE约束的话，可以使用ALTER TABLE语句将主键添加到表中，前提条件是参与UNIQUE约束的列或列组合必须包含唯一值。

   ALTER TABLE users
   ADD CONSTRAINT uc_username UNIQUE(username);
   

   如果要添加新列并为创建UNIQUE约束，请使用以下形式的ALTER TABLE语句。

   ALTER TABLE users
   ADD new_column data_type UNIQUE;
   

   要删除UNIQUE约束，请使用ALTER TABLE语句

   ALTER TABLE table_name
   DROP CONSTRAINT unique_constraint_name;
   

4. SQL Not Null约束

   NOT NULL约束是一个列约束，它定义将列限制为仅具有非NULL值的规则。

   这意味着当使用INSERT语句向表中插入新行时，必须指定NOT NULL列的值。以下语句是NOT NULL约束语法。 它强制column_name不能接受NULL值。

   建表时有两种方式定义NOT NULL约束：

   • 建表字段添加

   CREATE TABLE table_name(
      ...
      column_name data_type NOT NULL,
      ...
   );
   

   • NOT NULL约束等同于CHECK约束

   CREATE TABLE table_name ( 
      ...
      column_name data_type,
      ...
      CHECK (column_name IS NOT NULL)
   );
   

存储引擎

1. 定义

   存储引擎是一种用来存储mysql中对象（记录和索引）的一种特定的结构（文件结构），处于mysql服务器的最底层，直接存储数据。

   MySQL数据库由于其自身架构的特点，存在多种数据存储引擎，每种存储引擎所针对的应用场景特点都不太一样。

2. 设置存储引擎

   在创建表的时候我们使用sql语句：Create table tableName () engine=myisam|innodb;

   engine指明了存储引擎是myisam还是innodb

3. 查看支持的存储引擎

   show engines

   

4. 默认存储引擎

   • 5.5版本前：MylSAM
   • 5.5版本后：InnoDB

5. 推荐存储引擎

   • InnoDB：要提供提交、回滚和恢复的事务安全（ACID 兼容）能力，并要求实现并发控制优选

   • MyISAM：主要用来插入和查询记录

6. 存储引擎功能列表

   

数据记录存储格式

InnoDB存储引擎下数据记录的存储格式——Row Format行格式

在MySQL中，所谓Row Format行格式是指数据记录(或者称之为行)在磁盘中的物理存储方式。具体地，对于InnoDB存储引擎而言，常见的行格式类型有Compact、Redundant、Dynamic和Compressed。

在创建、修改数据表的时候，可以显式地指定row format行格式。SQL语句语法如下

-- 创建数据表时,显示指定行格式
create table 表名 (列的信息) row_format=行格式名称;
-- 创建数据表时,修改行格式
alter table 表名 row_format=行格式名称;

与此同时，如果需要查看某数据表的行格式，可通过如下语句实现

show table status from 数据库名 like '<数据表名>';

下面通过一个示例来验证上述语句的使用及效果。在test1数据库中创建一张名为task2的数据表，并指定行格式的类型为compact：

use test1;
-- 指定行格式
create table task2
(
    idx int auto_increment,
    primary key (idx)
) row_format = compact;

-- 查看行格式
show table status from test1 like 'task2';

从下图可以看出表的行格式类型被设置为compact

然后再将该表的行格式类型修改为dynamic

-- 修改行格式
alter table task2
    row_format = dynamic;

-- 查看行格式
show table status from test1 like 'task2';

从下图可以看出表的行格式类型已被修改为dynamic

mysql表操作

1. 查看表

   #查询某个数据库中所有的表名称
   show tables;		
   
   #查询表结构
   desc 表名;			
   
   #查看表内容
   select * from 表名;	
   

2. 创建表

   #创建数据表
   create table 表名(								
   	列名  类型  comment 'remark',
   	列名  类型  comment 'remark',
   	列名  类型  comment 'remark',
   
   	primary key('fieldName') using btree,
   	spatial key 'remark' ('fieldName'),
   	key 'remark' ('filedName') using btree 
   ) engine = innodb;
   
   #复制表
   create table 表名 like 被复制的表名;	
   

3. 删除表

   #删除表
   drop table 表名;				
   
   #判断表存在，再删除表
   drop table if exists 表名;	
   

4. 清空表

   delete from 表名
   
   truncate table 表名
   

5. 修改表

   #修改表名
   alter table 表名 rename to 新表名;
   
   #修改表的字符集
   alter table 表名 character set 字符集名称;	
   
   #添加列	
   alter table 表名 add 列名 类型
   
   #添加多列	
   alter table 表名 add (字段名1 字段类型（长度）,字段名2 字段类型（长度）,...)
   
   #删除列
   alter table 表名 drop 列名;
   
   #修改列类型
   alter table 表名 modify 列名 新数据类型;
   
   #修改列名称 类型
   alter table 表名 change 列名 新列名 新数据类型;
   
   #添加主键
   alter table 表名 add primary key(列名);
   
   #删除主键
   alter table 表名 drop primary key;
   alter table 表名  modify  列名 int, drop primary key;
   
   #修改默认值
   ALTER TABLE testalter_tbl ALTER i SET DEFAULT 1000;
   
   #删除默认值
   ALTER TABLE testalter_tbl ALTER i DROP DEFAULT;
   
   #添加外键
   alter table 从表 add constraint 外键名称（形如：FK_从表_主表） foreign key 从表(外键字段) references 主表(主键字段);
   
   #删除外键
   alter table 表名 drop foreign key 外键名称
   

mysql表内容操作

表内容操作无非就是增删改查，当然用的最多的还是查，而且查这一块东西最多，用起来最难。

1. 增

   --插入单条
   insert into 表 (列名,列名...) values (值,值,...)
   --插入多条
   insert into 表 (列名,列名...) values (值,值,...),(值,值,值...)
   --插入多条
   insert into 表 (列名,列名...) select