Changing it in the database is a little more complicated than you would think. First, there is the collation of the database, then there is the collation of each table, finally, there is the collation of the individual text fields in the DB. You can see all that if you look at the Structure tab in PhpMyAdmin.
In addition, if you change the character set as well, you have to deal with the fact that the data in the DB is in one character set, but you've specified it as being in another character set.
There's some more info here:
https://bobsguides.com/convert-db-utf8.html, though I suspect that you wouldn't need to go through all that just to change the collation.
Ultimately, you want the character set in the DB to match what's in config.inc.php and any character set specified in all templates.
For the collation, I think just changing it everywhere in the DB would be enough,
as long as the collation you pick goes with the character set. The collation is mainly just to prevent sorting errors caused by any accented characters when you alphabetize things (e.g., the order of elements and usernames in the Manager, or alphabetized lists in the front end).
That said, when I install MODX, I usually create the DB manually first and set the DB character set to utf8 / utf8mb4_general_ci. I would use utf8mb4_unicode_ci if the site was not in English. General is a little faster than unicode, but less accurate for words with accented letters.
FYI, the current recommendation for collations is utf8mb4_unicode_ci or utf8mb4_general_ci. See the accepted answer here:
https://stackoverflow.com/questions/766809/whats-the-difference-between-utf8-general-ci-and-utf8-unicode-ci. I think that would be safe for you to use if you're just changing the collation, but I could be wrong.
Whatever you do, I'd recommend backing up the site first.