SQL 异常汇总

MsSql

1.Cannot resolve the collation conflict between "Chinese_PRC_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.

原因：两个数据源使用了不同的排序规则

解决方案1：在 SQL 查询中强制转换排序规则

SELECT * 
FROM table2 
WHERE (
  Field1 COLLATE Chinese_PRC_CI_AS = ? 
  AND Field2 COLLATE Chinese_PRC_CI_AS = ?
)

解决方案2：修改目标表的排序规则（永久性方案）

ALTER TABLE table2
ALTER COLUMN Field1 VARCHAR(255) COLLATE Chinese_PRC_CI_AS;

ALTER TABLE table2
ALTER COLUMN Field2 VARCHAR(255) COLLATE Chinese_PRC_CI_AS;

解决方案3：使用视图抽象排序规则

--  1.在目标数据库中创建视图，统一排序规则
CREATE VIEW source2.vw_table2 AS
SELECT 
  Field1 COLLATE SQL_Latin1_General_CP1_CI_AS AS Field1,
  Field2 COLLATE SQL_Latin1_General_CP1_CI_AS AS Field2,
  -- 其他字段...
FROM source2.table2;

--  2.修改查询
SELECT * 
FROM source2.vw_table2 
WHERE (Field1 = ? AND Field2 = ?);

MySql

Oracle