MySQL常用SQL收集

[TOC]

Mysql 字段替换

update article set Content=replace(Content,’blog.weiyigeek.top’,’www.weiyigeek.top') WHERE ArticleID in (‘1564’,’1616’,’2945’)

update bugs set wybug_detail=replace(wybug_detail,’static.loner.fm’,’study.weiyigeek.top’);

WHERE ArticleID in (‘1564’,’1616’,’2945’)

https://www.cnblogs.com/wanglijun/p/8883875.html

https://www.cnblogs.com/mianbaoshu/p/11821112.html

Mysql 正则匹配

SET GLOBAL regexp_time_limit=1024000;
SELECT id,content FROM web.`js_cms_article_data` WHERE content LIKE '%关键字%' LIMIT 10;
-- URL
SELECT id,text FROM web.`js_cms_article_data` WHERE content REGEXP '([\da-z-]+.)\\.([a-z]{2,6})' LIMIT 100;
-- Email
SELECT id,text FROM web.`js_cms_article_data` WHERE content REGEXP '@([\da-z-]+.)\\.([a-z]{2,6})' LIMIT 100;
-- IP
SELECT id,content FROM web.`js_cms_article_data` WHERE content REGEXP '[1-9]{1,3}(\\.[[:digit:]]{1,3}){3}' LIMIT 100;

手机号、身份证号 脱敏

SELECT
  common_phone,
  INSERT ( common_phone, 4, 4, '****' ) 
FROM
  ctm_customer_file 
  LIMIT 1,10;


SELECT ReplyID,IdeaID,IsAvow FROM idea_reply	WHERE IdeaID IN (SELECT IdeaID from site.idea WHERE Content REGEXP '[0-9]{18}')

https://blog.csdn.net/weixin_44325655/article/details/119209246?spm=1001.2101.3001.6650.5&utm_medium=distribute.pc_relevant.none-task-blog-2%7Edefault%7EBlogCommendFromBaidu%7Edefault-5-119209246-blog-121381885.pc_relevant_multi_platform_whitelistv3&depth_1-utm_source=distribute.pc_relevant.none-task-blog-2%7Edefault%7EBlogCommendFromBaidu%7Edefault-5-119209246-blog-121381885.pc_relevant_multi_platform_whitelistv3&utm_relevant_index=10

查看mysql数据库大小

1、进入information_schema 数据库（存放了其他的数据库的信息）

use information_schema; #一定要先进这个库，才能查询信息 2、查询所有数据的大小：

select concat(round(sum(data_length/1024/1024),2),’MB’) as data from tables; 3、查看指定数据库的大小： 比如查看数据库test的大小

select concat(round(sum(data_length/1024/1024),2),’MB’) as data from tables where table_schema=’test’; 4、查看指定数据库的某个表的大小 比如查看数据库test中 members 表的大小

select concat(round(sum(data_length/1024/1024),2),’MB’) as data from tables where table_schema=’test’ and table_name=’members’; 例子： mysql> use information_schema; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A

Database changed mysql> select concat(round(sum(data_length/1024/1024),2),’MB’) as data from tables; +———–+ | data | +———–+ | 658247MB | +———–+ 1 row in set, 1 warning (2.93 sec)

其他混杂存储过程 | 全方位认识 sys 系统库 转载 , 表别名 https://dev.mysql.com/doc/refman/5.7/en/sys-create-synonym-db.html

https://blog.51cto.com/imysql/3169556

# 3-23 12:40:53
call create_synonym_db('xxx', 'cms')

# 3-23 12:41:42
call create_synonym_db('new db, 'code db')