mysql错误修改数据_使用正则表达式快速修改mysql中错误的varchar类型数据

昨天早上发现日志中有错误信息：Integer到String类型转换异常。

由于程序问题，导致数据库中的varchar列存入的json字符串不符合要求，这些json字符串在反序列化成Map之后就会报错。

现在要修改不合法数据。比如goods表的desc列

//当前格式

{"name":"aaa","age":24,"height":1.73}

{"name":"bbb","age":30,"weight":65.20}

//目标格式

{"name":"aaa","age":"24","height":"1.73"}

{"name":"bbb","age":"30","weight":"65.20"}

1、如何查出有问题的数据？

因为json数据并不规范，直接使用like查询不太现实，主要是分支有点多。

数据的共同问题就是数字没有被引号包围。所以只需要查出冒号:之后直接是数字的数据。

查询MysqL的文档之后，使用如下SQL查询：

select * from goods where desc regexp ':[[:digit:]]+'

select * from goods where desc regexp ':[[:digit:]]+\\,'这个没用

MysqL的正则表达式跟perl、unix风格的有较大的差异。其中[[:digit:]]表示数字。 \\,用来匹配json中的逗号

2、如何修改有问题的数据？

直接用MysqL修改？那就要把符合条件的数字(组)提取出来，粗略查了下文档，没查到，放弃。

用UE(UltraEdit)编辑器修改。UE支持三种正则表达式：Perl、Unix、和UE。

如何查询？以前常用Unix风格的正则表达式查询没问题，UE风格正则请参考文档，有点弱。

如何替换？这才最关键。要替换就得提取匹配的组。如何提取？UE的帮助文档如是说：

^(*^)

在表达式加上括号或标签在替换命令中使用。正则表达式中可以有 9 个表达式标签，数字根据它们在正则表达式中的次序确定数字。

相应的替换表达式是 ^x，x 的范围是 1-9。例如: 如果 ^(h*o^) ^(f*s^) 匹配“hello folks”，那么^2 ^1 表示将用“folks hello”替换它。

也就是说,用^(*^)这货来查找，用^1引用第一个组，^2引用第二个组。 例子里，^2引用了folks，^1引用了hello。

对于我遇到的问题

首先，要匹配数字，最简单的[0-9]，+表示至少1个，++表示0个或多个，[.]匹配小数点。所以匹配整数和小数可以用[0-9]+[.]++[0-9]++。因为后面要提取数字，所以用^([0-9]+[.]++[0-9]++^)

其次，出错的数据都是冒号后面直接带数字。所以表达式为:^([0-9]+[.]++[0-9]++^)。这样就把正确数据和错误数据区分了。

再次，如果要匹配key比如age、weight，用^("[~"]+"^):^([0-9]+[.]++[0-9]++^)。[~"]匹配引号以外的字符。这样的话，想干点什么就方便多了，比如根据json生成表格。

然后，打开查找对话框，选中正则表达式，点开高级，选正则表达式引擎为UltraEdit，查找表达式^("[~"]+"^):^([0-9]+[.]++[0-9]++^)，替换表达式^1 :"^2"，点击替换。 最后，替换完了，使用列编辑模式生成udpate语句，带上where条件和结束符；最后交给客户端批量执行。 几百条数据有问题，如果自己手工改，肯定早就改完了。不过掌握此种方法，以后类似的问题就容易多了。

总结

如果觉得编程之家网站内容还不错，欢迎将编程之家网站推荐给程序员好友。

本图文内容来源于网友网络收集整理提供，作为学习参考使用，版权属于原作者。

如您喜欢交流学习经验，点击链接加入交流1群：1065694478(已满)交流2群：163560250