更改大型数据表的字符集的更好方法是哪种? [英] which is the better way to change the character set for huge data tables?

问题描述

在我的生产数据库中，与警报相关的表是使用默认字符集"latin"创建的，因此，在尝试时会出现错误 在表中插入日语字符.我们需要将表和列的默认字符集更改为UTF8. 由于这些表中包含大量数据，Alter命令可能会花费很多时间(在我的本地数据库中使用相同数量的数据花费了5个小时) 并锁定将导致数据丢失的表.我们能否计划一种将字符集更改为UTF8而不丢失数据的机制.

In my production database Alerts related tables are created with default CharSet of "latin", due to this we are getting error when we try to insert Japanese characters in the table. We need to change the table and columns default charset to UTF8. As these tables are having huge data, Alter command might take so much time (it took 5hrs in my local DB with same amount of data) and lock the table which will cause data loss. Can we plan a mechanism to change the Charset to UTF8, without data loss.

更改大型数据表的字符集的更好方法是什么?

which is the better way to change the charset for huge data tables?

推荐答案

我在mysql手册

在大多数情况下，ALTER TABLE会制作原始文件的临时副本 桌子. MySQL等待其他正在修改表的操作， 然后继续.它将更改合并到副本中，删除 原始表，并重命名新表.而ALTER TABLE是 执行时，其他会话可以读取原始表.更新 并写入在ALTER TABLE操作之后开始的表 开始暂停直到新表准备好，然后 自动重定向到新表，而没有任何失败的更新

In most cases, ALTER TABLE makes a temporary copy of the original table. MySQL waits for other operations that are modifying the table, then proceeds. It incorporates the alteration into the copy, deletes the original table, and renames the new one. While ALTER TABLE is executing, the original table is readable by other sessions. Updates and writes to the table that begin after the ALTER TABLE operation begins are stalled until the new table is ready, then are automatically redirected to the new table without any failed updates

是的-在执行此操作时最大程度地减少停机时间是很棘手的.这取决于表的使用情况，是否还有更多的读/写操作?

So yes -- it's tricky to minimize downtime while doing this. It depends on the usage profile of your table, are there more reads/writes?

我能想到的一种方法是使用某种复制.因此，创建一个使用UTF-8的新Alert表，并找到一种在不影响可用性/吞吐量的情况下将原始表复制到新表的方法.复制完成(或足够接近)后，通过重命名表来切换表吗?

One approach I can think of is to use some sort of replication. So create a new Alert table that uses UTF-8, and find a way to replicate original table to the new one without affecting availability / throughput. When the replication is complete (or close enough), switch the table by renaming it ?

这当然说起来容易做起来难-甚至有可能需要更多的学习.

Ofcourse this is easier said than done -- need more learning if it's even possible.

这篇关于更改大型数据表的字符集的更好方法是哪种?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！