优享资讯 | 你不了解的 Mysql语法与使用案例，包含优化,分区,非结构化,事务,锁,必须收藏

掘金后端 ( ) • 2024-07-01 15:24

你不了解的 Mysql语法与使用案例，包含优化,分区,非结构化,事务,锁,必须收藏

肖哥弹架构 跟大家“弹弹” MySQL 全面的使用方法欢迎点赞，点赞，点赞。

关注本人的公众号Solomon肖哥弹架构获取更多精彩内容

MySQL是开源关系型数据库管理系统（RDBMS），具备以下主要特性：

关系型数据库: 基于关系模型存储数据，使用表格、行和列的格式。
SQL支持: 遵循标准的SQL（Structured Query Language）进行数据查询、操作和管理。
多用户多线程: 支持多用户并发访问，能够在多线程环境中稳定运行。
跨平台: 可在多种操作系统上运行，包括多种Unix和Linux变体、Windows和macOS。
高性能: 优化的查询处理和缓存机制，提供高性能的数据存取。
可靠性和稳定性: 通过强大的事务处理能力、备份和恢复机制，确保数据的安全性和可靠性。
可扩展性: 支持从单服务器到复杂的多服务器配置，可随着业务需求的增长进行扩展。
开源: 作为开源软件，拥有活跃的社区支持和持续的开发。
存储过程、触发器和视图: 支持高级数据库功能，如存储过程、触发器、视图等。
全文搜索: 支持对文本数据进行全文搜索。
安全性: 提供多层次的安全特性，如用户权限管理、加密连接等。
复制: 支持主从复制，用于数据的高可用性和负载均衡。
分区: 支持表分区，可以提高特定查询的性能并简化数据管理。
GIS支持: 提供空间数据类型和空间函数，支持地理信息系统（GIS）应用。
审计插件: 支持审计插件，用于跟踪和记录数据库活动。
X Protocol: 支持文档存储和NoSQL查询，扩展了MySQL的功能。
集群和高可用性: 支持InnoDB集群和各种高可用性解决方案。
性能优化器: 提供查询优化器，自动选择最有效的查询执行计划。

MySQL能够用于的业务场景举例：

电子商务: 管理产品目录、订单、客户信息和库存。
内容管理系统（CMS） : 存储和检索网站内容，如文章、图片和视频。
企业资源规划（ERP） : 集成管理企业的各种资源，如财务、人力资源、供应链。
在线事务处理（OLTP） : 处理大量日常事务，如金融交易。
数据仓库: 存储和分析大量数据，用于决策支持。
社交媒体: 存储用户数据、帖子、评论和关系信息。
移动应用后端: 为移动应用提供数据存储和业务逻辑处理。
物联网（IoT） : 收集和处理来自传感器和设备的大量数据。
在线游戏: 管理用户账户、游戏数据和虚拟物品。
地理信息系统（GIS） : 存储和分析地理空间数据。

数据定义语言 (DDL)

创建数据库

CREATE DATABASE IF NOT EXISTS my_ecommerce;
/* 如果不存在，则创建一个名为my_ecommerce的数据库 */

选择数据库

USE my_ecommerce;
/* 选择my_ecommerce作为当前操作的数据库 */

创建表

CREATE TABLE IF NOT EXISTS products (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  price DECIMAL(10, 2) NOT NULL,
  category_id INT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
/* 创建一个products表，包含产品ID、名称、价格、分类ID和创建时间 */

修改表结构

ALTER TABLE products ADD COLUMN in_stock INT;
/* 在products表中添加一个名为in_stock的列，用于存储库存数量 */

删除表

DROP TABLE IF EXISTS products;
/* 如果存在，则删除products表 */

创建临时表

CREATE TEMPORARY TABLE IF NOT EXISTS temp_products AS
SELECT * FROM products WHERE price > 200;
/* 创建一个临时表，包含价格超过200的产品 */

创建外键时指定ON DELETE和ON UPDATE行为

CREATE TABLE IF NOT EXISTS order_items (
  id INT AUTO_INCREMENT PRIMARY KEY,
  order_id INT,
  product_id INT,
  quantity INT,
  FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
  FOREIGN KEY (product_id) REFERENCES products(id) ON UPDATE SET NULL
);
/* 如果订单被删除，则级联删除订单项；如果产品ID更新，则将对应订单项的产品ID设置为NULL */

创建虚拟列

CREATE TABLE IF NOT EXISTS orders (
  id INT AUTO_INCREMENT PRIMARY KEY,
  product_id INT,
  quantity INT,
  total_price AS (quantity * (SELECT price FROM products WHERE id = product_id))

使用虚拟列

SELECT * FROM orders;
/* 虚拟列total_price将根据quantity和products表中的价格动态计算 */

创建物化视图

CREATE MATERIALIZED VIEW IF NOT EXISTS monthly_sales AS
SELECT YEAR(sale_date) AS sale_year, MONTH(sale_date) AS sale_month, SUM(amount) AS total_sales
FROM sales
GROUP BY YEAR(sale_date), MONTH(sale_date);
/* 创建一个物化视图，包含每月的销售总额 */

刷新物化视图

ALTER TABLE monthly_sales REFRESH;
/* 刷新monthly_sales物化视图 */

GIS空间数据

创建GIS空间数据表

CREATE TABLE IF NOT EXISTS geofences (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255),
  geometry Geometry NOT NULL,
  SPATIAL INDEX (geometry)
);
/* 创建一个包含空间数据的表 */

使用GIS函数

SELECT * FROM geofences
WHERE ST_Contains(geometry, ST_GeomFromText('POINT(lon lat)'));
/* 查询包含特定点的所有地理围栏 */

使用分区表

创建分区表

CREATE TABLE IF NOT EXISTS large_table (
  id INT,
  data VARCHAR(255),
  created_at DATE
)
PARTITION BY RANGE ( YEAR(created_at) ) (
  PARTITION p0 VALUES LESS THAN (1990),
  PARTITION p1 VALUES LESS THAN (2000),
  PARTITION p2 VALUES LESS THAN MAXVALUE
);
/* 创建一个按年分区的表 */

数据分区

创建分区表

CREATE TABLE sales (
  id INT NOT NULL,
  sale_date DATE NOT NULL,
  amount DECIMAL(10, 2) NOT NULL,
  PRIMARY KEY (id, sale_date)
)
PARTITION BY RANGE (YEAR(sale_date)) (
  PARTITION p0 VALUES LESS THAN (2019),
  PARTITION p1 VALUES LESS THAN (2020),
  PARTITION p2 VALUES LESS THAN MAXVALUE
);
/* 创建按年分区的销售数据表 */

数据操纵语言 (DML)

插入数据

INSERT INTO products (name, price, category_id) VALUES ('Laptop', 999.99, 1);
/* 向products表中插入一条新记录 */

更新数据

UPDATE products SET price = 899.99 WHERE id = 1;
/* 更新products表中ID为1的记录，将价格改为899.99 */

删除数据

DELETE FROM products WHERE id = 1;
/* 删除products表中ID为1的记录 */

批量插入数据

INSERT INTO products (name, price, category_id) VALUES 
('Mouse', 25.50, 3), ('Keyboard', 45.75, 3);
/* 向products表中批量插入两条记录 */

数据查询语言 (DQL)

基本查询

SELECT * FROM products;
/* 查询products表中的所有记录 */

条件查询

SELECT * FROM products WHERE price > 500;
/* 查询products表中价格大于500的记录 */

排序查询

SELECT * FROM products ORDER BY price DESC;
/* 查询products表中的记录，并按价格降序排序 */

限制查询结果数量

SELECT * FROM products LIMIT 5;
/* 查询products表中的前5条记录 */

聚合函数

COUNT()

SELECT COUNT(*) FROM products;
/* 计算products表中的记录数 */

SUM()

SELECT SUM(price) FROM products WHERE category_id = 1;
/* 计算分类ID为1的所有产品的总价格 */

AVG()

SELECT AVG(price) FROM products;
/* 计算products表中所有产品的平均价格 */

MAX() / MIN()

SELECT MAX(price), MIN(price) FROM products;
/* 查询products表中的最高价格和最低价格 */

分组查询

GROUP BY

SELECT category_id, COUNT(*) FROM products GROUP BY category_id;
/* 按分类ID分组，并计算每个分类的产品数量 */

多表查询

内连接

SELECT p.name, c.name AS category_name FROM products p
INNER JOIN categories c ON p.category_id = c.id;
/* 内连接products和categories表，获取产品名称和分类名称 */

左外连接

SELECT p.name, o.total_amount FROM products p
LEFT JOIN orders o ON p.id = o.product_id;
/* 左外连接products和orders表，即使某些产品没有订单也会列出 */

子查询

在WHERE子句中使用子查询

SELECT * FROM products WHERE price > (SELECT AVG(price) FROM products);
/* 查询价格高于平均价格的产品 */

HAVING 子句

使用HAVING进行条件筛选

SELECT category_id, SUM(price) FROM products GROUP BY category_id HAVING SUM(price) > 5000;
/* 选择总价格超过5000的分类ID和总价格 */

条件查询增强

使用IN子句

SELECT * FROM products WHERE category_id IN (1, 2);
/* 查询分类ID为1或2的所有产品 */

使用NOT子句

SELECT * FROM products WHERE price NOT BETWEEN 100 AND 200;
/* 查询价格不在100到200之间的所有产品 */

连接查询

自连接

SELECT o1.product_id, COUNT(*) 
FROM order_items o1
JOIN orders o2 ON o1.order_id = o2.order_id
WHERE o2.user_id = 1
GROUP BY o1.product_id;
/* 查询用户1购买的所有产品ID及其数量 */

子查询作为计算字段

SELECT name, price, (price * 1.1) AS price_with_tax
FROM products;
/* 为每个产品计算含税价格 */

使用CASE WHEN进行条件逻辑

SELECT name,
       price,
       CASE 
         WHEN price <= 50 THEN 'Cheap'
         WHEN price > 50 AND price <= 100 THEN 'Moderate'
         ELSE 'Expensive'
       END AS price_category
FROM products;
/* 根据价格将产品分类为'Cheap', 'Moderate', 'Expensive' */

条件表达式

使用CASE WHEN THEN ELSE

SELECT product_id,
  CASE
    WHEN stock > 10 THEN 'In Stock'
    WHEN stock > 0 THEN 'Low Stock'
    ELSE 'Out of Stock'
  END AS stock_status
FROM products;
/* 根据库存数量给产品分类 */

使用FULL OUTER JOIN

SELECT *
FROM products p
FULL OUTER JOIN categories c ON p.category_id = c.id;
/* 获取products和categories表的所有记录，包括没有匹配的记录 */

使用UNION合并查询结果

SELECT name FROM products WHERE price < 100
UNION
SELECT name FROM products WHERE price > 500;
/* 查询价格低于100或高于500的所有产品名称，不包含重复项 */

使用UNION ALL合并查询结果

SELECT name FROM products WHERE price < 100
UNION ALL
SELECT name FROM products WHERE price > 500;
/* 查询价格低于100或高于500的所有产品名称，包含重复项 */

动态SQL

执行动态SQL

SET @sql = 'SELECT * FROM products WHERE price > ' + IFNULL(CONVERT(@price_threshold, CHAR), '0');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
/* 根据变量@price_threshold的值动态构建并执行SQL语句 */

全文索引和搜索

创建全文索引

ALTER TABLE products ADD FULLTEXT(name);
/* 在products表的name字段上创建全文索引 */

执行全文搜索

SELECT * FROM products WHERE MATCH(name) AGAINST ('search term');
/* 在name字段上执行全文搜索，搜索包含'search term'的记录 */

JSON数据类型

使用JSON数据类型

SELECT JSON_OBJECT(
  'name', name,
  'price', price,
  'in_stock', IFNULL(in_stock, 'unknown')
) AS product_info
FROM products;
/* 创建一个包含产品信息的JSON对象 */

分布式ID生成器

使用分布式ID生成器

SELECT GET_LOCK('my_lock_name', 10);
SELECT RELEASE_LOCK('my_lock_name');
/* 使用锁来保证分布式系统中ID的唯一性 */

45. 序列（Sequences）

创建序列

CREATE SEQUENCE IF NOT EXISTS product_sequence
START WITH 1
INCREMENT BY 1;
/* 创建一个从1开始，每次递增1的序列 */

使用序列

SELECT NEXT VALUE FOR product_sequence AS product_id FROM DUAL;
/* 获取序列的下一个值 */

完整性约束

主键约束

ALTER TABLE products ADD PRIMARY KEY (id);
/* 将id列设置为主键 */

外键约束

ALTER TABLE products ADD FOREIGN KEY (category_id) REFERENCES categories(id);
/* 将category_id设置为外键，引用categories表的id列 */

非空约束

ALTER TABLE products MODIFY name VARCHAR(255) NOT NULL;
/* 将name列设置为非空 */

唯一约束

ALTER TABLE products ADD UNIQUE (name);
/* 确保name列的值是唯一的 */

索引操作

创建索引

CREATE INDEX idx_price ON products(price);
/* 在products表的价格列上创建索引 */

删除索引

DROP INDEX idx_price ON products;
/* 删除products表上的idx_price索引 */

视图

创建视图

CREATE VIEW IF NOT EXISTS expensive_products AS
SELECT * FROM products WHERE price > 1000;
/* 创建一个视图，包含价格超过1000的产品 */

存储过程

创建存储过程

DELIMITER $$
CREATE PROCEDURE GetProductsByCategory(IN cat_id INT)
BEGIN
  SELECT * FROM products WHERE category_id = cat_id;
END $$
DELIMITER ;
/* 创建一个存储过程，根据分类ID获取产品列表 */

触发器

创建触发器

DELIMITER $$
CREATE TRIGGER after_product_insert AFTER INSERT ON products
FOR EACH ROW
BEGIN
  INSERT INTO product_log (product_id, action) VALUES (NEW.id, 'Inserted');
END $$
DELIMITER ;
/* 创建一个触发器，每当有新产品插入时，在product_log表中记录这一操作 */

锁定表

锁定表以进行维护

LOCK TABLES products WRITE;
/* 锁定products表以进行写操作 */

解锁表
```
UNLOCK TABLES;
/* 解锁所有表 */
```

XA事务（分布式事务）

使用XA事务

XA START 'bname';
/* 开始一个XA事务 */
XA END 'bname';
/* 结束XA事务 */
XA PREPARE 'bname';
/* 准备XA事务 */
XA COMMIT 'bname';
/* 提交XA事务 */

事务

使用事务

START TRANSACTION;
INSERT INTO orders (user_id, product_id, quantity) VALUES (1, 1, 2);
UPDATE products SET in_stock = in_stock - 2 WHERE id = 1;
COMMIT;
/* 开启事务，插入一条订单记录并更新库存，然后提交事务 */

事务隔离级别

设置事务隔离级别

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
/* 设置当前会话的事务隔离级别为READ COMMITTED */

保存点

设置保存点

START TRANSACTION;
INSERT INTO products ...;
SAVEPOINT sp1;
/* ... 事务中的某个点，可以用来回滚到这个点 */

回滚到保存点

ROLLBACK TO SAVEPOINT sp1;
/* 回滚到sp1保存点，撤销从这个点之后的所有操作 */

使用EXPLAIN分析查询

EXPLAIN SELECT * FROM products WHERE price > 200;
/* 分析查询，查看查询的执行计划 */

字符集和排序规则

设置默认字符集

SET NAMES 'utf8';
/* 设置客户端和服务器之间的字符集为utf8 */

设置排序规则

SET character_set_client = 'utf8';
SET collation_connection = 'utf8_general_ci';
/* 设置客户端字符集和连接的排序规则 */

配置和系统变量

查看系统变量

SHOW VARIABLES LIKE 'max_allowed_packet';
/* 查看max_allowed_packet系统变量的值 */

设置系统变量

SET GLOBAL max_allowed_packet = 1073741824;
/* 设置全局max_allowed_packet的值为1GB */

备份和还原

使用mysqldump进行备份

mysqldump -u username -p database_name > backup.sql
/* 导出数据库的SQL备份到backup.sql文件 */

使用SOURCE进行还原

SOURCE backup.sql;
/* 从backup.sql文件导入SQL备份到当前数据库 */

性能调优

使用索引优化查询

ALTER TABLE products ADD INDEX (name);
/* 在products表的name列上添加索引以优化搜索 */

使用事件调度器

创建事件

CREATE EVENT IF NOT EXISTS ev_clean_up
ON SCHEDULE EVERY 1 DAY
STARTS TIMESTAMP(CURRENT_DATE) + INTERVAL 1 DAY
DO
BEGIN
  DELETE FROM products WHERE created_at < NOW() - INTERVAL 1 YEAR;
END;
/* 创建一个事件，每天自动清理一年前创建的产品记录 */

字符集转换函数

转换字符串的字符集

SELECT CONVERT(name USING utf8mb4) FROM products;
/* 将products表中的name字段的字符集转换为utf8mb4 */

数据类型转换函数

转换数据类型

SELECT CAST(price AS UNSIGNED) FROM products;
/* 将价格字段的价格转换为无符号整数 */

格式化日期和时间

格式化日期时间

SELECT DATE_FORMAT(created_at, '%Y-%m-%d %H:%i:%s') FROM products;
/* 格式化created_at字段为'年-月-日 时:分:秒'格式 */

时区转换

转换时区

SELECT created_at, CONVERT_TZ(created_at, '+00:00', '+08:00') AS created_at_beijing
FROM products;
/* 将created_at字段的时间从UTC时区转换到北京时间 */

加密和解密函数

加密数据

SELECT ENCODE(name, 'key') FROM products;
/* 使用密钥'key'对产品名称进行加密 */

解密数据

SELECT DECODE(encrypted_name, 'key') FROM products;
/* 使用密钥'key'对加密后的产品名称进行解密 */

信息模式（INFORMATION_SCHEMA）

查询INFORMATION_SCHEMA

SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'my_ecommerce';
/* 查询my_ecommerce数据库中所有表的列信息 */

角色和权限

创建角色

CREATE ROLE 'read_role';
/* 创建一个新角色read_role */

将角色授予用户

GRANT 'read_role' TO 'user_name'@'host_name';
/* 将read_role角色授予给user_name用户 */

切换角色

SET ROLE 'read_role';
/* 切换到read_role角色 */

事件

创建事件

CREATE EVENT IF NOT EXISTS ev_nightly_backup
ON SCHEDULE EVERY 1 DAY STARTS TIMESTAMP(CURRENT_DATE) + INTERVAL 1 DAY
DO
  BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
      -- Error handling code here
    END;

    -- Backup commands or SQL statements
  END;
/* 创建一个每晚执行的事件，用于执行备份任务 */

行模式匹配

行模式匹配

SELECT * FROM (
  SELECT @row_num := @row_num + 1 AS row_number, t.*
  FROM products, (SELECT @row_num := 0) r
  ORDER BY name
) t
WHERE row_number BETWEEN 11 AND 20;
/* 查询第11到20行的数据 */

窗口函数

使用窗口函数

SELECT product_id,
  name,
  price,
  RANK() OVER (ORDER BY sales DESC) AS sales_rank
FROM products;
/* 根据销量对产品进行排名 */

多源复制

配置多源复制

CHANGE REPLICATION SOURCE TO
  SOURCE_LOG_FILE='binlog_file',
 SOURCE_LOG_POS=binlog_position;
/* 配置MySQL复制的源 */

在线DDL操作

使用pt-online-schema-change

pt-online-schema-change --alter "ADD COLUMN new_column INT" D=dbname,t=tablename,u=username,p=password
/* 使用pt-online-schema-change工具在线修改表结构 */

审计插件

配置审计日志

AUDIT LOG EVENTS 'UPDATE' ON TABLES 'my_db.my_table';
/* 配置审计日志，记录对my_db.my_table表的UPDATE操作 */

资源组

创建资源组

CREATE RESOURCE GROUP my_resource_group TYPE=CONSUMER;
/* 创建一个新的资源组 */

将资源组与线程池关联

ALTER RESOURCE GROUP my_resource_group
FORCE THREAD_PRIORITY 1;
/* 将资源组与线程池关联，并设置线程优先级 */

性能模式

查询性能模式

SET NAMES 'utf8';
SET SESSION performance_schema = 'ON';
/* 开启性能模式 */

X Protocol

使用X Protocol

/* X Protocol 允许使用NoSQL风格的查询，以及对Document Store的支持 */

角色和权限的高级管理

查看角色

SELECT * FROM mysql.roles;
/* 列出所有角色 */

创建用户并授予角色

CREATE USER 'new_user'@'localhost';
SET DEFAULT ROLE 'read_role' TO 'new_user'@'localhost';
GRANT 'new_user'@'localhost' TO 'read_role';
/* 创建新用户并授予角色 */

审计日志

配置审计日志

INSTALL PLUGIN audit_log SONAME 'audit_log.so';
/* 安装审计插件 */

创建审计策略

CREATE SERVER_AUDIT IF NOT EXISTS audit_name FOR SERVER ON EVERY EVENT;
/* 创建一个审计策略，记录所有事件 */

服务器状态变量

查看服务器状态变量

SHOW STATUS LIKE 'Innodb_rows%';
/* 查看所有与InnoDB存储引擎行操作相关的状态变量 */

服务器系统变量

查看和设置系统变量

SHOW VARIABLES LIKE 'max_connections';
SET GLOBAL max_connections = 1000;
/* 查看最大连接数并设置为1000 */

备份和还原

使用mysqldump进行热备份

mysqldump --single-transaction --quick -u username -p database_name > backup.sql
/* 导出数据库的热备份 */

使用pt-table-checksum和pt-table-sync

表校验和同步

pt-table-checksum --nocheck --print --databases=your_db --tables=your_table
pt-table-sync source_db destination_db your_table
/* 使用Percona Toolkit工具进行表校验和同步 */

使用mysqlbinlog分析二进制日志

mysqlbinlog --start-datetime="2019-01-01 00:00:00" --stop-datetime="2019-01-02 23:59:59" binlog_file
/* 从二进制日志中分析指定日期范围内的事件 */

使用GROUPING SETS进行复杂的分组查询

SELECT category_id, SUM(price), COUNT(DISTINCT product_id)
FROM products
GROUP BY GROUPING SETS ((category_id), ());
/* 使用GROUPING SETS进行多级别的分组 */

使用CROSS APPLY和OUTER APPLY在行模式匹配

SELECT t1.*, t2.*
FROM products t1
CROSS APPLY (
  SELECT TOP 1 * FROM reviews WHERE review_product_id = t1.id ORDER BY review_date DESC
) t2;
/* 对于每个产品，获取最新的评论 */

使用RECURSIVE进行递归查询

SELECT * FROM (
  SELECT employee_id, manager_id, 'T1' AS trace
  FROM employees WHERE employee_id = ? -- Starting point
  UNION ALL
  RECURSIVE subquery AS (
    SELECT e.employee_id, e.manager_id, CONCAT(trace, '->T', e.employee_id) AS trace
    FROM employees e
    INNER JOIN subquery s ON s.employee_id = e.manager_id
  )
) t;
/* 递归查询员工的整个管理链 */

使用EXPLAIN ANALYZE进行查询性能分析

EXPLAIN ANALYZE SELECT * FROM large_table;
/* 进行更深入的查询性能分析 */

使用性能模式（Performance Schema）

SET NAMES 'utf8';
SET GLOBAL performance_schema = 'ON';
/* 启用性能模式以监控数据库性能 */

使用透明数据加密（TDE）

启用透明数据加密

/* 透明数据加密通常在操作系统级别或通过第三方插件实现，并非MySQL直接提供 */

使用MySQL Router

配置MySQL Router

/* MySQL Router 是一个负载均衡器和连接路由器，用于MySQL连接管理 */
mysqlrouter --user=root --password=<password> --routes-to-my-mysql-server=<hostname>

使用MySQL Shell的AdminAPI

使用AdminAPI管理MySQL

/* MySQL Shell 提供了一个交互式的环境和AdminAPI，用于执行管理任务 */
mysqlsh -u user -p -e "session.runSql('SELECT VERSION()')