创建：

DELIMITER ^^
CREATE TRIGGER t_user_asset_watch
	BEFORE UPDATE ON users
	FOR EACH ROW 
BEGIN
        IF (OLD.balance!=NEW.balance or OLD.balance_usdt!=NEW.balance_usdt 
            or OLD.frozen!=NEW.frozen or OLD.frozen_usdt!=NEW.frozen_usdt) THEN
            INSERT INTO user_asset_watch(user_id,balance_old,balance_new,frozen_old,frozen_new) 
            VALUES(OLD.id,OLD.balance,NEW.balance,OLD.frozen,NEW.frozen);
        END IF^^
END^^
DELIMITER ;

删除：

DROP TRIGGER IF EXISTS t_user_asset_watch;

• 查看当前进程列表： show processlist;

• 当前运行的事务： SELECT * FROM information_schema.INNODB_TRX;

• 当前的锁： SELECT * FROM information_schema.INNODB_LOCKs;

• 锁的对应关系： SELECT * FROM information_schema.INNODB_LOCK_waits;

• 批量终止事务：

  select concat('KILL ',id,';') from information_schema.processlist p inner
  join information_schema.INNODB_TRX x on p.id=x.trx_mysql_thread_id where db='DB_NAME';
  +------------------------+
  | concat('KILL ',id,';') |
  +------------------------+
  ————————————————
  

在阿里云 mysql -uxx -hxxx -p dbname < file.sql 恢复文件时由于 RDS 没有 Supper 权限所以报错：

Access denied; you need (at least one of) the SUPER privilege(s) for this operation

解决方法： 去除 GTID_PURGED 和 DEFINER 子句：

sed -e 's/DEFINER[ ]*=[ ]*[^*]*\*/\*/ ' your.sql > your_revised.sql
awk '{ if (index($0,"GTID_PURGED")) { getline; while (length($0) > 0) { getline; } } else { print $0 } }' your.sql | grep -iv 'set @@' > your_revised.sql

详见： https://help.aliyun.com/knowledge_detail/41701.html

MYSQL 使用 FOUND_ROWS 获取 SQL_CALC_FOUND_ROWS语句中符合条件的记录总数：

SELECT SQL_CALC_FOUND_ROWS `id`,`title`... FROM ... WHERE ...  ORDER BY... LIMIT m,n;

select FOUND_ROWS();

注意：在数据量较大的情况下 SELECT SQL_CALC_FOUND_ROWS 的开销会比较大。