由于程序问题,导致数据库中的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。
对于我遇到的问题
首先,要匹配数字,最简单的[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条件和结束符;最后交给客户端批量执行。 几百条数据有问题,如果自己手工改,肯定早就改完了。不过掌握此种方法,以后类似的问题就容易多了。