Strange fact about string matching in MySQL query

Standard

We all know that MySQL queries are not case-sensitive unless the columns’ collation are set to binary like utf8_bin, latin1_bin etc. Recently, while working on a project, I found that MySQL (server version 5.1.47) compares 'string' and 'string ' as equal while 'string' and ' 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,
`meta_keywords` text,
`meta_description` text,
`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_long_description` longtext,
`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.

Cheers :)

Advertisements

One thought on “Strange fact about string matching in MySQL query

  1. “All MySQL collations are of type PADSPACE. This means that all CHAR, VARCHAR, and TEXT values in MySQL are compared without regard to any trailing spaces. “Comparison” in this context does not include the LIKE pattern-matching operator, for which trailing spaces are significant. For example:”

    Source http://dev.mysql.com/doc/refman/5.7/en/char.html

I will be happy to answer your queries

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s