你的位置:首页 > 数据库

[数据库]SqlServer 2005升级至SqlServer 2008 解析Json 字符集问题


如果你数据库是通过sqlserver 2008以上版本创建的请绕过:

客户以前用的是sqlserver2005 创建的数据库。后来升级到 sqlserver 2008 . 有个业务用到了json查询 .可搜索了一下发现 sqlserver(sqlserver2016除外) 不支持json查询. 在网上搜了一番找到了一个解决方案:

IF OBJECT_ID (N'dbo.parseJSON') IS NOT NULL  DROP FUNCTION dbo.parseJSONGOIF EXISTS (SELECT * FROM sys.types WHERE name LIKE 'Hierarchy') DROP TYPE dbo.HierarchygoCREATE TYPE dbo.Hierarchy AS TABLE/*Markup languages such as JSON and */(  element_id INT primary key, /* internal surrogate primary key gives the order of parsing and the list order */  sequenceNo [int] NULL, /* the place in the sequence for the element */  parent_ID INT,/* if the element has a parent then it is in this column. The document is the ultimate parent, so you can get the structure from recursing from the document */  Object_ID INT,/* each list or object has an object id. This ties all elements to a parent. Lists are treated as objects here */  NAME NVARCHAR(2000),/* the name of the object, null if it hasn't got one */  StringValue NVARCHAR(MAX) NOT NULL,/*the string representation of the value of the element. */  ValueType VARCHAR(10) NOT null /* the declared type of the value represented as a string in StringValue*/)goCREATE FUNCTION dbo.parseJSON( @JSON NVARCHAR(MAX))RETURNS @hierarchy TABLE (  element_id INT IDENTITY(1, 1) NOT NULL, /* internal surrogate primary key gives the order of parsing and the list order */  sequenceNo [int] NULL, /* the place in the sequence for the element */  parent_ID INT,/* if the element has a parent then it is in this column. The document is the ultimate parent, so you can get the structure from recursing from the document */  Object_ID INT,/* each list or object has an object id. This ties all elements to a parent. Lists are treated as objects here */  NAME NVARCHAR(2000),/* the name of the object */  StringValue NVARCHAR(MAX) NOT NULL,/*the string representation of the value of the element. */  ValueType VARCHAR(10) NOT null /* the declared type of the value represented as a string in StringValue*/ )ASBEGIN DECLARE  @FirstObject INT, --the index of the first open bracket found in the JSON string  @OpenDelimiter INT,--the index of the next open bracket found in the JSON string  @NextOpenDelimiter INT,--the index of subsequent open bracket found in the JSON string  @NextCloseDelimiter INT,--the index of subsequent close bracket found in the JSON string  @Type NVARCHAR(10),--whether it denotes an object or an array  @NextCloseDelimiterChar CHAR(1),--either a '}' or a ']'  @Contents NVARCHAR(MAX), --the unparsed contents of the bracketed expression  @Start INT, --index of the start of the token that you are parsing  @end INT,--index of the end of the token that you are parsing  @param INT,--the parameter at the end of the next Object/Array token  @EndOfName INT,--the index of the start of the parameter at end of Object/Array token  @token NVARCHAR(200),--either a string or object  @value NVARCHAR(MAX), -- the value as a string  @SequenceNo int, -- the sequence number within a list  @name NVARCHAR(200), --the name as a string  @parent_ID INT,--the next parent ID to allocate  @lenJSON INT,--the current length of the JSON String  @characters NCHAR(36),--used to convert hex to decimal  @result BIGINT,--the value of the hex symbol being parsed  @index SMALLINT,--used for parsing the hex value  @Escape INT --the index of the next escape character   DECLARE @Strings TABLE /* in this temporary table we keep all strings, even the names of the elements, since they are 'escaped' in a different way, and may contain, unescaped, brackets denoting objects or lists. These are replaced in the JSON string by tokens representing the string */  (   String_ID INT IDENTITY(1, 1),   StringValue NVARCHAR(MAX)  ) SELECT--initialise the characters to convert hex to ascii  @characters='0123456789abcdefghijklmnopqrstuvwxyz',  @SequenceNo=0, --set the sequence no. to something sensible. /* firstly we process all strings. This is done because [{} and ] aren't escaped in strings, which complicates an iterative parse. */  @parent_ID=0; WHILE 1=1 --forever until there is nothing more to do  BEGIN   SELECT    @start=PATINDEX('%[^a-zA-Z]["]%', @json collate SQL_Latin1_General_CP850_Bin);--next delimited string   IF @start=0 BREAK --no more so drop through the WHILE loop   IF SUBSTRING(@json, @start+1, 1)='"'     BEGIN --Delimited Name     SET @start=@Start+1;     SET @end=PATINDEX('%[^\]["]%', RIGHT(@json, LEN(@json+'|')-@start) collate SQL_Latin1_General_CP850_Bin);    END   IF @end=0 --no end delimiter to last string    BREAK --no more   SELECT @token=SUBSTRING(@json, @start+1, @end-1)   --now put in the escaped control characters   SELECT @token=REPLACE(@token, FROMString, TOString)   FROM    (SELECT     '\"' AS FromString, '"' AS ToString     UNION ALL SELECT '\\', '\'     UNION ALL SELECT '\/', '/'     UNION ALL SELECT '\b', CHAR(08)     UNION ALL SELECT '\f', CHAR(12)     UNION ALL SELECT '\n', CHAR(10)     UNION ALL SELECT '\r', CHAR(13)     UNION ALL SELECT '\t', CHAR(09)    ) substitutions   SELECT @result=0, @escape=1 --Begin to take out any hex escape codes   WHILE @escape>0    BEGIN     SELECT @index=0,     --find the next hex escape sequence     @escape=PATINDEX('%\x[0-9a-f][0-9a-f][0-9a-f][0-9a-f]%', @token collate SQL_Latin1_General_CP850_Bin)     IF @escape>0 --if there is one      BEGIN       WHILE @index<4 --there are always four digits to a \x sequence          BEGIN          SELECT --determine its value          @result=@result+POWER(16, @index)          *(CHARINDEX(SUBSTRING(@token, @escape+2+3-@index, 1),                @characters)-1), @index=@index+1 ;             END        -- and replace the hex sequence by its unicode value       SELECT @token=STUFF(@token, @escape, 6, NCHAR(@result))      END    END   --now store the string away    INSERT INTO @Strings (StringValue) SELECT @token   -- and replace the string with a token   SELECT @JSON=STUFF(@json, @start, @end+1,          '@string'+CONVERT(NVARCHAR(5), @@identity))  END -- all strings are now removed. Now we find the first leaf.  WHILE 1=1 --forever until there is nothing more to do BEGIN SELECT @parent_ID=@parent_ID+1 --find the first object or list by looking for the open bracket SELECT @FirstObject=PATINDEX('%[{[[]%', @json collate SQL_Latin1_General_CP850_Bin)--object or array IF @FirstObject = 0 BREAK IF (SUBSTRING(@json, @FirstObject, 1)='{')   SELECT @NextCloseDelimiterChar='}', @type='object' ELSE   SELECT @NextCloseDelimiterChar=']', @type='array' SELECT @OpenDelimiter=@firstObject WHILE 1=1 --find the innermost object or list...  BEGIN   SELECT    @lenJSON=LEN(@JSON+'|')-1 --find the matching close-delimiter proceeding after the open-delimiter   SELECT    @NextCloseDelimiter=CHARINDEX(@NextCloseDelimiterChar, @json,                   @OpenDelimiter+1) --is there an intervening open-delimiter of either type   SELECT @NextOpenDelimiter=PATINDEX('%[{[[]%',       RIGHT(@json, @lenJSON-@OpenDelimiter)collate SQL_Latin1_General_CP850_Bin)--object   IF @NextOpenDelimiter=0     BREAK   SELECT @NextOpenDelimiter=@NextOpenDelimiter+@OpenDelimiter   IF @NextCloseDelimiter<@NextOpenDelimiter     BREAK   IF SUBSTRING(@json, @NextOpenDelimiter, 1)='{'     SELECT @NextCloseDelimiterChar='}', @type='object'   ELSE     SELECT @NextCloseDelimiterChar=']', @type='array'   SELECT @OpenDelimiter=@NextOpenDelimiter  END ---and parse out the list or name/value pairs SELECT  @contents=SUBSTRING(@json, @OpenDelimiter+1,            @NextCloseDelimiter-@OpenDelimiter-1) SELECT  @JSON=STUFF(@json, @OpenDelimiter,        @NextCloseDelimiter-@OpenDelimiter+1,        '@'+@type+CONVERT(NVARCHAR(5), @parent_ID)) WHILE (PATINDEX('%[A-Za-z0-9@+.e]%', @contents collate SQL_Latin1_General_CP850_Bin))<>0   BEGIN   IF @Type='Object' --it will be a 0-n list containing a string followed by a string, number,boolean, or null    BEGIN     SELECT      @SequenceNo=0,@end=CHARINDEX(':', ' '+@contents)--if there is anything, it will be a string-based name.     SELECT @start=PATINDEX('%[^A-Za-z@][@]%', ' '+@contents collate SQL_Latin1_General_CP850_Bin)--AAAAAAAA     SELECT @token=SUBSTRING(' '+@contents, @start+1, @End-@Start-1),      @endofname=PATINDEX('%[0-9]%', @token collate SQL_Latin1_General_CP850_Bin),      @param=RIGHT(@token, LEN(@token)-@endofname+1)     SELECT      @token=LEFT(@token, @endofname-1),      @Contents=RIGHT(' '+@contents, LEN(' '+@contents+'|')-@end-1)     SELECT @name=stringvalue FROM @strings      WHERE string_id=@param --fetch the name    END   ELSE     SELECT @Name=null,@SequenceNo=@SequenceNo+1    SELECT    @end=CHARINDEX(',', @contents)-- a string-token, object-token, list-token, number,boolean, or null   IF @end=0     SELECT @end=PATINDEX('%[A-Za-z0-9@+.e][^A-Za-z0-9@+.e]%', @Contents+' ' collate SQL_Latin1_General_CP850_Bin)     +1    SELECT    @start=PATINDEX('%[^A-Za-z0-9@+.e][A-Za-z0-9@+.e]%', ' '+@contents collate SQL_Latin1_General_CP850_Bin)   --select @start,@end, LEN(@contents+'|'), @contents    SELECT    @Value=RTRIM(SUBSTRING(@contents, @start, @End-@Start)),    @Contents=RIGHT(@contents+' ', LEN(@contents+'|')-@end)   IF SUBSTRING(@value, 1, 7)='@object'     INSERT INTO @hierarchy     (NAME, SequenceNo, parent_ID, StringValue, Object_ID, ValueType)     SELECT @name, @SequenceNo, @parent_ID, SUBSTRING(@value, 8, 5),      SUBSTRING(@value, 8, 5), 'object'    ELSE     IF SUBSTRING(@value, 1, 6)='@array'      INSERT INTO @hierarchy      (NAME, SequenceNo, parent_ID, StringValue, Object_ID, ValueType)      SELECT @name, @SequenceNo, @parent_ID, SUBSTRING(@value, 7, 5),       SUBSTRING(@value, 7, 5), 'array'     ELSE      IF SUBSTRING(@value, 1, 7)='@string'       INSERT INTO @hierarchy       (NAME, SequenceNo, parent_ID, StringValue, ValueType)       SELECT @name, @SequenceNo, @parent_ID, stringvalue, 'string'       FROM @strings       WHERE string_id=SUBSTRING(@value, 8, 5)     ELSE       IF @value IN ('true', 'false')        INSERT INTO @hierarchy        (NAME, SequenceNo, parent_ID, StringValue, ValueType)        SELECT @name, @SequenceNo, @parent_ID, @value, 'boolean'      ELSE        IF @value='null'         INSERT INTO @hierarchy         (NAME, SequenceNo, parent_ID, StringValue, ValueType)         SELECT @name, @SequenceNo, @parent_ID, @value, 'null'       ELSE         IF PATINDEX('%[^0-9]%', @value collate SQL_Latin1_General_CP850_Bin)>0          INSERT INTO @hierarchy          (NAME, SequenceNo, parent_ID, StringValue, ValueType)          SELECT @name, @SequenceNo, @parent_ID, @value, 'real'        ELSE          INSERT INTO @hierarchy          (NAME, SequenceNo, parent_ID, StringValue, ValueType)          SELECT @name, @SequenceNo, @parent_ID, @value, 'int'   if @Contents=' ' Select @SequenceNo=0  END ENDINSERT INTO @hierarchy (NAME, SequenceNo, parent_ID, StringValue, Object_ID, ValueType) SELECT '-',1, NULL, '', @parent_id-1, @type--  RETURNENDGO

执行后发现他执行失败,抛出异常:

Msg 207, Level 16, State 1, Procedure parseJSON, Line 65列名 'FROMString' 无效。Msg 207, Level 16, State 1, Procedure parseJSON, Line 65列名 'TOString' 无效。

如果你把 FROMString -> FromString , TOString -> ToString 虽然执行通过,但你也别得意了.问题还在后面

   SELECT @token=REPLACE(@token, FromString, ToString)

Msg 245, Level 16, State 1, Line 1在将 nvarchar 值 '1:@st' 转换成数据类型 int 时失败。

看到这个错误心都凉了半截~~那一坨代码你让我怎么找! 后来建了个新的数据库(sqlserver2008)来测排查是否语法错误还是数据库兼容问题! 结果是语法测试通过查询正常. 当时就骂了一句:F**K 后来找啊找啊找~都没发现什么问题.

后来别人提醒两个数据库配置对比一下.找到问题了罪魁祸首字符集

Sqlserver2005 的字符集是:Chinese_Taiwan_Stroke_CS_AS

Sqlserver2008r2 的字符集是:Chinese_Taiwan_Stroke_CI_AS

嗯,问题找到就好办了。将sqlserver2005数据库挂到sqlserver2008r2中,修改数据库字符集就好了

alter database TestDB2 collate Chinese_Taiwan_Stroke_CI_AS

注意 修改“字符集”也会有异常

1、

Msg 5030, Level 16, State 2, Line 1无法用排他锁锁定该数据库,以执行该操作。Msg 5072, Level 16, State 1, Line 1ALTER DATABASE 失败。无法将数据库 'XXXXXX' 的默认排序规则设置为 Chinese_Taiwan_Stroke_CI_AS。

解决办法:重启服务 或 修改数据库访问限制为单用户模式.

2、

Msg 5075, Level 16, State 1, Line 1对象 'XXXXXX' 依赖于 数据库排序规则。如果绑定到架构的对象依赖于数据库排序规则,则无法更改数据库排序规则。请删除数据库排序规则的依赖项,然后重试操作。Msg 5072, Level 16, State 1, Line 1
....
ALTER DATABASE 失败。无法将数据库 'TestDB2' 的默认排序规则设置为 Chinese_Taiwan_Stroke_CI_AS。

 解决办法:把'XXXXXX'生成脚本 备份好。 从数据库删除掉'XXXXXX' 再执行

alter database TestDB2 collate Chinese_Taiwan_Stroke_CI_AS

执行成功后,再把'XXXXXX'生成的脚本重新执行一下就好了。 问题终于决绝了