使用正则表达式快速修改mysql中错误的varchar类型数据
昨天早上发现日志中有错误信息：Integer到String类型转换异常。由于程序问题，导致数据库中的varchar列存入的json字符串不符合要求，这些json字符串在反序列化成Map<String,String>之后就会报错。
现在要修改不合法数据。比如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。
对于我遇到的问题
首先，要匹配数字，最简单的，+表示至少1个，++表示0个或多个，[.]匹配小数点。所以匹配整数和小数可以用+[.]++++。因为后面要提取数字，所以用^(+[.]++++^)
其次，出错的数据都是冒号后面直接带数字。所以表达式为:^(+[.]++++^)。这样就把正确数据和错误数据区分了。
再次，如果要匹配key比如age、weight，用^("[~"]+"^):^(+[.]++++^)。[~"]匹配引号以外的字符。这样的话，想干点什么就方便多了，比如根据json生成表格。
然后，打开查找对话框，选中正则表达式，点开高级，选正则表达式引擎为UltraEdit，查找表达式^("[~"]+"^):^(+[.]++++^)，替换表达式^1 :"^2"，点击替换。
最后，替换完了，使用列编辑模式生成udpate语句，带上where条件和结束符；最后交给客户端批量执行。
几百条数据有问题，如果自己手工改，肯定早就改完了。不过掌握此种方法，以后类似的问题就容易多了。
页:
[1]