3 ms·

> It's more explicit than MySQL, so in that way it's better. It sounds like you're under the impression that MySQL just makes everything case-insensitive and i

> It's more explicit than MySQL, so in that way it's better. It sounds like you're under the impression that MySQL just makes everything case-insensitive and is silent about this? That's decidedly not the case. MySQL 8 ships with 41 different character sets, supporting a total of 286 different collations. Collation names explicitly include "ci" (case-insensitive) vs "cs" (case-sensitive), as well as "ai" (accent-insensitive) vs "as" (accent-sensitive), and also the language/region conventions used for sorting purposes. You can choose collation at the column-level granularity, as well as setting defaults at the table, schema, and server levels. It's completely explicit and very configurable.

arp242 3y ago
There is no way to see from the query itself if it's case-sensitive or insensitive; that is what I meant.
- evanelias 3y ago
  Eh, just from a SQL query alone, there's no way to see that (lower(email)) is indexed, or see column data types etc. That metadata lives in the table definition, which is a normal place for it, so it seems reasonable for the collation / case-insensitivity to not be explicit in the query text. Besides, MySQL also supports functional indexes, so you could do the (lower(email)) approach in MySQL too if you really want!
  - arp242 3y ago
    No, you can't see everything, but you can see the exact comparison it's making. Is that useful? You can decide that for yourself but I like being able to see as much of the logic (and thus verify the correctness) in the query itself. Also helps with copy/paste and some other things. I never said you can't use functional indexes in MySQL. Someone said "you can't do this in PostgreSQL" and I just commented "here's (one way) to do this, maybe that's helpful some day". That's it.
    - evanelias 3y ago
      My apologies, I misunderstood "It's more explicit than MySQL" to imply that you were saying that approach couldn't be used in MySQL.

arp242 3y ago

There is no way to see from the query itself if it's case-sensitive or insensitive; that is what I meant.

evanelias 3y ago
Eh, just from a SQL query alone, there's no way to see that (lower(email)) is indexed, or see column data types etc. That metadata lives in the table definition, which is a normal place for it, so it seems reasonable for the collation / case-insensitivity to not be explicit in the query text. Besides, MySQL also supports functional indexes, so you could do the (lower(email)) approach in MySQL too if you really want!
- arp242 3y ago
  No, you can't see everything, but you can see the exact comparison it's making. Is that useful? You can decide that for yourself but I like being able to see as much of the logic (and thus verify the correctness) in the query itself. Also helps with copy/paste and some other things. I never said you can't use functional indexes in MySQL. Someone said "you can't do this in PostgreSQL" and I just commented "here's (one way) to do this, maybe that's helpful some day". That's it.
  - evanelias 3y ago
    My apologies, I misunderstood "It's more explicit than MySQL" to imply that you were saying that approach couldn't be used in MySQL.