mysql

show Variables like '%table_names'
show full columns from table_name
show table status

default-character-set=utf8
lower_case_table_names=2

SHOW GLOBAL VARIABLES LIKE 'group_concat_max_len';
SET GLOBAL group_concat_max_len=1048576;

[mysqld]
group_concat_max_len=1048576

SET @i=0;
SELECT
    Id,
    (@i:=@i+1) sn
FROM Microcampus_T_Activity
LIMIT 1,50


SET GLOBAL max_allowed_packet=524288000


    SELECT CONCAT('KILL ',id,';')  AS command
    FROM information_schema.processlist  
    WHERE TIME>3600 AND `user`='root'

SHOW ENGINE INNODB STATUS;

ALTER TABLE tab01 AUTO_INCREMENT=10020030;

INSERT INTO tab01 () VALUES();
SELECT @@IDENTITY;

/* 删除语句的真义 */
DELETE tu FROM ThirdUser tu, MyThirdUser t
WHERE tu.OpenId=t.OpenId;


# 慢查询
SET GLOBAL slow_query_log=ON;
SET GLOBAL slow_launch_time=5;
SET GLOBAL long_query_time=1; # 这个才有用
SHOW VARIABLES LIKE "%slow%";

 

表信息

SELECT
    a.*
FROM INFORMATION_SCHEMA.COLUMNS a
WHERE
	a.TABLE_SCHEMA = 'DatabaseName' 
	AND a.TABLE_NAME = 'TableName';

SELECT * 
FROM information_schema.tables 
WHERE table_schema ='DatabaseName'

 

锁表

SELECT 
    INNODB_TRX.trx_mysql_thread_id, 
    INNODB_TRX.trx_started,
    INNODB_LOCKS.* 
FROM INFORMATION_SCHEMA.INNODB_LOCKS
LEFT JOIN information_schema.INNODB_TRX ON INNODB_LOCKS.lock_trx_id=INNODB_TRX.trx_id
; 
KILL 72056;


# 查未提交的事务
SELECT t.* FROM information_schema.innodb_trx t

受影响行数

FOUND_ROWS() --  select 查询擦操作
ROW_COUNT() -- update delete insert.  增删改操作