Reason for multiplicity of character/collation settings in MySQL?
Version 5.7 made good strides toward settling on utf8mb4; the next version, 8.0, is there.

But, changing anything under the hood is all too likely to cause grief.  MySQL learned that lesson when "fixing" the handling of ß in utf8_general_ci collation many years ago.  It broke lots of users' tables.  So, they came out with the kludge utf8_general_mysql500_ci.

The grief over the 767-byte limit on a column in an index would break blindly changing utf8 to utf8mb4.

I predict that utf8 will live on, but only for compatibility for those upgrading.  Meanwhile, a really UTF-8-compliant utf8mb4 will be the default.  And there will be a succession of collations such as utf8mb4_unicode_ci, utf8mb4_unicode_520_ci, utf8mb4_0900_ai_ci, and more will keep up with the Unicode standard (4.0, 5.20, 9.0), which keeps changing.

The charset and collation for a database is only the default for subsequently created tables.  Ditto for table:column.

To change the charset/collation for an entire table:  ALTER TABLE .. CONVERT TO ...

I think I am sane.  This makes sense to me:

CREATE TABLE People (
    name        ... CHARACTER SET utf8mb4,
    postal_code ... CHARACTER SET ascii
);


It is even a performance issue if you let postal_code with utf8mb4 unnecessarily explode by a factor of 4 during certain operations.

As for why things are crashing in Aurora, I don't see enough info to help with this question.  And, since I have not heard of anything similar in MySQL or MariaDB, I suspect is may be an Aurora bug.  (Aurora is MySQL plus low level improvements; I have not heard of charset issues, but who knows?)