Sql Snippets

偏 MySQL 的 SQL 速查表，适合排障与临时查询。

进程与连接

查看当前连接

SHOW FULL PROCESSLIST;

结束某个连接

KILL <process_id>;

Binlog

检查是否开启

SHOW VARIABLES LIKE 'log_bin';

表清单

查看所有表

SHOW TABLES;

模糊过滤

SHOW TABLES LIKE 'p%';

SHOW TABLES WHERE Tables_in_testdb LIKE '%es';

权限与用户

MySQL 8.x 建议先创建用户，再授权。

CREATE USER 'root'@'%' IDENTIFIED BY 'root';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%';
FLUSH PRIVILEGES;

行锁（事务）

START TRANSACTION;
SELECT * FROM x_tab WHERE id = 2 FOR UPDATE;
UPDATE x_tab SET amount = amount + 5 WHERE id = 2;
COMMIT;

提示：FOR UPDATE 只锁住命中的行（InnoDB）。

重命名表

RENAME TABLE old_db.table TO new_db.table;

批量生成重命名 SQL

mysql -u username -ppassword old_db -sNe 'SHOW TABLES' | while read -r table; do
  mysql -u username -ppassword -sNe "RENAME TABLE old_db.$table TO new_db.$table";
done

查询库信息

统计库内表数量

SELECT table_schema, COUNT(1)
FROM information_schema.tables
WHERE table_schema LIKE 'keyword%'
GROUP BY table_schema;

视图

CREATE VIEW your_view_tab AS
SELECT * FROM your_tab;