Is it safe to compare strings with more and less in MySQL?

Question

MySQL (5.1.41-3ubuntu12.10-log) seems to give predictable results when comparing strings using> (more) and <(less):

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 apparently uses 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 robust cross-platform feature?

Answer 1

I think there are some errors, you can see the documentation here for some details:

http://dev.mysql.com/doc/refman/5.5/en/comparison-operators.html

If your fields also have null values, you should also take a look at the null-safe comparison 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 | +------------+-----------+--------------+-------------+--------------+ 

Answer 2

These comparisons are common. I am sure that string comparison by ascii value or some other encoding supporting cross platform. Sorry, I do not have the resources to back it up. This is probably how it compares strings (for sorting, etc.) internally. I would expect this to be a dominant feature.