TOC

MySQL 笔记

SQL 流程

graph TD;

client[客户端]
conn[连接处理/权限验证]
parser[解析器]
cache[缓存]
preprocessor[预处理器]
optimizer[优化器]
planner[计划器]
executor[执行器]
store[存储引擎]
fs[文件系统]

client --> conn
conn --> cache
conn --> parser

subgraph SQL解析与优化
parser --> cache
parser --> preprocessor
preprocessor --> optimizer
optimizer --> planner
planner --> executor
end

executor --> store
store --> fs

安全模式

Error Code: 1175. You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column To disable safe mode, toggle the option in Preferences -> SQL Editor and reconnect.

SET sql_safe_updates = 0;
DELETE FROM project_operations WHERE type = 'transfer';
SET sql_safe_updates = 1;

外键检查

Error Code: 1451. Cannot delete or update a parent row: a foreign key constraint fails...

SET foreign_key_checks = 0;
DELETE FROM products where type = 'software';
SET foreign_key_checks = 1;

MySQL 变量

SHOW VARIABLES LIKE 'log_%';
SHOW SESSION VARIABLES;
SHOW GLOBAL VARIABLES;

SELECT * FROM information_schema.global_variables;
SELECT * FROM information_schema.session_variables;

SHOW VARIABLES LIKE 'character_set_results';
SELECT @@character_set_results;

show_compatibility_56

ERROR 3167 (HY000): The 'INFORMATION_SCHEMA.GLOBAL_VARIABLES' feature is disabled; see the documentation for 'show_compatibility_56'

// show_compatibility_56 是 GLOBAL VARIABLE,必须 GLOBAL 修改
// 进程级修改,对整个当前运行的服务器进程有效
SET GLOBAL show_compatibility_56 = 1;