个人常用sql

 CREATE TABLE `test` (
   `cid` int(20) NOT NULL,
   `flag` tinyint(2) NOT NULL,
   `startdate` date NOT NULL,
   PRIMARY KEY (`cid`,`flag`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8

1. 给现有表加自增主键

   alter table `test` add column id int(10) first ;
   ALTER TABLE `test` DROP PRIMARY KEY ,ADD PRIMARY KEY ( `id` );
   alter table `test` modify column id int(10) auto_increment ;

2. 查询表创建时间、

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

3. 批量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';

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

    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);
    

5. 查询事务

   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 ;

6. 查看数据库大小,表大小

   #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';

7. 查询json

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

8. 更新json

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

9. mysqlworkbench安全模式禁止update

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

11. .
12. .
13. .
14. .
15. .
16. .