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

查看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')