0

I'm using MySQL 5.5.37. If I run the following query on my database

select count(*) FROM user where user_name = 'admin';

I get back the result "3". However, when I execute the query

select count(*) FROM user where user_name = 'admin     ';

, (notice the extra spaces after the word "admin", I also get back the result "3". In fact, when i look at teh records, they are the same records as the first query. However, there are no records in my databaes with the user_name field equal to "admin ". Given that I cannot upgrade my MySQL database at this time, what can I do to ensure that the second query (wiht the extra white space) returns the correct result (i.e. "0")?

Edit: The user_name field is of type varchar(50).

Dave
  • 15,639
  • 133
  • 442
  • 830

3 Answers3

1

I'm not 100% sure that it's the case, but it looks like your user_name field is a char(n) field and not varchar(n).

Such fields are filled with whitespaces to fully match the length of the field, while varchars are (I believe) null terminated strings.

Both queries return the same answer, as they also fill the missing characters with whitespaces.

If you want the query to work properly, consider changing the type of the column to varchar.

Marandil
  • 1,042
  • 1
  • 15
  • 31
0

You could try using a regular expression as described in this post? Query for exact match of an string in SQL

Community
  • 1
  • 1
mproffitt
  • 2,409
  • 18
  • 24
0
 select count(*) FROM [sb_user] where user_name = 'admin     ';

you may try this one

Zhengde Zhen
  • 26
  • 1
  • This resulted in the error "ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '[user] where user_name = 'admin '' at line 1" – Dave Feb 24 '15 at 21:49
  • you may try select count(*) FROM [sb_user] where user_name LIKE 'admin '; – Zhengde Zhen Feb 24 '15 at 22:01
  • Per my comment to the other respondent, it is not an option to change every query in my application. If the value of user_name isn't 'admin ' then MySQL should accurately return that. – Dave Feb 24 '15 at 22:53