Adding collate to mysql database-CodePudding

I bumped into this error ,when execting sql

ERROR 1253 (42000) at line 25: COLLATION 'utf8_general_ci' is not valid for CHARACTER SET 'utf8mb4'

So I try to add utf8_general_ci as the collate.

alter database mydatabase character set utf8mb4_general_ci collate utf8_general_ci;

I have error like this How can I add the collate to existing database.

Or My idea is wrong?

ERROR 1115 (42000): Unknown character set: 'utf8mb4_general_ci'

Thank you very much.

I try this

mysql> select * 
    -> from information_schema.collations 
    -> where CHARACTER_SET_NAME = 'utf8mb4' 
    ->   and COLLATION_NAME like '%general%';

 -------------------- -------------------- ---- ------------ ------------- --------- --------------- 
| COLLATION_NAME     | CHARACTER_SET_NAME | ID | IS_DEFAULT | IS_COMPILED | SORTLEN | PAD_ATTRIBUTE |
 -------------------- -------------------- ---- ------------ ------------- --------- --------------- 
| utf8mb4_general_ci | utf8mb4            | 45 |            | Yes         |       1 | PAD SPACE     |
 -------------------- -------------------- ---- ------------ ------------- --------- --------------- 
1 row in set (0.00 sec)

then I try this sql, it works

alter database mydatabase character set utf8mb4 collate utf8mb4_general_ci;

but, first problem ( I am trying to introduce dump file)

ERROR 1253 (42000) at line 25: COLLATION 'utf8_general_ci' is not valid for CHARACTER SET 'utf8mb4' is not solved.

CodePudding user response：

try to do this:

ALTER DATABASE `DBname` COLLATE 'utf8mb4_unicode_ci';

CodePudding user response：

Because you CHARACTER_SET_NAME is utf8mb4 you have the option to choose from these general collations:

select * 
from information_schema.collations 
where CHARACTER_SET_NAME = 'utf8mb4' 
  and COLLATION_NAME like '%general%';