MySQL: Why is that when creating a schema with collation utf8mb4_unicode_ci it becomes utf8mb4_0900_ai_ci

Question

When I create a schema with collition utf8mb4_unicode_ci it becomes collition utf8mb4_0900_ai_ci (using Workbench). Even when the default settings of the server have collation utf8mb4_unicode_ci set it still becomes utf8mb4_0900_ai_ci.

CREATE SCHEMA `new_schema` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

This gives issues with foreign key constrains with another database while importing the database. The only way to fix it is to set the columns for the constrains manually one by one to collation utf8mb4_unicode_ci. Once I do that the constrains can be made.

Hope someone can explain why this happens and has an answer how to prevent it.

Best, Ted

@Rick James, This is the output you requested:

Answer 1

The Workbench table inspector is not showing the collation that was set.

Run SHOW TABLE STATUS FROM your_schema; and you'll see the collation that was set. Or even in Workbench's Shema Inspector the correct collation is displayed.