Openbase SQL 区分大小写的奇怪之处（'=' 与 LIKE）- 移植到 MySQL
我们正在将以前使用 Openbase 7 的应用程序移植到现在使用 MySQL 5.0 的应用程序。
OB 7 在区分大小写方面确实有相当糟糕的定义（即未记录）行为。 我们现在只是在使用 MySQL 尝试相同的查询时才发现这一点。
看来 OB 7 处理使用“=”的查找与使用“LIKE”的查找不同：如果您有两个值“a”和“A”，并使用 WHERE f="a" 进行查询，则它只查找“ a”字段，而不是“A”字段。 但是，如果您使用 LIKE 而不是“=”，那么它会同时找到两者。
我们对 MySQL 的测试表明，如果我们使用非二进制排序规则（例如 latin1），那么“=”和“LIKE”都会不区分大小写进行比较。 然而，为了模拟 OB 的行为，我们只需要让“=”区分大小写。
我们现在试图弄清楚如何在 MySQL 中处理这个问题，而不必向我们的所有查询添加大量 LOWER() 函数调用（有很多！）。
我们可以完全控制 MySQL DB，这意味着我们可以根据需要选择其排序规则模式（幸运的是，我们的表名和唯一索引不受区分大小写问题的影响）。
有什么建议如何用最少的代码更改来模拟 MySQL 上的 OpenBase 行为吗？
（我意识到我们的源代码中的一些智能正则表达式替换来添加 LOWER 调用可能会成功，但我们宁愿找到不同的方法）
We are porting an app which formerly used Openbase 7 to now use MySQL 5.0.
OB 7 did have quite badly defined (i.e. undocumented) behavior regarding case-sensitivity. We only found this out now when trying the same queries with MySQL.
It appears that OB 7 treats lookups using "=" differently from those using "LIKE": If you have two values "a" and "A", and make a query with WHERE f="a", then it finds only the "a" field, not the "A" field. However, if you use LIKE instead of "=", then it finds both.
Our tests with MySQL showed that if we're using a non-binary collation (e.g. latin1), then both "=" and "LIKE" compare case-insensitively. However, to simulate OB's behavior, we need to get only "=" to be case-sensitive.
We're now trying to figure out how to deal with this in MySQL without having to add a lot of LOWER() function calls to all our queries (there are a lot!).
We have full control over the MySQL DB, meaning we can choose its collation mode as we like (our table names and unique indexes are not affected by the case sensitivity issues, fortunately).
Any suggestions how to simulate the OpenBase behaviour on MySQL with the least amount of code changes?
(I realize that a few smart regex replacements in our source code to add the LOWER calls might do the trick, but we'd rather find a different way)
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
另一个想法.. MySQL 是否提供类似用户定义函数之类的东西？ 然后，您可以编写一个 UDF 版本的 like，不区分大小写（ci_like 左右）并将所有 like 更改为 ci_like。 可能比重新调用 lower in .. 更容易做到。
Another idea .. does MySQL offer something like User Defined Functions? You could then write a UDF-version of like that is case insesitive (ci_like or so) and change all like's to ci_like. Probably easier to do than regexing a call to lower in ..
这两篇文章讨论了 mysql 中的大小写敏感性：
两者是 Google 搜索中的早期点击：
These two articles talk about case sensitivity in mysql:
Both were early hits in this Google search:
我知道这不是您正在寻找的答案..但考虑到您想保留这种行为，您不应该明确地对其进行编码（而不是在某处更改一些神奇的“配置”）吗？
这可能是一项相当大的工作，但至少您知道代码的哪些区域受到影响。
I know that this is not the answer you are looking for .. but given that you want to keep this behaviour, shouldn't you explicitly code it (rather than changing some magic 'config' somewhere)?
It's probably quite some work, but at least you'd know which areas of your code are affected.
快速浏览MySQL 文档似乎表明这正是 MySQL 的做法：
A quick look at the MySQL docs seems to indicate that this is exactly how MySQL does it: