英文:
Mysql - convert Tables in Database to diffrent encoding and collate - foreign key constraints are failing
问题
我有一个使用字符集utfmb4和校对规则utf8mb4_unicode_ci的MySQL数据库。
不，我注意到这会影响到我的搜索查询，其中我使用像'%grün%'这样的查询。这也会匹配到'Grund'。
我发现这种行为是由于我的表格/列的字符集和校对规则造成的。现在我想将表格切换到校对规则utf8mb4_de_pb_0900_ai_ci，以避免错误选择德语特殊字符。
首先，我更改了我的数据库的默认设置，这是被接受的：
ALTER DATABASE CHARACTER SET utf8mb4 COLLATE utf8mb4_de_pb_0900_ai_ci;
设置我的第一个表格的默认设置也被接受：
ALTER TABLE tablename CHARACTER SET utf8mb4 COLLATE utf8mb4_de_pb_0900_ai_ci;
但是，当我想将现有数据转换为新的设置时，出现了错误：
ALTER TABLE tablename CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_de_pb_0900_ai_ci;
引用的列'column1'和被引用的列'id'在外键约束'contraintname_fkey'中不兼容。
我可以对每个表格都执行这个操作，但总是会出现约束不兼容的错误，因为外部表格没有被转换。
我发现clever Queries中有一种方法来生成所有的ALTER语句，但由于上面描述的错误，我无法执行它们。
有没有一种简单的方法来做到这一点？
英文:
I have a MySQL Database with the Charset utfmb4 and the collate utf8mb4_unicode_ci.
No, I noticed that this influences my search queries where I use like '%grün%'.
This would also match 'Grund'.
I found that this behavior is because of the charset and collate of my Tables/Columns.
Now I want to switch the tables to the collate utf8mb4_de_pb_0900_ai_ci to avoid the wrong selection of german umlaute.
So first I change the default settings for my database which is accepted
ALTER DATABASE CHARACTER SET utf8mb4 COLLATE utf8mb4_de_pb_0900_ai_ci;
Setting the default setting for my first table is also accepted
ALTER TABLE tablename CHARACTER SET utf8mb4 COLLATE utf8mb4_de_pb_0900_ai_ci;
But when I want to convert the existing data to the new settings I get an error
ALTER TABLE tablename CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_de_pb_0900_ai_ci;
> Referencing column 'column1' and referenced column 'id' in foreign key constraint 'contraintname_fkey' are incompatible.
I can do this with every table and always get the error that the constraint is not compatible as the foreign table is not converted.
I found clever Queries to generate all alter statements, but I can not execute them because of the error described above.
Is there an easy way to do this?
答案1
得分: 1
你可以在修改表格结构时禁用外键检查。
SET FOREIGN_KEY_CHECKS=0;
...你的ALTER TABLE查询...
SET FOREIGN_KEY_CHECKS=1;
请注意，字符集中的"AI"表示不敏感于重音符号，即在比较文本时不考虑重音符号。如果需要对重音符号敏感的字符集，请选择带有"AS"的字符集。
英文:
You can disable checking of foreign keys while you are altering your tables.
SET FOREIGN_KEY_CHECKS=0;
...Your ALTER TABLE queries...
SET FOREIGN_KEY_CHECKS=1;
Remember that AI in the collation means Accent Insensitive, meaning accents are not taken into account when comparing text. For a collation that is sensitive to accents use a collation with _AS_ in its name.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论