How to store file paths, which collation to use?

Question

I have a table that stores Windows system paths. I am scratching my head about how to store them and in particular which collation to use.

The paths are unicode strings. They are case insensitive, therefore 'utf8_binis probably not the best thing to do. However all theutf8_*_ci` collations allows equality between strings corresponding to different paths.

I am aware that path comparison is generally much more involved that string comparison, due to the potential presence of parent (..) directories, symbolic links, 8.3 alternative naming, aso. I am assuming that the folders are input in a systematically consistent fashion.

What do people do when storing paths? Do they assume that folders are indeed always stored in a consistent fashion and say always store Windows paths in lower or upper case and use utf8_general? Or another collation? Or simply forget about the idea of using server-side path comparison?

Answer 1

Not until MySQL 8.0 is there a "case-insensitive, accent-sensitive" collation:

mysql> set names utf8mb4 collate utf8mb4_0900_as_ci;
Query OK, 0 rows affected (0.00 sec)

mysql> select 'a'='A', "a" = "à" ;
+---------+------------+
| 'a'='A' | "a" = "à"  |
+---------+------------+
|       1 |          0 |
+---------+------------+
1 row in set (0.00 sec)