Sql where 子句不起作用 [英] Sql where clause not working

问题描述

SQL where 子句在我的数据库中不起作用.

我有一个名为站点"的表和类似的结构

id 站点1 xyz.com2 google.com3 example.com

我正在运行此 SQL 查询

SELECT * FROM `sites` WHERE `site` = "google.com";

但是我得到了这个输出

 MySQL 返回一个空的结果集(即零行).(查询耗时 0.0009 秒)

我这辈子从来没有见过这样的事情.

更新:截图

我不想在项目中应用此查询.

SELECT * FROM `sites` WHERE `site` LIKE "%google.com%";

<预><代码>#

<小时>

真正的问题在于创建数据库的 insert 命令.试试

INSERT INTO sites (id, site) VALUES (1, '\nxyz.com\n'), (2, '\ngoogle.com\n'), (3, '\nexample.com\n')

并手动检查表中的记录.你不会看到换行符.这是我注意到的 SQL 中的一个问题.

解决方案

更新:OP 在他的数据集中有不可见的换行符 (\n).@EternalPoster(和我)认为 Trim 会删除所有空格，但是 用于正则表达式，所以它似乎不是 . 字符被用作通配符，但这可能值得一看.

创建一个空数据库并尝试运行我上面的脚本.你得到的结果和我一样吗?

SQL where clause is not working in my database.

I have a table called "sites" and structure like that

id     site
1      xyz.com
2      google.com
3      example.com

I am running this SQL query

SELECT * FROM `sites` WHERE `site` = "google.com";

But I am getting this output

 MySQL returned an empty result set (i.e. zero rows). (Query took 0.0009 sec)

I never see before like that in my life.

Update: Screenshot

I do not want to apply this query in project.

SELECT * FROM `sites` WHERE `site` LIKE "%google.com%";

#

---

The real problem was in insert commands on creation of DB. Try

INSERT INTO sites (id, site) VALUES (1, '\nxyz.com\n'), (2, '\ngoogle.com\n'), (3, '\nexample.com\n')

and manually check records in the table. You would not see line breaks. This is an issue in SQL I've noticed.

解决方案

UPDATE: OP had invisible newline characters (\n) in his dataset. @EternalPoster (and I) supposed that Trim would remove all whitespace, but MySql Trim Documentation specifies leading & trailing spaces only.

---

This is what I did:

-- for http://stackoverflow.com/questions/27203169/sql-query-not-work-for-google-com
-- and http://stackoverflow.com/questions/27202157/sql-where-clause-not-working

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";

-- --------------------------------------------------------

DROP TABLE IF EXISTS `sites`;

--
--  structure for table `sites`
--
CREATE TABLE IF NOT EXISTS `sites` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `site` varchar(32) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;

--
-- data for table `sites`
--
INSERT INTO `sites` (`id`, `site`) VALUES
(1, 'xyz.com'),
(2, 'google.com'),
(3, 'example.com');

--
-- select google
--
SELECT * 
FROM sites 
WHERE site = 'google.com'
;

--
-- select google
--
SELECT * 
FROM sites 
WHERE site = 'google.com'
;

and this is what I got:

So in my case, I see the script functioning as expected.

What's different about your case? My installation is a fairly default setup. The fact that Like '%google.com%' works on your dataset suggests a couple things. Folks have already suggested TRIM, because the Like expression would match invisible characters (spaces, tabs, backspaces, nulls). MySQL has a separate operator REGEXP for regular expressions, so it wouldn't seem to be that the . character is being used as a wildcard, but that might be worth a look.

Create an empty database and try running my script above. Do you get the same result I do?

这篇关于Sql where 子句不起作用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！