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;