个人常用sql

查询表创建时间、

SELECT table_name,create_time FROM information_schema.TABLES WHERE table_name = 'table_name';

批量kill 事务id

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='test';

查询逗号分割的字符串包含某个字

 alter table `test` add column stra varchar(200);
 INSERT INTO `test`.`test` (`cid`, `flag`, `startdate`, `stra`) VALUES ('1', '1', '1999-01-01', '3,4,55,23');
 select * From test where FIND_IN_SET(3,stra);
 

查询事务

select * from information_schema.innodb_trx ;  
select b.id,b.user,b.host,b.db,b.info,a.trx_started,a.trx_query,a.trx_state,a.trx_isolation_level from information_schema.innodb_trx a ,information_schema.processlist b where a.trx_mysql_thread_id = b.id ;

查看数据库大小,表大小

#1，查看数据库使用大小
select concat(round(sum(data_length/1024/1024),2),'MB') as data from tables where table_schema='DB_Name';
#2，查看表使用大小
select concat(round(sum(data_length/1024/1024),2),'MB') as data from tables where table_schema='apollo' and table_name='abnormalitem_tb_live_bak';

查询json

SELECT JSON_UNQUOTE(comment->"$.key") AS `item_id` FROM `question` `a`

更新json

UPDATE question SET comment = json_replace(comment, '$.key', ceil(RAND() *10000)+ceil(RAND() *10000) ) 

mysqlworkbench安全模式禁止update

 SET SQL_SAFE_UPDATES = 0; DELETE FROM TABLE_E;   SET SQL_SAFE_UPDATES = 1;