We all know that MySQL queries are not case-sensitive unless the columns’ collation are set to binary like
latin1_bin etc. Recently, while working on a project, I found that MySQL (server version 5.1.47) compares
'string ' as equal while
' string' are not equal. It’s a strange. Isn’t it? Did you know that?
Here’s the details of the evidence. I was working on a customized PHP/MySQL based CMS (Content Management System) and consequently on a module that handles the URL routes to generate SEO-friendly URLs. The URL for the homepage is http://www.domainname/home.html. My URL parser takes the ‘home’ as the page-slug element and queries the database table to get the page contents. When I tested by changing the URL to http://www.domainname.home .html, it still showed the content of the home page instead of throwing a 404 error page. I started to find why?
My Table structure is as follows:
CREATE TABLE IF NOT EXISTS `site_pages` (
`page_id` int(11) NOT NULL AUTO_INCREMENT,
`page_order` int(11) NOT NULL,
`page_main_heading` varchar(255) DEFAULT NULL,
`meta_title` varchar(255) DEFAULT NULL,
`page_image` varchar(255) NOT NULL,
`banner_image` varchar(225) NOT NULL,
`page_short_description1` text NOT NULL,
`page_short_description2` text NOT NULL,
`page_parent_id` varchar(10) DEFAULT 'NULL',
`page_status` varchar(10) DEFAULT NULL,
`page_slug` varchar(255) DEFAULT NULL,
`page_name` varchar(250) NOT NULL,
`page_template` varchar(30) NOT NULL,
`page_tag_line` varchar(500) NOT NULL,
PRIMARY KEY (`page_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
Let’s add a sample record in it:
INSERT INTO `site_pages` (`page_id`, `page_order`, `page_main_heading`, `meta_title`, `meta_keywords`, `meta_description`, `page_image`, `banner_image`, `page_short_description1`, `page_short_description2`, `page_long_description`, `page_parent_id`, `page_status`, `page_slug`, `page_name`, `page_template`, `page_tag_line`) VALUES
(1, 1, '', 'Sample Site - Home', '', '', '', '', '', '', '', '0', 'Active', 'home', 'Home', 'default', '');
I executed the following query:
SELECT * FROM `site_pages` WHERE `page_slug` = 'home ';
And, strangely, the query produced 1 row as a result set. As I was no allowed to change the column’s collation, to resolve the issue, I changed the query to:
SELECT * FROM `site_pages` WHERE BINARY `page_slug` = 'home ';
This time, as expected, the query produces zero result set. The MySQL keyword BINARY used to do a binary match among two strings. I’ve tried the same with the numeric columns and found the same behavior with the space added with the value. For example:
SELECT * FROM `site_pages` WHERE `page_id` = '1 ';
It will produce 1 row for the result set.
If you know why this is happening, I would request you to share the reason though the comment area below.
Hope it helps someone to save few hours.