使用 utf8 字符集索引 MySQL 表列的有效方法

发布于 2024-09-14 03:12:59 字数 810 浏览 13 评论 0原文

CREATE TABLE profile_category (
  id mediumint UNSIGNED NOT NULL AUTO_INCREMENT,
  pc_name char(255) NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY idx_name (name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

这是数据库中完全采用 utf8 字符集的表之一。问题就在这里（直到现在我才知道），pc_name 列的索引将增大三倍，因为 MySQL 为每个字符保留 3 个字节。在这种情况下，索引将占用更多空间。

我无法制作更短的索引，因为我需要该值是唯一的。解决方案之一可以设置 pc_name char(255) CHARSET latin1 NOT NULL, 但我不知道这是否是一个问题。这是一个好主意，还是有任何我不知道的解决方案？

更新：pc_name 列在应用程序中经过验证，为有效的 utf8。并且它允许非西方字符。但在这种情况下，如果值得的话，我可以只允许 /[_A-Za-z]/ 进行交易。

更新 2：我尝试将 pc_name 设置为 latin1 字符集，但现在出现如下异常： Zend_Db_Statement_Exception: SQLSTATE[HY000]: General error: 1267 Illegal mix of collations (latin1_swedish_ci,IMPLICIT) 和(utf8_general_ci,COERCIBLE) 用于操作“=”

原文

CREATE TABLE profile_category (
  id mediumint UNSIGNED NOT NULL AUTO_INCREMENT,
  pc_name char(255) NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY idx_name (name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

This is one of the tables in database that is entirely in utf8 charset. The problem is here (and I didn't new about it until now) that index for pc_name column will triple times bigger, because MySQL reserves 3 bites for every char. In this case indexes will take much more space.

I cannot make shorter index, because I need this value to be unique. One of the solutions could be set pc_name char(255) CHARSET latin1 NOT NULL, but I dont't know if this is a problem or not.
Is this is a good Idea, or are there any solutions that I don't know ?

Update: the pc_name column is validated in application to be valid utf8. And it allows non western characters. But in this case I can just make a trade of and allow only /[_A-Za-z]/ if the case is worth it.

Update 2: I tried to set pc_name to latin1 charset, but now I get exceptions like: Zend_Db_Statement_Exception: SQLSTATE[HY000]: General error: 1267 Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '='

分享到QQ

分享到微博