土耳其语 SQL 排序规则问题（土耳其语“I”）
我在将 MSSQL 数据库设置为任何土耳其语排序规则时遇到问题。 由于“Turkish I”问题，我们所有包含“i”的查询都无法正常工作。 例如，如果我们有一个名为“Unit”的表，其中定义了“UnitID”列，则查询“select unitid from unit”将不再有效，因为“id”中的小写“i”与定义的大写字母不同我在“UnitID”中。 错误消息将显示为“无效的列名‘unitid’”。
我知道发生这种情况是因为在土耳其语中，字母 i 和 I 被视为不同的字母。 但是，我不确定如何解决这个问题？ 无法检查 DB 中的所有 1900 个 SP 并更正“i”的大小写。
任何帮助将不胜感激，甚至可以使用其他排序规则代替土耳其语但支持其字符集的建议。
I'm having problems with our MSSQL database set to any of the Turkish Collations. Becuase of the "Turkish I" problem, none of our queries containing an 'i' in them are working correctly. For example, if we have a table called "Unit" with a column "UnitID" defined in that case, the query "select unitid from unit" no longer works because the lower case "i" in "id" differs from the defined capital I in "UnitID". The error message would read "Invalid column name 'unitid'."
I know that this is occurring because in Turkish, the letter i and I are seen as different letters. However, I am not sure as to how to fix this problem? It is not an option to go through all 1900 SPs in the DB and correct the casing of the "i"s.
Any help would be appreciated, even suggestions of other collations that could be used instead of Turkish but would support their character set.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（7）
事实证明，最好的解决方案实际上是重构所有 SQL 和代码。
在过去的几天里，我编写了一个重构应用程序来修复所有存储过程、函数、视图、表名，使其保持一致并使用正确的大小写，例如：将更
改为
应用程序然后还会遍历代码并替换任何出现的情况存储过程及其参数的信息，并更正它们以匹配数据库中定义的大小写。 应用程序中的所有数据表都设置为不变的区域设置（感谢 FXCop 指出所有数据表..），这可以防止代码内的调用必须区分大小写。
如果有人想要该应用程序或对此过程有任何建议，您可以通过 [email protected] 与我联系]。
Turns out that the best solution was to in fact refactor all SQL and the code.
In the last few days I've written a refactoring app to fix up all Stored procs, functions, views, tablenames to be consistent and use the correct casing eg:
would be changed to
The app also then goes through the code and replaces any occurrences of the stored proc and its parameters and corrects them to match the case defined in the DB. All datatables in the app are set to invariant locale (thanks to FXCop for pointing out all the datatables..), this prevents the calls from within code having to be case sensitive.
If anyone would like the app or any advice on the process you can contact me on [email protected].
我在土耳其的支持下开发了很多系统，正如您所说，这是众所周知的问题。
最佳实践是将数据库设置更改为 UTF-8，仅此而已。 它应该解决所有问题。
如果您想要支持 (ı-I,i-і) 中的区分大小写，那么您可能会遇到问题，而这在 SQL Server 中支持可能会出现问题。 如果整个入口来自 Web，请确保也是 UTF-8。
如果您将 Web UTF-8 输入和 SQL Server 设置保留为 UTF-8，一切都会顺利进行。
I developed so many systems with Turkish support and this is well known problem as you said.
Best practice to do change your database settings to UTF-8, and that's it. It should solve the all problem.
You might run into problems if you want to support case-sensitivity in (ı-I,i-İ) that can be a problematic to support in SQL Server. If the whole entrance is from Web ensure that is UTF-8 as well.
If you keep your Web UTF-8 input and SQL Server settings as UTF-8 everything should goes smoothly.
也许我不明白这里的问题，但这是否更有可能是因为数据库区分大小写而您的查询不区分大小写？ 例如，在 Sybase 上我可以执行以下操作：
这告诉我我的数据库不区分大小写：
Perhaps I don't understand the problem here, but is this not more likely because the database is case sensitive and your query is not? For example, on Sybase I can do the following:
Which tells me that my database is case-insensitive:
如果您可以更改正在使用的排序规则，请尝试使用不变区域设置。 但请确保您不会影响客户名称和地址等其他信息。 如果客户习惯于不区分大小写地搜索自己的名字，那么如果 ı 和 I 不再相等，或者 i 和 ï 不再相等，他们就不会喜欢这样。
If you can change the collation that you're using then try the Invariant locale. But make sure you don't impact other things like customer names and addresses. If a customer is accustomed to having case insensitive searching for their own name, they won't like it if ı and I stop being equivalent, or if i and İ stop being equivalent.
您能否将数据库排序规则更改为默认值：这将使所有文本列保留土耳其语排序规则？
查询将有效，但数据将正常运行。 理论上...
临时表和带有 varchar 列的表变量存在一些问题：您必须向这些添加 COLLATE 子句
Can you change the database collation to the default: this will leave all your text columns with the Turkish colllation?
Queries will work but data will behave correctly. In theory...
There are some gotchas with temp tables and table variables with varchar columns: you'll have to add COLLATE clauses to these
我意识到您不想通过所有存储过程来解决问题，但也许您可以使用重构工具来解决问题。 我说看看 SQL 重构。 我没有使用它，但看起来很有希望。
I realize you don't want to go through all the stored procedures to fix the issue but maybe you'd be OK to use a refactoring tool to solve the problem. I say take a look at SQL Refactor. I didn't use it but looks promising.
将机器的区域设置更改为英语（美国）完全可以节省时间！
Changing the Regional Settings of your machine to English(US) completely saves the day!