Is it safe to compare strings with 'greater than' and 'less than' in MySQL?
Solution 1
I think there are some gotchas, you can have a look at documentation here for some details :
http://dev.mysql.com/doc/refman/5.5/en/comparison-operators.html
If your fields have null values too, you should also take a look at null-safe comparision operator: http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html#operator_equal-to
example :
mysql> select "a" > "a ", "A" > "a" , "aB" > "ab" , "a" >= NULL , "a" <=> NULL ;
+------------+-----------+--------------+-------------+--------------+
| "a" > "a " | "A" > "a" | "aB" > "ab" | "a" >= NULL | "a" <=> NULL |
+------------+-----------+--------------+-------------+--------------+
| 0 | 0 | 0 | NULL | 0 |
+------------+-----------+--------------+-------------+--------------+
Solution 2
These comparisons are common. I'm certain that comparing strings by ascii value or some other encoding like that is supported cross-platform. Sorry I don't have any resources to back it up. That's probably the way it compares strings (for sorting and such) internally. I would expect that to be a dominant feature.
Ken
Web programmer at cms.scot - 20 years of experience working with custom code bases for a variety of accommodation, retail, mapping and weather sites. Mainly PHP/MySQL on custom sites, incorporating the likes of CS-Cart, WordPress, Google Maps and including custom back end integrations with external services (Booking.com, Italian train and ticket services, Stripe payments...). Conscientious version control (currently SVN) and more pragmatic Unit Testing and object-oriented programming. Remote worker with extensive experience of dealing with external programmers and providers, sales, accounting and office staff in both English and Italian. Current tools (although always happy to learn something new): Ubuntu terminal/Vim with scripting (Bash/PHP) to handle automation, data processing and to integrate with remote sites (SSH, Curl, etc.)
Updated on June 04, 2022Comments
-
Ken almost 2 years
MySQL (5.1.41-3ubuntu12.10-log) seems to give predictable results on string comparison using > (greater than) and < (less than):
select "a" > "a", "a" > "b", "b" > "a", "ab" > "aa", "ab" > "aabbbb"; +-----------+-----------+-----------+-------------+-----------------+ | "a" > "a" | "a" > "b" | "b" > "a" | "ab" > "aa" | "ab" > "aabbbb" | +-----------+-----------+-----------+-------------+-----------------+ | 0 | 0 | 1 | 1 | 1 | +-----------+-----------+-----------+-------------+-----------------+
and also seems to make use of keys:
explain select productcode from products where productcode < 'no'; +----+-------------+----------+-------+-----------------+------+---------+------+------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+----------+-------+-----------------+------+---------+------+------+--------------------------+ | 1 | SIMPLE | products | range | productcode,ppp | ppp | 34 | NULL | 432 | Using where; Using index | +----+-------------+----------+-------+-----------------+------+---------+------+------+--------------------------+
This doesn't seem to be documented - is it a dependable cross-platform feature?