PhP MySQL NOT LIKE 不返回正确的值

Question

我正在尝试在 PHP 和 MySQL 中使用 LIKE 和 NOT LIKE 查询结果。

我有一个包含 2 列的 BeaconEvent 表:BeaconID 和 EventID；

表BeaconEvent:

BeaconID        EventID
-------------------------
5DBA-BC5B       Time0003
5DBA-BC5B       NLB0001
5264-9028       NLB0004
1F8B-35E6       NLB0005

当我运行 MySQL 命令以选择所有 BeaconID 且 EventID 是特定值时，查询工作正常但是当我运行查询以选择 BeaconID 与 EventID 不等于指定值，它返回所有 BeaconID (这是不对的，应该排除 BeaconID which EventID 等于特定值)

有效的示例查询:

SELECT BeaconEvent.BeaconID 
FROM BeaconEvent 
WHERE BeaconEvent.EventID = "Time0003"

SELECT BeaconEvent.BeaconID 
FROM BeaconEvent 
WHERE BeaconEvent.EventID LIKE "Time0003"

并且这些查询不起作用:

SELECT BeaconEvent.BeaconID 
FROM BeaconEvent 
WHERE BeaconEvent.EventID != "Time0003"

SELECT BeaconEvent.BeaconID 
FROM BeaconEvent 
WHERE BeaconEvent.EventID <> "Time0003"

SELECT BeaconEvent.BeaconID 
FROM BeaconEvent 
WHERE BeaconEvent.EventID NOT LIKE "Time0003"

SELECT BeaconEvent.BeaconID 
FROM BeaconEvent 
WHERE BeaconEvent.EventID NOT LIKE "%Time0003%"

有什么建议可以比较不等于特定值的值吗？提前致谢

Answer 1

您的数据具有相同的 BeaconID 两个值:

BeaconID        EventID
5DBA-BC5B       Time0003       
5DBA-BC5B       NLB0001   

您的查询可以正常运行:

SELECT BeaconEvent.BeaconID 
FROM BeaconEvent 
WHERE BeaconEvent.EventID <> "Time0003";

SELECT BeaconEvent.BeaconID 
FROM BeaconEvent 
WHERE BeaconEvent.EventID = "Time0003";

SqlFiddleDemo

输出:

╔═══════════╗
║ BeaconID  ║
╠═══════════╣
║ 5DBA-BC5B ║
║ 5264-9028 ║
║ 1F8B-35E6 ║
╚═══════════╝

╔═══════════╗
║ BeaconID  ║
╠═══════════╣
║ 5DBA-BC5B ║
╚═══════════╝

---

如果你想排除它(并且你确定你的数据是正确的)你可以使用:

SELECT BeaconEvent.BeaconID 
FROM BeaconEvent 
WHERE BeaconEvent.BeaconID <> (SELECT BeaconID 
                               FROM BeaconEvent
                               WHERE EventID = 'Time0003')

SqlFiddleDemo2

输出:

╔═══════════╗
║ BeaconID  ║
╠═══════════╣
║ 5264-9028 ║
║ 1F8B-35E6 ║
╚═══════════╝