SQL 为什么 Access 中的 LIKE 查询不返回任何记录？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5166907/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Why does a LIKE query in Access not return any records?
提问by Jake
Is there any reason why
有什么理由吗
SELECT * FROM MyTable WHERE [_Items] LIKE '*SPI*'
does not return any records with OleDbAdapter.Fill(DataSet)or OleDbCommand.ExecuteReader()?
不使用OleDbAdapter.Fill(DataSet)或返回任何记录OleDbCommand.ExecuteReader()？
When I run the same SQL in MS Access directly, it returns the expected records. Also, in the same code, if I change the SQL to
当我直接在 MS Access 中运行相同的 SQL 时，它返回预期的记录。另外，在相同的代码中，如果我将 SQL 更改为
SELECT * FROM MyTable
all records are returned.
返回所有记录。
回答by onedaywhen
Try changing LIKEto ALIKEand your wildcard characters from *to %.
尝试更改LIKE为ALIKE和您的通配符从*到%。
The Access Database Engine (Jet, ACE, whatever) has two ANSI Query Modeswhich each use different wildcard characters for LIKE:
Access 数据库引擎（Jet、ACE 等）有两种ANSI 查询模式，每种模式使用不同的通配符LIKE：
ANSI-89 Query Mode uses
*ANSI-92 Query Mode uses
%
ANSI-89 查询模式使用
*ANSI-92 查询模式使用
%
OLE DB always uses ANSI-92 Query Mode. DAO always uses ANSI-89 Query Mode. The Access UI can be set to use one or the other.
OLE DB 始终使用 ANSI-92 查询模式。DAO 始终使用 ANSI-89 查询模式。Access UI 可以设置为使用其中一种。
However, when using ALIKEkeyword the wildcard character is always %regardless of ANSI Query Mode.
但是，当使用ALIKE关键字时，通配符始终%与 ANSI 查询模式无关。
Consider a business rule that states a data element must consist of exactly eight numeric characters. Say I implemented the rule as follows:
考虑一个业务规则，该规则规定数据元素必须正好由八个数字字符组成。假设我按如下方式实现了规则：
CREATE TABLE MyStuff
(
ID CHAR(8) NOT NULL,
CHECK (ID NOT LIKE '%[!0-9]%')
);
It is inevitable that I would use %as the wildcard character because Access's CHARdata type and CHECKconstraints can only be created in ANSI-92 Query Mode.
我将不可避免地%用作通配符，因为 Access 的CHAR数据类型和CHECK约束只能在 ANSI-92 查询模式中创建。
However, someone could access the database using DAO, which always uses ANS-89 Query Mode, and the %character would be considered a literal rather than a 'special' character, and the following code could be executed:
但是，有人可以使用 DAO 访问数据库，它总是使用 ANS-89 查询模式，并且该%字符将被视为文字而不是“特殊”字符，并且可以执行以下代码：
INSERT INTO MyStuff (ID) VALUES ('%[!0-9]%');
the insert would succeed and my data integrity would be shot :(
插入会成功，我的数据完整性会被击中:(
The same could be said by using LIKEand *in a Validation Rule created in ANSI-89 Query Mode and someone who connects using ADO, which always uses ANSI-92 Query Mode, and INSERTs a *character where a *character ought not to be.
通过使用LIKE和*在 ANSI-89 查询模式中创建的验证规则和使用 ADO 连接的人，它总是使用 ANSI-92 查询模式，并*在*字符不应该出现的地方插入一个字符，可以说同样的话。
As far as I know, there is no way of mandating which ANSI Query Mode is used to access one's Access database. Therefore, I think that all SQL should be coded to behave consistently regardless of ANSI Query Mode chosen by the user.
据我所知，没有办法强制要求使用哪种 ANSI 查询模式来访问一个人的 Access 数据库。因此，我认为无论用户选择何种 ANSI 查询模式，都应该对所有 SQL 进行编码以使其行为一致。
Note it is not too difficult to code for both using LIKEwith the above example e.g.
请注意，使用LIKE上述示例对两者进行编码并不太困难，例如
CHECK (
ID NOT LIKE '%[!0-9]%'
AND ID NOT LIKE '*[!0-9]*'
)
...or indeed avoid wildcards completely e.g.
...或者确实完全避免通配符，例如
CHECK (ID LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]')
However, using ALIKEwill result in less verbose code i.e. easier for the human reader and therefore easier to maintain.
然而，使用ALIKE将减少冗长的代码，即对人类读者来说更容易，因此更容易维护。
Also, when the time comes to port to a SQL product that is compliant with SQL Standards, ALIKEports well too i.e. transforming the ALIKEkeyword to LIKEis all that is required. When parsing a given SQL predicate, it is far, far easier to locate the one LIKEkeyword in than it is to find all the multiple instances of the *character in text literals. Remember that "portable" does not mean "code will run 'as is'"; rather, it is a measure of how easy it is to move code between platforms (and bear in mind that moving between versions of the same product is a port e.g. Jet 4.0 to ACE is a port because user level security no longer functions, DECIMALvalues sort differently, etc).
此外，当需要移植到符合 SQL 标准的 SQL 产品时，移植ALIKE也很好，即将ALIKE关键字转换LIKE为。在解析给定的 SQL 谓词时，定位一个LIKE关键字要比*在文本文本中找到该字符的所有多个实例要容易得多。请记住，“可移植”并不意味着“代码将按原样运行”；相反，它衡量在平台之间移动代码的难易程度（请记住，在同一产品的版本之间移动是一个端口，例如 Jet 4.0 到 ACE 是一个端口，因为用户级安全不再起作用，DECIMAL值排序不同，等等）。
回答by Neil Knight
Change your *to %as %is the wildcard search when using OLE DB.
使用 OLE DB 时，将您*的%原样更改为%通配符搜索。
SELECT * FROM MyTable WHERE [_Items] LIKE '%SPI%'
回答by Pooli
Try converting your wildcard chars (*) to %
尝试将通配符 (*) 转换为 %
This should sort the issue out.
这应该解决问题。
回答by Levi
Jeez, this works! Thanks a lot.
天哪，这有效！非常感谢。
I just had to replace not like criteriato not alike criteria.
我只需要替换not like criteria为not alike criteria.
I'm sharing my "story" to help others find this post easier and save them from a two hours search.
我正在分享我的“故事”，以帮助其他人更轻松地找到这篇文章，并将他们从两个小时的搜索中解救出来。
Although I've linked the Excel 95-97 xls files to the Access 2010 database, and ran create tableand insert intoqueries to import all data into a database, for some strange reason, the select query couldn't find the strings I've typed.
虽然我已经挂了Excel 95-97 XLS文件到Access 2010数据库，跑create table和insert into查询到的所有数据导入到数据库中，一些奇怪的原因，选择查询找不到我所输入的字符串。
I tried not like "something"and not like "%something%"with no success - simply didn't work.
我试图not like "something"与not like "%something%"没有成功-根本没有工作。
L
升
