So I have created a View in MySQL Workbench that pulls data from many different tables with the express purpose of using it as a base against which to run all of my full database searches; and all is fine and dandy till I try to include fields whose original type is not a string, cause I then get the message:
Error Code: 1271. Illegal mix of collations for operation 'match'
So I think... not a problem, I´ll just cast this field to the proper collation like such:
select concepts_id,
MATCH(
liabilities.id collate 'utf8mb4_unicode_ci' AS "liabilities_id",
liabilities.label,
liabilities.description,
liabilities.comment,
concepts.label,
and many more fields...
)
AGAINST('my search string' IN BOOLEAN MODE) score
FROM view_made_up_of_several_tables
And sure enough I got rid of that message, but now I get a new one saying:
Error Code: 1210. Incorrect arguments to MATCH
I did some research online and everytime the problem had to do with attempting a full search on many tables using a join and not constructing the appropiate fulltext_index first, but I don´t see this is my case since I´m only looking inside a single table, even though it´s a View constructed by many tables. I've also tried:
CONVERT(liabilities.id, CHAR) AS "liabilities_id"
CAST(liabilities.id AS CHAR) AS "liabilities_id"
And I get the same Error Code:1210. If I just comment out the non text fields, such as dates and numbers, everything works fine. I´ve ran out of ideas so any help would be much appreciated. Txs!