一般新的项目上线或域名必须要更改的时候,有些数据库存的图片或者文件地址带域名的要全部改,恰巧呢,数据库表超级多,恰巧呢,又刚做了接盘侠,啥也不知道,就给你连接数据库让你改,头大不头大
我这个接盘侠上任第一天就遇上了这问题,当拿到将近1G的数据的时候,我的想法,挨个查找关键字,记录出现的表和有关键字的字段写到excel,然后写程序读取excel再拼sql执行替换,嗯,然后整了三个小时,整理出来20个表和出现过关键词的字段,此时那1G的数据,我只看了几十K,当我以为我进公司前半个月都要干这苦力活的时候,救星来了。
公司一位php前辈来说可能有好的办法,可以先sql查询出现过的表和字段,这个确实是个好办法,但是当时想哪有sql可以这么查,经过一番百度,结果,真没找到sql可以这么查!
哈哈哈 你以为这就完了?不!程序员永远不能说不!
我发现有可以查询表的列名称关键词和备注及索引之类的关键词,那就换个思路,导出的表可以看出表的列备注还是很详细的,命名也不错,那就用这种办法了,先上SQL(关键词不懂得自行百度)
SELECT DISTINCT TABLE_NAME,COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_COMMENT LIKE '%图%'
OR COLUMN_COMMENT LIKE '%背景%'
OR COLUMN_COMMENT LIKE '%头像%'
OR COLUMN_COMMENT LIKE '%logo%'
OR COLUMN_name LIKE '%img%'
AND TABLE_SCHEMA='gyrtest';
已经测试过,现在数据库是600左右张表,数据量我觉得是很多了,但是这个sql不会跟数据库的具体数据挂钩,执行在1s以内,不会影响数据库服务器的其他操作,可以放心
然后就是找到所有的数据库表和名称以后的替换,我写了一个文件示例,事实上,我就是用这个文件执行的修改,这种方法不会百分百改掉所有,但是对于太多数据没法一个一个改的,先改百分之八九十也行,其他的一些没改掉的后续发现了再单独写sql也是可以的,上完整代码,测试的只需要改一下数据库ip user pwd db即可
<?php
/**
* 数据库全局替换域名(原理:根据数据库字段名及字段备注模糊匹配)
* @author [Geyaru] {QQ:534208139} 2019-05-22
*/
$ip = ''; //数据库ip
$user = ''; //用户名
$pwd = ''; //密码
$db = ''; //数据库名
$port = '3306'; //端口
$connect=mysqli_connect($ip,$user,$pwd,$db,$port);
$sql="SELECT DISTINCT TABLE_NAME,COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_COMMENT LIKE '%图%'
OR COLUMN_COMMENT LIKE '%背景%'
OR COLUMN_COMMENT LIKE '%头像%'
OR COLUMN_COMMENT LIKE '%logo%'
OR COLUMN_COMMENT LIKE '%照片%'
OR COLUMN_COMMENT LIKE '%国徽%'
OR COLUMN_COMMENT LIKE '%链接%'
OR COLUMN_COMMENT LIKE '%网址%'
OR COLUMN_COMMENT LIKE '%logo%'
OR COLUMN_COMMENT LIKE '%身份证%'
OR COLUMN_COMMENT LIKE '%域名%'
OR COLUMN_COMMENT LIKE '%文件%'
OR COLUMN_COMMENT LIKE '%样例%'
OR COLUMN_COMMENT LIKE '%位置%'
OR COLUMN_name LIKE '%img%'
OR COLUMN_name LIKE '%path%'
OR COLUMN_name LIKE '%url%'
OR COLUMN_name LIKE '%photo%'
OR COLUMN_name LIKE '%link%'
OR COLUMN_name LIKE '%href%'
OR COLUMN_name LIKE '%picture%'
OR COLUMN_name LIKE '%diagram%'
AND TABLE_SCHEMA='".$db."'
";
mysqli_query($connect,'set names utf8');
$result=mysqli_query($connect,$sql);
$res='';//定义字符串存储拼接sql
$y_old='a.com.cn'; //旧域名
$y_new='b.cn'; //新域名
$i = 0;
while($row =mysqli_fetch_array($result)){
$table = $row[0]; //表
$columns = $row[1]; //字段
$str_sql = "UPDATE %s SET %s= replace(%s, '%s', '%s'); ";
$mes = sprintf($str_sql,$table,$columns,$columns,$y_old,$y_new);
$res = $res."\n".$mes;
$i++;
}
echo 'sql总数:'.$i.$res;
?>
注意,我写的这个程序没有直接在代码写update的执行,是将拼好的sql拿到navicate执行,其实我就是懒得写了。
这个拼接的sql在我这600张表,占1G的数据库中总共执行了六百条sql,跑完花了一分钟左右,修改了二十多万行数据,好了就这些了,有好的办法联系博主哟~感谢
联系方式: QQ534208139