MySQL 笔记

安全模式

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;