首页 > 数据库 > MySQL

个人常用sql

来源：SegmentFault

时间：2023-02-24 20:41:59 282浏览收藏

知识点掌握了，还需要不断练习才能熟练运用。下面golang学习网给大家带来一个数据库开发实战，手把手教大家学习《个人常用sql》，在实现功能的过程中也带大家重新温习相关知识点，温故而知新，回头看看说不定又有不一样的感悟！

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 ;

查询表创建时间、

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;

终于介绍完啦！小伙伴们，这篇关于《个人常用sql》的介绍应该让你收获多多了吧！欢迎大家收藏或分享给更多需要学习的朋友吧~golang学习网公众号也会发布数据库相关知识，快来关注吧！

mysql MySQL

声明：本文转载于：SegmentFault 如有侵犯，请联系study_golang@163.com删除