更改mysql表编码时是否有任何不利影响？ [英] Are there any downside effects when changing mysql table encoding?

问题描述

我有一个以 latin1 编码的表格，并整理 latin1_bin 。

在我的表中有一个列注释类型'TEXT'，因为你知道这个列继承表的编码和排序规则，但从现在开始我应该更改它是 utf8 和 utf8_general_ci ，因为我开始存储特殊字符 / code>。

如果使用类似以下命令，会有什么不利影响？

  alter table notebooks修改注释文本CHARACTER SET utf8 COLLATE utf8_general_ci;感谢您的回答。
  >解决方案
 危险我认为 ALTER 会破坏现有文字。
 
 
 
此外，你的'名字'看起来中文，所以我猜你想存储汉字吗？在这种情况下，您应该使用 utf8mb4 ，而不仅仅是 utf8 。这是因为有些汉字需要4个字节（而不是Unicode BMP）。
 
 
 
我相信你需要 2个步骤： 
  ALTER TABLE notebooks MODIFY评论BLOB; 
 ALTER TABLE notebooks MODIFY注释TEXT 
 CHARACTER SET utf8mb4 COLLATE utf8mb4_general_520_ci; 
  
否则，latin1 字符将被转换为ut8。但是如果你在列中真的有中文，你没有latin1。上面的2步改变（1）关闭任何字符集的知识，和（2）确定字节真正是utf8mb4编码。
 
 
 
 更安全，请先
  RENAME TABLE notebooks TO old; 
 CREATE TABLE notebooks LIKE old; 
 INSERT INTO笔记本SELECT * FROM old; 
  
然后执行两个ALTER并测试结果。如果有麻烦，您可以 RENAME 取回旧的副本。
 
I have a table encoded in latin1 and collate latin1_bin.

In my table there is a column comments of type 'TEXT', as you know this column inherits table's encoding and collation, but from now on I should change it to be utf8 and utf8_general_ci because I'm starting to store special characters in comments.

Would it cause any downside effect if I'd use a command like the following?
alter table notebooks modify comments text CHARACTER SET utf8 COLLATE utf8_general_ci;
Thank you for your answer.
解决方案
Danger  I think that ALTER will destroy existing text.

Also, ...  Your 'name' looks Chinese, so I would guess that you want to store Chinese characters?  In that case, you should use utf8mb4, not just utf8.  This is because some of the Chinese characters take 4 bytes (and are not in the Unicode BMP).

I believe you need 2 steps:
ALTER TABLE notebooks MODIFY comments BLOB;
ALTER TABLE notebooks MODIFY comments TEXT
          CHARACTER SET utf8mb4  COLLATE utf8mb4_general_520_ci;
Otherwise the latin1 characters will be "converted" to ut8.  But if you really have Chinese in the column, you do not have latin1.  The 2-step alter, above, does (1) turn off any knowledge of character set, and (2) establish that the bytes are really utf8mb4-encoded.

To be safer, first do
RENAME TABLE notebooks TO old;
CREATE TABLE notebooks LIKE old;
INSERT INTO notebooks SELECT * FROM old;
Then do the two ALTERs and test the result.  If there is trouble, you can RENAME to get back the old copy.

                        
这篇关于更改mysql表编码时是否有任何不利影响？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！