近日,在項目Debug過程中發現了SQL Server排序規則衝突的問題。 由於原資料庫是從英文環境的SQL中生成的,其排序規則為“SQL_Latin1_General_CP1_CI_AS”,備份到本地中文環境之後,預設的排序規則為“Chinese_PRC_CI_AS”。本來對應的查詢語句一直處於穩 ...
近日,在項目Debug過程中發現了SQL Server排序規則衝突的問題。
由於原資料庫是從英文環境的SQL中生成的,其排序規則為“SQL_Latin1_General_CP1_CI_AS”,備份到本地中文環境之後,預設的排序規則為“Chinese_PRC_CI_AS”。本來對應的查詢語句一直處於穩定的狀態。但由於新增了欄位,本地環境新增欄位排序規則為“Chinese_PRC_CI_AS”,這時與原有的欄位進行聯查時會出現錯誤:
無法解決 equal to 運算中 "SQL_Latin1_General_CP1_CI_AS" 和 "Chinese_PRC_CI_AS" 之間的排序規則衝突。
為解決排序規則衝突,可直接修改對應欄位的排序規則,使其一致則可避免查詢出錯。
ALTER TABLE [表名] ALTER COLUMN [欄位名] nvarchar(256) COLLATE Chinese_PRC_CI_AS'
但是資料庫中還有很多排序為“SQL_Latin1_General_CP1_CI_AS”的欄位,如果逐個去改,幾個十幾個欄位的話還可以考慮,要是幾十上百個工作量可想而知。
我們可以先查詢當前資料庫的需要修改的欄位,查詢對應的表名、欄位名、排序規則、欄位類型、以及對應的長度等等。
SELECT t.name AS [Table], c.name AS [Column], c.collation_name AS [Collation], TYPE_NAME( c.system_type_id) AS [TypeName], c.max_length AS [TypeLength] FROM sys.columns c RIGHT JOIN sys.tables t ON c.object_id = t.object_id WHERE c.collation_name IS NOT NULL
資料庫查詢的結果為437行...
所以,過多的修改量基本上是不可能手動去慢慢修改的,需要通過SQL查詢結果統一修改。
我是將結果集插入到臨時表中,在通過迴圈臨時表,exec執行拼接SQL語句去修改每一個記錄,具體代碼如下:
DECLARE @table NVARCHAR(128)--迴圈Item表名 DECLARE @column NVARCHAR(128)--迴圈Item欄位名 DECLARE @type NVARCHAR(128)--對應欄位的類型,char、nchar、varchar、nvarchar等 DECLARE @typeLenght NVARCHAR(128)--對應類型的長度,nchar、nvarchar需要將數值除於2 DECLARE @sql NVARCHAR(MAX )--要拼接執行的sql語句 SET ROWCOUNT 0 SELECT NULL mykey, c.name, t.name AS [Table], c.name AS [Column], c.collation_name AS [Collation], Type_name(c.system_type_id) AS [TypeName], c.max_length AS [TypeLength] INTO #temp FROM sys.columns c RIGHT JOIN sys.tables t ON c.object_id = t.object_id WHERE c.collation_name IS NOT NULL --先測試Product表 --AND t.name='Product' SET ROWCOUNT 1 UPDATE #temp SET mykey = 1 WHILE @@ROWCOUNT > 0 BEGIN SET ROWCOUNT 0 --每次查詢第一條記錄並賦值到對應變數中 SELECT @table = [Table], @column = [Column], @type = TypeName, @typeLenght = TypeLength FROM #temp WHERE mykey = 1 --nchar、nvarchar需要將數值除於2 IF CONVERT(INT, @typeLenght) > 0 AND ( @type = 'nvarchar' OR @type = 'nchar' ) BEGIN SET @typeLenght=CONVERT(NVARCHAR(128), CONVERT(INT, @typeLenght) / 2) END IF @typeLenght = '-1' BEGIN SET @typeLenght='max' END --拼接sql,註意表名、欄位名要帶[],避免Group等關鍵字 SET @sql=' ALTER TABLE [' + @table + '] ALTER COLUMN [' + @column + '] ' + @type + '(' + @typeLenght + ') COLLATE Chinese_PRC_CI_AS' --Try執行 BEGIN TRY EXEC(@sql) END TRY --Catch查詢異常結果 BEGIN CATCH SELECT @sql AS [ASL], Error_message() AS msg END CATCH DELETE #temp WHERE mykey = 1 SET ROWCOUNT 1 UPDATE #temp SET mykey = 1 END SET ROWCOUNT 0 DROP TABLE #tempView Code
執行SQL,更新出錯的try catch查詢結果如下:
我們可看到只有寥寥的幾個欄位需要通過手動去修改,這些修改不成功的大部分是由於外鍵關聯等原因,逐個排查即可。
至此,SQL已自動修改了大部分欄位,大大的減少了工作量。