实用sql

-- 查字段是否存在
select count(1) from information_schema.COLUMNS WHERE TABLE_NAME='表名' and COLUMN_NAME='字段';

-- 查表是否存在
select count(1) from information_schema.TABLES WHERE TABLE_NAME='表名';

-- 查索引是否存在
select count(1) from information_schema.STATISTICS WHERE TABLE_NAME='表名' and INDEX_NAME='索引名';

-- 查表的创建日期
select CREATE_TIME from information_schema.TABLES WHERE TABLE_NAME='表名' AND TABLE_SCHEMA='数据库名';

-- 清空quartz表
delete from qrtz_cron_triggers;
delete from qrtz_triggers;
delete from qrtz_job_details;
delete from qrtz_locks;
delete from qrtz_scheduler_state;

-- 杀连接数
show PROCESSLIST;
SELECT concat('kill ',id,';') from information_schema.PROCESSLIST where HOST like 'IP地址%';
-- 杀掉查出的线程ID
kill 32586;
kill 32587;

-- 模拟执行10秒的sql语句
SELECT SLEEP(10);
-- 慢查询定义阈值，单位是秒，精度可到毫秒
SHOW VARIABLES LIKE 'long%';
-- 临时开启慢查询日志
SET GLOBAL slow_query_log = ON;
-- 输出慢查询日志到表
SET GLOBAL log_output='TABLE';
-- 输出慢查询日志到文件
SET GLOBAL log_output='FILE';
-- 查询慢查询日志
SELECT * FROM `slow_log`;

-- 查询最大连接数
SHOW VARIABLES LIKE 'max_user_connections%';
-- 临时设置最大连接数
SET GLOBAL max_connections= 200;