Cant change charset of database

35 views Asked by At

Now I have character set utf8mb3 for my database. I want to change it to utf8mb4. For this task, I create a script, that creates SQL script, that doing it. This script can generate rows like this:

ALTER DATABASE `database_name` CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci;
ALTER TABLE `database_name`.`table1` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
ALTER TABLE `database_name`.`table2` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
...
ALTER TABLE `database_name`.`table1` CHANGE `column1` `column1` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL;
ALTER TABLE `database_name`.`table1` CHANGE `column2` `column2` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL;
ALTER TABLE `database_name`.`table1` CHANGE `column3` `column3` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL;
...

I realize, that I have problems with foreign keys and unique keys (I think) and add such rows to my generated script:

SET SESSION FOREIGN_KEY_CHECKS = 0;
SET SESSION UNIQUE_CHECKS = 0;
...
SET SESSION FOREIGN_KEY_CHECKS = 1;
SET SESSION UNIQUE_CHECKS = 1;

The issues with foreign keys are gone, but I still have such errors with unique keys:

ERROR 1062 (23000) at line 14: Duplicate entry 'some_id' for key 'table1.column1'

Can I just set some variable to ignore such errors? What can I do with them?

0

There are 0 answers