Hoping you can help me understand a particular problem I'm facing when converting an Oracle 12c database to MySQL 5.7.
For context, let's say we have a table that looks a bit like this:
id firstname lastname --------------------------- 1 John Jones 2 tom smith 3 ALEX JonEs
Data can be entered into the table in any case or mixed case, there is no control at the application level or any constraints or triggers at the database level.
So for the purpose of effective querying, a functional index has been created in Oracle as follows, because we will always query the lastname field using the lower case function:
CREATE INDEX idx_table1_lastname ON schema1.table1 (LOWERCASE(lastname))
We can't do that in MySQL because functional indexes are not a thing. The same column in MySQL has been created as collation
utf8-general-ci so my question is this.
If we create the same index on our MySQL database, we can only do:
CREATE INDEX idx_table1_lastname ON table1 (lastname)
Will the index be effective, given the collation of our column is case-insensitive? Or should we be putting something in place to ensure all our database writes for this field are being written in a consistent case?