-2

I am using laravel but can use raw sql if needed.

I have multiple fields that are json fields, in that json there is translated data for that language. So, for example post table has field title and that title is {"en": "Title in english", "et": "Title in estonian"}

Now I need to make a fulltext search that searches these fields, for some columns i need to search term from all languages, not just from active one.

I am using MariaDB latest stable.

If i make index of these fields for fulltext search i can search fine but the search is case sensitive.

How can i make the search case insensitive? The json fields are currently longtext and utf8mb4_bin, laravel chose them for json field. I know bin is case sensitive collation but what else could i put so the functionality to find records by translated slug (for example) would be still there.

In laravel, one can search like ->where('slug->en','some-post-slug'). So i need to keep laravels json fields functionality intact.

I have been trying to achieve this for 2 days now, i need some external input.

user1365447
  • 145
  • 1
  • 12

1 Answers1

0

The MySQL documentation states the following:

By default, the search is performed in case-insensitive fashion. To perform a case-sensitive full-text search, use a case-sensitive or binary collation for the indexed columns. For example, a column that uses the utf8mb4 character set of can be assigned a collation of utf8mb4_0900_as_cs or utf8mb4_bin to make it case-sensitive for full-text searches.

I bet it's the same in MariaDB. If you change your collation it will work with case-insensitive searches like expected. You can change it that way:

ALTER TABLE mytable
CONVERT TO CHARACTER SET utf8mb4 
COLLATE utf8mb4_general_ci;
Philip Petrov
  • 975
  • 4
  • 8