1. MySQL关键字概述MySQL作为最流行的关系型数据库之一其关键字体系构成了SQL语句的骨架。这些保留字在数据库操作中具有特殊含义从基础的SELECT、INSERT到高级的PARTITION BY、WINDOW函数每个关键字都对应着特定的数据操作场景。在实际开发中我经常遇到因为不了解关键字特性而导致的语法错误或性能问题比如误用GROUP BY导致结果集异常或者没有正确使用INDEX提示而引发全表扫描。注意MySQL关键字不区分大小写但按照惯例我们通常用大写形式书写这既是行业规范也能提高SQL语句的可读性。2. 数据操作语言(DML)关键字详解2.1 查询操作核心关键字SELECT作为最基础也最复杂的关键字其完整语法包含12个可能的子句。在电商系统开发中我常用这样的组合SELECT product_id AS pid, product_name, unit_price * 0.9 AS discounted_price FROM products WHERE category_id 5 AND stock_quantity 0 ORDER BY create_time DESC LIMIT 10 OFFSET 0;这里有几个关键点AS关键字用于别名定义在复杂查询中必不可少WHERE子句支持BETWEEN、IN、LIKE等运算符ORDER BY配合DESC/ASC控制排序方向2.2 数据修改关键字INSERT的三种写法各有适用场景-- 标准写法明确列名 INSERT INTO users(username, email) VALUES (john_doe, johnexample.com); -- 批量插入实测性能提升5-8倍 INSERT INTO products(name, price) VALUES (Mouse, 49.9), (Keyboard, 99.9); -- 从查询结果插入数据迁移常用 INSERT INTO user_backup SELECT * FROM users WHERE reg_date 2020-01-01;UPDATE操作要特别注意WHERE条件我在生产环境见过因为没有WHERE导致全表更新的惨剧UPDATE orders SET status completed WHERE order_id 10045 AND status processing;3. 数据定义语言(DDL)关键字3.1 数据库对象操作CREATE TABLE语句的完整语法包含20个可选参数。这是我在金融系统中创建账户表的示例CREATE TABLE accounts ( account_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, user_id INT NOT NULL, balance DECIMAL(15,2) NOT NULL DEFAULT 0.00, status ENUM(active,frozen,closed) NOT NULL DEFAULT active, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (account_id), UNIQUE INDEX idx_user_account (user_id, account_id), CONSTRAINT fk_user FOREIGN KEY (user_id) REFERENCES users(user_id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_unicode_ci;关键元素解析NOT NULL约束比应用层校验更可靠DEFAULT值能简化应用代码复合索引的列顺序影响查询性能3.2 高级表结构修改ALTER TABLE可能是最危险的DDL语句大表操作会导致锁表。我的经验是添加列使用AFTER指定位置修改列类型要测试数据兼容性大数据表使用pt-online-schema-change工具-- 添加新列的正确姿势 ALTER TABLE products ADD COLUMN discount_rate TINYINT UNSIGNED DEFAULT 0 AFTER unit_price;4. 事务控制与锁关键字4.1 事务处理关键字银行转账的经典事务示例START TRANSACTION; UPDATE accounts SET balance balance - 1000 WHERE account_id 1001 AND balance 1000; UPDATE accounts SET balance balance 1000 WHERE account_id 2002; COMMIT; -- 发生错误时执行 ROLLBACK;重要提示InnoDB默认autocommit1开发复杂业务时一定要显式使用事务。4.2 锁相关关键字在高并发票务系统中SELECT ... FOR UPDATE是解决超卖问题的利器BEGIN; SELECT quantity FROM tickets WHERE show_id 101 AND seat_no A12 FOR UPDATE; -- 检查余票后执行更新 UPDATE tickets SET quantity quantity - 1 WHERE show_id 101 AND seat_no A12; COMMIT;LOCK TABLES在MyISAM引擎中仍有使用场景但要注意必须显式UNLOCK TABLES不同会话的锁会相互阻塞死锁检测不如InnoDB完善5. 查询优化关键字5.1 执行计划分析EXPLAIN是我优化慢查询的首选工具。分析这个查询EXPLAIN FORMATJSON SELECT c.customer_name, o.order_total FROM customers c JOIN orders o ON c.customer_id o.customer_id WHERE c.city 杭州 ORDER BY o.order_date DESC LIMIT 100;关键指标解读type列显示ALL表示全表扫描possible_keys显示可用索引rows预估检查的行数Extra列出现Using filesort需要警惕5.2 索引提示关键字当优化器选错索引时FORCE INDEX能派上用场SELECT product_id FROM products FORCE INDEX(idx_category) WHERE category_id 8 AND price BETWEEN 100 AND 500;但要注意数据分布变化后可能失效不同MySQL版本行为可能不同应该先用EXPLAIN验证效果6. 高级特性关键字6.1 窗口函数MySQL 8.0引入的窗口函数极大简化了复杂分析-- 计算每个部门的薪资排名 SELECT employee_name, department, salary, RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank FROM employees;常用窗口函数ROW_NUMBER(): 唯一序号LEAD/LAG(): 访问前后行数据FIRST_VALUE(): 窗口首行值6.2 公用表表达式(CTE)WITH子句使复杂查询更清晰WITH regional_sales AS ( SELECT region, SUM(amount) AS total_sales FROM orders GROUP BY region ), top_regions AS ( SELECT region FROM regional_sales WHERE total_sales 1000000 ) SELECT * FROM top_regions;递归CTE处理树形数据WITH RECURSIVE org_tree AS ( -- 基础查询根节点 SELECT id, name, parent_id, 1 AS level FROM organization WHERE parent_id IS NULL UNION ALL -- 递归查询 SELECT o.id, o.name, o.parent_id, t.level 1 FROM organization o JOIN org_tree t ON o.parent_id t.id ) SELECT * FROM org_tree;7. 关键字使用陷阱与最佳实践7.1 常见错误案例保留字作为标识符-- 错误写法 CREATE TABLE order (id INT); -- 正确写法 CREATE TABLE order (id INT);GROUP BY与HAVING混淆-- 错误在WHERE中使用聚合函数 SELECT department, AVG(salary) FROM employees WHERE AVG(salary) 5000 GROUP BY department; -- 正确使用HAVING过滤分组 SELECT department, AVG(salary) FROM employees GROUP BY department HAVING AVG(salary) 5000;7.2 性能优化建议避免SELECT *只查询需要的列特别是TEXT/BLOB类型合理使用JOIN小表驱动大表确保关联字段有索引考虑使用STRAIGHT_JOIN控制顺序分页优化-- 低效写法 SELECT * FROM large_table LIMIT 1000000, 20; -- 优化方案假设有自增主键 SELECT * FROM large_table WHERE id 1000000 ORDER BY id LIMIT 20;8. 版本特性差异8.1 MySQL 5.7 vs 8.0关键字变化新增关键字WINDOW (窗口函数)PERSIST (变量持久化)SYSTEM (时间函数)行为变更GROUP BY默认启用ONLY_FULL_GROUP_BY模式EXPLAIN新增FORMATTREE选项新增INVISIBLE索引特性8.2 兼容性注意事项保留字列表扩展MySQL 8.0新增50保留字使用反引号包裹可疑标识符存储引擎差异MyISAM不支持事务关键字NDB集群特有关键字9. 实用技巧与工具9.1 关键字查询方法查看全部保留字-- MySQL 8.0 SELECT * FROM information_schema.keywords; -- 所有版本适用 SHOW RESERVED WORDS;检查标识符合法性-- 会返回带反引号的合法名称 SELECT QUOTE_IDENTIFIER(order);9.2 SQL格式化工具命令行工具# 使用mysqlpager美化输出 mysql --pagerless -SFX -e SELECT * FROM users在线格式化sqlformat.orgdpriver.com/sql-formatterIDE插件MySQL Workbench自带格式化VSCode的SQLTools扩展10. 安全相关关键字10.1 权限控制精细授权示例-- 只允许查询特定列 GRANT SELECT(name, email) ON db.users TO reporter%; -- 存储过程权限 GRANT EXECUTE ON PROCEDURE db.cleanup TO operatorlocalhost;10.2 数据加密透明数据加密(TDE)CREATE TABLE payment_records ( id INT PRIMARY KEY, card_number VARBINARY(255) DEFAULT AES_ENCRYPT(, secret_key) ); -- 插入加密数据 INSERT INTO payment_records VALUES (1, AES_ENCRYPT(1234-5678-9012-3456, secret_key)); -- 查询解密 SELECT id, AES_DECRYPT(card_number, secret_key) AS card_number FROM payment_records;11. 监控与诊断关键字11.1 性能分析查看正在执行的SQL-- 完整进程列表 SHOW FULL PROCESSLIST; -- 8.0的性能模式 SELECT * FROM performance_schema.threads WHERE PROCESSLIST_COMMAND IS NOT NULL;11.2 资源监控关键指标查询-- 表空间使用情况 SELECT table_schema, SUM(data_lengthindex_length)/1024/1024 AS total_mb FROM information_schema.tables GROUP BY table_schema; -- 索引使用统计 SELECT * FROM sys.schema_index_statistics WHERE table_schema mydb;12. 备份与恢复关键字12.1 逻辑备份mysqldump常用参数# 完整备份 mysqldump --single-transaction --routines --triggers mydb backup.sql # 只备份结构 mysqldump --no-data mydb schema.sql12.2 物理备份Percona XtraBackup命令# 全量备份 xtrabackup --backup --target-dir/backups/full # 增量备份 xtrabackup --backup --target-dir/backups/inc1 \ --incremental-basedir/backups/full13. 配置优化关键字13.1 参数调整关键配置项-- 临时设置重启失效 SET GLOBAL innodb_buffer_pool_size 2147483648; -- 持久化设置8.0 SET PERSIST max_connections 500;13.2 引擎状态InnoDB监控-- 开启标准监控 SET GLOBAL innodb_status_output ON; -- 获取详细状态 SHOW ENGINE INNODB STATUS\G14. 高可用关键字14.1 主从复制配置命令示例-- 在主库创建复制账号 CREATE USER repl% IDENTIFIED BY password; GRANT REPLICATION SLAVE ON *.* TO repl%; -- 在从库配置连接 CHANGE MASTER TO MASTER_HOSTmaster_host, MASTER_USERrepl, MASTER_PASSWORDpassword, MASTER_AUTO_POSITION1; START SLAVE;14.2 组复制MySQL Group Replication设置-- 初始化组 SET GLOBAL group_replication_bootstrap_groupON; START GROUP_REPLICATION; SET GLOBAL group_replication_bootstrap_groupOFF; -- 新节点加入 START GROUP_REPLICATION USERrepl_user, PASSWORDpassword;15. 云数据库特性15.1 AWS RDS专用命令管理命令示例-- 创建只读副本 CALL mysql.rds_create_read_replica(replica-instance, source-instance, 3306); -- 设置备份保留期 CALL mysql.rds_set_configuration(binlog retention hours, 24);15.2 Azure Database操作管理弹性池-- 将数据库移入弹性池 ALTER DATABASE db1 MODIFY NAME db1, SERVICE_OBJECTIVE ELASTIC_POOL(name my-pool); -- 监控资源使用 SELECT * FROM sys.resource_stats;16. 开发技巧与模式16.1 软删除实现通用模式CREATE TABLE articles ( id INT PRIMARY KEY, title VARCHAR(100), content TEXT, is_deleted TINYINT(1) DEFAULT 0, deleted_at TIMESTAMP NULL ); -- 删除操作变为更新 UPDATE articles SET is_deleted 1, deleted_at NOW() WHERE id 123; -- 查询时过滤 SELECT * FROM articles WHERE is_deleted 0;16.2 审计日志方案使用触发器记录变更CREATE TABLE user_audit ( id INT AUTO_INCREMENT PRIMARY KEY, user_id INT NOT NULL, changed_column VARCHAR(50), old_value TEXT, new_value TEXT, change_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ); CREATE TRIGGER before_user_update BEFORE UPDATE ON users FOR EACH ROW BEGIN IF NEW.email OLD.email THEN INSERT INTO user_audit(user_id, changed_column, old_value, new_value) VALUES (OLD.id, email, OLD.email, NEW.email); END IF; END;17. 数据类型相关关键字17.1 JSON类型操作MySQL 5.7的JSON函数-- 创建JSON列 CREATE TABLE product_specs ( id INT PRIMARY KEY, specs JSON ); -- 插入JSON数据 INSERT INTO product_specs VALUES (1, { dimensions: {width: 10, height: 20}, colors: [red, blue] }); -- 查询JSON属性 SELECT id, specs-$.dimensions.width AS width, JSON_EXTRACT(specs, $.colors[0]) AS primary_color FROM product_specs; -- 更新JSON部分内容 UPDATE product_specs SET specs JSON_SET(specs, $.dimensions.depth, 15) WHERE id 1;17.2 空间数据类型GIS数据处理示例CREATE TABLE locations ( id INT PRIMARY KEY, name VARCHAR(100), position POINT SRID 4326, SPATIAL INDEX(position) ); -- 插入空间数据 INSERT INTO locations VALUES (1, Eiffel Tower, ST_GeomFromText(POINT(2.2945 48.8584))); -- 查找5公里内的点 SELECT name FROM locations WHERE ST_Distance_Sphere(position, ST_GeomFromText(POINT(2.3 48.86))) 5000;18. 存储程序关键字18.1 存储过程带参数的复杂过程DELIMITER // CREATE PROCEDURE transfer_funds( IN from_account BIGINT, IN to_account BIGINT, IN amount DECIMAL(15,2), OUT status_code INT, OUT message VARCHAR(100) ) BEGIN DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN ROLLBACK; SET status_code -1; SET message CONCAT(Error: , SQLSTATE); END; START TRANSACTION; -- 检查余额 SELECT balance INTO avail_balance FROM accounts WHERE account_id from_account FOR UPDATE; IF avail_balance amount THEN SET status_code -2; SET message Insufficient balance; ROLLBACK; ELSE -- 执行转账 UPDATE accounts SET balance balance - amount WHERE account_id from_account; UPDATE accounts SET balance balance amount WHERE account_id to_account; -- 记录交易 INSERT INTO transactions(from_account, to_account, amount) VALUES (from_account, to_account, amount); COMMIT; SET status_code 0; SET message Transfer successful; END IF; END // DELIMITER ;18.2 触发器应用数据校验触发器DELIMITER // CREATE TRIGGER validate_salary BEFORE INSERT ON employees FOR EACH ROW BEGIN IF NEW.salary 0 THEN SIGNAL SQLSTATE 45000 SET MESSAGE_TEXT Salary cannot be negative; END IF; -- 确保邮箱格式 IF NEW.email NOT REGEXP ^[A-Z0-9._%-][A-Z0-9.-]\\.[A-Z]{2,4}$ THEN SIGNAL SQLSTATE 45000 SET MESSAGE_TEXT Invalid email format; END IF; END // DELIMITER ;19. 分区表关键字19.1 分区策略按范围分区示例CREATE TABLE sales ( id INT AUTO_INCREMENT, sale_date DATE NOT NULL, amount DECIMAL(10,2) NOT NULL, region VARCHAR(50) NOT NULL, PRIMARY KEY (id, sale_date) ) PARTITION BY RANGE (YEAR(sale_date)) ( PARTITION p2020 VALUES LESS THAN (2021), PARTITION p2021 VALUES LESS THAN (2022), PARTITION p2022 VALUES LESS THAN (2023), PARTITION pmax VALUES LESS THAN MAXVALUE );19.2 分区维护分区管理操作-- 添加新分区 ALTER TABLE sales REORGANIZE PARTITION pmax INTO ( PARTITION p2023 VALUES LESS THAN (2024), PARTITION pmax VALUES LESS THAN MAXVALUE ); -- 删除旧分区高效删除大量数据 ALTER TABLE sales DROP PARTITION p2020; -- 重建分区优化 ALTER TABLE sales REBUILD PARTITION p2021, p2022;20. 性能优化进阶20.1 查询重写技巧使用派生表优化-- 原始低效查询 SELECT * FROM orders WHERE customer_id IN ( SELECT customer_id FROM customers WHERE vip_level 3 ); -- 优化后版本 SELECT o.* FROM orders o JOIN ( SELECT customer_id FROM customers WHERE vip_level 3 ) c ON o.customer_id c.customer_id;20.2 索引优化策略覆盖索引示例-- 创建覆盖索引 ALTER TABLE orders ADD INDEX idx_covering (customer_id, status, order_date); -- 查询可以直接使用索引 EXPLAIN SELECT customer_id, status FROM orders WHERE customer_id 1001 AND status shipped;函数索引MySQL 8.0-- 创建函数索引 CREATE INDEX idx_name_lower ON customers ((LOWER(last_name))); -- 查询使用索引 SELECT * FROM customers WHERE LOWER(last_name) smith;
1. MySQL关键字概述MySQL作为最流行的关系型数据库之一其关键字体系构成了SQL语句的骨架。这些保留字在数据库操作中具有特殊含义从基础的SELECT、INSERT到高级的PARTITION BY、WINDOW函数每个关键字都对应着特定的数据操作场景。在实际开发中我经常遇到因为不了解关键字特性而导致的语法错误或性能问题比如误用GROUP BY导致结果集异常或者没有正确使用INDEX提示而引发全表扫描。注意MySQL关键字不区分大小写但按照惯例我们通常用大写形式书写这既是行业规范也能提高SQL语句的可读性。2. 数据操作语言(DML)关键字详解2.1 查询操作核心关键字SELECT作为最基础也最复杂的关键字其完整语法包含12个可能的子句。在电商系统开发中我常用这样的组合SELECT product_id AS pid, product_name, unit_price * 0.9 AS discounted_price FROM products WHERE category_id 5 AND stock_quantity 0 ORDER BY create_time DESC LIMIT 10 OFFSET 0;这里有几个关键点AS关键字用于别名定义在复杂查询中必不可少WHERE子句支持BETWEEN、IN、LIKE等运算符ORDER BY配合DESC/ASC控制排序方向2.2 数据修改关键字INSERT的三种写法各有适用场景-- 标准写法明确列名 INSERT INTO users(username, email) VALUES (john_doe, johnexample.com); -- 批量插入实测性能提升5-8倍 INSERT INTO products(name, price) VALUES (Mouse, 49.9), (Keyboard, 99.9); -- 从查询结果插入数据迁移常用 INSERT INTO user_backup SELECT * FROM users WHERE reg_date 2020-01-01;UPDATE操作要特别注意WHERE条件我在生产环境见过因为没有WHERE导致全表更新的惨剧UPDATE orders SET status completed WHERE order_id 10045 AND status processing;3. 数据定义语言(DDL)关键字3.1 数据库对象操作CREATE TABLE语句的完整语法包含20个可选参数。这是我在金融系统中创建账户表的示例CREATE TABLE accounts ( account_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, user_id INT NOT NULL, balance DECIMAL(15,2) NOT NULL DEFAULT 0.00, status ENUM(active,frozen,closed) NOT NULL DEFAULT active, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (account_id), UNIQUE INDEX idx_user_account (user_id, account_id), CONSTRAINT fk_user FOREIGN KEY (user_id) REFERENCES users(user_id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_unicode_ci;关键元素解析NOT NULL约束比应用层校验更可靠DEFAULT值能简化应用代码复合索引的列顺序影响查询性能3.2 高级表结构修改ALTER TABLE可能是最危险的DDL语句大表操作会导致锁表。我的经验是添加列使用AFTER指定位置修改列类型要测试数据兼容性大数据表使用pt-online-schema-change工具-- 添加新列的正确姿势 ALTER TABLE products ADD COLUMN discount_rate TINYINT UNSIGNED DEFAULT 0 AFTER unit_price;4. 事务控制与锁关键字4.1 事务处理关键字银行转账的经典事务示例START TRANSACTION; UPDATE accounts SET balance balance - 1000 WHERE account_id 1001 AND balance 1000; UPDATE accounts SET balance balance 1000 WHERE account_id 2002; COMMIT; -- 发生错误时执行 ROLLBACK;重要提示InnoDB默认autocommit1开发复杂业务时一定要显式使用事务。4.2 锁相关关键字在高并发票务系统中SELECT ... FOR UPDATE是解决超卖问题的利器BEGIN; SELECT quantity FROM tickets WHERE show_id 101 AND seat_no A12 FOR UPDATE; -- 检查余票后执行更新 UPDATE tickets SET quantity quantity - 1 WHERE show_id 101 AND seat_no A12; COMMIT;LOCK TABLES在MyISAM引擎中仍有使用场景但要注意必须显式UNLOCK TABLES不同会话的锁会相互阻塞死锁检测不如InnoDB完善5. 查询优化关键字5.1 执行计划分析EXPLAIN是我优化慢查询的首选工具。分析这个查询EXPLAIN FORMATJSON SELECT c.customer_name, o.order_total FROM customers c JOIN orders o ON c.customer_id o.customer_id WHERE c.city 杭州 ORDER BY o.order_date DESC LIMIT 100;关键指标解读type列显示ALL表示全表扫描possible_keys显示可用索引rows预估检查的行数Extra列出现Using filesort需要警惕5.2 索引提示关键字当优化器选错索引时FORCE INDEX能派上用场SELECT product_id FROM products FORCE INDEX(idx_category) WHERE category_id 8 AND price BETWEEN 100 AND 500;但要注意数据分布变化后可能失效不同MySQL版本行为可能不同应该先用EXPLAIN验证效果6. 高级特性关键字6.1 窗口函数MySQL 8.0引入的窗口函数极大简化了复杂分析-- 计算每个部门的薪资排名 SELECT employee_name, department, salary, RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank FROM employees;常用窗口函数ROW_NUMBER(): 唯一序号LEAD/LAG(): 访问前后行数据FIRST_VALUE(): 窗口首行值6.2 公用表表达式(CTE)WITH子句使复杂查询更清晰WITH regional_sales AS ( SELECT region, SUM(amount) AS total_sales FROM orders GROUP BY region ), top_regions AS ( SELECT region FROM regional_sales WHERE total_sales 1000000 ) SELECT * FROM top_regions;递归CTE处理树形数据WITH RECURSIVE org_tree AS ( -- 基础查询根节点 SELECT id, name, parent_id, 1 AS level FROM organization WHERE parent_id IS NULL UNION ALL -- 递归查询 SELECT o.id, o.name, o.parent_id, t.level 1 FROM organization o JOIN org_tree t ON o.parent_id t.id ) SELECT * FROM org_tree;7. 关键字使用陷阱与最佳实践7.1 常见错误案例保留字作为标识符-- 错误写法 CREATE TABLE order (id INT); -- 正确写法 CREATE TABLE order (id INT);GROUP BY与HAVING混淆-- 错误在WHERE中使用聚合函数 SELECT department, AVG(salary) FROM employees WHERE AVG(salary) 5000 GROUP BY department; -- 正确使用HAVING过滤分组 SELECT department, AVG(salary) FROM employees GROUP BY department HAVING AVG(salary) 5000;7.2 性能优化建议避免SELECT *只查询需要的列特别是TEXT/BLOB类型合理使用JOIN小表驱动大表确保关联字段有索引考虑使用STRAIGHT_JOIN控制顺序分页优化-- 低效写法 SELECT * FROM large_table LIMIT 1000000, 20; -- 优化方案假设有自增主键 SELECT * FROM large_table WHERE id 1000000 ORDER BY id LIMIT 20;8. 版本特性差异8.1 MySQL 5.7 vs 8.0关键字变化新增关键字WINDOW (窗口函数)PERSIST (变量持久化)SYSTEM (时间函数)行为变更GROUP BY默认启用ONLY_FULL_GROUP_BY模式EXPLAIN新增FORMATTREE选项新增INVISIBLE索引特性8.2 兼容性注意事项保留字列表扩展MySQL 8.0新增50保留字使用反引号包裹可疑标识符存储引擎差异MyISAM不支持事务关键字NDB集群特有关键字9. 实用技巧与工具9.1 关键字查询方法查看全部保留字-- MySQL 8.0 SELECT * FROM information_schema.keywords; -- 所有版本适用 SHOW RESERVED WORDS;检查标识符合法性-- 会返回带反引号的合法名称 SELECT QUOTE_IDENTIFIER(order);9.2 SQL格式化工具命令行工具# 使用mysqlpager美化输出 mysql --pagerless -SFX -e SELECT * FROM users在线格式化sqlformat.orgdpriver.com/sql-formatterIDE插件MySQL Workbench自带格式化VSCode的SQLTools扩展10. 安全相关关键字10.1 权限控制精细授权示例-- 只允许查询特定列 GRANT SELECT(name, email) ON db.users TO reporter%; -- 存储过程权限 GRANT EXECUTE ON PROCEDURE db.cleanup TO operatorlocalhost;10.2 数据加密透明数据加密(TDE)CREATE TABLE payment_records ( id INT PRIMARY KEY, card_number VARBINARY(255) DEFAULT AES_ENCRYPT(, secret_key) ); -- 插入加密数据 INSERT INTO payment_records VALUES (1, AES_ENCRYPT(1234-5678-9012-3456, secret_key)); -- 查询解密 SELECT id, AES_DECRYPT(card_number, secret_key) AS card_number FROM payment_records;11. 监控与诊断关键字11.1 性能分析查看正在执行的SQL-- 完整进程列表 SHOW FULL PROCESSLIST; -- 8.0的性能模式 SELECT * FROM performance_schema.threads WHERE PROCESSLIST_COMMAND IS NOT NULL;11.2 资源监控关键指标查询-- 表空间使用情况 SELECT table_schema, SUM(data_lengthindex_length)/1024/1024 AS total_mb FROM information_schema.tables GROUP BY table_schema; -- 索引使用统计 SELECT * FROM sys.schema_index_statistics WHERE table_schema mydb;12. 备份与恢复关键字12.1 逻辑备份mysqldump常用参数# 完整备份 mysqldump --single-transaction --routines --triggers mydb backup.sql # 只备份结构 mysqldump --no-data mydb schema.sql12.2 物理备份Percona XtraBackup命令# 全量备份 xtrabackup --backup --target-dir/backups/full # 增量备份 xtrabackup --backup --target-dir/backups/inc1 \ --incremental-basedir/backups/full13. 配置优化关键字13.1 参数调整关键配置项-- 临时设置重启失效 SET GLOBAL innodb_buffer_pool_size 2147483648; -- 持久化设置8.0 SET PERSIST max_connections 500;13.2 引擎状态InnoDB监控-- 开启标准监控 SET GLOBAL innodb_status_output ON; -- 获取详细状态 SHOW ENGINE INNODB STATUS\G14. 高可用关键字14.1 主从复制配置命令示例-- 在主库创建复制账号 CREATE USER repl% IDENTIFIED BY password; GRANT REPLICATION SLAVE ON *.* TO repl%; -- 在从库配置连接 CHANGE MASTER TO MASTER_HOSTmaster_host, MASTER_USERrepl, MASTER_PASSWORDpassword, MASTER_AUTO_POSITION1; START SLAVE;14.2 组复制MySQL Group Replication设置-- 初始化组 SET GLOBAL group_replication_bootstrap_groupON; START GROUP_REPLICATION; SET GLOBAL group_replication_bootstrap_groupOFF; -- 新节点加入 START GROUP_REPLICATION USERrepl_user, PASSWORDpassword;15. 云数据库特性15.1 AWS RDS专用命令管理命令示例-- 创建只读副本 CALL mysql.rds_create_read_replica(replica-instance, source-instance, 3306); -- 设置备份保留期 CALL mysql.rds_set_configuration(binlog retention hours, 24);15.2 Azure Database操作管理弹性池-- 将数据库移入弹性池 ALTER DATABASE db1 MODIFY NAME db1, SERVICE_OBJECTIVE ELASTIC_POOL(name my-pool); -- 监控资源使用 SELECT * FROM sys.resource_stats;16. 开发技巧与模式16.1 软删除实现通用模式CREATE TABLE articles ( id INT PRIMARY KEY, title VARCHAR(100), content TEXT, is_deleted TINYINT(1) DEFAULT 0, deleted_at TIMESTAMP NULL ); -- 删除操作变为更新 UPDATE articles SET is_deleted 1, deleted_at NOW() WHERE id 123; -- 查询时过滤 SELECT * FROM articles WHERE is_deleted 0;16.2 审计日志方案使用触发器记录变更CREATE TABLE user_audit ( id INT AUTO_INCREMENT PRIMARY KEY, user_id INT NOT NULL, changed_column VARCHAR(50), old_value TEXT, new_value TEXT, change_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ); CREATE TRIGGER before_user_update BEFORE UPDATE ON users FOR EACH ROW BEGIN IF NEW.email OLD.email THEN INSERT INTO user_audit(user_id, changed_column, old_value, new_value) VALUES (OLD.id, email, OLD.email, NEW.email); END IF; END;17. 数据类型相关关键字17.1 JSON类型操作MySQL 5.7的JSON函数-- 创建JSON列 CREATE TABLE product_specs ( id INT PRIMARY KEY, specs JSON ); -- 插入JSON数据 INSERT INTO product_specs VALUES (1, { dimensions: {width: 10, height: 20}, colors: [red, blue] }); -- 查询JSON属性 SELECT id, specs-$.dimensions.width AS width, JSON_EXTRACT(specs, $.colors[0]) AS primary_color FROM product_specs; -- 更新JSON部分内容 UPDATE product_specs SET specs JSON_SET(specs, $.dimensions.depth, 15) WHERE id 1;17.2 空间数据类型GIS数据处理示例CREATE TABLE locations ( id INT PRIMARY KEY, name VARCHAR(100), position POINT SRID 4326, SPATIAL INDEX(position) ); -- 插入空间数据 INSERT INTO locations VALUES (1, Eiffel Tower, ST_GeomFromText(POINT(2.2945 48.8584))); -- 查找5公里内的点 SELECT name FROM locations WHERE ST_Distance_Sphere(position, ST_GeomFromText(POINT(2.3 48.86))) 5000;18. 存储程序关键字18.1 存储过程带参数的复杂过程DELIMITER // CREATE PROCEDURE transfer_funds( IN from_account BIGINT, IN to_account BIGINT, IN amount DECIMAL(15,2), OUT status_code INT, OUT message VARCHAR(100) ) BEGIN DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN ROLLBACK; SET status_code -1; SET message CONCAT(Error: , SQLSTATE); END; START TRANSACTION; -- 检查余额 SELECT balance INTO avail_balance FROM accounts WHERE account_id from_account FOR UPDATE; IF avail_balance amount THEN SET status_code -2; SET message Insufficient balance; ROLLBACK; ELSE -- 执行转账 UPDATE accounts SET balance balance - amount WHERE account_id from_account; UPDATE accounts SET balance balance amount WHERE account_id to_account; -- 记录交易 INSERT INTO transactions(from_account, to_account, amount) VALUES (from_account, to_account, amount); COMMIT; SET status_code 0; SET message Transfer successful; END IF; END // DELIMITER ;18.2 触发器应用数据校验触发器DELIMITER // CREATE TRIGGER validate_salary BEFORE INSERT ON employees FOR EACH ROW BEGIN IF NEW.salary 0 THEN SIGNAL SQLSTATE 45000 SET MESSAGE_TEXT Salary cannot be negative; END IF; -- 确保邮箱格式 IF NEW.email NOT REGEXP ^[A-Z0-9._%-][A-Z0-9.-]\\.[A-Z]{2,4}$ THEN SIGNAL SQLSTATE 45000 SET MESSAGE_TEXT Invalid email format; END IF; END // DELIMITER ;19. 分区表关键字19.1 分区策略按范围分区示例CREATE TABLE sales ( id INT AUTO_INCREMENT, sale_date DATE NOT NULL, amount DECIMAL(10,2) NOT NULL, region VARCHAR(50) NOT NULL, PRIMARY KEY (id, sale_date) ) PARTITION BY RANGE (YEAR(sale_date)) ( PARTITION p2020 VALUES LESS THAN (2021), PARTITION p2021 VALUES LESS THAN (2022), PARTITION p2022 VALUES LESS THAN (2023), PARTITION pmax VALUES LESS THAN MAXVALUE );19.2 分区维护分区管理操作-- 添加新分区 ALTER TABLE sales REORGANIZE PARTITION pmax INTO ( PARTITION p2023 VALUES LESS THAN (2024), PARTITION pmax VALUES LESS THAN MAXVALUE ); -- 删除旧分区高效删除大量数据 ALTER TABLE sales DROP PARTITION p2020; -- 重建分区优化 ALTER TABLE sales REBUILD PARTITION p2021, p2022;20. 性能优化进阶20.1 查询重写技巧使用派生表优化-- 原始低效查询 SELECT * FROM orders WHERE customer_id IN ( SELECT customer_id FROM customers WHERE vip_level 3 ); -- 优化后版本 SELECT o.* FROM orders o JOIN ( SELECT customer_id FROM customers WHERE vip_level 3 ) c ON o.customer_id c.customer_id;20.2 索引优化策略覆盖索引示例-- 创建覆盖索引 ALTER TABLE orders ADD INDEX idx_covering (customer_id, status, order_date); -- 查询可以直接使用索引 EXPLAIN SELECT customer_id, status FROM orders WHERE customer_id 1001 AND status shipped;函数索引MySQL 8.0-- 创建函数索引 CREATE INDEX idx_name_lower ON customers ((LOWER(last_name))); -- 查询使用索引 SELECT * FROM customers WHERE LOWER(last_name) smith;
