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

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

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

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

//当前格式
{"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"}

[size=large]1、如何查出有问题的数据？[/size]

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

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

查询mysql的文档之后，使用如下sql查询：[/size]

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

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

这个没用

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

[size=large]2、如何修改有问题的数据？[/size]

[size=medium]直接用mysql修改？那就要把符合条件的数字（组）提取出来，粗略查了下文档，没查到，放弃。

用UE（UltraEdit）编辑器修改。UE支持三种正则表达式：Perl、Unix、和UE。
如何查询？以前常用Unix风格的正则表达式查询没问题，UE风格正则请参考文档，有点弱。
如何替换？这才最关键。要替换就得提取匹配的组。如何提取？UE的帮助文档如是说：

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

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

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


对于我遇到的问题

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

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

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

然后，打开查找对话框，选中正则表达式，点开高级，选正则表达式引擎为UltraEdit，查找表达式[color=red]^("[~"]+"^):^([0-9]+[.]++[0-9]++^)[/color]，替换表达式[color=red]^1 :"^2"[/color]，点击替换。

最后，替换完了，使用列编辑模式生成udpate语句，带上where条件和结束符；最后交给客户端批量执行。


几百条数据有问题，如果自己手工改，肯定早就改完了。不过掌握此种方法，以后类似的问题就容易多了。[/size]