can explain how relational operator applied on string values in mysql database.
below input table in mysql
+--------------+ | employeename | +--------------+ | emp41 | | emp1 | | emp2 | | emp3 | | emp4 | | emp5 | | emp6 | | emp7 | | emp8 | | emp9 | | emp10 | | emp11 | | emp12 | | emp13 | | emp14 | | emp15 | | emp16 | | emp17 | | emp18 | | emp19 | | emp20 | | mgr1 | | mgr2 | | mgr3 | | mgr4 | | mgr5 | | mgr6 | | mgr7 | | mgr8 | | mgr9 | | mgr10 | | emp31 | | emp32 | | emp33 | | emp34 | | emp35 | | emp36 | | emp37 | | emp38 | | emp39 | | emp40 | +--------------+ below query tried
select * employee employeename<'emp41'; below result
+--------------+ | employeename | +--------------+ | emp1 | | emp2 | | emp3 | | emp4 | | emp10 | | emp11 | | emp12 | | emp13 | | emp14 | | emp15 | | emp16 | | emp17 | | emp18 | | emp19 | | emp20 | | emp31 | | emp32 | | emp33 | | emp34 | | emp35 | | emp36 | | emp37 | | emp38 | | emp39 | | emp40 | +--------------+ it pretty clear not based on ascii values.
if string compared relational operator number, mysql try convert string number. if timestamp column compared either string or number, try convert string/number timestamp. if can't that, convert timestamp value string or number.
time , date columns compared other values strings.
you can still 'use' compare strings each other, example, select 'bar' < 'foo' evaluate true, because 'b' comes before 'f' in characterset collation.
this depends on characterset , collation.
this can explained in query this:
e comes before m in charset, means mgr values won't showed. continues per letter, emp equal continue numbers.
0 4 evaluates true, 5-9 won't, why miss emp 5-9 values.
the rest of emp values show up, because example, emp1 < emp4(1)
you might want @ following references:
- reference mysql comparison operators.
- reference mysql charactersets / collations.
Comments
Post a Comment